Upgrade Oracle database to 19C

Upgrade Oracle database to 19C - Using DBUA Silent

With Oracle 12c database support coming to a EOS, It has become important for the DBAs to upgrade Oracle database to 19c. There are two method to upgrade oracle database :

1) Conventional Method - dbupgrade

2) Upgrade using DBUA Silent

In this blog , We will discuss about the upgradation of Oracle database using dbua method. Before upgrading let get to know about the what the minimum requirement for the upgrading database to 19c.

1) Upgrade Path for Oracle 19C

Minimum version of the database that can be directly upgraded to Oracle 19c

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

Intermediate upgrades needs to be carried for following releases

Indirect Upgrade Matrix
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

For any multi-step upgrade, if you must carry out two upgrades to upgrade to the current release, then you must run the preupgrade script twice: First, for the intermediate upgrade release, and second, for the target upgrade target release.

In case Intermediate upgrade you can use export import to upgrade the database if you do not want to take hassle of upgrading database twice.

2) Changes and Unsupported Oracle components in Oracle 19c

Desupport for Oracle Multimedia -
Oracle Multimedia is desupported in Oracle Database 19c, and the implementation is removed.As an alternative for image processing and conversion, Oracle recommends that you store multimedia content in SecureFiles LOBs, and use third party products, such as Piction. The ORDIM component remains in the registry and still has a VALID status. Oracle Multimedia objects and packages remain in the database. However, these objects and packages no longer function, and raise exceptions if there is an attempt made to use them. Oracle Locator is not affected by the desupport of Oracle Multimedia.
Desupport of Oracle Streams:
Starting in Oracle Database 19c (19.1), Oracle Streams is desupported. Oracle GoldenGate is the replication solution for Oracle Database.Note that Oracle Database Advanced Queuing is not deprecated, and is fully supported in Oracle Database 19c. Oracle Streams did not support features added in Oracle Database 12c (12.1) and later releases, including the multitenant architecture, LONG VARCHAR, and other new features. Oracle Streams replication functionality is superseded by GoldenGate. Preupgrade check "STREAMS_SETUP" will warn if Oracle Streams is presented. To remove Oracle streams in 18c refer to Oracle documentation, similarly refer to section "Removing an Oracle Streams Configuration" in the Oracle Streams Concepts and Administration Guide specific for the Oracle release from which you are removing.

3) Requirements and recommendations for source database

Take a cold or hot back up of the source database.
Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 19c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 19c to define the data security roles again.
If you upgrade an 11g database to Oracle Database 19c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 19c database.
If your source version's time zone is already higher than the destination's home, then apply the matching or any higher time zone patch to your destination home before upgrade. For latest DST patch refer to Note 412160.1
IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB. Refer to Note 1088970.1

Upgrade Apex:
Current Version:
-----------------
COMP_NAME    STATUS       VERSION
----------------------    ------------ ------------------------------
Oracle Application Express      VALID        5.0.4.00.12
SQL> select count(*) from APEX_050000.WWV_FLOWS where id = 4000;
COUNT(*)
----------
   1
If the query returns 0, it is a runtime only installation, and apxrtins.sql should be used for the upgrade.
If the query returns 1, this is a development install and apexins.sql should be used.
Create tablespace for apex:
--------------------------------
create tablespace apex DATAFILE 'G:\ORADATA\DEVDB\APEX.DBF' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 5G;
create tablespace apex_file DATAFILE 'G:\ORADATA\DEVDB\APEX_FILE.DBF' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 5G;
Backup Existing Apex directory in ORALCE HOME:
---------------------------------------------------
E:\app\Oradb\product\12.2.0\dbhome_1
Unzip new apex in oracle home.
Copy new apex from E:\app\Oradb\local\scripts\upgrade\apex to E:\app\Oradb\product\12.2.0\dbhome_1
unzip apex_20.1_en.zip in apex directory
Upgrade APEX:
--------------
run cmd as admin
set ORACLE_HOME=E:\app\Oradb\product\12.2.0\dbhome_1
set ORACLE_SID=devdb
cd E:\app\Oradb\product\12.2.0\dbhome_1\apex
sqlplus / as sysdba
spool E:\app\Oradb\local\scripts\upgrade\apex_upgrade.log
@apexins.sql APEX APEX_FILE TEMP /I/
spool off
Create an instance administrator user, if it does not exist and set their password
@apxchpwd.sql
user=apexadmin

