patternMinor
Connecting to Oracle Pluggable database using service name
Viewed 0 times
connectingpluggabledatabaseservicenameusingoracle
Problem
I installed Oracle 12c on windows using all the defaults but choosing to use a pluggable database and to install the sample data. The container database and SID name is ORCL and the pluggable database is PDBORCL.
I noticed that using SQL Developer I can connect to the pluggable database by using its name as the service name. Also I can connect to the container database using its name either as SID or service name.
Looking at my tnsnames.ora I found that only ORCL is mentioned as a service name. Does oracle register pluggable databases as service names automatically or there is a configuration file that I am missing?
I noticed that using SQL Developer I can connect to the pluggable database by using its name as the service name. Also I can connect to the container database using its name either as SID or service name.
Looking at my tnsnames.ora I found that only ORCL is mentioned as a service name. Does oracle register pluggable databases as service names automatically or there is a configuration file that I am missing?
Solution
It registers them automatically.
Very easy to test...
Listener status shows no PDB:
Create a new PDB:
It is now registered with the listener:
Very easy to test...
Listener status shows no PDB:
$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:41:15
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 04-MAR-2015 15:03:10
Uptime 8 days 18 hr. 38 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$ echo $ORACLE_SID
CDB1
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0Create a new PDB:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 13 09:41:27 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
create pluggable database pdb_phil
admin user phil identified by phil
3 file_name_convert = ('/pdbseed/', '/pdb_phil/');
Pluggable database created.
SQL> col name for a20
SQL> col open_mode for a10
SQL> select con_id, dbid, name, open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ----------
2 4096360259 PDB$SEED READ ONLY
3 3580684491 PDB_PHIL MOUNTED
SQL> quitIt is now registered with the listener:
$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:44:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 04-MAR-2015 15:03:10
Uptime 8 days 18 hr. 40 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "pdb_phil" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$Code Snippets
$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:41:15
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 04-MAR-2015 15:03:10
Uptime 8 days 18 hr. 38 min. 6 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$ echo $ORACLE_SID
CDB1
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Mar 13 09:41:27 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
create pluggable database pdb_phil
admin user phil identified by phil
3 file_name_convert = ('/pdbseed/', '/pdb_phil/');
Pluggable database created.
SQL> col name for a20
SQL> col open_mode for a10
SQL> select con_id, dbid, name, open_mode from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- -------------------- ----------
2 4096360259 PDB$SEED READ ONLY
3 3580684491 PDB_PHIL MOUNTED
SQL> quit$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 13-MAR-2015 09:44:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 04-MAR-2015 15:03:10
Uptime 8 days 18 hr. 40 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "CDB1" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "CDB1XDB" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
Service "UPGR" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "UPGRXDB" has 1 instance(s).
Instance "UPGR", status READY, has 1 handler(s) for this service...
Service "pdb_phil" has 1 instance(s).
Instance "CDB1", status READY, has 1 handler(s) for this service...
The command completed successfully
[CDB1] oracle@localhost:/u01/app/oracle/product/11.2.0
$Context
StackExchange Database Administrators Q#95149, answer score: 2
Revisions (0)
No revisions yet.