Oracle Database upgrade using Autoupgrade
There are several methods available to upgrade or migrate an Oracle database to the latest version, including:
-Database Upgrade Assistant (DBUA)
-Command-line upgrade
-Transportable Tablespaces (TTS) export/import
-Oracle Data Pump Export/Import
-Original Export/Import utilities
-Manual Upgrade Overview
A manual upgrade typically involves the following steps:
-Check for invalid objects
-Start the database in upgrade mode
-Run utlrp.sql
-Upgrade the time zone
Oracle AutoUpgrade
To avoid the complexities of manual upgrades, I would like to share information about the Oracle AutoUpgrade tool. Oracle AutoUpgrade is the primary command-line tool for upgrading Oracle databases, supporting both direct upgrades and migrations. This utility enables database upgrades with minimal manual intervention, automating approximately 99% of the required tasks.
-Identifies and fixes issues before the upgrade
-Performs the database upgrade automatically
-Supports multiple database types (Standalone, Oracle One Node RAC, RAC)
-AutoUpgrade determines the database type and performs the appropriate upgrade process. It upgrades the local Oracle Database home binaries to the new release binaries.
-AutoUpgrade operates in four processing modes:
-Analyze
-Fixup
-Deploy
-Upgrade
-The AutoUpgrade configuration file contains all required information for the upgrade. Both global and database-specific parameters can be defined. A sample configuration file can be generated using autoupgrade.jar.
Example:
global.autoupg_log_dir=
upt1.sid=
upt1.source_home=
upt1.target_home=
AutoUpgrade manages internally:
-Automatically starts and stops databases and RAC instances as needed
-Recreates the SPFILE with updated parameters
-Does not require agents, Oracle Enterprise Manager, or additional licenses
-Supports batch upgrades of multiple databases
-Performs prechecks, post-upgrade tasks, and time zone upgrades
-The database must run on Linux or Unix systems.
Key Features:
A] Availability and requirements
- Prerequisites
- AutoUpgrade Supported modes
-Analyze
-Fixup
-Deploy mode
- Configuration file creation
- Database analysis
- Database upgrade
- Pre-upgrade report review
B] Monitoring
- Command-line monitoring
- Single-line command execution
C] Upgrade Scenarios
- Single database upgrade
- Multiple database upgrades
- PDB upgrades
- Upgrades across multiple hosts
D] Configuration and Management
- Add or remove database parameters
- Execute .sh script
- Restore point management
- Manage underscore parameters
- Run utlrp.sql
- Perform time zone upgrades
E] Performance and Reliability
- Parallel performance optimization
- Error handling
- Data Guard support
F] Post-Upgrade
- Post-upgrade tasks
G] Advanced Handling
- MAA approach
- Upgrade databases on new servers
- Resumable AutoUpgrade
- Fix-up failure handling
H] Other
- Oracle Support
- Limitations
A] Availability and requirements
1. Prerequisites:
-Latest versions are available via Oracle Metalink node KB790461.
-From 19.3 onwards, AutoUpgrade is included with Oracle binaries.
-Java version 8 or higher is required.
- check Java version:
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -version
-Oracle Grid Infrastructure must be upgraded (for RAC)
-Database must be in ARCHIVELOG mode
-FRA must be configured with sufficient space
-A consistent backup and restore point must be created
-Database must be managed using SRVCTL
-Source database must be patched to the latest PSU
-Oracle software must be installed
2. AutoUpgrade supported modes:
Analyze Mode
- Setup
- Prechecks
Fixup Mode
- Setup
- Prechecks
- Prefixups
Deploy Mode
- Setup
- Pre-upgrade
- Prechecks
- Guaranteed Restore Point (GRP)
- Prefixups
- Drain
- DB Upgrade
3. Configuration file creation:
Generate a sample configuration file using AutoUpgrade:
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -create_sample_file config
Or create the configuration file manually, for example:
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/12.2.0.2/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
4. Database analysis:
Run the analysis mode:
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config db_config.cfg -mode analyze
5. Database upgrade:
Run the deploy mode:
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config db_config.cfg -mode deploy
6. Pre-upgrade report review:
Reports are generated in HTML, TEXT, and JSON formats.
B] Monitoring
1. Command-line monitoring:
lsj – List jobs
status – Show job status
tasks – Show tasks currently executing
resume -job – Restart a job
status -job – Show status of a specific job
restore -job – Restore the database from GRP
abort -job – Abort the specified job
2. Browser-based monitoring:
Start the Python SimpleHTTPServer:
python -m SimpleHTTPServer 8888
Open the browser and access:
http://:8000/state.html
C] Upgrade Scenarios
1. Single database upgrade:
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/12.2.0.2/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
2. AutoUpgrade single-line command execution:
AutoUpgrade can be executed using a single-line command by specifying the required configuration values.
$ORACLE_HOME/jdk/bin/Java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar –config_values “upt1.sid=orcl1,
upt1.source_home=/u01/app/oracle/product/12.2.0.2/db_1,
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1” –mode analyze
3. Multiple databases upgrade:
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt2.sid=orcl2
upt2.source_home=/u01/app/oracle/product/12.1.0.2/db_1
upt2.target_home=/u01/app/oracle/product/19.0.0/db_1
4. PDB upgrade:
PDBs can be upgraded using the unplug-and-plug method. They can be unplugged from Oracle 12c and plugged into a new Oracle 19c home.
vi db_config.cfg
global.autoupg_log_dir=/home/oracle/autoupg
upt1.sid=CDB1
upt1.pdbs=PDB1
upt1.source_home=/u01/app/oracle/product/12.2.0/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.target_cdb=CDB2
upt1.log_dir=/home/oracle/autoupg
upt1.target_pdb_name.PDB1=DEPT
upt1.target_pdb_copy_option.PDB1=file_name_convert=('PDB1','DEPT')
Run the AutoUpgrade analyze step.
$ORACLE_HOME/jdk/bin/Java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar –config db_config.cfg –mode analyze
Run the AutoUpgrade deploy step.
$ORACLE_HOME/jdk/bin/Java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar –config db_config.cfg –mode deploy
5. Upgrades across multiple hosts:
The database upgrade will proceed only when AutoUpgrade is executed on the matching hostname.
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.upgrade_node=pserver1.group.net
upt2.sid=orcl2
upt2.source_home=/u01/app/oracle/product/12.1.0.2/db_1
upt2.target_home=/u01/app/oracle/product/19.0.0/db_1
upt2.upgrade_node=pserver2.group.net
D] Configuration and Management
1. Add or remove database parameters:
Oracle database parameters can be added or removed using AutoUpgrade, either during or after the upgrade process.
vi db_config.cfg
global.del_during_upgrade_pfile=global_delete.ora
global.add_during_upgrade_pfile=global_add.ora
global.del_after_upgrade_pfile=global_delete_after.ora
global.add_after_upgrade_pfile=global_add_after.ora
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.add_after_upgrade_pfile=upt1_add.ora
upt2.sid=orcl2
upt2.source_home=/u01/app/oracle/product/12.1.0.2/db_1
upt2.target_home=/u01/app/oracle/product/19.0.0/db_1
upt2. add_after_upgrade_pfile = upt2_add.ora
2. Execute .sh script:
Execute the script either before or after the upgrade. In both cases, the .sh scripts can be scheduled using the AutoUpgrade configuration file.
vi db_config.cfg
global.before_action=rmanbackup.sh
global.after_action=rmanbackup.sh
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.before_action=rmanbackup.sh
upt2.sid=orcl2
upt2.source_home=/u01/app/oracle/product/12.1.0.2/db_1
upt2.target_home=/u01/app/oracle/product/19.0.0/db_1
upt2.after_action= rmanbackup.sh
3. Restore points management:
AutoUpgrade creates a Guaranteed Restore Point (GRP) by default to support rollback. This default behavior can be modified by setting the appropriate parameters in the configuration file.For Oracle Standard Edition, AutoUpgrade does not create a GRP. In this case, the GRP must be dropped manually after a successful upgrade.
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.restoration=no
upt2.sid=orcl2
upt2.source_home=/u01/app/oracle/product/12.1.0.2/db_1
upt2.target_home=/u01/app/oracle/product/19.0.0/db_1
upt2.drop_grp_after_upgrade=yes
4. Manage underscore parameters:
By default, AutoUpgrade does not remove underscore parameters or events. This behavior can be modified by setting the remove_underscore_parameter option in the configuration file.
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.remove_underscore_parameter=yes
5. utlrp.sql management:
By default, AutoUpgrade executes utlrp.sql to recompile invalid objects. This behavior can be changed by setting the run_utlrp parameter in the configuration file.
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.run_utlrp=no
6. Perform time zone upgrades:
By default, AutoUpgrade upgrades the database time zone automatically after the database upgrade. This behavior can be changed by setting the timezone_upg parameter in the configuration file. During the time zone upgrade, the database may be restarted multiple times. Once the time zone has been upgraded, it cannot be downgraded as a fallback option.
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.timezone_upg=no
E. Performance and Reliability
1. Parallel performance optimization:
AutoUpgrade performance can be improved by increasing the number of parallel processes.By default, AutoUpgrade uses the value of the cpu_count parameter to determine the total number of parallel processes. This behavior can be modified by setting the catctl_options parameter in the configuration file:
-n specifies the maximum number of parallel processes.
-N specifies the maximum number of parallel processes per PDB.
vi db_config.cfg
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.catctl_options=-n 64 –N 8
2. Error handling:
When AutoUpgrade encounters an error, it displays an error code. Some error codes are straightforward, while others can be more complex and may require reviewing the log files for additional details.
$ORACLE_HOME/jdk/bin/Java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -error_code
3. Data Guard support:
AutoUpgrade automatically detects the Data Guard (DG) setup and upgrades the standby database accordingly. It supports both manual and broker-managed Data Guard environments.
Before starting the upgrade:
-Take a backup of the Data Guard broker configuration files.
-Stop redo transport and apply from the primary to the standby database.
-Stop and disable the standby database.
-After upgrading, enable the standby database, restart DG, and re-enable redo transport.
F] Post-Upgrade
1. Post-upgrade tasks:
-Copy all .ora files from the old Oracle home to the new Oracle home (for example, tnsnames.ora and sqlnet.ora).
-Copy the password file and pfile/spfile.ora from the old Oracle home to the new Oracle home.
-Restore the Data Guard configuration file from backup to the new Oracle home.
-Update the database configuration using srvctl.
G] Advanced Handling
1. MAA approach:
-Keep the standby database online throughout the upgrade.
-Before starting the upgrade, start the standby database using the new Oracle home.
-Ensure redo transport and redo apply remain enabled during the upgrade.
2. Upgrade databases on new servers:
To upgrade the database on a different server using AutoUpgrade, follow the steps below.
Source Server
Run AutoUpgrade in analyze mode:
-mode analyze
Run AutoUpgrade fixups:
-mode fixups
Update /etc/oratab.
Prepare the ?/network/admin files.
Shut down the database:
SHUTDOWN IMMEDIATE
Copy the database and configuration files to the target server.
Configuration
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
Target Server
Start the database in upgrade mode:
STARTUP UPGRADE
Update configuration values as required:
upt1.sid=orcl1
upt1.source_home=/tmp
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
Run AutoUpgrade in upgrade mode:
-mode upgrade
3. Resumable AutoUpgrade:
AutoUpgrade is fully resumable. If a session is lost, AutoUpgrade jobs can be restarted from the command line. A job resumes from the point where it failed, stopped, or was killed. To avoid session-related issues, it is recommended to run AutoUpgrade using nohup.
nohup $ORACLE_HOME/jdk/bin/java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar –config test.cfg –mode deploy –noconsole &
4. Fix-up failure handling:
If a fixup fails during the analyze step, it can be bypassed. AutoUpgrade generates the checklist.cfg file in the prechecks folder. This file must be edited to disable the fixup. Update the relevant check entry as shown below:
[checkname] DICTIONARY_STATS
[stage] PRECHECKS
[fixup_available] YES
[runfix] NO
[severity] RECOMMEND
Set [runfix] to NO or SKIP to prevent the check and fixup from running.
vi db_config.cfg.
upt1.sid=orcl1
upt1.source_home=/u01/app/oracle/product/11.2.0.4/db_1
upt1.target_home=/u01/app/oracle/product/19.0.0/db_1
upt1.checklist=../prechecks/_checklist.cfg
Start the deploy mode.
$ORACLE_HOME/jdk/bin/Java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar –config test.cfg –mode deploy
5. DB post-upgrade tasks:
Once the upgrade is successful and all testing is completed:
-Drop the Restore Point
-Verify existing restore points:
select name from v$restore_point;
-Drop the required restore point:
drop restore point ;
Validate the Database
-Ensure the database is thoroughly tested before updating the compatible parameter.
Note: After updating the compatible parameter, the database cannot be downgraded.
Update the Compatible Parameter
alter system set compatible='19.0.0' scope=spfile;
Restart the Database
shutdown immediate;
startup;
Confirm the Setting
show parameter compatible;
H] Other
1. Oracle Support:
-If you encounter any issues during the AutoUpgrade process and require Oracle Support assistance:
Zip the entire global log folder.
Upload the zip file to the Oracle Support Service Request (SR).
$ORACLE_HOME/jdk/bin/Java –jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar –config db_config.cfg –zip
Additionally, upload the database alert.log.
2. Limitations:
-The AutoUpgrade tool cannot be used to upgrade Grid Infrastructure.
-Oracle Database AutoUpgrade does not support Oracle Express Edition.
-Microsoft Windows systems are not supported.
-Supported database versions: Oracle 11.2.0.4 and later.
