1. Verify the Physical Standby Database Is Performing Properly
Step 1 Identify the existing archived redo log files on standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
Step 2 Force a log switch to archive the current online redo log file on primary
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
Step 3 Verify the new redo data was archived on the standby database.
SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;
Step 4 Verify that received redo has been applied on standby
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
The value of the APPLIED column for the most recently received log file will be
either IN-MEMORY or YES if that log file has been applied.
2. Pre-Switchover Checks
Verify Managed Recovery is Running (non-broker) on the standby
SQL>select process from v$managed_standby where process like 'MRP%';
Cancel apply delay for the target standby using SQL
SQL>select delay_mins from v$managed_standby where process = 'MRP0';
if delay_mins >0 then
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
NODELAY
USING CURRENT LOGFILE
DISCONNECT FROM SESSION;
Verify there are no large GAPS.
On primary
SQL>SELECT THREAD#, SEQUENCE# FROM V$THREAD;
On the standby the following query should be no more than 1-2 less than the primary query result
SQL>SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG val, V$DATABASE vdb WHERE APPLIED = 'YES' AND val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE# GROUP BY THREAD#;
If there is a gap .. fix the gap
Verify Primary and Standby TEMP Files Match
SQL>select tmp.name FileName, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;
If there is no match then you will correct the temp tbs problem after switchover
Switchover
Clear Potential Blocking Parameters & Jobs
1.Capture current job state on the primary
SQL>select * from dba_jobs_running;
SQL>select owner,job_name, start_date, end_date, enabled from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS';
SQL>show parameter job_queue_processes -- and capture the value
Block further job submission
SQL>alter system set job_queue_processes=0 scope=both;
Monitor alert log for both primary and standby
3. Finally Switchover
Verify that the primary database can be switched to the standby role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
A value of TO STANDBY or SESSIONS ACTIVE (requires the WITH SESSION SHUTDOWN clause on the switchover command) indicates that the primary database can be switched to the standby role
Switchover the primary to a standby database
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
Verify that the standby database can be switched to the primary role
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
If the standby was open read-only then restart the standby
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
Switchover the standby database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Open the new primary database:
SQL> ALTER DATABASE OPEN;
Correct any tempfile mismatch
4. Restart the new standby
On the the new standby database (old production database), bring it to the mount state and start managed recovery.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Change the Log Archive Dest on New Primary to New Standby
Check that they are syncronized.
Perfom log switch on new primary
More info : http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_47.shtml
