Wednesday, October 2, 2013

Clone Pluggable Database to Remote Container Database

In this demo I show how to create a pluggable database to a remote container.

We want to quickly clone the the existing TSTDB pluggable database to our QA server to QAT1DB so the QA team can begin to QA the new code.

In a non-CDB database this would take several commands and steps to complete an RMAN clone. I will show how in a multitenant environment how easy it is to clone an existing pluggable database to a remote container.

Environment

Source Container- TESTDBS
Source Pluggable- TSTDB

Destination Container- QATDBS
Destination Pluggable- QAT1DB



Note: When we are going to clone a pluggable database it requires the source pluggable database to be in read-only mode. I hope Oracle will correct this in version 12.1.0.2. In a later post I will test using a physical standby to achieve not needing to close the source pluggable database.

Steps

I will use a common user I created on one of my previous post you can read here.

1. First thing we need to do is grant the "Create Pluggable Database" privilege to my common role in the source CDB. 
$sqlplus / as sysdba
SQL>grant create pluggable database to C##MIDLEVELDBA container=all;





2. Here you can see that my common user has the common role granted.
SQL>select grantee,granted_role,default_role,common
from dba_role_privs
where grantee='C##ORADBADMIN';





3. Connect to the destination CDB.
$sqlplus / as sysdba
SQL>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

You can see below I have no current PDB other then the seed.







4. Connected to the destination CDB create the dblink pointing to the source PDB in the source CDB with the common user. 
SQL>create database link tstdb
connect to c##oradbadmin identified by temp1234
using 'alpddbs002:1521/tstdb';






5. Test the dblink.
SQL>select instance_name,host_name from v$instance@tstdb;
SQL>select con_id,name,open_mode,open_time from c$pdbs@tstdb; 










6. Connect to source CDB verify open mode for the TSTDB PDB we will be cloning.
$sqlplus / as sysdba
SQL>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;








7. Close the TSTDB PDB.
SQL>alter pluggable databse tstd close immediate; 




8. Open the TSTDB PDB for read only.
SQL>alter pluggable database tstdb open read only; 





9.  Check the TSTDB PDB status after the open mode change.
SQL>select con_id,name,open_mode,open_time from v$pdbs order by con_id asc;

We can see that the open mode is now read only.









10. Issue the create pluggable database from the destination CDB using the dblink.
SQL>create pluggable database qat1pdb from tstdb@tstdb;

I get the following error message







Review the alert log on the source CDB and I see the following error. 





Trace file on the source system does not show much. 

















I issue the following but still same error which in my case I did not need to pass the file_name_convert since I am using ASM and the diskgroup are name the same in both servers.
create pluggable database qat1pdb from tstdb@tstdb
file_name_convert = ('+DG01','+DG01');

I test using a TNS file and recreate the dblink but same error. I also tested create the dblink using the source CDB but the same issue.

Quick scan of Oracle support does not show and articles or any bugs reported.

Quick scan of the internet using Google I find some one has already created a blog post on the same issue which you can see here. The user states the same issue as I am seeing and has reported the issue to Oracle support. The user states a bug has been created but as of this writing there is no fix yet.

More to come................................