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
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
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.