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 Dec 19, 2019 9:23 AM

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

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 Dec 19, 2019 9:28 AM (in response to Toni Junior)

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
Like Show 0 Likes(0)

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

Toni Junior Dec 19, 2019 10:00 AM (in response to Markus Flechtner)

[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
Like Show 0 Likes(0)

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

BPeaslandDBA Dec 19, 2019 4:18 PM (in response to Toni Junior)

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
Like Show 0 Likes(0)

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

Toni Junior Dec 20, 2019 8:10 AM (in response to BPeaslandDBA)

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
Like Show 0 Likes(0)

Actions

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

Andris Perkons-Oracle Dec 20, 2019 8:57 AM (in response to BPeaslandDBA)

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 Dec 20, 2019 5:28 PM (in response to Toni Junior)

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
Like Show 0 Likes(0)

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

3777523 Jan 10, 2020 8:28 AM (in response to Toni Junior)

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
Like Show 0 Likes(0)

Actions

Go to original post