Nothing but my learning and experience with Oracle..!

Friday, October 14, 2016

Creating a Clone PDB from a Remote PDB using database link

Up to Oracle 12.1.0.1,it was only possible to clone PDB's within the local Container database. Since version 12.1.0.2 Oracle has extended this functionality to remote PDB cloning. From 12.1.0.2 onwards,we can also clone a remote non-CDB as a PDB directly over the network. Remote Cloning of a Non-CDB is a simple procedure which utilizes a database link to transfer the data as part of running the

CREATE
PLUGGABLE DATABASE

command. Moreover, non-CDBs can be hot cloned, i.e. it is not required to put the source non-CDB in READ ONLY mode for cloning so that it can be cloned online. It is a pre-requisite that both the CDB and the non-CDB must be running Oracle Database 12c Release 1 (12.1.0.2) or later.

Requirements-

The source must be opened in read-only mode(for cloning PDBs)

CDB that is to accommodate the remote PDB must have the database link defined to the remote database.

It can point either to the CDB that the source PDB belongs to or directly to the source PDB.

User in the source database that the db link points to must have the CREATE PLUGGABLE DATABASE privilege. It can be common or local user (if PDB)

Additionaly databases should have the same options installed, same endianness and character sets

In this demonstration, I am going to clone a remote PDB using database link from the source CDB.In this example MYCDB1 is my source CDB and TDCDB is my destination CDB. TAPDB is a PDB within my MYCDB1 container and I am going to clone this PDB.

Login to source PDB and create some objects.

[oracle@mydb01]:[] $ . oraenv

ORACLE_SID = [oracle] ? MYCDB1

[oracle@mydb01]:[MYCDB1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 14 16:05:03 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> select NAME,CON_ID ,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

TAPDB 3 READ WRITE

SQL> alter session set container=TAPDB;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

TAPDB

SQL> create user mahi identified by mahi;

User created.

SQL> grant dba to mahi;

Grant succeeded.

SQL> !hostname -i

10.81.155.146

SQL> conn mahi/mahi@10.81.155.146:1521/TAPDB

Connected.

SQL> show con_name

CON_NAME

------------------------------

TAPDB

SQL> sho user

USER is "MAHI"

SQL> create table t1 as select * from dba_users;

Table created.

SQL> create table t2 as select * from dba_tables ;

Table created.

SQL> select count(1) from t1;

COUNT(1)

----------

SQL> select count(1) from t2;

COUNT(1)

----------

2338

Create one dedicated user for cloning in the remote pluggable database.

SQL> show con_name

CON_NAME

------------------------------

TAPDB

SQL> CREATE USER remote_pdb IDENTIFIED BY remote_pdb;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_pdb;

Grant succeeded.

SQL> select NAME,CON_ID ,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

TAPDB 3 READ WRITE

Close and open your remote PDB in read-only mode.

SQL> alter pluggable database close immediate instances=all;

Pluggable database altered.

SQL> select NAME,CON_ID ,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

TAPDB 3 MOUNTED

SQL> alter pluggable database open read only instances=all;

Pluggable database altered.

SQL> select NAME,CON_ID ,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

TAPDB 3 READ ONLY

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

Now connect to destination database and clone using the database link.

[oracle@mydb01]:[MYCDB1] $ . oraenv

ORACLE_SID = [MYCDB1] ? TDCDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@mydb01]:[TDCDB] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 14 16:18:02 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $ srvctl config scan

SCAN name: ptgracscan, Network: 1

Subnet IPv4: 10.81.155.144/255.255.255.240/bond0.3380, static

Subnet IPv6:

SCAN 0 IPv4 VIP: 10.81.155.156

SCAN VIP is enabled.

SCAN VIP is individually enabled on nodes:

SCAN VIP is individually disabled on nodes:

[oracle@mydb01]:[TDCDB] $ ss

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 14 16:19:27 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

