Connecting to a Container Database (CDB)
Connecting to the root of a container database is the same as database instance.
On the database server you can use OS Authentication.
[oracle@ram ~]$ export ORACLE_SID=dbwr [oracle@ram ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:10:26 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8795904 bytes Variable Size 671090944 bytes Database Buffers 1828716544 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL>
You can connect to other common users in similar way.
SQL> conn system/oracle Connected. SQL>
The v$services views can be used to display available services from the database.
SQL> col name for a20 SQL> col pdb for a20 SQL> select name , pdb from v$services order by name; NAME PDB -------------------- -------------- SYS$BACKGROUND CDB$ROOT SYS$USERS CDB$ROOT dbwr.carrierzone.com CDB$ROOT dbwrXDB CDB$ROOT pdb1.carrierzone.com PDB1 pdb5.carrierzone.com PDB5 pdb6.carrierzone.com PDB6 7 rows selected.
The lsnrctl utility allows you to display the available services from the command line
[oracle@ram ~]$ lsnrctl service LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-OCT-2018 02:19:58 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.localdomain.com)(PORT=1521))) Services Summary... Service "777d95e41a572d82e053dd971d40e224.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "77813d4b379738f5e0536501a8c05864.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "77f4b206457338a1e0536501a8c0ed42.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "dbwr.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "dbwrXDB.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER <machine: ram.localdomain.com, pid: 32509> (ADDRESS=(PROTOCOL=tcp)(HOST=ram.localdomain.com)(PORT=58780)) Service "pdb1.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb5.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "pdb6.carrierzone.com" has 1 instance(s). Instance "dbwr", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully.
Connections using services
[oracle@ram admin]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:30:30 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> conn system/oracle@dbwr Connected. SQL>
The connection using a TNS requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file
[oracle@ram admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. DBWR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = dbwr.carrierzone.com) ) )
Displaying the Current Container
The show con_name and show con_id commands in SQL*Plus display the current container name and ID respectively.
SQL> sho con_name CON_NAME ----------------- CDB$ROOT SQL> show con_id CON_ID ------------------ 1 SQL>
They can also be retrieved using the sys_context function.
SQL> select sys_context('userenv','con_name') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------------------------
CDB$ROOT
SQL> select sys_context('userenv','con_id') from dual;
SYS_CONTEXT('USERENV','CON_ID')
---------------------------------------------------
1
SQL>
Switching Between Containers
When logged in to the CDB as an appropriately privileged user, the alter session command can be used to switch between containers within the container database.
SQL> conn / as sysdba Connected. SQL> alter pluggable database all open; Pluggable database altered. SQL> alter session set container = pdb5; Session altered. SQL> sho con_name CON_NAME ------------------------------ PDB5 SQL> alter session set container =cdb$root; Session altered. SQL> sho con_name CON_NAME ------------------------------ CDB$ROOT SQL>
Connecting to a Pluggable Database (PDB)
Direct connections to pluggable databases must be made by using a service. Each pluggable database automatically registers a service with the listener.
This is how any application will connect to a pluggable database, as well as administrative connections.
[oracle@ram ~]$ export ORACLE_SID=dbwr [oracle@ram ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:47:52 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> conn / as sysdba Connected. SQL> conn system/oracle@pdb5 Connected. SQL>
The connection using a TNS alias requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file, such as the one shown below.
[oracle@ram admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. PDB5 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb5.carrierzone.com) ) )
PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB.
All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the create session privilege to enable connections.
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
