Friday, June 7, 2013

DataGuard FastStart Failover configuration with Oracle Wallet

Last two months were very busy for me with lot of work and moving house as well. There is a lot of thoughts in my head I want to write about but some of them have to wait until my lab will be online again.Recently I was working on Oracle DataGuard configuration with FastStart Failover and this is what I want to share with you today.

FastStart Failover is a good solution for automatic promoting standby database to be primary and allow application to keep running in case of primary database failure. I don't want to describe whole solution here but want to focus on Observer problems I had recently.

Observer is a part of DataGuard Broker running on 3rd server and it is used to prevent a brain split between primary and standby database. Observer is started by DGMGRL program and it required a sys user credentials. Customer doesn't want to keep sys password in scripts to start and stop observer so Oracle Wallet looks like a good solution to address this issue.

Configuration overview:
- database name - TESTDB
- unique database names - TESTDB_PRIMARY and TESTDB_STANDBY

Wallet has been created and sys user with alias for primary and standby database has been added to it.
I was able to connect to both servers:

[oracle@orasvr3 ~]$ mkstore -wrl /home/oracle/testwallet -listCredential
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
2: TESTDB_STANDBY sys
1: TESTDB_PRIMARY sys
[oracle@orasvr3 ~]$ 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 /@TESTDB_PRIMARY
Connected.
DGMGRL> connect /@TESTDB_STANDBY
Connected.
DGMGRL>

Observer has been started and I begun failover tests. Here is a first attempt:
12:09:25.21  Thursday, May 30, 2013
Initiating Fast-Start Failover to database "TESTDB_PRIMARY"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "TESTDB_PRIMARY"
12:09:31.78  Thursday, May 30, 2013

12:10:47.91  Thursday, May 30, 2013
Initiating reinstatement for database "TESTDB_STANDBY"...
Reinstating database "TESTDB_STANDBY", please wait...
Operation requires shutdown of instance "TESTDB" on database "TESTDB_STANDBY"
Shutting down instance "TESTDB"...
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the REINSTATE command:
        shut down instance "TESTDB" of database "TESTDB_STANDBY"
        start up and mount instance "TESTDB" of database "TESTDB_STANDBY"

12:11:06.36  Thursday, May 30, 2013

Looks like Observer was unable to shutdown TESTDB_STANDBY database. This is typical error if there is no DGMGRL services registered in listeners but I was sure network configuration is fine as it was working perfectly fine when observer has been started with user and password in connection string. So this problem has to be related to Oracle Wallet only. There is a not well know switch for DGMGRL program to enable debug mode and have some more information about issue and I decided to use it. First of all I have started with switchover tests as there need this same interaction with starting and stopping instance like failover and are faster to perform.

Here is a output with debug option enabled:
[oracle@orasvr3 ~]$ dgmgrl -debug
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 /@TESTDB_PRIMARY
[W000 05/30 12:25:02.04] Connecting to database using TESTDB_PRIMARY.
[W000 05/30 12:25:02.09] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 05/30 12:25:02.10] Broker version is '11.2.0.3.0'
Connected.

DGMGRL> switchover to TESTDB_PRIMARY;
Performing switchover NOW, please wait...
New primary database "TESTDB_PRIMARY" is opening...
Operation requires shutdown of instance "TESTDB" on database "TESTDB_STANDBY"
Shutting down instance "TESTDB"...
[W000 05/30 12:26:23.24] Connecting to database using (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGB)(INSTANCE_NAME=TESTDB)(SERVER=dedicated))).
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        shut down instance "TESTDB" of database "TESTDB_STANDBY"
        start up instance "TESTDB" of database "TESTDB_STANDBY"
  It is trying to connect to TESTDB_STANDBY using following connection description

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGB)(INSTANCE_NAME=TESTDB)(SERVER=dedicated)))

