Connecting
to the root of a container database is the same as that of any
previous database instance. On the database server you can use OS
Authentication.
[oracle@vmdb12c
~]$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Mon Nov 30 11:38:44 2015
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL>
show con_name
CON_NAME
——————————
CDB$ROOT
The
V$SERVICES views can be used to display available services from the
database.
SQL>
select name,pdb from v$services order by 2;
NAME
PDB
——————– ———-
zwcXDB
CDB$ROOT
SYS$BACKGROUND CDB$ROOT
SYS$USERS
CDB$ROOT
ptest12c PTEST12C
ptest12c_1
PTEST12C_1
ptest12c_2 PTEST12C_2
6
rows selected.
The
lsnrctl utility allows you to display the available services from the
command line.
[oracle@vmdb12c
~]$ lsnrctl status
LSNRCTL
for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:17:38
Copyright
(c) 1991, 2014, Oracle. All rights reserved.
Connecting
to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS
of the LISTENER
------------------------
Alias
LISTENER_TEST12C
Version
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start
Date 16-NOV-2015 18:02:53
Uptime
14 days 18 hr. 14 min. 45 sec
Trace
Level off
Security
ON: Local OS Authentication
SNMP
OFF
Listener
Parameter File
/data/amit/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener
Log File
/data/amit/app/oracle/diag/tnslsnr/bigdata2/listener_test12c/alert/log.xml
Listening
Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost.localdomain)(PORT=5500))(Security=(my_wallet_directory=/data/amit/app/oracle/admin/test12c/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services
Summary...
Service
"PLSExtProc" has 1 instance(s).
Instance
"PLSExtProc", status UNKNOWN, has 1 handler(s) for this
service...
Service
"PTEST12C" has 2 instance(s).
Instance
"PTEST12C", status UNKNOWN, has 1 handler(s) for this
service...
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Service
"ptest12c_1" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Service
"ptest12c_2" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Service
"ptest12c_3" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Service
"test12c" has 2 instance(s).
Instance
"test12c", status UNKNOWN, has 1 handler(s) for this
service...
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Service
"test12cXDB" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
The
command completed successfully
[oracle@vmdb12c
~]$ lsnrctl service
LSNRCTL
for Linux: Version 12.1.0.2.0 - Production on 01-DEC-2015 12:19:05
Copyright
(c) 1991, 2014, Oracle. All rights reserved.
Connecting
to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services
Summary...
Service
"PLSExtProc" has 1 instance(s).
Instance
"PLSExtProc", status UNKNOWN, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:0 refused:0
LOCAL
SERVER
Service
"PTEST12C" has 2 instance(s).
Instance
"PTEST12C", status UNKNOWN, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:71 refused:0
LOCAL
SERVER
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:1 refused:0 state:ready
LOCAL
SERVER
Service
"ptest12c_1" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:1 refused:0 state:ready
LOCAL
SERVER
Service
"ptest12c_2" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:1 refused:0 state:ready
LOCAL
SERVER
Service
"ptest12c_3" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:1 refused:0 state:ready
LOCAL
SERVER
Service
"test12c" has 2 instance(s).
Instance
"test12c", status UNKNOWN, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:10 refused:0
LOCAL
SERVER
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Handler(s):
"DEDICATED"
established:1 refused:0 state:ready
LOCAL
SERVER
Service
"test12cXDB" has 1 instance(s).
Instance
"test12c", status READY, has 1 handler(s) for this
service...
Handler(s):
.......
The command completed successfully
Connections
using services are unchanged from previous versions.
easy
connect
[oracle@vmdb12c
~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_2
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Last
Successful login time: Tue Dec 01 2015 13:00:19 +05:30
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL>
show con_name
CON_NAME
------------------------------
PTEST12C_2
[oracle@vmdb12c
~]$ sqlplus system/oracle123@202.137.235.171:1521/PTEST12C_3
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Dec 1 15:24:39 2015
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Last
Successful login time: Tue Dec 01 2015 13:00:19 +05:30
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL>
show con_name
CON_NAME
------------------------------
PTEST12C_3
tnsnames.ora
[oracle@vmdb12c
~]$sqlplus
system/oracle@PTEST12C_3
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Last
Successful login time: Tue Dec 01 2015 15:24:39 +05:30
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL>
show con_name
CON_NAME
------------------------------
PTEST12C_3
[oracle@vmdb12c
~]$sqlplus
system/oracle@PTEST12C_1
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Dec 1 15:47:25 2015
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Last
Successful login time: Tue Dec 01 2015 15:24:39 +05:30
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL>
show con_name
CON_NAME
------------------------------
PTEST12C_1
tnsnames.ora
TEST12C
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= test12c)
)
)
PTEST12C
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= PTEST12C)
)
)
PTEST12C_1
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= PTEST12C_1)
)
)
PTEST12C_3
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA
=
(SERVER
= DEDICATED)
(SERVICE_NAME
= PTEST12C_3)
)
)
LISTENER_TEST12C
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
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.
[oracle@vmdb12c
~]$ sqlplus
"/ as sysdba"
SQL*Plus:
Release 12.1.0.2.0 Production on Tue Dec 1 15:53:10 2015
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application
Testing options
SQL>
alter session set container=PTEST12C_1;
Session
altered.
SQL>
show pdbs
CON_ID
CON_NAME OPEN MODE RESTRICTED
----------
------------------------------ ---------- ----------
4
PTEST12C_1 READ WRITE NO
SQL>
alter session set container=PTEST12C_2;
Session
altered.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
5 PTEST12C_2 READ WRITE NO
SQL>
alter session set container=cdb$root;
Session
altered.
SQL>
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2
PDB$SEED READ ONLY NO
4 PTEST12C_1 READ WRITE NO
5 PTEST12C_2 READ WRITE NO
6 PTEST12C_3 READ WRITE NO
SQL>
select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
CDB$ROOT
SQL>
alter session set container=PTEST12C_1;
Session
altered.
SQL>
select sys_context('USERENV','CON_NAME') from dual;
SYS_CONTEXT('USERENV','CON_NAME')
——————————————————————————–
PTEST12C_1
I
hope this article helped you. Your
suggestions/feedback are most welcome.
Keep
learning... Have a great day!!!
