How to set SERVICE_NAME in oracle for tnsnames.ora

How to set SERVICE_NAME in oracle for tnsnames.ora file


col NAME format a30
col VALUE format a30
SQL> select name, value from v$parameter where name like 'service%' or name like 'db_domain' or name like 'db_unique_name' ;

NAME VALUE
------------------------------ ------------------------------
db_domain
service_names aptdb
db_unique_name aptdb



Default value DB_UNIQUE_NAME.DB_DOMAIN if defined




ANUJ =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = APTDB) <<<<<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""> )
)



=======================================================================




col NAME format a30
col VALUE format a30
select name, value from v$parameter where name like 'service%' or name like 'db_domain' or name like 'db_unique_name'

NAME VALUE
------------------------------ ------------------------------
db_domain anuj.co.uk
service_names orcl.anuj.co.uk
db_unique_name orcl



default value DB_UNIQUE_NAME.DB_DOMAIN



ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = apt-amd-02.anuj.co.uk)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.anuj.co.uk) <<------default be="" br="" db_unique_name.db_domain="" value="" will=""> )
)



anuj1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.251)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.anuj.co.uk) <<<--default be="" br="" db_unique_name.db_domain="" value="" will=""> )
)


===================================

Example file for listener.ora

oracle@novagenesis$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.



LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.210 )(PORT = 1521))
)
)

#APTDB =
# (DESCRIPTION_LIST =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.4 )(PORT = 1521))
# )
# )


# to enable trace file
#TRACE_LEVEL_CLIENT = SUPPORT
#TRACE_TIMESTAMP_CLIENT = ON
#TRACE_LEVEL_LISTENER=16
#TRACE_TIMESTAMP_LISTENER=TRUE
#TRACE_DIRECTORY_LISTENER=/opt/oracle/product/10g/network/admin
#TRACE_FILELEN_LISTENER=500000
#TRACE_FILENO_LISTENER=10

======================================================


you can find out variable name following way as well


SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SQL> select sys_context('userenv','DB_NAME') from dual;




SQL> alter user sys identified by sys ;

User altered.




You have to connect sys user through tnsnames other wise you will get wrong result .


oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:56:48 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
SYS$USERS




so connect this way .

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus sys/sys@anuj1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:25:23 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl.ptus.co.uk

or


SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.PTUS.CO.UK





oracle@apt-amd-02:/opt/app/oracle/product/11.2/network/admin> sqlplus sys/sys@anuj1 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 16 08:25:23 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl.ptus.co.uk



SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------
ORCL.PTUS.CO.UK




SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl.ptus.co.uk


SQL> show parameter domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string ptus.co.uk





service_name = db_unique_name.db_domain

<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">

<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">***************************************************************************

<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">

<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">in Oracle 21c

u01/app/oracle/product/21.0.0/dbhome1

<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">

<------------- be="" br="" db_unique_name.db_domain="" default="" value="" will=""><------default be="" br="" db_unique_name.db_domain="" value="" will=""><--default be="" br="" db_unique_name.db_domain="" value="" will="">

 locate OraDB21Home1

locate -i  oradb21

https://docs.oracle.com/en/database/oracle/oracle-database/21/netrf/local-naming-parameters-in-tns-ora-file.html#GUID-12C94B15-2CE1-4B98-9D0C-8226A9DDF4CB

[grid@oragrid ~]$ cat /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdborcl)
    )
  )

LISTENER_CDBORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))


# start-delta orcl
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.lan)
    )
  )

# end-delta orcl
# start-delta v14mpc
LISTENER_V14MPC =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))

V14MPC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oragrid.lan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = v14mpc)
    )
  )

PDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=192.168.1.120) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1.localdomain)))
dbfs=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=192.168.1.106) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
# end-delta v14mpc