Friday, January 12, 2024

Where is my TNSNAMES.ORA from Oracle database 21c

Oracle has started from Oracle version 18c to use Oracle read-only software trees. See my blog Configuring Read Only OracleHome - 18c 

From Oracle version 21c there are no distinctions between software trees in read-only and read-write modes. As of now, the default configuration is set to utilize the read-only mode introduced in Oracle 18c.

When installing a CDB through DBCA on Oracle Database 21c, you will observe the presence of TNSNAMES.ORA

The question then arises: where is it located?

[oracle@ol8-21-rac1 admin]$ ${GRID_HOME}/bin/crsctl status resource -w " NAME co cdbrac " -t
Name           Target  State        Server                   State details
Cluster Resources
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
      2        ONLINE  ONLINE       ol8-21-rac1              Open,HOME=/u01/app/o

We see we have a CDB called CDBRAC lets see if there is a TNSNAMES.ORA file

[oracle@ol8-21-rac1 admin]$ tnsping cdbrac

TNS Ping Utility for Linux: Version - Production on 12-JAN-2024 09:38:29

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-scan.joordsdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cdbrac)))
OK (10 msec)

Where is this TNSNAMES.ORA file located?

[oracle@ol8-21-rac1 admin]$ echo $ORACLE_HOME/network/admin/

ls -trl $ORACLE_HOME/network/admin/
total 4
-rw-r--r--. 1 oracle oinstall 1624 Feb 18  2020 shrept.lst
drwxr-xr-x. 2 oracle oinstall   64 Jan 11 09:48 samples
So lets look at the files starting with "ORABASE*" in the bin directory of the ORACLE_HOME

[oracle@ol8-21-rac1 admin]$ ls -trl $ORACLE_HOME/bin/orabas*
-rwxr-xr-x. 1 oracle oinstall   36208 Jul 27  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/orabase
-rwxr-x---. 1 oracle oinstall 1858568 Jul 27  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/orabasehome
-rwxr-x---. 1 oracle oinstall 1858576 Jul 27  2021 /u01/app/oracle/product/21.0.0/dbhome_1/bin/orabaseconfig

[oracle@ol8-21-rac1 admin]$ ls -d $(orabase)
[oracle@ol8-21-rac1 admin]$ ls -d $(orabasehome)
[oracle@ol8-21-rac1 admin]$ ls -d $(orabaseconfig)
we see that the $(orabasehome) is not equal to the $ORACLE_HOME which means that the TNSNAMES.ORA file will be located outside the $ORACLE_HOME

[oracle@ol8-21-rac1 admin]$ locate tnsnames.ora

[oracle@ol8-21-rac1 admin]$ pwd  $(orabaseconfig)/network/admin

[oracle@ol8-21-rac1 oracle]$ ls -trl $(orabasehome)/network/admin
total 4
-rw-r-----. 1 oracle oinstall 2914 Jan 11 15:08 tnsnames.ora
[oracle@ol8-21-rac1 oracle]$ cat $(orabasehome)/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

    (ADDRESS = (PROTOCOL = TCP)(HOST = ol8-21-scan.joordsdomain)(PORT = 1521))
      (SERVICE_NAME = cdbrac)

Setting the TNSNAMES.ORA as was before the following steps needs to be done

  1. Create a TNS_ADMIN environment variable set the $ORACLE_HOME/netwerk/admin
  2. Copy the $(orabasehome)/network/admin/tnsnames.ora to $TNS_ADMIN/tnsnames.ora
  3. Update your profile by add the TNS_ADMIN variable
  4. Update the ORACLE clusterware attribute, by adding the TNS_ADMIN environment variable to the CDB resource

No comments:

Post a Comment