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:
- You already have working Data Guard setup between primary and standby
- Archive redo is begin shipped and applied from primary to standby
- Archive redo application is up to date between primary & standby
Requirements :
- All applications must be shutdown
- Switchover must be initiated on primary database before being completed on standby database
- 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
Leave a Reply
You must be logged in to post a comment.
[…] posts http://www.oracledbasupport.co.uk/enable-archivelog-put-database-into-archive-mode/ & […]