Oracle Multitenant - PDB Clone New Features (12.1.0.2.0) - Part I

As you know, Oracle Database 12c Release 1 (12.1.0.2.0) already released at July 22, 2014. You can download new release from here.

With this new release coming some new features. This article series covers PDB Clone new features of Oracle Multitenant option of Oracle Database 12c Release 1 (12.1.0.2.0). 

This article is in four parts: 

1. Excluding Data When Cloning a PDB
2. Non-CDB Adopt to CDB as a PDB.
3. PDB Subset Cloning 
4. PDB Snapshot Cloning (as soon)

Part I:  Excluding Data When Cloning a PDB

We can create clone of PDB with CREATE PLUGGABLE DATABASE ... FROM statement from existing PDB. All clones of PDB contains all object definitions and data in PDB. Sometimes we need quickly creating clones of a PDB with only the users object definitions and no data. New NO DATA clause included the CREATE PLUGGABLE DATABASE ... FROM statement in 12.1.0.2.0 version. The NO DATA clause specifies that a PDB's data model definition is cloned but not the PDB's data. When you using NO DATA Clause in CREATE PLUGGABLE DATABASE ... FROM statement, the dictionary data in the source PDB is cloned, but all user-created table and index data from the source PDB is excluded. Follow examples :

[oracle@oel62-ora12c2 Desktop]$ export ORACLE_SID=prmcdb
[oracle@oel62-ora12c2 Desktop]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 24 10:19:26 2014

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> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select cdb,  name from  v$database;

CDB NAME
--- ---------
YES PRMCDB

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ WRITE
PRMPDB02         MOUNTED

SQL> conn mahir/mahir@prmpdb01;    
Connected.
SQL>
SQL> select table_name, iot_type, iot_name from user_tables; 

TABLE_NAME          IOT_TYPE          IOT_NAME
-----------     --------------   ---------------
T

SQL> select count(1) from t; 

  COUNT(1)
----------
    999999

SQL> select index_name from user_indexes;

INDEX_NAME
--------------------------------------------------------------------------------
IDX_T

SQL> select segment_name, bytes/1024/1024 sizeMB from user_segments 
  2   where segment_name = 'IDX_T';

SEGMENT_NAME    SIZEMB
-------------- ----------
IDX_T          37

SQL> conn sys@prmcdb as sysdba
Enter password: 
Connected.

SQL> alter pluggable database prmpdb01 close;
      
Pluggable database altered.

SQL> alter pluggable database prmpdb01 open  read only;

Pluggable database altered.

 
SQL> create pluggable database prmpdb03 from prmpdb01
  2  file_name_convert=('prmpdb01','prmpdb03') no data;

Pluggable database created.

SQL> select name, open_mode from v$pdbs;

NAME          OPEN_MODE
------------------------------ ----------
PDB$SEED         READ ONLY
PRMPDB01         READ ONLY
PRMPDB02         MOUNTED
PRMPDB03         MOUNTED

SQL> alter pluggable database prmpdb03 open;

Pluggable database altered.

Add new service description to tnsnames.ora file.

PRMPDB03 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel62-ora12c2.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prmpdb03)
    )
  )

Connect to new PDB (prmpdb03)

SQL> conn mahir/mahir@prmpdb03
Connected.
SQL> 
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
T

SQL> select count(1) from t; 

COUNT(1)
----------
0

SQL> select index_name from user_indexes;

INDEX_NAME
--------------------------------------------------------------------------------
IDX_T

SQL> select segment_name, bytes/1024/1024 sizeMB from user_segments
2  where segment_name = 'IDX_T';


no rows selected

When the NO DATA clause is included in the CREATE PLUGGABLE DATABASE statement, the source PDB cannot contain the following types of tables:

• Index-organized tables 
• Advanced Queue (AQ) tables 
• Clustered tables 
• Table clusters

SQL> conn sys@prmcdb as sysdba
Enter password: 
Connected.
SQL> alter pluggable database prmpdb01 close;
      
Pluggable database altered.

SQL> alter pluggable database prmpdb01 open;

Pluggable database altered.

SQL> conn mahir/mahir@prmpdb01
Connected.

SQL> create table iot (n number primary key) organization index;

Table created.

SQL> insert into iot  select *  from t;

999999 rows created.

SQL> commit;

Commit complete.

SQL> conn sys@prmcdb as sysdba
Enter password: 
Connected.
SQL> alter pluggable database prmpdb01 close;
      
Pluggable database altered.

SQL> alter pluggable database prmpdb01 open  read only;

Pluggable database altered.

SQL> create pluggable database prmpdb04 from prmpdb01 
  2  file_name_convert=('prmpdb01','prmpdb04') no data;
create pluggable database prmpdb04 from prmpdb01
*
ERROR at line 1:
ORA-65161: Unable to create pluggable database with no data

Pluggable database cannot created, because there have an Index Organized Table in mahir's schema.

Regards
Mahir M . Quluzade