Step by Step Database Upgrade from Oracle 12c to 19c

Environment Details:

Database: TEST

Database Role: Primary
DB Version: 12.1.0.2 EE 64 Bit

Operating System: Redhat Enterprise Linux 7.9 64 Bit

Version to be upgraded: 19.0.0.0 with latest RU/RUR patches

12c Home: /u01/app/oracle/product/12.1.0.2/dbhome_1

19c Home: /u01/app/oracle/product/19.3.0.0/dbhome_1

Upgrade Logs Location: /backup/upgrade_19c

Points to be checked before initiating upgrade activity:

Is application compatible with new DB version i.e. Oracle 19.0.0.0 ?
Is current Operating System certified with new DB version i.e. Oracle 19.0.0.0 ?
Am I downloading Oracle DB software of correct version i.e. Oracle 19c ?
Is my database certified on current Operating System ?
Is my Operating System 32-Bit or 64-Bit? Are Database and Operating System from same architecture ?
Is Operating System Kernel Version compatible with Oracle Database version to be installed ?

Step 1: Certification Matrix:

Ensure that current OS is certified with DB version to be upgraded and also, check 32-bit and 64 bit for both OS as well DB.

Upgrade roadmap matrix:

Source	Target
11.2.0.4	19c
12.1.0.2	19c
12.2.0.1	19c
18.1	19c

Intermediate upgrades need to be carried for following releases

Source Database	Intermediate Upgrade Path	Target Database
12.1.0.1	12.1.0.2/12.2.0.1	19c
11.2.0.1/11.2.0.2/11.2.0.3	11.2.0.4	19c
11.1.0.6/11.1.0.7	11.2.0.4	19c
10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5	11.2.0.4/12.1.0.2	19c
10.1.0.5	11.2.0.4/12.1.0.2	19c
9.2.0.8 or earlier	11.2.0.4	19c

Step 2: Data Capture

$su - oracle

$mkdir -p /backup/upgrade_19c

$chown -R oracle:oinstall /backup/upgrade_19c

$chmod -R 755 /backup/upgrade_19c

sqlplus / as sysdba

spool /backup/upgrade_19c/DB_data_capture_Pre.txt

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

select sysdate from dual;

Prompt ========================================================
Prompt Database Details
Prompt ========================================================
set lines 300 pages 3000
select name,db_unique_name,database_role,open_mode,log_mode,controlfile_type,flashback_on from v$database;
select banner from v$version;
Prompt ========================================================
Prompt D + R + C + T Names and Location
Prompt ========================================================
col name for a56
col member for a56
select file#,name from v$datafile;
select name from v$controlfile;
select name from v$tempfile;
select * from v$logfile;
select * from v$log;
Prompt ========================================================
Prompt Parameter checks
Prompt ========================================================
show parameter db_name
show parameter db_unique_name
show parameter compatible
show parameter cluster
show parameter sga
show parameter pga
show parameter memory
show parameter reco
show parameter parallel
show parameter job_queue_processes
show parameter case
show parameter broker
show parameter processes
show parameter db_file
show parameter transaction
show parameter session
Prompt ========================================================
Prompt Patches checks
Prompt ========================================================
set line 200 pages 2000
col ACTION for a30
col STATUS for a10
col ACTION_TIME for a30
col DESCRIPTION for a70
select PATCH_ID,ACTION,ACTION_TIME,DESCRIPTION,STATUS from dba_registry_sqlpatch;

Prompt ========================================================
Prompt NLS Parameter Checks
Prompt ========================================================
col property_name for a45
col property_value for a45
select property_name,property_value from database_properties order by 1;

Prompt ========================================================
Prompt Database Registry Component checks
Prompt ========================================================
col comp_name for a56
col comp_id for a20
col schema for a34
select COMP_ID,COMP_NAME,VERSION,STATUS,schema from dba_registry;

