Author : Cihan Gedik
Email : [email protected]
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