Sunday, June 20, 2021

How to run pre-upgrade utility before upgrade ?

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>


No comments:

Post a Comment