How to run pre-upgrade utility before upgrade ?
[oracle@localhost ~]$ /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /backup/19.3.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: 1 on 2021-04-28T16:15:41
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: orcl
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
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
================
None
RECOMMENDED ACTIONS
===================
1. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
This action may be done now or when starting the database in upgrade mode
using the 19 ORACLE HOME.
Parameter Currently 19 minimum
--------- --------- ------------------
*memory_target 713031680 1203765248
The database upgrade process requires certain initialization parameters
to meet minimum values. The Oracle upgrade process itself has minimum
values which may be higher and are marked with an asterisk. After
upgrading, those asterisked parameter values may be reset if needed.
2. 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.
There is one or more non-fresh MV in the database or sumdelta$ is not
empty.
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 make the UPGRADE process faster. 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.
3. (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.
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.
There is one or more non-fresh MV in the database or sumdelta$ is not
empty.
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 make the UPGRADE process faster. 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.
INFORMATION ONLY
================
5. 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 840 MB 947 MB
TEMP 131 MB 150 MB
UNDOTBS1 70 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
6. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
4618 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space.
Archiving cannot proceed if the archive log destination is full during
upgrade.
Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch
The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.
7. 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 ORCL
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
8. 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.
9. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
10. (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.
11. 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 ORCL
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2021-04-28T16:15:41
[oracle@localhost ~]$
SQL> @/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-04-28 16:15:36
For Source Database: ORCL
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. mv_refresh NO Informational only.
Further action is optional.
3. dictionary_stats YES None.
4. mv_refresh NO Informational only.
Further action is optional.
5. tablespaces_info NO Informational only.
Further action is optional.
6. min_archive_dest_size NO Informational only.
Further action is optional.
7. rman_recovery_version NO Informational only.
Further action is optional.
The 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 reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
SQL> SQL> SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL>
