Perform the following steps using the SQL*Plus connection for both the databases:
TO STANDBY
or SESSIONS ACTIVE
:SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
The previous output shows that the primary database is ready to switch to the standby database role. The SESSIONS ACTIVE
status indicates that some user sessions are still connected to the database. Such a case does not pose an obstacle for switchover. When output is SESSIONS ACTIVE
, you have to perform switchover using the keyword WITH SESSION SHUTDOWN
. This is so that those sessions will be terminated during the switchover.
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.
Wed Oct 10 16:12:26 2012 alter database commit to switchover to physical standby with session shutdown ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 23631] (TURKEY)
335
. After performing switchover, all the transactions will be written to the online redo logfiles and the log switch will be forced on the primary database.Wed Oct 10 16:12:30 2012 Archived Log entry 764 added for thread 1 sequence 336 ID 0x4e7c64e3 dest 1: ...... Waiting for potential switchover target to become synchronized... Wed Oct 10 16:12:47 2012 Active, synchronized Physical Standby switchover target has been
MRP
status on the standby database alert log:Wed Oct 10 16:12:47 2012 Media Recovery Log /u02/app/oracle/flash_recovery_area/INDIA_UN/archivelog/2012_10_10/o1_mf_1_337_87bn9793_.arc Media Recovery Waiting for thread 1 sequence 338
337
is also switched and applied on standby. Now all the processes will be terminated and the redo thread of each respective thread will be closed; no further log switches can be performed. At the end, EOR will be generated as follows:ARCH: End-Of-Redo Branch archival of thread 1 sequence 338 Archived Log entry 767 added for thread 1 sequence 338 ID 0x4e7c64e3 dest 1: ....... Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/turkey_un/TURKEY/trace/TURKEY_ora_23631.trc Archivelog for thread 1 sequence 338 required for standby recovery Switchover: Primary controlfile converted to standby controlfile succesfully.
338
from the primary database, as shown in the following code:SQL> select thread#,sequence#,END_OF_REDO,END_OF_REDO_TYPE from v$archived_log; THREAD# SEQUENCE# END END_OF_RED ---------- ---------- --- ---------- 1 337 NO 1 337 NO 1 338 YES SWITCHOVER
338
including EOR will be applied on the standby database (INDIA) as shown in the following code:Resetting standby activation ID 1316775139 (0x4e7c64e3) Media Recovery End-Of-Redo indicator encountered Media Recovery Applied until change 3085369 MRP0: Media Recovery Complete: End-Of-REDO (INDIA) MRP0: Background Media Recovery process shutdown (INDIA)
Wed Oct 10 16:12:58 2012 Switchover: Complete - Database shutdown required (TURKEY) Completed: alter database commit to switchover to physical standby with session shutdown
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- NOT ALLOWED SQL> / SWITCHOVER_STATUS -------------------- SWITCHOVER PENDING SQL> / SWITCHOVER_STATUS -------------------- TO PRIMARY
NOT ALLOWED
. After processing switchover from the primary database, during recovery the status will be changed to SWITCHOVER PENDING
. Once End-of-Redo
is applied on standby, the database will be ready to become primary as shown in the following code:SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
SWITCHOVER
command on the standby database as shown in the following code:SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Database altered.
On the alert logfile you will see the following:
Wed Oct 10 18:01:15 2012 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN ALTER DATABASE SWITCHOVER TO PRIMARY (INDIA) Maximum wait for role transition is 15 minutes. ............. SwitchOver after complete recovery through change 3085369 ............... Standby became primary SCN: 3085367 Switchover: Complete - Database mounted as primary
SQL> select CURRENT_SCN,STANDBY_BECAME_PRIMARY_SCN from v$database; CURRENT_SCN STANDBY_BECAME_PRIMARY_SCN ----------- -------------------------- 3156173 3085367
MOUNTED
as shown in the following code:SQL> select db_unique_name,database_role,open_mode from v$database; DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE -------------------- ---------------- -------------------- INDIA_UN PRIMARY MOUNTED
Open the database with the following statement:
SQL> alter database open; Database altered.
NOMOUNT
status.SQL> select status from v$instance; STATUS ------------ STARTED
Now perform a clean shutdown with SHUTDOWN IMMEDIATE
and then start up the new standby database in the READ ONLY
mode if Active Data Guard will be used. Then start Redo Apply on the standby database (TURKEY)
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
SQL> select db_unique_name,open_mode from v$database; DB_UNIQUE_NAME OPEN_MODE --------------- -------------------- turkey_un READ ONLY WITH APPLY
If you have multiple standby databases in the Data Guard configuration, start Redo Apply on each standby database.
Switchover can also be performed using the Data Guard broker. Managing switchover with the broker is very simple. In SQL*Plus, we have to manage commands from both the primary and standby databases. When using the broker, the SWITCHOVER
command is executed from either the primary or the standby database.