I try to catch up with missing AutoUpgrade posts. And I should have written this one a while earlier ideally. Just yesterday a colleague asked me about it. So it’s time now to describe how to use Oracle AutoUpgrade between two servers.

Photo by Alex Motoc on Unsplash
Documentation
The Oracle Database 19c Upgrade Guide has this section about the scenario:
And you see that it consists of 5 steps:
- Run the -mode analyze step on source server
- Run the -mode fixups step on source server
- Shutdown and restore your database to the target server (not done by AutoUpgrade)
- Start your database in STARTUP UPGRADE mode on target server
- Run the -mode upgrade step on target server
To avoid failing at some point, I’d like to give you step-by-step instructions here.
My Setup
I use two virtual machines, an OL6.10 one with Oracle 12.1.0.2, and my OL7.6 one with Oracle 19.8.0. I will do the required steps on the 12.1.0.2 database without presence of Oracle 19c (it is not supported on OL6), then shutdown my database and copy it to the new server.
If I’d be interested in minimal downtime for this solution, I’d install Oracle 12.1.0.2 software on my OL7 machine, and setup a physical standby database. But as this blog post is not about Minimal Downtime but instead about how to use AutoUpgrade between two servers, I won’t cover the standby option here.
Preparation
At first, I’m downloading the most recent AutoUpgrade tool to my source machine:
In addition, I need to prepare a config file:
global.autoupg_log_dir=/home/oracle/upg_logs # # Database number 1 # upg1.dbname=DB12 upg1.start_time=NOW upg1.source_home=/u01/app/oracle/product/12.1.0.2 upg1.target_home=/u01/app/oracle/product/19 upg1.sid=DB12 upg1.log_dir=/home/oracle/upg_logs upg1.upgrade_node=localhost upg1.target_version=19 upg1.restoration=no
You may recognize that I specify a target_home which does not exist on this server. But this will be ignored.
Analyze Mode
To start my upgrade, I need to execute AutoUpgrade on the source server with -mode analyze. Neither my OL6 nor my 12.1.0.2 java versions are good enough to work here – but I have an 18c Oracle Home as well. Hence, I will utilize the JDK installed with 18c. At worst case, you may need to refresh the Java version in your current home.
$ /u01/app/oracle/product/18/jdk/bin/java -jar /u01/app/oracle/product/12.1.0.2/rdbms/admin/autoupgradeOL6.jar -config DB12.cfg -mode analyze AutoUpgrade tool launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be analyzed Type 'help' to list console commands upg> Job 100 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished successfully [1] Jobs failed [0] Jobs pending [0] ------------- JOBS FINISHED SUCCESSFULLY ------------- Job 100 for DB12
I show a part of the output from the analyze run of the db12_preupgrade.html report file written into the logging ./prechecks subdirectory.
Everything can be either fixed automatically or safely ignored.
Fixups Mode
As next step I’m executing the -mode fixups phase. This will do changes to my source database now. Hence, I’d recommend that you do this as closely as possible to the downtime window your acquired for the upgrade to the new server.
$ /u01/app/oracle/product/18/jdk/bin/java -jar /u01/app/oracle/product/12.1.0.2/rdbms/admin/autoupgradeOL6.jar -config DB12.cfg -mode fixups AutoUpgrade tool launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> lsj +----+-------+---------+---------+-------+--------------+--------+-------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+---------+---------+-------+--------------+--------+-------------+ | 101| DB12|PREFIXUPS|EXECUTING|RUNNING|20/07/31 12:13|12:14:08|Remaining 4/4| +----+-------+---------+---------+-------+--------------+--------+-------------+ Total jobs 1 upg> Job 101 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished successfully [1] Jobs failed [0] Jobs pending [0] ------------- JOBS FINISHED SUCCESSFULLY ------------- Job 101 for DB12
As you may recognize, it created a new job 101 – and of course a new directory tree for this job under the log directory you defined.
In the ./prefixups subdirectory you will find the prefixups_db12.log. It lists all the actions being done here by AutoUpgrade. In my particular case these are:
BEGIN SYS.DBMS_STATS.GATHER_INDEX_STATS('SYS', 'I_OBJ#'); SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SYS'); SYS.DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM'); END; / BEGIN SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END; / PURGE DBA_RECYCLEBIN /
That’s it.
Move the database
Now I can shutdown my database and copy it to the new server. In my case, I use the most simple way. I copy all files including redologs, controlfiles – plus the SPFILE and the password file to the new machine. I keep the exact same directory structure.
In addition, I prepare also my environment file and add an entry to /etc/oratab.
Adjust the config file
A very important step I need to do is adjusting the config file. As there is no source home on the target server, I need to do a tweak. The source_home will point to a random directory, such as /tmp.
global.autoupg_log_dir=/home/oracle/logs upg1.dbname=DB12 upg1.start_time=NOW upg1.source_home=/tmp upg1.target_home=/u01/app/oracle/product/19 upg1.sid=DB12 upg1.log_dir=/home/oracle/logs upg1.upgrade_node=localhost upg1.target_version=19 upg1.restoration=no
In a later version of AutoUpgrade this may not be necessary anymore.
Start in STARTUP UPGRADE
Next step for me is starting the 12.1.0.2 database in STARTUP UPGRADE mode:
$ . db19 [DB12] oracle@hol:/u02/oradata/DB12 $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 31 14:10:10 2020 Version 19.8.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 1048575776 bytes Fixed Size 8904480 bytes Variable Size 268435456 bytes Database Buffers 763363328 bytes Redo Buffers 7872512 bytes Database mounted. Database opened.
Upgrade Mode
You may ask yourself: Why am I not running (as usual) the -mode deploy at this stage but instead -mode upgrade? The answer is simple. We don’t have a source home on the target server. Hence, we don’t need a Guaranteed Restore Point – and we wouldn’t even be able to write it. Furthermore, the deploy mode combines analyze, fixups and upgrade with some extras. But we have done the fixups already.
$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config DB12.cfg -mode upgrade AutoUpgrade tool launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> lsj +----+-------+---------+---------+-------+--------------+--------+------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+---------+---------+-------+--------------+--------+------------+ | 100| DB12|DBUPGRADE|EXECUTING|RUNNING|20/07/31 14:16|14:19:51|10%Upgraded | +----+-------+---------+---------+-------+--------------+--------+------------+ Total jobs 1
Here we go.
upg> Job 100 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished successfully [1] Jobs failed [0] Jobs pending [0] ------------- JOBS FINISHED SUCCESSFULLY ------------- Job 100 for DB12
And here is the upgrade summary:
Start of Summary Report ------------------------------------------------------ Oracle Database Release 19 Post-Upgrade Status Tool 07-31-2020 14:38:0 Database Name: DB12 Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 19.8.0.0.0 00:12:31 Oracle Workspace Manager UPGRADED 19.8.0.0.0 00:00:29 Oracle Real Application Clusters OPTION OFF 19.8.0.0.0 00:00:00 Oracle XML Database UPGRADED 19.8.0.0.0 00:01:39 Datapatch 00:02:46 Final Actions 00:03:25 Post Upgrade 00:00:21 Total Upgrade Time: 00:20:09 Database time zone version is 18. It is older than current release time zone version 32. Time zone upgrade is needed using the DBMS_DST package. Grand Total Upgrade Time: [0d:0h:27m:22s]
Flawless!
Can we plugin?
Just out of curiosity, I wanted to know if the plugin operation works in this case as well.
But this is something I will cover in an additional blog post:
Further Links and Information
- Oracle 19c Upgrade Guide: AutoUpgrade with Source and Target Database Homes on Different Servers
- MOS Note: 2485457.1 – AutoUpgrade Tool
–Mike
Hi Mike
The precheck reported a hidden parameter ‘_fix_control’
but the prefixup did not report it to be removed.
Has the hidden parameter been removed?
Sven-Olaf
No – AU does not remove any hidden parameters unless you advice it to do so in the config file.
Cheers,
Mike
Hi Mike,
I’d like to test the procedure on a 11.2.0.4.
Since there’s no Java 8 here, is it possible to install it (rpm or tgz) in order to use it with autoupgrade?
In this case JDK SE is required by an Oracle product, so I presume there’s no problem with Java licence. Am I wrong?
Do you have a best practice in these cases?
Thanks,
Andrea
Hi Andrea,
there shouldn’t be any license implication here:
https://mikedietrichde.com/2020/01/31/do-you-need-to-license-java-for-autoupgrade/
Cheers,
Mike
I followed everything to the letter (DB started in UPGRADE mode, latest verson of autoupgrade, checked alert log for errors, I can connect via sqlplus, /etc/oratab updated, etc…) but when I run the upgrade via:
/u01/app/oracle/product/19.0.0/dbome_1jdk/bin/java -jar autoupgrade.jar -config testdb_config -mode upgrade
I get this error:
2020-08-03 10:18:43.930 INFO Loading user config file metadata
2020-08-03 10:18:45.125 ERROR The database bdbprod appears to be down or open with the incorrect binaries for the mode UPGRADE,
ensure it is open with /u01/app/oracle/product/19.0.0/dbhome_1
2020-08-03 10:18:45.133 ERROR The database bdbprod is currently open with a status of CLOSED. For UPGRADE mode, it needs to be open in one of the following: [OPEN_MIGRATE].
Here are the relevant details from the main log (autoupgrade.log):
2020-08-03 10:18:43.999 INFO Copying AutoUpgrade login.sql file to /home/oracle/upg_logs/cfgtoollogs/upgrade/auto/sql/login.sql – LoginFileCreator.process
2020-08-03 10:18:44.009 INFO Processing dbEntry db1 – UpgradeConfigMaker.process
2020-08-03 10:18:44.027 INFO Begin [/u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus, -silent, /, as, sysdba] – ExecuteProcess.startSqlPlusProcess
2020-08-03 10:18:44.027 INFO Begin Setting Oracle Environment – ExecuteProcess.startSqlPlusProcess
2020-08-03 10:18:44.029 INFO [(SQLPATH=/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/sql), (ORACLE_SID=testdb), (ORACLE_PATH=/home/oracle/upg_logs/cfgtoollogs/upgrade/auto/sql), (ORACLE_BASE= ), (TWO_TASK=N/A), (ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1), (TNS_ADMIN=N/A), (LDAP_ADMIN=N/A), (PERL5LIB=N/A)] – ExecutionEnv.addEnvToProcess
2020-08-03 10:18:44.029 INFO Starting – ExecuteProcess.setLibsForSqlplus
2020-08-03 10:18:44.030 INFO Finished – ExecuteProcess.setLibsForSqlplus
2020-08-03 10:18:44.030 INFO End Setting Oracle Environment – ExecuteProcess.startSqlPlusProcess
2020-08-03 10:18:44.030 INFO Begin Creating process – ExecuteProcess.startSqlPlusProcess
2020-08-03 10:18:44.042 INFO End Creating process – ExecuteProcess.startSqlPlusProcess
2020-08-03 10:18:44.043 INFO Executing SQL [SELECT STATUS FROM SYS.V$INSTANCE;] in [testdb, container:null] – ExecuteSql$SQLClient.run
2020-08-03 10:18:45.106 INFO Errors executing [SELECT STATUS FROM SYS.V$INSTANCE;
SELECT STATUS FROM SYS.V$INSTANCE
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
] [testdb] – ExecuteSql$SQLClient.run
2020-08-03 10:18:45.124 ERROR The database testdb appears to be down or open with the incorrect binaries for the mode UPGRADE,
ensure it is open with /u01/app/oracle/product/19.0.0/dbhome_1 – UpgradeConfigDBValidator.initializeIsDBUp
2020-08-03 10:18:45.132 ERROR The database testdb is currently open with a status of CLOSED. For UPGRADE mode, it needs to be open in one of the following: [OPEN_MIGRATE]. – UpgradeConfigDBValidator.initializeIsDBUp
2020-08-03 10:18:45.137 INFO Content of the file /u01/app/oracle/product/19.0.0/dbhome_1/sqlplus/admin/glogin.sql is:
—
— Copyright (c) 1988, 2005, Oracle. All Rights Reserved.
—
— NAME
— glogin.sql
—
— DESCRIPTION
— SQL*Plus global login “site profile” file
—
— Add any SQL*Plus commands here that are to be executed when a
— user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
—
— USAGE
— This script is automatically run
— – GLoginLogger.logGlogin
The weird thing is that I can connect via sqlplus / as sysdba and run “SELECT status from sys.v$instance” without any problem:
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Wed Aug 5 11:18:01 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> select status from sys.v$instance;
STATUS
————————-
OPEN MIGRATE
SQL>
Environment:
ORACLE_SID=TESTDB
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=vt220
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib:/usr/lib
PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin
Can you help?
Yes, I think we can:
https://dohdatabase.com/2020/08/04/upgrade-and-profile-scripts/
Hope this helps – thanks,
Mike
I solved this issue -> I have to ensure that the “sid” specified in the autoupgrade config file is in the same case as the ORACLE_SID environment variable on the target server, which must match what it was on the source databas server.
Thanks Nelson!
Cheers,
Mike
silly question:
Can we do an AutoUpgrade but just take a copy of the source database files and keep the original database intact ?
Not a silly question – but you need to take care of providing the copy of your database. AutoUpgrade will upgrade “in place”.
Thanks,
Mike
if a client wants to move to CDB from a non CDB prod database . they POC they came up with was to do do it without downtime on a remote host (test server) . Do you think it’s doable ?
thanks Mike.
No.
It is a migration and always requires downtime. The only option to avoid the downtime part is to add Oracle GoldenGate on top as the synching solution.
Cheers,
Mike
I am going to do a migration test in the same server (windows) . At which point or step in particular should I expect downtime ?(I’ll have a good idea as the test db is a prod refresh) .
Thanks again Mike
Then you follow the standard procedure, and not the one with two servers.
You will get downtime from the minute you kick off autoupgrade in deploy-mode.
Cheers,
Mike
Hi Mike,
what about this solution:
Source system (Linux 6 and Oracle 11).
First, I make the database ready to upgrade:
autoupgrade.jar -config step1.cfg -mode analyze
autoupgrade.jar -config step1.cfg -mode fixups
autoupgrade.jar -config step1.cfg -mode analyze (Just to check again)
Next I’ll backup the source database.
From this backup I create an database on target system:
(Linux 7 and Oracle 19)
RMAN>connect auxiliary /
RMAN> duplicate database to noopen backup location ….
SQLPLUS> alter database open resetlogs upgrade;
Finaly:
autoupgrade.jar -config up3.cfg -mode upgrade
Is this an vaild path?
Thanks
Yes, it will be a valid path.
The only problem I see is that AU does not upgrade the database when it is in UPGRADE mode.
I think you need to shutdown it again after you did the “alter database open resetlogs upgrade” in order to allow AU to start it in upgrade mode again.
Cheers,
Mike
Hi Mike, I’m trying to follow your procedure. Thanks for the HOL19c and all your work.
My client would like to take an RMAN backup of a database on the old h/w running 11g/12c and restore&recover on new h/w running 19c. We don’t want to install 19c on the old h/w and we don’t want to install 11g/12c on the new h/w. Following your various guides I’m fine upto where the db is recovered. I can ALTER DATABASE OPEN RESETLOGS; SHUTDOWN IMMEDIATE; STARTUP UPGRADE.
However, if I run autoupgrade.jar … -mode deploy I get….
“Error Cause: Database UPGR open with status OPEN_MIGRATE. For DEPLOY mode, open it with one of the following: [OPEN, MOUNTED].”
But I can’t open an 11.2.0.4 db under 19.8 binaries w/o UPGRADE….if I leave it just mounted then and run -mode upgrade|deploy then it runs for a while saying “The jobs are still being prepared, try again in a few seconds” and then exits with “Removing database UPGR. AutoUpgrade failed to open it in an [OPEN] mode with a [READ_WRITE] open_mode”.
I feel like I’m missing something here.
OLD TEST VM NEW TEST VM
HOL11g HOL19c
RHEL 7.6 RHEL 7.6
Oracle v11.2.0.4 Oracle v19.8
The linux homes and /u03 (fra) are nfs but the rest is local.
Previously I tested rman cloning and upgrade
HI Duncan,
I think the issue here is that AU does not expect the database to be started in upgrade mode.
The workaround should be:
After the restoration is complete, start the database with “alter database open resetlogs upgrade”
Then shut it down immediate.
Then let AU start it in UPGRADE mode afterwards.
I guess that should work.
And you need to run AU with “-mode upgrade”, not “-mode deploy” in this case
Cheers,
Mike