Upgrade 12c to 19c: Traditional

Introduction

This page described how to upgrade single instance of FMS Database from version 11.2.0.4 to 19.3.0.0 in Windows Server 2012 R2 Standard

Source Server Preparation Upgrade Checklist

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

Introduction

This page described how to upgrade single instance of FMS Database from version 11.2.0.4 to 19.3.0.0 in Windows Server 2012 R2 Standard

Source Server Preparation Upgrade Checklist

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

Use this checklist to prepare your server with the earlier Oracle Database release before you start the upgrade.

TAKSDESCRIPTION
Clean up the databaseAs SYS User, Empty the recycle bin PURGE DBA_RECYCLEBIN; Check for INVALID objects in SYS and SYSTEM col owner format a15 col object_name format a45 col object_type format a15 select owner, object_name, object_type from dba_objects where owner in (‘SYS’,’SYSTEM’) and status=’INVALID’ order by owner,object_type; Check for duplicate objects in SYS and SYSTEM 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’) ;
  Check materialized viewsCheck the status of all materialized views (MV), and refresh any materialized views that are not fresh. SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 
Check Time Zone settingUse the following query to detect the timezone of your source database: select version from v$timezone_file; Default time zone version for Oracle Database 12c release 2 (12.2) is V26 If the source database Time Zone version is higher than the target database, then apply the Time Zone patch on the target database to match the source database version.
Time Zone defaults are in located in the path $ORACLE_HOME/oracore/zoneinfo.
Removing EM Repository (if exists)Execute emremove.sql script. The script will be located in new target  ORACLE_HOME/rdbms/admin SET ECHO ON
SET SERVEROUTPUT ON
@emremove.sql After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.
Check accounts using ONLY 10g password versionAs SYS, enter the following SQL query select USERNAME from DBA_USERS where ( PASSWORD_VERSIONS = ’10G ‘ or PASSWORD_VERSIONS = ’10G HTTP ‘) and USERNAME <> ‘ANONYMOUS’; If there is any account, then: 1- Configure the system so that it is not running in Exclusive Mode by editing the setting of the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a level appropriate for affected accounts. For example: SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 2- After you make this change, proceed with the upgrade 3- After the upgrade completes, use the following command syntax to expire the accounts you found in step 1, where username is the name of a user returned from the query in step 1:
ALTER USER username PASSWORD EXPIRE; 4- Ask the users for whom you have expired the passwords to log in. 5- When these users log in, they are prompted to reset their passwords. The system internally generates the missing 11G and 12C password versions for their account, in addition to the 10G password version. The 10G password version is still present, because the system is running in the permissive mode.
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSEAs SYS execute:  show parameter SEC_CASE_SENSITIVE_LOGON. If is TRUE or FALSE, remove it from the init.ora File and restart the DB
Gather dictionary statistics to decrease Oracle Database downtimeEXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Back up the database (recommended)Back up the database, create a guaranteed flashback restore point, or both. man target /  run {
allocate channel test_backup_disk1 type disk format ‘/u01/backup/%d_%U’;
backup incremental level 0 tag ‘ORCL_BEFORE_UPG’  database;
backup format  tag ‘ORCL_CONTROL_FILE’ current controlfile;
release channel test_backup_disk1;
}

Install 19c Binaries

Unzip and copy the file into:

C:\temp

Install 19c Software.  Run setup.exe

put c:\oracle as Oracle base!!

  • Click Install

2. Once completed installation, run this in powershell

C:\Oracle\product\12.1.0\dbhome_1\jdk\bin\java -jar C:\Oracle\product\19.0.0.0\dbhome_1\rdbms\admin\preupgrade.jar

– if encounter below issues :-

ERROR – Unable to run preupgrade due to:

ERROR:

ORA-01017: invalid username/password; logon denied

Solution

Go to C:\Oracle\Oracle\product\12.1.0\dbhome_1\network\admin\sqlnet.ora then set SQLNET.AUTHENTICATION_SERVICES= (NTS) instead of (NONE)

It will generate pre-upgrade script, which you need to run and check, in powershell login to db using

sqlplus / as sysdba

  1. If you have issue connecting to SQL or start listener, refer to http://www.dba-oracle.com/t_ora_12577.htm

Below command will list all the issues that you need to resolve before upgrade

@C:\Oracle\cfgtoollogs\fidelio\preupgrade\preupgrade_fixups.sql

Below command will list all the issues that you need to resolve before upgrade

  • To get rid of from the warnings. Steps are below
    • Dictionary_stats → 
      SQL> SET ECHO ON;
      SQL> SET SERVEROUTPUT ON;
      SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Then run below script for get rib the Invalid_objects_exist. 

sql> @C:\Oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN\utlrp.sql, and 

sql> select count(*) from dba_objects where status=’INVALID’;

→ make sure it return 0 error

If unable to return 0 means there have some compiling issue. run below script to see the object.

sql >SET SERVEROUTPUT ON;

sql >EXECUTE DBMS_PREUP.INVALID_OBJECTS;

To get rid of this, recompile objects. You have several options:

1. This script may take some time, depending on the number of objects.

@$ORACLE_HOME/rdbms/admin/utlrp.sql

2. Compile objects of a particular schema:

EXEC DBMS_UTILITY.compile_schema(schema => ‘APPS’);

Compiling individual objects:

— Compiling a package;

ALTER PACKAGE APPS.DAIL_REPORT COMPILE;

ALTER PACKAGE APPS.DAIL_REPORT COMPILE BODY;

— Compiling a procedure:

ALTER PROCEDURE APPS.REPORT_PROC COMPILE;

— Compiling a view:

ALTER VIEW APPS.USERSTATUS_VW COMPILE;

— Compiling a function:

ALTER FUNCTION APPS.SYNC_FUN COMPILE;

If still cannot, then sql > drop Object_type Object_Name;

sql> select count(*) from dba_objects where status=’INVALID’;

→ make sure it return 0 error

Perform Upgrade

Now you can invoke DBUA from Command Prompt.

cd C:\Oracle\product\19.0.0.0\dbhome_1\bin

dbua

  • Don’t need to key in SYSDBA Username or password, select your source database and click next>>

Make sure all the severity issue have been solve.

Tick Recompile invalid Objects during Post Upgrade

Info
Make sure your listener status is Up, and Migrate = Yes!!

Untick Configure Enterprise Manager

Check Everything is in order, then start upgrade

Complete Installation

  • Once completed

@C:\Oracle\cfgtoollogs\fidelio\preupgrade\postupgrade_fixups.sql

Maybe will encounter issue as below, but just ignore it.

Enter value for c_apex_version_4_dots:

Enter value for c_upgradable_versions:

Enter value for c_minimum_compatible:

Enter value for c_upgradable_versions:

Enter value for c_upgradable_versions:

Enter value for c_upgradable_versions:

Enter value for c_minimum_compatible:

Enter value for c_minimum_compatible:

Enter value for c_minimum_compatible:

Enter value for c_oracle_high_version_4_dots:

Enter value for c_ltz_content_ver:

Enter value for c_ltz_content_ver:

Enter value for c_ltz_content_ver:

Enter value for c_ltz_content_ver:

Enter value for c_oracle_high_version_0_dots:

Enter value for c_upgradable_versions:

Enter value for c_default_processes:

Enter value for c_oracle_high_version_4_dots:

Enter value for c_oracle_high_version_0_dots:

ERROR at line 1:

ORA-04063: package body “SYS.DBMS_PREUP” has errors

ORA-06512: at line 11

Target Server Post-Upgrade Checklist

Complete these checks on the upgraded Oracle Database environment.

Query for any INVALID ObjectsSELECT substr(owner,1,12) owner, substr(object_name,1,12) object, substr(object_type,1,30) object, status from dba_objects WHERE status <> ‘VALID’;
Verify DBA_REGISTRYcol COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Target Server Post-Upgrade Checklist

Complete these checks on the upgraded Oracle Database environment.

TASKDESCRIPTION
Query for any INVALID ObjectsSELECT substr(owner,1,12) owner, substr(object_name,1,12) object, substr(object_type,1,30) object, status from dba_objects WHERE status <> ‘VALID’;
Verify DBA_REGISTRYcol COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
TAKSDESCRIPTION
Clean up the databaseAs SYS User, Empty the recycle bin PURGE DBA_RECYCLEBIN; Check for INVALID objects in SYS and SYSTEM col owner format a15 col object_name format a45 col object_type format a15 select owner, object_name, object_type from dba_objects where owner in (‘SYS’,’SYSTEM’) and status=’INVALID’ order by owner,object_type; Check for duplicate objects in SYS and SYSTEM 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’) ;
  Check materialized viewsCheck the status of all materialized views (MV), and refresh any materialized views that are not fresh. SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; 
