Forum Stats

  • 3,840,360 Users
  • 2,262,592 Discussions
  • 7,901,243 Comments

Discussions

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

Toni Junior
Toni Junior Member Posts: 18
edited Jan 10, 2020 3:28AM in Multitenant

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/[email protected]: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 ?

Answers

  • Markus Flechtner
    Markus Flechtner Member Posts: 503 Bronze Trophy
    edited Dec 19, 2019 4:28AM

    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

  • Toni Junior
    Toni Junior Member Posts: 18
    edited Dec 19, 2019 5:00AM

    [[email protected] ~]# 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

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited Dec 19, 2019 11:18AM

    You do not have your connection information defined correctly.

    sqlplus test1/[email protected]: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/[email protected]//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

  • Toni Junior
    Toni Junior Member Posts: 18
    edited Dec 20, 2019 3:10AM

    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/[email protected]//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

  • Andris Perkons-Oracle
    Andris Perkons-Oracle Posts: 1,094 Employee
    edited Dec 20, 2019 3:57AM

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

    $ sqlplus test/[email protected]/xepdb1SQL*Plus: Release 18.0.0.0.0 - Production on Fri Dec 20 09:56:56 2019Version 18.4.0.0.0Copyright (c) 1982, 2018, Oracle.  All rights reserved.Verbunden mit: Oracle Database 18c Express Edition Release 18.0.0.0.0 - ProductionVersion 18.4.0.0.0SQL> 
  • Markus Flechtner
    Markus Flechtner Member Posts: 503 Bronze Trophy
    edited Dec 20, 2019 12:28PM

    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

  • User_9ATAD
    User_9ATAD Member Posts: 2 Red Ribbon
    edited Jan 10, 2020 3:28AM

    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

    Adam vonNiedaUser_5F5KM