Common commands used in Physical Standby database

Mount Standby Database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Check Modes of Standby Database:
SQL> select open_mode, protection_mode, protection_level from v$database;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL
-------------------- -------------------- --------------------
MOUNTED              MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

Cancel Recovery:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check SCN:
SQL> SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

Archives applied upto which sequence:
SQL> select THREAD#,max(SEQUENCE#),APPLIED from v$archived_log where APPLIED='YES' group by thread#,applied;
SQL> select sequence#,applied from v$archived_log;

If manually need to apply any gap log:
SQL> alter database register or replace logfile '/ora_arch/arch_2_4470_718297972.arc';

Manually Recovery/Applying the Logs in the Archive Gap to the Standby Database:
SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;

To check if you are using Real-Time Apply:
SQL> SELECT DEST_ID, RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

Check Status of Processes:
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
RFS       IDLE
RFS       IDLE
RFS       IDLE
MRP0      WAIT_FOR_LOG     ------- It means Redo apply is not real-time
RFS       IDLE

To identify standby redo logs:
SQL> select * from v$standby_log;

Check members of standby redo log file:
SQL> select * from  v$logfile where type=’STANDBY’;

To add Standby Redo Log File Group to a Specific Group Number:
SQL> alter database add standby logfile group 4 (
‘/<full_path_for_srl>/log04a.dbf’,
‘/<full_path_for_srl>/log04b.dbf’
) size 50m;
Note:Standby Redo Logs should be same size as of Online Redo Logs.


On Primary Node:

Enable/Disable Archive Dest:
alter system set log_archive_dest_state_2=defer sid='*' scope=both;
alter system set log_archive_dest_state_2=enable sid='*' scope=both;

Check Archive Gap:
SQL> SELECT applied.thread#
,last_applied
,newest_log
,newest_log - last_applied gap
FROM (
SELECT thread#
,MAX( sequence# ) last_applied
FROM v$archived_log
WHERE applied='YES'
GROUP BY thread#
) applied,
(
SELECT thread#
,MAX( sequence# ) newest_log
FROM v$archived_log
WHERE applied='NO'
GROUP BY thread#
) newest
WHERE applied.thread# = newest.thread#;

To identify the logs in the archive gap
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT
FROM V$ARCHIVED_LOG R, V$LOG L WHERE R.DEST_ID=2 AND L.ARCHIVED='YES';

LAST_SEQ_RECD LAST_SEQ_SENT
------------- -------------
15147         15147

To check the Current log sequence
select THREAD#,max(SEQUENCE#) from gv$log where THREAD# in (1,2) group by THREAD#;