Cloning a Remote Pluggable Database (PDB) in Oracle Database 12c Release 2

Cloning a Remote PDB

Connect to the remote CDB and prepare the remote PDB for cloning.

1. We need to connect the Database with local connection.Check its up and running.
2. Need to create user in the remote pluggable database also with the privilege of create session , create pluggable database.
3. Switch the remote PDB to read only mode.when the source pluggable database prepared with tnsnames and listener as well.
4. Create Database link in the root container pointing to the user which we create in the remote pluggable database.
5. We can now clone the pluggable database from the root container using Database link which we created with the user connection.
6. If we are not using OMF we need to convert the path using FILE_NAME_CONVERT parameter.
7. Now we can check the pluggable database which we created through the clone.It has created successfully.

Step 1 : Database up and running

Step 2 : Required to set the particular pluggable database in read write mode.

Step 3 : Create a user in the remote database for use with the database link. In this case, we will use a local user in the remote PDB.

Step 4 : Open the remote PDB in read-only mode.

[oracle@ram ~]$ ps -ef|grep pmon
oracle 4725 4707 0 01:09 pts/0 00:00:00 grep pmon
oracle 55330 1 0 Oct10 ? 00:00:01 ora_pmon_dbwr
oracle 60884 1 0 Oct10 ? 00:00:00 ora_pmon_orcl
[oracle@ram ~]$ export ORACLE_SID=orcl
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 11 11:09:51 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$database;

NAME
---------
ORCL

SQL> conn / as sysdba
Connected.


SQL> show user
USER is "SYS"

SQL> alter session set container=orclpdb;

Session altered.

SQL> alter pluggable database orclpdb open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME        OPEN_MODE
----------  ----------
ORCLPDB     READ WRITE


SQL> col name for a15;
SQL> select name,open_mode from v$pdbs;

NAME             OPEN_MODE
--------------   ----------
ORCLPDB          READ WRITE

SQL> create user clone_user identified by clone_user;

User created.

SQL> grant create session,create pluggable database to clone_user;

Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database orclpdb close;

Pluggable database altered.

SQL> alter pluggable database orclpdb open read only;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Step 5 :Switch to the local server and create a “tnsnames.ora” entry pointing to the remote database for use in the using clause of the database link.

[oracle@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/network/admin/
[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr.carrierzone.com)
)
)

ORCLPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr.carrierzone.com)
)
)

PDB5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb5.carrierzone.com)
)
)
PDB6 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb6.carrierzone.com)
)
)
[oracle@ram admin]$ tnsping ORCLPDB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 11-OCT-2018 11:21:29

Copyright (c) 1997, 2016, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = dbwr.carrierzone.com)))
OK (30 msec)

Step 6 :Connect to the local database to initiate the clone.

[oracle@ram admin]$ export ORACLE_SID=dbwr
[oracle@ram admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 11 11:22:09 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode from v$pdbs;

NAME           OPEN_MODE
----------     ----------
PDB$SEED       READ ONLY
PDB5           MOUNTED
PDB6           MOUNTED


SQL> col name for a15
SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            MOUNTED
PDB6            MOUNTED

SQL> alter session set container = pdb5;

Session altered.

SQL> alter pluggable database pdb5 open read only;

Pluggable database altered.

Step 7 :Create a database link in the local database, pointing to the remote database.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> drop database link dblink;
drop database link dblink
*
ERROR at line 1:
ORA-02024: database link not found

SQL> create database link dblink connect to dblink identified by clone_user using 'orclpdb';

Database link created.

Step 8 :Create a new PDB in the local database by cloning the remote PDB. In this case we need to bother with FILE_NAME_CONVERT parameter for file name conversions we are not using Oracle Managed Files (OMF).

Step 9 :We can see the new PDB has been created, but it is in the MOUNTED state.

Step 10 :The PDB is opened in read-write mode to complete the process.

SQL>create pluggable database orclpdbnew from  orclpdb@dblink file_name_convert='/u01/app/oracle/oradata/orcl/orclpdb/','/u01/app/ oracle/oradata/orcl/orclpdbnew/');

Pluggable database created.

SELECT name, open_mode FROM v$pdbs WHERE name = 'ORCLPDBNEW';

NAME            OPEN_MODE
--------------- ----------
ORCLPDBNEW       MOUNTED

SQL>

ALTER PLUGGABLE DATABASE orclpdbnew OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'ORCLPDBNEW';

NAME                           OPEN_MODE
------------------------------ ----------
ORCLPDBNEW                     READ WRITE

SQL>

Thus the pluggable database created successfully with the remote clone.

Catch Me On:- Hariprasath Rajaram

LinkedIn:                  https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:                 https://www.facebook.com/HariPrasathdba
Facebook Group:   https://www.facebook.com/groups/894402327369506/
Facebook Page:      https://www.facebook.com/dbahariprasath/?
Twitter:                     https://twitter.com/hariprasathdba