What is Data Guard Role Management Services or What is DR Drill or How to Switch Over databases?

What is Data Guard Role Management Services or What is DR Drill or How to Switch Over databases?

What is Data Guard Role Management Services?

What is DR Drill?

How to Switch Over databases?

A database operates in one of the following mutually exclusive roles: primary or standby.

Data Guard enables you to change these roles dynamically by issuing the SQL statements or by using either of the Data Guard broker's interfaces.

Role Transitions

Ø  There are two types of role transition

Ø  Switchover

Ø  Planned failover to standby database

Ø  Original primary becomes new standby

Ø  Original standby becomes new primary

Ø  No data loss

Ø  Can switchback at any time

Ø  Failover

Ø  Unplanned failover to standby database

Ø  Original standby becomes new primary

Ø  Original primary may need to be rebuilt

Ø  Possible data loss

Switchover

Failover

Practical Exercise SWITCHOVER:

A switchover takes place in two phases.

Ø  In the first phase, the existing primary database undergoes a transition to a standby role.

Ø  In the second phase, a standby database undergoes a transition to the primary role.

Switchover - Physical Standby Databases

Ø  Best method to use when switching roles

–      Primary to Standby, Standby to Primary

Ø  Must be done in order

–      Primary first, On Standby Second

Ø  Leaves Primary usable as a standby

–      This method prevents the redo log files on the primary from being reset.

Ø  Leaves all other standby databases up and ready

–      Allows the remaining standby sites to receive redo data from a ‘new’ primary site

Ø  Switchback is the reverse of this process.

Steps for Switchover

Step 1 - Switchover Primary to a Physical Standby

Step 2 - Start the New Standby Database

Step 3 - Switchover the standby to a Primary

Step 4 - Finish up the new primary and new standby

Step 5 - Switch them back! If Required

Primary Database Switchover

Ø  Switch the physical primary to a standby.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

v  Needs exclusive access & connectivity to standby

v  Archives Current Redo Log

v  Adds an End of Redo (EOR) marker to final archive log

v  Ships all logs to the standby sites

v  Backs up the current control file to trace

v  Converts the control file to a standby control file

v  Dismounts Primary database instance

    Ø  Must now be re-started as a standby database

v  Can be target of a switchover to primary

Step 1 - Switchover “Primary”

Getting rid of user sessions!

Specify “WITH SESSION SHUTDOWN” to ensure all sessions are shutdown prior to switchover

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBYWITH SESSION SHUTDOWN;

Step 2 - Start the New Standby Database

Ø  Restart the old primary as a standby (9i)

SQL> SHUTDOWN

SQL> STARTUP NOMOUNT PFILE=...

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Ø  Restart the old primary as a standby (10g)

SQL> SHUTDOWN

SQL> STARTUP MOUNT;

Ø  Restart the old primary as a standby (11g)

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

Ø  Restart the old primary as a standby (12c)

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

Step 3 - Standby Database Switchover

Ø  Switch one of the standby databases to a primary.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

ü  Requires that the primary was switched first.

•       And the action has completed!

ü  Needs exclusive access - Only one instance running

•       Closes the database if open for Read Only access

ü  Applies all log files through the last primary archive log

ü  Converts the Standby control to a current control file

ü  Database mounted as primary.

ü  Database must now be started as a primary

•       Standby sites now receive logs from this ‘primary’ site.

Step 4 - Switchover Final Steps

Ø  On the New Primary (the original Standby)

% sqlplus “SYS/CHANGE_ON_INSTALL AS SYSDBA”

SQL> SHUTDOWN

SQL> STARTUP

Ø  Open the Standby databse.

SQL> ALTER DATABASE OPEN;

Ø  On the New Standby (the original Primary)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Preparation for a Role Transition (Switchover)

Ø  Verify that each database is properly configured for the role that it is about to assume.

Ø  Verify that there are no redo transport errors or redo gaps at the standby database .

Ø  Ensure temporary files exist on the standby database that match the temporary files on the primary database.

Ø  Before performing a switchover from an Oracle RAC primary database to aphysical standby database, shut down all but one primary database instance. Anyprimary database instances shut down at this time can be started after theswitchover completes.

Ø  Clear Potential Blocking Parameters & Jobs

Ø  Turn on Data Guard tracing on primary and standby

SQL> ALTER SYSTEM SET log_archive_trace=8191;

On Primary

Ø  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

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 the Primary is a RAC, then shutdown all secondary primary instances

Ø  Switchover the primary to a standby database

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

In the primary alert log you will see messages like these:

Switchover: Primary controlfile converted to standby controlfilesuccesfully.
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

On Standby:

Ø  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:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
-----------------
TO PRIMARY

Ø  Switchover the standby database to a primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSIONSUTDOWN;

Ø  Open the new primary database

    SQL> ALTER DATABASE OPEN;

Ø  Restart the new standby

    SQL> SHUTDOWN ABORT;

    SQL> STARTUP MOUNT;

Ø  Start MRP

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

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

Till 11g………

Ø  Step 1 Verify that the primary database can be switched to the standby role.

Ø  Step 2 Initiate the switchover on the primary database.

Ø  Step 3 Shut down and then mount the former primary database.

Ø  Step 4 Verify that the switchover target is ready to be switched to the primary role.

Ø  Step 5 Switch the target physical standby database role to the primary role.

Ø  Step 6 Open the new primary database. 

Ø  Step 7 Start Redo Apply on the new physical standby database.

12c

Ø  Step 1 Verify that the target standby database is ready for switchover.

Ø  Step 2 Initiate the switchover on the primary database.

Ø  Step 3 Open the new primary database.

Ø  Step 4 Mount the new physical standby database.

Ø  Step 5 Start Redo Apply on the new physical standby database.

Step 1 Verify that the target standby database is ready for switchover.

            SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;

SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;

ERROR at line 1:

ORA-16470: Redo Apply is not running on switchover target

SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;

ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details

SQL> ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;

Database altered.

Step 2 Initiate the switchover on the primary database.

            SQL> ALTER DATABASE SWITCHOVER TO CHICAGO;

Database altered.

Step 3 Open the new primary database.

           Issue the following SQL statement on the new primary database CHICAGO:

           SQL> ALTER DATABASE OPEN;

Step 4 Mount the new physical standby database.

           Issue the following SQL statement on the new standby database BOSTON:

           SQL> STARTUP MOUNT;

Or, if BOSTON is an Oracle Active Data Guard physical standby database, then issue thefollowingSQLstatement to open it read only:

             SQL> STARTUP;

Step 5 Start Redo Apply on the new physical standby database.

              SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Thank you...........

Regards,

Malliik