Cloning Pluggable Database in Oracle

Back to: Oracle DBA Tutorials

Cloning Pluggable Database in Oracle with Examples

In this article, I am going to discuss Cloning Pluggable Database in Oracle with Examples. Please read our previous article where we discussed Drop Pluggable Database in Oracle with Examples.

Cloning Pluggable Database in Oracle

We will try to understand what is the meaning of cloning the pluggable database. Until now we have learned how to clone a pluggable database from PDB$SEED. Now, we will try to learn how to clone a pluggable database from another pluggable database.

What is Cloning a Pluggable Database in Oracle?

Cloning is copying a source PDB from a CDB and plugging the copy into the same CDB or another CDB.

Example: We have a ROOT database and a SEED database. We have a user-created pluggable database called JAYAPDB. We can use a simple command to copy the data present in the pluggable database JAYAPDB and paste it into a new database and name it pdb4. So, the database pdb4 will be a copy of the pluggable database called JAYAPDB.

We can paste this copy of the data from the JAYAPDB database can be used in the same container or else we can use it in a different container database. But as of now, we will focus on the same container database.

Conditions to clone a PDB from another PDB in Oracle:

• You must have connected to a CDB$ROOT and the current container must be the CDB$ROOT database.
• You must have the CREATE PLUGGABLE DATABASE system privilege. In most cases, the SYS user will be part of creating a pluggable database.
• The CDB in which the PDB is being created must be in READ WRITE mode.
• We must put the PDB being cloned in READ_ONLY mode before you can clone it.

So, these conditions must be met during the cloning of a pluggable database from another PDB.

Why do we need to Clone the Pluggable Database in Oracle?

For example. If we need to apply a patch to the database and you do not need to damage the data present in the database. So, we will make a copy of the database using database cloning and paste it into a new pluggable database and apply the patch to the database. If we need to check the performance of the database clone the database to another database and check the performance of the original database and make changes accordingly.

Let’s take another example, we have an application that is running on the database. Suppose, another customer asks you to provide a copy of the same application then we can clone the database and use that database to run the application. The is cloning is applicable since 12c,18c, and 19c.

Exercise:

Let us try to do some exercises to learn about cloning a pluggable database. Open Linux virtual machine and start the database and make sure check the listener and services are running. Open SQL Developer and connect to the database using “sqlplus / as sysdba”

Step 1: Check the container name and list of pluggable databases.

Command: show con_name

We are connected to the CDB$ROOT database. Let’s check the list of pluggable databases.

Query: select name,open_mode from v$pdbs;

We can see there are three pluggable databases running PDB$SEED, JAYAPDB, and JAYAPDB1.

Step 2: Connect to the pluggable database

Let us go ahead and connect to the pluggable database JAYAPDB.

Command: alter session set container=JAYAPDB;

Now, that we are connected to the pluggable database JAYAPDB, let’s check the list of users present in the database.

Query: select username, DEFAULT_TABLESPACE, common from dba_users;

We can see the users present in the JAYAPDB database. This is the combination of common users and non-common users. There are non-common users like dotnet, pdbadmin, hr user. The hr user is specific to the JAYAPDB database. When we clone the database the user hr also will be copied to the pluggable database.

Step 3: Check the tables and tablespaces present in the JAYAPDB database.

Now, that we know that the hr user is present. There are tables present inside the hr user. Let’s go ahead and check the list of tables in the hr user.

Query: select * from dba_tables where owner=’hr’;

We can see there are 7 tables present in the JAYAPDB database under hr user. Now, let’s check the data files present in the JAYAPDB database.

Query: select con_id, file#, name from v$datafile;

We can see there are 3 datafiles present in the database JAYAPDB. When we clone this database all these datafiles will be copied. Let’s check the tablespaces associated with these data files.

Query: select * from v$tablespace;

Step 4: Create a directory for the new pluggable database.

Go to the below path and open a new terminal from the location.

Once you are in the directory, open the terminal from the same path and execute the below command to create the directory for the new pluggable database pdb4.

Command: mkdir /u01/app/oracle/oradata/JAYADB/pdb4

We can see after executing the command a folder was created in the path.

Step 5: Make an uncommitted transaction in the JAYAPDB database.

Log in to the database as “sqlplus / as sysdba”. Once logged in connect to the pluggable database JAYAPDB.

