Validate Primary and standby for swichover.
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
On the target physical standby database capture the current delay value
SQL> SELECT DELAY_MINS
FROM V$MANAGED_STANDBY
WHERE PROCESS = 'MRP0';
On the target physical standby database turn off delay if > 0
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Ensure Online Redo Log Files on the Target Physical Standby have been cleared
On the target physical standby run the following query to determine if the online redo logs have not been cleared:
SQL> SELECT DISTINCT L.GROUP#
FROM V$LOG L, V$LOGFILE LF
WHERE L.GROUP# = LF.GROUP#
AND L.STATUS NOT IN (‘UNUSED’, ‘CLEARING’,’CLEARING_CURRENT’);
If the above query returns rows, on the target physical standby issue the following statement for each GROUP# returned:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP
;
Check for Previously Disabled Redo Threads
To determine if this situation exists, on your primary database, first run the following query to determine if there are any threads with a SEQUENCE# greater than 0:
SQL> SELECT THREAD#
FROM V$THREAD
WHERE SEQUENCE# > 0;
On the primary database, determine the current database redo branch:
SQL> SELECT TO_CHAR(RESETLOGS_CHANGE#)
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT';
Any of the threads identified by the first query are disabled provided there are no archive log or log history records in the control file of the target physical standby database on the current branch of redo on the primary.
To determine this, substitute the resetlogs_change# from the primary database (found in the second query) into the query below and execute it on the target physical standby database for each thread reported from the first query above.
SQL> SELECT 'CONDITION MET'
FROM SYS.DUAL
WHERE NOT EXISTS (SELECT 1
FROM V$ARCHIVED_LOG
WHERE THREAD# =
AND RESETLOGS_CHANGE# =
)
AND NOT EXISTS (SELECT 1
FROM V$LOG_HISTORY
WHERE THREAD# =
AND RESETLOGS_CHANGE# =
);
If this query returns a row for any of the threads, you have a disabled thread with a non-zero SEQUENCE# that can prevent a switchover from the primary database to the physical standby database. In this case, you must apply the latest patchset or use one of the following workarounds:
Check if the standby has ever been open read-only
On the target physical standby database run this query:
SQL> SELECT VALUE
FROM V$DATAGUARD_STATS
WHERE NAME='standby has been open';
Verify there are no large GAPS.
Identify the current sequence number for each thread on the primary database
SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;
Verify the target physical standby database has applied up to, but not including the logs from the primary query. 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
WHERE APPLIED = 'YES'
AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;
Verify Primary and Standby TEMP Files Match
On the standby for each temporary tablespace, verify that temporary files associated with that tablespace on the primary database also exist on the standby database. Temp files added after initial standby creation are not propagated to the standby. Run this query on both the primary and target physical standby databases and verify that they match.
SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE
FROM V$TEMPFILE TMP, V$TABLESPACE TS
WHERE TMP.TS#=TS.TS#;
If the queries do not match then you can correct the mismatch now or immediately after the open of the new primary.
To correct now: add or delete a tempfile now requires managed recovery to be stopped and the standby to be open read only. Opening the standby read-only will require a database close and open before becoming the new primary, see “Open the new primary database”.
To correct post-primary-open: see “Correct any tempfile mismatch” step of Switchover
Verify that there is no issue with V$LOG_HISTORY on the Standby
Determine threads that have been active at some point on the primary database:
SQL> SELECT THREAD#, SEQUENCE#
FROM V$THREAD
WHERE SEQUENCE# > 0;
Get the RESETLOGS_CHANGE# from the primary database:
SQL> SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT';
On the target physical standby database, get the maximum sequence numbers for each thread from V$LOG_HISTORY:
SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE#=< resetlogs_change# from the primary V$DATABASE_INCARNATION.RESETLOGS_CHANGE# >
GROUP BY THREAD#;
The last SEQUENCE# for each THREAD# from V$LOG_HISTORY on the target physical standby database should be close (the difference in log sequences < 3) to the SEQUENCE# for each THREAD# from V$THREAD on the primary database. If the difference in log sequences is greater than 3 or no row is returned for the thread, you have encountered this problem and should recreate the standby controlfile. See Note 459411.1. If backups are being done on the standby without an RMAN Catalog then backup history will be lost. It is highly recommended to use an RMAN Catalog for all backups.
Verify no old partial Standby Redo Logs on the Standby
Get the RESETLOGS_CHANGE# from the primary database:
SQL> SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT';
On the target physical standby database, identify any active standby redo logs (SRL’s)
SQL> SELECT GROUP#, THREAD#, SEQUENCE#
FROM V$STANDBY_LOG
WHERE STATUS = 'ACTIVE'
ORDER BY THREAD#,SEQUENCE#;
On the target physical standby database, identify maximum applied sequence number(s).
SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE#=< resetlogs_change# from the primary V$DATABASE_INCARNATION.RESETLOGS_CHANGE# >
GROUP BY THREAD#;
If there are any active SRL's that have a thread#/sequence# less than the thread#/sequence# returned from the V$LOG_HISTORY (meaning the recovery has progressed beyond the active SRL) query, clear them on the target physical standby.
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP
;
Swichover to DR.
Clear Potential Blocking Parameters & Jobs
Capture current job state on the primary
SQL> SELECT *
FROM DBA_JOBS_RUNNING; [depending on what the running job is, be ready to terminate]
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
Note: cron job candidates to be disabled among others: oracle text sync and optimizer, RMAN backups, application garbage collectors, application background agents.
Block further job submission
SQL> ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID='*';
SQL> EXECUTE DBMS_SCHEDULER.DISABLE(
);
Disable any cron jobs that may interfere.
Create Guaranteed Restore Points
Create a guaranteed restore point on the primary
Verify if flashback database is on or a guaranteed restore point already exists
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
If this query returns “YES” (flashback database is on) or “RESTORE POINT ONLY” (Flashback is on but one can only flashback to an existing guaranteed restore point) then proceed to creating the guaranteed restore point.
NOTE: Unless you have a backport for Bug 7568556, “ACTIVE APPLY RATE SEEN FROM 63MB/S TO 544KB/S AFTER RESTORE POINT ENABLED”, you should not have just a guaranteed restore point only (V$DATABASE.FLASHBACK_ON=”RESTORE POINT ONLY”) and ensure that flashback database is also on (V$DATABASE.FLASHBACK_ON=”YES”) when creating a guaranteed restore point.
If this query returns “NO” then you need to turn on flashback database before creating the guaranteed restore point. This requires the database to be mounted.
See Enabling Logging for Flashback Database for those steps.
Create the guaranteed restore point
SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
Create a guaranteed restore point on the standby
Verify if flashback database is on or a guaranteed restore point already exists
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
If this query returns “YES” (flashback database is on) or “RESTORE POINT ONLY” (Flashback is on but one can only flashback to an existing guaranteed restore point) then proceed to creating the guaranteed restore point.
If this query returns “NO” then you need to turn on flashback database before creating the guaranteed restore point. This requires being in the MOUNT state.
See Enabling Logging for Flashback Database for those steps.
Create the guaranteed restore point
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Verify that the primary database can be switched to the standby role
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database. For example:
SQL> SELECT SWITCHOVER_STATUS
FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
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. If neither of these values is returned, a switchover is not possible because redo transport is either mis-configured or is not functioning properly
Switchover the primary to a standby database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
If an ORA-16139 is encountered, as long as V$DATABASE.DATABASE_ROLE=’PHYSICAL STANDBY’, then you can proceed. A common case where this can occur is when there are a large number of data files, greater than 1,000, the apply of the EOR log will timeout.. Once managed recovery is started on the new standby it will recover.
If the role was not changed then you need to cancel the switchover and review the alert logs and trace files further.
Verify the standby has received the end-of-redo (EOR) log(s)
MRP0: Media Recovery Complete: End-Of-REDO (sfs_stby1)
Resetting standby activation ID 2821924805 (0xa83327c5)
Mon Nov 3 06:53:21 2008
MRP0: Background Media Recovery process shutdown (sfs_stby1)
Mon Nov 3 06:53:22 2008
SUCCESS: diskgroup REGR was dismounted
Mon Nov 3 06:53:22 2008
Switchover: Complete - Database shutdown required (sfs_stby1)
Mon Nov 3 06:53:22 2008
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN
And correspondingly in the standby alert log file you should see messages like this:
Mon Nov 3 06:53:17 2008
Media Recovery Log +REGR2/sfs/archivelog/2008_11_03/thread_1_seq_21.3819.669797593
Identified End-Of-Redo for thread 1 sequence 21
Mon Nov 3 06:53:17 2008
Media Recovery End-Of-Redo indicator encountered
Mon Nov 3 06:53:17 2008
Media Recovery Applied until change 8338654
Mon Nov 3 06:53:17 2008
MRP0: Media Recovery Complete: End-Of-REDO (sfs1)
Resetting standby activation ID 2821924805 (0xa83327c5)
Mon Nov 3 06:53:19 2008
MRP0: Background Media Recovery process shutdown (sfs1)
Verify that the standby database can be switched to the primary role
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database. For example:
SQL> SELECT SWITCHOVER_STATUS
FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO PRIMARY
A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that redo apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.
Switchover the standby database to a primary
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Open the new primary database:
SQL> ALTER DATABASE OPEN;
!!!!!!!!After Switchover need delete restore points on both databases!!!!!!
DROP RESTORE POINT SWITCHOVER_START_GRP;
