Creating PDBs By Cloning
Last post we learned to create PDBs from scratch. This week we will look at creating PDBs by cloning other PDBs.
We can create a pluggable database from a local PDB (within the same CDB), from a remote PDB (from another CDB) or from a non-CDB database. The PDB we are cloning from is also called the source. The resulting PDB after the clone, is the target, and it is also called a clone PDB.
PDB cloning is achieved through the CREATE PLUGGABLE DATABASE statement. The files of the source system are copied to a new location, for the clone, and then the files are associated with the cloned PDB.
If the source CDB database is in ARCHIVELOG mode, the source PDB can be open read write mode during the cloning process. Another requirement for the source CDB is to be in local undo mode, which is the default for newly created databases (12.2 and up). If you don’t know what local undo mode means, just know that each PDB has its own undo tablespace in this mode, and it is not sharing the undo tablespace with the CDB and other PDBs.
We can get into more details about this in a later post. This cloning method is called hot clone.
If the above 2 requirements are not met (ARCHIVELOG mode and local undo), then you cannot do a hot clone of a PDB. In this case the PDB must be in read only mode and not in read write, so changes are not happening to the PDB during the cloning process.
Let’s look at some examples!
Cloning a local PDB
This is the easiest method of cloning a PDB. You create a copy (a clone) of a PDB within the same CDB. You specify the source PDB in the FROM clause of the create pluggable database statement.
You must be connected to the root container or the application root when executing the statement. Issue the CREATE PLUGGABLE DATABASE statement with the FROM clause. Once the statement completes, the clone PDB will be in mounted state, in a NEW status. A default service is created for the new PDB, with the same name as the PDB. Open the clone PDB in read write mode to use it. Once the PDB is open, the status will change to NORMAL. As a good DBA that you are, take a backup of the new clone PDB.
A simplified example below. We will connect to CDB1 and clone PDB1 to PDB1_CLONE, within the same CDB1:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> create pluggable database PDB1_CLONE from PDB1;
Pluggable database created.
SQL> select name, open_mode from v$pdbs
where name like 'PDB1%';
NAME OPEN_MODE
--------------- ----------
PDB1 READ WRITE
PDB1_CLONE MOUNTED
SQL> select pdb_name, status from dba_pdbs
where pdb_name like 'PDB1%';
PDB_NAME STATUS
--------------- ----------
PDB1 NORMAL
PDB1_CLONE NEW
SQL> alter pluggable database PDB1_CLONE open;
Pluggable database altered.
SQL> select name, open_mode from v$pdbs
where name like 'PDB1%'; 2
NAME OPEN_MODE
--------------- ----------
PDB1 READ WRITE
PDB1_CLONE READ WRITE
SQL> select pdb_name, status from dba_pdbs
where pdb_name like 'PDB1%';
PDB_NAME STATUS
--------------- ----------
PDB1 NORMAL
PDB1_CLONE NORMAL
The above simple statement worked because OMF was enabled, or PDB_FILE_NAME_CONVERT parameter was set. Otherwise the FILE_NAME_CONVERT parameter should be used to rename the datafiles during the clone process. During the clone process, the new files were copied to the new location, (mostly using the GUID, covered in the previous post). Remember, there are multiple clauses that can be used when cloning a PDB, such as FILE_NAME_CONVERT, CREATE_FILE_DEST, STORAGE, depending on what you want changed and what settings you have.
You can also create a PDB clone using DBCA in silent mode, starting with 18c.
Another cool feature is the NO DATA clause. You can clone a PDB, with the definition of the PDB but no data. Basically all the tables will be cloned within the PDB, but there will be no data in those tables. Now how cool is that? Have a look below at my example. I am going to create a table in PDB1, insert some data, then clone PDB1 NO DATA.
SQL> alter session set container=PDB1;
SQL> create table hr.test (id number);
SQL> insert into hr.test values (1);
SQL> insert into hr.test values (2);
SQL> commit;
SQL> select count(*) from hr.test;
COUNT(*)
----------
2
--have to be connected to root, otherwise you'll get an error.
SQL> conn / as sysdba
SQL> create pluggable database PDB1_NODATA from PDB1 NO DATA;
Pluggable database created.
SQL> alter pluggable database PDB1_NODATA open;
Pluggable database altered.
SQL> alter session set container=PDB1_NODATA;
Session altered.
SQL> select count(*) from hr.test;
COUNT(*)
----------
0
--you notice above there is no data in hr.test table.
Cloning a remote PDB
A remote PDB is a PDB in a different CDB than the CDB you want to clone in. The process is similar with the previous cloning, but now you’ll need a database link to perform the cloning process. You will use the same CREATE PLUGGABLE DATABASE statement, and you’ll specify a db link in the FROM clause.
The database link has to exist in the target CDB, where the cloned PDB will reside. The link has to point to the remote CDB, the source, where we want to clone from. The link can connect either to the remote CDB or the remote PDB we want to clone.
You can also clone a remote PDB with DBCA in silent mode, starting with 19c.
There are some prerequisites that need to be met, in regards to the character sets of the CDBs involved in the cloning process. Ideally the character sets are the same, or the character set is AL32UTF8. You can read about the character sets prerequisites here.
The source PDB cannot be closed if you want to clone it. The local undo mode applies here as well. Ideally the CDB is in local undo mode and in archive log mode, and then the source PDB can be open in read write when you clone it, otherwise the remote PDB has to be in read only mode.
In the above link to the oracle documentation, there are all the prerequisite listed, you can review them all.
Lets see the actual steps to clone a remote PDB. We have 2 CDBs: CDB1 and CDB2. We are going to clone PDB1 from CDB1 as PDB1_remote in CDB2.
--connect to CDB2
SQL> create database link cdb1 connect to system
identified by oracle using 'CDB1';
Database link created.
SQL> select * from dual@cdb1;
D
-
X
SQL> create pluggable database pdb1_remote from pdb1@CDB1;
Pluggable database created.
SQL> alter pluggable database pdb1_remote open;
Pluggable database altered.
I hope you learned something new today! I personally can see how well the NO DATA clause could be used when setting up test environments for users!
If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!
If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD available until the end of the month!
