• Arun Kumar

Perform Manual Switchover on Physical Standby

Connect to proddb database via client: Take a new putty session and connect to proddb database via client machine. Keep querying below

sqlplus sys/sys@proddb as sysdba

SQL> select name, open_mode, db_unique_name, database_role from v$database;

NAME      OPEN_MODE            DB_UNIQUE_NAME                 DATABASE_ROLE
--------- -------------------- ------------------------------ --------------
proddb    READ WRITE           proddb                         PRIMARY

Check primary and standby for any gaps

On primary:
===========
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;

On standby:
===========
SQL> select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;

Convert primary to standby: We will first convert primary to standby and later standby to primary

On primary:
===========
SQL> select SWITCHOVER_STATUS from V$DATABASE;

You must see TO STANDBY or SESSIONS ACTIVE

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

SQL> startup mount;

Execute query on client: At this stage, there is no primary to accept queries from client. Run below query on client putty terminal. The query will hang and wait until standby is converted to primary

SQL> select name, open_mode, db_unique_name, database_role from v$database;

Convert standby to primary: Our primary is already converted to standby. Now it’s time to convert original standby into primary

SQL> select SWITCHOVER_STATUS from V$DATABASE;
SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;

Check client query: Check the query you executed in step 4 on client, it must get executed


On new standby – Initially your primary database: Start MRP

SQL> alter database recover managed standby database disconnect;

Revert back: Once again follow the above process from top and re-execute steps in proper databases to revert back to original setup.

Recent Posts

See All

How to check database backups

Checking Database backups are one of the main focus areas of a DBA. Time to time, DBA needs to check database backup status and see if its completed, failed, running etc. Also, DBA must be able to get

Automate RMAN Backups using Shell Scripts

In a real environment, off course you will not manually trigger all the Oracle database backups. You need an automated mechanism to trigger RMAN backups. In this article we will look at RMAN backup au

Oracle RMAN Cold Backup

It’s easy to fire RMAN backups when database is running in archive log mode. RMAN will take your database backup along with archive logs. You can even use RMAN to take backup of a database running in

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback