Step by Step Oracle Database Upgrade from 11g (11.2.0.4) to 12c (12.2.0.1)

March 7, 2021

Step by Step Oracle Database Upgrade from 11g (11.2.0.4) to 12c (12.2.0.1) using DBUA

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.

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_1
allocated channel: CH1
channel CH1: SID=43 device type=DISK

allocated channel: CH2
channel CH2: SID=40 device type=DISK

allocated channel: CH3
channel CH3: SID=63 device type=DISK


Starting backup at 07-JAN-21
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=96 RECID=39 STAMP=1002213405
input archived log thread=1 sequence=97 RECID=40 STAMP=1007893959
input archived log thread=1 sequence=98 RECID=41 STAMP=1008011196
channel CH1: starting piece 1 at 07-JAN-21
channel CH2: starting compressed archived log backup set
channel CH2: specifying archived log(s) in backup set
input archived log thread=1 sequence=105 RECID=48 STAMP=1055163808
input archived log thread=1 sequence=106 RECID=49 STAMP=1057163130
input archived log thread=1 sequence=107 RECID=50 STAMP=1057792561
input archived log thread=1 sequence=108 RECID=51 STAMP=1058877648
input archived log thread=1 sequence=109 RECID=52 STAMP=1061201550
channel CH2: starting piece 1 at 07-JAN-21
channel CH3: starting compressed archived log backup set
channel CH3: specifying archived log(s) in backup set
input archived log thread=1 sequence=99 RECID=42 STAMP=1008093341
input archived log thread=1 sequence=100 RECID=43 STAMP=1051561860
input archived log thread=1 sequence=101 RECID=44 STAMP=1051645000
input archived log thread=1 sequence=102 RECID=45 STAMP=1051645235
input archived log thread=1 sequence=103 RECID=46 STAMP=1051645435
input archived log thread=1 sequence=104 RECID=47 STAMP=1051701269
channel CH3: starting piece 1 at 07-JAN-21
channel CH2: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_1vvk1dkf_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:35
channel CH2: starting compressed archived log backup set
channel CH2: specifying archived log(s) in backup set
input archived log thread=1 sequence=110 RECID=53 STAMP=1061205483
input archived log thread=1 sequence=111 RECID=54 STAMP=1061205581
input archived log thread=1 sequence=112 RECID=55 STAMP=1061205646
channel CH2: starting piece 1 at 07-JAN-21
channel CH3: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_20vk1dkf_1_1 tag=UPGRADE_DB comment=NONE
channel CH3: backup set complete, elapsed time: 00:00:36
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_1uvk1dkf_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:37
channel CH2: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_21vk1dlj_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JAN-21

Starting backup at 07-JAN-21
channel CH1: starting compressed full datafile backup set
channel CH1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/labdb01/example01.dbf
channel CH1: starting piece 1 at 07-JAN-21
channel CH2: starting compressed full datafile backup set
channel CH2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/labdb01/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/labdb01/undotbs01.dbf
channel CH2: starting piece 1 at 07-JAN-21
channel CH3: starting compressed full datafile backup set
channel CH3: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/labdb01/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/labdb01/users01.dbf
channel CH3: starting piece 1 at 07-JAN-21
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_22vk1dll_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:01:05
channel CH1: starting compressed full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
channel CH1: starting piece 1 at 07-JAN-21
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_25vk1dnn_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
channel CH1: starting compressed full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel CH1: starting piece 1 at 07-JAN-21
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_26vk1dnp_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
channel CH3: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_24vk1dll_1_1 tag=UPGRADE_DB comment=NONE
channel CH3: backup set complete, elapsed time: 00:03:23
channel CH2: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_23vk1dll_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:04:03
Finished backup at 07-JAN-21

Starting backup at 07-JAN-21
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=113 RECID=56 STAMP=1061205928
channel CH1: starting piece 1 at 07-JAN-21
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguru_27vk1dta_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JAN-21

Starting backup at 07-JAN-21
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
channel CH1: starting piece 1 at 07-JAN-21
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/dbsguructl_28vk1dtc tag=UPGRADE_CTL comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JAN-21

Starting backup at 07-JAN-21
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel CH1: starting piece 1 at 07-JAN-21
channel CH1: finished piece 1 at 07-JAN-21
piece handle=/u01/backup/DBSGURUSPF_29vk1dte_1_1 tag=UPGRADE_SPFILE comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished 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.

[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.

[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.sql
drwxr-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:-

SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
/

4. Manually gather statistics:-
Execute the below commands to gather statistics.

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.

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.

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.

SQL> SELECT * FROM dba_2pc_pending;

In case the above command returns any selected rows with data, then run the below-following commands:

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.

SQL> set lines 333 pages 111
SQL> col COMP_NAME form a55
SQL> select comp_name, version, status from dba_registry;

9. Invalid Objects compile:-
Execute the below command to validate invalid objects and Recompile them.

SQL> select owner, count(*) from dba_objects where status <> 'VALID' group by owner;
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

10. Create pfile:-

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:-

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.

SQL> shutdown immediate
SQL> startup mount
SQL> 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.

SQL> show parameter sec_case_sensitive_logon
SQL> 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.

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:

[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:-

[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.

[oracle@DBsGuruN1 ~]$ cat /etc/oratab | grep -i testdb

2. Invalid Objects:-
Validate invalid objects in the database, it should not be any invalid objects.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> 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.

SQL> set lines 333 pages 111
SQL> col COMP_NAME form a51
SQL> 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.

[oracle@DBsGuruN1 ~]$ ps -eaf | grep -i tns
[oracle@DBsGuruN1 ~]$ lsncrctl status LISTENER
[oracle@labdb01 ~]$ sqlplus system@testdb
Enter Password:

5. Timezone Status:-
Validate timezone status, it should be 26 after upgrading to 12.2..0.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.

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.

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.

SQL> shutdown immediate
SQL> startup mount
SQL> 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.

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.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DATABASE, ORACLE, UPGRADE