How to switchover using DGMGRL, broker with example|Oracle Talk

How to switchover using DGMGRL, broker with example

Mar
2

Mon

Posted By ritzy on Monday, March 02, 2009
1340 Views 5 Comments

This time around, we'll do a switchover and switchback exercise using Oracle dataguard broker. If you need help on setting up the broker, refer my previous post How to setup DGMGRL, broker with example.

Before we start, remember that DG broker does not have the ability to create standby and is used for managing the dataguard configuration. Before proceeding with step-by-step instructions on how to set up DG broker, I would recommend you to get comfortable with the concepts first.

Physical Standby Concepts

DataGuard Broker Concepts

The following configuration was tested on RHEL4U2 64-bit with Oracle 10.1.0.5 database. I used 2-node RAC as the primary and single instance database as the standby for this test.

Pri db_unique_name    =    'TESTPRI'
DG db_unique_name    =    'TESTDG'
Configuration(any name) =    'DG_TEST'
sys password        =    'sys'
Pri conn stg        =    'TESTPRI'

Most of the times, switchover using broker fails because the configuration is not set correctly. So to avoid later troubleshooting, let us do a quick sanity check first. Ensure following parameters are set correctly on all instances of primary and standby
1. Ensure SPFILE is used
SQL> sho parameter spfile

2. Verify dmon process is running and broker parameters viz. DG_BROKER_START is set to TRUE and DG_BROKER_CONFIG_FILEn are set correctly
SQL> sho parameter broker
$ps -ef|grep dmon|grep -v grep

3. Check if LOCAL_LISTENER is set(This is needed only if you are non-default port other than 1521)
DGMGRL> show instance 'TESTPRIR1' 'LocalListenerAddress';

4. Verify if GLOBAL_DBNAME in listener.ora is set correctly to <<db_unique_name>>_DGMGRL.<<db_domain>>
Example listener.ora:
SID_LIST_LISTENER_TESTPRIR1 =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /apps/oracle/product/10g/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = TESTPRIR1)
      (GLOBAL_DBNAME = TESTPRI_DGMGRL)
      (ORACLE_HOME = /apps/oracle/product/10g/db)
    )
)
Note: In the above case, db_domain was null.

5. Verify if START_OPTIONS is set to MOUNT in the OCR
$ srvctl config database -d TESTPRI -a
testprir1 TESTPRIR1 /apps/oracle/product/10g/db
testprir2 TESTPRIR2 /apps/oracle/product/10g/db
DB_NAME: null
ORACLE_HOME: /apps/oracle/product/10g/db
SPFILE: /n01/oradata/TESTPRI/spfileTESTPRI.ora
DOMAIN: null
DB_ROLE: PRIMARY
START_OPTIONS: MOUNT

6. Broker configuration is enabled and state of all members as intended is ONLINE
DGMGRL> show configuration

7. remote_login_passwordfile is set to 'EXCLUSIVE'
SQL> sho parameter remote_login_passwordfile

8. Verify TESTPRI and TESTDG tnspingable from both primary as well as standby and can connect to each other.

Let us do the actual switchover now. Since our primary database is RAC, let us also keep a watch on how the OCR status is changing

9. Ensure <<db_unique_name>>_DGMGRL is part of service names otherwise post switchover, instances may have to be manually started

OCR Config before switchover:
[oracle@testprir1]/apps/oracle/product/10g/db/dbs% srvctl status database -d TESTPRI
Instance TESTPRIR1 is running on node testprir1
Instance TESTPRIR2 is running on node testprir2

[oracle@testprir1]/apps/oracle/product/10g/db/dbs% srvctl config database -d TESTPRI -a
testprir1 TESTPRIR1 /apps/oracle/product/10g/db
testprir2 TESTPRIR2 /apps/oracle/product/10g/db
DB_NAME: null
ORACLE_HOME: /apps/oracle/product/10g/db
SPFILE: /n01/oradata/TESTPRI/spfileTESTPRI.ora
DOMAIN: null
DB_ROLE: PRIMARY
START_OPTIONS: MOUNT

