Switchover from primary to standby/ Switchover Primary : No Dgmgrl

This process will reverse database roles in a Data Guard setup, i.e. standby database becomes primary.

It’s assumed that:

1. You already have working Data Guard setup between primary and standby
2. Archive redo is begin shipped and applied from primary to standby
3. Archive redo application is up to date between primary & standby

Requirements :

1. All applications must be shutdown
2. Switchover must be initiated on primary database before being completed on standby database
3. The primary database must be open, the standby database must be mounted and in managed recovery mode

Note
When you perform a switchover the controlfile type is converted in-place, i.e. the primary controlfile becomes a standby controlfile and vice-versa. You must be CAREFUL which database you are working on…..

On the STANDBY database

1. Check archive_lag_target

SQL> show parameters lag

NAME TYPE VALUE ———————————— ———– —————————-
archive_lag_target integer 0

If non-zero set to zero using

SQL> alter system set archive_lag_target=0 scope=both;

On the PRIMARY database

1. Shutdown RAC databases

(RAC environment only) Shutdown all but one of the primary database instances.

2. Verify it is possible to switchover

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

Switchover requires that there be only one active session (the one performing the switchover).

Documentation states the above query should return “TO STANDBY” for switchover to succeed but it – always returns “SESSIONS ACTIVE” because of the one active session – Problemo!!

The key point is to make sure there is only one user connected – SYS – check V$SESSION

3. Initiate the switchover

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

Database altered.

This converts the primary database to standby. Current controlfile is backed up to trace should you need to reverse the switchover. At this stage we temporarily have two standby databases…

4. Shutdown, restart and mount as standby

SQL> SHUTDOWN NORMAL;

ORA-01507: database not mounted <= This can be ignored

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 920912008 bytes
Fixed Size 711896 bytes
Variable Size 285212672 bytes
Database Buffers 111554492 bytes
Redo Buffers 811008 bytes

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

5. Verify switchover status

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

According to documentation this should return “SWITCHOVER PENDING”, same catch as before, there has to be one session active to do the switchover.

6. Verify switchover status

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

——————

SESSIONS ACTIVE

According to the documentation this should return “SWITCHOVER PENDING” but I have only ever seen “SESSIONS ACTIVE”, same situation as before – there has to be one session active to do the switchover.

On the STANDBY database

6. Switch standby database to primary

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;

Database altered.

7. Shutdown and restart the new primary database

SQL> SHUTDOWN

ORA-01507: database not mounted <= This can be ignored

ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 920912008 bytes
Fixed Size 711896 bytes
Variable Size 285212672 bytes
Database Buffers 111554492 bytes

Redo Buffers 811008 bytes
Database mounted.
Database opened.

The database is now the primary database.

On the STANDBY database (the original primary)

8. Start managed recovery and log apply services and set archive lag target

SQL> ALTER SYSTEM SET ARCHIVE_LAG_TARGET=900 SCOPE=BOTH;

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

Database altered.

On the PRIMARY database (the original standby)

9. Begin archiving logs

SQL> ALTER SYSTEM ARCHIVE LOG START;

System altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

On BOTH databases

10. Check the alert logs to make sure archived redo is being sent and received

The primary alert log should look like this

ARC1: Evaluating archive log 3 thread 1 sequence 30

ARC1: Archive destination LOG_ARCHIVE_DEST_2: Previously completed

ARC1: Beginning to archive log 3 thread 1 sequence 30

Creating archive destination LOG_ARCHIVE_DEST_1: ‘/u01/app/oracle/archive/arch/DATAGD_1_30.arc’

ARC1: Completed archiving log 3 thread 1 sequence 30

The standby alert log should look like this

Media Recovery Waiting for thread 1 seq# 30 (in transit)

Fri Sep 3 10:12:14 2004

Media Recovery Log  ‘/u01/app/oracle/archive/arch/DATAGD_1_30.arc