1. check the database compatibility.
2. Take full backup of database using RMAN.
3. Run pre-upgrade script
Example:
$/u01/app/oracle/product/12.1.0.2/dbhome2/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome1/rdbms/admin/preupgrade.jar DIR /tmp/preupgrade
-------log-----------------
==================
PREUPGRADE SUMMARY
==================
/tmp/preupgrade/preupgrade.log
/tmp/preupgrade_fixups.sql
/tmp/postupgrade_fixups.sql
Execute fixup scripts across the entire CDB:
Before upgrade:
1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade/ -b preup_db01 /nfs/dpz/sb/preupgrade/preupgrade_fixups.sql
2. Review logs under /tmp/preupgrade/
After the upgrade:
1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade/ -b postup_db01 /tmp/preupgrade/postupgrade_fixups.sql
2. Review logs under /tmp/preupgrade/
-----------------------------------------------------------------
4. Fix issues reported in preupgrade log.
5. purge recyclebin.
6. compile invalid objects usign utlrp.sql
.$ORACLE_HOME/rdbms/admin/utlrp.sql( in container, all pdbs)
--check any invalis objects in sys,system after that. apex upgarde to 18c
7. If any issues reported about APEX version in pregupgardefixup log, upgrade APEX version in the database.
check current APEX version:
Select Comp_name, status, Version
From Dba_Registry
where comp_id='APEX';
Download recommended version from oracle site.
--upgrade process for apex--
cd /nfs/dpz/sb/-- downlaoded place
cd /nfs/dpz/sb/apex
sqlplus /as sysdba
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
After APEX version upgrade, check if APX version.
Example:
SQL> Select Comp_name, status, Version
From Dba_Registry
where comp_id='APEX'; 2 3
COMP_NAME
--------------------------------------------------------------------------------
STATUS
--------------------------------------------------------------------------------
VERSION
--------------------------------------------------------------------------------
Oracle Application Express
VALID
20.2.0.00.20
8. EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
9. TEMP tablespace should be minimum 150MB for container.increase to 1GB
10. .Remove parallel_adaptive_multi_user parameter-deprecated-- need to be false
11. stop LISTENER
lsnrctl stop LISTENER
12. Check if flashback is on in database , if not turn it on,
13. create guaranteed restore point in 12c database.
14. shutdown the database
15. Copy parameter files to 19c home.
Example:
[user@erver01 ~]$ cp /u01/app/oracle/product/12.1.0.2/dbhome2/dbs/orpw /u01/app/oracle/product/19.3.0/dbhome1/dbs/
[user@erver01 ~]$ cp $ORACLE_HOME/dbs/spfileoradev.ora /u01/app/oracle/product/19.3.0/dbhome1/dbs/
[user@erver01 ~]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome1/network/admin
16. start database in upgrade mode.
select name,open_mode,cdb,version,status from v$database,v$instance;
SQL> CONNECT / AS SYSDBA
Start the CDB in upgrade mode:
SQL> startup upgrade
Start the instance by issuing the following command in SQL*Plus:
SQL> alter pluggable database all open upgrade;
18.start the upgrade process
cd /u01/app/oracle/product/19.3.0/dbhome1/dbs/
nohup ./dbupgrade &
19. compile any invalid objects. execute utlrp.sql
cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin
SQL>utlrp.sql
check for any invalid objects after compilation
.select count(*) from dba_objects where status='INVALID';
20.Upgrade timezone if it is recommened in postfixup sccripts.
cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin
SQL>utltz_upg_check.sql
SQL>utltz_upg_apply.sql
SELECT version FROM v$timezone_file;
20.Execute utlusts.sql
cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin
SQL>utlusts.sql
21.Execute Catuppst.sql
cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin
SQL>Catuppst.sql
22.Check postfixup_upgrade.sql for any recommendations which is executed in step3.
23. DROP guaranteed restore point which is created before upgrade.
24. change database compatibility parameter (this needs db bounce)
ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
25.check if /etc/oratab file is updated with new version.
26. Take full backup of database.
