CODEWITHSUNDEEP
linuxoracle-databasepdb
Piotr_aka_Pyjter
Piotr_aka_Pyjter

Reputation: 45

Not able connect to PDB, Oracle 19C Linux RH7.7 ORA-01034, ORA-27101

So I'm struggling with Oracle for almost 2 days and I gave up. I believe that I checked all solutions available at uncle google.

To summarize my issue:

  1. I have deployed Oracle 19C on Linux RH 7.7. SID = orcl, PDBS = orclpdb and PDB
  2. I can connect to Oracle instance from the host machine and a remote machine
  3. I can do alter session set container = PDB; ALTER PLUGGABLE DATABASE PDB OPEN;

But when I try login to pdb via

-Sqlplus(localhost) - oracle/product/19/dbhome/bin/sqlplus system/password@pdb

-Impdp (localhost)

-Sqldeveloper (remote) enter image description here

I received an error:

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: 4376
Additional information: -1774770425
Process ID: 0
Session ID: 0 Serial number: 0

My environment looks like:

show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
     2 PDB$SEED                       READ ONLY  NO        
     3 ORCLPDB                        MOUNTED              
     4 PDB                            READ WRITE NO     

$ echo $ORACLE_HOME
/oracle/product/19/dbhome
$ echo $ORACLE_SID
orcl

Listener:

cat /oracle/product/19/dbhome/network/admin/listener.ora
# listener.ora Network Configuration File: /oracle/product/19/dbhome/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
  (GLOBAL_DBNAME = orcl)
  (ORACLE_HOME = /oracle/product/19/dbhome)
  (SID_NAME = orcl)
)
(SID_DESC =
  (GLOBAL_DBNAME = orclpdb)
  (ORACLE_HOME = /oracle/product/19/dbhome)
  (SID_NAME = orclpdb)
)
(SID_DESC =
  (GLOBAL_DBNAME = pdb)
  (ORACLE_HOME = /oracle/product/19/dbhome)
  (SID_NAME = pdb)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.19.240)(PORT = 1521))
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

Tnsnames:

cat /oracle/product/19/dbhome/network/admin/tnsnames.ora
ORCL=
(description=
(address_list=
 (address = (protocol = TCP)(host = 10.103.19.240)(port = 1521))
 )
(connect_data =
(service_name=orcl)
)
)
PDB=
(description=
(address_list=
 (address = (protocol = TCP)(host = 10.103.19.240)(port = 1521))
 )
(connect_data =
(service_name=pdb)
)
)

Based on https://community.oracle.com/thread/2572501

cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

Listener status:

$ /oracle/product/19/dbhome/bin/lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2020 12:11:13

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

Starting /oracle/product/19/dbhome/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /oracle/product/19/dbhome/network/admin/listener.ora
Log messages written to /oracle/diag/tnslsnr/ies-phipdb-r77/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.19.240)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.103.19.240)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2020 12:11:13
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/19/dbhome/network/admin/listener.ora
Listener Log File         /oracle/diag/tnslsnr/ies-phipdb-r77/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.103.19.240)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orclpdb" has 1 instance(s).
  Instance "orclpdb", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
  Instance "pdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Any ideas why it doesn't work? and how to make it works?

Regards Piotr

Upvotes: 0

Views: 5312

Answers (3)

Piotr_aka_Pyjter
Piotr_aka_Pyjter

Reputation: 45

Thank you for your replay and the time which you have spent on it.

It was solved by adding the suffix of FQDN to PDB name in Listener.ora and tnsnames.ora.

Also to connect remotely I needed to add this suffix too.

Upvotes: 0

Bjarte Brandt
Bjarte Brandt

Reputation: 4461

In my Vagrant/Virtualbox environment:

rename listener.ora (use default settings)

[oracle@localhost ~]$ mv /u01/app/oracle/product/19.0.0/grid/network/admin/listener.ora /u01/app/oracle/product/19.0.0/grid/network/admin/listener.ora.1

restart listener

[oracle@localhost ~]$ lsnrctl stop; lsnrctl start; lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 11:10:41

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 11:10:41

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 11:10:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 11:10:41

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 11:10:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
The listener supports no services
The command completed successfully

there are no services exposed via the listener yet!

# A database background process is responsible for registration every 30 sec,
# but we can force service registration
[oracle@localhost ~]$ echo 'alter system register;' | sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 11:11:41 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
System altered.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

oh yes! services are there

[oracle@localhost ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2020 11:12:12

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-SEP-2020 11:10:41
Uptime                    0 days 0 hr. 1 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca.oradb3.private" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "af547de90c477ffee055000000000001.oradb3.private" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl.oradb3.private" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.oradb3.private" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1.oradb3.private" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

my CDB is named: orcl.oradb3.private

# now, let's connect!
[oracle@localhost ~]$ sqlplus system/Welcome1@localhost:1521/orcl.oradb3.private

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 11:21:05 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Sep 16 2020 11:20:48 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> col name for a30
SQL> r
  1* select con_id, name from v$pdbs

    CON_ID NAME
---------- ------------------------------
         2 PDB$SEED
         3 PDB1

my PDB is named: pdb1.oradb3.private

# now, let's connect!
[oracle@localhost ~]$ sqlplus system/Welcome1@localhost:1521/pdb1.oradb3.private

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 16 11:24:46 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Sep 16 2020 11:21:05 +02:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


SQL> sho con_id

CON_ID
------------------------------
3
SQL>

Make sure you can connect using EZConnect, thereafter alter your tnsnames.ora to reflect your exposed services.

Best of luck!

Upvotes: 0

Connor McDonald
Connor McDonald

Reputation: 11591

It is your SID_LIST_LISTENER config in listener.ora

Having a list of pluggables in there as "SID" means the listener is offering out those names as if they were instances. This is why you get the "shared realm etc etc", because those instances do not exist (and hence are started).

A typical listener.ora might look like

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1519))
    )
  )

SID_LIST_LISTENER19 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\product\19)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\product\19\bin\oraclr19.dll")
    )
  )

where you can see the entries there are just to support host/port and external programs. Each pluggable will happily register itself with the listener, which will then offer the pluggables out as services.

Upvotes: 2

Related Questions