Prompt ========================================================
Prompt Object Validations/Invalidations
Prompt ========================================================
set lines 200 pages 2000
col owner for a25
col object_name for a60
col object_type for a25
select object_name,owner,object_type,status,created,last_ddl_time from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by 1;
select owner,count(*) from dba_objects where status='INVALID' group by owner order by 1;
select owner,object_name,object_type,status from dba_objects where status='INVALID' and owner in('SYS','SYSTEM');

Prompt ================================================================================================================
Prompt Timezone checks
Prompt Note: If your source version's time zone is already higher than the destination's home,
Prompt then apply the matching or any higher time zone patch to your destination home before upgrade.
Prompt   For latest DST patch refer to Note 412160.1
Prompt ================================================================================================================
SELECT version FROM v$timezone_file;

Prompt ========================================================
Prompt Oracle Label Security and Database Vault Checks
Prompt ========================================================
SELECT * FROM V$OPTION WHERE PARAMETER like 'Oracle%';
Prompt ========================================================
Prompt Data Encryption Checks
Prompt ========================================================
col wrl_type for a30
col WRL_PARAMETER for a60
select * from v$encryption_wallet;
select * from gv$encryption_wallet;
select distinct tablespace_name,ENCRYPTED from dba_tablespaces;
SELECT * FROM DBA_ENCRYPTED_COLUMNS;
Prompt ====================================================================================================================
Prompt Mview checks
Prompt Note: Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
Prompt Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Prompt Refer: Unable To Proceed With Upgrade Due To Materiealized View Refresh That Never Completes (Doc ID 1467392.1)
Prompt ====================================================================================================================
SELECT distinct o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) = 8;
col MVIEW_NAME for a45
select MVIEW_NAME,OWNER,STALENESS,STALE_SINCE,LAST_REFRESH_DATE,REFRESH_METHOD from dba_mviews order by 1;
select u.name owner,o.name mview_name, s.obj#,lastrefreshdate from obj$ o, sum$ s, user$ u where u.user# = o.owner# and o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
select decode(bitand(s.mflags,8), 8,'Refresh In Progress','Not being refreshed') from sys.obj$ o, sys.user$ u, sys.sum$ s where o.obj# = s.obj# and o.owner# = u.user# and o.type# = 42 and o.name='&mview';
select * from v$mvrefresh;
select * from dba_jobs_running;
Prompt ======================================================================================
Prompt Distributed Transaction Checks
PromptNote: There should not be any distributed transaction in pending state.
Prompt ======================================================================================
select * from dba_2pc_pending;

Prompt ======================================================================================
Prompt Check whether there are tables with SDO_GEOMETRY by executing below query.
Prompt If it returns a row(s), then apply Patch 25293022 on the 12.2 Oracle home, before starting the upgrade.
Prompt Check Oracle Spatial (SDO) is installed, if yes apply Patch 29715479 onto ORACLE_HOME before upgrade to avoid issue reported in Note 2547520.1
Prompt ======================================================================================
col owner format a15
col table_name format a30
col column_name format a30
SELECT owner,table_name,column_name FROM dba_tab_columns WHERE data_type = 'SDO_GEOMETRY' AND owner != 'MDSYS' ORDER BY 1,2,3;

Prompt ======================================================================================
Prompt To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment.
Prompt After the upgrade, grant specific required privileges. Access is based on the usage in the original database.
Prompt ======================================================================================
set lines 300 pages 3000
col REFERENCED_OWNER for a25
col OWNER for a30
col REFERENCED_NAME for a34
col REFERENCED_LINK_NAME for a34
SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
Prompt ======================================================================================
Prompt Capture DB Link Details and create script to re-create db link
Prompt During the upgrade to Oracle Database 12c any passwords in database links are encrypted.
Prompt To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade.
Prompt ======================================================================================
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC', 'public')||' DATABASE LINK
'||CHR(10)||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
|| 'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''||chr(10)||';' TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
Prompt =========================================================================================================
Prompt SYS.AUD$ table checks
Prompt Note: IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$
Prompt =========================================================================================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt =========================================================================================================
Prompt Couting and Querying duplicate objects
Prompt Note: Refer Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Prompt =========================================================================================================
SELECT count(1)
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
from dba_objects
where owner = 'SYS')
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ;
SELECT object_name, object_type, subobject_name, object_id
FROM dba_objects
WHERE object_name||object_type in
(SELECT object_name||object_type
FROM dba_objects
WHERE owner = 'SYS')
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ;

