LATEST TOPICS

Steps to manually switch over database roles

Login to the Primary Database and Switch the Logs
SQL>  alter system archive log current;
----
---- (Issue this command few times)

(In case of RAC database, shutdown all the nodes except the primary one. Perform log switch in primary node again)

Login to primary and check the max generated sequences
SQL>  select thread#,max(sequence#)
from v$archived_log group by thread# order by thread#;
Login to standby and check the applied sequences
SQL>  select thread#,max(sequence#)
from v$archived_log where applied='YES' group by thread#;

(Go to to the next step when all Standby Sequences = Primary Sequences or are different by  not more than 1)

Disable the transport of archives from the Primary Database
----
SQL>  alter system set log_archive_dest_state_2=’defer’ scope=both sid='*';
Check  the conversion status of the Standby database
SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS
-----------------
SESSIONS ACTIVE

During normal operations it is acceptable to see the following values for SWITCHOVER_STATUS on the primary to be SESSIONS ACTIVE or TO STANDBY. 

Convert the primary database to the new standby:

SQL>  alter database commit to switchover to standby with session shutdown;

Database altered.
-----
----- shut down the new standby database
SQL>  shut immediate; 

-----
----- Mount the new standby database
SQL>  startup mount;

Converting standby database to new  Primary  database

Stop managed recovery on old standby database
SQL>  alter database recover managed standby database cancel;

Database altered.
Check the conversion status
SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS
-----------------
NOT ALLOWED

During normal operations on the standby it is acceptable to see the values of NOT ALLOWED or SESSIONS ACTIVE.

Perform  Conversion  Step
SQL>  alter database commit to switchover to primary with session shutdown;

Database altered.
Check the status of Database Role to confirm the conversion from Standby to Primary
SQL>  select name,database_role,protection_mode from v$database;

NAME       DATABASE_ROLE PROTECTION_MODE
---------- ------------- ----------------
[db_name]  PRIMARY       MAXIMUM PERFORMANCE
Perform a clean shutdown and startup of database
SQL>  shutdown immediate;

SQL>  startup;
Enable the transport of archives from the New Primary Database
SQL>  alter system set log_archive_dest_state_2=’ENABLE’ scope=both sid='*';
Start managed recovery on the new standby database
SQL>  alter database recover managed standby database disconnect;

Media Recovery Complete.
Start remaining  instances in case of RAC database
srvctl start instance -d [database name] -i [instance_name]

 

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed