We will be looking at the most common and easily used methods of upgrading the Oracle database from 12c to 19c using DBUA.
**Note :** You can perform a direct upgrade to 19c from 11.2.0.4, 12.1.0.2, 12.2.0.1 and 18c.
Pre-Upgrade Tasks
1. Make sure you backup your database before performing the upgrade.
2. Check application compatibility with the new version of the Oracle database.
3. Run the 19c pre-install package on Linux to complete all OS-level pre-requisites.
>yum install -y oracle-database-preinstall-19c
>yum update -y
**Install 19cR3 Software on Linux host**
>unzip LINUX.X64_193000_db_home.zip
>./runinstaller
Complete with installation , and set all envirnment veriables.
Check for Invalid objects in 12C database.
>select count(*) from dba_objects where status=’INVALID’;
Run pre-upgrade script
>/u01/app/oracle/product/12.2.0.1/db_home/jdk/bin/java -jar /u 01/app/oracle/product/19.3.0/db_home/rdbms/admin/preupgrade.jar
FILE DIR /home/oracle/preupgrade
View Pre upgrade log
>cat /home/oracle/preupgrade/preupgrade.log
Run Oracle Generated FIXUP SCRIPT
>@/home/oracle/preupgrade/preupgrade_fixups.sql
All looks good in our case, lets move to verify tablespace sizes for upgrade
>set line 1000
set pages 5000
col tablespace_name for a30
col file_name for a80
col free_space for 9999999
compute sum of total_space on report
compute sum of free_space on report
compute sum of MAX_SPACE on report
break on tablespace_name on report nodup
>select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) “Free%”,a.max_space from (select file_id,file_name,sum(bytes)/1024/1024 total_space,sum(MAXBYTES)/1024/1024/1024 max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,(select file_id,nvl(sum(bytes)/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_files) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name;
Gather DICTIONARY STATS
>SET ECHO ON;
SET SERVEROUTPUT ON;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Purge Recycle bin
>PURGE DBA_RECYCLEBIN;
Refresh MVs
>declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,’C’,’’, TRUE, FALSE);
end;
/
Verify archive log dest size and Create Flashback Guaranteed Restore Point
>archive log list
alter system set db_recovery_file_dest_size=10G;
>select flashback_on from v$database;
select name,open_mode,log_mode from v$database;
show parameter compatible
show parameter recovery
select * from V$restore_point;
>create restore point pre_upgrade guarantee flashback database;
>col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
Run DBUA
>cd /u01/app/oracle/product/19.3.0/db_home/bin/
./dbua
The database upgrade has been completed successfully, and the database is ready for use!
**Post Upgrade Tasks**
Check /etc/oratab and check if ORACLE_HOME location has changed to 19c home
>cat /etc/oratab | grep -i prod
Check Timezone version
>SELECT version FROM v$timezone_file;
Check INVALID objects
>select count(1) from dba_objects where status=’INVALID’;
Check DBA_REGISTRY
>select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
Run postupgrade_fixups.sql
>Sql @/home/oracle/preupgrade/postupgrade_fixups.sql
Update parameter
>ALTER SYSTEM SET COMPATIBLE = ‘19.0.0’ SCOPE=SPFILE;
>startup force
>select name,open_mode,version from v$database,v$instance;