Oracle database version 12.2.0.1 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.
SEE ALSO:
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 12.1.0.2 version) to 12.2.0.1
INSTALLATION STEPS:
NEW ORACLE_HOME – /oracle/app/oracle/product/12.2.0.1/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/12.2.0.1/dbhome-1
PRE-UPGRADE STEPS:
1. Run the preupgrade tool
Preupgrade tool is available under new $ORACLE_HOME/rdbms/admin.
ls -ltr /oracle/app/oracle/product/12.2.0.1/dbhome-1/rdbms/admin/preupgrade.jar
SYNTAX
$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/12.2.0.1/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
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: TESTDB
Container Name: TESTDB
Container ID: 0
Version: 12.1.0.2.0
Compatible: 12.1.0.2.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 12.2.0.1.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 12.2.0.1.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
12.2.0.1.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: 12.2.0.1.0 Build: 1
Generated on: 2017-02-21 17:50:51
For Source Database: TESTDB
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.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)
export ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome-1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
6. Run DBUA
cd $ORACLE_HOME/bin
./dbua
This screen has been changed, compare to previous dbua in 12.1.0.2.
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 = 12.1.0.2.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: 12.2.0.1.0 Build: 1
Generated on: 2017-02-21 17:50:52
For Source Database: TESTDB
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.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 12.2.0.1.0 CATPROC VALID 12.2.0.1.0 JAVAVM VALID 12.2.0.1.0 XML VALID 12.2.0.1.0 CATJAVA VALID 12.2.0.1.0 XDB VALID 12.2.0.1.0 OWM VALID 12.2.0.1.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 12.2.0.1.0 00:14:59 JServer JAVA Virtual Machine VALID 12.2.0.1.0 00:05:47 Oracle Workspace Manager VALID 12.2.0.1.0 00:00:59 Oracle XDK VALID 12.2.0.1.0 00:01:53 Oracle XML Database VALID 12.2.0.1.0 00:02:20 Oracle Database Java Packages VALID 12.2.0.1.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
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.
DBACLASS Admin.
Thank you for this post.
Will this upgrade take any downtime?
Dear Indrani,
Yes, all upgrades need downtime. The database will be in MIGRATE mode during this activity.
Regards
Rajkishore
Nice Explanation . Pls share manual upgrade steps also
Dear ,
DBUA upgrade has been very much simplied . So it is very much recommended to use DBUA for upgrade acitivity.
Regards
Admin
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.
Regards
Admin
can you also share the manual upgrade steps? would be helpful.
Dear ,
I will try to do that on test machine and share the steps.
Dear Sir,
Please provide steps to upgrade grid infrastructure and RAC database.