In my previous blog post I did showcase how to restart a failed database upgrade on the command line before Oracle Database 12c, with Oracle Database 12.1.0.x and with the new Oracle Database 12.2.0.x:
Here I’d like to show the new capability of the Database Upgrade Assistant (DBUA) to restart an upgrade, a functionality the DBUA was missing until now. Please note that you can always go back to the command line, no matter in which version you have used the DBUA.
I won’t describe an upgrade with the DBUA in this blog post as this is showcased in the documentation already:
Starting the database upgrade with DBUA
I will upgrade the UPGR database well known from our Hands-On Lab.
And please don’t put in your credentials in the fields below – if you are logged in as the oracle user then this will lead to failure and drive you crazy …
Another thing which puzzles me:
I still have to execute the olspreupgrade.sql script from the newer (in my case the 12.2) home by myself. I’d wish
the DBUA would do this for me as well as I’ll have to open an xterm, set my environment and type in a very long path name to point to the new 12.2 home in order to execute this script in my source environment.
Ok, let’s kick off the upgrade:
The progress bar is very imprecise in relation to the duration – you can ignore it more or less.
And – very sad – the Alert and Activity Monitor buttons disappeared – but they may reappear in a later release of the DBUA.
.
The Error Scenario
It’s always fun to kill pmon finding out how cool this database is 😉 It survives the deadly attack 🙂 Of course it does … it’s the Oracle Database 😉
Bang!
.
The DBUA recognizes the failure
Even though the DBUA recognized the failure quite quickly it still tries to complete the upgrade – which of course results in a ton of errors. It just means that you’ll have to wait until the DBUA has “progressed” the upgrade till the end – and DON’T CLOSE THE DBUA as otherwise you’ll lose the ability to RETRY.
You’ll see the error count going up until the DBUA has reached the “end” of the (failed) upgrade.
And then it displays the RETRY button:
Rerun the Database Upgrade
Once you hit RETRY the DBUA will try to solve the situation – and in my case it will start up my source database in STARTUP UPGRADE mode again – and then process the upgrade using the -R option of catctl.pl (described in the previous blog post)
You’ll find also a new set of logfiles in $ORACLE_BASE/cfgtoollogs/dbua/ subdirectories indication with a number (here: 1) and an “R” that this is the first restart attempt’s logs:
–Mike
Excellent Sir!
Thanks for this detailed explanation, along with screen prints.
Excellent Sir!
Thanks for this detailed explanation, along with screen prints.
Mike,
As usual, excellent posts about 12.2 upgrade. My question is: Let’s say during upgrade, some database related issue is found and upgrade fails. To correct the issue, I have to start the database in normal mode which might be impossible because upgrade already made changes to data dictionary half way. What is my course of action?
Thanks,
Arun
Arun,
you raise a very good question.
There are things you can solve in STARTUP UPGRADE mode. But once the upgrade kicked off you usually can’t start it in NORMAL MODE anymore as you’ll get BOOTSTRAP TABLE errors.
If you really can’t fix something in STARTUP UPGRADE mode anymore than I would FLASHBACK DATABASE to a GUARANTEED RESTORE POINT which is my preferred way of resolving issues during upgrade (or for testing). Now I know that you can’t use this technique with SE environments as there’s no FLASHBACK DATABASE in SE. In this case you can only restore your backup, either your ONLINE backup or your PARTIAL OFFLINE backup (both described in the FALLBACK section in our big slide deck).
Thanks
Mike
Hi Mike,
I’m, probably far from the smartest person even in our office, so I don’t still get it. Let’s say a very common situation (my situation right now). I have a database in process of upgrade and some component failed during the upgrade due to something in the database. Then I’m in the phase where all components finished it’s migration process (only one failed with some minor error which I unfortunately am not able to resolve) and I would like to continue to the next part – post upgrade. In previous DB versions it was possible – I was able to simple ignore this errors and continue and then resolve those errors manually. Now I can only Retry (which is useless since the error occurs again) or abort – which I don’t want to. I can’t press Ignore since it is grayed out.
Now what?
Thanks.
Vasek,
the behavior shouldn’t be any different from previous version. If a component does not upgrade correctly but the DBUA does not treat this as “I must exit” the upgrade will finish. The “Restart” option is meant for cases when, for instance the upgrade got stuck, you fixed the reason for a stuck upgrade by applying the change with a SQL*Plus session, and then you continue in DBUA by restarting the upgrade. The crucial point IMHO is that in most cases people will quit the DBUA – and once it has been quit you can only retry the upgrade on the command line by using “catctl.pl -R”. This by the way will finish a quit DBUA upgrade 😉
Therefore, no difference in behavior to previous versions. Just a new option.
Cheers,
Mike
The primary reason I would need to restart an upgrade is that the network disconnected and DBUA has exited on its own. So the instructions that you lose the ability to restart if DBUA has exited – makes any talk of restart, nearly completely useless.
What would be nice is if the tool kept track of where it was, and had the ability to start where it left off – the thing that we have to do manually now.
Maybe someday. This tool is not improved much.
Robert,
you can always kick off again on the command line 🙂
https://mikedietrichde.com/2017/01/24/restarting-a-failed-database-upgrade-with-catctl-pl/
I do my upgrades usually on command line 😉
Cheers,
Mike
Hi Mike,
I’m totally blocked with DBUA, not even pass the first screen. When no credentials are set on the first screen, I got “[FATAL] [DBT-20010] Specified database “mibdd11g” could not be started.” When I set credentials, “[FATAL] [DBT-20007] Specified SYSDBA credentials are not correct. Specify the correct SYSDBA credentials and try again.”
As you stated, this is driving me crazy after hours and hours of orapw, parameters and other security stuff revisions.
I’m going to use manual method, but don’t know if this maybe already happened to you.
Thanks a lot.
Boris,
I ___NEVER___ put the credentials into the DBUA screen as I have seen only issues with it.
Use preupgrade.jar from MOS 884522.1 first (download the newest one), then follow the output – and then run simply “dbupgrade -l /home/oracle” to upgrade your database. Then follow the “post upgrade” advice of preupgrade.jar and all will be good.
Cheers,
Mike
I too facing the same issue, are you able to fix it?
[FATAL] [DBT-20010]
Don’t use the DBUA – use AutoUpgrade from 19c – see the blog for news next week – but it will work for 12.2.0.1 as well as long as you have at least the Jan 2019 RU installed in your destination home.
Cheers,
Mike
Hi,
I was totally crazy trying to update my test database for a training due to incorrect SYSDBA credentials (error was DBT-20007, logs showed PRCR-1097, PRCD-1024). After hours of research and changes specially to authentication parameters and pwfile, I noticed in logs this error was associated with some SRVCTL validations. So, I deleted the resource configuration with “srvctl remove database -d testdb”. When dbua 12.2 was executed after that, the error disappeared.
Hope this helps.
Hello Mike ,
Sorry for bothering you with my question , I had performed several oracle upgrade from 11203 to 12201 in our company but I got stopped with the quality server as the DBUA had stopped it self and I could not start it anymore , it seems that it was running in the background as when I restart the DBUA I can see only the version 12201. anyway I have performed the upgrade manfully after that and it finished successfully , but when I try to restart the database I got the error message below as you can see. any idea what went wrong here>
SQL> STARTUP ;
ORACLE instance started.
Total System Global Area 1526726656 bytes
Fixed Size 8747400 bytes
Variable Size 855639672 bytes
Database Buffers 654311424 bytes
Redo Buffers 8028160 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: “ACDRROWTSINTCOL#”: invalid identifier
Process ID: 4028
Session ID: 363 Serial number: 13855
SQL>
Thanks a lot for your feedback in advance.
Best regards,
Mohamed Zaghloul
Hi Mohamed,
you hit unfortunately a very typical DBUA case – not being able to resume and give you advice.
1. Make sure you have the init.ora and password file in the new home present (usually in ?/dbs)
2. Set your environment correctly for the new home
3. Start the database in STARTUP UPGRADE mode
4. Exist SQL*Plus
5. On the command line:
mkdir /home/oracle/upgradelogs
dbupgrade -R -l /home/oracle/upgradelogs
Check if the command line upgrade will resume your upgrade.
Please read before:
Oracle Support Document 2304874.1 (12C: While Upgrading RDBMS Using DBUA Fails with Error “[FATAL] [DBT-20024] The local instance for the specified database “db” could not be started” ) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2304874.1
Cheers,
Mike
Hallo Mike ,
Thank you very much really for your quick reply and advice which I followed and finalized the upgrade Successfully.
I have just to run the mentioned above commando after creating the upgrade logs directory under /TEMP directory on windows as dbugrade script was complaining that the logs directory was not created despite that I had created under home/oracle. By checking the upgrade script I could see that it was expected to find the upgrade directory under /TEMP and not home/oracle. anyway I do appreciate really again your professional help.
Cheers,
Mohamed.
Hi Mohamed,
thanks for your feedback – and happy that it helped.
In the future, relying in the AutoUpgrade will be much easier than the DBUA 🙂
Cheers,
Mike
Hello Mike, in my case, an 18c upgrade failed and used dbua to flashback to a guaranteed restore point creatrd before the upgrade. However. I am unable to open the database with following errors
1. startup fails with ora-39700- database must be opened in upgrade mode.
2. Startup upgrade failed with ora-01092 and ora-00600
2. I am able to mount the database but unable to open it. Any thoughts,
Thank you.
Hi Daniel,
please open an SR for such things. What you could do is:
1. Flashback manually
https://mikedietrichde.com/hol-19c-fallback-issues-during-the-upgrade/
(scroll down to 2.)
2. Open the database with RESETLOGS
My suspicion is that the flashback with DBUA didn’t work correctly.
But I’m not an DBUA expert and I strongly recommend to use either “dbupgrade” or the new AutoUpgrade.
Thanks,
Mike
And yet the Oracle documentation strongly recommends using DBUA to reduce the chance of missed steps or wrong order of steps.
Also ASM has to be upgraded using GUI tool like gridsetup? No cmdline option is available?
Really?? Where does the Oracle Documentation recommend to use DBUA in 19c? Seriously, I’d be happy to learn where this gets recommended.
The ASM gets upgraded automatically when you use gridsetup – it happens during rootupgrade.sh.
But no compatible parameters will be adjusted in ASM.
Cheers,
Mike
I can find it mentioned in these links:
Oracle 19c – DBUA In Silent Mode (Doc ID 2548985.1)
– It is the recommended method for performing Database upgrade at Release and patch level.
Complete Checklist for Upgrading to Oracle Database 12c Release 1 using DBUA (Doc ID 1516557.1)
– It is the recommended method for performing a major release upgrade or patch release upgrade.
https://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD60026
– DBUA is the recommended method for performing a major release upgrade or patch release upgrade.
https://docs.oracle.com/cd/E11882_01/server.112/e23633/intro.htm#UPGRD52704
– DBUA is the recommended method for performing a major release upgrade or patch release upgrade.
In 19c I would definitely use autoupgrade.
Without autoupgrade I would like to use DBUA except for the risk of a network disconnection.
Would running DBUA in silent mode with a response file (I could run the gui first to generate the response file) get around that risk whilst retaining the benefits of using DBUA?
I am even thinking of running gridSetup in silent mode with response file.
Thanks Paul!
These are Support notes – and I never understand why Support favors the DBUA as the logs are … well …
Anyhow, thanks for sharing and pointing me to it.
Actually, the DBUA does not work with a response file but with a long litany of command line parameters.
Instead, you could use “dbupgrade -l /tmp” and let the database upgrade. Command line upgrade has a few steps to type (preupgrade.jar, preupgrade_fixups.sql, dbupgrade, utlrp.sql, postupgrade_fixups.sql) but at least you see what’s happening – it writes the same logs as AutoUpgrade does.
Cheers,
Mike
What we can do if the DBUA is running and server got restart due to hardware failer.
How we can start the DBUA then after restart.
DBUA is not restartable. One of its biggest flaws.
Please use AutoUpgrade instead. DBUA gets deprecated for a ton of reasons.
Cheers
Mike
great