Here we are going to demonstrate step-by-step Oracle database upgrades from 11g (11.2.0.4) to 12c (12.2.0.1), same steps can be followed to upgrade to 12.1.0.2 as well.
List of target upgrade versions from supported source version, data source support.oracle.com.
| Target DB Version | Source DB Version | Script Build/Date |
| 12.2.0.1 (12cR2 ) | 12.1.0.2 12.1.0.1 11.2.0.4 11.2.0.3 | Build 23 Oct 3rd 2019 |
| 12.1.0.2 (12cR1) | 12.1.0.1 11.2.0.4 11.2.0.3 11.2.0.2 11.1.0.7 10.2.0.5 | Build 18 March 2018 |
TABLE: Target & Source Version
Source Oracle Home: /u01/app/oracle/product/11204/db_1
Target Oracle Home: /u01/app/oracle/product/12201/db_1
Pre-Checks / Pre-Steps
1. Take database a full backup before upgrade.
| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160 | RMAN> run{ALLOCATE CHANNEL D1 DEVICE TYPE DISK FORMAT '/u01/bkp/dbsguru_%U';ALLOCATE CHANNEL D2 DEVICE TYPE DISK FORMAT '/u01/bkp/dbsguru_%U';ALLOCATE CHANNEL D3 DEVICE TYPE DISK FORMAT '/u01/bkp/dbsguru_%U';BACKUP tag 'UPGRADE_DB' FORCE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;BACKUP CURRENT CONTROLFILE TAG 'UPGRADE_CTL' FORMAT '/u01/bkp/dbsguructl_%u';BACKUP SPFILE TAG 'UPGRADE_SPFILE' FORMAT '/u01/backup/DBSGURUSPF_%U';RELEASE CHANNEL D1;RELEASE CHANNEL D2;RELEASE CHANNEL D3;} RMAN> run{ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '/u01/backup/dbsguru_%U';ALLOCATE CHANNEL CH2 DEVICE TYPE DISK FORMAT '/u01/backup/dbsguru_%U';ALLOCATE CHANNEL CH3 DEVICE TYPE DISK FORMAT '/u01/backup/dbsguru_%U';BACKUP tag 'UPGRADE_DB' FORCE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;BACKUP CURRENT CONTROLFILE TAG 'UPGRADE_CTL' FORMAT '/u01/backup/dbsguructl_%u';BACKUP SPFILE TAG 'UPGRADE_SPFILE' FORMAT '/u01/backup/DBSGURUSPF_%U';RELEASE CHANNEL CH1;RELEASE CHANNEL CH2;RELEASE CHANNEL CH3;}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> released channel: ORA_DISK_1allocated channel: CH1channel CH1: SID=43 device type=DISK allocated channel: CH2channel CH2: SID=40 device type=DISK allocated channel: CH3channel CH3: SID=63 device type=DISK Starting backup at 07-JAN-21current log archivedchannel CH1: starting compressed archived log backup setchannel CH1: specifying archived log(s) in backup setinput archived log thread=1 sequence=96 RECID=39 STAMP=1002213405input archived log thread=1 sequence=97 RECID=40 STAMP=1007893959input archived log thread=1 sequence=98 RECID=41 STAMP=1008011196channel CH1: starting piece 1 at 07-JAN-21channel CH2: starting compressed archived log backup setchannel CH2: specifying archived log(s) in backup setinput archived log thread=1 sequence=105 RECID=48 STAMP=1055163808input archived log thread=1 sequence=106 RECID=49 STAMP=1057163130input archived log thread=1 sequence=107 RECID=50 STAMP=1057792561input archived log thread=1 sequence=108 RECID=51 STAMP=1058877648input archived log thread=1 sequence=109 RECID=52 STAMP=1061201550channel CH2: starting piece 1 at 07-JAN-21channel CH3: starting compressed archived log backup setchannel CH3: specifying archived log(s) in backup setinput archived log thread=1 sequence=99 RECID=42 STAMP=1008093341input archived log thread=1 sequence=100 RECID=43 STAMP=1051561860input archived log thread=1 sequence=101 RECID=44 STAMP=1051645000input archived log thread=1 sequence=102 RECID=45 STAMP=1051645235input archived log thread=1 sequence=103 RECID=46 STAMP=1051645435input archived log thread=1 sequence=104 RECID=47 STAMP=1051701269channel CH3: starting piece 1 at 07-JAN-21channel CH2: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_1vvk1dkf_1_1 tag=UPGRADE_DB comment=NONEchannel CH2: backup set complete, elapsed time: 00:00:35channel CH2: starting compressed archived log backup setchannel CH2: specifying archived log(s) in backup setinput archived log thread=1 sequence=110 RECID=53 STAMP=1061205483input archived log thread=1 sequence=111 RECID=54 STAMP=1061205581input archived log thread=1 sequence=112 RECID=55 STAMP=1061205646channel CH2: starting piece 1 at 07-JAN-21channel CH3: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_20vk1dkf_1_1 tag=UPGRADE_DB comment=NONEchannel CH3: backup set complete, elapsed time: 00:00:36channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_1uvk1dkf_1_1 tag=UPGRADE_DB comment=NONEchannel CH1: backup set complete, elapsed time: 00:00:37channel CH2: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_21vk1dlj_1_1 tag=UPGRADE_DB comment=NONEchannel CH2: backup set complete, elapsed time: 00:00:01Finished backup at 07-JAN-21 Starting backup at 07-JAN-21channel CH1: starting compressed full datafile backup setchannel CH1: specifying datafile(s) in backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/labdb01/example01.dbfchannel CH1: starting piece 1 at 07-JAN-21channel CH2: starting compressed full datafile backup setchannel CH2: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/labdb01/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/labdb01/undotbs01.dbfchannel CH2: starting piece 1 at 07-JAN-21channel CH3: starting compressed full datafile backup setchannel CH3: specifying datafile(s) in backup setinput datafile file number=00003 name=/u01/app/oracle/oradata/labdb01/sysaux01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/labdb01/users01.dbfchannel CH3: starting piece 1 at 07-JAN-21channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_22vk1dll_1_1 tag=UPGRADE_DB comment=NONEchannel CH1: backup set complete, elapsed time: 00:01:05channel CH1: starting compressed full datafile backup setchannel CH1: specifying datafile(s) in backup setincluding current control file in backup setchannel CH1: starting piece 1 at 07-JAN-21channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_25vk1dnn_1_1 tag=UPGRADE_DB comment=NONEchannel CH1: backup set complete, elapsed time: 00:00:01channel CH1: starting compressed full datafile backup setchannel CH1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel CH1: starting piece 1 at 07-JAN-21channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_26vk1dnp_1_1 tag=UPGRADE_DB comment=NONEchannel CH1: backup set complete, elapsed time: 00:00:01channel CH3: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_24vk1dll_1_1 tag=UPGRADE_DB comment=NONEchannel CH3: backup set complete, elapsed time: 00:03:23channel CH2: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_23vk1dll_1_1 tag=UPGRADE_DB comment=NONEchannel CH2: backup set complete, elapsed time: 00:04:03Finished backup at 07-JAN-21 Starting backup at 07-JAN-21current log archivedchannel CH1: starting compressed archived log backup setchannel CH1: specifying archived log(s) in backup setinput archived log thread=1 sequence=113 RECID=56 STAMP=1061205928channel CH1: starting piece 1 at 07-JAN-21channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguru_27vk1dta_1_1 tag=UPGRADE_DB comment=NONEchannel CH1: backup set complete, elapsed time: 00:00:01Finished backup at 07-JAN-21 Starting backup at 07-JAN-21channel CH1: starting full datafile backup setchannel CH1: specifying datafile(s) in backup setincluding current control file in backup setchannel CH1: starting piece 1 at 07-JAN-21channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/dbsguructl_28vk1dtc tag=UPGRADE_CTL comment=NONEchannel CH1: backup set complete, elapsed time: 00:00:01Finished backup at 07-JAN-21 Starting backup at 07-JAN-21channel CH1: starting full datafile backup setchannel CH1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel CH1: starting piece 1 at 07-JAN-21channel CH1: finished piece 1 at 07-JAN-21piece handle=/u01/backup/DBSGURUSPF_29vk1dte_1_1 tag=UPGRADE_SPFILE comment=NONEchannel CH1: backup set complete, elapsed time: 00:00:01Finished backup at 07-JAN-21 released channel: CH1 released channel: CH2 released channel: CH3 RMAN> |
2. Execute the pre-upgrade command:-
Below command to identify any upgrade issues that may cause of failure.
| 12345678910 | [oracle@DBsGuruN1 ~]$ /u01/app/oracle/product/11204/db_1/jdk/bin/java -jar /u01/app/oracle/product/12201/db_1/rdbms/admin/preupgrade.jar TERMINAL TEXT OR [oracle@DBsGuruN1 ~]$ /u01/app/oracle/product/11204/db_1/jdk/bin/java -jar /u01/app/oracle/product/12201/db_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/ Preupgrade generated files: /home/oracle/preupgrade.log /home/oracle/preupgrade_fixups.sql /home/oracle/postupgrade_fixups.sql |
Switch to the directory to get generated files by preupgrade tool as mentioned in preupgrade.jar.
| 123456789101112131415 | [oracle@DBsGuruN1 ~]$ cd /home/oracle/[oracle@DBsGuruN1 ~]$ ls -lrt drwxr-xr-x 3 oracle dba 4096 Feb 2 03:50 oracle-rw-r--r-- 1 oracle dba 5373 Feb 2 03:50 dbms_registry_basic.sql-rw-r--r-- 1 oracle dba 12693 Feb 2 03:50 dbms_registry_extended.sql-rw-r--r-- 1 oracle dba 7027 Feb 2 03:50 preupgrade_driver.sql-rw-r--r-- 1 oracle dba 415655 Feb 2 03:50 preupgrade_package.sqldrwxr-xr-x 3 oracle dba 4096 Feb 2 03:50 upgrade-rw-r--r-- 1 oracle dba 63860 Feb 2 03:50 preupgrade_messages.properties-rw-r--r-- 1 oracle dba 11767 Feb 2 03:50 preupgrade_fixups.sql-rw-r--r-- 1 oracle dba 6889 Feb 2 03:50 postupgrade_fixups.sql-rw-r--r-- 1 oracle dba 11518 Feb 2 03:50 preupgrade.log SQL> @/home/oracle/preupgrade_fixups.sql |
NOTE: 1. Review log preupgrade.log and act if any action requires especially for tablespaces SYSTEM, SYAUX, UNDO, and TEMP.
2. preupgrade_fixups.sql command will be executed before the upgrade, here we will be doing also manual execution of various SQLs and other commands which are the surety of success along with the smooth upgrade.
3. Refresh materialized views if any:-
| 123456 | SQL> declarelist_failures integer(3) :=0;beginDBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);end;/ |
4. Manually gather statistics:-
Execute the below commands to gather statistics.
| 1234 | SQL> exec dbms_stats.gather_fixed_objects_stats;SQL> exec dbms_stats.gather_schema_stats ('SYSTEM');SQL> exec dbms_stats.gather_schema_stats ('SYS');SQL> exec dbms_stats.gather_dictionary_stats; |
Note: Gather statistics option is also available while database upgrades using DBUA. To reduce downtime of upgrade activities, here we performed manually.
5. Active Backup validation:-
Validate database for active backup or if any datafiles are in recovery mode.
| 12 | SQL> SELECT * FROM v$recover_file;SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; |
6. Default Tablespace for SYS & SYSTEM:-
Validate users SYS and SYSTEM have default tablespace as SYSTEM.
| 1 | SQL> SELECT * from dba_users where username in ('SYS','SYSTEM') and default_tablespace!='SYSTEM'; |
7. Pending 2phase pending transactions:-
Validate any Pending 2phase Transactions active.
| 1 | SQL> SELECT * FROM dba_2pc_pending; |
In case the above command returns any selected rows with data, then run the below-following commands:
| 123 | SQL> SELECT local_tran_id FROM dba_2pc_pending;SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');SQL> COMMIT; |
8. Componentโs version along with status:-
Validate existing component versions along with their status and should be valid.
| 123 | SQL> set lines 333 pages 111SQL> col COMP_NAME form a55SQL> select comp_name, version, status from dba_registry; |
9. Invalid Objects compile:-
Execute the below command to validate invalid objects and Recompile them.
| 12 | SQL> select owner, count(*) from dba_objects where status <> 'VALID' group by owner;SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql |
10. Create pfile:-
| 123 | SQL> create pfile = '/home/oracle/upgrade_pfiletestdb.ora' from spfile;[oracle@DBsGuruN1 ~]$ cp -p $ORACLE_HOME/dbs/spfiletestdb.ora /home/oracle/spfiletestdb.ora_before_upgrade.bkp[oracle@DBsGuruN1 ~]$ cp -p $ORACLE_HOME/dbs/inittestdb.ora /home/oracle/inittestdb.ora_before_upgrade.bkp |
11. Purge deleted objects from recyclebin:-
| 123 | SQL> select count(*) from DBA_RECYCLEBIN;SQL> purge DBA_RECYCLEBIN;SQL> select count(*) from DBA_RECYCLEBIN; |
12. Others:-
12.1 Disable cronjobs/scheduler jobs if any.
12.2 Blackout database in OEM.
12.3 Stop all dependent applications.
13. Put the database in noarchive mode.
Optionally you can put the database in noarchive log mode but make sure before that you took full DB backup along with archives.
| 12345 | SQL> shutdown immediateSQL> startup mountSQL> alter database noarchivelog;SQL> alter database open;SQL> archive log list |
14. Validate the value of parameter sec_case_sensitive_logon:-
If the value is FALSE then change it to TRUE.
| 123 | SQL> show parameter sec_case_sensitive_logonSQL> alter system set sec_case_sensitive_logon=TRUE scope=both;SQL> show parameter sec_case_sensitive_logon |
15. Create a guaranteed restore point:-
You can also create a guaranteed restore point, for this feature your DB should be in archive log mode along with flashback ON with ample free space in FRA.
| 12 | SQL> create restore point PRE_UPGRADEDB guarantee flashback database;SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; |
NOTE: Steps 12 & 14 are completely optional which depends on your requirements and approach to upgrade and opt for any one option.
Upgrade Database using DBUA
1. Set/Export environment:-
Export environment to new ORACLE_HOME & PATH, follow the below:
| 123 | [oracle@DBsGuruN1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12201/db_1[oracle@DBsGuruN1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH[oracle@DBsGuruN1 ~]$ which dbua |
2. Launch DBUA:-
| 1 | [oracle@DBsGuruN1 ~]$ dbua |
3. Select database name:-
On this screen, select the correct database name which needs to be upgraded and also put the SYS password then click Next.
4. Prerequisite checks:-
Prechecks are performed by dbua, once 100% looks good click on Finish.
5. Upgrade options:-
On this screen, select the below option and click Next:
A. Enable parallel options.
B. Recompile invalid objects while post-upgrade.
C. Upgrade Timezone.
NOTE: We have already gathered statistics manually in prechecks so skipping here.
6. Backup & recovery options:-
Select option I have my own backup and recovery strategy because we have our own backup along with guaranteed restored options.
7. Network configuration (Listener):-
On this screen, you can select the existing Listener name if itโs running from the same upgraded ORACLE HOME else create a new Listener in case upgrading the database the first time on the server for ORACLE HOME/version.
8. Configuration manangement:-
Safely skip this screen i.e not select any options and click Next.
9. Summary Page:-
On this screen, review the summary carefully which tells you about all those on the base of options opted from step 3 to step 8. Still, we have the option to edit, upon review/edit completion click on Finish.
10. Upgrade in progress:-
take a long breath and closely monitor the upgrade progress along with the database alert log and DBUA log. Here you also have the option to Pause your activities.
11. Upgrade Results:-
This is the last screen of the upgrade where you can review the result of the upgrade like source & database before and after upgrade versions, components status along with the time taken in the upgrade, Timezone version, etc.
Post Upgrade Tasks
1. New ORACLE HOME in oratab:-
Validate the new ORACLE_HOME in oratab, it should point to 12.2.0.1 HOME.
| 1 | [oracle@DBsGuruN1 ~]$ cat /etc/oratab | grep -i testdb |
2. Invalid Objects:-
Validate invalid objects in the database, it should not be any invalid objects.
| 12 | SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlSQL> select owner, count(*) from dba_objects where status <> 'VALID' group by owner; |
NOTE: Ignore invalid objects which are owned by non-default users subject to the status prior to the upgrade.
3. Components and Version Status:-
Validate the status of components along with the status and upgraded version.
| 1234 | SQL> set lines 333 pages 111SQL> col COMP_NAME form a51SQL> select comp_name, version, status from dba_registry;SQL> select * from v$version; |
4. Listener status & Remote Connections:-
Validate listener status on DB server along with one remote connection to upgraded database either from any remote servers or SQL DEVELOPER/TOAD.
| 1234 | [oracle@DBsGuruN1 ~]$ ps -eaf | grep -i tns[oracle@DBsGuruN1 ~]$ lsncrctl status LISTENER[oracle@labdb01 ~]$ sqlplus system@testdbEnter Password: |
5. Timezone Status:-
Validate timezone status, it should be 26 after upgrading to 12.2..0.1.
| 1 | SQL> select * from v$timezone_file; |
6. Execute utlu122s.sql:-
Optionally you can execute the post upgraded tool any time after the upgrade which will fetch data from DBA_REGISTRY.
| 1 | SQL> @$ORACLE_HOME/rdbms/admin/utlu122s.sql |
7. Drop guaranteed restore point:-
Drop restore point only after successful validation along with green signal by dependent applications.
| 123 | SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;SQL> drop restore point PRE_UPGRADEDB;SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; |
8. Put the database in archive mode:-
Change the database mode to archive log mode if changed before the upgrade.
| 12345 | SQL> shutdown immediateSQL> startup mountSQL> alter database archivelog;SQL> alter database open;SQL> archive log list |
9. Set the COMPATIBLE parameter to 12.2.0.1.0:-
Change the COMPATIBLE parameter value to 12.2.0.1.0 to enable to use of all features of the upgraded version. This step is a very crucial step in terms of downgrading the database to the previous version or restoring the guaranteed restore point. Make sure before the set a new value of the COMPATIBLE parameter no major impact on DB in terms of performance, if possible test the application for a few days with an existing value of this parameter especially for the lower environment (DEV/TEST/UAT) upgrades so you have enough confidence to change it when you perform an upgrade for PROD databases.
| 1234 | SQL> show parameter COMPATIBLE parameter;SQL> alter system set compatible='12.2.0.1.0' scope=spfile;SQL> show parameter COMPATIBLE parameter;SQL> crteat pfile from spfile; |
10. Others:-
10.1 Enable cronjobs/scheduler jobs if any.
10.2 Delete blackout database in OEM.
10.3 Start all dependent applications.
Click here for Step by Step Manual Upgrade Oracle Database from12c to 19c
This document is only for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.
credit: https://dbsguru.com/step-by-step-database-upgrade-from-11-2-0-4-to-12-2-0-1/