Prompt =========================================================================================================================
Prompt Disabling Concurrent Statistics Collection
Prompt Database Upgrade to 11.2 hangs, when upgrade process executes dbms_registry_sys.gather_stats(null) (Doc ID 1588552.1)
Prompt =========================================================================================================================
SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

Prompt =========================================================================================================================
Prompt Backing-up Scheduler Jobs
Prompt Disable any running scheduler jobs before upgrade. All scheduler jobs will be converted into dba_jobs from Oracle 19c onwards.
Prompt =========================================================================================================================
col owner for a30
col job_name for a50
col job_action for a70
set lines 300 pages 3000
col status for a13
col ACTUAL_START_DATE for a23
col additional_info for a60
select OWNER,JOB_NAME,JOB_ACTION,ENABLED from dba_scheduler_jobs where ENABLED='TRUE';
select JOB_NAME,OWNER,STATUS,additional_info,to_char(ACTUAL_START_DATE,'dd-mm-yyyy hh24:mi:ss') ACTUAL_START_DATE from dba_scheduler_job_run_details where job_name='&Job_Name' order by ACTUAL_START_DATE;
Prompt =========================================================================================================
Prompt Check whether database has any externally authenticated SSL users
Prompt =========================================================================================================
SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';

Prompt =========================================================================================================
Prompt Remove all hidden parameters prior to upgrading
Prompt =========================================================================================================
col NAME for a45
col VALUE for a45
SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Prompt =========================================================================================================
Prompt Network ACL Checks
Prompt If the query returns any row, then please follow Note 1958876.1 Upgrade fails with ORA-01830 date format picture ends before converting entire input string ORA-06512: at "SYS.XS_OBJECT_MIGRATION" to avoid failure in XDB's upgrade.
Prompt =========================================================================================================
select aclid, start_date, end_date from xds_ace where start_date is not null;

Prompt =========================================================================================================
Prompt Database and RDBMS Compatibility Checks and ensure all Diskgroups are mounted or connected.
Prompt =========================================================================================================
select name,state,total_mb,free_mb,COMPATIBILITY,DATABASE_COMPATIBILITY from gv$asm_diskgroup;

