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.
| TAKS | DESCRIPTION |
| Clean up the database | As 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 views | Check 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 setting | Use 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 version | As 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 FALSE | As 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 downtime | EXEC 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
- 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
- Remove OLAP
- Remove case insensitive auth
- purge recyclebin → sql> purge dba_recyclebin;
- 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 Objects | SELECT 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_REGISTRY | col 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.
| TASK | DESCRIPTION |
| Query for any INVALID Objects | SELECT 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_REGISTRY | col 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; |
| TAKS | DESCRIPTION |
| Clean up the database | As 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 views | Check 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 setting | Use 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 version | As 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 FALSE | As 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 downtime | EXEC 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
- 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
- Remove OLAP
- Remove case insensitive auth
- purge recyclebin → sql> purge dba_recyclebin;
- 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 Objects | SELECT 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_REGISTRY | col 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.
| TASK | DESCRIPTION |
| Query for any INVALID Objects | SELECT 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_REGISTRY | col 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; |