Update Readiness Tool Error: "Unable to establish db connection for user protect - invalid connection identifier."

Products

• Data Loss Prevention Enforce
• Data Loss Prevention

Issue/Introduction

The Data Loss Prevention (DLP) Update Readiness Tool(URT) is failing after performing the following steps to prepare the tool.

1. Grant permissions to "Protect" user see Verify or Grant Oracle Database Permissions to Protect User or Upgrade Readiness User (broadcom.com).
2. Create an additional update readiness user
3. Use the create_user.sql script
4. Transfer the URT zip file to the Enforce server
5. Unzip the URT contents
6. Verify connections to Oracle Database for both the protect user and new update readiness user (see step 2), and correct any incorrect usernames and passwords.

Error: unable to establish db connection for user protect - invalid connection identifier.

Cause

The service name used with URT does not resolve to the service_name parameter in the Oracle database.

 

Resolution

Confirm the service_name using one of the following methods

Review the tnsnames.ora file for the service_name. In Oracle the file can be found here: \oracle\product\oracle_version\db_1\NETWORK\ADMIN\tnsnames.ora

Example:

                 PROTECT = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = protect) ) )

Review the jdbc.properties file on Enforce. The file can be found here: \Protect\config\jdbc.properties

  Example:

                 # JDBC connection information for the "oracle-thin" connection jdbc.username.oracle-thin=protect jdbc.driver.oracle-thin=oracle.jdbc.OracleDriver # use the P6Spy driver in order to see the SQL generated by OJB # jdbc.driver.oracle-thin=com.p6spy.engine.spy.P6SpyDriver jdbc.subprotocol.oracle-thin=oracle:thin jdbc.dbalias.oracle-thin=@(description=(address=(host=127.0.0.1)(protocol=tcp)(port=1521))(connect_data=(sid=protect)))

Review oracle parameters for the configured service_name via sqlplus.

1. Open cmd or terminal
2. run the following command: sqlplus /nolog
3. Connect to oracle: connect sys as sysdba
4. Run this query to confirm the service_name parameter: select value from v$parameter where name='service_names';

Example:

                 C:\Users\Administrator>sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn sys as sysdba Enter password: Connected. SQL> select value from v$parameter where name='service_names'; VALUE -------------------------------------------------------------------------------- protect

 

Additional Information

If after verifying all the above and you still get the error: Update Readiness Tool Error: "Unable to establish db connection for user protect - invalid connection identifier."

Then, make sure that you have the environment variable for the Oracle client Path is set properly and pointing to the correct location. 

It is also good to add ORACLE_HOME variable with the same path pointing Oracle client (without bin folder):