Prompt =========================================================================================================
Prompt Ensure error column returns "no rows". If you get any error message then resolve that first.
Prompt =========================================================================================================
set lines 300
set pages 3000
col DEST_NAME for a30
select inst_id,dest_id,DEST_NAME,STATUS,DB_UNIQUE_NAME,RECOVERY_MODE,DATABASE_MODE,error from gv$archive_dest_status;
SELECT resetlogs_id,dest_id,max(SEQUENCE#) FROM V$ARCHIVED_LOG where applied='YES' group by dest_id,resetlogs_id order by 1 desc,2;

Prompt =========================================================================================================
Prompt Existing Restore point and flashback details
Prompt =========================================================================================================
col name for a30
select flashback_on from v$database;
select scn, storage_size, time, name from v$restore_point;

Prompt =========================================================================================================
Prompt Ensure Database Vault owner has DV PATCH ADMIN role.
Prompt =========================================================================================================
col GRANTEE for a34
col GRANTED_ROLE for a34
select grantee,granted_role from dba_role_privs where grantee='SYS' and granted_role like '%DV%';

Prompt =========================================================================================================
Prompt Ensure qopatch command is executed successfully without any error.
Prompt =========================================================================================================
set serverout on
exec dbms_qopatch.get_sqlpatch_status;

Prompt =========================================================================================================
Prompt Ensure there should be any DB file in recovery mode, backup mode, and there should not be any block corruption in DB.
Prompt =========================================================================================================
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
select * from v$recover_file;
select * from v$database_block_corruption;

Prompt =========================================================================================================
Prompt Before the upgrade, if Oracle Multimedia and/or Oracle Spatial is installed, check the definition of the PUBLIC synonym AREA.
Prompt It should be defined to be a synonym for OGC_AREA, otherwise, it causes invalid DB components upon upgrading.
Prompt =========================================================================================================
col OWNER for a30
col SYNONYM_NAME for a34
col TABLE_OWNER for a30
select owner, synonym_name, table_owner, table_name from dba_synonyms where synonym_name = 'AREA';

Prompt =========================================================================================================
Prompt Check adequate free space is avaialable in temporarary tablespace
Prompt =========================================================================================================
col tablespace for a12
SELECT A.tablespace_name tablespace, D.gb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024 Gb_used,
D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.gb_total;

Prompt =========================================================================================================
Prompt Check adequate free space is avaialable in flash recovery area
Prompt =========================================================================================================
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS TOTAL_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS USED_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

Prompt =========================================================================================================
Prompt Check adequate free space is avaialable in default and system tablespaces
Prompt =========================================================================================================
set pages 200
set line 180
col file_name for a60
col TABLESPACE_NAME for a25
SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME having (100-(SUM(A.SUMB)*100/SUM(A.TOTS))) > 10
/
Prompt =========================================================================================================
Prompt Check whether Unified Auditing is enabled
Prompt =========================================================================================================
SELECT PARAMETER, VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
select count(*) from audsys.aud$unified;

col trigger_name for a30
col trigger_type for a23
col owner for a19
col status for a17
col TABLE_NAME for a36
col TABLE_OWNER for a23
col BASE_OBJECT_TYPE for a34
set lines 300 pages 3000
select trigger_name,trigger_type,owner,status,TABLE_NAME,TABLE_OWNER,BASE_OBJECT_TYPE from dba_triggers where status='ENABLED' order by 1;

spool off;

Now capture OS Data. Login as oracle user and execute below commands:

$cat /etc/redhat-release

$date

$id

$hostname

$uname -a

$env | grep ORA

$ps -ef |grep pmon

$ps -ef |grep tns

$ps -ef | grep agent

$lsnrctl status LISTENER_NAME

$which opatch

$opatch version

$opatch lspatches

$opatch lsinv

Step 3: Create new mount-point or add space in existing mount-point for new 19c binaries. Now 19c software is gold image copy setup. You have to directly copy setup file to ORACLE_HOME location. If you unzip the setup then it will unzip all binaries in that location.

Here, you have to install software with applying latest patches.

$cd /u01/app/oracle/product/19.3.0.0/dbhome_1

$unzip setup_file.zip =====> Refer actual setup file name.

$ls -ltr

$./runInstaller -applyRU PATCH_LOCATION

i.e.

$cd /u01/app/oracle/product/19.3.0.0/dbhome_1/

./runInstaller -applyRU /bkp/patch/33192793

Step 4: Copy below parameter files from OLD Home to NEW Home.

$cp -pr /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initORACLE_SID.ora /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/initORACLE_SID.ora

$cp -pr /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/spfileORACLE_SID.ora /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/spfileORACLE_SID.ora

$cp -pr /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwORACLE_SID /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/orapwORACLE_SID

$cp -pr /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/tnsnames.ora

$cp -pr /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora

$cp -pr /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/sqlnet.ora

$cp -pr crontab -l >> /backup/upgrade_19c/crontab_backup_oracle.txt =====>By oracle user

$cp -pr crontab -l >> /backup/upgrade_19c/crontab_backup_root.txt =====>By root user

Copy wallet_files from 12_HOME to 19c_HOME

Create below two pfiles with below names in 19c_HOME/dbs location:

pfile_12c.ora => This will be used for before upgrade.

Remove any hidden parameters

Remove any deprecated parameters

parallel_max_servers should not be more than 200

job_queue_processes should be 0 during upgrade

pfile_19c.ora => This will be used for after upgrade.

Step 5: Execute preupgrade.jar file

Source 12c environment variables and execute below command:

/u01/app/oracle/product/12.1.0.2/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.3.0.0/dbhome_1/rdbms/admin/preupgrade.jar

FILE TEXT DIR /backup/upgrade_19c/preupgrade

==================
PREUPGRADE SUMMARY
==================
  /backup/upgrade_19c/preupgrade/preupgrade.log
  /backup/upgrade_19c/preupgrade/preupgrade_fixups.sql
  /backup/upgrade_19c/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/backup/upgrade_19c/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/backup/upgrade_19c/preupgrade/postupgrade_fixups.sql

Step 6: Make below changes in database.

1) Move sys.aud$ in SYSTEM tablespace if not in SYSTEM tablespace.

