This is a simple guide that you can follow to upgrade a database using autoupgrade.jar. The official Oracle documentation has all the details you need, but in this guide, I want to mention a couple of changes I did for my specific use-case
- The database I wanted to upgrade was running on NOARCHIVELOG mode
- The ORACLE_HOME I was using used symbolic links in its path
The first thing you need to do is download and copy the latest autoupgrade.jar file to the target version – in my case 19c – oracle home location ($TARGET_HOME/rdbms/admin). The autoupgrade.jar file can be downloaded from the MOS Note: 2485457.1
Next, we need to create a config file, which has all the global parameters as well as the parameters needed for individual databases. Below is an example of the config file I used
cat >/tmp/autoupg.cfg <<EOF #Global global.autoupg_log_dir=/home/oracle/autoupgrade # Database specific - orcl upg1.dbname=orcl upg1.source_home=/u01/app/oracle/product/11204_200414 upg1.target_home=/u01/app/oracle/product/19700_200414 upg1.sid=orcl1 upg1.log_dir=/home/oracle/autoupgrade/orcl upg1.upgrade_node=ordb1 upg1.target_version=19.7 upg1.run_utlrp=yes upg1.timezone_upg=yes ## This is required for databases in NOARCHIVELOG mode, to skip restore point creation upg1.restoration=no ## This script is used to change a couple of parameters before the upgrade, which will enable the 19c database to use directory paths which contain symlinks upg1.before_action=/home/oracle/before_orcl.sh EOF
Once the config file is created, the next thing is to create a shell script which is configured to be executed before the upgrade – /home/oracle/before_orcl.sh
cat >/home/oracle/before_orcl.sh <<EOF export ORACLE_SID=orcl1 ORAENV_ASK=NO . oraenv ORAENV_ASK="" sqlplus / as sysdba <<EOSQL ALTER SYSTEM SET "_disable_directory_link_check"=TRUE SCOPE=SPFILE; ALTER SYSTEM SET "_kolfuseslf"=TRUE SCOPE=SPFILE; exit; EOSQL EOF
Apart from the usual parameters, I have added a couple of things to the cfg file. Let me explain each one of them
upg1.restoration=no
If the database is in NOARCHIVELOG, the auto-upgrade script will fail even before the upgrade, because the tool actually tries to create a guaranteed restore point, which will fail since there is no archive logs. To tell the script to no create the restore point, we use the parameter restoration=no. be aware that this means there will be no fallback plan if the upgrade fails
upg1.before_action
Another thing we need to make sure is the two parameters which are needed to make the database allow symbolic links in directory path need to be set if your TARGET_HOME has symbolic links in it. This is because during the upgrade, after the data dictionary upgrade, the tool runs datapatch, which will apply the latest patchset available. The datapatch uses three directories for its operation, which is always inside the ORACLE_HOME being used. If the ORACLE_HOME has symbolic links, these directories will also have symbolic links in it. Hence we use the “before” script to set those parameters before upgrade itself. If this is not done, you will see error message similar to the one below in the upgrade log files
Queryable inventory could not determine the current opatch status. Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual' and/or check the invocation log /u01/oracle/********/****/cfgtoollogs/sqlpatch/sqlpatch_****/sqlpatch_invocation.log for the complete error. Prereq check failed, exiting without installing any patches. Please refer to MOS Note 1609718.1 and/or the invocation log /u01/oracle/********/****/cfgtoollogs/sqlpatch/sqlpatch_****/sqlpatch_invocation.log for information on how to resolve the above errors.
You will also see the below SQL output in the database
SQL> select dbms_sqlpatch.verify_queryable_inventory from dual; VERIFY_QUERYABLE_INVENTORY -------------------------------------------------------------------------------- ORA-20013: DBMS_QOPATCH ran mostly in non install area
Once the config file and before_orcl.sh are created, we can start the autoupgrade.jar execution in analyze mode using java from the 19c ORACLE_HOME
cd $TARGET_HOME/rdbms/admin $TARGET_HOME/jdk/bin/java -jar autoupgrade.jar -config /tmp/autoupg.cfg -mode analyze
you will get to the auto-upgrade console once you enter the above command. You can check the progress of job execution using the command :
lsj
For detailed status of a particular job ,you can use the command
status -job <JOBID>
Once the analyze mode is completed, it will display the result. You can then start the database upgrade in deploy mode
cd $TARGET_HOME/rdbms/admin $TARGET_HOME/jdk/bin/java -jar autoupgrade.jar -config /tmp/autoupg.cfg -mode deploy
This command will run the pre-upgrade, upgrade and post-upgrade tasks and display the progress in the console using lsj command, and also in the log files which are saved in the <JOBID> directory under the log directory specified in the config file
Also if you would prefer to disconnect from the console after starting the upgrade job, use the option
-noconsole
Finally, if there is any failure in the upgrade job and you want to restart the upgrade, rename the log directory to something different and then you will be able to start over.
Here is a nice little video by which helped me to get familiar with the actual process of upgrading a database using this tool