Create Oracle Pluggable Database Manually Using SQL Prompt

We can create a pluggable database in an existing multitenant database either using dbca or manually.

STEPS:

Connect to the container database:(ROOT)

[oracle@localhost ~]$ sqlplus sys/oracle@cdb1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 23 10:27:08 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
While creating PDB if you don’t mention FILE_NAME_CONVERT parameter then below error will come
SQL> create pluggable database dbwr admin user ram identified by ram;
create pluggable database dbwr admin user ram identified by ram                                                                        *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
Create PDB:(correct command)
SQL> create pluggable database dbwr admin user ram identified by ram FILE_NAME_CONVERT=('/home/oracle/app/oracle/oradata/cdb1/pdbseed','/home/oracle/app/oracle/oradata/cdb1/dbwr');


Pluggable database created.


SQL> select con_id,name,open_mode from v$Pdbs;

CON_ID NAME                OPEN_MODE
----------            -------------------
2 PDB$SEED               READ ONLY
3 ORCL                   READ WRITE
4 DBWR                   MOUNTED
-- Open the PDB
SQL> alter pluggable database dbwr open;

Pluggable database altered.



SQL> select con_id,name,open_mode from v$pdbs; 

CON_ID NAME                      OPEN_MODE
----------                       ----------
2 PDB$SEED                           READ ONLY
3 ORCL                               READ WRITE
4 DBWR                               READ WRITE

SQL> alter session set container=dbwr;

Session altered.

SQL> show con_name

CON_NAME
-------------
DBWR

SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------
/home/oracle/app/oracle/oradata/cdb1/dbwr/system01.dbf
/home/oracle/app/oracle/oradata/cdb1/dbwr/sysaux01.dbf

SQL>

Continue reading “Create Oracle Pluggable Database Manually Using SQL Prompt”

Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

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