Actual measurement: Oracle 19C hot cloning application pitfall avoidance guide

1. Background

As Oracle 11g enters the extended support stage, Oracle 19C, as the final stable version of the 12C family, has been well known and used in production by most companies. My company is also trying to deploy, test, upgrade, and migrate 19C, so I took this opportunity to test the hot cloning feature.

2. Prerequisites for using hot cloning

1. Requires 12C R2 and above versions

In 12C R1, to clone a PDB, the source PDB must be in a quiescent state during the cloning operation, so it requires the source PDB to be down, colloquially known as a “cold clone”.

Starting with 12C R2 and later releases, “hot cloning” is supported, which is the ability of Oracle Database to use online cloning. When the source PDB is opened in read-write mode, the cloning operation can be performed without interrupting the operations in the source PDB or shutting down the application.

2. You must use local undo

When using share undo, you need to convert share undo to local undo before hot cloning can be used. You can use alter database local undo on in upgrade mode to convert.

3. Working principle

As can be seen from the three pictures below, whether it is local cloning, remote cloning, or non-cdb cloning, backup and recovery are performed in a similar way to rman. Hot cloning will have the following 3 stages:

Phase 1: When hot cloning starts (t0), the data file of the source PDB is read in blocks until the last block of the source PDB is read and copied to the target PDB (t1). At this time t0- Changes may have been made to some blocks that were already replicated during the t1 period. Well, at this stage, the target PDB may not be physically consistent with the source PDB.

Phase 2: Transfer the changes made to the source PDB between t0-t1 to the target PDB for redo application. At this stage, the target PDB will become a physical copy of the source PDB at t1, but this includes both committed and uncommitted transactions, so there may be transactional inconsistencies.

Phase 3: As of t1, the source PDB contains all committed transactions, all uncommitted transactions will be rolled back, and the target PDB will be a transactionally consistent copy of the source PDB as of t1. It can be seen that the key to implementing hot cloning is local undo, so hot cloning must use local undo.

Figure 1 Local PDB clone

Figure 2 Remote PDB clone

Figure 3 Remote non-cdb clone

4. Common application scenarios

1. Local cloning

1) Clone through seed template

This method is mainly used to create a new PDB using the seed template.

① Check the status of pdb

SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>------------------------------------ ----- ----------</code><code> 2 PDB$SEED READ ONLY NO

Copy code

② View the datafile of the seed template

SYS@ora19c>select con_id,name from v$datafile where con_id=2;</code>
<code> CON_ID NAME</code><code>---------- ---------------------------- --------------------------------</code><code> 2 /u01/app/oracle/oradata/ORA19C /pdbseed/system01.dbf</code><code> 2 /u01/app/oracle/oradata/ORA19C/pdbseed/sysaux01.dbf</code><code> 2 /u01/app/oracle/oradata/ORA19C/pdbseed /undotbs01.dbf

Copy code

③ Use the seed template to clone the new PDB without performing any operations on the source database and specify the data file conversion directory mapping

SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_mgr1 IDENTIFIED BY oracle roles=(dba) file_name_convert=('/u01/app/oracle/oradata/ORA19C/pdbseed','/u01/app/oracle /oradata/ORA19C/pdb1');</code>
<code>Pluggable database created.

Copy code

④ Open a new PDB for verification

SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1 MOUNTED</code>
<code>SYS@ora19c>alter pluggable database pdb1 open;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1 READ WRITE NO</code>
<code>SYS@ora19c>select con_id,name from v$datafile where con_id=3;</code>
<code>CON_ID NAME</code><code>---------- ---------------------------- -------------------------------------</code><code>3 /u01/app/ oracle/oradata/ORA19C/pdb1/system01.dbf</code><code>3 /u01/app/oracle/oradata/ORA19C/pdb1/sysaux01.dbf</code><code>3 /u01/app/oracle/ oradata/ORA19C/pdb1/undotbs01.dbf

Copy code

2) Clone an existing PDB

This method is often used to quickly create a local mirror of an existing PDB, which has exactly the same data, structure, users, permissions, etc. as the source PDB.

① Create a u1 user for the newly created PDB1 and authorize it, and verify whether the clone will clone the user and permissions

