7 Replies Latest reply on Jan 10, 2020 8:28 AM by 3777523

    ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB

    Toni Junior

      Hello , I am trying to connect to the PDB I could connect to 2 days ago but now I can't anymore .

       

      pdb is opened ,I can do alter session to switch to that pdb from root ,  and then  I can query sys views or dba tables .

       

       

      SQL>  select name, open_mode from v$pdbs

       

      NAME OPEN_MODE

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

      PDB1 READ WRITE

       

       

      But when I try to connect with sqldeveloper or sqlplus , it fails , as the instance is down .

       

      sqlplus test1/password@hostname.com:1521/PDB1

       

      SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 19 09:14:44 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved.

       

      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: 3701 Additional information: -1520736149

      Process ID: 0 Session ID: 0 Serial number: 0

       

      Is this about the 'services'  maybe ?

       

      If it is , how can I start the service for that pdb ?

       

       

        • 1. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
          Markus Flechtner

          Could you please post the result of "lsnrctl status" on your database server?

          (If you are using a Non-Default listener, do not forget to add the listener name "lsrnctl status <listenername>").

           

          Regards

          Markus

          • 2. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
            Toni Junior

            [oracle@hostname ~]# lsnrctl status

             

             

            LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-DEC-2019 09:52:59

            Copyright (c) 1991, 2016, Oracle. All rights reserved.

             

            Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.com)(PORT=1521)))

            STATUS of the LISTENER

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

            Alias LISTENER

            Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production

            Start Date 25-NOV-2019 17:07:30

            Uptime 23 days 16 hr. 45 min. 28 sec

            Trace Level user

            Security ON: Local OS Authentication

            SNMP OFF

            Listener Parameter File /app/oracle/product/12.2/db_1/network/admin/listener.ora

            Listener Log File /app/oracle/diag/tnslsnr/hostname/listener/alert/log.xml

            Listener Trace File /app/oracle/diag/tnslsnr/hostname/listener/trace/ora_2109_140118273466816.trc

            Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=1521)))

                                           (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

            Services Summary...

            Service "PDB1" has 1 instance(s).

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

            Service "TST1" has 1 instance(s).

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

            The command completed successfully

            • 3. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
              BPeaslandDBA

              You do not have your connection information defined correctly.

               

              sqlplus test1/password@hostname.com:1521/PDB1

               

              This is called Oracle EZ Connect where you can specify the hostname, port, and service name (not the PDB as I will discuss in a minute) instead of the tnsalias. However, you must precede all of that with "//". So the EZ Connect string should look like:

               

              sqlplus test1/password@//hostname:1521/PDB1

               

              As I said above, the format is "//hostname:port/service_name"

               

              You specified a service name of "PDB1", not a PDB name. But it works because once a PDB is opened, the database registers a service with the listener that has the same name as the PDB. I just wanted you to be aware of the technical differentiation here.

               

              Cheers,
              Brian

              • 4. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
                Toni Junior

                Dear Bryan ,

                thank you for your input.

                 

                In my first post it's visible that the PDB name is actually PDB1.

                 

                Now, I tried to connect as you suggested  , but the error is still the same :

                 

                sqlplus test1/password@//hostname.com:1521/PDB1

                 

                SQL*Plus: Release 12.2.0.1.0 Production on Fri Dec 20 07:55:56 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved.

                 

                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: 3701 Additional information: -1520736149

                Process ID: 0 Session ID: 0 Serial number: 0

                 

                Do you suggest that I create a service associated with that  pdb ?

                 

                Although , few days ago, all of that worked , but now , I'm not sure what's changed so this doesn't work anymore.

                 

                Here are results of queries when I'm connected as sysdba to a root container :

                 

                SQL> select service_id,name,pdb,con_id from cdb_services order by con_id

                SERVICE_ID NAME PDB CON_ID

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

                1 SYS$BACKGR CDB$ROOT 1

                4 TST1 CDB$ROOT 1

                3 TST1XD CDB$ROOT 1

                2 SYS$USERS CDB$ROOT 1

                7 pdb1 PDB1 3

                 

                 

                SQL> SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') FROM DUAL;

                SYS_CONTEXT('USERENV','CON_NAME')

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

                CDB$ROOT

                • 5. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
                  Andris Perkons-Oracle

                  The use of "//" is optional, so that's not the problem:

                   

                  $ sqlplus test/test@192.168.255.25/xepdb1
                  
                  SQL*Plus: Release 18.0.0.0.0 - Production on Fri Dec 20 09:56:56 2019
                  Version 18.4.0.0.0
                  
                  Copyright (c) 1982, 2018, Oracle.  All rights reserved.
                  
                  Verbunden mit: 
                  Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
                  Version 18.4.0.0.0
                  
                  SQL> 
                  
                  • 6. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
                    Markus Flechtner

                    Hi,

                     

                    you are sure that the client resolves the hostname to the correct IP address and that the listener is listening to the correct address?

                     

                     

                    Regards

                    Markus

                    • 7. Re: ERROR: ORA-01034: ORACLE not available while trying to connect to the PDB
                      3777523

                      I had similar issue... in my case setting local_listener PDB level fixed my problem:

                       

                      SQL > alter session set container = XXX;

                      SQL > alter system set local_listener = XXX scope=both;

                       

                      good luck

                      g