Broker config before switchover:
DGMGRL> show configuration;

Configuration
Name:            DG_TEST
Enabled:         YES
Protection Mode: MaxPerformance
Databases:
    TESTPRI - Primary database
    TESTDG - Physical standby database

Current status for "DG_TEST":
SUCCESS

Switchover Now:
DGMGRL> switchover to 'TESTDG';
Performing switchover NOW. Please wait...
Operation requires shutdown of instance "TESTPRIR1" on database "TESTPRI".
Shutting down instance "TESTPRIR1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "TESTPRI" on database "TESTDG".
Shutting down instance "TESTPRI"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "TESTPRIR1" on database "TESTPRI".
Starting instance "TESTPRIR1"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "TESTPRI" on database "TESTDG".
Starting instance "TESTPRI"...
ORACLE instance started.
Database mounted.
Switchover succeeded. New primary is "TESTDG"
DGMGRL> exit

OCR after switchover:
[oracle@testprir1]/apps/oracle/product/10g/db/dbs% srvctl config database -d TESTPRI -a
testprir1 TESTPRIR1 /apps/oracle/product/10g/db
testprir2 TESTPRIR2 /apps/oracle/product/10g/db
DB_NAME: null
ORACLE_HOME: /apps/oracle/product/10g/db
SPFILE: /n01/oradata/TESTPRI/spfileTESTPRI.ora
DOMAIN: null
DB_ROLE: PHYSICAL_STANDBY
START_OPTIONS: MOUNT

[oracle@testprir1]/apps/oracle/product/10g/db/dbs% srvctl status database -d TESTPRI
Instance TESTPRIR1 is running on node testprir1
Instance TESTPRIR2 is running on node testprir2

Broker config post switchover:
DGMGRL> show configuration;

Configuration
Name:            DG_TEST
Enabled:         YES
Protection Mode: MaxPerformance
Databases:
    TESTPRI - Physical standby database
    TESTDG - Primary database

Current status for "DG_TEST":
SUCCESS

Switchback Now:
DGMGRL> connect sys/sys@TESTDG
Connected.

DGMGRL> switchover to 'TESTPRI';
Performing switchover NOW. Please wait...
Operation requires shutdown of instance "TESTPRI" on database "TESTDG".
Shutting down instance "TESTPRI"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "TESTPRIR1" on database "TESTPRI".
Shutting down instance "TESTPRIR1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "TESTPRI" on database "TESTDG".
Starting instance "TESTPRI"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "TESTPRIR1" on database "TESTPRI".
Starting instance "TESTPRIR1"...
ORACLE instance started.
Database mounted.
Switchover succeeded. New primary is "TESTPRI"
DGMGRL> exit

Back to original config:
[oracle@testprir1]/apps/oracle/product/10g/db/dbs% srvctl status database -d TESTPRI
Instance TESTPRIR1 is running on node testprir1
Instance TESTPRIR2 is running on node testprir2

[oracle@testprir1]/apps/oracle/product/10g/db/dbs% srvctl config database -d TESTPRI -a
testprir1 TESTPRIR1 /apps/oracle/product/10g/db
testprir2 TESTPRIR2 /apps/oracle/product/10g/db
DB_NAME: null
ORACLE_HOME: /apps/oracle/product/10g/db
SPFILE: /n01/oradata/TESTPRI/spfileTESTPRI.ora
DOMAIN: null
DB_ROLE: PRIMARY
START_OPTIONS: MOUNT

Troubleshooting Tips:
1. Remember to create TEMP files on DG(new primary) after switchover

2. Give sys passsword explicitly as time of connecting using DGMGRL for switchover else it fails. Let us take an example where sys password was not given.
DGMGRL> connect /
Connected.
DGMGRL> switchover to 'TESTDG';
Performing switchover NOW. Please wait...
Operation requires shutdown of instance "TESTPRIR1" on database "TESTPRI".
Shutting down instance "TESTPRIR1"...
ORA-01017: invalid username/password; logon denied

