How to create Pluggable database from SEED database ?

How to Create   Pluggable database ??

To check current environment of CDB and PDB


SET LINESIZE 200 PAGESIZE 100 SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100
SELECT
 'DB_NAME: ' ||sys_context('userenv', 'db_name') ||
 ' \CDB:-' ||(select cdb from v$database) ||
 ' \AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \USER: ' ||sys_context('userenv', 'current_user') ||
 ' \CONTAINER:' ||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB') "DB DETAILS"
 FROM DUAL ;


DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: orcl12c \CDB:-YES \AUTH_ID: oracle \USER: SYS \CONTAINER:ANUJ


set linesize 200 pagesize 200 
col FILE_NAME for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

 CON_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf
 5 SYSTEM /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf
 5 SYSAUX /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf
 5 USERS /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf
 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf

19 rows selected.


How to check seed database path ?


SQL> alter session set exclude_seed_cdb_view=false;

Session altered.

show parameter EXCLUDE_SEED_CDB_VIEW

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
exclude_seed_cdb_view boolean FALSE


Now seed database file ..

set linesize 200 pagesize 200 
col FILE_NAME for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

 CON_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf
 2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
 2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
 5 SYSTEM /u01/app/oracle/oradata/orcl12c/ANUJ/system01.dbf
 5 SYSAUX /u01/app/oracle/oradata/orcl12c/ANUJ/sysaux01.dbf
 5 USERS /u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf
 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf

21 rows selected.




 alter system set "EXCLUDE_SEED_CDB_VIEW"=FALSE ;

System altered.


set linesize 200 pagesize 200 
col FILE_NAME for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

 CON_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
 2 SYSTEM /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
 2 SYSAUX /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
 4 SYSTEM /u01/app/oracle/oradata/orcl12c/ORDS/system01.dbf
 4 SYSAUX /u01/app/oracle/oradata/orcl12c/ORDS/sysaux01.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users02.dbf
 4 USERS /u01/app/oracle/oradata/orcl12c/ORDS/users01.dbf
 3 SYSTEM /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
 3 SYSAUX /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
 3 USERS /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
 3 EXAMPLE /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
 3 APEX_1851336378250219 /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
 3 APEX_5457999048253711 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
 3 APEX_5500333564645084 /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf
 1 SYSTEM /u01/app/oracle/oradata/orcl12c/system01.dbf
 1 SYSAUX /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
 1 USERS /u01/app/oracle/oradata/orcl12c/users01.dbf
 1 UNDOTBS2 /u01/app/oracle/oradata/orcl12c/undotbs2.dbf
 1 APEX /u01/app/oracle/oradata/orcl12c/apex01.dbf

18 rows selected.



SET SERVEROUTPUT ON
COLUMN "RESTRICTED" FORMAT A10
select 
 v.name, 
 v.open_mode, 
 nvl(v.restricted, 'n/a') "RESTRICTED", 
 d.status from v$pdbs v , dba_pdbs d
where 1=1
and v.guid=d.guid
order by v.create_scn;

NAME OPEN_MODE RESTRICTED STATUS
------------------------------ ---------- ---------- ---------
PDB$SEED READ ONLY NO NORMAL
ORCL READ WRITE NO NORMAL
ORDS READ WRITE NO NORMAL

=========================================

To check seed database file only 

set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME  from cdb_data_files   where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' ) 
union 
select TABLESPACE_NAME, FILE_NAME  from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );



set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME  from cdb_data_files   where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME  from cdb_temp_files where CON_ID=( select CON_ID   from v$pdbs where NAME='PDB$SEED' );

no rows selected


Check this parameter ...

SQL> show parameter EXCLUDE_SEED_CDB_VIEW

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
exclude_seed_cdb_view                boolean     TRUE


SQL> alter session set EXCLUDE_SEED_CDB_VIEW=false ;

Session altered.


set linesize 200
col FILE_NAME for a80
select TABLESPACE_NAME, FILE_NAME  from cdb_data_files   where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' )
union
select TABLESPACE_NAME, FILE_NAME  from cdb_temp_files where CON_ID=( select CON_ID from v$pdbs where NAME='PDB$SEED' );


TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           /u01/app/oracle/oradata/orcl12c/pdbseed/pdbseed_temp012016-06-02_07-10-28-AM.dbf
SYSAUX                         /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
SYSTEM                         /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

==========================================================

 As seed database path 

from 
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
to 
/u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf

So convert parameter .. 
FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/')

Create Pluggable databse ..


SQL> SQL>
CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123
storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/orcl12c/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');

Pluggable database created.


SET SERVEROUTPUT ON
COLUMN "RESTRICTED" FORMAT A10
select
 v.name,
 v.open_mode,
 nvl(v.restricted, 'n/a') "RESTRICTED",
 d.status from v$pdbs v , dba_pdbs d
where 1=1
and v.guid=d.guid
order by v.create_scn;

NAME OPEN_MODE RESTRICTED STATUS
------------------------------ ---------- ---------- ---------
PDB$SEED READ ONLY NO NORMAL
ORCL READ WRITE NO NORMAL
ORDS READ WRITE NO NORMAL
ANUJ MOUNTED n/a NEW

SQL> alter pluggable database ANUJ open ;

Pluggable database altered.


NAME OPEN_MODE RESTRICTED STATUS
------------------------------ ---------- ---------- ---------
PDB$SEED READ ONLY NO NORMAL
ORCL READ WRITE NO NORMAL
ORDS READ WRITE NO NORMAL
ANUJ READ WRITE NO NORMAL

==============================

 pwd
/u01/app/oracle/oradata/vihcdb1

 ls -ltr
total 2439372
drwxr-xr-x 2 oracle oinstall      4096 Sep 23 13:53 pdbseed
-rw-r----- 1 oracle oinstall  20979712 Sep 23 15:15 temp01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep 23 21:16 redo02a.log
-rw-r----- 1 oracle oinstall 524296192 Sep 23 21:21 users01.dbf
-rw-r----- 1 oracle oinstall 734011392 Sep 24 03:52 system01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep 24 04:01 redo03a.log
-rw-r----- 1 oracle oinstall 466624512 Sep 24 04:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall 576724992 Sep 24 04:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall  52429312 Sep 24 04:05 redo01a.log
-rw-r----- 1 oracle oinstall  18038784 Sep 24 04:05 ora_control1.ctl
-rw-r----- 1 oracle oinstall  18038784 Sep 24 04:05 ora_control2.ctl

[oracle@cl-ora vihcdb1]$ mkdir ANUJ
[oracle@cl-ora vihcdb1]$ cd ANUJ/
[oracle@cl-ora ANUJ]$ pwd
/u01/app/oracle/oradata/vihcdb1/ANUJ
[oracle@cl-ora ANUJ]$ !sql

CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123
SQL>   2  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  3  DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/vihcdb1/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
  4  FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');


Pluggable database created.


CREATE PLUGGABLE DATABASE anuj ADMIN USER vihaan IDENTIFIED BY vihaan123
SQL>   2  storage (maxsize 5G MAX_SHARED_TEMP_SIZE 500M)
  3  DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/vihcdb1/ANUJ/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON
  4  FILE_NAME_CONVERT = ('/pdbseed/', '/ANUJ/');


Pluggable database created.

SQL> SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANUJ                           MOUNTED
SQL> alter session set container=anuj ;
 
Session altered.

SQL> startup ;
Pluggable Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ANUJ                           READ WRITE NO