I’m currently refreshing our Hands-On Lab and will exchange Oracle 12.2.0.1 with Oracle 18.2.0. This blog post series is about how to Upgrade to Oracle 12.2.0.1 to Oracle Database 18c on-premises. There are other blog posts available you may use by yourself if you plan to refresh the lab by yourself until we uploaded a newer version. And it’s actually a good exercise.
- Deinstalling Oracle Database 12.2.0.1
- Installing Oracle Database 18.1.0
- Patching Oracle 18.1.0 to 18.2.0
- Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises
For Multitenant upgrades from Oracle 12.1.0.2 to Oracle 12.2.0.1 you may read this:
Oracle Database 18c on-premises is supposed to be generally available with the July 2018 Update. For details please always check the single-source-of-truth MOS Note: 742060.1. Furthermore, please see my previous blog post from March 20, 2018, as well. And I’m very happy to see that some customers already moved to 18c.
Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises
At first – even if you do the entire exercise in a different order, you will have to download the most recent preupgrade.jar from MOS Note:884522.1.Unpack it to any directory:
$ unzip preupgrade_181_cbuild_2_lf.zip Archive: preupgrade_181_cbuild_2_lf.zip inflating: dbms_registry_extended.sql inflating: preupgrade_driver.sql inflating: preupgrade.jar inflating: preupgrade_messages.properties inflating: preupgrade_package.sql $ ls dbms_registry_extended.sql preupgrade.jar preupgrade_181_cbuild_2_lf.zip preupgrade_messages.properties preupgrade_driver.sql preupgrade_package.sql
Afterwards, execute preupgrade.jar. In my example I will do it for the CDB2 Multitenant database in the lab environment:
$ java -jar preupgrade.jar TEXT TERMINAL Report generated by Oracle Database Pre-Upgrade Information Tool Version 18.0.0.0.0 on 2018-06-13T15:08:45 Upgrade-To version: 18.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: CDB2 Container Name: CDB$ROOT Container ID: 1 Version: 12.2.0.1.0 Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 1. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid objects. You can view the individual invalid objects with SET SERVEROUTPUT ON; EXECUTE DBMS_PREUP.INVALID_OBJECTS; 3 objects are INVALID. There should be no INVALID objects in SYS/SYSTEM or user schemas before database upgrade. 2. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters. The database contains the following initialization parameters whose name begins with an underscore: _exclude_seed_cdb_view Remove hidden parameters before database upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the pfile/spfile. 3. (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. INFORMATION ONLY ================ 4. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 550 MB 616 MB SYSTEM 700 MB 1117 MB TEMP 22 MB 150 MB UNDOTBS1 315 MB 433 MB Minimum tablespace sizes for upgrade are estimates. 5. No action needed. Using default parallel upgrade options, this CDB with 1 PDBs will first upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2 parallel processes per PDB. The number of PDBs upgraded in parallel and the number of parallel processes per PDB can be adjusted as described in Database Upgrade Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database CDB2 container CDB$ROOT which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 6. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26 and the target 18.0.0.0.0 release ships with time zone file version 31. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database Globalization Support Guide. 7. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. 8. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database CDB2 container CDB$ROOT which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql Report generated by Oracle Database Pre-Upgrade Information Tool Version 18.0.0.0.0 on 2018-06-13T15:08:58 Upgrade-To version: 18.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: CDB2 Container Name: PDB$SEED Container ID: 2 Version: 12.2.0.1.0 Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG Readonly: TRUE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 1. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid objects. You can view the individual invalid objects with SET SERVEROUTPUT ON; EXECUTE DBMS_PREUP.INVALID_OBJECTS; 6 objects are INVALID. There should be no INVALID objects in SYS/SYSTEM or user schemas before database upgrade. 2. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters. The database contains the following initialization parameters whose name begins with an underscore: _exclude_seed_cdb_view Remove hidden parameters before database upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the pfile/spfile. 3. (AUTOFIXUP) Gather stale data dictionary statistics prior to database upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Dictionary statistics do not exist or are stale (not up-to-date). Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. 4. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade. None of the fixed object tables have had stats collected. Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. INFORMATION ONLY ================ 5. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 235 MB 500 MB SYSTEM 210 MB 584 MB TEMP 20 MB 150 MB UNDOTBS1 210 MB 412 MB Minimum tablespace sizes for upgrade are estimates. 6. No action needed. Using default parallel upgrade options, this CDB with 1 PDBs will first upgrade the CDB$ROOT, and then upgrade at most 1 PDBs at a time using 2 parallel processes per PDB. The number of PDBs upgraded in parallel and the number of parallel processes per PDB can be adjusted as described in Database Upgrade Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database CDB2 container PDB$SEED which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 7. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 26 and the target 18.0.0.0.0 release ships with time zone file version 31. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database Globalization Support Guide. 8. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. 9. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database CDB2 container PDB$SEED which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following from within the container SQL>@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql ================== PREUPGRADE SUMMARY ================== /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade.log /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql Execute fixup scripts across the entire CDB: Before upgrade: 1. Execute preupgrade fixups with the below command $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ -b preup_CDB2 /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql 2. Review logs under /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ After the upgrade: 1. Execute postupgrade fixups with the below command $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ -b postup_CDB2 /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/postupgrade_fixups.sql 2. Review logs under /u01/app/oracle/cfgtoollogs/CDB2/preupgrade/ Preupgrade complete: 2018-06-13T15:09:00
You may recognize that the script’s output is now better structured. And whenever it says AUTOFIXUP, there’s nothing to do for myself as this will be executed by the preupgrade_fixups.sql.
Execute the necessary fixups
The log of preupgrade.jar highlights that I have invalid objects I should recompile before upgrade:
1. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
6 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
Therefore, I’m following the advice:
set serverout on EXECUTE DBMS_PREUP.INVALID_OBJECTS; SYS/SYSTEM INVALID OBJECTS OWNER |OBJECT_NAME |OBJECT_TYPE -------------------------------------------------------------------------------- ------------------------------------------------ NON SYS/SYSTEM INVALID OBJECTS OWNER |OBJECT_NAME |OBJECT_TYPE -------------------------------------------------------------------------------- ------------------------------------------------ PUBLIC LOCAL_CHUNKS SYNONYM PUBLIC LOCAL_CHUNK_TYPES SYNONYM PUBLIC SHA_DATABASES SYNONYM
I clean it up with the recommended procedure and double-check afterwards:
start ?/rdbms/admin/utlrp EXECUTE DBMS_PREUP.INVALID_OBJECTS; PL/SQL procedure successfully completed.
“No results” means: everything is valid now.
Afterwards I kick of the preupgrade_fixups.sql and wait for its completion.
@/u01/app/oracle/cfgtoollogs/CDB2/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2018-06-13 15:08:42
For Source Database: CDB2
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 18.0.0.0.0
Executing in container: CDB$ROOT
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. invalid_objects_exist YES None.
2. hidden_params NO Informational only.
Further action is optional.
3. dictionary_stats YES None.
4. tablespaces_info NO Informational only.
Further action is optional.
5. cycle_number NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
The “PREUP ACTION NUMBER” match the enumeration from the output of preupgrade.jar. This will make your life easier.
My database is now ready for upgrade. I can shut it down.
create pfile from spfile; shutdown immediate
If you don’t want the Hands-On Lab to grow a lot, it may be wise to deinstall the 12.2. software at this point, then install Oracle 18c, patch it and upgrade the database. Otherwise an additional Oracle Home will increase the size of the lab quite a bit.
To remove Oracle 12.2.0.1 at this point, follow this blog posts, then return afterwards and continue here.
- Deinstalling Oracle Database 12.2.0.1 [Will be available on June 19, 2018]
As next step I will have to install Oracle Database 18c. Please follow this blog post:
- Installing Oracle Database 18c [Will be available on June 20, 2018]
Finally I believe you always want to apply the most recent Update (RU) before you really start the upgrade. Find the details here:
- Patch Oracle 18.1.0 to 18.2.0 [Will be available on June 21, 2018]
And once I completed all these actions I can go forward and upgrade my database(s).
Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises
I kick off the upgrade as usual with catctl.pl:
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /home/oracle/log catupgrd.sql
And after a while, the database got upgraded (on very slow hardware in this case):
...
------------------------------------------------------
Grand Total Time: 2127s [PDB$SEED]
LOG FILES: (/home/oracle/log/catupgrdpdb_seed*.log)
Upgrade Summary Report Located in:
/home/oracle/log/upg_summary.log
Total Upgrade Time: [0d:0h:35m:27s]
Time: 2284s For CDB$ROOT
Time: 2133s For PDB(s)
Grand Total Time: 4417s
LOG FILES: (/home/oracle/log/catupgrd*.log)
Upgrade Summary Report Located in:
/home/oracle/log/upg_summary.log
Grand Total Upgrade Time: [0d:1h:13m:37s]
Post-Upgrade Steps
Finally I will execute a few post-upgrade steps.
At first, I start the recompilation:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 4 -e -b utlrp -d '''.''' utlrp.sql
And as the last step I execute the postupgrade_fixups.sql:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 4 -d /u01/app/oracle/cfgtoollogs/CDB2/preupgrade -l /home/oracle/log -b postupgrade_fixups postupgrade_fixups.sql
Final check:
select count(*) from cdb_objects where status='INVALID'; COUNT(*) ---------- 0
That’s it. Done.
–Mike
Mike,
What improvements have been made to the upgrade of the Oracle schema (i.e. catctl, dbua) to reduce the elapsed time and the corresponding downtime? This blog shows catctl being used and the elapsed time was 1h:13m:37s. The time was approximately 36 minutes for each container (i.e. cdb$root, pdb$seed) You did say the hardware was very slow, so the time may not be valid for comparison to prior upgrades.
If it takes approximately the same type to upgrade each PDB/container in the database, would it be faster to create a new database using the new software release and plug the PDBs, other than cdb$root and pdb$seed, into the new database?
Thank you, in advance.
Dean,
yes, this is true. And a valid point.
Single Tenant environments have the downside of running 3 upgrades for 1 database. And two of the phases sequentially, i.e. the CDB$ROOT first as a single upgrade, and then afterwards the PDB$SEED (which includes a forced recompilation) and the PDB containing data/application/users, both at least in parallel.
Hence, your proposed solution (have a new CDB in the new environment, unplug PDB from env_old and plugin into env_new, then upgrade it) is the much faster approach as you have only one single upgrade.
Cheers,
Mike
Mike,
The 18c upgrade documentation has an option -T which puts the application tablespaces in read only mode. I tested an upgrade with this option (dbupgrade -T) and my upgrade just hung up. There was no entry in alert log, nothing in catupgrd*.log. Finally, after more than an hour, I killed the upgrade process and re-started without the -T option and upgrade finished in 25 minutes. When it hung up, this was the last entry in catupgrd0.log:
Total Number of Phases: 109
Number of Cpus = 28
Database Name =
DataBase Version = 11.2.0.4.0
Log file directory = [/u01/app/oracle/product/18.0.0.0/dbhome_1/cfgtoollogs//upgrade20180713093129]
Parallel SQL Process Count = 4
Components in []
Installed [CATALOG CATJAVA CATPROC JAVAVM ORDIM RAC XDB XML]
Not Installed [APEX APS CONTEXT DV EM MGW ODM OLS OWM SDO WK XOQ]
Have you had any issues with this option?
Thanks,
Arun
Arun,
can you give me a list of your tablespaces please?
Something such as:
select tablespace_name, contents from dba_tablespaces order by 1;
Can you check also which tablespaces have been taken READ ONLY?
The option is meant to be used with a PARTIAL OFFLINE BACKUP only. But of course it shouldn’t get stuck.
Thanks,
Mike
There are 189 tablespaces, PERMANENT, TEMP and UNDO. I cannot post the entire list here as it may not be permitted by our security folks. None of these are in READ ONLY mode. I did not see anything about PARTIAL OFFLINE BACKUP in the upgrade guide. Did I look in the wrong place?
https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/database-preparation-tasks-to-complete-before-upgrades.html#GUID-E10096D0-36E3-41AF-9381-D74089FC6E17
Is guaranteed restore point a good way to rollback the upgrade if all goes south? This will work till the COMPATIBLE parameter is raised, and will protect against any catastrophic failures. If there are major issues after increasing COMPATIBLE, there is always the hot backup. We can do a DBPITR.
Thanks for your help…
Arun
Hi Arun,
I will explain the Partial Offline Backup strategy a bit more in a blog post today or tomorrow.
My suspicion is that there’s a tablespace read only where we want to update information it it (it could be also AQ).
And yes, GRP is the best way:
https://mikedietrichde.com/2017/08/29/fallback-strategy-flashback-to-guaranteed-restore-points/
Thanks,
Mike