You are no longer connected to ORACLE
Please connect again.
Unable to shut down instance "TESTPRIR1".
You must shut down instance "TESTPRIR1" manually.
Operation requires shutdown of instance "TESTPRI" on database "TESTDG".
You must shut down instance "TESTPRI" manually.
Operation requires startup of instance "TESTPRIR1" on database "TESTPRI".
You must start instance "TESTPRIR1" manually.
Operation requires startup of instance "TESTPRI" on database "TESTDG".
You must start instance "TESTPRI" manually.
Switchover succeeded. New primary is "TESTDG"

Fix:

Just manually stop and start the instances. Switchover of role reversal is already done.

3. Before executing the switchover you may reduce the number of ARCH processes to the minimum needed for both remote and local archiving. Additional ARCH processes can take additional time to shutdown thereby increasing overall switchover timings. Once the switchover has been completed you can reenable the additional ARCH processes.'log_archive_max_processes' is the parameter in question here.

This is it. So we saw how easy it was to switchover/switchback using Oracle Dataguard broker. If the configuration is set correctly, it's very easy to manage.

5 Comments so far ...

Leave a Comment

Mail me on replies to my comment

Mail me on comments to this Post

Vishal Thursday, April 02, 2009 at 6:31 PM

Re: How to switchover using DGMGRL, broker with example
Thanks for your comprehensive step-by-step instructions.
I'm able to switchover but both primary and standby instance needs to be started manually. I've used sys password explicitly as i know connecting using '/' causes this issue. Here , the interesting thing here is instances are shutdown properly by the broker which means sys password is working correctly but unable to start the instances.

DGMGRL> switchover to 'test2_dg'
Performing switchover NOW, please wait...
Operation requires shutdown of instance "test2" on database "test2_pr"
Shutting down instance "test2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "test2" on database "test2_dg"
Shutting down instance "test2"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "test2" on database "test2_pr"
Starting instance "test2"...
ORA-01031: insufficient privileges

You are no longer connected to ORACLE
Please connect again.
Unable to start instance "test2"
You must start instance "test2" manually
Operation requires startup of instance "test2" on database "test2_dg"
You must start instance "test2" manually
Switchover succeeded, new primary is "test2_dg"
DGMGRL>

Ritzy Thursday, April 02, 2009 at 6:54 PM

Re: How to switchover using DGMGRL, broker with example
Please confirm the following:
1. remote_login_passwordfile should be set to 'SHARED' or 'EXCLUSIVE' on both primary and standby
2. sys password used is same on both primary and standby.
3. password file exists in ${ORACLE_HOME}/dbs and and in the correct format i.e orapw{SID}
4. Are you able to connect using 'sys/{pwd}@{conn_string} as sysdba' to both primary and standby?

Vishal Thursday, April 02, 2009 at 7:03 PM

Re: How to switchover using DGMGRL, broker with example
remote_login_passwordfile is set to EXCLUSIVE and passwordfile is correct too. I found out that I'm able to connect using connect string when the DB is up but when the DB is down, I'm getting ORA-01031: insufficient privileges. I think this is precisely why switchover fails to start the instance.

Unable to connect when DB is down:
% sqlplus sys/sys@test2_pr as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 1 13:34:08 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges

While able to connect when the db is up:

% sqlplus sys/sys@test2_pr as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 1 13:39:37 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Ritzy Thursday, April 02, 2009 at 7:29 PM

Re: How to switchover using DGMGRL, broker with example
Can you verify the case of SID is consistent across? I mean in the name of password file and in the listener.

Vishal Thursday, April 02, 2009 at 7:57 PM

Re: How to switchover using DGMGRL, broker with example
WOW! It's fixed. The SID was indeed in uppercase in listener.ora. I changed it to lower case, bounced the listener and it's all fine now. I can connect as sysdba even when the DB is down. Error ORA-01031: insufficient privileges is resolved now. Switchover also completes now without any manual intervention. Thanks a lot and truly appreciate your response on this.