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 👍!
