Database upgrade can be performed either using manual or DBUA. Below are the steps for upgrading oracle database from oracle 12c to 19c version using DBUA.
Important points:
- Direct upgrade to 19 can be performed from 11.2.0.4,12.1.0.2,12.2.0.1 & 18c .
- Compatible parameter should be at minimum 11.2.0
- Post upgrade , oracle default accounts ( whose password has not been reset before upgrade), will be locked and set to NO AUTHENICATE MODE.
- Post upgrade, you may not be able to login to the existing users with the password, because of new authentication method. To fix this, sqlnet.ora file need to be update(details explained at the end of this article).
Current environment details:
DATABASE TYPE - STAND ALONE
DATABASE NAME - TESTDB
DATABASE VESION - 12.1.0.2
CURRENT ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
NEW ORACLE_HOME = /oracle/app/oracle/product/19.0.0.0/dbhome_1
Install oracle 19c ORACLE_HOME:
unzip the binary and run runInstaller.sh
mkdir -p /oracle/app/oracle/product/19.0.0.0/dbhome_1
./runInstaller.sh
PRE-UPGRADE CHECK :
Run preupgrade tool scriptpreupgrade.jar tool file is available with the oracle database binary. Run this to do the precheck
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
$ORACLE_HOME/jdk/bin/java -jar /oracle/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/preupgrade.jar
==================
PREUPGRADE SUMMARY
==================
/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade.log
/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2019-08-26T13:09:51
Run the pre-upgrade fixup script:
SQL> @/oracle/app/oracle/product/12.1.0.2/dbhome_1/cfgtoollogs/TESTDB/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2019-08-26 13:09:37
For Source Database: TESTDB
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. invalid_objects_exist NO Manual fixup recommended.
2. exclusive_mode_auth NO Manual fixup recommended.
3. case_insensitive_auth NO Manual fixup recommended.
4. underscore_events NO Informational only.
Further action is optional.
5. dictionary_stats YES None.
6. parameter_deprecated NO Informational only.
Further action is optional.
7. min_archive_dest_size NO Informational only.
Further action is optional.
8. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
2.Run utlrp.sql:( to compile invalid objects)
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
3.Check database component status:
set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME
----------------------------------------------------------------------------------------------------
STATUS VERSION
----------- ----------------------------------------
JServer JAVA Virtual Machine
VALID 12.1.0.2.0
Oracle Database Catalog Views
VALID 12.1.0.2.0
Oracle Database Java Packages
VALID 12.1.0.2.0
Oracle Database Packages and Types
VALID 12.1.0.2.0
Oracle Multimedia
VALID 12.1.0.2.0
Oracle Text
VALID 12.1.0.2.0
Oracle Workspace Manager
VALID 12.1.0.2.0
Oracle XDK
VALID 12.1.0.2.0
Oracle XML Database
VALID 12.1.0.2.0
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
4.Check timezone version:
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 18 0
5.Check files in backup mode:(should return zero rows)
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
SQL> SELECT * FROM v$recover_file;
no rows selected
6.Purge recyclebin:
SQL> purge dba_recyclebin;
As pre-check is successful . Now we will proceed with the upgrade
UPGRADE DATABASE:
Enable the flashback on the database.
To enable restore , in case of failure, enable flashback option.alter system set db_recovery_file_dest_size=20G scope=both;
alter system set db_recovery_file_dest='/dumparea/FRA/' scope=both;
alter database flashback on;
2.Start DBUA
export ORACLE_HOME=/oracle/app/oracle/product/19.0.0.0/dbhome_1/
cd $ORACLE_HOME/bin
./dbua