By Mike Dietrich-Oracle on May 18, 2015
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:
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 188.8.131.52 database
Well, the documentation doesn't say anywhere anything about source release limitans. So I tried it simply with an Oracle 184.108.40.206 database.
- Created a database link from my existing CDB pointing into my 220.127.116.11 database
- Started my SOURCEDB in read-only mode
- 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 18.104.22.168 database in file system
Thanks to Tim Hall - his blog post did the magic trick for me:
First of all, the reason why my Test 1 failed is simply that I can't have a user in an Oracle 22.214.171.124 database with the privilege CREATE PLUGGABLE DATABASE - but this is a requirement as I learned later on.
- You'll need a user in SOURCEDB with the privilege to CREATE PLUGGABLE DATABSE:
GRANT CREATE PLUGGABLE DATABASE TO sourcedb_user;
- Start SOURCEDB in read-only mode after shutting it down:
STARTUP OPEN READ ONLY;
- 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';
- Now create the pluggable database from the stand-alone UPGR12 database:
CREATE PLUGGABLE DATABASE pdb_upgr12 FROM NON$CDB@sourcedblink
- 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;
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 126.96.36.199
- 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.