This post is also available in:
Português
The Fast-Start Failover Observer is an Oracle Data Guard Broker 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, so that you can remove the password of your scripts.
Consider the scenario of a Data Guard with 2 hosts separated by cities:
- Unique Name: aaasne / IP: 10.2.200.2
- Unique Name: aaarjo / IP: 10.1.100.1
Checking the connection settings:
DGMGRL> show database verbose aaasne; Database - aaasne ... Properties: ... DGConnectIdentifier = 'aaasne' ObserverConnectIdentifier = '' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))' ... Database Status: SUCCESS DGMGRL> show database verbose aaarjo; Database - aaarjo ... Properties: ... DGConnectIdentifier = 'aaarjo' ObserverConnectIdentifier = '' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_DGMGRL)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED)))' ... Database Status: SUCCESS
The Observer will try to connect the databases using the ObserverConnectIdentifier.
If this is null, it will use the DGConnectIdentifier. So our first task is to include these two entries in the tnsnames.ora and in the Oracle Wallet of Observer's host.
In this scenario, the client does not allow the user SYS to be used and neither that the passwords remain saved in the script. I then created the user dgobserver, password abcd1234 as sysdba and I replicated the pwfile in both environments to reflect this privilege.
Including this user in Wallet:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -create Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again: [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential aaarjo dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string1 [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential aaasne dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string2
Let's add in the sqlnet.ora the path to the Wallet:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /home/oracle/wallet_dir) ) ) SQLNET.WALLET_OVERRIDE = TRUE
After that, it is already possible to start the Observer without receiving error:
DGMGRL> connect /@aaarjo Connected. DGMGRL> start observer; Observer started
We will simulate an error running a "shutdown abort" in the main instance at the moment (aaasne):
[oracle@aaasneserver ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 16:51:21 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> shutdown abort; ORACLE instance shut down. SQL>
Now watching the Observer log:
DGMGRL> start observer; Observer started 16:52:06.96 Tuesday, December 02, 2014 Initiating Fast-Start Failover to database "aaarjo"... Performing failover NOW, please wait... Failover succeeded, new primary is "aaarjo" 16:52:18.96 Tuesday, December 02, 2014
The Observer has successfully made the transition from aaasne making aaarjo as the primary database.
Starting the database again in another session:
[oracle@aaasneserver ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 2 16:55:24 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 1069252608 bytes Fixed Size 2188408 bytes Variable Size 700455816 bytes Database Buffers 356515840 bytes Redo Buffers 10092544 bytes Database mounted. ORA-16649: possible failover to another database prevents this database from being opened SQL>
Though, analyzing the Observer log, we realize that the automatically REINSTATE process fails:
16:56:00.37 Tuesday, December 02, 2014
Initiating reinstatement for database "aaasne"...
Reinstating database "aaasne", please wait...
Operation requires shutdown of instance "aaasne" on database "aaasne"
Shutting down instance "aaasne"...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the REINSTATE command:
shut down instance "aaasne" of database "aaasne"
start up and mount instance "aaasne" of database "aaasne"
16:56:22.21 Tuesday, December 02, 2014
The problem is that during the REINSTATE phase, Oracle uses the StaticConnectIdentifier as connection. This can be seen when we simulate again the reinstate with the Observer in debug mode ("dgmgrl -debug"):
[W000 12/02 17:22:24.33] Ping the primary database.
[W000 12/02 17:22:24.33] Sending command PING to thread P004
[P004 12/02 17:22:24.33] Executing PING command.
[P004 12/02 17:22:24.33] {BEGIN dbms_drs.Ping(25640, 248, 0, 194, 0, :version, :flags, :focond, :status); END;}
[P004 12/02 17:22:24.34] Ping returned REINSTATING
[W000 12/02 17:22:24.34] Command PING to thread P004 returned status=0
[W000 12/02 17:22:27.34] Ping the primary database.
[W000 12/02 17:22:27.34] Sending command PING to thread P004
[P004 12/02 17:22:27.34] Executing PING command.
[P004 12/02 17:22:27.34] {BEGIN dbms_drs.Ping(25640, 248, 0, 194, 0, :version, :flags, :focond, :status); END;}
Operation requires shutdown of instance "aaasne" on database "aaasne"
Shutting down instance "aaasne"...
[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))).
[P004 12/02 17:22:27.36] Ping returned AFO_SUCCESS
[W000 12/02 17:22:27.36] Command PING to thread P004 returned status=0
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the REINSTATE command:
shut down instance "aaasne" of database "aaasne"
start up and mount instance "aaasne" of database "aaasne"
17:22:28.44 Tuesday, December 02, 2014
[W000 12/02 17:22:29.36] Command REINSTATE to thread S005 returned status=16661
[W000 12/02 17:22:29.36] Failed to reinstate database aaasne. Will retry later
In this case, the Observer tried to connect using the service:
- '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGMGRL)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))'.
To allow the REINSTATE process to work properly, it will be necessary to add to the Wallet the connection strings represented by StaticConnectIdentifier of both databases:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgmgrl)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string3 [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgmgrl)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string4 [oracle@fsfoserver ~]$
Testing again the REINSTATE automatic process:
DGMGRL> start observer; Observer started 17:36:42.59 Tuesday, December 02, 2014 Initiating Fast-Start Failover to database "aaarjo"... Performing failover NOW, please wait... Failover succeeded, new primary is "aaarjo" 17:36:54.36 Tuesday, December 02, 2014 17:37:29.72 Tuesday, December 02, 2014 Initiating reinstatement for database "aaasne"... Reinstating database "aaasne", please wait... Operation requires shutdown of instance "aaasne" on database "aaasne" Shutting down instance "aaasne"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "aaasne" on database "aaasne" Starting instance "aaasne"... ORACLE instance started. Database mounted. Continuing to reinstate database "aaasne" ... Reinstatement of database "aaasne" succeeded 17:38:50.59 Tuesday, December 02, 2014
Okay, we will not have problems with the FSFO Observer. So far, we have achieved that this process works properly in unattended mode in conjunction with Oracle Wallet.
However, what if we try to manually perform an switchover between these two database?
[oracle@fsfoserver ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /@aaarjo
Connected.
DGMGRL> show configuration;
Configuration - timdbubb
Protection Mode: MaxAvailability
Databases:
aaarjo - Primary database
aaasne - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
DGMGRL> switchover to aaasne;
Performing switchover NOW, please wait...
Operation requires a connection to instance "aaasne" on database "aaasne"
Connecting to instance "aaasne"...
Unable to connect to database
ORA-12545: Connect failed because target host or object does not exist
Failed.
Warning: You are no longer connected to ORACLE.
connect to instance "aaasne" of database "aaasne"
DGMGRL>
Note that even creating before the 2 entries that use the static listener, we had a new problem. Let's analyze it in debug mode:
[oracle@fsfoserver ~]$ dgmgrl -debug
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /@aaarjo
[W000 12/02 18:27:29.12] Connecting to database using aaarjo.
[W000 12/02 18:27:29.49] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;].
[W000 12/02 18:27:29.50] Broker version is '11.2.0.4.0'
Connected.
DGMGRL> switchover to aaasne;
Performing switchover NOW, please wait...
Operation requires a connection to instance "aaasne" on database "aaasne"
Connecting to instance "aaasne"...
[W000 12/02 18:27:35.86] Connecting to database using (DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGB)(INSTANCE_NAME=aaasne)(SERVER=dedicated))).
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
connect to instance "aaasne" of database "aaasne"
DGMGRL>
By the logs, we can see that in the process of switchover, the Broker tries to use another service:
- '(DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_DGB)(INSTANCE_NAME=aaasne)(SERVER=dedicated)))'.
This service is derived from the one set in the DGConnectIdentifier, concatenating "_DGB" to SERVICE_NAME. Therefore, you must also we include this string of both servers in Wallet:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgb)(INSTANCE_NAME=aaarjo)(SERVER=dedicated)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string5 [oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -createCredential '(DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgb)(INSTANCE_NAME=aaasne)(SERVER=dedicated)))' dgobserver abcd1234 Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Create credential oracle.security.client.connect_string6 [oracle@fsfoserver ~]$
And testing again:
DGMGRL> switchover to aaasne; Performing switchover NOW, please wait... Operation requires a connection to instance "aaasne" on database "aaasne" Connecting to instance "aaasne"... Connected. New primary database "aaasne" is opening... Operation requires startup of instance "aaarjo" on database "aaarjo" Starting instance "aaarjo"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "aaasne" DGMGRL>
Agora o Observer está livre para executar as todas as tarefas utilizando o Oracle Wallet, além de ser possível efetuar operações manuais.
Now the Observer is free to perform all tasks using the Oracle Wallet, as well as being possible to perform manual operations.
Finally, the Wallet was as follows, with 3 entries for each host:
[oracle@fsfoserver ~]$ mkstore -wrl ./wallet_dir/ -listCredential Oracle Secret Store Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 6: (DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgb)(INSTANCE_NAME=aaasne)(SERVER=dedicated))) dgobserver 5: (DESCRIPTION=(SDU=32767)(SEND_BUF_SIZE=160000)(RECV_BUF_SIZE=160000)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378)))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgb)(INSTANCE_NAME=aaarjo)(SERVER=dedicated))) dgobserver 4: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.2.200.2)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaasne_dgmgrl)(INSTANCE_NAME=aaasne)(SERVER=DEDICATED))) dgobserver 3: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.100.1)(PORT=4378))(CONNECT_DATA=(SERVICE_NAME=aaarjo_dgmgrl)(INSTANCE_NAME=aaarjo)(SERVER=DEDICATED))) dgobserver 2: aaasne dgobserver 1: aaarjo dgobserver [oracle@fsfoserver ~]$Have you enjoyed? Please leave a comment or give a 👍!
4 comments
Skip to comment form
Great post! Rodrigo
Could you let me know -
Since you have created one user account - dgobserver with sysdba privilege, how do you know in dgmgrl, the connect / will use dgobserver to login db, not any other users account wiht sysdba role?
Author
Hi Jing,
The "connect /" will not use the dgobserver user. It will actually use the SYS account via dba group OS auth. To use the dgobserver you need to connect using "connect dgobserver".
The dgmgrl utility will append the "as sysdba" after the username. So if you do:
- "DGMGRL> connect /", it's the samething as "connect / as sysdba" in SQLPlus = dba group OS auth
- "DGMGRL> connect sys", it's the samething as "connect sys as sysdba" in SQLPlus = sys user password auth
- "DGMGRL> connect dgobserver", it's the samething as "connect dgobserver as sysdba" in SQLPlus = dgobserver user password auth
Anyway, if you create the Wallet entry, you may use: "/@tnsentry". In that case, the dgmgrl utility will get the username (dgobserver), password and service from the wallet and connect through that account using sysdba.
Regards,
RJ
Nice post. Have you figured out a way of having 1 TNS alias per database rather than needing 3 connections per instance? I presume StaticConnectIdentifier can be changed to a TNS alias and put into OID/TNSNAMES, but what about the switchiver string using _DGB? Any ideas how to get that aliased (to be the same as StaticConnectIdentifier or DGConnectIdentifier?
Author
Well, the StaticConnectIdentifier must have some Service that is static, because the broker needs to be able to connect to the DB in mount state. So if you use some TNS entry there, don't forget to set up a static service on it.
Also, the thing is, the StaticConnectIdentifier is automatically managed based on the LOCAL_LISTENER parameter. If you change it manually, it will never be automatically managed again (unless you reconfigure your broker). Not a big deal, but just to let you know.
The only way that I can think of having a single TNS is having all your entries with the _DGB and making it a static service:
DGConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=yyy))(CONNECT_DATA=(SERVICE_NAME=orcl_dgb)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxx)(PORT=yyy))(CONNECT_DATA=(SERVICE_NAME=orcl_dgb)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
I don't know if the switch-over would append again the _DGB making it orcl_dgb_dgb, in that case this approach wouldn't work.
Anyway, the broker does not execute switchover on FSFO, only failover. So the _dgb string is not really necessary if you are only using broker and single TNS entry for FSFO.
Regards,
RJ