Nothing but my learning and experience with Oracle..!: Creating a PDB Using SQL Plus

There are different methods that a DBA can use to create CDBs and PDBs. Here I am going to create one PDB database using SQL plus.

You can even use DBCA to create a PDB in an existing CDB, using the PDB$SEED database as a template for your new PDB.

Creating a PDB Using SQL Plus

To create a PDB from the SQL*Plus command line, use the CREATE PLUGGABLE DATABASE SQL statement. This PDB is also created from the PDB$SEED database (that will be always in read only mode and we cant alter its state) and includes the full data dictionary, which includes internal links to system-supplied root objects and metadata.You can create a PDB within a CDB, but not from within a non-CDB or from within another PDB.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

Use the following query to identify which datafile belongs to which container.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name
---------------------------------------------------------------------------------- ---------- ---------------
/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT
/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED
/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1
/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1
9 rows selected .

SQL>

If you are using OMF, then create new pluggable database using following syntax.

SQL> create pluggable database pdb2 admin user syspdb2 identified by syspdb2 roles=(CONNECT,DBA);

Pluggable database created.

SQL>

Note the clause "admin user syspdb2 identified by syspdb2". It means the PDB has a user called syspdb2 which is an admin user. The next line "roles=(CONNECT,DBA)" indicates that the user has the CONNECT and DBA roles. This becomes the DBA user of the PDB. Let's see that by connecting as that user and confirming that the roles have enabled.

if you are not using OMF,you have to use FILE_NAME_CONVERT parameter while creating PDB , for example

SQL>CREATE PLUGGABLE DATABASE pdb2 ADMIN USER syspdb2 IDENTIFIED BY syspdb2 FILE_NAME_CONVERT=('/oradata/cdb1/pdbseed/','/oradata/cdb1/pdb2/');

Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter at CDB level before calling the command without using the FILE_NAME_CONVERT clause.
SQL>CONN / AS SYSDBA

SQL>ALTER SESSION SET PDB_FILE_NAME_CONVERT='/oradata/cdb1/pdbseed/','/oradata/cdb1/pdb3/';

So while creating the PDB, you can simply create PDB without specifying the FILE_NAME_CONVERT clause. For example,

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER syspdb3 IDENTIFIED BY syspdb3;

Admin user clause:-

Use this clause to create an administrative user who can be granted the privileges required to perform administrative tasks on the PDB. For admin_user_name, specify name of the user to be created. Use the IDENTIFIED BY clause to specify the password for admin_user_name. Oracle Database creates a local user in the PDB and grants the PDB_DBA local role to that user. Oracle prefers not to use SYS(common user) to do administrative task in PDBs , instead use this admin user.

SQL> select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

----------------------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

11 rows selected.

SQL>

We could see two new datafile got created one for SYSTEM and another is for SYSAUX. There is a separate SYSTEM and SYSAUX tablespace for the root container of the CDB and each PDB.

Newly created pluggable database will be in MOUNTED mode, we need to open it.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 MOUNTED

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

Now we can see that new pluggable database PDB2 is detected by listener.

[oracle@mydb01]:[TDCDB] $ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2016 16:47:12

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 27-SEP-2016 19:51:35

Uptime 12 days 19 hr. 55 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/grid/product/12.1.0.2/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/mydb01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.81.155.146)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.81.155.155)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "-MGMTDBXDB" has 1 instance(s).

---output truncated--

Service "pdb1" has 1 instance(s).

Instance "TDCDB", status READY, has 1 handler(s) for this service...

Service "pdb2" has 1 instance(s).

Instance "TDCDB", status READY, has 1 handler(s) for this service...

Service "ptgracscan" has 1 instance(s).

Instance "-MGMTDB", status READY, has 1 handler(s) for this service...

The command completed successfully

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> alter session set container=pdb2;

Session altered.

SQL>

SQL> select USERNAME,COMMON from dba_users;

USERNAME COM

---------------------------- ---

SYSPDB2 NO

SYSTEM YES

SYS YES

--- output truncated---.

You could see syspdb2 is not a common user - ie it is available only to pdb2 .

I created one TNS entry PDB2 , that will connects to PDB2.

[oracle@ehrsvpitoel02]:[TDCDB] $ tnsping PDB2

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-OCT-2016 16:48:30

Used parameter files:

/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ptgracscan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB2)))

OK (0 msec)

[oracle@ehrsvpitoel02]:[TDCDB] $ sqlplus syspdb2/syspdb2@PDB2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 16:48:48 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from session_roles;

ROLE

----------------------

CONNECT

DBA

PDB_DBA

... output truncated ...

26 rows selected.

SQL> sho con_name

CON_NAME

------------------------------

PDB2

Since this user(syspdb2) is a DBA, it can alter the parameters of the PDB as needed: You can change parameters in specific containers to meet the application's requirement.

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

optimizer_index_cost_adj integer 100

SQL> alter system set optimizer_index_cost_adj = 50;

System altered.

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------------

optimizer_index_cost_adj integer 50

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 10 16:50:18 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,Advanced Analytics and Real Application Testing options

SQL> show parameter optimizer_index_cost_adj

NAME TYPE VALUE

------------------------------------ ----------- ------------------

optimizer_index_cost_adj integer 100

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL>

Note that not all the parameters can be modified in a PDB. A column ISPDB_MODIFIABLE in V$PARAMETER shows whether the parameter can be modified in a PDB or not. Here is an example:

SQL> select name, ispdb_modifiable from v$parameter where name in ( 'optimizer_index_cost_adj','audit_trail');

NAME ISPDB

----------------------------- -----

audit_trail FALSE

optimizer_index_cost_adj TRUE

SQL>

The audit_trail parameter is for the entire CDB; you can't modify them for individual PDBs.

Reference:- http://www.oracle.com/technetwork/articles/database/multitenant-part1-pdbs-2193987.html

Nothing but my learning and experience with Oracle..!

Pages

Monday, October 10, 2016

Creating a PDB Using SQL Plus

No comments:

Post a Comment

Popular Posts