Recently I was testing OCI database upgrade from 12c to 19c and ran into an issue I wanted to fix. I was doing this via dbaascli (on Exadata) and the upgrade hit Bug 34455245 – db12c upgrade to 19c fails with ORA-600 [kxhfmgetextent.1] (Doc ID 34455245.8). I tried few things as with dbaascli you can continue the upgrade or roll it back, resuming the upgrade didn’t work so I thought let’s roll back and do it again.
What’s good with dbaascli is, that it does Guaranteed Restore Point automatically. You can then use dbaascli to roll back and redo your upgrade.
Initial upgrade command is really simple, I need to define my target home (new 19c home) and the database I’m upgrading.
dbaascli database upgrade --targetHome /u02/app/oracle/product/19.0.0.0/dbhome_5
--dbname MYUPG --upgradeOptions -keepEvents
Based on documentation I should be able to roll this upgrade back using dbaascli and adding flags -rollback and -sessionID. Let’s just say the documentation isn’t very clear on this how you need to set the flags! However, when I found the correct combination it basically says there’s no upgrade process for this session.
[root@iadsa001-exa1 upgrade]# dbaascli database upgrade --targetHome /u02/app/oracle/product/19.0.0.0/dbhome_5 --dbname MYUPG --upgradeOptions -keepEvents --sessionID 2169 --rollback
DBAAS CLI version 23.1.2.0.0
Executing command database upgrade --targetHome /u02/app/oracle/product/19.0.0.0/dbhome_5 --dbname MYUPG --upgradeOptions -keepEvents --sessionID 2169 --rollback
Job id: a06d5f3c-a841-4154-8713-a32c3f2012c7
Session log: /var/opt/oracle/log/MYUPG/database/upgrade/dbaastools_2023-04-19_07-38-55-PM_26991.log
Loading PILOT...
Session ID of the current execution is: 2178
Log file location: /var/opt/oracle/log/MYUPG/database/upgrade/pilot_2023-04-19_07-38-57-PM_29054
-----------------
Running Initialize_plugin job
Completed Initialize_plugin job
-----------------
Running Validate_plugin_inputs job
Completed Validate_plugin_inputs job
-----------------
Running Verify_upgrade_operation_state job
Execution of Verify_upgrade_operation_state failed
[FATAL] [DBAAS-54009] The current operation cannot proceed because the following database has no upgrade operation to be reverted from: MYUPG.
CAUSE: There were no upgrade records found in the cloud registry of the selected database.
ACTION: Check the logs at /var/opt/oracle/log/MYUPG/database/upgrade/pilot_2023-04-19_07-38-57-PM_29054 and the selected database.
*** Executing jobs which need to be run always... ***
******** PLUGIN EXECUTION FAILED ********
So, I can’t do dbaascli rollback and I can’t finalize the upgrade (which went 100% but failed on PDB side) using dbaascli. As this was test database, I was looking on fixing this issue and then re-running dbaascli database upgrade.
I still have the Guaranteed Restore Point what dbaascli had created, so why not use that! Below are the steps I took to use GRP. I validated GRP, did a startup mount, flashed back database and started it again on the old 12c home.
SQL> select con_id, name, time, guarantee_flashback_database from v$restore_point order by 1,2;
CON_ID
----------
NAME
--------------------------------------------------------------------------------
TIME GUA
--------------------------------------------------------------------------- ---
0
DBAAS#UPGRADE#20230410916961
19-APR-23 04.11.22.000000000 PM YES
[oracle@iadsa001-exa1 upgrade]$ srvctl stop database -d MYUPG_a3s_iad
[oracle@iadsa001-exa1 upgrade]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 19 19:43:34 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2.5744E+10 bytes
Fixed Size 32747552 bytes
Variable Size 5972688896 bytes
Database Buffers 1.9462E+10 bytes
Redo Buffers 276926464 bytes
SQL> flashback database to restore point DBAAS#UPGRADE#20230410916961;
Flashback complete.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
After this, start it again on the old home and open resetlogs
[oracle@iadsa001-exa1 upgrade]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 19 19:50:50 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2.5770E+10 bytes
Fixed Size 6870952 bytes
Variable Size 6024435800 bytes
Database Buffers 1.9462E+10 bytes
Redo Buffers 276926464 bytes
Database mounted.
SQL> alter database open resetlogs;
From database perspective that’s all that there was! However, when stopping the database and starting it up, I noticed srvctl still points to the 19c home. It’s good example that on OCI, many of the tasks are automated and you don’t need to do all the steps you’d be doing normally.
PRCD-1229 : An attempt to access configuration of database MYUPG was rejected because its version 19.0.0.0.0 differs from the program version 12.1.0.2.0. Instead run the program from /u02/app/oracle/product/19.0.0.0/dbhome_5.
What I needed to do still was to downgrade srvctl, it can be done with a simple command where you run the srvctl command from 19c home and downgrade it to use old 12c home.
/u02/app/oracle/product/19.0.0.0/dbhome_5/bin/srvctl downgrade database -db MYUPG -oraclehome /u02/app/oracle/product/12.1.0/dbhome_3 -targetversion 12.1.0.2
That was it! After downgrading srvctl, all normal pre-12c upgrade functionality seemed to be there and Console UI also properly updated. Sometimes, it’s good to see changes get synced to Console UI as well since dbaascli changes take some time (I guess around 30minutes) to get replicated there.
In the end, after rolling back and applying the fix to the bug enabled me to run database upgrade cleanly. I still couldn’t figure out why dbaascli rollback didn’t work as I’d be more than happy to use same approach each time. Luckily, I knew dbaascli takes automatic GRP which I could use.
It’s also good to notice the path of log files, they’re normally written under /var/opt/oracle/log/DATABASE_NAME/database/upgrade/ for upgrade, and can give more guidance on how to troubleshoot.
Even in OCI where things are automated, it’s good to have understanding what you can still do manually so you can troubleshoot issues faster than purely relying on support!
Last time I showed how to provision Autonomous Database Serverless (ADB-S) on Google Cloud. This…
I bet few years back folks didn't expect that by 2024 we would be able…
This will NOT be a technical walkthrough on Oracle Database@Azure but rather my opinions and…
Recently OCI announced small but VERY useful service, IP Address Insights. Why this matters? I've…