Differences

This shows you the differences between two versions of the page.

--- oracledataguard [2015/07/08 14:57] – created 131.111.27.50
+++ oracledataguard [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 4: / Line 4: @@
 <code>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
+ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 240 DISCONNECT FROM SESSION;
 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;</code>
@@ Line 24: / Line 25: @@
 <code>ALTER SYSTEM SWITCH LOGFILE;</code>
 Transport and Apply Lag should now always be 0 seconds.
+==== SQL Scripts ====
+Primary
+<code>-- Check Specific Standby error for log_archive_dest_2
+set linesize 170
+col destination format a90
+col error format a20
+SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;
+-- Check Standby Errors for log_archive_dest_n
+set linesize 170
+col destination format a90
+col error format a20
+SELECT DEST_ID, DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE TARGET = 'STANDBY';</code>
+Primary/Standby
+<code>-- Compare Archived Logs on Primary and Standby
+SELECT THREAD# THREAD, MAX(SEQUENCE#) SEQUENCE FROM GV$ARCHIVED_LOG
+WHERE ARCHIVED = 'YES'
+AND RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM GV$ARCHIVED_LOG)
+GROUP BY THREAD#
+ORDER BY THREAD#;</code>
+Standby
+<code> -- Check Apply and Transport Lag
+col name format a16
+col value format a16
+col time_computed format a30
+SELECT name, value, time_computed FROM V$DATAGUARD_STATS WHERE name like '%lag';
+-- Media Recovery Information
+alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
+set linesize 160
+col timestamp format a20
+col facility format a24
+col severity format a13
+col message format a100 trunc
+select timestamp, facility, severity, message
+from v$dataguard_status
+order by timestamp;
+-- Check if Real Time Apply is Enabled
+col DEST_NAME format a25
+select DEST_ID,dest_name,status,type,srl,recovery_mode
+from v$archive_dest_status
+where status <> 'INACTIVE';</code>
+==== Switchover ====
+If the database you are using RAC and the database version is 11gR2 or less then you need to shut down all database instances apart from the first one before doing a switchover.\\
+This is not neccessary any more in 12c.
+The example below assumes a 2 Node RAC system using 10gR2.\\
+Primary Database: DBPRIM\\
+Standby Database: DBSTBY\\
+<code>PRIMARY: alter system set log_archive_trace=8191;
+STANDBY: alter system set log_archive_trace=8191;
+PRIMARY: srvctl modify database -d DBPRIM -s mount
+STANDBY: srvctl modify database -d DBSTBY -s open
+PRIMARY: select switchover_status from v$database; (Value should say TO_STANDBY or SESSIONS_ACTIVE)
+PRIMARY: srvctl stop instance -d DBPRIM -i DBPRIM2
+PRIMARY: alter system archive log current;
+STANDBY: srvctl stop instance -d DBSTBY -i DBSTBY2
+PRIMARY: alter database commit to switchover to physical standby with session shutdown;
+STANDBY: select switchover_status from v$database; (Value should say TO_PRIMARY or SESSIONS_ACTIVE)
+STANDBY: alter database commit to switchover to primary;
+STANDBY: alter database open;
+STANDBY: srvctl start instance -d DBSTBY -i DBSTBY2
+PRIMARY: shutdown immediate;
+PRIMARY: srvctl start database -d DBPRIM
+PRIMARY: alter database recover managed standby database using current logfile disconnect;
+STANDBY: srvctl modify database -d DBSTBY -r primary
+PRIMARY: srvctl modify database -d DBPRIM -r physical_standby
+PRIMARY: alter system set log_archive_trace=0;
+STANDBY: alter system set log_archive_trace=0;</code>
+==== Failover ====
+https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB4773
+<code>
+SELECT STATUS, INSTANCE_NAME, DATABASE_ROLE from V$DATABASE, V$INSTANCE;
+ALTER DATABASE RECOVER MANAGED DATABASE CANCEL;
+ALTER DATABASE RECOVER MANAGED DATABASE FINISH;
+ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
+ALTER DATABASE ACTIVATE STANDBY DATABASE;
+ALTER DATABASE OPEN;
+SELECT STATUS, INSTANCE_NAME, DATABASE_ROLE from V$DATABASE, V$INSTANCE;
+</code>
 ==== Active Data Guard ====