but this one doesn't exist in wallet. Service _DBG is created by DMON process and it is registered in default listener or listeners specified in local_listener parameter.
I have added above connection string to Oracle Wallet and completed all manual steps before next try. Here is a output for second switchover run:
[oracle@orasvr3 ~]$ dgmgrl -debug
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 /@TESTDB_PRIMARY
[W000 06/06 12:50:31.01] Connecting to database using TESTDB_PRIMARY.
[W000 06/06 12:50:31.05] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 06/06 12:50:31.06] Broker version is '11.2.0.3.0'
Connected.
DGMGRL> switchover to TESTDB_PRIMARY;
Performing switchover NOW, please wait...
New primary database "TESTDB_PRIMARY" is opening...
Operation requires shutdown of instance "TESTDB" on database "TESTDB_STANDBY"
Shutting down instance "TESTDB"...
[W000 06/06 12:50:45.95] Connecting to database using (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGB)(INSTANCE_NAME=TESTDB)(SERVER=dedicated))).
ORACLE instance shut down.
Operation requires startup of instance "TESTDB" on database "TESTDB_STANDBY"
Starting instance "TESTDB"...
[W000 06/06 12:50:47.09] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))).
ORA-01031: insufficient privileges
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
        start up and mount instance "TESTDB" of database "TESTDB_STANDBY"
Still there is manual step to do but this time database was shut down automatically, and Observer was unable to connect to start up database. There is other connection string

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))).

This one can be found here
DGMGRL> show database verbose TESTDB_STANDBY;

Database - TESTDB_STANDBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    TESTDB

  Properties:
    DGConnectIdentifier             = 'TESTDB_STANDBY'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '10'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = 'a, a'
    FastStartFailoverTarget         = 'TESTDB_PRIMARY'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'TESTDB'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/oralogs/arch/TESTDB'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = 'arch_%t_%s_%r.arc'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS
It has been added to Oracle Wallet as well and now my wallet looks like this 
[oracle@orasvr3 ~]$ mkstore -wrl /home/oracle/testwallet -listCredential
Oracle Secret Store Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
6: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))) sys
5: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr1)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_PRIMARY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))) sys
4: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGB)(INSTANCE_NAME=TESTDB)(SERVER=dedicated))) sys
3: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr1)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=TESTDB_PRIMARY_DGB)(INSTANCE_NAME=TESTDB)(SERVER=dedicated))) sys
2: TESTDB_STANDBY sys
1: TESTDB_PRIMARY sys
After manual completion of required steps I run switchover again and this time it completed without any errors 
DGMGRL> switchover to TESTDB_PRIMARY;
Performing switchover NOW, please wait...
New primary database "TESTDB_PRIMARY" is opening...
Operation requires shutdown of instance "TESTDB" on database "TESTDB_STANDBY"
Shutting down instance "TESTDB"...
[W000 05/30 12:36:51.39] Connecting to database using (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGB)(INSTANCE_NAME=TESTDB)(SERVER=dedicated))).
ORACLE instance shut down.
Operation requires startup of instance "TESTDB" on database "TESTDB_STANDBY"
Starting instance "TESTDB"...
[W000 05/30 12:36:52.54] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))).
[W000 05/30 12:36:52.58] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 363 Serial number: 5

ORACLE instance started.
[W000 05/30 12:36:54.81] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))).
[W000 05/30 12:36:54.84] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 05/30 12:36:54.84] Broker version is '11.2.0.3.0'
alter database  mount

Database mounted.
[W000 05/30 12:37:02.74] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasvr2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=TESTDB_STANDBY_DGMGRL)(INSTANCE_NAME=TESTDB)(SERVER=DEDICATED))).
[W000 05/30 12:37:02.77] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 05/30 12:37:02.78] Broker version is '11.2.0.3.0'
Switchover succeeded, new primary is "TESTDB_PRIMARY"
DGMGRL>

Lesson learned:
  • FSFO Observer can work with Oracle Wallet
  • 3 entries are required per database, service name has to be set to : <db name unique>, <db name unique_DBG> and <db name unique_DGMGRL>
  • DGMGRL entry can be checked in database configuration in Broker
  • DBG entry - use -debug mode if you can't find a proper connection string 
Hope it will help you with DataGuard Observer configuration.

regards,
Marcin


Posted by Marcin Przepiorowski at 8:49 PM
Labels: ,

1 comments:

Rodrigo Jorge said...

Perfect article. I was in step to debug the dgmgrl to understand what was failing until I found you.

Thanks

December 2, 2014 at 3:21 PM

Post a Comment

Subscribe to: Post Comments (Atom)