We will see how to upgrade the database to 19C Manually.
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
- Make sure you backup your database before performing the upgrade and also check application compatibility with the new version of the Oracle database.
- Direct upgrade to 19c is possible for 11.2.0.4, 12.1.0.2, 12.2.0.1 and 18c versions. The upgrade steps are all same for the various supported versions.
- 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.
Check for Invalid objects
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;
Shutdown the database
shut immediate
Copy init and password files from 12c to 19c dbs home
cd $ORACLE_HOME/dbs ls -ltr cp orapwprod spfileprod.ora /u01/app/oracle/product/19.3.0/db_home/dbs ls -ltr /u01/app/oracle/product/19.3.0/db_home/dbs/
Set Oracle 19C environment.
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_home export ORACLE_SID=prod
Startup DB in Upgrade mode from 19c home
sqlplus / as sysdba startup upgrade;
Run DB upgrade
cd /u01/app/oracle/product/19.3.0/db_home/bin ls -ltr dbupgrade nohup ./dbupgrade & → Press enter 2 times jobs -l disown ps -ef | grep -i catctl.pl
Startup DB from 19c home
sqlplus / as sysdba startup;
select name,open_mode,cdb,version,status from v$database, v$instance; select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
Post Upgrade Steps
Checked invalid objects and compile using “utlrp.sql”
select count(*) from dba_objects where status=’INVALID’; @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utlrp.sql
Run postupgrade_fixups.sql
@/home/oracle/preupgrade/postupgrade_fixups.sql
Upgrade Timezone file check and upgrade
@/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_check.sql @/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utltz_upg_apply.sql
SELECT version FROM v$timezone_file;
Run utlusts.sql
@/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/utlusts.sql
Run catuppst.sql
@/u01/app/oracle/product/19.3.0/db_home/rdbms/admin/catuppst.sql
Re-Run postupgrade_fixups.sql
@/home/oracle/preupgrade/postupgrade_fixups.sql
Set COMPATIBLE parameter value to 19.0.0
show parameter COMPATIBLE ALTER SYSTEM SET COMPATIBLE = ‘19.0.0’ SCOPE=SPFILE; shut immediate; startup; show parameter COMPATIBLE
Checked database registry
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
Completed