SQL> select segment_name,segment_type,owner,tablespace_name from dba_segments where segment_name='AUD$' and owner='SYS';

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'SYSTEM');

END;

SQL> select segment_name,segment_type,owner,tablespace_name from dba_segments where segment_name='AUD$' and owner='SYS';

2) Add more space in SYSTEM,SYSAUX,TEMP,UNDO tablespaces at lest 10GB free.

set lines 300 pages 3000

col FILE_NAME for a50

select file_id,file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible from dba_data_files where tablespace_name='&TBS';

select file_id,file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible from dba_temp_files where tablespace_name='&TBS';

3) Disable application and goldengate trigger during upgrade time. Later you can enable it after upgrade activity.

set lines 300 pages 3000

select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where status='ENABLED' and owner ='&APP_OWNER' or trigger_name='GGS_DDL_TRIGGER_BEFORE' order by 1;

4) Defer standby destinations if configured.(Here, only dest_id2 is configured and hence we are disabling destination 2 only).

SQL> show parameter dest

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> alter system switch all logfile;
SQL> alter system switch all logfile;
SQL> alter system switch all logfile;
SQL> alter system checkpoint;

SQL> set lines 300 pages 3000

SQL> select name,open_mode,db_unique_name,database_role,log_mode,controlfile_type from v$database;

SQL> select thread#,max(sequence#) Standby from gv$archived_log group by thread# order by 1;

SQL> select thread#,max(sequence#) Standby from gv$archived_log where dest_id=2 and applied='YES' group by thread# order by 1;

SQL> alter system set log_archive_dest_state_2=defer;

SQL> show parameter log_archive_dest_state_2

5) Invalid Object and Registry Compo Check

SQL> set lines 200 pages 2000

SQL> col owner for a25

SQL> col object_name for a60

SQL> col object_type for a25

SQL> select object_name,owner,object_type,status,created,last_ddl_time from dba_objects where status='INVALID';

SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by 1;

SQL> select owner,count(*) from dba_objects where status='INVALID' group by owner order by 1;

SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID' and owner in('SYS','SYSTEM');

SQL> set line 200 pages 2000

SQL> col comp_name for a56

SQL> col comp_id for a20

SQL> col schema for a34

SQL> select COMP_ID,COMP_NAME,VERSION,STATUS,schema from dba_registry;

6) Execute preupgrade_fixup.sql and utlrp.sql scripts.

SQL> set time on;

SQL> set timing on;

SQL> @?/rdbms/admin/utlrp.sql

SQL>select owner,object_name,object_type,status from dba_objects where status='INVALID' and owner in('SYS','SYSTEM');

SQL>set line 200 pages 2000

SQL>col comp_name for a56

SQL>col comp_id for a20

SQL>col schema for a34

SQL>select COMP_ID,COMP_NAME,VERSION,STATUS,schema from dba_registry;

SQL> purge dba_recyclebin;

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

SQL>@/backup/upgrade_19c/preupgrade/preupgrade_fixups.sql

SQL>select owner,object_name,object_type,status from dba_objects where status='INVALID' and owner in('SYS','SYSTEM');

SQL> select COMP_ID,COMP_NAME,VERSION,STATUS,schema from dba_registry;

7) Enable flashback and create pre-upgrade guaranteed restore point to flashback DB incase upgarde is failed and we have to rollback the entire activity.