if needed:
Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users, if it does not exist
@apex_rest_config.sql
Make sure the APEX_PUBLIC_USER account is unlocked.
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxxx ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxxx ACCOUNT UNLOCK;
select COMP_ID, VERSION, STATUS from dba_registry where COMP_ID like 'APEX%';
SYS> select COMP_ID, VERSION, STATUS from dba_registry where COMP_ID like 'APEX%';
COMP_ID                        VERSIO
------------------------------ ------------------------------
STATUS
--------------------------------------------
APEX                           20.1.0.00.13
VALID
No INVALID object / Components in Source
Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
Execute dbupgdiag.sql (refer Note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support.

4) Requirements and recommendations for target ORACLE_HOME

Verify the whether your operating system is certified for 19c.
Download and install latest RU if any
Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home

Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
Note min value of COMPATIBLE parameter to upgrade 19c is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater

Review patch recommendations as given in the article "Patches to apply before upgrading Oracle GI and DB to 19c (Doc ID 2539751.1)"

5) Pre-upgrade checks

Clean up Database

Empty the recycle bin
Check for INVALID objects in SYS and SYSTEM
Check for duplicate objects in SYS and SYSTEM
Check for INVALID, mandatory, obsolete component
Note: These will be taken care by preupgrade.jar.
Check materialized view

Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh the base table materialized views.
Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logsBefore upgrading Oracle Database, you must wait until all materialized views have completed refreshing. Run below query for Checking the mview refresh:

SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
Schema-Only Accounts and Upgrading EXPIRED Password Accounts
Before starting your upgrade, determine if you want to use password authenticate to default Oracle Database accounts where their passwords are in EXPIRED status, and their account is in LOCKED status

During upgrades to Oracle Database 19c, default Oracle accounts that have not had their passwords reset before upgrade (and are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete.

Because of this new feature, default accounts that are changed to schema-only accounts become unavailable for password authentication. The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database-provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.

If you want to prevent these Oracle accounts from being set to schema-only accounts during the upgrade, then you must either set a valid strong password for the account before you start the upgrade, or set a valid strong password for these accounts after upgrade, or unlock the accounts before you log in to the upgraded Oracle Database.

After the upgrade, an administrator can also enable password authentication for schema-only accounts. However, for better security, Oracle recommends that you keep these accounts as schema only accounts.
Copying Transparent Encryption Oracle Wallets

If Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home. You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
Open the Oracle wallet in mount.
For example:
SQL> STARTUP MOUNT
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN
Understanding Password Case Sensitivity

Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.

For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.
Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:

Identify if you have accounts that use only 10G case-insensitive password authentication versions.

Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.

Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.

For more information refer 19c Oracle database documentation
Running Upgrades with Read-Only Tablespace
Running Upgrades with Read-Only Tablespaces
Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB

6) Preparing the New Oracle Home for Upgrading

Copy configuration files from the Oracle home of the database being upgraded to the new release Oracle Database Oracle home.
If you have a password file that resides within the old environment Oracle home, then move or copy the password file to the new Oracle home. It is recommended to recreate the password file freshly to leverage the new features (if any) provided by orapwd utility.
Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases. Also, alter any parameter whose syntax has changed in the new release.
If you are upgrading a cluster database, then you need to change CLUSTER_DATABASE=FALSE before starting upgrade. NOTE: NA to DBUA method.
Databases That Use Oracle Label Security and Oracle Database Vault

