Switchover and Switchback are very important mechanisms to validate the integrity of Oracle Data Guard configuration. Before you commit to Switchover/Switchback tests, don't forget to make sure that primary and secondary databases are in synch. Switchover and switchback are planned events. Oracle ships End Of Redo marker to the standby database to inform the standby about impending switchover event. Below is the log of such a test that I conducted.
Switchover to Physical Standby
Step 0 - Make sure that primary and standby databases are in sync
Step 1 - Verify that the Primary Database can be switched over to the Physical Standby
Step 0 - Make sure that primary and standby databases are in sync
Primary and standby database must be in synch before primary database can be switched over to the physical standby.
Query the
SWITCHOVER_STATUS column of the V$DATABASE view on the primary database.For example:SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
A value of TO STANDBY or SESSIONS ACTIVE
indicates that the primary database can be switched to the standby role.
If neither of these values is returned, a switchover is not possible
because redo transport is either misconfigured or is not functioning
properly.
Step 2 - On Primary (DGP) - Make it a Physical Standby Database
DGP> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ;
Database altered.
DGP> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
DGP> startup mount ;
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 1298288 bytes
Variable Size 171966608 bytes
Database Buffers 452984832 bytes
Redo Buffers 2895872 bytes
Database mounted.
DGP> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
Step 2 - On Secondary (DGS) - Make it a New Primary Database
DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
DGS> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
DGS> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
DGS> startup ;
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1298144 bytes
Variable Size 163578144 bytes
Database Buffers 436207616 bytes
Redo Buffers 2895872 bytes
Database mounted.
Database opened.
Step 3 - On Old Primary (DGP) - Make it a new Standby DB
DGP> alter database recover managed standby database using current logfile disconnect ;
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-38500: USING CURRENT LOGFILE option not available without standby redo log files
DGP> alter database recover managed standby database disconnect ;
Database altered.
Step 4 - On New Primary (DGS) - Perform Switch Logs
DGS> alter system switch logfile ;
System altered.
DGS> alter system switch logfile ;
System altered.
DGS> select max(tstamp) from jmehta.dg_sync_status ;
MAX(TSTAMP)
---------------------------------------------------------------------------
02-MAR-11 03.39.37.096000 PM
Switchback to the old Primary
Step 1 - On New Primary (DGS) : Switchback to Old Primary (DGP)
Step 1 - On New Primary (DGS) : Switchback to Old Primary (DGP)
DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
DGS> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
DGS>
DGS> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ;
Database altered.
DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SELECT SWITCHOVER_STATUS FROM V$DATABASE
*
ERROR at line 1:
ORA-01507: database not mounted
DGS> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
DGS> startup mount ;
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1298144 bytes
Variable Size 163578144 bytes
Database Buffers 436207616 bytes
Redo Buffers 2895872 bytes
Database mounted.
DGS> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
Step 2 - On Old Primary (DGP) - Make it Primary
DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 8
ORA-16139: media recovery required
DGP> alter database close ;
Database altered.
DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
DGP> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING
DGP> select switchover_status from v$database ;
DGP> alter database recover managed standby database finish ;
Database altered.
DGP> select switchover_status from v$database ;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
DGP> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
DGP> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
DGP> startup
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 1298288 bytes
Variable Size 171966608 bytes
Database Buffers 452984832 bytes
Redo Buffers 2895872 bytes
Database mounted.
Database opened.
DGP>
Step 3 - On Secondary (DGS) - Activate Recovery
DGS> alter database recover managed standby database using current logfile disconnect ;
No comments:
Post a Comment