8 Commonly Used SQL Commands

1. Manual Register Archive File
alter database register logfile '<ARCHIVELOG_PATH>';

2. Archive Log Applied or Not
select sequence#, archived, applied from v$archived_log order by sequence#;

select thread#,max(sequence#) from v$archived_log group bythread#;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;

select max(sequence#) from v$archived_log where applied='YES';

select process,status,sequence# from v$managed_standby;

3. Switch Log File
alter system switch logfile;

4. STANDBY_FILE_MANAGEMENT
show parameter standby_file_management

alter system set standby_file_management=auto;

5. Create Tablespace
create tablespace dummy datafile size 10m;

6. Real Time Archive Log Apply
alter database recover managed standby database using current logfile disconnect;

select recovery_mode from v$archive_dest_status where dest_id = 2;

7. Monitor Redo
select process, client_process, thread#, sequence#, status from v$managed_standby;

select * from v$dataguard_stats;

select distinct s.PROCESS,s.STATUS,s.thread#,v.machine,v.wait_class,v.WAIT_TIME_MICRO, v.event from gvw_session v  LEFT JOIN gv$process vp on v.paddr = vp.addr and v.inst_id=vp.inst_id join V$MANAGED_STANDBY s on s.pid=vp.spid where thread#>0 order by s.thread#,1;

8. Sync status Check
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#)
"Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE#
FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;