Remote PDB Cloning

Introduction

Starting with Oracle Database 12c (12.1.0.2) we can perform remotely cloning for PDB and Non-CDB. Cloning for Non-CDB is only supported for 12c databases. This is a great feature and it is very useful if we have to perform cloning from remote sites. In this article I will show you how you can clone your PDBs or Non-CDBs. If your environment is configured with Oracle Managed Files (OMF) the process can be easy as running just one command.

These are the possible techniques for creating a PDB. In the first section we are going to demonstrate remote cloning of a PDB, and in the second section we are going to see how we can clone 12c Non-CDB to a PDB.

admin358

Environment

For the purposes of this article assume we have the following configuration:

Host Database Name Container Database
db-srv1 cdb Yes
noncdb No
db-srv2 cdb2 Yes

*Note: All databases mentioned in the table are 12c (12.1.0.2) databases.

What we are going to show is how we can remotely clone pluggable database from “CDB” to “CDB2” and clone the Non-CDB “noncdb” database to “CDB2” as pluggable database.

As far as the storage, Automatic Storage Management (ASM) is configured on both hosts db-srv1 and db-srv2.

 

CREATE PLUGGBLE DATABASE statement

Cloning a PDB is done with the same command as creating, with CREATE PLUGGBLE DATABASE statement. There are several parameters that we can specify for create pluggable database statement in order to configure additional settings or add restrictions to the PDB. Some of the clauses are available starting from 12.1.0.2 version.

create_pdb_clone

 

  • pdb_storage_clause – With this clause we can set hard limit on the storage for the PDB we want to create. We can limit the size of data files and temporary files.
  • file_name_convert – If OMF is not configured and PDB_FILE_NAME_CONVERT is not set then we must specify the file name conversion in order database file names to be generated correctly.
  • path_prefix_clause – Restricts the directory objects to specific directory and its subdirectories.
  • tempfile_reuse_clause – If temporary file already exists and we want to reuse it for the new PDB.
  • SNAPSHOT COPY – Cloning is performed using storage snapshots. Applicable only for PDB cloning.
  • user_tablespaces_clause (12.1.0.2) – Defines default user tablespace for the PDB.
  • standbys_clause (12.1.0.2) – We can exclude or include the PDB from standby configurations.
  • logging_clause (12.1.0.2) – Controls the logging attribute for tablespaces created within the PDB.
  • create_file_dest_clause (12.1.0.2) – We can implicitly change the OMF settings to be different from the root container.
  • NO DATA (12.1.0.2) – If specified, user data won’t be replicated. This is applicable only for PDB cloning.

As we can see from the graphic, none of the listed clauses are mandatory for the CLONE process and since we are using ASM we basically have Oracle Managed Files (OMF) enabled. This means that we don’t have to specify file_name_convert clause. The data files will be created automatically in the destination defined by CREATE_FILE_DEST, in this environment set to the DATA disk group.

Creating (cloning) a PDB Remotely from a PDB

Assume that we want to clone the pluggable database PDB1 from “CDB” to “CDB2”.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE

db-srv2 configuration:

Before we start with the cloning procedure we need to configure the network communication between the two servers. For that purpose we need to configure tnsnames.ora entry for the source database on the db-srv2 server.

CDB_SOURCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-srv1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

Next, on the target database we need to create the database link which will be used in the CREATE PLUGGABLE DATABASE statement. We are using the system user because we are connecting to the root container and the user must have the CREATE PLUGGABLE DATABASE system privilege. The database link user that is used for connection must be able to connect to the root container or to the PDB which we want to clone.

SQL> create database link cdb_source connect to system identified by oracle using 'CDB_SOURCE';

Database link created.

As rule-of-thumb always check whether the name of the pluggable database you want to create it is not already used. We can confirm that we don’t have name violations via V$PDBS dictionary view.

SQL> select con_id, name, open_mode from v$pdbs where name = 'PDB1';

no rows selected

db-srv1 configuration:

The pluggable database that we want to clone needs to be in read-only mode.

SQL> select con_id, name, open_mode from v$pdbs where name = 'PDB1';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           READ WRITE

Let’s put the PDB1 into read-only mode. If the pluggable database current mode it is READ WRITE, than you can perform CLOSE IMMEDIATE or CLOSE NORMAL (waiting for all transactions to finish).

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL>
SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

At this stage we are ready to execute the CREATE PLUGGABLE DATABASE using the database link previously defined.

Let’s run the CREATE PLUGGABLE DATABASE statement.

SQL> create pluggable database pdb1 from pdb1@cdb_source;

Pluggable database created.

The time to complete the statement will vary depending on your network speed between the servers and the amount of data (data files size) that needs to be transferred.

While this statement is executing we can monitor the process from the alert.log. We can also confirm that the pluggable database is successfully created. Also, as you can see from the alert.log contents Oracle has created default service for PDB1.

...
create pluggable database pdb1 from pdb1@cdb_source
Tue Oct 27 13:12:34 2015
****************************************************************
Pluggable Database PDB1 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Database Characterset for PDB1 is AL32UTF8
Deleting old file#8 from file$
Deleting old file#9 from file$
Deleting old file#10 from file$
Deleting old file#11 from file$
Adding new file#12 to file$(old file#8)
Adding new file#13 to file$(old file#9)
Adding new file#14 to file$(old file#10)
Adding new file#15 to file$(old file#11)
Successfully created internal service pdb1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB1 with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database pdb1 from pdb1@cdb_source
...

By default the new pluggable database will be left in MOUNTED state.

SQL> select con_id, name, open_mode from v$pdbs where name = 'PDB1';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           MOUNTED

We can open the pluggable database to confirm that everything works as it should.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

After the cloning process is finished we can re-open the pluggable database on the source target to READ WRITE if it’s need to be available.

Leave a Reply