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