Upgrade 12c to 19c: Traiditional Way


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

 

Machine generated alternative text:Select Database
              Installation Option CJ Eel Select Database Edition
              Database Database sca edibon do to @EMU' E • 'tin n the a
              databaø

 

 

put c:\oracle as Oracle base!!

Machine generated alternative text:Specify Spec*' a
              'eh to Qr.de ttinos all end c 19C ORACLE Database This the
              Or le 19C up OnM dba dba ORACLE Data bas g

 

  • 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 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;