Differences

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

--- oracledataguard [2016/03/13 12:32] – [SQL Scripts] z0hpvk
+++ 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 41: / Line 42: @@
 Primary/Standby
-<code>SELECT THREAD# THREAD, MAX(SEQUENCE#) SEQUENCE FROM GV$ARCHIVED_LOG
+<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)
@@ Line 48: / Line 50: @@
 Standby
-<code>col name format a16
+<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';</code>
+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 ====