Put the DB in archivelog mode first if not enabled.

SQL> shut immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> select log_mode from v$database;

SQL> alter database open;

SQL> show parameter recovery

SQL> alter system set db_flashback_retention_target=1440;

SQL> select flashback_on from v$database;

SQL> alter database flashback on;

SQL> select flashback_on from v$database;

SQL> col name for a30SQL> select scn, storage_size, time, name from v$restore_point;

SQL> create restore point before_upgrade guarantee flashback database;

SQL> select scn, storage_size, time, name from v$restore_point;

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system checkpoint;

8) Disable Database Vault if enabled. You have to restart entire DB to reflect DV vault changes. You won't see dv vault status as disabled until you don't take DB restart.

SQl> set lines 300 pages 3000

SQL> select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';

SQL> select grantee,granted_role from dba_role_privs where granted_role='DV_PATCH_ADMIN';

SQL> conn DVOWNER/password

SQL> show user

SQL> EXEC DBMS_MACADM.DISABLE_DV;

SQL> conn / as sysdba
SQL> shut immediate
SQL> startup

SQL> select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';

Step 7: Start upgrade Activity

Source 12c environment and stop 12c D and associated listener services.

$export ORACLE_SID=TEST

$export ORACLE_BASE=/u01/app/oracle

$export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

$export PATH=$ORACLE_HOME/bin:$PATH

$export PATH=$ORACLE_HOME/OPatch:$PATH

SQL> shut immediate;

tail -400f alert_TEST.log

Source 19c environment and startup DB in upgrade mode by pfile_12c.ora.

$export ORACLE_SID=TEST

$export ORACLE_BASE=/u01/app/oracle

$export ORACLE_HOME=/u01/app/oracle/product/19.3.0.0/dbhome_1

$export PATH=$ORACLE_HOME/bin:$PATH

$export PATH=$ORACLE_HOME/OPatch:$PATH

$which sqlplus

$which opatch

$env | grep ORA

sqlplus / as sysdba

SQL> startup upgrade pfile='/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/pfile_12c.ora';

SQL> select name,open_mode from v$database;

SQL> select instance_name , status from gv$instance;

SQL> show parameter job_queue_processes

SQL> show parameter parallel_min_servers

SQL> show parameter parallel_max_servers

SQL> EXIT;

$cd /backup/upgrade_19c/ACTIVITY

nohup /u01/app/oracle/product/19.3.0.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0.0/dbhome_1/rdbms/admin/catctl.pl -l /backup/upgrade_19c/ACTIVITY -n 8 /u01/app/oracle/product/19.3.0.0/dbhome_1/rdbms/admin/catupgrd.sql &

$jobs

cd /backup/upgrade_19c/ACTIVITY location and tail nohup.out file.

Verify all catupgrd log files located in above directory and ensure no errors are observed.

cd /backup/upgrade_19c/ACTIVITY

grep -i "ORA-" *.log

Step 8: Start the database manually and perform post checks.

SQL> startup pfile='/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/pfile_12c.ora';

SQL>select name,open_mode from v$database;

SQL>select instance_name , status from gv$instance;

SQL>set time on;

SQL>set timing on;

SQL>@?/rdbms/admin/utlrp.sql

SQL> select count(*),status from dba_objects group by status;

SQL>set lines 200 pages 2000

SQL>col owner for a25

SQL>col object_name for a60

SQL>col object_type for a25

SQL>select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by 1;

SQL>select owner,count(*) from dba_objects where status='INVALID' group by owner order by 1;

SQL>select owner,object_name,object_type,status from dba_objects where status='INVALID' and owner in('SYS','SYSTEM');

SQL>set line 200 pages 2000

SQL>col comp_name for a56

SQL>col comp_id for a20

SQL>col schema for a34

SQL>select COMP_ID,COMP_NAME,VERSION,STATUS,schema from dba_registry;

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

SQL>@/backup/upgrade_19c/preupgrade/postupgrade_fixups.sql

