Interesting variations in outcome when cloning a CDB with a subset of PDBs in Oracle
Recently I was working on a client request where a CDB along with a subset of its PDBs had to be cloned to a new server for testing purposes. This post illustrates that in certain circumstances the outcome of this activity depends on at least two factors:
- whether we’re duplicating from a database operating in READ-WRITE mode, or from one in MOUNT mode, such as a physical standby. As we’ll see, the differences in behavior could be significant, and not immediately noticeable, as no errors are raised.
- using one of the two forms of the DUPLICATE command: “DUPLICATE..PLUGGABLE DATABASE <list of PDBs to include in the duplication>” or “DUPLICATE .. SKIP PLUGGABLE DATABASE <list of PDBs to exclude from duplication>“. As we’ll see, one is not the opposite of the other.
The different behaviors don’t seem to be bugs, however it’s good to be aware of them, as based on the description from the documentation, they might not be what you’d expect.
In case you want to skip the analysis section, there’s a summary section at the end where the differences among various approaches are summarized.
Ways to clone a CDB with a subset of PDBs
The documentation describes two options to clone a CDB with a subset of PDBs using RMAN’s DUPLICATE command:
1) “PLUGGABLE DATABASE pdb_name“: Duplicates one or more PDBs specified in a comma-delimited list to a new CDB. By default, RMAN also duplicates the root and the seed database of the CDB that contains the listed PDBs.
2) “SKIP PLUGGABLE DATABASE pdb_name“: Duplicates all the PDBs within the CDB, except the ones specified in the comma-separated list pdb_name to a new CDB. By default, RMAN also duplicates the root and the seed database of the CDB.
Given the above description, the seed PDB and everything in CDB$ROOT should always be duplicated alongside the list of PDBs we specified to be (or not to be) included. However, this is not always the case.
Testcase setup
A CDB with three PDBs will be used, and one of those PDBs will be duplicated along with the CDB. The Oracle version I used is 19c RU 16, running on Linux x86 64-bit. The databases are setup as follows:
- CDB2: primary database containing 3 PDBs, open in READ-WRITE mode
- DG01CDB2: physical standby database running in MOUNT mode
- CDBDUP: CDB database name of the future clone. Note that CDBDUP is not pre-created as an empty “shell” CDB database. Before the duplication, the instance is only started in NOMOUNT mode with just a single init parameter, which is “db_name=’CDBDUP'”. So, there are no controlfiles or datafiles yet, as they will be cloned during the execution of the DUPLICATE command.
There are three PDBs in CDB2:
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 PDB3 READ WRITE NO
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM YES /u02/oradata/CDB2/system01.dbf
2 320 PDB$SEED:SYSTEM YES /u02/oradata/CDB2/pdbseed/system01.dbf
3 550 SYSAUX NO /u02/oradata/CDB2/sysaux01.dbf
4 165 PDB$SEED:SYSAUX NO /u02/oradata/CDB2/pdbseed/sysaux01.dbf
5 315 UNDOTBS1 YES /u02/oradata/CDB2/undotbs01.dbf
6 220 PDB$SEED:UNDOTBS1 YES /u02/oradata/CDB2/pdbseed/undotbs01.dbf
7 5 USERS NO /u02/oradata/CDB2/users01.dbf
8 320 PDB1:SYSTEM YES /u02/oradata/CDB2/pdb1/system01.dbf
9 165 PDB1:SYSAUX NO /u02/oradata/CDB2/pdb1/sysaux01.dbf
10 220 PDB1:UNDOTBS1 YES /u02/oradata/CDB2/pdb1/undotbs01.dbf
11 320 PDB2:SYSTEM YES /u02/oradata/CDB2/pdb2/system01.dbf
12 165 PDB2:SYSAUX NO /u02/oradata/CDB2/pdb2/sysaux01.dbf
13 220 PDB2:UNDOTBS1 YES /u02/oradata/CDB2/pdb2/undotbs01.dbf
14 320 PDB3:SYSTEM YES /u02/oradata/CDB2/pdb3/system01.dbf
15 165 PDB3:SYSAUX NO /u02/oradata/CDB2/pdb3/sysaux01.dbf
16 220 PDB3:UNDOTBS1 YES /u02/oradata/CDB2/pdb3/undotbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 105 TEMP 32767 /u02/oradata/CDB2/temp01.dbf
2 105 PDB$SEED:TEMP 32767 /u02/oradata/CDB2/pdbseed/temp01.dbf
3 105 PDB1:TEMP 32767 /u02/oradata/CDB2/pdb1/temp01.dbf
4 105 PDB2:TEMP 32767 /u02/oradata/CDB2/pdb2/temp01.dbf
5 105 PDB3:TEMP 32767 /u02/oradata/CDB2/pdb3/temp01.dbf
Let’s create two tablespaces in CDB$ROOT. The SYS_OBJ tablespace will store sample segments owned by SYS. The NON_SYS_OBJ tablespace will store sample segments owned by a non-SYS user:
create tablespace sys_obj datafile '/u02/oradata/CDB2/sys_obj.dbf' size 50M autoextend off; create tablespace non_sys_obj datafile '/u02/oradata/CDB2/non_sys_obj.dbf' size 50M autoextend off;
Following the directions in “How To Move The DB Audit Trails To A New Tablespace Using DBMS_AUDIT_MGMT? (Doc ID 1328239.1)”, let’s move the AUD$ and FGA_LOG$ tables out of the the SYSTEM tablespace to a different one, SYS_OBJ:
BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, --this moves table AUD$ audit_trail_location_value => 'SYS_OBJ'); END; / BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, --this moves table FGA_LOG$ audit_trail_location_value => 'SYS_OBJ'); END; /
and create some tables in the previously created tablespaces (again, in CDB$ROOT):
-- Create a custom table in the SYS schema in the SYS_OBJ tablespace: create table sys.t_sys tablespace sys_obj as select * from dba_objects; -- Create a second custom table in the SYSTEM schema in the non_sys_obj tablespace: create table system.t_system tablespace non_sys_obj as select * from dba_objects; -- Create a third custom table in the C##TEST_USER schema in the non_sys_obj tablespace: create table C##TEST_USER.t_custom_usr tablespace non_sys_obj as select * from dba_objects;
Duplication of CDB and one PDB from a primary database
In the first duplication attempt, the “DUPLICATE TARGET DATABASE TO CDBDUP PLUGGABLE DATABASE PDB1 FROM ACTIVE DATABASE” syntax is used:
$ rman target sys/oracle@CDB2 auxiliary sys/oracle@CDBDUP
run
{
allocate channel tgt1 device type disk ;
allocate auxiliary channel aux1 device type disk ;
duplicate target database to cdbdup pluggable database pdb1 from active database
spfile
parameter_value_convert '/u02/oradata/CDB2', '/u02/oradata/CDBDUP'
SET DB_UNIQUE_NAME 'CDBDUP'
set control_files='/u02/oradata/CDBDUP/control01.ct', '/u02/fast_recovery_area/CDBDUP/control02.ctl'
set db_file_name_convert='/u02/oradata/CDB2', '/u02/oradata/CDBDUP'
set log_file_name_convert='CDB2', 'CDBDUP'
set dg_broker_start='false'
set audit_file_dest='/u01/app/oracle/admin/CDBDUP/adump'
set diagnostic_dest='/u01/app/oracle'
set db_create_file_dest='/u02/oradata/CDBDUP'
set db_create_online_log_dest_1='/u02/oradata/CDBDUP'
set db_recovery_file_dest='/u02/fast_recovery_area';
}
The duplication log indicates which tablespaces will be skipped from CDB$ROOT, and lists out SYS-owned objects:
Skipping pluggable database PDB2 Skipping pluggable database PDB3 Automatically adding tablespace SYSTEM Automatically adding tablespace SYSAUX Automatically adding tablespace PDB$SEED:SYSTEM Automatically adding tablespace PDB$SEED:SYSAUX Automatically adding tablespace PDB$SEED:SYSTEM Automatically adding tablespace UNDOTBS1 Automatically adding tablespace PDB$SEED:UNDOTBS1 Skipping tablespace USERS <<<----- Skipping tablespace SYS_OBJ <<<----- Skipping tablespace NON_SYS_OBJ <<<----- The following SYS objects were found in skipped tablespaces <<<----- Object SYS.AUD$ on tablespace SYS_OBJ Object SYS.SYS_IL0000000590C00043$$ on tablespace SYS_OBJ Object SYS.SYS_LOB0000000590C00043$$ on tablespace SYS_OBJ Object SYS.SYS_IL0000000590C00041$$ on tablespace SYS_OBJ Object SYS.SYS_LOB0000000590C00041$$ on tablespace SYS_OBJ Object SYS.SYS_IL0000000590C00040$$ on tablespace SYS_OBJ Object SYS.SYS_LOB0000000590C00040$$ on tablespace SYS_OBJ Object SYS.SYS_IL0000025128C00030$$ on tablespace SYS_OBJ Object SYS.SYS_LOB0000025128C00030$$ on tablespace SYS_OBJ Object SYS.SYS_IL0000025128C00028$$ on tablespace SYS_OBJ Object SYS.SYS_LOB0000025128C00028$$ on tablespace SYS_OBJ Object SYS.SYS_IL0000025128C00013$$ on tablespace SYS_OBJ Object SYS.SYS_LOB0000025128C00013$$ on tablespace SYS_OBJ Object SYS.FGA_LOG$ on tablespace SYS_OBJ Object SYS.T_SYS on tablespace SYS_OBJ
Shortly after that, the duplication fails with:
RMAN-05501: aborting duplication of target database RMAN-05553: SYS objects in skipped tablespaces prevent duplication
The MOS note RMAN Duplicate PDB Fails With RMAN-05548 If Audit Trails Are Moved Outside Of SYSAUX (Doc ID 2256158.1) indicates what the underlying problem is, and how to address it. Let’s try to do that.
Amending the list of tablespaces to be duplicated
As indicated by the previously mentioned Doc ID 2256158.1, let’s add the sys_obj tablespace to the DUPLICATE command (only the relevant DUPLICATE part of the command is shown, the rest of the command is the same as in the first example):
duplicate target database to cdbdup pluggable database pdb1 from active database tablespace sys_obj
The duplication completes without errors, the CDB and PDB1 are successfully cloned:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
There were no errors in the duplication log, and we might assume that the whole CDB was duplicated, however checking closer, RMAN output shows that the following tablespaces were skipped:
Skipping tablespace USERS Skipping tablespace NON_SYS_OBJ
As the above tablespaces were skipped, this means that the ‘T_SYSTEM’ and ‘T_CUSTOM_USR’ tables are not available in the clone:
SQL> select owner, object_name from dba_objects where object_name in ('AUD$','T_SYS','T_SYSTEM','T_CUSTOM_USR');
OWNER OBJECT_NAME
----- --------------------
SYS AUD$
SYS T_SYS
This is something to be aware of in case our CDB$ROOT contains such objects, as they won’t be present in the clone.
Including all of the tablespaces in the clone
In case we want to clone the whole CDB$ROOT (with all of it’s tablespaces) and PDB1, we need to list all of the user-created tablespaces in the duplicate command, not only the ones containing SYS-owned objects:
duplicate target database to cdbdup pluggable database pdb1 from active database tablespace sys_obj,non_sys_obj,users
This creates a clone with all of the tablespaces and tables we created above:
SQL> select owner, object_name from dba_objects where object_name in ('AUD$','T_SYS','T_SYSTEM','T_CUSTOM_USR');
OWNER OBJECT_NAME
--------------- --------------------
SYS AUD$
SYS T_SYS
SYSTEM T_SYSTEM
C##TEST_USER T_CUSTOM_USR
RMAN> report schema;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
.
.
17 50 SYS_OBJ NO /u02/oradata/CDBDUP/sys_obj.dbf
18 50 NON_SYS_OBJ NO /u02/oradata/CDBDUP/non_sys_obj.dbf
Alternative syntax to clone PDBs
Another, seemingly easier way to clone some PDBs from a CDB is by using the “SKIP PLUGGABLE DATABASE” syntax. Let’s see what happens if we list the PDBs we don’t want to be duplicated:
duplicate target database to cdbdup skip pluggable database pdb2,pdb3 from active database
The duplication completes without any errors, and we end up with our PDB1 and all of the tablespaces and objects we created:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> select owner, object_name from dba_objects where object_name in ('AUD$','T_SYS','T_SYSTEM','T_CUSTOM_USR');
OWNER OBJECT_NAME
--------------- --------------------
SYS AUD$
SYS T_SYS
SYSTEM T_SYSTEM
C##TEST_USER T_CUSTOM_USR
RMAN> report schema;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
.
.
17 50 SYS_OBJ NO /u02/oradata/CDBDUP/sys_obj.dbf
18 50 NON_SYS_OBJ NO /u02/oradata/CDBDUP/non_sys_obj.dbf
The significant difference in behavior compared to the “DUPLICATE..PLUGGABLE DATABASE <list of PDBs>” is that in this case, we didn’t have to explicitly list any of the tablespaces from CDB$ROOT, like we had to in the second and third test. Tablespaces containing SYS and non SYS-owned were automatically included in the duplicated database. In summary, the whole CDB$ROOT and the desired PDBs get duplicated, which seems the intuitive behavior when duplicating the CDB$ROOT and PDBs. Based on that, using the “SKIP” syntax variant of the DUPLICATE command seems to be the easier approach to get a complete clone of CDB$ROOT.
Duplicating from a physical standby running in MOUNT mode
Let’s repeat the same steps as in the first test, but this time connecting to the standby database as the target. The DUPLICATE command is similar as the one we used above, only the paths and convert parameters changed:
$ rman target sys/oracle@DG01CDB2 auxiliary sys/oracle@CDBDUP
run
{
allocate channel tgt1 device type disk ;
allocate auxiliary channel aux1 device type disk ;
duplicate target database to cdbdup pluggable database pdb1 from active database
spfile
parameter_value_convert '/u02/oradata/DG01CDB2', '/u02/oradata/CDBDUP'
SET DB_UNIQUE_NAME 'CDBDUP'
set control_files='/u02/oradata/CDBDUP/control01.ct', '/u02/fast_recovery_area/CDBDUP/control02.ctl'
set db_file_name_convert='/u02/oradata/DG01CDB2', '/u02/oradata/CDBDUP'
set log_file_name_convert='DG01CDB2', 'CDBDUP'
set dg_broker_start='false'
set audit_file_dest='/u01/app/oracle/admin/CDBDUP/adump'
set diagnostic_dest='/u01/app/oracle'
set db_create_file_dest='/u02/oradata/CDBDUP'
set db_create_online_log_dest_1='/u02/oradata/CDBDUP'
set db_recovery_file_dest='/u02/fast_recovery_area';
}
Whereas the duplication from a read-write database failed with “RMAN-05553: SYS objects in skipped tablespaces prevent duplication”, the duplication from the standby opened in MOUNT mode completes without errors, and we might assume all of the tablespaces in the root container were duplicated, but that’s actually not the case. There’s a difference in how the duplication is performed from a database in MOUNT mode, and RMAN clearly states it in its output, as can be seen in the last line in the below excerpt:
Skipping pluggable database PDB2 Skipping pluggable database PDB3 Automatically adding tablespace SYSTEM Automatically adding tablespace SYSAUX Automatically adding tablespace PDB$SEED:SYSTEM Automatically adding tablespace PDB$SEED:SYSAUX Automatically adding tablespace UNDOTBS1 Skipping tablespace USERS Skipping tablespace SYS_OBJ Skipping tablespace NON_SYS_OBJ TARGET database not open, cannot verify that set of tablespaces being duplicated does not have SYS objects
The end result is that none of the tablespaces and tables we’re observing in this test were duplicated, including AUD$ and FGA_LOG$:
SQL> select owner, object_name from dba_objects where object_name in ('AUD$','FGA_LOG$','T_SYS','T_SYSTEM','T_CUSTOM_USR');
no rows selected
This could have some nasty side effects, especially if it goes unnoticed. In this specific case, when we try to login to the cloned database with auditing enabled, we’ll get:
[oracle@hol trace]$ sqlplus system/oracle SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 24 17:20:19 2022 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. ERROR: ORA-02002: error while writing to audit trail ORA-00942: table or view does not exist ORA-02002: error while writing to audit trail ORA-00942: table or view does not exist ORA-02002: error while writing to audit trail ORA-00942: table or view does not exist Enter user-name:
The solution to avoid the above problem is to use one of the commands we used in the previous examples:
duplicate target database to cdbdup pluggable database pdb1 from active database tablespace sys_obj,non_sys_obj,users
or
duplicate target database to cdbdup skip pluggable database pdb2,pdb3 from active database
Out of curiosity, I tested how the duplication behaves when the source is a standby running in Active Dataguard mode (v$database.open_mode = ‘READ ONLY WITH APPLY’). As it turns out, it behaves as when the database is operating in read-write mode. The only difference I noticed when testing the command
duplicate target database to cdbdup pluggable database pdb1 from active database tablespace sys_obj,non_sys_obj,users
is that, RMAN reports:
database mounted Skipping pluggable database PDB2 Skipping pluggable database PDB3 Automatically adding tablespace SYSTEM Automatically adding tablespace SYSAUX Automatically adding tablespace PDB$SEED:SYSTEM Automatically adding tablespace PDB$SEED:SYSAUX RMAN-05519: warning: tablespace PDB$SEED:SYSTEM is always included when duplicating Automatically adding tablespace UNDOTBS1 Automatically adding tablespace PDB$SEED:UNDOTBS1 Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained <<-----
However, all of the indicated tablespaces get duplicated.
Summary
There are differences in the cloned database when duplicating a subset of PDBs from a CDB which has user-created tablespaces in CDB$ROOT.
Source DB is operating in READ-WRITE or READ-ONLY mode
Option #1 – list PDBs to be cloned
Syntax:
Result: RMAN automatically includes the SYSTEM, SYSAUX, and UNDO tablespaces from CDB$ROOT in the duplicated database. However, it skips the duplication of all the other tablespaces from CDB$ROOT. In case any of the skipped tablespaces contains any SYS-owned objects, the duplication fails with “RMAN-05553: SYS objects in skipped tablespaces prevent duplication”.
Amending option #1
If we want to include the skipped tablespaces, we must explicitly list all of them in the duplicate command:
Option #2 – list PDBs to be skipped in the clone
Syntax:
Result: All of the datafiles in CDB$ROOT, and PDBs not present in the SKIP list get duplicated. This variant of the DUPLICATE command seems to be the easier and more intuitive approach in order to get a complete clone of CDB$ROOT and desired PDBs.
Source DB is in MOUNT mode
Contrary to the case where the source is in open or read-only mode, the duplication from the source DB doesn’t raise any errors about skipping tablespaces containing SYS-owned objects. The duplication lists the skipped tablespaces in the log, skips them during duplication, and completes without errors. The cloned DB might thus be missing some tablespaces in CDB$ROOT.
To avoid this, use one of the commands we already used above where we either specify all of the user-created tablespaces in DB$ROOT, or use the “SKIP” version of the DUPLICATE command:
or
