Create a PDB directly from a stand-alone database?

The documentation offers a well hidden feature for migrating a database into the universe of Oracle Single-/Multitenant:

Remote Cloning with the NON$CDB option.

If you’ll read the documentation it doesn’t say much about this option, neither the requirements nor the exact syntax or an example:
http://docs.oracle.com/database/121/SQLRF/statements_6010.htm#SQLRF55686

Scroll down to the FROM clause:

Create a PDB directly from a stand-alone database?

FROM NON$CDB@dblink … this option will be able to plugin a stand-alone database and make it a pluggable database. Sounds interesting, let’s try it.

Test 1 – Try to plugin an Oracle 11.2.0.4 database

Well, the documentation doesn’t say anywhere anything about source release limitans. So I tried it simply with an Oracle 11.2.0.4 database.

  1. Created a database link from my existing CDB pointing into my 11.2.0.4 database
  2. Started my SOURCEDB in read-only mode
  3. Tried to create a pluggable database from my SOURCEDB – and failed …
    SQL> create pluggable database PDB1 from non$cdb@sourcedb;
    create pluggable database PDB1 from non$cdb@sourcedb
    *
    ERROR at line 1:
    ORA-17627: ORA-28002: the password will expire within 7
    days
    ORA-17629: Cannot connect to the remote database server

Test 2 – Try to plugin an Oracle 12.1.0.2 database in file system 

Thanks to Tim Hall – his blog post did the magic trick for me:
http://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1.php#cloning-remote-non-cdb

First of all, the reason why my Test 1 failed is simply that I can’t have a user in an Oracle 11.2.0.4 database with the privilege CREATE PLUGGABLE DATABASE – but this is a requirement as I learned later on.

  1. You’ll need a user in SOURCEDB with the privilege to CREATE PLUGGABLE DATABSE:
    GRANT CREATE PLUGGABLE DATABASE TO sourcedb_user;
  2. Start SOURCEDB in read-only mode after shutting it down:
    SHUTDOWN IMMEDIATE
    STARTUP OPEN READ ONLY;
  3. Create a database link pointing from the CDB back into the SOURCEDB:
    CREATE DATABASE LINK sourcedblink CONNECT TO sourcedb_user IDENTIFIED BY password USING 'upgr12';
  4. Now create the pluggable database from the stand-alone UPGR12 database:
    CREATE PLUGGABLE DATABASE pdb_upgr12 FROM NON$CDB@sourcedblink
    FILE_NAME_CONVERT=('/oradata/UPGR12','/oradata/CDB2/pdb_upgr12');
  5. But when you check the status of the new PDB you’ll realize it is OPEN but only in RESTRICTED mode. Therefore noncdb_to_pdb,sql needs to be run. Connect to the new PDB and start the script:
    ALTER SESSION SET CONTAINER=pdb_upgr12;
    @?/rdbms/admin/noncdb_to_pdb.sql

Summary

What will you get from this command? Actually it will allow a simple way to plug in a stand-alone database into a container database but the following restrictions apply:

  • Source database has to be:
    • at least Oracle 12.1.0.2
    • Same Endianness
    • at the same (equal) version as the container database
  • Script noncdb_to_pdb.sql needs to be run

Addition:
Please see the restrictions and limitations (thanks to Susan Smith from Oracle for digging out the links):

You may have a look at this MOS Note:1928653.1 Example for Cloning PDB from NON-CDB
via Dblink
as well [Thanks Krishnakumar for pointing me to this note].

Finally the only simplification seems to be to avoid the extra step of creating the XML manifest file with DBMS_PDB.DESCRIBE – but apart from that I can’t see many other benefits – except for easing of remote cloning with the above restrictions.

And not to forget:
The clone-via-db-link command does not allow to specify the NOCOPY option. So you’ll get always a copy of your source database which may not be desired in every situation … 

–Mike

Share this: