Clone Pluggable Database to Local Container Database

In this demo I show how to create a pluggable database from an existing pluggable to a local container.

We want to quickly clone the the existing TSTDB pluggable database to DEV4DB so the development team can review a possible bug in there application 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.

Environment

Container- TESTDBS

Pluggable- TSTDB

New Pluggable- DEV4DB

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

1. Connect to root container

$sqlplus / as sysdba

2. Show current pluggable databases.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

3. When cloning a pluggable database the source pluggable database must be in read-only mode.
SQL>alter pluggable database tstdb close immediate;

4. Now open the source pluggable database in read-only.
SQL>alter pluggable database tstdb open read only;

5. Execute the create pluggable database command to clone the tstdb to dev4db.
SQL> create pluggable database dev4db
from tstdb
file_name_convert = ('+DG01','+DG01')
storage (maxsize 6G max_shared_temp_size 1000M);

Observation of the alert log

We can see that the DEV4DB pluggable database is create with pdb id 7. One thing is it says datafile # 7-9 are being deleted from file$. I do not show that the files were deleted so seems the wording in the alert log is misleading. The buffer cache is flushed only for the new pluggable database and then it marks the pluggable database new. The pluggable database is mark new until it is successfully open for the first time.

6. Show status for all pluggable databases after clone is complete.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

We can see that our new DEV4DB has been created and is in a mounted state.

7. We need to open the new pluggable database DEV4DB for read write.
SQL>alter pluggable database DEV4DB open read write;

Observation of the alert log

We see that when the new pluggable database is open it does a check of the dictionary. There is also a warning message to review the view PDB_PLUG_IN_VIOLATIONS for more details.

Then the pluggable database DEV4DB opens successful.

8. Show status for all pluggable databases after new pluggable is open.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

We can see that our new DEV4DB has been created and is in a open read write state. We can also see that the source pluggable database is still in read only mode.

Review of the  PDB_PLUG_IN_VIOLATIONS shows warning message for different database components.
SQL>select name,cause,type,message,action from pdb_plug_in_violations
where name='DEV4DB';

9.  Review new datafiles for pluggable database DEV4DB.
SQL>select b.con_id,b.name,a.name as db_file_name from v$datafile a, v$pdbs b
where a.con_id= b.con_id;

We can see three new datafiles for DEV4DB.

10. We need to close the source database and new open for read write.
SQL>alter pluggable database tstdb close immediate;

11. We need top open the source database for read write.
SQL>alter pluggable database tstdb open read write;

12.  Show status for all pluggable databases.

SQL>show con_name

SQL>select con_id,name,open_mode,open_time from v$pdbs

order by con_id asc;

We can see that the source TSTDB is now open for read write.

13. The create pluggable command also create an active service for the pluggable database DEV4DB. We the two SQL statements below we can see that the size of the DEV4DB pluggable database matches to the TSTDB. We also see that the DEV4DB pluggable database as an active service called dev4db.

SQL>select con_id,name,open_mode,open_time,ceil(total_size)/1024/1024 total_size_in_mb from v$pdbs
order by con_id asc;

SQL>select con_id,con_name,name,network_name from v$active_services
order by con_id asc;

Check out my next blog post that will show how to create a pluggable database to a remote container from existing pluggable database.

Follow me on Twitter