Audit Table Preupgrade and Archive Requirements

For Oracle Database releases earlier than 12.1 using Oracle Label Security and Oracle Database Vault, you must run the OLS preprocess script before you upgrade.

If you are upgrading from a database earlier than Oracle Database release 12.1 that uses Oracle Label Security (OLS) and Oracle Database Vault, then you must first run the OLS preprocess script, olspreupgrade.sql, to process the aud$ table contents. The OLS upgrade moves the aud$ table from the SYSTEM schema to the SYS schema. The olspreupgrade.sql script is a preprocessing script required for this move.

Running the olspreupgrade.sql script before upgrading is mandatory for upgrading databases earlier than Oracle Database release 12.1 that use Oracle Label Security and Oracle Database Vault. Once you have upgraded to Oracle Database release 12.1, you do not have to perform the OLS preprocessing procedure going forward to patch or upgrade the database.
The olspreupgrade.sql script creates a temporary table PREUPG_AUD$ in the SYS schema and moves the SYSTEM.aud$ records to SYS.PREUPG_AUD$. As a safety measure, Oracle recommends that you archive your audit trail before running the olspreupgrade.sql script. If Oracle Label Security is installed on your database, and you are upgrading from an earlier release, then you must run the OLS preprocess script before upgrading.

Oracle Database Vault and Upgrades of Oracle Database Release 11.2

If Oracle Label Security is installed in the earlier release that you are upgrading, then grant the DV_PATCH_ADMIN role to SYS.
To run the OLS preprocess script on a release 11.2 database before upgrading:

1. Copy the following scripts script from the newly installed Oracle home (19c) to the Oracle home of the database that needs to be upgraded (11.2)
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
ORACLE_HOME/rdbms/admin/emremove.sql
ORACLE_HOME/olap/admin/catnoamd.sql

2. Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade.
3. Run the following statement:
SQL> GRANT DV_PATCH_ADMIN to SYS;
4.    At the system prompt, connect SYS as SYSDBA
CONNECT SYS AS SYSDBA
5.    Run the preprocess scripts for Data Vault
ORACLE_HOME/rdbms/admin/olspreupgrade.sql
ORACLE_HOME/rdbms/admin/emremove.sql
ORACLE_HOME/olap/admin/catnoamd.sql
You may continue to run your applications on the database while the preprocess scripts are running.

6.    After the olspreupgrade.sql completes its run successfully, start SQL*Plus and connect to the database as DVOWNER.

7.    Run the following SQL statement:
SQL> REVOKE DV_PATCH_ADMIN from SYS;
Granting the DV_PATCH_ADMIN Role to SYS for Oracle Database Vault
If Oracle Database Vault is enabled, then to perform checks for Oracle Data Vault, the upgrade process requires running three SQL scripts - olspreupgrade.sql, emremove.sql, catnoamd.sql

Start SQL*Plus and connect as DVOWNER to the database that you want to upgrade
Run the following statement:
SQL> GRANT DV_PATCH_ADMIN to SYS;
Manually remove DB control with emremove.sql
Stop/shutdown DB contro
emctl stop dbconsole

Login as sysdba
SQL>SET ECHO ON
SQL>SET SERVEROUTPUT ON
SQL>@emremove.sql >> Script located in new 12c ORACLE_HOME/rdbms/admin

7) Ensure additional check like no datafile is in backup mode ,Audit records are purged ,Gather stats are done and backup of the database information con be capture.

You can use below script for the same:

spool pre_checks_<DBNAME>.log
set time on
set timing on
set serveroutput on
set trimspool on
set lines 200 pages 2000
col comp_name for a50
@?/rdbms/admin/utlrp.sql
select comp_id,comp_name,status,version from dba_registry;
select * from sys.registry$history order by 1 desc;
select * from registry$sqlpatch;
col ACTION_TIME for a40
select PATCH_ID,DESCRIPTION,ACTION_TIME from dba_registry_sqlpatch order by 3;

