Creating a snapshot sparse clone from a different Release Update

Those days, I was testing if it is possible to create a snapshot sparse clone in one database referencing a PDB on a lower release (like 19.11), and later applying datapatch on this cloned PDB to take it to the higher release (like 19.12).

Technically I couldn't see any constraint, given the Snapshot Copy requirements are already met. But nothing better than testing to check if something works.

So let's try it!

• Source DB:
• • CDB: RJ1911
  • PDB: PDB1911
• Target DB:
  • CDB: RJ1912
  • PDB: PDB1912

Preparing the source (19.11):

[oracle@exacs-c0gab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 9 18:01:44 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> CREATE PLUGGABLE DATABASE PDB1911 file_name_convert=('+DATAC1','+SPRC1')
  2  ADMIN USER myadm IDENTIFIED BY password ROLES=(DBA);

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE PDB1911 OPEN;

Pluggable database altered.

SQL> CREATE USER c##pdb_clone_user IDENTIFIED BY "Rodrigo.12345" CONTAINER=ALL;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##pdb_clone_user CONTAINER=ALL;

Grant succeeded.

SQL> alter pluggable database PDB1911 close;

Pluggable database altered.

SQL> alter pluggable database PDB1911 open read only;

Pluggable database altered.

SQL>

Now connecting on the target (19.12):

[oracle@exacs-c0gab1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 9 18:05:03 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> CREATE DATABASE LINK RJ1911
  2  CONNECT TO c##pdb_clone_user IDENTIFIED BY "Rodrigo.12345"
  3  USING 'exacs-c0gab-scan.x.x.oraclevcn.com:1521/RJ1911.x.x.oraclevcn.com';

Database link created.

SQL> select * from dual@RJ1911;

D
-
X

SQL> CREATE PLUGGABLE DATABASE PDB1912 FROM PDB1911@RJ1911 snapshot copy create_file_dest='+SPRC1';

Pluggable database created.

SQL> alter pluggable database PDB1912 open;

Warning: PDB altered with errors.

SQL> set lines 10000 pages 10000 tab off
SQL> select * from pdb_plug_in_violations where name='PDB1912';

TIME                            NAME      CAUSE                     TYPE      ERROR_NUMBER       LINE MESSAGE                                                                                                                                                                           STATUS    ACTION                                                 CON_ID
------------------------------- --------- ------------------------- --------- ------------ ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- -------------------------------------------------- ----------
09-SEP-21 06.08.13.722151 PM    PDB1912   Parameter                 WARNING              0          1 CDB parameter wallet_root mismatch: Previous '/var/opt/oracle/dbaas_acfs/RJ1911/wallet_root' Current '/var/opt/oracle/dbaas_acfs/RJ1912/wallet_root'                              PENDING   Please check the parameter in the current CDB               1
09-SEP-21 06.08.13.722715 PM    PDB1912   Oracle Opatch             WARNING              0          1 Oracle opatch mismatch: opatch 32490416 is missing in the CDB.                                                                                                                    PENDING   Install the Oracle opatch in the CDB.                       1
09-SEP-21 06.08.13.722850 PM    PDB1912   Oracle Opatch             WARNING              0          2 Oracle opatch mismatch: opatch 32399816 is missing in the CDB.                                                                                                                    PENDING   Install the Oracle opatch in the CDB.                       1
09-SEP-21 06.08.13.722926 PM    PDB1912   Oracle Opatch             WARNING              0          3 Oracle opatch mismatch: opatch 32579761 is missing in the CDB.                                                                                                                    PENDING   Install the Oracle opatch in the CDB.                       1
09-SEP-21 06.08.13.723071 PM    PDB1912   Oracle Opatch             WARNING              0          4 Oracle opatch mismatch: opatch 32545013 is missing in the CDB.                                                                                                                    PENDING   Install the Oracle opatch in the CDB.                       1
09-SEP-21 06.08.14.164498 PM    PDB1912   SQL Patch                 ERROR                0          1 Interim patch 32876380/24269510 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)): Installed in the CDB but not in the PDB                                                       PENDING   Call datapatch to install in the PDB or the CDB             5
09-SEP-21 06.08.14.166400 PM    PDB1912   SQL Patch                 ERROR                0          2 Interim patch 30310195/24121718 (DBSAT REPORTED DISABLED CONSTRAINTS FOR SHARDING  STS_CHUNKS ON GSMADMIN_INTERNAL.SHARD_TS): Not installed in the CDB but installed in the PDB   PENDING   Call datapatch to install in the PDB or the CDB             5
09-SEP-21 06.08.14.167041 PM    PDB1912   SQL Patch                 ERROR                0          3 Interim patch 32399816/24099425 (OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816)): Not installed in the CDB but installed in the PDB                                             PENDING   Call datapatch to install in the PDB or the CDB             5
09-SEP-21 06.08.14.167585 PM    PDB1912   SQL Patch                 ERROR                0          4 '19.12.0.0.0 Release_Update 2107161418' is installed in the CDB but '19.11.0.0.0 Release_Update 2104130040' is installed in the PDB                                               PENDING   Call datapatch to install in the PDB or the CDB             5
09-SEP-21 06.08.14.168930 PM    PDB1912   is encrypted tablespace?  WARNING          28427          1 Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.                                                                                    PENDING   Encrypt the tablespace.                                     5
09-SEP-21 06.08.14.170047 PM    PDB1912   is encrypted tablespace?  WARNING          28427          2 Tablespace SYSAUX is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.                                                                                    PENDING   Encrypt the tablespace.                                     5
09-SEP-21 06.08.17.622991 PM    PDB1912   Sync Failure              WARNING           4068          2 Sync PDB failed with ORA-04068 while performing 'DROP USER "C##PDB_CLONE_USER"'                                                                                                   PENDING                                                               5

