DataGuard, Read-Only Oracle Home, $TNS_ADMIN and ORA-12154

I’m currently setting up a MAA lab with Oracle 19c and 21c databases running on 2 node cluster with Oracle 21c Grid Infrastructure. When creating the DataGuard configuration I ran into some strange ORA-12154 error messages.

My configuration in short:

1. Oracle 21c Grid Infrastructure (21.4)
2. Oracle 21c Database (21.4)
3. Oracle 19c Database (19.13)
4. All homes are read-only-Oracle-Homes (default in Oracle Database 21c, for 19c I ran “roohctl -enable”)
5. My Oracle net configuration is centralized in my TNS_ADMIN directory /u00/app/oracle/network/admin

As I’m using $TNS_ADMIN there are no *.ora files in $ORACLE_BASE/homes/<HOME_NAME>/network/admin

“tnsping” works fine for all entries in tnsnames.ora and I can connect to the instances with sqlplus via Oracle Net. So I thought my configuration is OK.

I started dgmgrl and connected to the primary database:

I ran:

DGMGRL> create configuration NCDB_DG as primary database is NCDB_S1 connect identifier is 'NCDB_S1_DG.markusdba.local';
Configuration "ncdb_dg" created with primary database "ncdb_s1"

Then I wanted to add the standby database:

DGMGRL> add database NCDB_S2 as connect identifier is 'NCDB_S2_DG.markusdba.local' maintained as physical;

But the result was:

Error: ORA-12154: TNS:could not resolve the connect identifier specified

I checked the tnsnames.ora entry and tested it again:

oracle@freki:/u00/app/oracle/homes/OraDB21Home1/network/ [rdbms19] tnsping NCDB_S2_DG.markusdba.local

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 07-DEC-2021 22:21:57

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = hugin.markusdba.local)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = munin.markusdba.local)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = NCDB_S2_DGMGRL.markusdba.local) (SERVER=DEDICATED)))
OK (0 msec)

And the connect via sqlplus worked fine, too.

So tnsping and sqlplus OK, but dgmgrl not. So it seems that dgmgrl does not evaluate TNS_ADMIN.

I finally solved the issue by creating a symbolic link from $ORACLE_BASE/homes/<HOME_NAME>/network to $TNS_ADMIN

oracle@freki:/u00/app/oracle/homes/OraDB21Home1/network/ [rdbms19] mv admin admin.backup
oracle@freki:/u00/app/oracle/homes/OraDB21Home1/network/ [rdbms19] ln -s $TNS_ADMIN admin

both the for 19c and the 21c ORACLE_HOME. And after I could continue my DataGuard configuration 🙂

Note (December 2022): I think, this issue is another reason for using Easy Connect instead of the tnsnames.ora file for defining the database connections for DataGuard.