{"id":1979,"date":"2020-08-14T12:46:20","date_gmt":"2020-08-14T10:46:20","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1979"},"modified":"2020-08-18T15:56:29","modified_gmt":"2020-08-18T13:56:29","slug":"dg-ezconnect-observer","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/dg-ezconnect-observer\/","title":{"rendered":"Data Guard, Easy Connect and the Observer for multiple configurations"},"content":{"rendered":"

EZConnect<\/strong><\/p>\n

One of the challenges of automation in bin Oracle Environments is dealing with tnsnames.ora<\/em> files.
\nThese files might grow big and are sometimes hard to distribute\/maintain properly.
\nThe worst is when manual modifications are needed: manual operations, if not made carefully, can screw up the connection to the databases.
\nThe best solution is always using LDAP <\/strong>naming resolution. I have seen customers using OID, OUD, Active Directory, openldapd<\/em>, all with a great level of control and automation. However, some customer don’t have\/want this possibility and keep relying on TNS naming resolution.
\nWhen Data Guard (and eventually RAC) are in place, the tnsnames.ora gets filled by entries for the DGConnectIdentifiers and StaticConnectIdentifier. If I add the observer, an additional entry is required to access the dbname_CFG service created by the Fast Start Failover.<\/p>\n

Actually, all these entries are not required if I use Easy Connect.<\/p>\n

My friend Franck Pachot wrote a couple of nice blog posts about Easy Connect while working with me at CERN:
\nhttps:\/\/medium.com\/@FranckPachot\/19c-easy-connect-e0c3b77968d7<\/a><\/p>\n

https:\/\/medium.com\/@FranckPachot\/19c-ezconnect-and-wallet-easy-connect-and-external-password-file-8e326bb8c9f5<\/a><\/p>\n

Basic Data Guard configuration<\/strong><\/p>\n

The basic configuration with Data Guard is quite simple to achieve with Easy Connect. In this examples I have:
\n– The primary database TOOLCDB1_SITE1<\/strong>
\n– The duplicated database for standby TOOLCDB1_SITE2<\/strong><\/p>\n

After setting up the static registration (no Grid Infrastructure in my lab):<\/p>\n

SID_LIST_LISTENER=\r\n  (SID_LIST=\r\n    (SID_DESC=\r\n      (GLOBAL_DBNAME=TOOLCDB1_SITE1_DGMGRL)\r\n      (SID_NAME=TOOLCDB1)\r\n      (ORACLE_HOME=\/u01\/app\/oracle\/product\/db_19_8_0)\r\n    )\r\n  )<\/pre>\n
and copying the passwordfile, the configuration can be created with:<\/p>\n
DGMGRL> create configuration TOOLCDB1 as primary database is TOOLCDB1_SITE1 connect identifier is 'newbox01:1521\/TOOLCDB1_SITE1';\r\nConfiguration \"toolcdb1\" created with primary database \"toolcdb1_site1\"\r\n\r\nDGMGRL>  edit database TOOLCDB1_SITE1 set property 'StaticConnectIdentifier'='newbox01:1521\/TOOLCDB1_SITE1_DGMGRL';\r\nProperty \"StaticConnectIdentifier\" updated\r\n\r\nDGMGRL>  add database TOOLCDB1_SITE2 as connect identifier is 'newbox02:1521\/TOOLCDB1_SITE2';\r\nDatabase \"toolcdb1_site2\" added\r\n\r\nDGMGRL>  edit database TOOLCDB1_SITE2 set property 'StaticConnectIdentifier'='newbox02:1521\/TOOLCDB1_SITE2_DGMGRL';\r\nProperty \"StaticConnectIdentifier\" updated\r\n\r\nDGMGRL>  enable configuration;\r\nEnabled.<\/pre>\n
That’s it.<\/p>\n
Now, if I want to have the configuration observed, I need to activate the Fast Start Failover:<\/p>\n
DGMGRL> edit database toolcdb1_site1 set property LogXptMode='SYNC';\r\nProperty \"logxptmode\" updated\r\n\r\nDGMGRL> edit database toolcdb1_site2 set property LogXptMode='SYNC';\r\nProperty \"logxptmode\" updated\r\n\r\nDGMGRL> edit database toolcdb1_site1 set property FastStartFailoverTarget='toolcdb1_site2';\r\nProperty \"faststartfailovertarget\" updated\r\n\r\nDGMGRL> edit database toolcdb1_site2 set property FastStartFailoverTarget='toolcdb1_site1';\r\nProperty \"faststartfailovertarget\" updated\r\n\r\nDGMGRL> edit configuration set protection mode as maxavailability;\r\nSucceeded.\r\n\r\nDGMGRL> enable fast_start failover;\r\nEnabled in Zero Data Loss Mode.\r\n<\/pre>\n
With just two databases, FastStartFailoverTarget is not explicitly needed, but I usually do it as other databases might be added to the configuration in the future.
\nAfter that, the broker complains that FSFO is enabled but there is no observer yet:<\/p>\n
DGMGRL> show fast_start failover;\r\n\r\nFast-Start Failover: Enabled in Zero Data Loss Mode\r\n\r\n  Protection Mode:    MaxAvailability\r\n  Lag Limit:          0 seconds\r\n\r\n  Threshold:          180 seconds\r\n  Active Target:      toolcdb1_site2\r\n  Potential Targets:  \"toolcdb1_site2\"\r\n    toolcdb1_site2 valid\r\n  Observer:           (none)\r\n  Shutdown Primary:   TRUE\r\n  Auto-reinstate:     TRUE\r\n  Observer Reconnect: 180 seconds\r\n  Observer Override:  FALSE\r\n\r\nConfigurable Failover Conditions\r\n  Health Conditions:\r\n    Corrupted Controlfile          YES\r\n    Corrupted Dictionary           YES\r\n    Inaccessible Logfile            NO\r\n    Stuck Archiver                  NO\r\n    Datafile Write Errors          YES\r\n\r\n  Oracle Error Conditions:\r\n    (none)\r\n\r\n\r\nDGMGRL> show configuration;\r\n\r\nConfiguration - toolcdb1\r\n\r\n  Protection Mode: MaxAvailability\r\n  Members:\r\n  toolcdb1_site1 - Primary database\r\n    Warning: ORA-16819: fast-start failover observer not started\r\n\r\n    toolcdb1_site2 - (*) Physical standby database\r\n\r\nFast-Start Failover: Enabled in Zero Data Loss Mode\r\n\r\nConfiguration Status:\r\nWARNING   (status updated 39 seconds ago)\r\n<\/pre>\n
 <\/p>\n
Observer for multiple configurations<\/strong><\/p>\n
This feature has been introduced in 12.2 but it is still not widely used.
\nBefore 12.2, the Observer was a foreground process: the DBAs had to start it in a wrapper script executed with nohup in order to keep it live.
\nSince 12.2, the observer can run as a background process as far as there is a valid wallet for the connection to the databases.
\nAlso, 12.2 introduced the capability of starting multiple configurations with a single dgmgrl command: “START OBSERVING”.<\/p>\n
For more information about it, you can check the documentation here:
\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/dgbkr\/using-data-guard-broker-to-manage-switchovers-failovers.html#GUID-BC513CDB-1E06-4EB3-9FE1-E1331E15E492<\/a><\/p>\n
How to set it up with Easy Connect?<\/strong><\/p>\n
First, I need a wallet. And here comes the first compromise:
\nHaving a single dgmgrl session to start all my configurations means that I have a single wallet for all the databases that I want to observe.
\nFair enough, all the DBs (CDBs?) are managed by the same team in this case.
\nIf I have only observers on my host I can easily point to the wallet from my central sqlnet.ora:<\/p>\n
WALLET_LOCATION =\r\n   (SOURCE =\r\n      (METHOD = FILE)\r\n      (METHOD_DATA = (DIRECTORY = \/u01\/app\/oracle\/admin\/observers\/wallet))\r\n  )\r\nSQLNET.WALLET_OVERRIDE = TRUE<\/pre>\n
Otherwise I need to create a separate TNS_ADMIN for my observer management environment.
\nThen, I create the wallet:<\/p>\n
$ WALLET_DIR=$ORACLE_BASE\/admin\/observers\/wallet\r\n$ mkdir -p $WALLET_DIR\r\n$ orapki wallet create -wallet $WALLET_DIR -auto_login_local -pwd Password2020\r\nOracle PKI Tool Release 21.0.0.0.0 - Production\r\nVersion 21.0.0.0.0\r\nCopyright (c) 2004, 2020, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOperation is successfully completed.<\/pre>\n
Now I need to add the connection descriptors.<\/p>\n
Which connection descriptors do I need?<\/strong>
\nThe Observer uses the DGConnectIdentifier<\/strong> to keep observing the databases, but needs a connection to both of them<\/strong> using the TOOLCDB1_CFG<\/strong> service (unless I specify something different with the broker configuration property ConfigurationWideServiceName<\/strong>) to connect to the configuration and get the DGConnectIdentifier information. Again, you can check it in the doc. or the note Oracle 12.2 – Simplified OBSERVER Management for Multiple Fast-Start Failover Configurations (Doc ID 2285891.1)<\/strong><\/p>\n
So I need to specify three secrets for three connection descriptors:<\/p>\n
$ mkstore -wrl \"$TNS_ADMIN\" -createCredential newbox01,newbox02:1521\/TOOLCDB1_CFG sysdg\r\nOracle Secret Store Tool Release 21.0.0.0.0 - Production\r\nVersion 21.0.0.0.0\r\nCopyright (c) 2004, 2020, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nYour secret\/Password is missing in the command line\r\nEnter your secret\/Password:\r\nRe-enter your secret\/Password:\r\nEnter wallet password:\r\n\r\n$ mkstore -wrl \"$TNS_ADMIN\" -createCredential newbox01:1521\/TOOLCDB1_SITE1 sysdg\r\nOracle Secret Store Tool Release 21.0.0.0.0 - Production\r\nVersion 21.0.0.0.0\r\nCopyright (c) 2004, 2020, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nYour secret\/Password is missing in the command line\r\nEnter your secret\/Password:\r\nRe-enter your secret\/Password:\r\nEnter wallet password:\r\n\r\n\r\n$ mkstore -wrl \"$TNS_ADMIN\" -createCredential newbox02:1521\/TOOLCDB1_SITE2 sysdg\r\nOracle Secret Store Tool Release 21.0.0.0.0 - Production\r\nVersion 21.0.0.0.0\r\nCopyright (c) 2004, 2020, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nYour secret\/Password is missing in the command line\r\nEnter your secret\/Password:\r\nRe-enter your secret\/Password:\r\nEnter wallet password:\r\n<\/pre>\n
The first one will be used for the initial connection. The other two to observe the Primary and Standby.
\nI need to be careful that the first EZConnect descriptor matches EXACTLY what I put in observer.ora (see next step) and the last two match my DGConnectIdentifier (unless I specify something different with ObserverConnectIdentifier<\/strong>), otherwise I will get some errors and the observer will not observe correctly (or will not start at all).<\/p>\n
The dgmgrl needs then a file named observer.ora.
\n$ORACLE_BASE\/admin\/observers or the central TNS_ADMIN would be good locations, but what if I have observers that must be started from multiple Oracle Homes?
\nIn that case, having a observer.ora in $ORACLE_HOME\/network\/admin (or $ORACLE_BASE\/homes\/{OHNAME}\/network\/admin\/ if Read-Only Oracle Home is enabled) would be a better solution: in this case I would need to start one session per Oracle Home<\/p>\n
The content of my observer.ora must be something like:<\/p>\n
BROKER_CONFIGS=\r\n   (\r\n     (CONFIG=\r\n       (NAME=TOOLCDB1)\r\n       (CONNECT_ID=newbox01,newbox02:1521\/TOOLCDB1_CFG)\r\n       (CONFIG_HOME=\/export\/soft\/oracle\/admin\/TOOLCDB1\/observer)\r\n     )\r\n   )<\/pre>\n
This is the example for my configuration, but I can put as many (CONFIG=…) as I want in order to observe multiple configurations.
\nThen, if everything is configured properly, I can start all the observers with a single command:<\/p>\n
DGMGRL> SET OBSERVERCONFIGFILE=\/u01\/app\/oracle\/admin\/observers\/observer.ora\r\nDGMGRL> START OBSERVING\r\nObserverConfigFile=observer.ora\r\nobserver configuration file parsing succeeded\r\nSubmitted command \"START OBSERVER\" using connect identifier \"newbox01,newbox02:1521\/TOOLCDB1_CFG\"\r\n\r\nCheck superobserver.log, individual observer logs and Data Guard Broker logs for execution details.\r\n\r\nDGMGRL> show observers\r\nObserverConfigFile=\/u01\/app\/oracle\/admin\/observers\/observer.ora\r\nobserver configuration file parsing succeeded\r\nSubmitted command \"SHOW OBSERVER\" using connect identifier \"newbox01,newbox02:1521\/TOOLCDB1_CFG\"\r\nConnected to \"TOOLCDB1_SITE2\"\r\n\r\nConfiguration - toolcdb1\r\n\r\n  Primary:            toolcdb1_site1\r\n  Active Target:      toolcdb1_site2\r\n\r\nObserver \"newbox03.trivadistraining.com1\" - Master\r\n\r\n  Host Name:                    newbox03.trivadistraining.com\r\n  Last Ping to Primary:         1 second ago\r\n  Last Ping to Target:          2 seconds ago\r\n<\/pre>\n
Troubleshooting<\/strong><\/p>\n
If the observer does not work, sometimes it is not easy to understand the cause.<\/p>\n