Oracle 19c upgrade step by step

Here in this post, we will be looking at how to upgrade an existing 12c database to 19c Database. Here is the high-level summary of the steps

• Installation of Oracle 19c Oracle Home
• Preupgrade Checks
• 19c Upgrade
• PostUpgrade Checks

Installation of Oracle 19c Oracle Home

You can take a look at the below article for detailed step-by-step instructions to install Oracle 19c Oracle Home.

Step by Step Oracle 19c Database Installation on Linux

Once the installation is done, we can move forward with the next steps. i.e PreUpgrade checks

Preupgrade Checks

(1) Ensure that all database components/objects provided by Oracle are VALID in the source database prior to starting the upgrade.

Here is the script to check for components status

set pagesize 500
set linesize 300
col COMP_NAME format a40;
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

Here is the script to check for invalid object status before the upgrade

col OBJECT_NAME format a30;
col OWNER format a20
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type
from dba_objects where status='INVALID' order by owner,object_type;

(2) Ensure that you do not have duplicate objects in the SYS and SYSTEM schema

cd <location of the script>
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit

(3) Timezone should be less than or equal to the target database timezone version.

The time zone files that are supplied with Oracle Database 19c are version 32.
We can check the pre-upgraded database timezone as

SQL> select version from v$timezone_file;

if < 32, then this database upgrade will upgrade the database to Timezone version 32
if > 32, then BEFORE the upgrade you MUST patch the target 19c $ORACLE_HOME with a timezone data file of the SAME version as the one used in the source release database.

(4) Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

(5) Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

Execute below query to check for the status of the backup:
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
Ensure that no files require media recovery:
SQL> SELECT * FROM v$recover_file;

(6) Gathering Optimizer Statistics to Decrease Oracle Database Downtime

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

(7) Purging Recycle Bin before the upgrade

SQL> PURGE DBA_RECYCLEBIN;

(8) Check the accounts use Case-Insensitive password version

col USERNAME format a40;
SELECT USERNAME,PASSWORD_VERSIONS,account_status FROM DBA_USERS;

If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after the upgrade is completed.

(9) About Password Case Sensitivity

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.

(10) Run Pre-Upgrade information tool script

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then the default is FILE.
TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

$ORACLE_HOME/jdk/bin/java -jar /uf00/oracle/product/19.0.0.0/rdbms/admin/preupgrade.jar FILE TEXT

We can look at the file generated

==================
PREUPGRADE SUMMARY
/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/preupgrade.log
/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/preupgrade_fixups.sql
/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/postupgrade_fixups.sql
Preupgrade complete:

We need to examine the content of preupgrade.log and take action according to it. Some of the action can be manual like increasing memory, processes, etc and other incorporated into the “preupgrade_fixups.sql”.

We can run the pre-upgrade fixup script as

sqlplus / as sysdba
@/u01/app/oracle/cfgtoollogs/db11g/preupgrade/preupgrade_fixups.sql
exit;

(11) Recovery options.

We need to decide about the recovery options in case of failure. We should enable the Archive log and then either take full RMAN backup or if possible create the guaranteed restore point by enabling Flashback

How to enable archive log mode in Oracle

SQL> Shutdown immediate
SQL>Startup mount
SQL>Alter database archivelog;
SQL> Alter database Open;
Verify Using
SQL> ARCHIVE LOG LIST

How to enable flashback and create a restore point

SQL> Shutdown immediate
SQL>Startup mount
SQL>Alter database flashback on;
SQL> Alter database Open;
Verify Using
SQL> select flashback_on from v$database;

SQL>create restore point BEF_UPG guarantee flashback database;

Once all these pre-upgrade are completed. we can move the 19c upgrade.

19c Database Upgrade

Source the new oracle home

cd $19c_Oracle_HOME/bin
./dbua

Check the database to be upgraded, put the sys username and password, and click Next

Address the pre-upgrade warning if have and then click next

Choose the option of Enable parallel upgrade, compile invalid and upgrade timezone data and then click next

We enable the flashback before starting the upgrade and created the Guaranteed restore point BEF_UPG, So we choose just that in Recovery options and click next

We can choose to upgrade the listener and create a new listener. We are just ignoring here

Ignoring these and click next

Check the detail and click on Upgrade

The upgrade will be finished

PostUpgrade Checks

(1) Compile the Invalid’s

SQL> @?/rdbms/admin/utlrp.sql

(2) Gather system and dictionary statistics

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

(3) Run the post-upgrade fixup scripts

sqlplus / as sysdba
@/uf01/app/oracle/cfgtoollogs/TEST/preupgrade/postupgrade_fixups.sql

(4) Execute dbupgdiag.sql script to verify the status of objects and components.

cd <location of the script>
$ sqlplus / as sysdba
sql> alter session set nls_language='American';
sql> @dbupgdiag.sql
sql> exit

(5) Ensure that all database components/objects are upgraded and valid

Here is the script to check for components status

set pagesize 500
set linesize 300
col COMP_NAME format a40;
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

Here is the script to check for invalid object status before the upgrade

col OBJECT_NAME format a30;
col OWNER format a20
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type
from dba_objects where status='INVALID' order by owner,object_type;

(6) Check the timezone data

SQL> select version from v$timezone_file;

This finished the upgrade process. I hope you like this content