DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, March 29, 2016

STEPS FOR RAC SWITCHOVER - PHYSICAL STANDBY

PRE SWITCHOVER  CHECKS:

1) Ensure LOG_ARCHIVE_CONFIG & DG_CONFIG parameters are established in primary database.

2)  Verify the physical standby database performing properly.

3)On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
118      27-MAY-13 27-MAY-13
119      27-MAY-13 28-MAY-13
120      28-MAY-13 28-MAY-13

DO a log switch - alter system switch logfile;

System altered.

Then Verify redo received on Standby and applied................

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
 119        YES
 120        NO
 120        YES
 121        NO
 121        YES
 122        NO
 122        YES
 123        NO
 123        NO

 Then Verify Managed Recovery is running on the standby...........

 SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

 4) Verify that recovery is running with “REAL TIME APPLY”  option

 ELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY

If managed standby recovery is not running or not started with real-time apply,    
restart managed recovery with  Real-time apply enabled Using any of the below command

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

5)Verify there are no large Gaps Identify the current sequence number for each thread on the primary database
               
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 within 1 or 2 of 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#;

6)Then Verify Primary and Standby tempfiles match and all datafiles are ONLINE
For each temporary tablespace on the standby, verify that temporary files associated
with that tablespace on the primary database also exist on the standby database.
Tempfiles 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 database.Prior to switchover, on the target standby, verify that all datafiles necessary for updates after
role transition to primary are ONLINE.

On the target standby:

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;

If there are any OFFLINE datafiles, and these are needed after switchover, bring them ONLINE:

SQL> ALTER DATABASE DATAFILE ‘datafile-name’ ONLINE;

7)Check if any jobs are running....IN PROD

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

Note: 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=’*’;

Disable any jobs that may interfere.      

SQL> EXECUTE DBMS_SCHEDULER.DISABLE( );

-----------------------------------------------------------------------------------------------------------------

 SWITCHOVER STEPS:

1)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:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
 -----------------
   TO STANDBY
 
The result of the query should be as below
A value of TO STANDBY or SESSIONS ACTIVE (which 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  

2)If The Primary is a RAC, then shutdown all secondary primary instances.

Shutdown all the Primary Instances and Start only one Instance

srvctl stop database -d databasename

Start only one Instance

Startup (Make RAC to Stand-alone)

select * from v$active_instances; (1 Instance Up and Running)

Switchover the primary to a standby database

 SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

 If an ORA-16139 error 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. Once managed recovery is started on the new standby, the database will recover.

 NOTE: If the role was not changed then you need to cancel the switchover and review the alert logs and trace files further.


 3)Verify the standby has received the end-of-redo (EOR) logs
In the primary alert log you will see messages like these:

Switchover: Primary controlfile converted to standby controlfile succesfully.
       Tue Mar 15 16:12:15 2011
 MRP0 started with pid=17, OS id=2717
MRP0: Background Managed Standby Recovery process started (SFO)
 Serial Media Recovery started
 Managed Standby Recovery not using Real Time Apply
 Online logfile pre-clearing operation disabled by switchover
 Media Recovery Log /u01/app/flash_recovery_area/SFO/archivelog/2011_03_15/o1_mf_1_133_6qzl0yvd_.arc
 Identified End-Of-Redo for thread 1 sequence 133
       Resetting standby activation ID 0 (0x0)
 Media Recovery End-Of-Redo indicator encountered
       Media Recovery Applied until change 4314801
       MRP0: Media Recovery Complete: End-Of-REDO (SFO)
       MRP0: Background Media Recovery process shutdown (SFO)
       Tue Mar 15 16:12:21 2011
 Switchover: Complete - Database shutdown required (SFO)
 Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN

And correspondingly in the standby alert log file you should see messages like these:
                Tue Mar 15 16:12:15 2011
                RFS[8]: Assigned to RFS process 2715
                RFS[8]: Identified database type as 'physical standby': Client is Foreground pid 2568
                Media Recovery Log /u01/app/flash_recovery_area/NYC/archivelog/2011_03_15/o1_mf_1_133_6qzl0yjp_.arc
                Identified End-Of-Redo for thread 1 sequence 133
                Resetting standby activation ID 2680651518 (0x9fc77efe)
 Media Recovery End-Of-Redo indicator encountered
                Media Recovery Continuing
                Resetting standby activation ID 2680651518 (0x9fc77efe)
Media Recovery Waiting for thread 1 sequence 134

4)Verify that the standby database can be switched to the primary role

SELECT SWITCHOVER_STATUS FROM V$DATABASE;
       SWITCHOVER_STATUS
       -----------------
       TO PRIMARY

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;  
 
6)In the standby alert log file you should see messages like these:


Tue Mar 15 16:16:44 2011
                ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN
                ALTER DATABASE SWITCHOVER TO PRIMARY (NYC)
 Maximum wait for role transition is 15 minutes.
 Switchover: Media recovery is still active
                Role Change: Canceling MRP - no more redo to apply
                Tue Mar 15 16:16:45 2011
                MRP0: Background Media Recovery cancelled with status 16037
                Errors in file /u01/app/diag/rdbms/nyc/NYC/trace/NYC_pr00_2467.trc:
                ORA-16037: user requested cancel of managed recovery operation
 Managed Standby Recovery not using Real Time Apply
                Recovery interrupted!
 Waiting for MRP0 pid 2460 to terminate
 Errors in file /u01/app/diag/rdbms/nyc/NYC/trace/NYC_pr00_2467.trc:
 ORA-16037: user requested cancel of managed recovery operation
                Tue Mar 15 16:16:45 2011
 MRP0: Background Media Recovery process shutdown (NYC)
 Role Change: Canceled MRP

 Open the new primary database    ---- For  RAC  setup  also open the database on second node.

  ALTER DATABASE OPEN; ( Shutdown & Startup )

7) Correct any tempfile mismatch
If there was a tempfile that was not corrected during the pre-switchover check, then correct it now on the new primary.


Restart the new standby
 If the new standby database (former primary database) was not shutdown since switching it to standby,
 bring it to the mount state and start managed recovery. This can be done in parallel to the new primary open.

 SQL> STARTUP MOUNT;

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

 Note: If you were using a delay for your standby then you would restart the apply without real time apply:

 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

 Finally, if the database is a RAC, then start all secondary instances on the both new standby and current primary.

ONCE THE SWITCHOVER IS SUCCESSFULL RESTORE ANY JOBS TAKEN DOWN AND DISABLE TRACE IF ENABLED.
  

No comments: