Clicky Web Analytics

Clicky

Mar
2
Mon
Posted By ritzy on Monday, March 02, 2009
20169 Views 6 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 ConceptsPhysical Standby Concepts

Dataguard Broker ConceptsDataGuard 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.

 

Categories

Rants & Raves Minimize

  • Gravatar
    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>

    • Gravatar
      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?

      • Gravatar
        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>

        • Gravatar
          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.

          • Gravatar
            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.

            • Gravatar
              Naveed Monday, March 07, 2011 at 4:05 PM

              In RAC environment, using DGMGRL how we can change the applying instance at standby site.

  • andrewfraserdba.com Tuesday, August 09, 2011 at 11:21 AM
    via pingback
    Oracle Data Guard Overview | Andrew Fraser DBA

  • Recommended Oracle DBA Books Minimize

         

    Tag Cloud Minimize


    Archive Posts Minimize
     
    Monthly
      Yearly

      Disclaimer:
      This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.

      This posting has nothing to do with my present or past employer.