Re: RE: [Spam] Re: Issues Connecting to PDB

I put the tnsnames.ora entry back and the tnsping resolves:

 

oracle_at_vmora00b7:DBA2CTST>tnsping dba2ptst

 

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 30-JUN-2022 08:13:04

 

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

 

Used parameter files:

/var/opt/oracle/sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.16.4.144)(PORT =1521))) (CONNECT_DATA = (SERVICE_NAME = DBA2PTST)))

OK (10 msec)

 

The service appears to be there:

 

SQL> alter session set container=dba2ptst

  2  ;

 

Session altered.

SQL> select service_id, name, network_name

  2  from v$active_services;

 

SERVICE_ID NAME

---------- ----------------------------------------------------------------

NETWORK_NAME

--------------------------------------------------------------------------------

         1 dba2ptst

dba2ptst

 

         3 DGRDTST2

DGRDTST2

 

 

SQL>

 

But I still get the error when trying to connect:

 

oracle_at_vmora00b7:DBA2CTST>sqlplus sys_at_dba2ptst as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 30 08:13:10 2022

Version 19.15.0.0.0

 

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

 

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

 

 

 

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

 

From: Andy Sayer <andysayer_at_gmail.com>
Sent: Wednesday, June 29, 2022 4:38 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: albert.y.balbekov_at_gmail.com; oracle-l_at_freelists.org
Subject: [Spam] Re: Issues Connecting to PDB

 

That’s because you’ve removed the entry with that name from the tnsnames. I *think* that entry looked fine. 

 

I suggest putting the entry back, and doing a tnsping to make sure it resolves how we’d expect (with the service name of the PDB). 

 

It’s worth double checking what services are active in the PDB, you could connect to the root container, do alter session set container=… and query v$active_services to these.

 

The original error suggests that your connection string was resolved to a local SID which is quite strange. Maybe a dodgy bit of white space in the config somewhere that wasn’t expected and Oracle is just silently deciding to treat it weirdly.

 

Hope that helps,

Andy

 

On Wed, 29 Jun 2022 at 12:52, Scott Canaan <srcdco_at_rit.edu> wrote:

I removed the pdb entry from the listener.ora file and the tnsnames.ora file and restarted the listener.

 

Now, I get:

 

oracle_at_vmora00b7:DBA2CTST>sqlplus sys_at_dba2ptst as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 29 15:51:29 2022

Version 19.15.0.0.0

 

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

 

Enter password:

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

 

 

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

 

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Al B.
Sent: Tuesday, June 28, 2022 10:21 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: oracle-l_at_freelists.org
Subject: Re: Issues Connecting to PDB

 

Hi Scott,

 

Listener.ora  does not need a separate entry for PDB, because there is no such thing as PDB instance. As soon as listener knows about container instance, the PDB inside that container should auto register with the listener as a service with the name equal to PDB name. Try to remove the PDB entry and see if it helps.

 

Good luck,

Albert Balbekov

 

On Tue, Jun 28, 2022 at 10:25 AM Scott Canaan <srcdco_at_rit.edu> wrote:

I am playing with containers to try to learn the nuances.  I have a container database and one pdb.  The particulars are:

 

Oracle version: 19.15

O/S: Red Hat 7

Container: dba2ctst

Pdb: dba2ptst

 

Both the container and pdb are open:

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 DBA2PTST                       READ WRITE NO

 

I set the system password in both (I think), using the following command:

 

SQL> alter user system identified by xxxxxxxxxxxxxxx container=all;

 

User altered.

 

When I try to connect to the pdb, I get an ORA-01034:

 

SQL> conn system_at_dba2ptst

Enter password:

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

Additional information: 4475

Additional information: -1748254857

Process ID: 0

Session ID: 0 Serial number: 0

 

 

Warning: You are no longer connected to ORACLE.

 

The alert log doesn’t have any errors indicating what file might be missing.

 

I’m thinking the issue is really with the listener and tnsnames.ora file.  The listener has the following entry:

 

   (SID_DESC =

      (GLOBAL_DBNAME = dba2ctst.rit.edu)

      (SID_NAME = DBA2CTST)

      (ORACLE_HOME = /oracle/app/product/19.0.0.0)

    )

    (SID_DESC =

#      (GLOBAL_DBNAME = dba2ptst.rit.edu)

      (SID_NAME = DBA2PTST)

      (SERVICE_NAME = DBA2PTST)

      (ORACLE_HOME = /oracle/app/product/19.0.0.0)

    )

 

The entries in the tnsnames.ora file are:

 

DBA2CTST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT =1521))

    )

    (CONNECT_DATA = (SERVICE_NAME = DBA2CTST))

  )

 

DBA2PTST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT =1521))

    )

    (CONNECT_DATA = (SERVICE_NAME = DBA2PTST))

  )

 

The lsnrctl status command returns:

 

Services Summary...

Service "DBA2PTST" has 1 instance(s).

  Instance "DBA2PTST", status UNKNOWN, has 1 handler(s) for this service...

Service "dba2ctst.rit.edu" has 1 instance(s).

  Instance "DBA2CTST", status UNKNOWN, has 1 handler(s) for this service...

 

I thought you were supposed to be able to connect directly to a pdb, as if it were a standalone database.  I’m sure it is something simple, I just can’t see it.

 

Thank you,

 

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration

Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520

srcdco_at_rit.edu | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.