Step 9: Timezone Upgrade

SQL>set line 200 pages 2000

SQL>SELECT version FROM v$timezone_file;

SQL>col PROPERTY_NAME for a30

SQL>col value for a50

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

SQL> @?/rdbms/admin/utltz_countstats.sql

SQL> @?/rdbms/admin/utltz_countstar.sql

SQL> @?/rdbms/admin/utltz_upg_check.sql

SQL> @?/rdbms/admin/utltz_upg_apply.sql

SQL>set line 200 pages 2000

SQL>SELECT version FROM v$timezone_file;

SQL>col PROPERTY_NAME for a30

SQL>col value for a50

SQL>SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

Step 10: Execute post upgrade scripts.

SQL> set time on;

SQL> set timing on;

SQL> @?/rdbms/admin/utlusts.sql TEXT

SQL> @?/rdbms/admin/catuppst.sql

Step 11: Enable Database Vault.

SQl> set lines 300 pages 3000

SQL> select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';

SQL> conn DVOWNER/password

SQL> EXEC DBMS_MACADM.ENABLE_DV;

SQL> conn / as sysdba

SQL> select grantee,granted_role from dba_role_privs where granted_role='DV_PATCH_ADMIN';

Restart the DB by old pfile (pfile_19c.ora).

cp -pr pfile_19c.ora initTEST.ora

SQL> shut immediate;

SQL> startup nomount pfile='/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/initTEST.ora';

SQL> create spfile from pfile='/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/initTEST.ora';

SQL> shut immediate;

SQL> startup;

SQl> set lines 300 pages 3000

SQL> select * From gv$option where parameter like '%Vault%' or parameter like '%Label%';

Step 12: Post upgrade verification.

Since we have already copied tnsnames.ora,listener.ora,sqlnet.ora,wallet and parameter files, we have to crosscheck the same once.

1) Wallet Checks

col wrl_type for a30
col WRL_PARAMETER for a60
select * from v$encryption_wallet;
select * from gv$encryption_wallet;

2) Network Files Checks

$cd $ORACLE_HOME/network/admin/

$vi listener.ora (Change ORACLE_12c_HOME to ORACLE_19c_HOME) and start the listener with new 19c Home.

$lsnrctl start LISTENER_NAME

3) Enable application and goldengate triggers one by one which we had disabled before upgrade activity.

e.g.

alter trigger owner.trigger_name enable;

4) Object, component, and timezone checks

SQL>select owner,object_name,object_type,status from dba_objects where status='INVALID' and owner in('SYS','SYSTEM');

SQL>set line 200 pages 2000

SQL>col comp_name for a56

SQL>col comp_id for a20

SQL>col schema for a34

SQL>select COMP_ID,COMP_NAME,VERSION,STATUS,schema from dba_registry;

SQL> SELECT version FROM v$timezone_file;

5) Data Verification checks

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

select * from v$recover_file;

select * from v$database_block_corruption;

tail alert log file and check any ORA- errors.

6) Parameter checks

SQL> show parameter compatible

SQL> show parameter parallel

SQL> show parameter job_queue_processes

7) Patching status check

set line 200 pages 2000
col ACTION for a30
col STATUS for a10
col ACTION_TIME for a30
col DESCRIPTION for a70

select PATCH_ID,ACTION,ACTION_TIME,DESCRIPTION,STATUS from dba_registry_sqlpatch;

Step 13: Handover the DB to an application team and ask to perform application sanity checks and let us know in case of any issue.

You have successfully upgraded Oracle DB from 12 to 19c.

For upgrading DR(Standby) database, click here:

Please comment if you like this post !

Step by Step Database Upgrade from Oracle 12c to 19c

Post a Comment

Oracle Database 23ai - Latest Long Term Release

Resolving ORA-01035: Restricted Session Privilege Error

Oracle DBA ++

Followers

Search This Blog

About Me

Contact form

Step by Step Database Upgrade from Oracle 12c to 19c

You may like these posts

Post a Comment

Contact form