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:
- Oracle 21c Grid Infrastructure (21.4)
- Oracle 21c Database (21.4)
- Oracle 19c Database (19.13)
- All homes are read-only-Oracle-Homes (default in Oracle Database 21c, for 19c I ran “roohctl -enable”)
- 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:
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 188.8.131.52.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.