SQL> CREATE DATABASE LINK link_to_tapdb CONNECT TO remote_pdb IDENTIFIED BY remote_pdb USING 'ptgracscan:1521/TAPDB';

Database link created.

SQL> select sysdate from dual@link_to_tapdb;

SYSDATE

---------

14-OCT-16

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

SQL> CREATE PLUGGABLE DATABASE TAPDBCLONE from TAPDB@link_to_tapdb;

Pluggable database created.

Note:- After from caluse in the create pluggable database syntax you need to mention the remote PDB database name- In this case I have given TAPDB,ie my remote pluggable database that we put in read-only mode.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

5 TAPDBCLONE MOUNTED

SQL> alter pluggable database TAPDBCLONE open instances=all;

Pluggable database altered.

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

4 PDB2 READ WRITE NO

5 TAPDBCLONE READ WRITE NO

SQL> conn mahi/mahi@10.81.155.146:1521/TAPDBCLONE

Connected.

SQL> select * from tab;

TNAME

----------------

TABTYPE CLUSTERID

------- ----------

TABLE

SQL> select count(1) from t1;

COUNT(1)

----------

SQL> select count(1) from t2;

COUNT(1)

----------

2338

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

Lets open the source pluggable database back to read-write mode

[oracle@mydb01]:[TDCDB] $ . oraenv

ORACLE_SID = [TDCDB] ? MYCDB1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@mydb01]:[MYCDB1] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 14 16:28:11 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 TAPDB READ ONLY NO

SQL> alter pluggable database TAPDB close immediate ;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 TAPDB MOUNTED

SQL> alter pluggable database TAPDB open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 TAPDB READ WRITE NO

SQL>

Thursday, October 13, 2016

Creating a Clone PDB Using a Local PDB

