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 

      Comments

      Popular posts from this blog

      Oracle Data Guard 11g - How to safely remove a Data Guard Broker configuration?

      Oracle Database 12c Release 2 - SQL*Plus History Command

      Oracle Database 18c - Install On-Premises for Linux