Home » , , » Data Guard Switch Over With DGMGRL and SQL*PLUS

Data Guard Switch Over With DGMGRL and SQL*PLUS

Written By Srikrishna Murthy Annam on Wednesday, December 7, 2011 | 2:16 PM

In this article I am going to explain the step by step instructions to do switchover in standby setup with data guard broker configuration command line utility (DGMGRL) and with sql commands.
To present this article, I assume that I already have a physical standby setup and data guard broker is also configured to administer standby setup. Please follow my other articles on how to setup physical standby database and how to configure data guard broker configuration.
Before performing switchover, make sure your physical standby database is running with no large redo gaps and delay time is set to 0.
Verifying that there is no large redo gaps :
On Primary :
[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 26 10:37:36 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     409
Next log sequence to archive   411
Current log sequence           411
SQL>

On Standby :
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 26 10:38:11 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     409
Next log sequence to archive   0
Current log sequence           411
SQL>


Then do some log switches on primary to verify if they are transferred and applied immediately to the standby side or not.
On Primary :
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/orcl/
Oldest online log sequence     411
Next log sequence to archive   413
Current log sequence           413
SQL>

On Standby :
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/flash_recovery_area/sbyorcl/
Oldest online log sequence     411
Next log sequence to archive   0
Current log sequence           413
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

  SEQUENCE# APPLIED
---------- ---------

………
       409 YES
       410 YES
       411 YES
       412 IN-MEMORY

48 rows selected.
SQL>

SwitchOver With DGMGRL:
SwitchOver with DGMGRL is a very simple and single command. “switchover to ‘<DB_UNIQUE_NAME>’. When connecting to database using DGMGRL you need to use username and password instead of OS authentication. ( Don’t use connect / ). This is because during switchover operations the new standby database may not start due to OS authentication on the other server.
[oracle@dgaskmpri01 ~]$ dgmgrl DGMGRL for Linux: Version 11.2.0.2.0 - Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle Connected.
DGMGRL> show configuration;

Configuration - askm_config
  Protection Mode: MaxPerformance
  Databases:
    orcl    - Primary database
    sbyorcl - Physical standby database

Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> switchover to 'sbyorcl'; Performing switchover NOW, please wait...
New primary database "sbyorcl" is opening...
Operation requires shutdown of instance "orcl" on database "orcl"
Shutting down instance "orcl"...
ORACLE instance shut down.
Operation requires startup of instance "orcl" on database "orcl"
Starting instance "orcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "sbyorcl"
DGMGRL> switchover to 'orcl'; Performing switchover NOW, please wait...
New primary database "orcl" is opening...
Operation requires shutdown of instance "sbyorcl" on database "sbyorcl"
Shutting down instance "sbyorcl"...
ORACLE instance shut down.
Operation requires startup of instance "sbyorcl" on database "sbyorcl"
Starting instance "sbyorcl"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "orcl"
DGMGRL> exit
[oracle@dgaskmpri01 ~]$


SwithOver With SQLPLUS :
Switchover operation with sqlplus will be performed by executing sqlplus commands in the same sequence as shown below.
SQL> alter database commit to switchover to standby with session shutdown; => On Primary Database
SQL> alter database commit to switchover to primary with session shutdown; => On Standby Database
SQL> alter database open; => On Primary Database ( Old Standby )
SQL> shut abort => On Standby Database ( Old Primary )
SQL> startup mount => On Standby Database ( Old Primary )
SQL> alter database recover managed standby database using current logfile disconnect from session; => On Standby Database ( Old Primary )

On Primary :
[oracle@dgaskmpri01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 26 10:44:53 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          TO STANDBY

SQL> alter database commit to switchover to standby with session shutdown;
Database altered.
SQL>
On Standby :
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 26 10:45:21 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL>
On Primary ( Old Standby ) :
[oracle@dgaskmsby01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 26 10:45:21 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;
Database altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          FAILED DESTINATION

SQL>
On Standby ( Old Primary ) :
SQL> shut abort ORACLE instance shut down.
SQL> startup mount ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             390073016 bytes
Database Buffers           58720256 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

SQL>

Reference :  1305019.1 and  1304939.1
Hope It Helps
SRI
Share this article :

Related Articles By Category



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger