Over the years, I have done many migrations and many upgrades to Oracle databases. Throughout the versions, that methodology has varied slightly and in all cases has been painstakingly manual until now. Oracle’s “autoupgrade.jar” changes all of that.

“autoupgrade.jar” takes all of the following tasks you used to do manually and automates most of it:

• prechecks
• prefixups
• upgrade
• migration from non-cdb to cdb
• postfixups

Periodically, Oracle will make updates and changes to the tool. The latest version and notes for autoupgrade can be obtained from this note: 2485457.1 – AutoUpgrade Tool. Prior to your own testing, ensure that you have reviewed this note and have downloaded the latest version. Also Mike Dietrich has an entire blog dedicated to upgrades and the tool itself.

https://mikedietrichde.com/2019/04/29/the-new-autoupgrade-utility-in-oracle-19c/

Creating a configuration file:

The basis of this tool is a configuration file where parameters are contained that describe the “source” and the “target” information. You can generate a sample configuration file as a starting point by issuing the following command:

java -jar ./autoupgrade.jar -create_sample_file config

A full explanation of all the parameters allowable within a configuration file are detailed in within the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/autoupgrade-utility-configuration-files.html#GUID-6AF95954-DAD4-47E2-9318-5EB35631F83D

For example, if you are upgrading a non-cdb to non-cdb, the contents may contain the following parameters:

upg1.dbname=testdb
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=testdb1
upg1.log_dir=/home/oracle/upg_logs/testdb1
upg1.upgrade_node=orcl-vbox2
upg1.target_version=19
upg1.run_utlrp=yes
upg1.timezone_upg=yes

In cases where you are upgrading from non-cdb to cdb, there are a few additional parameters:

upg1.dbname=testdb
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=testdb1
upg1.log_dir=/home/oracle/upg_logs/testdb1
upg1.upgrade_node=orcl-vbox2
upg1.target_version=19
upg1.run_utlrp=yes
upg1.timezone_upg=yes
upg1.target_cdb=vboxdb1
upg1.target_pdb_copy_option=file_name_convert=('/u02/oradata/TESTDB/datafile/o1_mf_example_hrfz1tct_.dbf','+DATA','/u02/oradata/TESTDB/datafile/o1_mf_sysaux_hrfyw505_.dbf','+DATA','/u02/oradata/TESTDB/datafile/o1_mf_system_hrfyw480_.dbf','+DATA','/u02/oradata/TESTDB/datafile/o1_mf_temp_hrfz1omf_.tmp','+DATA','/u02/oradata/TESTDB/datafile/o1_mf_undotbs1_hrfyw5f9_.dbf','+DATA','/u02/oradata/TESTDB/datafile/o1_mf_users_hrfyw5hh_.dbf','+DATA')
upg1.target_pdb_name=testdb

Notice that when also converting to cdb, at a minimum the following parameters are required:

• target_cdb
• target_pdb_name
• target_pdb_copy_option (optional, if not specified, but the previous two parameters are, then the PDB is created with the NOCOPY option)

One thing to keep in mind that when you utilize the “target_pdb_copy_option=file_name_convert” parameter, you must list every datafile much like when cloning a database. Unfortunately, at the time of this writing, there is no option such as “SET NEWNAME FOR DATABASE” like exists within the RMAN DUPLICATE process.

To help facilitate populating this parameter, this SQL may help:

SELECT
    LISTAGG(
        chr(39)||file_name||chr(39)||','||chr(39)||'+DATA'||chr(39)||','
    ) WITHIN GROUP(
    ORDER BY
        file_id
    ) AS file_name_convert
FROM
    dba_data_files
GROUP BY
    file_name
union
SELECT
    LISTAGG(
        chr(39)||file_name||chr(39)||','||chr(39)||'+DATA'||chr(39)||','
    ) WITHIN GROUP(
    ORDER BY
        file_id
    ) AS file_name_convert
FROM
    dba_temp_files
GROUP BY
    file_name;

FILE_NAME_CONVERT
‘/u02/oradata/TESTDB/datafile/o1_mf_example_hrfz1tct_.dbf’,’+DATA’,
‘/u02/oradata/TESTDB/datafile/o1_mf_sysaux_hrfyw505_.dbf’,’+DATA’,
‘/u02/oradata/TESTDB/datafile/o1_mf_system_hrfyw480_.dbf’,’+DATA’,
‘/u02/oradata/TESTDB/datafile/o1_mf_temp_hrfz1omf_.tmp’,’+DATA’,
‘/u02/oradata/TESTDB/datafile/o1_mf_undotbs1_hrfyw5f9_.dbf’,’+DATA’,
‘/u02/oradata/TESTDB/datafile/o1_mf_users_hrfyw5hh_.dbf’,’+DATA’,

With this output you can plug it into the parameter “target_pdb_copy_option” as shown within the sample configuration file.

*** Update on 10/15/2020
Shortly after posting this post, I was made aware that the option also exists to leverage the target database “db_create_file_dest” parameter to also perform the conversion to the correct OMF location. If you wish to use this methodology instead of listing every file in the “target_pdb_copy_option=file_name_convert=” parameter, instead populate this parameter as follows:

target_pdb_copy_option=file_name_convert=NONE

Executing pre-checks:

Now that the configuration file is ready to be used, you can execute the pre-checks and then either decide to fix them yourself or allow the tool to fix them for you. In either case, you should always re-execute the pre-checks after fixing them to ensure that everything has been addressed. To begin the pre-check process, execute the following command:

java -jar autoupgrade.jar -config testdb_autoupgrade_config.cfg -mode analyze

This will take the source database and create a log file showing all of the items that need to be fixed and whether or not the tool can fix them automatically or if they need to be fixed manually. The log file is created in the log directory specified in the “log_dir” parameter within the configuration file and then within the directory that corresponds to the task number of the “analyze” task.

Executing fixups:

Once the pre-checks are completed, the script can then be executed to attempt to fix things that it can fix. You can then execute the pre-checks one more time to ensure that all issues have been addressed. To execute the fixups, execute the following command:

java -jar autoupgrade.jar -config testdb_autoupgrade_config.cfg -mode fixups

Deploying the upgrade:

Once all of the fixups are completed, you are ready to upgrade. The upgrade is quite easy using the tool:

java -jar autoupgrade.jar -config testdb_autoupgrade_config.cfg -mode deploy

Finalizing the upgrade:

As with any upgrade, paying attention to the log files is very important. That being said, a few items that I noticed when testing on my test server:

• oratab is not modified if you are migrating from non-cdb to cdb
• clusterware is not updated if you are migrating from non-cdb to cdb
• if migrating from non-cdb to cdb, an existing cdb database must be specified or one must be pre-created
• be sure to check for any Guaranteed Restore Points that might be created
• check for the proper setting of the ‘compatible’ parameter and advance it at the appropriate time

Also, the tool does so much more, and this is just the tip of the iceberg. I plan to investigate more of these options such as automated patching and fleet patching in subsequent posts.