How to connect to a pluggable database in 12c using SQL*plus:
There are 2 methods to connect to a 12c Pluggable database,
i. using alter session set container on the server
ii. creating a service and connecting thru tns
First method :-
[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_SID
cdb
[oracle@rac1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 14 21:43:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show con_name >>>>>>>>> Now we are connected to CDB - Container database <<<<<<<<<
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs >>>>>>>>> Lists the available PDB's under this container db <<<<<<<<<
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> alter session set container = pdb; >>>>>>>>> Setting container in the session level <<<<<<<<<
Session altered.
SQL> show con_name >>>>>>>>> Now we are connected to PDB - Pluggable database <<<<<<<<<
CON_NAME
------------------------------
PDB
SQL> show con_id
CON_ID
------------------------------
3
Second method :-
[oracle@rac1 admin]$ lsnrctl status >>>>>>>>> Checking listener status <<<<<<<<<
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-JAN-2014 21:55:12
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@rac1 admin]$
[oracle@rac1 admin]$ lsnrctl start >>>>>>>>> Starting the listener <<<<<<<<<
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-JAN-2014 21:55:18
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 14-JAN-2014 21:55:18
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/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cat tnsnames.ora >>>>>>>>> creating a tns entry for pdb <<<<<<<<<
pdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb)
)
)
SQL> alter pluggable database all open; >>>>>>>>> start all the pluggable databases <<<<<<<<<
Pluggable database altered.
SQL> show pdbs >>>>>>>>> Checking the status of the PDB's <<<<<<<<<
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> ! lsnrctl services >>>>>>>>> checking the listener services - PDB instance is registered & listed <<<<<<<<<
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-JAN-2014 22:09:53
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "cdb" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cdbXDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: rac1.india.com, pid: 5925>
(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=59729))
Service "pdb" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
SQL>
SQL> conn hr/manager@pdb >>>>>>>>> connect to PDB using connect string <<<<<<<<<
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> show user
USER is "HR"
SQL>
mount: /u01 is not a block device
mount: /u02 is not a block device
This error is occurred when there is misplaced entry in your fstab file. The /etc/fstab file should have entries as below. The /dev/sdb1 is the block device and /u01 is the mount point.
[root@rac1 ~]# cat /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults,size=1500m 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
/dev/sdb1 /u01 ext3 defaults,rw 0 0
/dev/sdc1 /u02 ext3 defaults,rw 0 0
There are 2 methods to connect to a 12c Pluggable database,
i. using alter session set container on the server
ii. creating a service and connecting thru tns
First method :-
[oracle@rac1 ~]$
[oracle@rac1 ~]$ echo $ORACLE_SID
cdb
[oracle@rac1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@rac1 ~]$
[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jan 14 21:43:31 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show con_name >>>>>>>>> Now we are connected to CDB - Container database <<<<<<<<<
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs >>>>>>>>> Lists the available PDB's under this container db <<<<<<<<<
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> alter session set container = pdb; >>>>>>>>> Setting container in the session level <<<<<<<<<
Session altered.
SQL> show con_name >>>>>>>>> Now we are connected to PDB - Pluggable database <<<<<<<<<
CON_NAME
------------------------------
PDB
SQL> show con_id
CON_ID
------------------------------
3
Second method :-
[oracle@rac1 admin]$ lsnrctl status >>>>>>>>> Checking listener status <<<<<<<<<
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-JAN-2014 21:55:12
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@rac1 admin]$
[oracle@rac1 admin]$ lsnrctl start >>>>>>>>> Starting the listener <<<<<<<<<
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-JAN-2014 21:55:18
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 14-JAN-2014 21:55:18
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/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@rac1 admin]$
[oracle@rac1 admin]$ cat tnsnames.ora >>>>>>>>> creating a tns entry for pdb <<<<<<<<<
pdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb)
)
)
SQL> alter pluggable database all open; >>>>>>>>> start all the pluggable databases <<<<<<<<<
Pluggable database altered.
SQL> show pdbs >>>>>>>>> Checking the status of the PDB's <<<<<<<<<
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> ! lsnrctl services >>>>>>>>> checking the listener services - PDB instance is registered & listed <<<<<<<<<
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-JAN-2014 22:09:53
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "cdb" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cdbXDB" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: rac1.india.com, pid: 5925>
(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.india.com)(PORT=59729))
Service "pdb" has 1 instance(s).
Instance "cdb", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
SQL>
SQL> conn hr/manager@pdb >>>>>>>>> connect to PDB using connect string <<<<<<<<<
Connected.
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> show user
USER is "HR"
SQL>
Block device error when trying to mount a filesystem:
[root@rac1 ~]# mount -amount: /u01 is not a block device
mount: /u02 is not a block device
This error is occurred when there is misplaced entry in your fstab file. The /etc/fstab file should have entries as below. The /dev/sdb1 is the block device and /u01 is the mount point.
[root@rac1 ~]# cat /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults,size=1500m 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
/dev/sdb1 /u01 ext3 defaults,rw 0 0
/dev/sdc1 /u02 ext3 defaults,rw 0 0
0 comments:
Post a Comment