AutoUpgrade utility:
The AutoUpgrade tool is a new upgrade utility which allows you to upgrade your Oracle databases with minimal manual intervention. The AutoUpgrade tool is designed in such a way that when it’s run, it not only identifies the issues before the database upgrade but also fixes them, and then performs the database upgrade and runs any post upgrade steps.
The AutoUpgrade tool can upgrade both the single instances and the cluster databases. It also supports both the non-container database(non-cdb) and thecontainer database(cdb) upgrades. The AutoUpgrade tool supports upgrading a non-cdb database to apluggable database(pdb) using one single command, and also supports all the operating systems where we can have the Oracle databases configured, and it upgrades multiple databases in one single operation.
It is used only to upgrade the Oracle database NOT GRID
Support of the AutoUpgrade utility
The AutoUpgrade tool can be used to upgrade the Oracle databases to the following target database versions:
• Oracle Database version 12c (12.2.0 with Jan 2019 release update and later)
• Oracle Database version 18c (18.5.0 and later)
• Oracle Database version 19c (19.3.0 and later)
The minimum Oracle database version that the AutoUpgrade utility can upgrade from is Oracle Database 11.2.0.4. As of today, the AutoUpgrade tool can be used to upgrade the Oracle databases from the following versions to Oracle 19c:
• Oracle Database version 18c
• Oracle Database version 12.2.0.1
• Oracle Database version 12.1.0.2
• Oracle Database version 11.2.0.4
Installing the AutoUpgrade utility
The AutoUpgrade utility uses an autoupgrade.jar
AutoUpgrade tool can be downloaded from the My Oracle support ( MOS ) (Doc ID 2485457.1).
Processing modes of AutoUpgrade
Analyze mode : The Analyze mode is the first mode that the AutoUpgrade utility runs as part of the database upgrade. In this mode, it reads the source database and preforms all the prechecks and prepares the database for the upgrade. The AutoUpgrade process only reads the database, and this mode will not make any changes to the source database, and the source database can continue to be available during in Analyze mode. This mode will generate the log files which needs to be reviewed to identify any potential issues and fix them before going to the upgrade process.
Fixup mode : The Fixup mode also reads the source database and identifies any potential issues that needs to be fixed before the database upgrade. The identified fixes can either be fixed automatically by the AutoUpgrade utility when we run the upgrade in the deploy mode, or fixed manually before initiating the database upgrade execution in the deploy mode.
Deploy mode : The Deploy mode is the mode which actually executes the database upgrade, based on the information defined in the configuration file. This mode will start by performing a pre-check in the source database and fix any issues that can be fixed automatically and ensure that the database is ready for the database upgrade. It will then perform the database upgrade and once the upgrade completes, it will also perform the post upgrade tasks including the Timezone version upgrade and any custom scripts that we defined in the configuration file (like running utlrp script).
Upgrade mode : The Upgrade mode also performs the same steps as the Deploy mode, the only difference is that this mode is used when you don't have access to the source database home.
Stages of AutoUpgrade utility
When the AutoUpgrade utility is started with any of the modes mentioned earlier, the Oracle AutoUpgrade job manager process will be started, and this process will parse the configuration file and create the required jobs depending on the processing mode. Each of these jobs goes through the various stages and performs a set of specific actions to perform the actual upgrade work. Each of these AutoUpgrade jobs generates a unique ID, called jobid , specific for each of the database mentioned in the configuration file. AutoUpgrade uses jobid as an option to detect its current stage and if it has to resume the operation, it will resume from the previously stopped point without starting from the beginning of the operation. The various series of stages that the AutoUpgrade process goes through are given as follows:
• Setup stage : This is the first stage where the AutoUpgrade job manager process creates the upgrade job and starts the job.
• GRP stage : In this stage, the AutoUpgrade tool creates the GRP before starting any upgrade process in the source database. We can use this restore point to downgrade the database using the flashback method.
• Pre-upgrade stage : In this stage, AutoUpgrade performs various pre-upgrade checks in the source database which includes the checks like free space, available memory, and so on.
• Pre-checks stage : In this stage, AutoUpgrade will read the source database and perform readiness checks to see if the source database meets all the requirements to get upgraded.
• Pre-fixups stage : In this stage, AutoUpgrade performs the pre-upgrade fixups which it has identified in the precheck stage like gathering the statistics, purging the recycle bin, and so on.
• Drain stage : In this stage, the AutoUpgrade process will start shutting down the source listener and the source database.
• Dbupgrade stage : This is the stage where the AutoUpgrade process will perform the actual upgrade process. After the upgrade task, it also runs the utlrp script to compile the invalid objects in the upgraded database.
• Postchecks stage : In this stage, the AutoUpgrade process will run the post upgrade checks to ensure that the database is upgraded successfully. If it finds everything alright, it will then move to running the post upgrade fixup scripts in the next stage.
• Postfixups stage : In this stage, the AutoUpgrade process will perform the post upgrade tasks including the timezone upgrade.
• PostUpgrade stage : This is the final stage of the upgrade process where the AutoUpgrade will upgrade the database configuration to the target database version and start the database from the target database home. It will also copy all the database configuration files (like tnsnames.ora , sqlnet.ora , etc.) from the source database home and add them to the target database home. It will then update the configuration file called /etc/oratab to point to the new database home.
Restarting an AutoUpgrade job
we can use the resume option from the AutoUpgrade command prompt and, by default, the AutoUpgrade will resume the job from the point where it was halted.
you can then simply resume the job using the resume option and it will pick it up from where it was stopped. (just run the deploy command again )
Precheck all basic requirement before upgrading like the db version should not be less that 11.4 and also its optional but make sure to patch the 19c home with latest patch . like for
Oracle Database 12.2.0.1 with Jan 2019 RU
So AUTOUPGRADE is very simple and easy we need to follow only 3 steps.
1. create proper directories and create config file.
2. Run analyze and resolve issue.
3. run deploy and have fun .
Configuration File of AutoUpgrade Utility (config command should be run from the location where you want your config file location)
/app/oracle/product/19.0.0.0/db/jdk/bin/java -jar /exports/upgrade/autoupgrade.jar -create_sample_file config upgrade19c.cfg
Here : /app/oracle/product/19.0.0.0/db/jdk/bin/java (Its location of the java in 19c home)
: /exports/upgrade/autoupgrade.jar (autoupgrade utility )
: upgrade19c.cfg (config file: you can change upgrade19c name to desired name)
NOW FUN PART lets edit config file but before that lets create directories.
we need 2 directories : Global and Local.
we need 2 directories : Global and Local.
create global and local directory.(you can create folder anywhere)
Global: [ /exports/upgrade/logs/hostname/global/ (jobname ) ] {jobname here means global name for the job like wave1 as we can add and upgrade 3 or more database at a time in single config file}
Local : [ /exports/upgrade/logs/hostname/ (DB-Name) ]
>>>
The global parameters are the parameters which specifies the behavior for all the databases defined in the configuration file. The global parameters are optional.
The global parameters are the parameters which specifies the behavior for all the databases defined in the configuration file. The global parameters are optional.
vi upgrade19c.cfg (by default it will look something like this)
>>>
>>>
### Global parameters ##################################
global.autoupg_log_dir=/u01/software/upgrade/global
## First database ######################################
upg1.log_dir=/u01/software/upgrade
upg1.sid=testdb
upg1.source_home=/u01/app/Oracle/product/12c/db_1
upg1.target_home=/u01/app/Oracle/product/19c/db_1
upg1.upgrade_node=hostname
upg1.run_utlrp=yes
upg1.timezone_upg=yes
upg1.target_version=19
### Second database ####################################
upg2.sid=database1
upg2.source_home=/u01/app/Oracle/product/111.2.0.4/db_1
in the cfg file you have to make few changes (remove # sign ).
1. change the global folder location (global.autoupg_log_dir)
2. change the local folder location (upg1.log_dir)
3. verify source and target home location [ upg1.source_home ,upg1.target_home ]
4. upg1.run_utlrp and upg1.timezone_upg is yes (if you dont want to upgrade timezone than plz change it to no
5. upg1.target_version= 19 (target db upgrade version )
>>>sample cfg file
global.autoupg_log_dir=/exports/upgrade/logs/host/global/wave1
# Database number 1 - Full DB/CDB upgrade
#
upg1.log_dir=/exports/upgrade/logs/host/database1 # Path of the log directory for the upgrade job
upg1.sid=db1 # ORACLE_SID of the source DB/CDB
upg1.source_home=/app/oracle/product/12.2.0.1/db # Path of the source ORACLE_HOME
upg1.target_home=/app/oracle/product/19.0.0.0/db # Path of the target ORACLE_HOME
upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) |
upg1.upgrade_node=usrncXXXX # Optional. To find out the name of your node
upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade
upg1.timezone_upg=yes # Optional. Whether or not to run the timezone upgrade
upg1.target_version=19 # Oracle version of the target ORACLE_HOME. Only required
#
# Database number 2 - Unplug/Plug upgrade
#
upg2.log_dir=/exports/upgrade/logs/host/database2 # Path of the log directory for the upgrade job
upg2.sid=db2 # ORACLE_SID of the source DB/CDB
upg2.source_home=/app/oracle/product/12.2.0.1/db # Path of the source ORACLE_HOME
upg2.target_home=/app/oracle/product/19.0.0.0/db # Path of the target ORACLE_HOME
upg2.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss]
upg2.upgrade_node=usrnXXXX # Optional. To find out the name of your node, run the hostname utility. Default is ''localhost''
upg2.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade
upg2.timezone_upg=yes # Optional. Whether or not to run the timezone upgrade
upg2.target_version=19 # Oracle version of the target ORACLE_HOME. Only required
Now run the analyze command to check if the autoupgade have any manual error that need to be resolved .
>>>Step 2
/app/oracle/product/19.0.0.0/db/jdk/bin/java -jar /exports/upgrade/autoupgrade.jar -config /exports/upgrade/configfile/upgrade19c.cfg -mode analyze
It will take you to upg> prompt
you can run lsj command to check status (upg> lsj) once analyze command completes it will look some thing like this .
>>with error >> clearly showing failure (no worries we will resolve that )
==========================================
Autoupgrade Summary Report
==========================================
[Date] Thu Aug 11 14:24:21 PDT 2022
[Number of Jobs] 2
==========================================
[Job ID] 100
==========================================
[DB Name] db1
[Version Before Upgrade] 12.2.0.1.0
[Version After Upgrade] 19.15.0.0.0
------------------------------------------
[Stage Name] PRECHECKS
[Status] FAILURE
[Start Time] 2022-08-11 14:22:36
[Duration] 0:00:30
[Log Directory] /exports/upgrade/logs/xxxx/db1/db1/100/prechecks
[Detail] /exports/upgrade/logs/xxxx/db1/db1/100/prechecks/db1_preupgrade.log
Check failed for db1, manual intervention needed for the below checks
[FLASH_RECOVERY_AREA_SETUP, JAVAVM_STATUS, ARCHIVE_MODE_ON]
Cause:
Reason:Database Checks has Failed details in /exports/upgrade/logs/xxxx/db1/db1/100/prechecks
Action:[MANUAL]
Info:Return status is ERROR
ExecutionError:No
Error Message:None
------------------------------------------
==========================================
[Job ID] 101
==========================================
[DB Name] db2
[Version Before Upgrade] 12.2.0.1.0
[Version After Upgrade] 19.15.0.0.0
------------------------------------------
[Stage Name] PRECHECKS
[Status] FAILURE
[Start Time] 2022-08-11 14:22:38
[Duration]
[Log Directory] /exports/upgrade/logs/xxxxx/db2/db2/101/prechecks
[Detail] /exports/upgrade/logs/xxxxxx/db2/db2/101/prechecks/rgt01s_preupgrade.log
Check failed for db2, manual intervention needed for the below checks
[JAVAVM_STATUS]
Cause:
Reason:Database Checks has Failed details in /exports/upgrade/logs/xxxx/db2/db2/101/prechecks
Action:[MANUAL]
Info:Return status is ERROR
ExecutionError:No
Error Message:None
>>you can see no error or many errors based on the different situations.
IN ORDER TO CHECK ERRORS OPEN PREUPGRADE.LOG FILE
the log file will be divided into parts REQUIRED ACTIONS, RECOMMENDED ACTIONS, INFORMATION ONLY with options like auto fix etc.
now usually autofix means utility will resolve it for you .
But i have seen during our upgradation . Few times upgrade stuck in postfix and some time during upgrade when i leave error in REQUIRED ACTIONS and RECOMMENDED ACTIONS part which are autofix . so I always advice to resolve all error which comes in REQUIRED ACTIONS and RECOMMENDED ACTIONS for smooth upgrade.
once all errors resolved please re-run analyze command and check preupgrade.log . make sure no errors in REQUIRED ACTIONS and RECOMMENDED ACTIONS left uncheck .
Below log show first and second run analyze command results.
Sample log file : first run
Report generated by AutoUpgrade 22.4.220712 (#161fde38)
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: rg
Container Name: rg
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717
Compatible: 11.2.0.3
Blocksize: 8192
Platform: HP-UX IA (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
Oracle Java Packages [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
*
* ALL Components in This Database Registry:
*
Component Current Current Original Previous Component
CID Version Status Version Version Schema
---------- ----------- ----------- ----------- ----------- ------------
CATALOG 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATJAVA 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATPROC 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
JAVAVM 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
OWM 12.2.0.1.0 VALID 11.2.0.3.0 WMSYS
XDB 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 XDB
XML 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Contact Oracle Support for instructions on how to resolve this error.
ERROR: ORA-29548 ORA-29548: Java system class reported: joxcsys: release
mismatch, 12.2.0.1.0 1.8 in database (classes.bin) vs 12.2.0.1.220118 1.8
in executable
There is a problem with the JAVAVM component and database upgrade cannot
be performed until it is corrected.
JAVAVM component must be functioning properly before database upgrade.
RECOMMENDED ACTIONS
===================
2. (AUTOFIXUP) Connect to the database as SYS to drop all Data Pump Advanced
Queuing (AQ) tables prior to upgrading. Check MOS note 2789059.1 for
details.
There exists at least one Data Pump Advanced Queuing (AQ) table in the
SYS schema which might prevent Data Pump AQ message types from getting
re-created.
The database needs to be free of Data Pump Advanced Queuing (AQ) tables
in order for Data Pump AQ message types to be re-created during the
database upgrade.
3. (AUTOFIXUP) Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to
recompile invalid objects. Parameter job_queue_processes is set to 4000.
utlrp.sql will run parallel.
2898 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
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. Remove Streams setup. For detailed steps, refer to the section "Removing
an Oracle Streams Configuration" in the Oracle Streams Concepts and
Administration Guide specific for the Oracle release from which you are
removing. For versions pre-12.1.0.2, the procedure
dbms_streams_adm.remove_streams_configuration must not be used as may
lead to unwanted results. Instead, use the other procedures
(dbms_capture_adm.drop_capture, dbms_apply_adm.drop_apply,
dbms_streams_adm.remove_queue, etc). For 12.1.0.2 and higher, procedure
dbms_streams_adm.remove_streams_configuration can be safely used.
Oracle Streams feature is configured in the database.
Starting with Oracle Database 19, Oracle Streams is desupported. It is
strongly advised to remove any streams configuration manually.
6. Remove the expired snapshots and shrink AWR tables with more than 50%
free space before upgrade to reduce the upgrade downtime.
A large number of expired AWR snapshot exist in the database.
Automatic Workload Repository collects performance statistics to help
DBAs to diagnose and troubleshoot performance problems in the database.
Oracle recommends removing expired AWR data before database upgrade.
7. (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.
8. (AUTOFIXUP) Review and remove any unnecessary HIDDEN/UNDERSCORE
parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
_aggregation_optimization_settings
_always_anti_join
_always_semi_join
_and_pruning_enabled
_b_tree_bitmap_plans
_bloom_filter_enabled
_bloom_folding_enabled
_bloom_pruning_enabled
_complex_view_merging
_compression_compatibility
_connect_by_use_union_all
_convert_set_to_join
_cost_equality_semi_join
_cpu_to_io
_cursor_obsolete_threshold
_dimension_skip_null
_disable_streams_pool_auto_tuning
_disk_sector_size_override
_eliminate_common_subexpr
_enable_type_dep_selectivity
_fast_full_scan_enabled
_first_k_rows_dynamic_proration
_gby_hash_aggregation_enabled
_generalized_pruning_enabled
_globalindex_pnum_filter_enabled
_gs_anti_semi_join_allowed
_improved_outerjoin_card
_improved_row_length_enabled
_index_join_enabled
_kghdsidx_count
_ksb_restart_policy_times
_left_nested_loops_random
_lm_share_lock_opt
_local_communication_costing_enabled
_minimal_stats_aggregation
_mmv_query_rewrite_enabled
_new_initial_join_orders
_new_sort_cost_estimate
_nlj_batching_enabled
_optim_adjust_for_part_skews
_optim_enhance_nnull_detection
_optim_new_default_join_sel
_optim_peek_user_binds
_optimizer_adaptive_cursor_sharing
_optimizer_better_inlist_costing
_optimizer_cbqt_no_size_restriction
_optimizer_coalesce_subqueries
_optimizer_complex_pred_selectivity
_optimizer_compute_index_stats
_optimizer_connect_by_combine_sw
_optimizer_connect_by_cost_based
_optimizer_connect_by_elim_dups
_optimizer_correct_sq_selectivity
_optimizer_cost_based_transformation
_optimizer_cost_hjsmj_multimatch
_optimizer_cost_model
_optimizer_dim_subq_join_sel
_optimizer_distinct_agg_transform
_optimizer_distinct_elimination
_optimizer_distinct_placement
_optimizer_eliminate_filtering_join
_optimizer_enable_density_improvements
_optimizer_enable_extended_stats
_optimizer_enable_table_lookup_by_nl
_optimizer_enhanced_filter_push
_optimizer_extend_jppd_view_types
_optimizer_extended_cursor_sharing
_optimizer_extended_cursor_sharing_rel
_optimizer_extended_stats_usage_control
_optimizer_false_filter_pred_pullup
_optimizer_fast_access_pred_analysis
_optimizer_fast_pred_transitivity
_optimizer_filter_pred_pullup
_optimizer_fkr_index_cost_bias
_optimizer_full_outer_join_to_outer
_optimizer_group_by_placement
_optimizer_improve_selectivity
_optimizer_interleave_jppd
_optimizer_join_elimination_enabled
_optimizer_join_factorization
_optimizer_join_order_control
_optimizer_join_sel_sanity_check
_optimizer_max_permutations
_optimizer_mode_force
_optimizer_multi_level_push_pred
_optimizer_native_full_outer_join
_optimizer_new_join_card_computation
_optimizer_null_aware_antijoin
_optimizer_or_expansion
_optimizer_order_by_elimination_enabled
_optimizer_outer_join_to_inner
_optimizer_outer_to_anti_enabled
_optimizer_push_down_distinct
_optimizer_push_pred_cost_based
_optimizer_rownum_bind_default
_optimizer_rownum_pred_based_fkr
_optimizer_skip_scan_enabled
_optimizer_sortmerge_join_inequality
_optimizer_squ_bottomup
_optimizer_star_tran_in_with_clause
_optimizer_system_stats_usage
_optimizer_table_expansion
_optimizer_transitivity_retain
_optimizer_try_st_before_jppd
_optimizer_undo_cost_change
_optimizer_unnest_corr_set_subq
_optimizer_unnest_disjunctive_subq
_optimizer_use_cbqt_star_transformation
_optimizer_use_feedback
_or_expand_nvl_predicate
_ordered_nested_loop
_parallel_broadcast_enabled
_partition_view_enabled
_pivot_implementation_method
_pre_rewrite_push_pred
_pred_move_around
_push_join_predicate
_push_join_union_view
_push_join_union_view2
_px_minus_intersect
_px_partition_scan_enabled
_px_pwg_enabled
_px_ual_serial_input
_query_rewrite_setopgrw_enable
_remove_aggr_subquery
_replace_virtual_columns
_resource_manager_plan
_right_outer_hash_enable
_selfjoin_mv_duplicates
_smm_max_size
_smm_min_size
_smm_px_max_size
_sql_model_unfold_forloops
_sqltune_category_parsed
_subquery_pruning_enabled
_subquery_pruning_mv_enabled
_table_scan_cost_plus_one
_union_rewrite_for_gs
_unnest_subquery
_upgrade_capture_noops
_upgrade_optim
_use_column_stats_for_function
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
INFORMATION ONLY
================
9. (AUTOFIXUP) 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.
Parameter
---------
local_listener=remove
cluster_database=FALSE
remote_login_passwordfile=NONE
core_dump_dest=remove
Mandatory changes are required to perform the upgrade. These changes are
implemented in the during_ and after_upgrade_pfile_dbname.ora files.
10. 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.
11. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
CATALOG 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATJAVA 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATPROC 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
JAVAVM 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
OWM 12.2.0.1.0 VALID 11.2.0.3.0 WMSYS
XDB 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 XDB
XML 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
Review the information before upgrading.
12. Here is a count of invalid objects by Oracle-maintained users:
Oracle-Maintained User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
14. (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.
15. (AUTOFIXUP) 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.
16. 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 7 user directory objects to be checked: DPUMP_DIR, EXPDP_DIR,
EXP_ILM, IMPDP_DIR, PREUPGRADE_DIR, TOAD_BDUMP_DIR, VMA.
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.
17. (AUTOFIXUP) Recompile the objects with timestamp mismatch. Please refer
to MOS note 781959.1 for more details.
There are objects whose timestamp are mismatched with its parent objects.
Timestamp of dependent objects must coincide with the timestamp of parent
objects.
18. (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.
19. 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;
Oracle recommends gathering fixed object statistics after upgrade. 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.
20. (AUTOFIXUP) Run @?/rdbms/admin/utlrp.sql in order to recompile any
invalid objects.
There are invalid objects in the database after the upgrade.
Invalid database objects need to be recompiled after the upgrade.
Sample log file : Second run
Report generated by AutoUpgrade 22.4.220712 (#161fde38) on 2022-08-12 15:59:29
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: rg
Container Name: rg
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717
Compatible: 11.2.0.3
Blocksize: 8192
Platform: HP-UX IA (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
Oracle Java Packages [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle Workspace Manager [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
*
* ALL Components in This Database Registry:
*
Component Current Current Original Previous Component
CID Version Status Version Version Schema
---------- ----------- ----------- ----------- ----------- ------------
CATALOG 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATJAVA 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATPROC 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
JAVAVM 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
OWM 12.2.0.1.0 VALID 11.2.0.3.0 WMSYS
XDB 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 XDB
XML 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Review and remove any unnecessary HIDDEN/UNDERSCORE
parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
_aggregation_optimization_settings
_always_anti_join
_always_semi_join
_and_pruning_enabled
_b_tree_bitmap_plans
_bloom_filter_enabled
_bloom_folding_enabled
_bloom_pruning_enabled
_complex_view_merging
_compression_compatibility
_connect_by_use_union_all
_convert_set_to_join
_cost_equality_semi_join
_cpu_to_io
_cursor_obsolete_threshold
_dimension_skip_null
_disable_streams_pool_auto_tuning
_disk_sector_size_override
_eliminate_common_subexpr
_enable_type_dep_selectivity
_fast_full_scan_enabled
_first_k_rows_dynamic_proration
_gby_hash_aggregation_enabled
_generalized_pruning_enabled
_globalindex_pnum_filter_enabled
_gs_anti_semi_join_allowed
_improved_outerjoin_card
_improved_row_length_enabled
_index_join_enabled
_kghdsidx_count
_ksb_restart_policy_times
_left_nested_loops_random
_lm_share_lock_opt
_local_communication_costing_enabled
_minimal_stats_aggregation
_mmv_query_rewrite_enabled
_new_initial_join_orders
_new_sort_cost_estimate
_nlj_batching_enabled
_optim_adjust_for_part_skews
_optim_enhance_nnull_detection
_optim_new_default_join_sel
_optim_peek_user_binds
_optimizer_adaptive_cursor_sharing
_optimizer_better_inlist_costing
_optimizer_cbqt_no_size_restriction
_optimizer_coalesce_subqueries
_optimizer_complex_pred_selectivity
_optimizer_compute_index_stats
_optimizer_connect_by_combine_sw
_optimizer_connect_by_cost_based
_optimizer_connect_by_elim_dups
_optimizer_correct_sq_selectivity
_optimizer_cost_based_transformation
_optimizer_cost_hjsmj_multimatch
_optimizer_cost_model
_optimizer_dim_subq_join_sel
_optimizer_distinct_agg_transform
_optimizer_distinct_elimination
_optimizer_distinct_placement
_optimizer_eliminate_filtering_join
_optimizer_enable_density_improvements
_optimizer_enable_extended_stats
_optimizer_enable_table_lookup_by_nl
_optimizer_enhanced_filter_push
_optimizer_extend_jppd_view_types
_optimizer_extended_cursor_sharing
_optimizer_extended_cursor_sharing_rel
_optimizer_extended_stats_usage_control
_optimizer_false_filter_pred_pullup
_optimizer_fast_access_pred_analysis
_optimizer_fast_pred_transitivity
_optimizer_filter_pred_pullup
_optimizer_fkr_index_cost_bias
_optimizer_full_outer_join_to_outer
_optimizer_group_by_placement
_optimizer_improve_selectivity
_optimizer_interleave_jppd
_optimizer_join_elimination_enabled
_optimizer_join_factorization
_optimizer_join_order_control
_optimizer_join_sel_sanity_check
_optimizer_max_permutations
_optimizer_mode_force
_optimizer_multi_level_push_pred
_optimizer_native_full_outer_join
_optimizer_new_join_card_computation
_optimizer_null_aware_antijoin
_optimizer_or_expansion
_optimizer_order_by_elimination_enabled
_optimizer_outer_join_to_inner
_optimizer_outer_to_anti_enabled
_optimizer_push_down_distinct
_optimizer_push_pred_cost_based
_optimizer_rownum_bind_default
_optimizer_rownum_pred_based_fkr
_optimizer_skip_scan_enabled
_optimizer_sortmerge_join_inequality
_optimizer_squ_bottomup
_optimizer_star_tran_in_with_clause
_optimizer_system_stats_usage
_optimizer_table_expansion
_optimizer_transitivity_retain
_optimizer_try_st_before_jppd
_optimizer_undo_cost_change
_optimizer_unnest_corr_set_subq
_optimizer_unnest_disjunctive_subq
_optimizer_use_cbqt_star_transformation
_optimizer_use_feedback
_or_expand_nvl_predicate
_ordered_nested_loop
_parallel_broadcast_enabled
_partition_view_enabled
_pivot_implementation_method
_pre_rewrite_push_pred
_pred_move_around
_push_join_predicate
_push_join_union_view
_push_join_union_view2
_px_minus_intersect
_px_partition_scan_enabled
_px_pwg_enabled
_px_ual_serial_input
_query_rewrite_setopgrw_enable
_remove_aggr_subquery
_replace_virtual_columns
_resource_manager_plan
_right_outer_hash_enable
_selfjoin_mv_duplicates
_smm_max_size
_smm_min_size
_smm_px_max_size
_sql_model_unfold_forloops
_sqltune_category_parsed
_subquery_pruning_enabled
_subquery_pruning_mv_enabled
_table_scan_cost_plus_one
_union_rewrite_for_gs
_unnest_subquery
_upgrade_capture_noops
_upgrade_optim
_use_column_stats_for_function
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
INFORMATION ONLY
================
2. (AUTOFIXUP) 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.
Parameter
---------
local_listener=remove
cluster_database=FALSE
remote_login_passwordfile=NONE
core_dump_dest=remove
Mandatory changes are required to perform the upgrade. These changes are
implemented in the during_ and after_upgrade_pfile_dbname.ora files.
3. 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.
4. Here are ALL the components in this database registry:
Component Current Current Original Previous Component
CID Version Status Version Version Schema
--------- ----------- ----------- ----------- ----------- -----------
CATALOG 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATJAVA 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
CATPROC 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
JAVAVM 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
OWM 12.2.0.1.0 VALID 11.2.0.3.0 WMSYS
XDB 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 XDB
XML 12.2.0.1.0 VALID 11.1.0.7.0 11.2.0.3.0 SYS
Review the information before upgrading.
5. Here is a count of invalid objects by Oracle-maintained users:
Oracle-Maintained User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
6. Here is a count of invalid objects by Application users:
Application User Name Number of INVALID Objects
--------------------------- -------------------------
None None
Review the information before upgrading.
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
7. (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.
8. (AUTOFIXUP) 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. 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 7 user directory objects to be checked: DPUMP_DIR, EXPDP_DIR,
EXP_ILM, IMPDP_DIR, PREUPGRADE_DIR, TOAD_BDUMP_DIR, VMA.
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.
10. (AUTOFIXUP) Recompile the objects with timestamp mismatch. Please refer
to MOS note 781959.1 for more details.
There are objects whose timestamp are mismatched with its parent objects.
Timestamp of dependent objects must coincide with the timestamp of parent
objects.
11. (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.
12. 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;
Oracle recommends gathering fixed object statistics after upgrade. 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.
13. (AUTOFIXUP) Run @?/rdbms/admin/utlrp.sql in order to recompile any
invalid objects.
There are invalid objects in the database after the upgrade.
Invalid database objects need to be recompiled after the upgrade.
>>>You can see the difference in log file .
NOW we can star the upgrade
Step 3 :
/app/oracle/product/19.0.0.0/db/jdk/bin/java -jar /exports/upgrade/autoupgrade.jar -config /exports/upgrade/configfile/upgrade19c.cfg -mode deploy
you can use commands like lsj to monitor the progress.
>>Few command that can be helpful are
Command Action
------------------------ -----------------------------------
lsj List the running jobs
status -job (job number) Status of specific job
abort -job (job number) Aborts a specific job
resume -job (job number) Resume a specific job
Restore -job (job number) restores a database from GRP
i will update this blog for upgrading RAC, datguard. and pdb to cdb soon .
thanks
Comments
Post a Comment