Table of contents
Preamble
Whether you like it or not Multitenant is the way to go ! Starting with 19c the option has been made free for 3 pluggable databases (PDBs) per container database (CDB):
And in 21c the non-CDB architecture is desupported (not deprecated), this is clearly stated in upgrade guide.
For my testing I have created an empty pluggable database with my own admin account using:
SQL> CREATE pluggable DATABASE pdb1 admin USER yjaquier IDENTIFIED BY "secure_password" roles = (dba) DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/ORCL1/pdb1/users01.dbf' SIZE 100m file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdbseed','/u01/app/oracle/oradata/ORCL1/pdb1') path_prefix = '/u01/app/oracle/oradata/ORCL1/pdb1'; Pluggable DATABASE created. SQL> ALTER pluggable DATABASE pdb1 OPEN; Pluggable DATABASE altered. SQL> !ls -l /u01/app/oracle/oradata/ORCL1/pdb1 total 850040 -rw-r----- 1 oracle dba 356524032 Jul 12 15:13 sysaux01.dbf -rw-r----- 1 oracle dba 304095232 Jul 12 15:13 system01.dbf -rw-r----- 1 oracle dba 104865792 Jul 12 15:13 temp01.dbf -rw-r----- 1 oracle dba 104865792 Jul 12 15:13 undotbs01.dbf -rw-r----- 1 oracle dba 104865792 Jul 12 15:13 users01.dbf SQL> ALTER pluggable DATABASE pdb1 save state; Pluggable DATABASE altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO |
In the local PDB account I create a test table to demonstrate figures copy:
SQL> CREATE TABLE test01(id NUMBER, descr VARCHAR2(50)); TABLE created. SQL> ALTER USER yjaquier quota unlimited ON users; USER altered. SQL> INSERT INTO test01 VALUES (1, 'One'); 1 ROW created. SQL> INSERT INTO test01 VALUES (2, 'Two'); 1 ROW created. SQL> COMMIT; COMMIT complete. |
For proxy and remote cloning I have also created a global PDB admin account:
SQL> CREATE USER c##pdbadmin IDENTIFIED BY "secure_password"; USER created. SQL> GRANT CREATE SESSION, sysoper, CREATE pluggable DATABASE TO c##pdbadmin container=ALL; GRANT succeeded. |
Remark:
If your PDB$SEED tempfile has a strange name use alter tablespace temp add tempfile and alter tablespace temp drop tempfile to adjust it.
The purpose of those blog posts is to experiment rapid provisioning of production database copy and potential automated refresh of live production database. I will also experiment what is called application containers to provision instant fresh application installation for POC or training purposes.
This first part is about pluggable database upgrade as well as features that required special Oracle engineered system or special feature.
Testing has been done on Red Hat Enterprise Linux Server release 7.9 (Maipo) using Oracle Enterprise Edition 19c (19.15) and Oracle Enterprise Edition 21c (21.6) for the upgrade part.
Pluggable database upgrade from 19c (19.15) to 21c (21.6)
First things first let’s migrate a PDB from 19c to 21c. I will do it on my unique test machine but outside the physical copy it is exactly same process between different nodes (if operating system are different with different endian then it is more complex with a datafiles conversion). The now recommended method for database upgrade (whatever upgrade you do) is autoupgrade, it used to be DBUA but Mike and his team has done a hard job on this new tool. You can find tons of article about it on Mike’s blog (https://mikedietrichde.com/). The MOS Document 2485457.1 provide the latest release of this Java tool.
I start by creating a sample config file:
[oracle@server01 ~]$ java -jar autoupgrade.jar -create_sample_file config Created sample configuration file /home/oracle/sample_config.cfg |
That I have customized for my PDB1 pluggable database. The decision I have taken is to copy the datafiles from the non-OMF location to the new non-OMF location (datafiles copy):
[oracle@server01 ~]$ grep -v ^# autoupgrade.cfg global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade upg2.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/orcl1 upg2.sid=orcl1 upg2.source_home=/u01/app/oracle/product/19.0.0/dbhome_1 upg2.target_cdb=orcl2 upg2.target_home=/u01/app/oracle/product/21.0.0/dbhome_1 upg2.pdbs=pdb1 upg2.target_pdb_name.pdb1=upgpdb1 upg2.target_pdb_copy_option.pdb1=file_name_convert=('/u01/app/oracle/oradata/ORCL1/pdb1', '/u01/app/oracle/oradata/ORCL2/pdb1_upg') upg2.run_utlrp=yes upg2.timezone_upg=yes |
Remark:
DO NOT PUT an ending slash at the end f your Oracle home or the tool will not be able to connect to your databases !!
Check if the upgrade is possible with:
[oracle@server01 ~]$ java -jar autoupgrade.jar -config autoupgrade.cfg -mode analyze AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM) AutoUpgrade 22.3.220503 launched with default internal options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 PDB(s) will be analyzed Type 'help' to list console commands upg> Job 100 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log |
Review the log file for potential actions to perform before the upgrade and to understand what will be done while and after the upgrade automatically by the tool.
I upgrade on the same server so (refer to documentation if upgrading to a different server):
[oracle@server01 ~]$ java -jar autoupgrade.jar -config autoupgrade.cfg -mode deploy AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM) AutoUpgrade 22.3.220503 launched with default internal options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 PDB(s) will be processed Type 'help' to list console commands upg> lsj +----+-------+---------+---------+-------+----------+-------+----------------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+----------------------------+ | 102| orcl1|PRECHECKS|EXECUTING|RUNNING| 17:21:16| 0s ago|Loading database information| +----+-------+---------+---------+-------+----------+-------+----------------------------+ Total jobs 1 upg> lsj +----+-------+-----+---------+-------+----------+-------+-------------------+ |Job#|DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+-----+---------+-------+----------+-------+-------------------+ | 102| orcl1|DRAIN|EXECUTING|RUNNING| 17:21:16| 0s ago|Compatibility check| +----+-------+-----+---------+-------+----------+-------+-------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+------------------+ | 102| orcl1|DBUPGRADE|EXECUTING|RUNNING| 17:21:16|60s ago|0%Upgraded UPGPDB1| +----+-------+---------+---------+-------+----------+-------+------------------+ Total jobs 1 upg> lsj +----+-------+---------+---------+-------+----------+-------+-------------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE| +----+-------+---------+---------+-------+----------+-------+-------------------+ | 102| orcl1|DBUPGRADE|EXECUTING|RUNNING| 17:21:16|14s ago|52%Upgraded UPGPDB1| +----+-------+---------+---------+-------+----------+-------+-------------------+ Total jobs 1 upg> lsj +----+-------+----------+---------+-------+----------+-------+-------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE| +----+-------+----------+---------+-------+----------+-------+-------+ | 102| orcl1|POSTFIXUPS|EXECUTING|RUNNING| 17:21:16|26s ago| | +----+-------+----------+---------+-------+----------+-------+-------+ Total jobs 1 upg> Job 102 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs restored [0] Jobs pending [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log |
Check all the logs to ensure you have no error to resolve…
And magically on my 21c CDB instance (the one on my 19c CDB has gone):
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 UPGPDB1 READ WRITE NO |
Cleaning:
SQL> DROP pluggable DATABASE upgpdb1 including datafiles; DROP pluggable DATABASE upgpdb1 including datafiles * ERROR AT line 1: ORA-65025: Pluggable DATABASE UPGPDB1 IS NOT closed ON ALL instances. SQL> ALTER pluggable DATABASE upgpdb1 CLOSE IMMEDIATE; Pluggable DATABASE altered. SQL> DROP pluggable DATABASE upgpdb1 including datafiles; Pluggable DATABASE dropped. |
Creating a split mirror clone pluggable database
This feature is inherited from ASM and has been extended directly at PDB level. But you need to rely on ASM, which is not my case and will be the case for really few customers not running RAC:
SQL> ALTER pluggable DATABASE prepare mirror copy pdb1_mirror01; ALTER pluggable DATABASE prepare mirror copy pdb1_mirror01 * ERROR AT line 1: ORA-15120: ASM FILE name '/u01/app/oracle/oradata/ORCL2/pdb1/system01.dbf' does NOT BEGIN WITH the ASM prefix character |
Cloning a pluggable database from a pluggable snapshot
A PDB snapshot is a point-in-time copy of a PDB and a PDB snapshot carousel is a collection of PDB snapshots. I start by taking a snapshot of one of my PDB:
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> ALTER SESSION SET container=pdb1; SESSION altered. SQL> ALTER pluggable DATABASE snapshot pdb1_snap01; ALTER pluggable DATABASE snapshot pdb1_snap01 * ERROR AT line 1: ORA-12754: Feature PDB SNAPSHOT CAROUSEL IS disabled due TO missing capability . |
As we can see on the extract from the licensing guide the feature to create a pluggable database from snapshot and the associated snapshot carousel feature is not available on Enterprise Edition (EE column). You need to be on Engineered Systems (EE-ES column) or in the Cloud to be able to use them.
References