Check Time Zone settingUse the following query to detect the timezone of your source database: select version from v$timezone_file; Default time zone version for Oracle Database 12c release 2 (12.2) is V26 If the source database Time Zone version is higher than the target database, then apply the Time Zone patch on the target database to match the source database version.
Time Zone defaults are in located in the path $ORACLE_HOME/oracore/zoneinfo.
Removing EM Repository (if exists)Execute emremove.sql script. The script will be located in new target  ORACLE_HOME/rdbms/admin SET ECHO ON
SET SERVEROUTPUT ON
@emremove.sql After emremove.sql completes, you must manually remove ORACLE_HOME/HOSTNAME_SID and ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_HOSTNAME_SID directories from your file system.
Check accounts using ONLY 10g password versionAs SYS, enter the following SQL query select USERNAME from DBA_USERS where ( PASSWORD_VERSIONS = ’10G ‘ or PASSWORD_VERSIONS = ’10G HTTP ‘) and USERNAME <> ‘ANONYMOUS’; If there is any account, then: 1- Configure the system so that it is not running in Exclusive Mode by editing the setting of the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a level appropriate for affected accounts. For example: SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 2- After you make this change, proceed with the upgrade 3- After the upgrade completes, use the following command syntax to expire the accounts you found in step 1, where username is the name of a user returned from the query in step 1:
ALTER USER username PASSWORD EXPIRE; 4- Ask the users for whom you have expired the passwords to log in. 5- When these users log in, they are prompted to reset their passwords. The system internally generates the missing 11G and 12C password versions for their account, in addition to the 10G password version. The 10G password version is still present, because the system is running in the permissive mode.
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSEAs SYS execute:  show parameter SEC_CASE_SENSITIVE_LOGON. If is TRUE or FALSE, remove it from the init.ora File and restart the DB
Gather dictionary statistics to decrease Oracle Database downtimeEXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
Back up the database (recommended)Back up the database, create a guaranteed flashback restore point, or both. man target /  run {
allocate channel test_backup_disk1 type disk format ‘/u01/backup/%d_%U’;
backup incremental level 0 tag ‘ORCL_BEFORE_UPG’  database;
backup format  tag ‘ORCL_CONTROL_FILE’ current controlfile;
release channel test_backup_disk1;
}

Install 19c Binaries

Unzip and copy the file into:

C:\temp

Install 19c Software.  Run setup.exe

put c:\oracle as Oracle base!!

  • Click Install

2. Once completed installation, run this in powershell

C:\Oracle\product\12.1.0\dbhome_1\jdk\bin\java -jar C:\Oracle\product\19.0.0.0\dbhome_1\rdbms\admin\preupgrade.jar

– if encounter below issues :-

ERROR – Unable to run preupgrade due to:

ERROR:

ORA-01017: invalid username/password; logon denied

Solution

Go to C:\Oracle\Oracle\product\12.1.0\dbhome_1\network\admin\sqlnet.ora then set SQLNET.AUTHENTICATION_SERVICES= (NTS) instead of (NONE)

It will generate pre-upgrade script, which you need to run and check, in powershell login to db using

sqlplus / as sysdba

  1. If you have issue connecting to SQL or start listener, refer to http://www.dba-oracle.com/t_ora_12577.htm

Below command will list all the issues that you need to resolve before upgrade

@C:\Oracle\cfgtoollogs\fidelio\preupgrade\preupgrade_fixups.sql

Below command will list all the issues that you need to resolve before upgrade

Machine generated alternative text:
wfivmOÆ9P1 - Remote Desktop Connection 
SQL» ac: Xoraclexcfgtool Xups .sql 
Executing Oracle PRE-upgrade Fixup Scrlpt 
to-Generated by: 
nerated on: 
For Source Database: 
Source Database Version: 
For upgrade to version: 
reup 
Action 
2020-04-01 
OHCFM 
12.2.o.1.o 
19.0.o.o.o 
Further DBA Action 
None. 
Manual fixup recommended. 
Manual fixup recommended. 
Informational only. 
Further action h s optional. 
None . 
None . 
Informational only. 
Further action h s optional 
Informational only. 
Further action is optional . 
Informational only. 
Further action h s optional . 
Informational only. 
Further action is optional . 
Preupgrade 
Issue Is 
Remedied 
Preupgrade Check Name 
purge—recyclebin 
inval 
case_i ns ens ve_auth 
mv_refresh 
dictionary_stats 
t r gown e dmndbt r a 
mv_refresh 
parameter_deprecated 
1 on 
e fixup scripts have been run and resolved what they can. However, 
there are still issues originally identified by the preupgrade that 
have not been remedied and are still present in the database. 
Depending on the severity of the specific issue, and the nature of 
the issue itself, that could mean that your database is not ready 
for upgrade. TO resolve the Outstanding issues, Start by revi ewing 
the preup rade_fixups . sq I and searching it for the name Of 
the faile CHECK NAPE or Preupgrade Action Number isted above. 
There will find the original corresponding diagnostic message 
from the preupgrade Which explains in more detail What still needs 
be done. 
/SQL procedure successfully completed.
  • To get rid of from the warnings. Steps are below
    • Dictionary_stats → 
      SQL> SET ECHO ON;
      SQL> SET SERVEROUTPUT ON;
      SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Then run below script for get rib the Invalid_objects_exist. 

sql> @C:\Oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN\utlrp.sql, and 

sql> select count(*) from dba_objects where status=’INVALID’;

→ make sure it return 0 error

If unable to return 0 means there have some compiling issue. run below script to see the object.

sql >SET SERVEROUTPUT ON;

