Oracle Database Discussions

Dear Experts,

I am trying access PDB using TNS connect string but getting an error.

service_names parameter is pointing the same value “asmdbprd” for PDB databases and CDB database.**

Do i need to configure different service names for CDB and PDB databases.

username and password is correct for the PDB

>> Error

[oracle@dgprim admin]$ sqlplus appsuser01/oracle@ASMDBPRD
SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 1 05:14:22 2024
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

>>

>>

Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs;
   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 ASMPRD1                        READ WRITE NO
        4 ASMPRD2                        READ WRITE NO

SQL> show parameter service;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      asmdbprd

>>>

SQL> show parameter local_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ASMDBPRD

>>>

SQL> alter session set container=ASMPRD1;
Session altered.

SQL> show parameter service;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      asmdbprd


SQL> show parameter local_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ASMDBPRD


SQL> alter session set container=ASMPRD2;
Session altered.

SQL> show parameter service;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      asmdbprd

SQL> show parameter local_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ASMDBPRD

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@dgprim admin]$
[oracle@dgprim admin]$

[oracle@dgprim admin]$ sqlplus appsuser01/oracle@ASMDBPRD
SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 1 05:04:34 2024
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

>> Tnsnames.ora

[oracle@dgprim admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))

LISTENER_ASMDBPRD =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))

CRMS =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = crms)
   )
 )
CDB1 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = cdb1)
   )
 )
ASMDBPRD =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = asmdbprd)
   )
 )
ASMDBPRD1 =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = asmdbprd1)
   )
 )
SANDBOX =
SANDBOX =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = sandbox)
   )
 )
LISTENER_SANDBOX =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))

LISTENER_CRMS =
 (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim)(PORT = 1521))

[oracle@dgprim admin]$

Thanks