{"id":1175,"date":"2014-12-03T10:08:15","date_gmt":"2014-12-03T13:08:15","guid":{"rendered":"http:\/\/www.dbarj.com.br\/?p=1175"},"modified":"2016-03-31T14:20:09","modified_gmt":"2016-03-31T17:20:09","slug":"oracle-data-guard-fast-start-failover-oracle-wallet","status":"publish","type":"post","link":"https:\/\/www.dbarj.com.br\/en\/2014\/12\/oracle-data-guard-fast-start-failover-oracle-wallet\/","title":{"rendered":"Oracle Data Guard Fast-Start Failover and Oracle Wallet"},"content":{"rendered":"

The Fast-Start Failover Observer<\/strong><\/span> is an Oracle Data Guard Broker<\/strong> component that allows the DBA automate failover tasks and have more peaceful nights of sleep. What a few DBA know, however, is that it can work in conjunction with the Oracle Wallet<\/strong>, so that you can remove the password of your scripts.<\/p>\n

Consider the scenario of a Data Guard with 2 hosts separated by cities:<\/p>\n

    \n
  1. Unique Name: aaasne \/\u00a0<\/strong>IP: 10.2.200.2<\/strong><\/li>\n
  2. Unique Name: aaarjo \/\u00a0<\/strong>IP: 10.1.100.1<\/strong><\/li>\n<\/ol>\n

    Checking the connection settings:<\/p>\n

    DGMGRL> show database verbose aaasne;\r\n\r\nDatabase - aaasne\r\n...\r\nProperties:\r\n...\r\nDGConnectIdentifier = 'aaasne'\r\nObserverConnectIdentifier = ''\r\nStaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))'\r\n...\r\n\r\nDatabase Status:\r\nSUCCESS\r\n\r\nDGMGRL> show database verbose aaarjo;\r\n\r\nDatabase - aaarjo\r\n...\r\nProperties:\r\n...\r\nDGConnectIdentifier = 'aaarjo'\r\nObserverConnectIdentifier = ''\r\nStaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_DGMGRL)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED)))'\r\n...\r\n\r\nDatabase Status:\r\nSUCCESS<\/pre>\n
    The Observer will try to connect the databases using the ObserverConnectIdentifier<\/strong>.<\/p>\n
    If this is null, it will use the DGConnectIdentifier<\/strong>. So our first task is to include these two entries in the tnsnames.ora<\/strong>\u00a0and in the Oracle Wallet<\/strong> of\u00a0Observer’s host.<\/p>\n
    In this scenario, the client does not allow the user SYS<\/strong><\/span>\u00a0to be used and neither that the passwords remain saved in the script. I then created the user dgobserver<\/strong><\/span>, password abcd1234<\/strong><\/span> as sysdba and I replicated the pwfile in both environments to reflect this privilege.<\/p>\n
    Including this user in Wallet:<\/p>\n
    [oracle@fsfoserver ~]$ mkstore -wrl .\/wallet_dir\/ -create\r\nOracle Secret Store Tool : Version 11.2.0.4.0 - Production\r\nCopyright (c) 2004, 2013, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nEnter password:\r\nEnter password again:\r\n[oracle@fsfoserver ~]$ mkstore -wrl .\/wallet_dir\/ -createCredential aaarjo dgobserver abcd1234\r\nOracle Secret Store Tool : Version 11.2.0.4.0 - Production\r\nCopyright (c) 2004, 2013, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nEnter wallet password:\r\nCreate credential oracle.security.client.connect_string1\r\n[oracle@fsfoserver ~]$ mkstore -wrl .\/wallet_dir\/ -createCredential aaasne dgobserver abcd1234\r\nOracle Secret Store Tool : Version 11.2.0.4.0 - Production\r\nCopyright (c) 2004, 2013, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nEnter wallet password:\r\nCreate credential oracle.security.client.connect_string2<\/pre>\n
    Let’s add in the sqlnet.ora<\/strong> the path to the Wallet:<\/p>\n
    WALLET_LOCATION =  (SOURCE =    (METHOD = FILE)    (METHOD_DATA =      (DIRECTORY = \/home\/oracle\/wallet_dir)    )  )\r\nSQLNET.WALLET_OVERRIDE = TRUE<\/pre>\n
    After that, it is already possible to start the Observer without receiving error:<\/p>\n
    DGMGRL> connect \/@aaarjo\r\nConnected.\r\nDGMGRL> start observer;\r\nObserver started<\/pre>\n
    We will simulate an error running a “shutdown abort”<\/em> in the main instance at the moment (aaasne<\/strong>):<\/p>\n
    [oracle@aaasneserver ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 16:51:21 2014\r\n\r\nCopyright (c) 1982, 2013, Oracle.  All rights reserved.\r\n\r\nEnter password:\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\r\nWith the Partitioning, Oracle Label Security, OLAP, Data Mining,\r\nOracle Database Vault and Real Application Testing options\r\n\r\nSQL> shutdown abort;\r\nORACLE instance shut down.\r\nSQL><\/pre>\n
    Now watching the Observer log:<\/p>\n
    DGMGRL> start observer;\r\nObserver started\r\n\r\n16:52:06.96  Tuesday, December 02, 2014\r\nInitiating Fast-Start Failover to database \"aaarjo\"...\r\nPerforming failover NOW, please wait...\r\nFailover succeeded, new primary is \"aaarjo\"\r\n16:52:18.96  Tuesday, December 02, 2014<\/pre>\n
    The Observer has successfully made the transition from aaasne<\/strong> making aaarjo<\/strong> as the primary database.<\/p>\n
    Starting the database again in another session:<\/p>\n
    [oracle@aaasneserver ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 16:55:24 2014\r\n\r\nCopyright (c) 1982, 2013, Oracle.  All rights reserved.\r\n\r\nEnter password:\r\nConnected to an idle instance.\r\n\r\nSQL> startup;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 1069252608 bytes\r\nFixed Size                  2188408 bytes\r\nVariable Size             700455816 bytes\r\nDatabase Buffers          356515840 bytes\r\nRedo Buffers               10092544 bytes\r\nDatabase mounted.\r\nORA-16649: possible failover to another database prevents this database from\r\nbeing opened\r\n\r\n\r\nSQL><\/pre>\n
    Though, analyzing the Observer log, we realize that the automatically REINSTATE process fails:<\/p>\n
    16:56:00.37  Tuesday, December 02, 2014\r\nInitiating reinstatement for database \"aaasne\"...\r\nReinstating database \"aaasne\", please wait...\r\nOperation requires shutdown of instance \"aaasne\" on database \"aaasne\"\r\nShutting down instance \"aaasne\"...\r\nORA-01017: invalid username\/password; logon denied\r\n\r\nWarning: You are no longer connected to ORACLE.\r\n\r\nPlease complete the following steps and reissue the REINSTATE command:\r\n        shut down instance \"aaasne\" of database \"aaasne\"\r\n        start up and mount instance \"aaasne\" of database \"aaasne\"\r\n\r\n16:56:22.21  Tuesday, December 02, 2014<\/pre>\n
    The problem is that during the REINSTATE<\/strong> phase, Oracle uses the StaticConnectIdentifier<\/strong> as connection. This can be seen when we simulate again the reinstate with the Observer in debug mode (“dgmgrl -debug”<\/em><\/strong>):<\/p>\n
    [W000 12\/02 17:22:24.33] Ping the primary database.\r\n[W000 12\/02 17:22:24.33] Sending command PING to thread P004\r\n[P004 12\/02 17:22:24.33] Executing PING command.\r\n[P004 12\/02 17:22:24.33] {BEGIN dbms_drs.Ping(25640, 248, 0, 194, 0, :version, :flags, :focond, :status); END;}\r\n[P004 12\/02 17:22:24.34] Ping returned REINSTATING\r\n[W000 12\/02 17:22:24.34] Command PING to thread P004 returned status=0\r\n[W000 12\/02 17:22:27.34] Ping the primary database.\r\n[W000 12\/02 17:22:27.34] Sending command PING to thread P004\r\n[P004 12\/02 17:22:27.34] Executing PING command.\r\n[P004 12\/02 17:22:27.34] {BEGIN dbms_drs.Ping(25640, 248, 0, 194, 0, :version, :flags, :focond, :status); END;}\r\nOperation requires shutdown of instance \"aaasne\" on database \"aaasne\"\r\nShutting down instance \"aaasne\"...\r\n[S005 12\/02 17:22:27.34] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED))).\r\n[P004 12\/02 17:22:27.36] Ping returned AFO_SUCCESS\r\n[W000 12\/02 17:22:27.36] Command PING to thread P004 returned status=0\r\nORA-01017: invalid username\/password; logon denied\r\n\r\nWarning: You are no longer connected to ORACLE.\r\n\r\nPlease complete the following steps and reissue the REINSTATE command:\r\n        shut down instance \"aaasne\" of database \"aaasne\"\r\n        start up and mount instance \"aaasne\" of database \"aaasne\"\r\n\r\n17:22:28.44  Tuesday, December 02, 2014\r\n[W000 12\/02 17:22:29.36] Command REINSTATE to thread S005 returned status=16661\r\n[W000 12\/02 17:22:29.36] Failed to reinstate database aaasne. Will retry later<\/pre>\n
    In this case, the Observer tried to connect using the service:<\/p>\n