Single Instance DB Upgrade From 12c to 19c

Single Instance DB Upgrade From 12c to 19c

-Oracle 12c database should be up and running and it should be applied with latest patches
-Download latest 19c software's from oracle website.
-Also download latest Oracle Patches for 19c
-Start Installation of 19c software on separate mount point and apply latest patches which was downloaded earlier.

At this stages two separate database software will be ready one is 12cR2 and other will be 19c on two different mount points

High Level Steps:

The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
mkdir -p /u02/preupgrade
Run the pre-upgrade tool from 12c home referring file of new 19c home

/u01/app/oracle/product/12c/db_1/jdk/bin/java -jar /u02/app/oracle/product/19c/db_1/rdbms/admin/preupgrade.jar DIR /u02/preupgrade

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

purge dba_recyclebin;
purge recyclebin;

select name,open_mode,log_mode from v$database;
@/u02/preupgrade/preupgrade_fixups.sql

Performing the pre-upgrade actions

Run the preupgrade_fixups.sql

==> Need to resize datafile as per the recommendations

select file_name from dba_data_files;

alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_system_j87d12w1_.dbf' resize 1g; 

alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_sysaux_j87d2j6s_.dbf' resize 700m; alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_undotbs1_j87d3mbh_.dbf' resize 600m; 

select file_name from dba_temp_files;
alter database tempfile '/u01/app/oradata/PROD/datafile/o1_mf_temp_j87d525p_.tmp' resize 300m;

==>Create restore point before_upgrade_19c guarantee flashback database;

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from v$restore_point;

sqlplus / as sysdba
shutdown immediate;
exit

Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME

cp orapwtest spfiletest.ora /u02/app/oracle/product/19c/db_1/dbs
ls -lrt /u02/app/oracle/product/19c/db_1/dbs
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 7680 Sep 23 21:12 orapwtest
-rw-r-----. 1 oracle oinstall 3584 Sep 23 21:12 spfiletest.ora

Stop listener running on 12c home and start it from 19c home. Start the database from 19c ORACLE_HOME and start the upgrade.

sqlplus / as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;

Run the DB Upgrade utility

$ORACLE_HOME/bin/dbupgrade -n 8

-Check output and wait for 30 min.
-Check the upgrade summary log,
-Startup database manually and

startup;
SELECT name, open_mode, status, version from v$database, v$instance;
@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Recompile the INVALID Objects using utlrp.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql

Connect to sqlplus and run the postupgrade_fixups.sql script

sqlplus / as sysdba
@/u02/preupgrade/postupgrade_fixups.sql

Upgrade the timezone file version

sqlplus / as sysdba
SELECT version FROM v$timezone_file;

@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
@$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;

SELECT version FROM v$timezone_file;
Gather statistics on fixed objects


Connect to sqlplus as sys user and execute
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Now re-run the postupgrade_fixups.sql
select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;


Update the compatible parameter:
alter system set compatible='19.0.0' scope=spfile;

shutdown immediate;
startup;
show parameter compatible;
select name, open_mode, version from v$database, v$instance;
select name,open_mode,database_role from V$database;
show parameter cluster

set lines 200 pages 500
col owner for a15
col OBJECT_NAME for a35
select owner,object_name,object_type,status from dba_objects where status='INVALID';

col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

col ACTION_TIME for a30
col action for a30
col version for a10
col BUNDLE_SERIES for a30
col COMMENTS for a47
select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;