masoudb
Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 8598 MB. Check alert log during the upgrade to ensure there is remaining free space available in the recovery area.
The database has archivelog and flashback enabled, and the upgrade process will need free space to generate archived and flashback logs to the recovery area specified by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as that can cause the upgrade to not proceed.
DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 3598 MB of free space remaining, which may not be adequate for the upgrade.
Currently:
Fast recovery area : /u01/app/oracle/fast_recovery_area
Limit : 4182 MB
Used : 583 MB
Available : 3598 MB
Remove OLAP Catalog by running the 11.2.0.4.0 SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script.
Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported and will be automatically marked as OPTION OFF during the database upgrade if present. Oracle recommends removing OLAP Catalog (OLAP AMD) before database upgrade. This step can be manually performed before the upgrade to reduce downtime.
The OLAP Catalog component, AMD, exists in the database.
Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 19 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control, using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands, you will not be able to follow the progress of the script.
Starting with Oracle Database 12c, the local Enterprise Manager Database Control does not exist anymore. The repository will be removed from your database during the upgrade. This step can be manually performed before the upgrade to reduce downtime.
The database has an Enterprise Manager Database Control repository.
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.
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.
There are one or more materialized views in either stale or invalid state, or which are currently being refreshed.
Upgrade Oracle Application Express (APEX) manually before or after the database upgrade.
Starting with Oracle Database Release 18, APEX is not upgraded automatically as part of the database upgrade. Refer to My Oracle Support Note 1088970.1 for information about APEX installation and upgrades. Refer to MOS Note 1344948.1 for the minimum APEX version supported for your target database release. Unsupported versions of APEX will be in an INVALID state when its database dependencies are not in sync with the upgraded database.
The database contains APEX version 3.2.1.00.12, which is not supported on the target version 19.0.0.0.0. APEX must be upgraded to at least version 18.2.0.00.12 either before or after the database is upgraded
Gather stale data dictionary statistics prior to database upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
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 11.2.0.4 Oracle Database Performance Tuning Guide.
Dictionary statistics do not exist or are stale (not up-to-date).
Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the trigger or drop and re-create the trigger with a user that was granted directly with such. You can list those triggers using: SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIM(BASE_OBJECT_TYPE)='DATABASE' AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER').
The creation of database triggers must be done by users granted with ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted directly.
There is one or more database triggers whose owner does not have the right privilege on the database.
Gather statistics on fixed objects prior the upgrade.
Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 11.2.0.4 Oracle Database Performance Tuning Guide.
None of the fixed object tables have had stats collected.
Run $ORACLE_HOME/rdbms/admin/catnoexf.sql located in the new Oracle Database Oracle home to remove both EXF and RUL.
Starting with Oracle Database release 12.1, the Expression Filter (EXF) and Database Rules Manager (RUL) features are desupported, and are removed during the upgrade process. This step can be manually performed before the upgrade to reduce downtime.
Expression Filter (EXF) or Rules Manager (RUL) exist in the database.
Mandatory changes are applied automatically in the during_upgrade_pfile_dbname.ora file. Some of these changes maybe present in the after_upgrade_pfile_dbname.ora file. The during_upgrade_pfile_dbname.ora is used to start the database in upgrade mode. The after_upgrade_pfile_dbname.ora is used to start the database once the upgrade has completed successfully.
Mandatory changes are required to perform the upgrade. These changes are implemented in the during_ and after_upgrade_pfile_dbname.ora files.
| Parameter |
|---|
| cluster_database='FALSE' |
Check the Oracle Backup and Recovery User's Guide for information on how to manage an RMAN recovery catalog schema.
It is good practice to have the catalog schema the same or higher version than the RMAN client version you are using.
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.
To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process.
Minimum tablespace sizes for upgrade are estimates.
| Tablespace Name | Allocated | Minimum Required for Upgrade |
|---|---|---|
| SYSTEM | 760 MB | 900 MB |
| TEMP | 29 MB | 150 MB |
| UNDOTBS1 | 105 MB | 401 MB |
| SYSAUX | 550 MB | 673 MB |
| EXAMPLE | 346 MB | 460 MB |
Follow the instructions in the Oracle Multimedia README.txt file in <19 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 to determine if Oracle Multimedia is being used. If Oracle Multimedia is being used, refer to MOS note 2347372.1 for suggestions on replacing Oracle Multimedia.
Starting in release 19c, Oracle Multimedia is desupported. Object types still exist, but methods and procedures will raise an exception. Refer to 19 Oracle Database Upgrade Guide, the Oracle Multimedia README.txt file in <19 ORACLE_HOME>/ord/im/admin/README.txt, or MOS note 2555923.1 for more information.
Oracle Multimedia component (ORDIM) is installed.
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.
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.
There are user tables dependent on Oracle-Maintained object types.
Upgrade the database time zone file using the DBMS_DST package.
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.
The database is using time zone file version 14 and the target 19 release ships with time zone file version 32.
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.
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.
Found 5 user directory objects to be checked: DATA_FILE_DIR, LOG_FILE_DIR, MEDIA_DIR, SS_OE_XMLDIR, SUBDIR.
Gather dictionary statistics after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
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.
Oracle recommends gathering dictionary statistics after upgrade.
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;
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 11.2.0.4 Oracle Database Performance Tuning Guide.
Oracle recommends gathering fixed object statistics after upgrade. This recommendation is given for all preupgrade runs.