SYS@ora19c>alter session set container=pdb1;</code><code>Session altered.</code>
<code>SYS@ora19c>create user u1 identified by oracle;</code>
<code>User created.</code>
<code>SYS@ora19c>grant connect,resource to u1;</code>
<code>Grant succeeded.

Copy code

② Clone PDB2 from the existing PDB1, and the source library can be operated directly in read write mode

SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1 READ WRITE NO</code>
<code>SYS@ora19c>create pluggable database pdb2 from pdb1 file_name_convert=('pdb1','pdb2');</code>
<code>Pluggable database created.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1 READ WRITE NO</code><code> 4 PDB2 MOUNTED

Copy code

③ Open the newly created PDB for verification

SYS@ora19c>alter pluggable database pdb2 open;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>select con_id,name from v$datafile where con_id=4;</code>
<code> CON_ID NAME</code><code>---------- ---------------------------- -------------------------------------</code><code> 4 /u01/app/ oracle/oradata/ORA19C/pdb2/system01.dbf</code><code> 4 /u01/app/oracle/oradata/ORA19C/pdb2/sysaux01.dbf</code><code> 4 /u01/app/oracle/ oradata/ORA19C/pdb2/undotbs01.dbf

Copy code

④ Verify whether the cloned new library has the users and permissions of the source library

SYS@ora19c>conn u1/[email protected]/pdb2</code><code>Connected.</code><code>[email protected]/pdb2>select * from session_privs;</code>
<code>PRIVILEGE</code><code>------------------------------------------ -</code><code>SET CONTAINER</code><code>CREATE INDEXTYPE</code><code>CREATE OPERATOR</code><code>CREATE TYPE</code><code>CREATE TRIGGER</code> <code>CREATE PROCEDURE</code><code>CREATE SEQUENCE</code><code>CREATE CLUSTER</code><code>CREATE TABLE</code><code>CREATE SESSION</code>
<code>[email protected]/pdb2>select * from session_roles;</code>
<code>ROLE</code><code>------------------------------------------ ------------------</code><code>CONNECT</code><code>RESOURCE</code><code>SODA_APP

Copy code

2. Remote cloning

1) Clone the existing remote PDB

This method is often used to quickly create a mirror of an existing PDB between different machines, with exactly the same data, structure, users, permissions, etc. as the source PDB.

① Source database pdb_mgr1 user is granted create pluggable database permission

SYS@ora19c>alter session set container=pdb1;</code>
<code>Session altered.</code>
<code>SYS@ora19c>grant create pluggable database to pdb_mgr1;</code>
<code>Grant succeeded.

Copy code

② Create db link in the target CDB

SYS@ora19c>create public database link lk_pdb1 connect to pdb_mgr1 identified by oracle using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.101)(PORT=1521))(CONNECT_DATA=(SERVER =DEDICATED)(SERVICE_NAME=pdb1)))';</code>
<code>Database link created.

Copy code

③ To perform remote cloning operation, the source library does not need to perform other operations and can be operated under read write

SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1_r FROM pdb1@lk_pdb1 file_name_convert=('pdb1','pdb1_r');</code>
<code>Pluggable database created.

Copy code

④ Open the newly created PDB for verification

SYS@ora19c>alter pluggable database pdb1_r open;</code><code>Pluggable database altered.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1_R READ WRITE NO</code>
<code>SYS@ora19c>select con_id,name from v$datafile where con_id=3;</code>
<code> CON_ID NAME</code><code>---------- ---------------------------- ----------------------------------</code><code> 3 /u01/app/oracle/oradata /ORA19C/pdb1_r/system01.dbf</code><code> 3 /u01/app/oracle/oradata/ORA19C/pdb1_r/sysaux01.dbf</code><code> 3 /u01/app/oracle/oradata/ORA19C /pdb1_r/undotbs01.dbf

Copy code

2) Remote cloning Non-CDB

This method is often used for Non-CDB off-site migration CDB to generate a new PDB.

① Check the status of the source library

SYS@noncdb>select name,cdb,con_id from v$database; </code><code>NAME CDB CON_ID</code><code>--------------- ------------ --------- ----------</code><code>NONCDB NO 0

