24^th SEP 2014

LOG SHIPPING CHECK & MANUAL SWITCHOVER PHYSICAL STANDBY

On Primary

Execute below command to enable log shipping to standby

sql > alter system set log_archive_dest_state_2=ENABLE scope=both

Check the status of Standby archiving destination

sql > select status,error from v$archive_dest where dest_id=2;

Note :-- The status should return -- valid . If it return error , then check connectivity between primary and standby

Physical Standby

On the standby database execuet the following command to start Managed Recovery Process (MRP) . The command is executed on Mount stage

SQL > alter database recover managed standby database disconnect from session;

Check log applied process

SQL > select name,applied,archived from v$archived_log;

SQL > select max(sequence#) from v$archived_log where applied='YES';

Role Transition

Oracle Data Guard supports two role transition operations:

switchover:

switchover allows the primary database to switch roles with one of its standby databases.There is no data loss during a switchover.After a switchover,each database continues to participate in the data guard configuration with its new role

Failover:

Failover transitions a standby database to the primary role in response to a primary database failure. If the primary database was not opening in either maximum protection mode or maximum availability mode before the failure,some data loss may occur. After a failover, the failed database no longer participates in the Data Guard configuration.It needs to be reinstated to become an active part of Data Guard configuration

Manual Switchover

On the primary Database (open stage)

Query v$database to check the role of Primary Database

sql > select database_role from v$database;

It will return primary

Now check the switchover status of the primary database

sql > select switchover_status from v$database

It will return "SESSIONS ACTIVE"

Now at this point database is ready for manual switchover

execute the below command

sql > alter database commit to switchover to physical standby with session shutdown;

Now your primary database has become physical standby.

Query the database_role column of v$database . It will return "PHYSICAL STANDBY"

Shutdown this new physical standby database and start in mount stage

On the PHYSICAL STANDBY Database (Mount Stage)

Query to check the database role

sql > select database_role from v$database;

It will return "PHYSICAL STANDBY"

Now check the switchover status of the standby database

sql > select switchover_status from v$database;

It will return "SESSIONS ACTIVE"

Cancel the MRP Process which is running in the background

sql > alter database recover managed standby database cancel;

Now you are ready to perform a manual switchover from physical standby to primary

Execute the below command using "SWITCH SESSION SHUTDOWN"

sql > alter database commit to switchover to primary with session shutdown;

Now your physical standby database has become primary .

Query database_role column of v$database . It will return "PRIMARY"

shutdown the database and again start in open stage.