select '==========Invalid objects Info============' from dual;
select count(1),owner,object_type,status from dba_objects where status <> 'VALID' group by owner,object_type,status;
col owner for a30
col object_name for a50
select owner,object_name,object_type,status from dba_objects where status <> 'VALID' order by owner,object_name,object_type;

select '==========Time Zone Info============' from dual;
SELECT * FROM v$timezone_file;
SELECT tz_version FROM registry$database;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;

Select '=========Check Mview Logs=================' from dual;
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

Select '=====================USER Pass VERSION===========' from dual;
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

Select '==============Check Datafile Backup Mode================' from dual;
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

Select '===========Purge recycle bin============' from dual;
PURGE DBA_RECYCLEBIN;

select '==========AUD Table Count==========' from dual;
select count(1) from FGA_LOG$;
select count(1) from AUD$;
select count(*) from unified_audit_trail;

select '============Gather Dictiorary Stats============' from dual;
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

select '==============Database Link backup==============' from dual;
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#;

select '==============Collect DB Info=========' from dual;

col name format a70
select name from v$datafile;
select name from v$tempfile;
select member name from v$logfile;
select name from v$tablespace;
select name from v$controlfile;

col grantee for a30
col granted_role for a30
SELECT grantee, granted_role FROM dba_role_privs order by grantee;
col username for a30
col password for a30
col account_status for a30
col profile for a30
col EXPIRY_DATE for a30
select username,password,account_status,profile,EXPIRY_DATE from dba_users order by profile;

col comp_id format a12
col version format a12
col comp_name format a40
col status format a10
SELECT Comp_ID, Status, Version, Comp_Name FROM DBA_Registry ORDER by 1,2;

col ACTION_TIME format a30
col COMMENTS format a30
col ACTION format a10
select * From dba_registry_history;

set pages 999
col object_name format a30
col owner format a20
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_NAME, OBJECT_TYPE;

col tablespace_name format a30 heading "Tablespaces|Name"
col total_space format 999,990.00 heading "Current Total|Space (Gb)"
col free_space format 999,990.00 heading "Current Free|Space (Gb)"
col pct_free format 990.00 heading "Current|% Free"
col pct_used format 990.00 heading "Current|% Used"

select t.tablespace_name, s.total_space, f.free_space, (f.free_space / s.total_space) * 100 pct_free , (s.total_space-f.free_space)/s.total_space*100 pct_used
from dba_tablespaces t, (select tablespace_name, sum(user_bytes) / 1073741824 total_space, sum(decode(autoextensible, 'YES', maxbytes, user_bytes)) / 1073741824 extensible
from dba_data_files group by tablespace_name) s, (select tablespace_name, sum(bytes) / 1073741824 free_space from dba_free_space group by tablespace_name) f
where s.tablespace_name = t.tablespace_name and f.tablespace_name = t.tablespace_name ;

Spool off

8) Preupgrade step

Execute Preupgrade script from source home

Set environment variable to source home and database:

E:\app\oracle\product\19.0.0\dbhome_1\rdbms\admin>set ORACLE_HOME=D:\app\Oracle\virtual\product\12.2.0\dbhome_1

E:\app\oracle\product\19.0.0\dbhome_1\rdbms\admin>set ORACLE_SID=uatdb

E:\app\oracle\product\19.0.0\dbhome_1\rdbms\admin>cd D:\app\Oracle\virtual\product\12.2.0\dbhome_1\jdk\bin

Run below command for performing per-upgrade check:

D:\app\Oracle\virtual\product\12.2.0\dbhome_1\jdk\bin>java -jar D:\app\oracle_new\product\19.0.0\dbhome_1\rdbms\admin\preupgrade.jar TERMINAL TEXT
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 7 on 2020-08-09T11:38:01

