Total Pageviews

Oracle FAQ's :

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>

                                                                                                                                                                 



Block device error  when trying to mount a filesystem:

[root@rac1 ~]# mount -a
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


                                                                                                                                                                 


hits count
Database | Oracle PartnerNetwork Blog