Copy code

② Source database pdb_mgr1 user is granted create pluggable database permission

SYS@noncdb>grant create pluggable database to system;</code>
<code>Grant succeeded.

Copy code

③ Create db link in target CDB

SYS@ora19c>create public database link lk_noncdb connect to system identified by oracle using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.101)(PORT=1521))(CONNECT_DATA=(SERVER =DEDICATED)(SERVICE_NAME=noncdb)))';</code>
<code>Database link created.</code>
<code>SYS@ora19c>select name,cdb,con_id from v$database@lk_noncdb;</code>
<code>NAME CDB CON_ID</code><code>--------------------------- --------- - ---------</code><code>NONCDB NO 0

Copy code

④ Perform remote cloning of noncdb

SYS@ora19c>CREATE PLUGGABLE DATABASE noncdb_pdb FROM noncdb@lk_noncdb file_name_convert=('/u01/app/oracle/oradata/NONCDB','/u01/app/oracle/oradata/ORA19C/noncdb_pdb') ;</code>
<code>Pluggable database created.

Copy code

⑤ Open a new PDB for verification

SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1_R READ WRITE NO</code><code> 5 NONCDB_PDB MOUNTED</code>
<code>SYS@ora19c>alter pluggable database NONCDB_PDB open;</code>
<code>Warning: PDB altered with errors.

Copy code

⑥ open failed, execute nocdb to pdb script

SYS@ora19c>alter session set container=NONCDB_PDB;</code>
<code>Session altered.</code>
<code>SYS@ora19c>@?/rdbms/admin/noncdb_to_pdb.sql

Copy code

⑦ Open the newly created PDB for verification

SYS@ora19c>alter pluggable database NONCDB_PDB open;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1_R READ WRITE NO</code><code> 5 NONCDB_PDB READ WRITE NO</code><code>SYS@ora19c>select con_id,name from v$datafile where con_id=5;</code>
<code> CON_ID NAME</code><code>---------- ---------------------------- --------------------------------------------------</code><code> 5/ u01/app/oracle/oradata/ORA19C/noncdb_pdb/system01.dbf</code><code> 5 /u01/app/oracle/oradata/ORA19C/noncdb_pdb/sysaux01.dbf</code><code> 5 /u01/ app/oracle/oradata/ORA19C/noncdb_pdb/undotbs01.dbf</code><code> 5 /u01/app/oracle/oradata/ORA19C/noncdb_pdb/users01.dbf

Copy code

5. Special application scenarios

1. Subset cloning

Starting from 12.1.0.2, User Tablespaces has been introduced, which simply means that you can clone PDB by tablespace (created by users). For example, in the current PDB1, the user has created two new table spaces, ts1 and ts2. Cloning only requires the data in the ts1 table space. Then we can use the USER_TABLESPACES clause to clone only the ts1 table space in PDB1, which greatly shortens the time and Unnecessary space overhead. It is also useful for splitting data. You can split a library according to table spaces.

grammar:

USER_TABLESPACES=ALL By default, all table spaces are cloned;
USER_TABLESPACES=NONE All user-created table spaces will not be cloned;
USER_TABLESPACES=(ts1’) specifies to clone only ts1;
USER_TABLESPACES=ALL EXCEPT(ts1’) Clone all table spaces except ts1.

1) The source database creates table spaces ts1, ts2

SYS@ora19c>create tablespace ts1 datafile '/u01/app/oracle/oradata/ORA19C/pdb1/ts1.dbf' size 10m;</code>
<code>Tablespace created.</code>
<code>SYS@ora19c>create tablespace ts2 datafile '/u01/app/oracle/oradata/ORA19C/pdb1/ts2.dbf' size 10m;</code>
<code>Tablespace created.

Copy code

2) Perform subset cloning and only clone the ts1 table space

SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1_z FROM pdb1 file_name_convert=('pdb1','pdb1_z') user_tablespaces=('ts1');</code>
<code>Pluggable database created.

Copy code

3) Open the newly created PDB for verification