Now, let us make an un-committed transaction in the database, and does it copy to the cloning database or not? Let’s run an update statement to the table in hr user and see if that updates the new database.

Query: update hr.employees set salary=salary+1 where employee_id=100;

We are not going to commit this transaction because any uncommitted transaction will not be transferred during the cloning process. Any dirty buffer will not be copied. Now, that the table is updated let’s check value is updated or not in the database.

Query: select salary from hr.employees where employee_id=100;

We can see the salary is added by only 1 so, the term is updated. We are not going to commit to this transaction.

Step 6: Clone the Database.

Let us go back to the SQL developer and connect to the CDB$ROOT database.

Command: alter session set container=cdb$root;

The reason behind connecting back to the CDB$ROOT database is when we are cloning the database, we have to connect to the root database. Let’s create a new pluggable database using the JAYAPDB database.

Query:

Create pluggable database pdb4 from jayapdb
FILE_NAME_CONVERT= ('/u01/app/oracle/oradata/JAYADB/jayapdb/'.
           '/u01/app/oracle/oradata/JAYADB/pdb4/');

So, we are creating a new pluggable database pfb4 from the database jayapdb. In the term, file_name_convert will copy the datafiles from the source path which is considered as phrase 1 ‘/u01/app/oracle/oradata/JAYADB/jayapdb/’ to the source path which is considered as phrase 2 ‘u01/app/oracle/oradata/JAYADB/pdb4/’. We can do it in another way.

Query:

Create pluggable database pdb4 from jayapdb
Create_file_dest='/u01/app/oracle/oradata/JAYADB/pdb4/';

This will clone the database from jayapdb but the names of the files will be managed by Oracle. But doing the first method is more convenient.

This is the exact copy of the database jayapdb. Let’s go ahead and check the files were created in the folder of pdb4.

We can see there are data files present in the folder. So, the database is created.

Step 7: Open the new pluggable database pdb4.

Let us check the list of pluggable databases present.

Command: select name, open_mode from v$pdbs;

Now, that the database pdb4 is in a mounted state. Let’s connect to the database pdb4 and open the database.

Command: alter pluggable database pdb4 open;

We have opened the new pluggable database. Let’s connect to the pdb4 database.

Command: alter session set container=pdb4;

Step 8: Check the data in pdb4.

Now, that the database is opened let us go ahead and check the data if it is created or not. Let’s check the value that we have updated previously has been updated or not.

We can see the value is not updated to 24001 in the pdb4 database because only clean data will be copied. We haven’t committed the data to the JAYAPDB database.

Step 9: Check the users and tables in the pdb4 database.

Let us go ahead and check the users in the newly created pdb4 database.

Query: select username, DEFAULT_TABLESPACE, common from dba_users;

So, we can see all the users present in the JAYAPDB database are present in the pdb4 database as well. Now, let’s check the list of tables present in the hr user.

Query: select * from dba_tables where owner=’HR’;

So, there are same tables compared to JAYAPDB. Let’s check the tablespaces as well.

Query: select * from v$tablespace

So, all the tablespaces are present as same as JAYAPDB.

Step 9: Check the listener status and add entries in tnsnames.ora

Let us go ahead and check if the listener is updated with the new service name pdb4.com

So, the listener is updated with the new service name of the database pdb4. Let’s go ahead and add the entry in tnsnames.ora file as well. Open the tnsnames.ora file and check copy any entry and paste down below and make changes according to the database. We have made changes as below.

We have added pdb4 as the connection name and the service name as pdb4.com. The hostname and the port number as the same as we have only one container database. Once the changes are made save the file.

So, we have added the new database entry in tnsnames.ora, let’s go ahead and check the connectivity of the database pdb4 using the tnsnames in SQL developer.

Open the SQL developer and click on the new connection. Enter the connection name as hr and the username as hr and the password for the username hr and select the connection type as TNS. Once that is selected select the tnsname as pdb4.com You can find the details below.

Once everything is selected check the connection of the user in the database pdb4.

We can see the connection status is a success. So, any user can connect to the newly created database. So, the database cloning copied everything including usernames, tables, and even passwords to the users. So, it is exactly a copy from the old database JAYAPDB and we understood how to clone a database.

In the next article, I am going to discuss Plugging and Unplugging in PDBs with Examples. Here, in this article, I try to explain Cloning Pluggable Database in Oracle with Examples and I hope you enjoy this Cloning Pluggable Database in Oracle article.