PLUGGABLE DATABASE HOT CLONE REFRESH AUTO

Cihan Gedik

6 years ago

Author : Cihan Gedik

Email : cihangedik@gmail.com

Linkedin : https://www.linkedin.com/in/cihan-gedik-13b55065/

Group : https://www.linkedin.com/groups/13792740/

PLUGGABLE DATABASE HOT CLONE REFRESH AUTO

Compatible Versions

Oracle Database 12.1.x and Later

Requirements

One Source PDB in CDB

One Empty CDB for Target

In this post ;

Database Version : 19C

PDB01 is my source pluggable database in CDB01

PDB01_HOT_MAN is my target clone pluggable database in CDB02

Solution

For this LAB Please create below requirements

Create PDB01 in CDB01 and insert some data

Create Empty CDB02 Database

#### Create CDB01

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname CDB01 -sid CDB02 -createAsContainerDatabase true -numberofPDBs 0 -useLocalUndoForPDBS true -responseFile NO_VALUE -totalMemory 1800 -sysPassword welcome1 -systemPassword welcome1 -pdbAdminPassword welcome1 -enableArchive true -recoveryAreaDestination /u01/app/oracle/databases/recovery_area/CDB01 -recoveryAreaSize 1500 -datafileDestination /u01/app/oracle/databases/datafiles/CDB01

Create PDB01 and Insert Some Data

create pluggable database PDB01 admin user adminpdb01 identified by welcome1 roles=(CONNECT) CREATE_FILE_DEST='/u01/app/oracle/databases/datafiles/PDB01';
alter pluggable database PDB01 open;

#### Generate Data via Toad

Create some tables as you wish and use toad to insert data

Toad > Administrator > Import Data > Generate Data

sqlplus as sysdba

connect sys@PDB01 as sysdba

Enter password:

### Grant Create PDB Privilege to the user which you will use.. I used system user in lab

grant create pluggable database to system;

Grant succeeded.



Note : Until this point you should have PDB01 in CDB01 Database with some data populated into tables

### This is the data Before Clone on Source PDB01
sqlplus system/welcome1 as sysdba
alter session set container=PDB01;


select count(*) from scott.DEPT;


COUNT(*)

----------

14000


select count(*) from scott.emp;

COUNT(*)

----------

14000

#### Create CDB02 with no PDB

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname CDB02 -sid CDB02 -createAsContainerDatabase true -numberofPDBs 0 -useLocalUndoForPDBS true -responseFile NO_VALUE -totalMemory 1800 -sysPassword welcome1 -systemPassword welcome1 -pdbAdminPassword welcome1 -enableArchive true -recoveryAreaDestination /u01/app/oracle/databases/recovery_area/CDB02 -recoveryAreaSize 1500 -datafileDestination /u01/app/oracle/databases/datafiles/CDB02

sqlplus system/welcome1 as sysdba

select instance_name from v$instance;


INSTANCE_NAME

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

CDB02

####Create DB Link

Syntax : create pluggable database <NEW PDB NAME> from <SOURCEPDBNAMETOCLONE>@<DBLINKONTARGETTOSOURCE> refresh mode every 1 minutes;
alter session set db_create_file_dest='/u01/app/oracle/databases/datafiles/CDB02/PDB01_HOT_MAN';

create pluggable database PDB01_HOT_MAN from PDB01@PDB_SOURCE_FOR_HOT_CLONE refresh mode every 1 minutes;

Pluggable database created.




show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01_HOT_MAN MOUNTED

#### Before Insert some data into PDB01 in CDB01 and Refresh PDB01_HOT_MAN on the target site in CDB02. Lets see how many rows we have.

alter pluggable database PDB01_HOT_MAN open read only;

alter session set container=PDB01_HOT_MAN;








select count(*) "CDB02 EMP TABLE" from scott.emp;

CDB02 EMP TABLE
---------------
14000

SQL> select count(*) "CDB02 DEPT TABLE" from scott.dept;

CDB02 DEPT TABLE
----------------
14000

#### 7000 rows are inserted into PDB01 in CDB01.. Not lets close and re-open our Clone PDB and Check Rows

alter pluggable database PDB01_HOT_MAN close; ##### Close and Wait For 1 Minute

alter pluggable database PDB01_HOT_MAN open read only;

Pluggable database altered.

SQL> select count(*) "CDB02 EMP TABLE" from scott.emp;

CDB02 EMP TABLE
---------------
21000

SQL> select count(*) "CDB02 DEPT TABLE" from scott.dept;

CDB02 DEPT TABLE
----------------
21000

Read the following post to learn how to perform pluggable database hot clone refresh manual

PLUGGABLE DATABASE HOT CLONE REFRESH MANUAL