Oracle database version 18.104.22.168 is recently released. I searched the database software for Solaris platform, Found it in oracle edelivery.
I downloaded the software from edelivery and did an installation and upgrade on a test box.
Upgrade database from 11g to 12cR1 using manual method
Upgrade database from 11g to 12cR1 Using DBUA method:
Upgrade database from 11g to 12cR1 from OEM 12c cloud control:
Here we will first Install 12.2. ORACLE_HOME and then upgrade the database TESTDB ( which is on 22.214.171.124 version) to 126.96.36.199
NEW ORACLE_HOME – /oracle/app/oracle/product/188.8.131.52/dbhome-1
Now we will proceed with upgrade activity:
DBNAME – TESTDB
CURRENT ORACLE_HOME – /oracle/app/oracle/product/12.1.0/dbhome_1
NEW ORACLE_HOME – /oracle/app/oracle/product/184.108.40.206/dbhome-1
1. Run the preupgrade tool
Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.
ls -ltr /oracle/app/oracle/product/220.127.116.11/dbhome-1/rdbms/admin/preupgrade.jar
$OLD_ORACLE_HOME/jdk/bin/java -jar $NEW_ORACLE_HOME/rdbms/admin/preupgrade.jar -FILE DIR $OS_DIRECTORY
/oracle/app/oracle/product/12.1.0/dbhome_1/jdk/bin/java -jar /oracle/app/oracle/product/18.104.22.168/dbhome-1/rdbms/admin/preupgrade.jar FILE DIR /export/home/oracle/UPG122 Preupgrade generated files: /export/home/oracle/UPG122/preupgrade.log /export/home/oracle/UPG122/preupgrade_fixups.sql /export/home/oracle/UPG122/postupgrade_fixups.sql
/export/home/oracle# cat /export/home/oracle/UPG122/preupgrade.log Report generated by Oracle Database Pre-Upgrade Information Tool Version 22.214.171.124.0 Upgrade-To version: 126.96.36.199.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: TESTDB Container Name: TESTDB Container ID: 0 Version: 188.8.131.52.0 Compatible: 184.108.40.206.0 Blocksize: 8192 Platform: Solaris[tm] OE (64-bit) Timezone File: 18 Database log mode: ARCHIVELOG 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 Oracle Workspace Manager [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run /preupgrade_fixups.sql to complete all of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ + Adjust TABLESPACE SIZES as needed. Auto 220.127.116.11.0 Tablespace Size Extend Min Size Action ---------- ---------- -------- ---------- ------ SYSAUX 550 MB DISABLED 500 MB None SYSTEM 700 MB ENABLED 1017 MB None TEMP 20 MB ENABLED 150 MB None UNDOTBS1 300 MB ENABLED 400 MB None Note that 18.104.22.168.0 minimum sizes are estimates. If you plan to upgrade multiple pluggable databases concurrently, then you must ensure that the UNDO tablespace size is equal to at least the number of pluggable databases that you upgrade concurrently, multiplied by that minimum. Failing to allocate sufficient space can cause the upgrade to fail. INFORMATION ONLY ================ + Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least 2979 MB of archived logs. Check alert log during the upgrade that there is no write error to the destination due to lack of disk space. Execute 'archive log list' and query v$archive_dest for more LOG_ARCHIVE_DEST_ destinations to check. Archiving cannot proceed if the archive log destination is full during upgrade. Archive Log Destination: Parameter : LOG_ARCHIVE_DEST_1 Destination : /archive/TESTDB The database has archiving enabled and LOG_ARCHIVE_DEST_ set. The upgrade process will need free disk space in the archive log destination(s) to generate archived logs to. ============= AFTER UPGRADE ============= Run /postupgrade_fixups.sql to complete all of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== + Upgrade the database time zone version using the DBMS_DST package. The database is using timezone datafile version 18 and the target 22.214.171.124.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data. For further information, refer to My Oracle Support Note 1585343.1. + (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.
Run the preupgrade_fixups.sql file.
SQL> @/export/home/oracle/UPG122/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 126.96.36.199.0 Build: 1 Generated on: 2017-02-21 17:50:51 For Source Database: TESTDB Source Database Version: 188.8.131.52.0 For Upgrade to Version: 184.108.40.206.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ min_archive_dest_size Failed Manual fixup recommended. PL/SQL procedure successfully completed.
Please take necessary again if any issues reported by the precheck script.
2. List down invalid objects present
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; no rows selected
3. Make sure source db in archivelog mode.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /archive/TESTDB Oldest online log sequence 195 Next log sequence to archive 197 Current log sequence 197
4. Enable flashback mode, to enable restore in case of any failure during upgrade.
SQL> show parameter db_recover NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> alter system set db_recovery_file_dest_size=5G scope=both; System altered. SQL> alter system set db_recovery_file_dest=^C SQL> alter system set db_recovery_file_dest='/archive/TESTDB' scope=both; System altered. SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
5. Set environment variables to NEW ORACLE_HOME(12.2)
6. Run DBUA
This screen has been changed, compare to previous dbua in 220.127.116.11.
1. No option to control upgrade parallelism, DBUA pickup parallelism , according to system resource
2. No option to control utlrp.sql compliation parallelism thread.
Select FLASHBACK OPTION, It will create a guaranteed restore point.
We have a PAUSE button here. Let’s click and see if we can resume it later or not.
Message shows, Executions will pause after Database Components Upgrade is completed. I.e PAUSE will not stop the upgrade process instantly, It will wait for the current phase to complete.
So upgrade continued until the Database components Upgrade completed.
You can monitor the upgrade log at below location.
upgrade log location: /oracle/app/oracle/cfgtoollogs/dbua/upgrade2017-02-21_05-55-20-PM/TESTDB tail -100f Oracle_Server.log Number of Cpus = 96 Database Name = TESTDB DataBase Version = 18.104.22.168.0 Parallel SQL Process Count = 4 Components in [TESTDB] Installed [CATALOG CATJAVA CATPROC JAVAVM OWM XDB XML] Not Installed [APEX APS CONTEXT DV EM MGW ODM OLS ORDIM RAC SDO ------------------------------------------------------ Phases [0-115] Start Time:[2017_02_21 18:01:57] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [TESTDB] Files:1 Time: 44s *************** Catalog Core SQL *************** Serial Phase #:1 [TESTDB] Files:5 Time: 47s Restart Phase #:2 [TESTDB] Files:1 Time: 1s *********** Catalog Tables and Views *********** Parallel Phase #:3 [TESTDB] Files:19 Time: 18s Restart Phase #:4 [TESTDB] Files:1 Time: 1s ************* Catalog Final Scripts ************ Serial Phase #:5 [TESTDB] Files:6 Time: 19s ***************** Catproc Start **************** Serial Phase #:6 [TESTDB] Files:1 Time: 20s ***************** Catproc Types **************** Serial Phase #:7 [TESTDB] Files:2 Time: 17s Restart Phase #:8 [TESTDB] Files:1 Time: 1s **************** Catproc Tables **************** Parallel Phase #:9 [TESTDB] Files:69 Time: 26s Restart Phase #:10 [TESTDB] Files:1 Time: 1s ************* Catproc Package Specs ************ Serial Phase #:11 [TESTDB] Files:1 Time: 56s Restart Phase #:12 [TESTDB] Files:1 Time: 0s ************** Catproc Procedures ************** Parallel Phase #:13 [TESTDB] Files:97 Time: 12s Restart Phase #:14 [TESTDB] Files:1 Time: 1s Parallel Phase #:15 [TESTDB] Files:118 Time: 20s Restart Phase #:16 [TESTDB] Files:1 Time: 1s Serial Phase #:17 [TESTDB] Files:13 Time: 4s Restart Phase #:18 [TESTDB] Files:1 Time: 1s ***************** Catproc Views ****************
As soon as database upgrade component completed upgrade stopped due to PAUSE button we have used. Click on Continue to resume the upgrade .
Upgrade has been completed successfully.
POST UPGRADE :
1. Run postupgrade_fixups.sql script
SQL> @ /export/home/oracle/UPG122/postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 22.214.171.124.0 Build: 1 Generated on: 2017-02-21 17:50:52 For Source Database: TESTDB Source Database Version: 126.96.36.199.0 For Upgrade to Version: 188.8.131.52.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ old_time_zones_exist Passed None post_dictionary Passed None PL/SQL procedure successfully completed. Session altered.
2. Check the latest timezone version:
SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_26.dat 26 0
3. Check the component status:
SQL> select comp_id,status,version from dba_registry; COMP_ID STATUS VERSION ------------------------------ ----------- ------------------------------ CATALOG VALID 184.108.40.206.0 CATPROC VALID 220.127.116.11.0 JAVAVM VALID 18.104.22.168.0 XML VALID 22.214.171.124.0 CATJAVA VALID 126.96.36.199.0 XDB VALID 188.8.131.52.0 OWM VALID 184.108.40.206.0 7 rows selected.
4. Run the post upgrade validation script:
SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql Oracle Database 12.2 Post-Upgrade Status Tool 02-22-2017 08:51:39 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server VALID 220.127.116.11.0 00:14:59 JServer JAVA Virtual Machine VALID 18.104.22.168.0 00:05:47 Oracle Workspace Manager VALID 22.214.171.124.0 00:00:59 Oracle XDK VALID 126.96.36.199.0 00:01:53 Oracle XML Database VALID 188.8.131.52.0 00:02:20 Oracle Database Java Packages VALID 184.108.40.206.0 00:00:19 Final Actions 00:04:59 Post Upgrade 00:02:19 Post Compile 00:02:25 Total Upgrade Time: 00:36:46 Database time zone version is 26. It meets current release needs. Summary Report File = /oracle/app/oracle/cfgtoollogs/dbua/upgrade2017-02-21_05-55-20-PM/TESTDB/upg_summary.log
5. Upgrade tables depends on oracle maintained Type.
COLUMN owner FORMAT A30 COLUMN table_name FORMAT A30 SELECT DISTINCT owner, table_name FROM dba_tab_cols WHERE data_upgraded = 'NO' ORDER BY 1,2; no rows selected
If above query returns any rows and run the below script.
SET SERVEROUTPUT ON @utluptabdata.sql
6. Disable flashback and drop the restore point;
If there is no plan to rollback the upgrade, then disable flashback and drop restore point:
alter database flashback off; select * from v$restore_point; drop restore point ;
Now database is ready for use.
NOTE: Currently full version of oracle 12.2. is available in oracle support.
ORACLE 12CR2 New features:
Conver non-partitioned table to partitioned table online using simple alter command:
Datapump new features in oracle 12cR2 release
12 thoughts on “Upgrade database from 220.127.116.11 to 18.104.22.168”
Thanks for sharing.
We are planning to pgrade the exadata databases to 12.2 next week.
Thanks Naresh. Please share your experience and issues you faced post upgrade.
Thank you for this post.
Will this upgrade take any downtime?
Yes, all upgrades need downtime. The database will be in MIGRATE mode during this activity.
Nice Explanation . Pls share manual upgrade steps also
DBUA upgrade has been very much simplied . So it is very much recommended to use DBUA for upgrade acitivity.
Maybe I am wrong but I think there is need to create a restore point after enabling the database on flashback mode
SQL> create restore point before_upgrade_12cR2 guarantee flashback database;
before running dbua
If this is not necessary can you clarify why?
Dear, Manually we don’t need to create any restore point. If you observe the DBUA (step 4 of 9), there we have selected the guarantee flashback option, which will create the restore point automatically.
So before starting DBUA, just enable flashback and select appropriate option on DBUA screens.
can you also share the manual upgrade steps? would be helpful.
I will try to do that on test machine and share the steps.
Please provide steps to upgrade grid infrastructure and RAC database.