SYS@ora19c>alter pluggable database pdb1_z open;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1 READ WRITE NO</code><code> 4 PDB2 READ WRITE NO</code><code> 6 PDB1_Z READ WRITE NO</code>
<code>SYS@ora19c>select con_id,name from v$datafile where con_id=6;</code>
<code> CON_ID NAME</code><code>---------- ---------------------------- --------------------------------</code><code> 6 /u01/app/oracle/oradata/ORA19C /pdb1_z/system01.dbf</code><code> 6 /u01/app/oracle/oradata/ORA19C/pdb1_z/sysaux01.dbf</code><code> 6 /u01/app/oracle/oradata/ORA19C/pdb1_z /undotbs01.dbf</code><code> 6 /u01/app/oracle/oradata/ORA19C/pdb1_z/ts1.dbf

Copy code

To clone only a subset of metadata, use the no data, creation syntax:

create pluggable database pdb_nodata from pdb1 file_name_convert=('pdb1','pdb1_nodata') no data;

Copy code

2. Use the function of refreshing PDB for data migration

The refreshable PDB functionality is based on hot cloning.

When the amount of data in the production PDB is very large and data migration needs to be performed within a short window of time, everything will become simple with the functions of refreshing the PDB and hot cloning. There is no need to worry about how long cloning will take because there is no downtime to the source database. When the target PDB becomes stale, we can refresh it, applying all the deltas accumulated since the last refresh. Even if the source database is very large, incremental redo will usually be much smaller. Finally, only when cutover is required, set the source PDB to read only and perform an incremental refresh.

Please note the following points when refreshing PDB:

The source library must enable archive logging and local undo;
It can be refreshed manually or automatically at a scheduled time, but the target must be in the mounted state when refreshing;
During non-refresh periods, the target can be opened in read-only mode;
If you need to open the target in read-write mode, you must set the refresh mode to none. After setting none, you cannot return to other refresh modes;
You must use dblink to refresh PDB. dblink can point to the same CDB or different CDBs.

1) Create db link in the target PDB

SYS@ora19c>create public database link lk_pdb1 connect to pdb_mgr1 identified by oracle using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.8.101)(PORT=1521))(CONNECT_DATA=(SERVER =DEDICATED)(SERVICE_NAME=pdb1)))';</code>
<code>Database link created.

Copy code

2) Create refresh PDB through db link

SYS@ora19c>CREATE PLUGGABLE DATABASE pdb1_ref FROM pdb1@lk_pdb1 file_name_convert=('pdb1','pdb1_ref') REFRESH MODE EVERY 60 MINUTES;</code>
<code>Pluggable database created.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1_R READ WRITE NO</code><code> 4 PDB1_REF MOUNTED</code><code> 5 NONCDB_PDB READ WRITE NO

Copy code

3) When PDB is in REFRESH mode, it can only have two states: mounted and read only

SYS@ora19c>alter pluggable database pdb1_ref open;</code><code>alter pluggable database pdb1_ref open</code><code>*</code><code>ERROR at line 1:</code><code>ORA-65341: cannot open pluggable database in read/write mode</code>
<code>SYS@ora19c>alter pluggable database pdb1_ref open read only;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>select pdb_id,pdb_name,refresh_mode from cdb_pdbs;</code>
<code>PDB_ID PDB_NAME REFRES</code><code>---------- --------------- ------</code><code> 2 PDB$SEED NONE</code><code> 4 PDB1_REF AUTO</code><code> 5 NONCDB_PDB NONE</code><code> 3 PDB1_R NONE

Copy code

4) PDB can only use the REFRESH function in the mounted state

SYS@ora19c>alter pluggable database refresh; </code><code>alter pluggable database refresh</code><code>*</code><code>ERROR at line 1:</code><code> ORA-65025: Pluggable database PDB1_REF is not closed on all instances.</code>
<code>Alert log:</code><code>PDB1_REF(4):PDB1_REF(4):ERROR:PDB needs to be closed for auto refresh</code><code>PDB1_REF(4):Completed: alter pluggable database refresh

Copy code

5) Source PDB creates test data

[email protected]/pdb1>create table t1 as select * from dba_objects;</code>
<code>Table created.</code>
<code>[email protected]/pdb1>select count(*) from t1;</code>
<code> COUNT(*)</code><code>----------</code><code> 72359

