How to check Data Guard Physical standby status? (Oracle)

For data guard issues from an existing physical standby, I usually only use the following queries to diagnostic the problem. These queries can serve 95% of the cases and tell me where is the problem from.

For the rest 5% that you would like to look deeper, check out the srdc_DGPhyStby_diag.sql from Oracle support site. It gives you a report with all details in html format.

set tab off;
set linesize 300;
set pagesize 1000;
set long 100;
set verify off;
set feedback off;
--spool check_dg_tmp.out;
prompt
prompt
prompt *********************************
prompt showing ..... DB information
prompt *********************************

column host_name format a20
column  "STARTUP_TIME" format a25
select name Database,INSTANCE_NAME,OPEN_MODE,DATABASE_STATUS,database_role,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP_TIME" from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... Standby applying status
prompt *********************************

select process,status,sequence#,thread#,BLOCK#,BLOCKS,pid from gv$managed_standby order by 2 desc;

prompt
prompt
prompt *********************************
prompt showing ..... DG status
prompt *********************************
select INSTANCE_NAME,database_role, switchover_status,PROTECTION_MODE,PROTECTION_LEVEL , FLASHBACK_ON,force_logging "FORCE_LOGGING"  from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... archive log destination (location and roles, 1 - 6)
prompt *********************************
column dest_name format a19
column destination format a22
column db_unique_name format a12
column gap_status format a15
column dest_id format 9999
select a.DEST_ID, a.dest_name, a.type, a.STATUS, a.DESTINATION,  a.VALID_TYPE, a.VALID_ROLE, a.db_unique_name, b.gap_status
FROM V$ARCHIVE_DEST a,  V$ARCHIVE_DEST_STATUS b WHERE a.dest_id=b.dest_id and a.DESTINATION is not null;


prompt
prompt
prompt *********************************
prompt showing ..... archive log state (enable or defer)
prompt *********************************
show parameter log_archive_dest_state_1
show parameter log_archive_dest_state_2
show parameter log_archive_dest_state_3
show parameter log_archive_dest_state_4
show parameter log_archive_dest_state_5
show parameter log_archive_dest_state_6

prompt
prompt
prompt *********************************
prompt showing ..... archive log file format ( %t_%s_%r.arc)
prompt *********************************
show parameter log_archive_format

prompt
prompt
prompt *********************************
prompt showing ..... convert parameters (log and db_file)
prompt *********************************
show parameter convert

prompt
prompt
prompt *********************************
prompt showing ..... flashback retention
prompt *********************************
show parameter  db_flashback_retention_target

prompt
prompt
prompt *********************************
prompt showing ..... DB recover file dest and size
prompt *********************************
show parameter db_recovery

prompt
prompt
prompt *********************************
prompt showing ..... local listener and remote listener
prompt *********************************
show parameter remote_listener
show parameter local_listener

--spool off;
exit;

How External Table Works in StandBy Database

Standby database/Data Guard is highly used for reporting purposes. However, when you want to write some log data for your reporting purpose, you might encounter an issue that you are not allowed to do so on the standby instance.

In this case, using External Table is an option. You can create a stored procedure and use it to write os file for the external table.

Here is a sample code.

PROCEDURE wrt_transaction_log (p_job_id IN NUMBER,
p_programname IN VARCHAR2,
p_filename IN VARCHAR2,
p_full IN VARCHAR2,
p_feedstartts IN DATE,
p_feedendts IN DATE,
p_status IN VARCHAR2,
p_message IN VARCHAR2)
IS
v_output_file UTL_FILE.file_type;
filename VARCHAR2 (100);
v_string VARCHAR2 (2000);
nodata EXCEPTION;
BEGIN
EXECUTE IMMEDIATE ('ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD HH24:MI:SS''');

filename := 'at_genpdfile_pkg_log_ext.txt';
v_output_file := UTL_FILE.fopen ('XTABLES', filename, 'A');

v_string :=
at_genpdfile_pkg_log_seq.NEXTVAL
|| '|'
|| p_job_id
|| '|'
|| USER
|| '|'
|| p_programname
|| '|'
|| p_filename
|| '|'
|| p_full
|| '|'
|| p_feedstartts
|| '|'
|| p_feedendts
|| '|'
|| p_status
|| '|'
|| p_message
|| '|'
|| SYSDATE;
UTL_FILE.put_line (v_output_file, v_string);
UTL_FILE.fclose_all;
END;