Tag Archives: switchover dataguard

Switchover with Broker

Dataguard, ,

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.

Switchover with No Broker

Dataguard, , , ,

Performing Switchover with No Broker (Manual)

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. Steps to perform switchover with Physical Standby databases
3. Additional Information


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 ;

On Primary

	
SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            20

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL> select * from dba_jobs_running;

no rows selected

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL>

Note: Block further job submission by setting the job_queue_processes parameter to 0 so that 
there would be no jobs running during switchover.

SQL> alter system set job_queue_processes=0 scope=spfile;

System altered.

SQL>

On Standby

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg

SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            20

SQL> select name from v$datafile where status='OFFLINE';

no rows selected

SQL>


2. Steps to perform switchover with Physical Standby databases

On PRIMARY

Note: Always perform the switchover of the primary database to standby database first and then 
switchover the standby database to primary. 

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005pr

SQL>

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL>

Note: If status “TO STANDBY” or “SESSIONS ACTIVE” on the primary database, then perform the switchover
using the below query

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL>
SQL> shut immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 10 01:15:12 2016

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             822083824 bytes
Database Buffers          436207616 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005pr

SQL>  select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
            22

On STANDBY

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ ONLY WITH APPLY PHYSICAL STANDBY w5005prg
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
W5005PR   MOUNTED

SQL> alter database open;

Database altered.

SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;

NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME
--------- -------------------- ---------------- ----------------
W5005PR   READ WRITE           PRIMARY          w5005prg

SQL> SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

System altered.

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)
--------------
            22

SQL>


3. Additional Information

The SWITCHOVER_STATUS column of v$database can have the following values:

NOT ALLOWED - Either this is a standby database and the primary database 
              has not been switched first, or this is a prstimary 
              database and there are no standby databases.

SESSIONS ACTIVE - Indicates that there are active SQL sessions attached
                  to the primary or standby database that need to be 
                  disconnected before the switchover operation is 
                  permitted.

SWITCHOVER PENDING - This is a standby database and the primary database 
                     switchover request has been received but not 
                     processed.

SWITCHOVER LATENT - The switchover was in pending mode, but did not 
                    complete and went back to the primary database.

TO PRIMARY - This is a standby database, with no active sessions, that is 
             allowed to switch over to a primary database. 

TO STANDBY - This is a primary database, with no active sessions, that is 
             allowed to switch over to a standby database.

RECOVERY NEEDED - This is a standby database that has not received the 
                  switchover request.

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.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/