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 126.96.36.199 database
Well, the documentation doesn’t say anywhere anything about source release limitans. So I tried it simply with an Oracle 188.8.131.52 database.
- Created a database link from my existing CDB pointing into my 184.108.40.206 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 220.127.116.11 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 18.104.22.168 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:
SHUTDOWN IMMEDIATE 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 FILE_NAME_CONVERT=('/oradata/UPGR12','/oradata/CDB2/pdb_upgr12');
- 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
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 22.214.171.124
- Same Endianness
- at the same (equal) version as the container database
- Script noncdb_to_pdb.sql needs to be run
Please see the restrictions and limitations (thanks to Susan Smith from Oracle for digging out the links):
- 12.1 target: Cloning a Remote PDB or Non-CDB
- 12.2 target: Cloning a Remote PDB or Non-CDB
- 18c target: Cloning a Remote PDB or Non-CDB
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 …