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................................









13 comments:

  1. Javier, the remote PDB clone functionality is not available in the first release. This functionality will be available in the next release. Thanks and keep blogging; nice detail. - jpm

    ReplyDelete
  2. Thanks for sharing the information

    For more info : Oracle Certification Path

    ReplyDelete
  3. Really something Grate in this article Thanks for sharing this. We are providing DATABASES courses training online. After reading this slightly am changed my way of introduction about my training to people. And also refer my website for DATABASES Training and solutions of DATABASES applications. Please Visit Us @ DATABASES training courses online

    ReplyDelete
  4. Nice article i was really impressed by seeing this article, it was very intresting and it is very useful for Oracle Training Learners.. We are Providing best qa online training in worldwide.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Try using the below modified command:

    CREATE PLUGGABLE DATABASE qat1pdb FROM NON$CDB@tstdb FILE_NAME_CONVERT=('+DG01/','+DG01/');

    Slash (/) at the end of ASM disk groups.

    Also change the name of your dblink to tstdb_link. Makes things more clear.

    Hope this helps.

    ReplyDelete
  7. You need to connect to CDB as SYS user to run that command.

    ReplyDelete
  8. ARINET DBA Services is a Chicago, IL based organization established in 2013. Uniting more than 100+ years of combined involvement in giving quality Oracle database Support administrations to American organizations, we've given the diverse options about remote dba masters, remote dba organizations, remote dba reinforce, remote database, oracle remote, dba remote, database association, prophet dba reinforce, Oracle sponsorship and Oracle Consultants.

    ReplyDelete

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).