Today my experiment will be upgrading 18c (18.14) to the latest 19c version (currently it is 19.12) in multitenant architecture with 2 PDBs. The idea of this scenario is to validate and test not only the upgrade process but especially the downgrade strategy.
Please note I will keep the COMPATIBLE parameter of my target 19c as 18.0.0 so I can try my fallback strategy without any issues.
My playground will be:
- Source DB (18.14.0):
- Running on server odbfcl-18-0-0-0
- 18.14 RU
dbca -silent -createDatabase \ -gdbName orcl \ -templateName General_Purpose.dbc \ -characterSet AL32UTF8 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbAdminPassword "Rodrigo.123" \ -pdbName PDB01 \ -useLocalUndoForPDBs true \ -databaseConfigType SINGLE \ -databaseType MULTIPURPOSE \ -datafileDestination /u01/app/oradata \ -dvConfiguration false \ -emConfiguration NONE \ -enableArchive false \ -memoryMgmtType AUTO_SGA \ -totalMemory 4000 \ -nationalCharacterSet AL16UTF16 \ -olsConfiguration false \ -recoveryAreaDestination /u01/app/fra \ -sampleSchema false \ -sid orcl \ -storageType FS \ -useOMF false \ -sysPassword "Rodrigo.123" \ -systemPassword "Rodrigo.123"
- Target DB (19.12.0):
- Running on server odbfcl-19-0-0-0
- 19.12 RU
Preparing my source environment
In my 18c CDB source system, I will create a new PDB and some user data, so I can check them later after the migration.
[oracle@odbfcl-18-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Sep 30 19:33:19 2021
Version 18.14.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.14.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> CREATE PLUGGABLE DATABASE PDB02 ADMIN USER PDBADMIN IDENTIFIED BY "Rodrigo.123" file_name_convert=('/u01/app/oradata/ORCL/pdbseed/','/u01/app/oradata/ORCL/PDB02/');
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE PDB02 OPEN;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 READ WRITE NO
SQL> alter session set container=PDB01;
Session altered.
SQL> CREATE USER TEST IDENTIFIED BY "oracle";
User created.
SQL> GRANT DBA TO TEST;
Grant succeeded.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oradata/ORCL/PDB01/undotbs01.dbf
/u01/app/oradata/ORCL/PDB01/sysaux01.dbf
/u01/app/oradata/ORCL/PDB01/system01.dbf
/u01/app/oradata/ORCL/PDB01/users01.dbf
SQL> CREATE TABLESPACE TOOLS DATAFILE '/u01/app/oradata/ORCL/PDB01/tools01.dbf' size 10M;
Tablespace created.
SQL> CREATE TABLE TEST.T1 TABLESPACE TOOLS AS SELECT * FROM DBA_TABLES;
Table created.
SQL> select count(*) from TEST.T1;
COUNT(*)
----------
2134
SQL>
Upgrading first
The first step is to perform the upgrade of the 18c. I will use AutoUpgrade as it automates most of the tasks for me.
My config file has:
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/18c upg1.sid=orcl upg1.source_home=/u01/app/oracle/product/18.0.0.0/dbhome_1 upg1.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1 upg1.target_version=19 upg1.upgrade_node=localhost upg1.run_utlrp=yes upg1.timezone_upg=no
As I'm also moving to a new server, I will break the AutoUpgrade in the 3 steps approach. Please note you don't need to break it in 3 phases if you have the new Oracle Home version already deployed on that same server.
So first, let me start with the analyse phase:
[oracle@odbfcl-18-0-0-0 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config ./upgrade.cfg -mode analyze AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be analyzed Type 'help' to list console commands upg> Job 101 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] ------------- JOBS FINISHED WITH ERROR ------------- Job 101 for orcl 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
Next, the fixups:
[oracle@odbfcl-18-0-0-0 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config ./upgrade.cfg -mode fixups AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> Job 102 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] ------------- JOBS FINISHED WITH ERROR ------------- Job 102 for orcl 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
The next step is to stop the database and move all the database and configuration files (including datafiles, controlfiles, spfile, pwfile) and also the files generated by AutoUpgrade tool (upgrade.cfg and /u01/app/oracle/cfgtoollogs/autoupgrade/ folder) to the new server. Note again you won't need this if you are upgrading to the same server.
[oracle@odbfcl-18-0-0-0 ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Thu Sep 30 20:12:27 2021 Version 18.14.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.14.0.0.0 SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.14.0.0.0
After everything is placed, I need to start up the database in 19c Oracle Home in upgrade mode:
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 30 20:30:25 2021 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 3154114080 bytes Fixed Size 9139744 bytes Variable Size 754974720 bytes Database Buffers 2382364672 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> exit; Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0
And finally, I will call the upgrade phase of AutoUpgrade on the target machine:
[oracle@odbfcl-19-0-0-0 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config ./upgrade.cfg -mode upgrade AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> Job 103 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [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
Now just checking if my database was really upgraded:
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 00:14:27 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 READ WRITE NO
SQL> alter session set container=PDB01;
Session altered.
SQL> select count(*) from TEST.T1;
COUNT(*)
----------
2134
SQL> select cname, prv_version, version from registry$;
CNAME PRV_VERSION VERSION
-------------------------------------------------- ------------------------------ ------------------------------
Oracle Database Catalog Views 18.0.0.0.0 19.0.0.0.0
Oracle Database Packages and Types 18.0.0.0.0 19.0.0.0.0
Oracle XML Database 18.0.0.0.0 19.0.0.0.0
Oracle Real Application Clusters 18.0.0.0.0 19.0.0.0.0
Oracle Workspace Manager 18.0.0.0.0 19.0.0.0.0
JServer JAVA Virtual Machine 18.0.0.0.0 19.0.0.0.0
Oracle XDK 18.0.0.0.0 19.0.0.0.0
Oracle Database Java Packages 18.0.0.0.0 19.0.0.0.0
Oracle Text 18.0.0.0.0 19.0.0.0.0
Oracle Multimedia 18.0.0.0.0 19.0.0.0.0
OLAP Analytic Workspace 18.0.0.0.0 19.0.0.0.0
Oracle OLAP API 18.0.0.0.0 19.0.0.0.0
Spatial 18.0.0.0.0 19.0.0.0.0
Oracle Label Security 18.0.0.0.0 19.0.0.0.0
Oracle Database Vault 18.0.0.0.0 19.0.0.0.0
15 rows selected.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 18.0.0
noncdb_compatible boolean FALSE
Downgrade
Now that I'm on 19c, let's imagine a fallback scenario. After running my production in this environment for some time, I want to move back to the original 18c database. I can't simply restore a backup as I already had some production workload executed in this new database. So a downgrade is the only option I have.
Let's start the downgrading process. I followed the official documentation but there is also a MOS Note for it.
The overall steps are:
- Taking a backup of my CDB before starting.
- Cleaning out the UNIFIED_AUDIT_TRAIL.
- Starting CDB in downgrade mode.
- Calling DB downgrade
- $ORACLE_HOME/bin/dbdowngrade
- $ORACLE_HOME/bin/dbdowngrade
- Startup the database in the original Oracle Home
- Run catrelod.sql
First, I need to clean (or backup) the UNIFIED_AUDIT_TRAIL in all the containers:
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 01:40:25 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> select count(*) from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
2091
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
PL/SQL procedure successfully completed.
SQL> alter session set container=PDB01;
Session altered.
SQL> select count(*) from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
1056
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
PL/SQL procedure successfully completed.
SQL> alter session set container=PDB02;
Session altered.
SQL> select count(*) from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
1031
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
PL/SQL procedure successfully completed.
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> select count(*) from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
1035
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
PL/SQL procedure successfully completed.
The next step is to start up the database in downgrade mode:
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 1 02:36:01 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup downgrade;
ORACLE instance started.
Total System Global Area 3154114080 bytes
Fixed Size 9139744 bytes
Variable Size 637534208 bytes
Database Buffers 2499805184 bytes
Redo Buffers 7634944 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open downgrade;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PDB01 MIGRATE YES
4 PDB02 MIGRATE YES
Finally, calling the $ORACLE_HOME/bin/dbdowngrade wrapper:
[oracle@odbfcl-19-0-0-0 ~]$ sh $ORACLE_HOME/bin/dbdowngrade Downgrading containers catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd_catcon_21426.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd_*.lst] files for spool files, if any catcon.pl: completed successfully [oracle@odbfcl-19-0-0-0 ~]$ grep '^ORA-' /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd*.log [oracle@odbfcl-19-0-0-0 ~]$
As you can see above. No errors were returned.
Finally, the last step is to start back the database on the original 18c Oracle Home in upgrade mode. In my case, I had to move back all the database and configuration files (including datafiles, controlfiles, spfile, pwfile) to the original system.
[oracle@odbfcl-18-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 1 02:44:12 2021
Version 18.14.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 3154112808 bytes
Fixed Size 8900904 bytes
Variable Size 771751936 bytes
Database Buffers 2365587456 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PDB01 MIGRATE YES
4 PDB02 MIGRATE YES
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.14.0.0.0
[oracle@odbfcl-18-0-0-0 ~]$
And the last step is to run catrelod.sql, to recreate the database objects for the 18c version:
[oracle@odbfcl-18-0-0-0 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -b catrelod -d $ORACLE_HOME/rdbms/admin catrelod.sql catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/catrelod_catcon_27176.lst] catcon::set_log_file_base_path: catcon: See [/home/oracle/catrelod*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/home/oracle/catrelod_*.lst] files for spool files, if any catcon.pl: completed successfully [oracle@odbfcl-18-0-0-0 ~]$ grep '^ORA-' /home/oracle/catrelod*.log [oracle@odbfcl-18-0-0-0 ~]$
Now I'm with my CDB is back in 18c:
[oracle@odbfcl-18-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 1 03:37:45 2021
Version 18.14.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.14.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PDB01 MIGRATE YES
4 PDB02 MIGRATE YES
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 3154112808 bytes
Fixed Size 8900904 bytes
Variable Size 771751936 bytes
Database Buffers 2365587456 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB02 READ WRITE NO
SQL> alter session set container=PDB01;
Session altered.
SQL> select count(*) from TEST.T1;
COUNT(*)
----------
2134
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.14.0.0.0
And that's it! As you can see, there is no mystery about moving forward and back from 19c to 18c.
Have you enjoyed? Please leave a comment or give a 👍!
2 comments
Why do you need to shutdown the database after fix up? is it different server assuming you are using -upgrade method?
after - analyze the -deploy will take care of everything right?
Author
Yes, the only reason I've shut it down is that I used the AU to also move to a new server. Otherwise, if it was the same server, "-deploy" would take care of everything.