13 rows selected.

SQL>

Ok. So I could create it and the only issues I have while opening it seems to be related to datapatch. Let me run it:

[oracle@exacs-c0gab1 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.12.0.0.0 Production on Thu Sep  9 18:16:59 2021
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_203590_2021_09_09_18_16_59/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 30310195 (DBSAT REPORTED DISABLED CONSTRAINTS FOR SHARDING  STS_CHUNKS ON GSMADMIN_INTERNAL.SHARD_TS):
  Binary registry: Not installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
  PDB PDB1912: Applied successfully on 21-APR-21 09.13.38.257725 AM
Interim patch 32399816 (OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816)):
  Binary registry: Not installed
  PDB CDB$ROOT: Not installed
  PDB PDB$SEED: Not installed
  PDB PDB1912: Applied successfully on 21-APR-21 09.13.38.231464 AM
Interim patch 32876380 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380)):
  Binary registry: Installed
  PDB CDB$ROOT: Applied successfully on 23-JUL-21 03.27.54.717269 PM
  PDB PDB$SEED: Applied successfully on 23-JUL-21 03.28.03.883772 PM
  PDB PDB1912: Not installed

Current state of release update SQL patches:
  Binary registry:
    19.12.0.0.0 Release_Update 210716141810: Installed
  PDB CDB$ROOT:
    Applied 19.12.0.0.0 Release_Update 210716141810 successfully on 23-JUL-21 03.27.54.713346 PM
  PDB PDB$SEED:
    Applied 19.12.0.0.0 Release_Update 210716141810 successfully on 23-JUL-21 03.28.03.879588 PM
  PDB PDB1912:
    Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 21-APR-21 09.13.38.227152 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED
    No interim patches need to be rolled back
    No release update patches need to be installed
    No interim patches need to be applied
  For the following PDBs: PDB1912
    The following interim patches will be rolled back:
      32399816 (OJVM RELEASE UPDATE: 19.11.0.0.210420 (32399816))
      30310195 (DBSAT REPORTED DISABLED CONSTRAINTS FOR SHARDING  STS_CHUNKS ON GSMADMIN_INTERNAL.SHARD_TS)
    Patch 32904851 (Database Release Update : 19.12.0.0.210720 (32904851)):
      Apply from 19.11.0.0.0 Release_Update 210413004009 to 19.12.0.0.0 Release_Update 210716141810
    The following interim patches will be applied:
      32876380 (OJVM RELEASE UPDATE: 19.12.0.0.210720 (32876380))

Installing patches...
Patch installation complete.  Total patches installed: 4

Validating logfiles...done
Patch 32399816 rollback (pdb PDB1912): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/32399816/24099425/32399816_rollback_RJ1912_PDB1912_2021Sep09_18_17_47.log (no errors)
Patch 30310195 rollback (pdb PDB1912): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/30310195/24121718/30310195_rollback_RJ1912_PDB1912_2021Sep09_18_18_00.log (no errors)
Patch 32904851 apply (pdb PDB1912): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_RJ1912_PDB1912_2021Sep09_18_18_07.log (no errors)
Patch 32876380 apply (pdb PDB1912): SUCCESS
  logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/32876380/24269510/32876380_apply_RJ1912_PDB1912_2021Sep09_18_18_00.log (no errors)
SQL Patching tool complete on Thu Sep  9 18:19:13 2021
[oracle@exacs-c0gab1 OPatch]$

And now if I try to reopen the PDB:

[oracle@exacs-c0gab1 OPatch]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 9 18:23:27 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter pluggable database PDB1912 close;

Pluggable database altered.

SQL> alter pluggable database PDB1912 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDB1912                        READ WRITE NO
SQL>

And it worked! So creating snapshot clones between different RU releases can be a nice way for patching testing of your master copies.

Have you enjoyed? Please leave a comment or give a !

+3