sql >EXECUTE DBMS_PREUP.INVALID_OBJECTS;

Machine generated alternative text:
V REVENUE GUEST otLY 
v SCD_DETAILS 
V SEC_RIGHTS TEST 
v SECURITf_RIGHTS 
V SHP CURR PREV NEXT CRU 
V TNA WORK TIPIE 
V USER CLASS_GROUP 
ACC

To get rid of this, recompile objects. You have several options:

1. This script may take some time, depending on the number of objects.

@$ORACLE_HOME/rdbms/admin/utlrp.sql

2. Compile objects of a particular schema:

EXEC DBMS_UTILITY.compile_schema(schema => ‘APPS’);

Compiling individual objects:

— Compiling a package;

ALTER PACKAGE APPS.DAIL_REPORT COMPILE;

ALTER PACKAGE APPS.DAIL_REPORT COMPILE BODY;

— Compiling a procedure:

ALTER PROCEDURE APPS.REPORT_PROC COMPILE;

— Compiling a view:

ALTER VIEW APPS.USERSTATUS_VW COMPILE;

— Compiling a function:

ALTER FUNCTION APPS.SYNC_FUN COMPILE;

If still cannot, then sql > drop Object_type Object_Name;

sql> select count(*) from dba_objects where status=’INVALID’;

→ make sure it return 0 error

Perform Upgrade

Now you can invoke DBUA from Command Prompt.

cd C:\Oracle\product\19.0.0.0\dbhome_1\bin

dbua

  • Don’t need to key in SYSDBA Username or password, select your source database and click next>>
Machine generated alternative text:
Select Database 
Seed 
so 
of the s 
19C 
ORACLE 
Database 
Nom. 
if OS 
12102.0 S' 
upgrade the 
STS O ga

Make sure all the severity issue have been solve.

Machine generated alternative text:
prerequisite Checks 
W. min "S 
Database 
and 
Ret 
Ore 
the OR' 
112, 
the default 
not

Tick Recompile invalid Objects during Post Upgrade

Machine generated alternative text:
Select Upgrade Options 
19C 
ORACLE 
Database 
Spew the SQL to

Info
Make sure your listener status is Up, and Migrate = Yes!!

Machine generated alternative text:
Configure Network 
owle 
Database 
new

Untick Configure Enterprise Manager

Machine generated alternative text:
Data rade AssiStM1t 
- Sto 6 Of 9 
(EMI 
ORACLE 
Configure Management 
19C

Check Everything is in order, then start upgrade

Machine generated alternative text:
Sum 
upgrade 
Hem: 
Target 
Checks 
be 
'LTESTOB 
NONE 
19C 
ORACLE 
O at abase 
disk 
the
Machine generated alternative text:
Pro ss 
19C 
ORACLE 
Data bagg 
pre 
steps 
co n fig 
in Home

Complete Installation

Machine generated alternative text:
Re suns 
Database 
19C 
ORACLE 
Database 
Upgrade Results 
upgrade has and the database is 
Upgrade Details
  • Once completed

@C:\Oracle\cfgtoollogs\fidelio\preupgrade\postupgrade_fixups.sql

Maybe will encounter issue as below, but just ignore it.

Enter value for c_apex_version_4_dots:

Enter value for c_upgradable_versions:

Enter value for c_minimum_compatible:

Enter value for c_upgradable_versions:

Enter value for c_upgradable_versions:

Enter value for c_upgradable_versions:

Enter value for c_minimum_compatible:

Enter value for c_minimum_compatible:

Enter value for c_minimum_compatible:

Enter value for c_oracle_high_version_4_dots:

Enter value for c_ltz_content_ver:

Enter value for c_ltz_content_ver:

Enter value for c_ltz_content_ver:

Enter value for c_ltz_content_ver:

Enter value for c_oracle_high_version_0_dots:

Enter value for c_upgradable_versions:

Enter value for c_default_processes:

Enter value for c_oracle_high_version_4_dots:

Enter value for c_oracle_high_version_0_dots:

ERROR at line 1:

ORA-04063: package body “SYS.DBMS_PREUP” has errors

ORA-06512: at line 11

Target Server Post-Upgrade Checklist

Complete these checks on the upgraded Oracle Database environment.

Query for any INVALID ObjectsSELECT substr(owner,1,12) owner, substr(object_name,1,12) object, substr(object_type,1,30) object, status from dba_objects WHERE status <> ‘VALID’;
Verify DBA_REGISTRYcol COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

Target Server Post-Upgrade Checklist

Complete these checks on the upgraded Oracle Database environment.

TASKDESCRIPTION
Query for any INVALID ObjectsSELECT substr(owner,1,12) owner, substr(object_name,1,12) object, substr(object_type,1,30) object, status from dba_objects WHERE status <> ‘VALID’;
Verify DBA_REGISTRYcol COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;