Upgrade-To version: 19.0.0.0.0

=======================================
Status of the database prior to upgrade
=======================================
      Database Name: UATDB
     Container Name: UATDB
       Container ID: 0
            Version: 12.2.0.1.0
     DB Patch Level: WINDOWS DB BUNDLE PATCH 12.2.0.1.190416(64bit):29394003
         Compatible: 12.2.0
          Blocksize: 8192
           Platform: Microsoft Windows x86 64-bit
      Timezone File: 26
Database log mode: NOARCHIVELOG
           Readonly: FALSE
            Edition: EE

Oracle Component                       Upgrade Action    Current Status
----------------                       --------------    --------------
Oracle Server                          [to be upgraded] VALID
JServer JAVA Virtual Machine           [to be upgraded] VALID
Oracle XDK for Java                    [to be upgraded] VALID
Real Application Clusters              [to be upgraded] OPTION OFF
Oracle Workspace Manager               [to be upgraded] VALID
OLAP Analytic Workspace                [to be upgraded] VALID
Oracle Label Security                  [to be upgraded] VALID
Oracle Database Vault                  [to be upgraded] VALID
Oracle Text                            [to be upgraded] VALID
Oracle XML Database                    [to be upgraded] VALID
Oracle Java Packages                   [to be upgraded] VALID
Oracle Multimedia                      [to be upgraded] VALID
Oracle Spatial                         [to be upgraded] VALID
Oracle OLAP API                        [to be upgraded] VALID

==============
BEFORE UPGRADE
==============

REQUIRED ACTIONS
================
1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.

The database contains 42 objects in the recycle bin.

The recycle bin must be completely empty before database upgrade.

RECOMMENDED ACTIONS
===================
2. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with

SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;

30 objects are INVALID.

There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.

3. Remove the SEC_CASE_SENSITIVE_LOGON instance initialization parameter
setting, to configure your system to use case sensitive password
authentication by default.

      Your database system is configured to enforce case insensitive password
      authentication (the SEC_CASE_SENSITIVE_LOGON instance initialization
      parameter setting is FALSE).

      Starting with Oracle Database release 12.2, Exclusive Mode is the default
      password-based authentication mode. Case insensitive password
      authentication is not supported in Exclusive Mode. If your system needs
      to use case insensitive password authentication, Exclusive Mode must be
      switched off prior to the upgrade. See the Network Reference Manual
      chapter about the SQLNET.ORA parameter
      SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.

4. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
      empty before doing upgrade, unless you have strong business reasons not
      to do so. You can use dbms_mview.refresh() to refresh the MVs except
      those stale ones to be kept due to business need. If there are any stale
      MVs depending on changes in sys.sumdelta$, do not truncate it, because
      doing so will cause wrong results after refresh. Please refer to the
      Materialized View section in MOS Note 2380601.1 for more details.

