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:
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#;
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:
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#;
No comments:
Post a Comment