Oracle DataGuard Failover Support for Data Loss Prevention
Article ID: 278062
Updated On:
Products
Issue/Introduction
Problem Statement:
DataGuard is being used to support the failover process for database resilience. During the failover process, DLP will not reach the failed over database instance despite being able to reach the database server.
The errors returned are:
- ORA-12521: TNS:Listener does not currently know of instance requested in connect descriptor
- ORA-12514: TNS:Listener does not currently know of the service requested in the connect
descriptor
Environment
DLP 16.X
Oracle Enterprise Edition
Oracle DataGuard
Cause
Problem Identification:
During install, DLP requires that a database server and port number be identified. This connection is published in two places, the tnsnames.ora file and the jdbc.properties file.
This is the tnsnames.ora entry:
protect0 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver.example.com)(PORT = 1521 ))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = protect)
)
)
This is the jdbc.properties entry:
# JDBC connection information for the "oracle-thin" connection
jdbc.username.oracle-thin=protect
jdbc.driver.oracle-thin=oracle.jdbc.OracleDriver
jdbc.subprotocol.oracle-thin=oracle:thin
jdbc.dbalias.oracle-thin=@(description=(address=(host==dbserver.example.com)(protocol=tcp)(port=1521))(connect_data=(service_name=protect)))
During software installation and updates/upgrades/patches, the DLP installation relies on the tnsnames.ora file. However, during normal operation, DLP leverages the jdbc.dbalias.oracle-thin entry in the jdbc.properties file.
During a DataGuard configuration, the simplest configuration is there are two or more instances on individual servers, one instance per server, with one being the primary and the rest being secondary, replicated partners. DataGuard aliases all these instances and servers behind a single service name. In DLP’s case, it’s “protect”. Under this system, the primary instance is active and available but all secondary instances are deprecated into a read-only state. Listeners on all members of the Data Guard partnership remain active. When the primary instance gets failed down, a secondary instance becomes primary and the previous primary becomes a deprecated secondary.
This creates a situation where a tnsping or a sqlplus session which uses tnsnames.ora to connect will show a successful connection and the listener on the now deprecated ex-primary will still respond. This creates a confusing situation where DLP can contact the now deprecated ex-primary but the Listener on that server returns ORA-12521 and ORA-12514 errors because the actual service is not running on that system anymore and is now running on the new, failed-over primary that is not in the oracle-thin entry in jdbc.properties. This causes DLP to be unable to connect to the new primary because it doesn’t know that the new primary exists and still tries to reach out to the old primary. DataGuard is supposed to insulate DLP from this but for it to be successful, DLP needs to have the connection information for all applicable nodes in the DataGuard partnership.
Resolution
Problem Resolution:
Because DLP cannot connect but typical Oracle based utilities can, there is an incorrect/incomplete/malformed connect descriptor somewhere in the mix. The probable culprit is the oracle-thin connector contained in jdbc.properties as it is how DLP connects for regular operation.
A DataGuard connect descriptor will look more complicated as it will contain additional information for failover functionality, timeout definition and load balancing as well as all the nodes in the DataGuard partnership. This is an example of what would be expected for a DataGuard connect descriptor:
protect0 =
(DESCRIPTION=
(FAILOVER=on)(CONNECT_TIMEOUT=30) (TRANSPORT_CONNECT_TIMEOUT=30) (RETRY_COUNT=3)
(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST= dbserver1.example.com)(PORT=1521)
(ADDRESS=(PROTOCOL=TCP)(HOST= dbserver2.example.com)(PORT=1521))
)
(CONNECT_DATA=(SERVICE_NAME= protect)
)
)
You can see both database servers defined, and the service name is “protect”. Keep in mind that this is an example and timeout settings, retry counts and load balancer settings should come from the database administration team. These settings are typically in place to account for the time it takes for the database failover processes to occur and complete. Your DBA should be aware of those values and the values listed in this connection descriptor should reflect them accurately.
This entry needs to be in the tnsnames.ora file. To fix the issue of DLP not being able to connect to the failed over server, the oracle-thin entry in the jdbc.properties file also needs to reflect the multiple database server names as well as the correct service name.
The jdbc.properties file entry needs to look like this:
# JDBC connection information for the "oracle-thin" connection
jdbc.username.oracle-thin=protect
jdbc.driver.oracle-thin=oracle.jdbc.OracleDriver
jdbc.subprotocol.oracle-thin=oracle:thin
# jdbc.dbalias.oracle-thin=@(description=(address=(host==dbserver1.example.com)(protocol=tcp)(port=1521))(connect_data=(service_name=protect)))
jdbc.dbalias.oracle-thin=@(DESCRIPTION= (FAILOVER=on)(CONNECT_TIMEOUT=30) (TRANSPORT_CONNECT_TIMEOUT=30)
(RETRY_COUNT=3)
(ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver1.example.com)(PORT=1521))(ADDRESS
=(PROTOCOL=TCP)(HOST=dbserver2.example.com)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME= protect)))
A copy of the jdbc.properties file should be made before any changes are made to the file. Additionally, the original connection string can be commented out by using a pound (#) sign at the beginning of the line.
Once the changes are made, restart the DLP services on the Enforce Server and test the failover of the database again.
A good test is to failover to the secondary instance, log in to the Enforce Console and create a new test policy in DLP. Then failover back to the original primary instance and see if the new policy exists in the original instance. If the new policy is present, the test is successful. If the new policy is not present then the DataGuard application is not replicating correctly, contact your database management team for resolution.
Additional Information
Please note that both the tnsnames.ora entry and the jdbc.properties entry are required as they support different functions within DLP. The tnsnames.ora file is used by the installer for various software installation, maintenance and update tasks. The jdbc.properties entry is necessary for the operation of DLP to be able to interact with the database to perform regular functions around incident collection and actions, user management and server suite management. Because of this, both the tnsnames.ora entry and the jdbc.properties entry should match.
Feedback