We can clone a new pdb into the same location(where source pdb's datafile resides) or into a different location than the source pdb database. Here I used OMF for configuring my CDB database.In this demonstration I am considering two case,

1.Clone a pdb from the local pdb into the same mount point/disk group as that of source PDB.

[oracle@mydb01]:[TDREC] $ . oraenv

ORACLE_SID = [TDREC] ? TDCDB

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:10:13 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

I will create one user in my source PDB.

SQL> alter session set container=PDB2;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB2

SQL> create user mahi identified by mahi;

User created.

SQL> grant dba to mahi;

Grant succeeded.

SQL> conn mahi/mahi

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> exit

Basically you can’t use connect clause within a PDB. So I will connect to PDB2 using tns entry and will create one table on it.

[oracle@mydb01]:[TDCDB] $ sqlplus mahi/mahi@PDB2

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:18:48 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table ta (id number );

Table created.

SQL> insert into ta values (11);

1 row created.

SQL> insert into ta values (12);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ta;

----------

SQL> alter session set container=cdb$root;

ERROR:

ORA-01031: insufficient privileges

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:20:00 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 MOUNTED

SQL> alter pluggable database pdb2 open read only;

Pluggable database altered.

SQL> show parameter db_create

NAME TYPE VALUE

------------------------------------ ----------- --------

db_create_file_dest string /data3

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> create pluggable database pdb2_clone from pdb2;

Pluggable database created.

SQL> alter pluggable database pdb2_clone open;

Pluggable database altered.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

--------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_system_czvwqlcv_.dbf 5 PDB2_CLONE

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_sysaux_czvwqld1_.dbf 5 PDB2_CLONE

13 rows selected.

You could see both datafiles are created within the mountpont(/data3) as that of my source PDB. Login to the new pluggable database and verify the user and tables.

SQL> alter session set container=PDB2_CLONE;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB2_CLONE

SQL> sho user;

USER is "SYS"

SQL> select * from mahi.ta;

----------

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

PDB2_CLONE 5 READ WRITE

Lets open our source database(PDB2) back to normal read-write mode.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ ONLY

PDB2_CLONE 5 READ WRITE

SQL> alter pluggable database pdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

PDB2_CLONE 5 READ WRITE

2.Clone a PDB from the local PDB into a different mountpoint/diskgroup than the source PDB.

Set the new OMF path from root container – note that new OMF path sould be set from the root container cdb$root, and not from the source pdb database.

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:45:47 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show parameter create ;

NAME TYPE VALUE

------------------------------------ ----------- -----------

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string /data3

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> alter system set db_create_file_dest='/data1';

System altered.

SQL> show parameter create ;

NAME TYPE VALUE

------------------------------------ ----------- -----------

create_bitmap_area_size integer 8388608

create_stored_outlines string

db_create_file_dest string /data1

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ ONLY

PDB2 4 READ WRITE

PDB2_CLONE 5 READ WRITE

SQL> create pluggable database pdb1_clone from pdb1;

Pluggable database created.

SQL> alter pluggable database pdb1_clone open;

Pluggable database altered.

SQL> set lines 222

col file_name for a81

col con_name for a15

select a.name "file_name",a.CON_ID,b.NAME "con_name" from v$datafile a,v$containers b where a.con_id=b.con_id order by a.CON_ID;

file_name CON_ID con_name

--------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/datafile/o1_mf_system_cz9f6hfw_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_sysaux_cz9f6mwy_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_undotbs1_cz9f6pc3_.dbf 1 CDB$ROOT

/data3/TDCDB/datafile/o1_mf_users_cz9f75cx_.dbf 1 CDB$ROOT

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_system_cz9f6kjf_.dbf 2 PDB$SEED

/data3/TDCDB/3E1AABF18F9224FCE053929B510AFA95/datafile/o1_mf_sysaux_cz9f6ob6_.dbf 2 PDB$SEED

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_system_cz9kdysr_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_sysaux_cz9kdysy_.dbf 3 PDB1

/data3/TDCDB/3E1BAC67DF755A38E053929B510AB754/datafile/o1_mf_users_cz9kf3f7_.dbf 3 PDB1

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_system_czpbmw7q_.dbf 4 PDB2

/data3/TDCDB/3E7D3CCB54754352E053929B510A3A5B/datafile/o1_mf_sysaux_czpbmw7w_.dbf 4 PDB2

file_name CON_ID con_name

--------------------------------------------------------------------------------- ---------- ---------------

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_system_czvwqlcv_.dbf 5 PDB2_CLONE

/data3/TDCDB/3EA7B41A2375762AE053929B510A1C46/datafile/o1_mf_sysaux_czvwqld1_.dbf 5 PDB2_CLONE

/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_system_czvy5pjw_.dbf 6 PDB1_CLONE

/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_sysaux_czvy5pjy_.dbf 6 PDB1_CLONE

/data1/TDCDB/3EA80C16908B3C48E053929B510AD194/datafile/o1_mf_users_czvy5pk0_.dbf 6 PDB1_CLONE

16 rows selected.

You could see the datafiles of new PDB created within /data1 mount point instead of /data3 mount point.

Lets drop all pluggable database that we cloned.

[oracle@mydb01]:[TDCDB] $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 19:49:44 2016

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,

Advanced Analytics and Real Application Testing options

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter pluggable database pdb2_clone close immediate;

Pluggable database altered.

SQL> drop pluggable database pdb2_clone including datafiles;

Pluggable database dropped.

SQL> alter pluggable database pdb1_clone close immediate;

Pluggable database altered.

SQL> drop pluggable database pdb1_clone including datafiles;

Pluggable database dropped.

SQL> select NAME,CON_ID,OPEN_MODE from v$containers;

NAME CON_ID OPEN_MODE

------------------------------ ---------- ----------

CDB$ROOT 1 READ WRITE

PDB$SEED 2 READ ONLY

PDB1 3 READ WRITE

PDB2 4 READ WRITE

SQL>

Nothing but my learning and experience with Oracle..!

Pages

Friday, October 14, 2016

Creating a Clone PDB from a Remote PDB using database link

Thursday, October 13, 2016

Creating a Clone PDB Using a Local PDB

Popular Posts