PLUGGABLE DATABASE HOT CLONE REFRESH AUTO

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

 285 views last month,  1 views today

Cihan Gedik

I am currently working as Senior Oracle Application Database Administrator. I have experienced many EBS database upgrade, migrations, Fresh EBS installations, Solaris to Linux, Windows to Linux replatform migration projects in medium and large companies and also experienced core database migration projects for one of the biggest bank of Katar.With my colleagues we decided to run this platform to share our knowledge

Leave a Reply