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.
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.
I use two virtual machines, an OL6.10 one with Oracle 184.108.40.206, and my OL7.6 one with Oracle 19.8.0. I will do the required steps on the 220.127.116.11 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 18.104.22.168 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.
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/22.214.171.124 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.
To start my upgrade, I need to execute AutoUpgrade on the source server with -mode analyze. Neither my OL6 nor my 126.96.36.199 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/188.8.131.52/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  Jobs failed  Jobs pending  ------------- 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.
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/184.108.40.206/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  Jobs failed  Jobs pending  ------------- 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 /
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 220.127.116.11 database in STARTUP UPGRADE mode:
$ . db19 [DB12] oracle@hol:/u02/oradata/DB12 $ sqlplus / as sysdba SQL*Plus: Release 18.104.22.168.0 - Production on Fri Jul 31 14:10:10 2020 Version 22.214.171.124.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.
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  Jobs failed  Jobs pending  ------------- 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 126.96.36.199.0 00:12:31 Oracle Workspace Manager UPGRADED 188.8.131.52.0 00:00:29 Oracle Real Application Clusters OPTION OFF 184.108.40.206.0 00:00:00 Oracle XML Database UPGRADED 220.127.116.11.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]
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
The precheck reported a hidden parameter ‘_fix_control’
but the prefixup did not report it to be removed.
Has the hidden parameter been removed?
No – AU does not remove any hidden parameters unless you advice it to do so in the config file.
I’d like to test the procedure on a 18.104.22.168.
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?
there shouldn’t be any license implication here:
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.
— 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.
— 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 22.214.171.124.0 – Production on Wed Aug 5 11:18:01 2020
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 – Production
SQL> select status from sys.v$instance;
Can you help?
Yes, I think we can:
Hope this helps – thanks,
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.
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”.
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 ?
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.
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.
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;
autoupgrade.jar -config up3.cfg -mode upgrade
Is this an vaild path?
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.
In your Blog You suggest to run it this way:
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
Must I go an otherway on Windows?
I did it like discribed in the blog several times one linux, wich went fine
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 188.8.131.52 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
RHEL 7.6 RHEL 7.6
Oracle v184.108.40.206 Oracle v19.8
The linux homes and /u03 (fra) are nfs but the rest is local.
Previously I tested rman cloning and upgrade
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
Hi. Mike. I will see you today KR seminar ^^
You said that “I use two virtual machines, an OL6.10 one with Oracle 220.127.116.11, and my OL7.6 one with Oracle 19.8.0. ”
The autoupgrade analyze job for 18.104.22.168 RAC on OL 6.10 is hung on “PFileReplicator.summary” over 2 hours.
Surely, after copy the datafiles to the OL7 for 22.214.171.124 Single on 19c RAC ASM, it’s no problem on the job.
Also, copy the datafiles to the OL7 on 126.96.36.199 RAC and execute autoupgrade analyze job, it is not hung and no problem, the job takes only 30 seconds (Surely, java version on OL6.10 was upgraded to latest version. )
Can I get any solutions for OL6 11204 RAC database?
* Autoupgrae analyze job Hung. 188.8.131.52 RAC ON OL6.10
order by vp.name,vp.ordinal;] in [PROD1, container:null] – ExecuteSql$SQLClient.run
2021-03-11 08:45:47.329 INFO Ignoring parameter spfile – PFileReplicator.convertStringRecordToPRecord
2021-03-11 08:45:47.336 INFO Generating content hash – PFileReplicator.summary
=> After that no message
* Good case. 184.108.40.206 on OL7
2021-02-08 17:33:30.178 INFO Ignoring parameter spfile – PFileReplicator.convertStringRecordToPRecord
2021-02-08 17:33:30.182 INFO Generating content hash – PFileReplicator.summary
2021-02-08 17:33:30.187 INFO Content hash successfully produced [02EA47F2336B343A1EA31A562A321470] – PFileReplicator.summary
2021-02-08 17:33:30.187 INFO Adding AutoUpgrade signature to pfile /home/oracle/upgrd_log/PROD1/temp/before_upgrade_pfile_prod1.ora – PFileReplicator.createPFileFromScratch
2021-02-08 17:33:30.189 INFO Begin [/oracle/db/220.127.116.11/bin/sqlplus, -silent, /nolog] – ExecuteProcess.startSqlPlusProcess
2021-02-08 17:33:30.189 INFO Begin Setting Oracle Environment – ExecuteProcess.startSqlPlusProcess
Thank you – and I will need the AutoUpgrade logs from the hang situation, potentially with a hanganalyze as well.
We have not see this before (at least, I haven’t).
I will get back to you in a separate email.
wondering if autoupgrade would handle going from 32 bit windows to 64 bit windows with ols and wm in use in the database. Also have concerns of going from we8mswin1251 to al32utf8. Nature of client is small window each year to upgrade. Have been hitting issues of expdp/impdp ols and wm from 18.104.22.168 to 19 so considering other approch
32-bit to 64.bit happens automatically as part of an upgrade. Hence, no action needed or required.
It will handle OLS and OWM as well. And you can keep MSWIN1252 if you’d like to. There is no pressure to move to AL32UTF8.
If you plan to do this change, you can use DMU (data migration assistant for unicode) instead of doing a full expdp/impdp.
Can i also use this procedure to migrate from 22.214.171.124 Standard Edition to 19.10 Enterprise Edition on a new host?
yes, you can – SE2 (or SE) to EE is straight forward. Only restriction you will need to use is the upg1.restoration=no flag in your config file.
Thx Mike for the tip.
I have lots of 126.96.36.199 db’s running on Oracle Linux 6.x servers with older java versions:
java version “1.5.0_51”
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_51-b10)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_51-b10, mixed mode)
How can one upgrade the java version to a version supported by the AutoUpgrade tool ?
I think that you need an additional JRE installation in your home:
Or if you have a newer home on your server and upgrade in place on the same machine, you can just set the home to the newer home with JRE 1.8.
We need to upgrade an 18 to 19c EE Database running von Windows.
Problem is the servers must be changed, too.
When running the “upgrade” step on the new server, we run into this error:
autoUpgrade Fails With UPG-1400#UPGRADE FAILED (Doc ID 2593418.1) as the “user.sql” file is missing.
This seems to be logical, as the autoupgrade steps (which create these file) run on the old server.
On the new server, there is no cfgtoollogs dir at all.
How to handle this one?
Simply copy the $ORACLE_BASE/cfgtoollogs directory to the new server before starting autoupgrade?
Hi Christian, please see my other comment.
we face this scenario on Windows:
Source is an 18.14 Database, target 19.10 – on different servers.
We did an analyze and fixup on source, then we did an backup and copied the backup to target system.
Then we created the service – using the spfilefile form source
oradim.exe -NEW -SID MIGDB -SYSPWD xx -MAXUSERS 50 -STARTMODE auto -SPFILE
After this we restored the database, opend it with :
alter database open resetlogs upgrade;
Then we started the upgrade:
java -jar autoupgrade.jar -config /home/oracle/mig5b.cfg -mode upgrade
The upgrade stops complaining that “login.sql” was missng. (DOC 2593418.1).
We set SQLPATH, copied the sqlfile and started the upgrade again.
However, the upgrade does not finish – for example TZ is not upgrades
We never faced this problem on Linux systems, so I wonder wether there is something wrong with the windows service.
When and how show I create the service?
I see your email and your comment regarding MOS: 2593418.1 working for Linux but failing on Windows.
Would you please mind working forward with the SR. As I have no Win system, I can#t reproduce anything on MS Win.
Is it possible to run the analyze and fixup phase only on the PDB on source (12c CDB). Example is a non cdb.
I will clone the PDB to a new 19 CDB on a new server after analyze/fixup and let autoupgrade upgrade the PDB there.
sure, you can. You need to add the pdb parameter to do so to the config file:
How to upgrade 188.8.131.52 to 19 using autoupgrade tool. I am able to upgrade a standalone/RAC databases but having issues while doing it on standby databases.
Autoupgrade tool launched with default options
Invalid value for dbName [ xxxx ]
Please open an SR and upload your logs. Make sure you used the newest AutoUpgrade tool.
You collect the logs with:
java -jar autoupgrade.jar -config yourconfig.cfg -zip
Add the alert.log and “opatch lsinventory”.
Hi Mike , Thanks for the post on autoupgrade tools. It’s really helping a lot.
Hope we can use autoupgrade my issue
My 11 g binaries are owned by orarac user and , we have installed 19c binaries with oracle user. As a standard, we must use oracle user for upgrade. Can this be done using autoupgrade.
at worst you can do the two-server approach by doing the “-mode analyze” and the “-mode fixups” step with orarac, and then the “-mode upgrade” task with the other user.
See Daniel’s FAST DEPLOY MODE blog post:
This is what you want to do then with two users but on the same server.
Thank you so much for your time .I had upgraded the DB in same server with “-mode upgrade” with new OS user and it worked . For source home, I had given /tmp in config file . The DB was upgraded and I had done post steps manually. This was done with 12c version to 19c version in lab setup, all components were in valid state . I am not sure, if we can follow same in our test and prod environment.
if all worked fine, all should be good, shouldn’t it?