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:

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 must be at least Oracle 12.1.0.1
  • Source database must be on the same OS platform
  • Source database must be at the same (equal) version as the container database
  • Script noncdb_to_pdb.sql needs to be run

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

3 thoughts on “Create a PDB directly from a stand-alone database?

  1. This method has one more benefit – Remote cloning
    In case noncdb is at different server, we can create PDB using DB link. No need to copy datafile to server where CDB is located.
    This pdb creation using dblink is introduced in 12.1.0.2.0 version

  2. Hi Mike,
    Thank you for the insightful article. We have source 11g R2 datbase on the Windows 2012 VM, which would be considered stand-alone. The database contains critical replication via MVs from yet another source DB that runs on the Mainframe.
    Our goal is to migrate/upgrade the existing, functioning 11g R2 database from 11g R2 EE to 12c EE Multitenant as PDB. The 12c EE is running on the same Windows VM in the separate oracle home.

    Please provide any advice or suggestion if we will be able to migrate/upgrade the existing 11g DB to a 12c PDB without first upgrading the 11g DB home to 12c. In other words we want to have upgrade/migrate directly. Another question is will the original 11g EE DB be preserved of will it become 12c PDB after migration?

    Thank you

  3. Sorry to say that but your WILL HAVE TO UPGRADE TO 12c FIRST. I would love to tell you any advice to have this easier but that’s how it has been implemented. You will be able to unplug only ONCE you have upgraded the database first making the package DBMS_PDB available.

    You could of course use other techniques which don’t require an upgrade first (such as Data Pump or Transportable Tablepsaces or Full Transportable Export/Import – see the video on the right side where Roy demonstrates how to migrate a database directly from 11.2 to 12c PDB in the Cloud).

    Cheers
    Mike

Leave a Reply

Your email address will not be published. Required fields are marked *