Confusion of Service Name in Oracle Networking in 12c,19c, 21c,23c Oracle Database

-

********For Connection through SQL Developer (or any IDE) using service name************* 

Let Suppose,

sid,db_name,db_unique_name,instance_name = oradb (are same by default installation in Single Instances)

service_names (in DB) =oradb (by default) 

Suppose, we set service names (in DB) = ora





1. Service name inside oracle database.


show parameter service_names;


2. Global names should be same in case of Static Listener. 




3. For Connection through SQL developer using service should match above criteria.

Service name = "ora" will be used  





******************For Connection through "tnsnames.ora" alias name *********************

Let Suppose,

SID = oradb

service_names (in DB) =oradb (by default) 

Suppose, we set service names (in DB) = ora


We can give Alias name in tnsnames.ora file whatever like oradb, but there service name must be the ora.


If we set service name in tnsnames.ora or in db. We must bounce the Listener.

For Testing TNS connection is working or not.

sqlplus system@oradb ---> Note : oradb is TNS alias name. It should not be service name.





Thank you !

Ravi J.

Comments

Post a Comment

Popular posts from this blog

Enabling Autostart during server reboot of Oracle Database 19c filesystem on Oracle / Redhat Linux 7

-
============= Create a file /etc/init.d/dbora using dbora ================ vi /etc/init.d/dbora #! /bin/sh  -x # # This is my old script for auto-starting Oracle DB on Linux. # When I'm not using Cloud Databases, I now install the Oracle Database 21c RPM which includes its own script # # chkconfig: 2345 80 05 # description: start and stop Oracle Databases # # OL7: systemctl enable dbora # # Note: #   Change the awk pattern to match a DB of the ORACLE_HOME to be used #   Change the autostart entry of /etc/oratab to Y for any DB you want started; this is required by dbstart ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1 # Note: Change the value of ORACLE to the login name of the oracle owner ORACLE=oracle PATH=${PATH}:$ORACLE_HOME/bin export ORACLE_HOME PATH case $1 in 'start')         echo -n $"Starting Oracle: "         su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME" &         ;; 'stop') ...
Read more

Management of Users and privilleges in Oracle Database 12c / 19c in container & Pluggable database

-
 [oracle@LNX-D1V-ORCL01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 15 07:59:58 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show pdbs;     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 PDB                            READ WRITE NO   Create the common user using the CONTAINER clause SQL> create user c##user1 identified by c##user1 container=all; User created. SQL> grant create session to c#...
Read more

Oracle Temporary Tablespace Resize

-
  Description:- In this article I'm going to see how to resize Temp Tablespace on PDB database level Demo:- Login in to PDB Database SQL> alter session set container=ORCLPDB; Session altered. First Check Temporary Tablespace Usage From PDB Level:- SELECT A.tablespace_name tablespace, D.GB_total,SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_used,D.GB_total , SUM (A.used_blocks * D.block_size) / 1024 / 1024 / 1024 GB_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 / 1024 GB_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.GB_total; TABLESPACE GB_TOTAL GB_USED GB_FREE —————————— ———- ———- ———- TEMP               .034179688            0             .034179688 From CDB level:- To find CDB and PDB temporary tablespace files s...
Read more