Tag Archives: Switchover with DG Broker

Switchover with Broker

Performing Switchover with DG Broker

Overview:

A switchover is a role reversal between the primary database and one of its standby  databases.  A switchover operation guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role and the standby database transitions to the primary role. The transition occurs without having to recreate either database.

0. Enviroment
1. Pre-Switchover tasks
2. Verify TNS Entries
3. Steps to perform switchover with Physical Standby databases
4. Switch back to old primary


0. Enviroment

Primary:

		Platform: Linuxx86_64
		Server Name: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
		DB Version: Oracle 11.2.0.3, File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005pr; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1
		
Standby:
		
		Platform: Linuxx86_64
		Server Name: RAC2.RAJASEKHAR.COM, IP: 192.168.2.102
		DB Version: Oracle 11.2.0.3, , File system: ASM
		DB Name: w5005pr, DB_UNIQUE_NAME: w5005prg; Non-RAC
		Flashback: Disabled
		Oracle Home Path: /u01/app/oracle/product/11.2.0/db_1


1. Pre-Switchover tasks

- Verify that there is network connectivity between the primary and standby locations. 
- Always recommened test the switchover in your testing system befre working on production.
- Verify primary database READ WRITE and standby instance mounted.
- Verify there are no active users connected to database.
- Make sure last redo data transmitted from primary to standby and applied.
- Set job_queue_processes=0 and aq_tm_processes = 0, dbwr_io_slaves = 0 on both Primary and DR
	alter system set job_queue_processes=0 ;
	alter system set aq_tm_processes = 0 ;
	alter system set dbwr_io_slaves = 0 ; 


2. Vefify TNS Entries

Primary

[oracle@rac1 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005pr_dgmgrl)
                (SID_NAME = w5005pr)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac1 ~]$

[oracle@rac1 ~]$ /u01/app/11.2.0/grid/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2016 00:37:10

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-JAN-2016 17:25:42
Uptime                    0 days 7 hr. 11 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005pr" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005prXDB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005pr_DGB" has 1 instance(s).
  Instance "w5005pr", status READY, has 1 handler(s) for this service...
Service "w5005pr_dgmgrl" has 1 instance(s).
  Instance "w5005pr", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac1 ~]$

Standby

[oracle@rac2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
(SID_LIST =
        (SID_DESC =
                (GLOBAL_DBNAME = w5005prg_dgmgrl)
                (SID_NAME = w5005prg)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        )
)

[oracle@rac2 ~]$


[oracle@rac2 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

W5005PR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005pr)
    )
  )

W5005PRG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.102)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = w5005prg)
    )
  )

[oracle@rac2 ~]$

[oracle@rac2 ~]$ /u01/app/11.2.0/grid/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 11-JAN-2016 00:37:27

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-JAN-2016 17:25:53
Uptime                    0 days 7 hr. 11 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "w5005prg" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prgXDB" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prg_DGB" has 1 instance(s).
  Instance "w5005prg", status READY, has 1 handler(s) for this service...
Service "w5005prg_dgmgrl" has 1 instance(s).
  Instance "w5005prg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$


3. Steps to perform switchover with Physical Standby databases

[oracle@rac1 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys@w5005pr
Connected.
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL> show database verbose w5005pr

Database - w5005pr

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    w5005pr

  Properties:
    DGConnectIdentifier             = 'w5005pr'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'w5005pr'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=w5005pr_DGMGRL)(INSTANCE_NAME=w5005pr)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+FRA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> 



DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to w5005prg;
Performing switchover NOW, please wait...
New primary database "w5005prg" is opening...
Operation requires shutdown of instance "w5005pr" on database "w5005pr"
Shutting down instance "w5005pr"...
ORACLE instance shut down.
Operation requires startup of instance "w5005pr" on database "w5005pr"
Starting instance "w5005pr"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "w5005prg"
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005prg - Primary database
    w5005pr  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


4. Switch back to old primary (If required)

DGMGRL> switchover to w5005pr;
Performing switchover NOW, please wait...
New primary database "w5005pr" is opening...
Operation requires shutdown of instance "w5005prg" on database "w5005prg"
Shutting down instance "w5005prg"...
ORACLE instance shut down.
Operation requires startup of instance "w5005prg" on database "w5005prg"
Starting instance "w5005prg"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "w5005pr"
DGMGRL>
DGMGRL> show configuration

Configuration - w5005pr

  Protection Mode: MaxPerformance
  Databases:
    w5005pr  - Primary database
    w5005prg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.