There are one or more materialized views in either stale or invalid
state, or which are currently being refreshed.

      Oracle recommends that all materialized views (MV's) are refreshed before
      upgrading the database because this will clear the MV logs and the
      sumdelta$ table and may reduce the upgrade time. If you choose to not
      refresh some MVs, the change data for those MV's will be carried through
      the UPGRADE process. After UPGRADE, you can refresh the MV's and MV
      incremental refresh should work in normal cases.

5. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Dictionary statistics do not exist or are stale (not up-to-date).

      Dictionary statistics help the Oracle optimizer find efficient SQL
      execution plans and are essential for proper upgrade timing. Oracle
      recommends gathering dictionary statistics in the last 24 hours before
      database upgrade.

For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.

INFORMATION ONLY
================
6. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.

                                                 Min Size
      Tablespace                        Size     For Upgrade
      ----------                     ---------- -----------
      SYSTEM                             890 MB      1002 MB

Minimum tablespace sizes for upgrade are estimates.

7. Consider removing the following deprecated initialization parameters.

      Parameter
      ---------
      sec_case_sensitive_logon

These deprecated parameters probably will be obsolete in a future release.

8. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.

      If you are using a version of the recovery catalog schema that is older
      than that required by the RMAN client version, then you must upgrade the
      catalog schema.

It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database UATDB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade/preupgrade_fixups.sq
l

=============
AFTER UPGRADE
=============

REQUIRED ACTIONS
================
None

RECOMMENDED ACTIONS
===================
9. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
      $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
      to VALIDATE and UPGRADE any user tables affected by changes to
      Oracle-Maintained types.

There are user tables dependent on Oracle-Maintained object types.

      If the -T option is used to set user tablespaces to READ ONLY during the
      upgrade, user tables in those tablespaces, that are dependent on
      Oracle-Maintained types, will not be automatically upgraded. If a type is
      evolved during the upgrade, any dependent tables need to be re-validated
      and upgraded to the latest type version AFTER the database upgrade
      completes.

10. Upgrade the database time zone file using the DBMS_DST package.

The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.

      Oracle recommends upgrading to the desired (latest) version of the time
      zone file. For more information, refer to "Upgrading the Time Zone File
      and Timestamp with Time Zone Data" in the 19 Oracle Database
      Globalization Support Guide.

11. Recreate directory objects to remove any symbolic links from directory
      paths. To identify paths that contain symbolic links before upgrading,
      use OS commands like UNIX file or WINDOWS dir. After upgrading, run
      $ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
      with symbolic links in the path.

Found 2 user directory objects to be checked: ORACLECLRDIR, WD_XML_DIR.

      Starting in Release 18c, symbolic links are not allowed in directory
      object paths used with BFILE data types, the UTL_FILE package, or
      external tables.

12. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Oracle recommends gathering dictionary statistics after upgrade.

      Dictionary statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. After a database
      upgrade, statistics need to be re-gathered as there can now be tables
      that have significantly changed during the upgrade or new tables that do
      not have statistics gathered yet.

13. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

This recommendation is given for all preupgrade runs.

      Fixed object statistics provide essential information to the Oracle
      optimizer to help it find efficient SQL execution plans. Those
      statistics are specific to the Oracle Database release that generates
      them, and can be stale upon database upgrade.

For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.

ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database UATDB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following

SQL>@D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade/postupgrade_fixups.s
ql

==================
PREUPGRADE SUMMARY
==================
D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade.log
D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade_fixups.sql
D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\postupgrade_fixups.sql

Preupgrade complete: 2020-08-09T11:38:02

D:\app\Oracle\virtual\product\12.2.0\dbhome_1\jdk\bin>

Review logs and take required action to resolve the issue.

Once done run D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\preupgrade_fixups.sql on the source database.

9) Start the upgrade

E:\app\oracle\product\19.0.0\dbhome_1\bin\dbua -silent -sid infradb -oracleHome D:\app\Oracle\virtual\product\12.2.0\dbhome_1 -ignorePreReqs -upgrade_parallelism 12 -upgradeTimezone true

Run post upgrade script : D:\app\Oracle\virtual\cfgtoollogs\UATDB\preupgrade\postupgrade_fixups.sql

Run utlrtp on Database: @?/rdbms/admin/utlrp

10) Make Sure all the components are valid in database.

11)Upgrade the Time Zone File Version After Upgrading Oracle Database

No need as this have been taken care by the DBUA.

12) Upgrading Statistics Tables

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS', 'dictstattab');

13)Check and change compatible parameter:

SELECT name, value FROM v$parameter WHERE name = 'compatible';

NOTE: Conventional method will require few manual steps which include set cluster_database parameter to false , upgrading Time Zone and in case of RAC database upgarding home using SRVCTL command.We would prefer to use DBUA method where all these steps are automatied

You can learn more about oracle database administration:

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

==========================================================

Please check our other blogs for Upgrade.

Search This Blog

Database Solutions