dgstatus.sql

show parameter name
set lines 200 pages 50000
col flashback_on for a3
col force_logging for a3
col supplemental_log_data_min for a3
col db_unique_name for a12
select dbid,name,db_unique_name,flashback_on,force_logging,supplemental_log_data_min,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUS,GUARD_STATUS,OPEN_MODE from gv$database;

set lines 160 pages 50000
select thread#,group#,bytes/1024/1024,blocksize,members,status from v$log order by 1,2;
select thread#,group#,bytes/1024/1024,blocksize,status from v$standby_log order by 1,2;
col member for a80
col group# for 999999
col status for a10
col type for a7
select * from v$logfile order by 1;

set linesize 200
set pagesize 999
col value for a30
col name for a30
col unit for a30
col time_computed for a30
col datum_time for a30
col status for a15
col last_change# for 99999999999999999999999
select group#,thread#,sequence#,status,used,archived,last_change# from v$standby_log;
select inst_id,process,status,client_process,thread#,sequence#,delay_mins from gv$managed_standby;
select * from v$dataguard_stats;

set linesize 200
set pagesize 999
col value for a30
col name for a30
col unit for a30
col time_computed for a30
col datum_time for a30
col status for a15
col last_change# for 99999999999999999999999
select group#,thread#,sequence#,status,used,archived,last_change# from v$standby_log where status='ACTIVE';
select inst_id,process,status,client_process,thread#,sequence#,delay_mins from gv$managed_standby where process like '%MRP%';
select name,value,unit,time_computed,datum_time from v$dataguard_stats;

column name format a20
column free_mb format 999,999,999
select name,to_char(sysdate,'YYYY-MM-DD HH24:MI') SYSTEM_DATETIME from v$database;
select (select name from v$database) db_name,name diskgroup_name,total_mb,free_mb,round(free_mb/total_mb*100,1) FREE_Uti_PCT from v$asm_diskgroup order by 4;

观察DG同步情况
DG库开启redo apply之后，需要从源库接收归档日志和redo log并不断应用，最后与源库数据接近同步。
源库端检查：

set lines 160 pages 50000
col value for a100
col name for a30
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2','log_archive_dest_3','log_archive_dest_state_1','log_archive_dest_state_2','log_archive_dest_state_3', 'remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','fal_client','db_file_name_convert','log_file_name_convert','standby_file_management','redo_transport_user','db_create_file_dest','db_flashback_retention_target','db_recovery_file_dest','db_recovery_file_dest_size','control_files','control_file_record_keep_time') order by 1;

set lines 200 pages 50000
select * from v$archive_dest_status where dest_id=3;
select sequence#, applied, to_char(first_time,'mm/dd/yy hh24:mi:ss') first from v$archived_log where dest_id=3 order by first_time;
select to_char(current_scn) from v$database;
select to_char(min(CHECKPOINT_CHANGE#)) from v$datafile_header;

检查alert log，确保发送日志正常
DG库端检查：
set lines 200 pages 50000
select * from v$archive_gap;
select to_char(current_scn) from v$database;
select to_char(min(CHECKPOINT_CHANGE#)) from v$datafile_header;

On Both
select protection_mode, protection_level from v$database;

On primary
SQL> select thread#, sequence#, status from v$log;

On standby
SQL> select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#;