Purpose :
This document is created for use as a guideline and checklist for upgrading version about database from oracle 12c up to the release -- 19c
1 Guideline
1.1 oracle database version
source version：12.1.0.2
destination version：19.13
1.2 reference about official document
Primary Note for Database Proactive Patch Program (Doc ID 756671.1)
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Doc ID 556610.1)
2 Prerequisites
2.1 check implied parameters
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
2.2 Run dbupgdiag.sql
cd /u01/app/oracle
$ sqlplus / as sysdba
> alter session set nls_language='American';
> @dbupgdiag.sql
> exit
2.3 RMAN full backup
Make sure we obtain an available backup file for source database
nohup sh rman_L0. sh &
2.4 ban for customize trigger
ban for customize trigger as below，and restart them after completing upgrade
select 'ALTER TRIGGER '||DT.OWNER ||'.'|| DT.trigger_name||' DISABLE;' from dba_triggers dt where DT.owner IN ('TESTREPO','TESTUSER','TESTSAP') AND (dt.trigger_type like 'BEFORE%' OR DT.trigger_type LIKE 'AFTER%' )
2.5 run pre-upgrade tool
occording to official document，run scripts and command just like below：
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0
$ export PATH=/u01/app/oracle/product/12.1.0/bin:$PATH
$ export ORACLE_SID=TEXT
$ JAVA_BIN -jar /u01/app/oracle/product/19.0.0/db_home_1/rdbms/admin/preupgrade.jar FILE TEXT
2.6 Stop materialized view
select * from DBA_BASE_TABLE_MVIEWS;
2.7 Stop business cronJobs
select * from dba_jobs；
crontab –l
3 Prepare for destination oracle
3.1 Check OS options are compatibled with version 19C
3.2 configuration about environment parameters
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibumask=022
export PATH=$PATH:$ORACLE_HOME/OPatchSource
export PATH=$PATH:$ORACLE_HOME/OPatch
3.3 Install oracle 19c
download oracle software from official website and configuration oracle account&group in OS .
We use the desktop graphical wizard to install oracle by VNC
xhost +su - oracle
export DISPLAY=db01-t:1
cd $ORACLE_HOME
unzip /u01/soft/V982063-01.zip
then， shift to VNC desktop
./runInstaller
3.4 Patching oracle PSU
patching PSU depends on readme doc from official website.
Refer：Doc ID 756671.1
4 Oracle Former inspect
4.1 former inspect for params
handling issues without "autofix" keywords base on Step2.5.
this step is very tough and triky，the result occasionally like that：
4.2 handle the issues with manual fixup
4.2.1 parameter_obsolete & hidden_params
optimizer_adaptive_features=FALSE
code comments params ：
_max_outstanding_log_writes
_use_adaptive_log_file_sync
_optimizer_ads_use_result_cache
_optimizer_dsdir_usage_control
restart oracle service and mount new pfile ，as above changes
Startup mount pfile =initTEST.ora ;
create spfile from pfile;
4.2.2 Upgrade Oracle Application Express (APEX)
refer：Primary Note for Oracle Application Express (APEX) Upgrades (Doc ID 1088970.1)
4.2.3 make sure the result as below
4.3 former inspect for materialized view
expected： no results
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
4.4 check for TIMESTAMP WITH TIMEZONE
select version from v$timezone_file;
4.5 check for datafile
Make sure there is no datafile need to media recovery
SQL> SELECT * FROM v$recover_file;
4.6 Purge recyclebin
PURGE DBA_RECYCLEBIN;
CONGRATUATIONS！ LET'S GO TO UPGRADE VERSION!
5 Upgrade main steps
5.1 12c stop listner
$ lsnrctl stop
5.2 12c /etc/oratab
Ensure the record has been exists：
TEST :/u01/app/oracle/product/12.1.0:Y
5.3 restart 12c oracle & open flashback
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter system set db_recovery_file_dest_size = 100G;
SQL> alter database open;
5.4 12c create restore point
SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
5.5 12c shutdown
SQL> shutdown immediate;
5.6 19c shift to upgrade mode
operating in 19c environment
SQL> startup **_upgrade_** pfile='/home/oracle/pfile.ora';
5.7 19c running catupgrd.sql
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
5.8 19c startup oracle
startup oracle and make confirmation about version info
Select comp_id,Comp_name, status, Version,status,schema From Dba_Registry Order by Comp_name;
that‘s all
BTW. we can make oracle environment much more stable with choose R2 patching from official website.
the oracle official support ：support.oracle.com/portal/