Copy code

6) Simulate the application to stop the application and set the source PDB to read only

SYS@ora19c>alter pluggable database pdb1 close immediate;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>alter pluggable database pdb1 open read only;</code>
<code>Pluggable database altered.

Copy code

7) Manually refresh the target end, apply the latest increment, and observe whether the directory is normal

SYS@ora19c>alter pluggable database pdb1_ref refresh;</code>
<code>Pluggable database altered.</code>
<code>Alert log:</code><code>2020-02-19T13:23:44.457060 + 08:00</code><code>alter pluggable database pdb1_ref refresh</code><code>2020-02-19T13 :23:45.940479 + 08:00</code><code>Applying media recovery for pdb-4099 from SCN 2793352 to SCN 2793357</code><code>Remote log information: count-1</code><code>thr -1, seq-12, logfile-/u01/app/oracle/product/db_1/dbs/archparlog_1_12_4aa635f6_1029786031.arc, los-2752894, nxs-18446744073709551615</code><code>PDB1_REF(4):Media Recovery Start</code> code><code>2020-02-19T13:23:45.942469 + 08:00</code><code>PDB1_REF(4):Serial Media Recovery started</code><code>PDB1_REF(4):max_pdb is 9</code><code>2020-02-19T13:23:45.996021 + 08:00</code><code>PDB1_REF(4):Media Recovery Log /u01/app/oracle/product/db_1/dbs/archparlog_1_12_4aa635f6_1029786031.arc </code><code>2020-02-19T13:23:46.257650 + 08:00</code><code>PDB1_REF(4):Incomplete Recovery applied until change 2793357 time 02/19/2020 13:23:09</code><code>2020-02-19T13:23:46.264473 + 08:00</code><code>PDB1_REF(4):Media Recovery Complete (ora19c)</code><code>Completed: alter pluggable database pdb1_ref refresh

Copy code

8) Target PDB turns off refresh mode

SYS@ora19c>ALTER PLUGGABLE DATABASE pdb1_ref REFRESH MODE NONE;</code>
<code>Pluggable database altered

Copy code

9) Pull up the target PDB

SYS@ora19c>ALTER PLUGGABLE DATABASE pdb1_ref open read write;</code>
<code>Pluggable database altered.</code>
<code>SYS@ora19c>show pdbs;</code>
<code> CON_ID CON_NAME OPEN MODE RESTRICTED</code><code>---------- ------------------------- ----- ---------- ----------</code><code> 2 PDB$SEED READ ONLY NO</code><code> 3 PDB1_R MOUNTED</code><code> 4 PDB1_REF READ WRITE NO</code><code> 5 NONCDB_PDB MOUNTED

Copy code

10) The application connects to the new PDB and verifies the business

SYS@ora19c>conn u1/[email protected]/pdb1_ref</code><code>Connected.</code>
<code>[email protected]/pdb1_ref>select count(*) from t1;</code>
<code> COUNT(*)</code><code>----------</code><code> 72359

Copy code

6. Common errors in hot cloning

Error 1:
Solution: alter session set container=cdb$root;
Error 2:
Solution: Go to the source library and grant the user create pluggable database permission.
Mistake 3:
Solution: File mapping path problem, make a one-to-one correspondence between “folder-folder” or “file-file”.
Mistake 4:
Solution: Wrong path or pay attention to the case in the path.
Mistake 5:
Solution: When creating a refreshable PDB, the archive mode is not turned on at the source.

7. Summary

Hot cloning methods are currently relatively mature and can be used flexibly, suitable for a variety of application scenarios. It can be applied both to quickly create a complete copy or a subset copy of the production environment, as well as to migrate with less downtime. The business interruption time is short or even no business interruption is required. The operation is simple and error-free. However, certain scenarios require higher environmental requirements.

About the author:

Wang Yibin, Xinju Network database expert. Proficient in database operation and maintenance technologies such as Oracle and MySQL, with certifications such as Oracle OCM and MySQL OCP, and rich experience in system architecture design and data migration. He is good at Oracle SQL optimization and participates in the optimization of multiple core systems in the telecommunications industry.

Original link:

Account has been migrated