I need to bring this blog post forward about AutoUpgrade and Data Guard, RAC, Restart and non-CDB to PDB. Initially I planned to write this a bit later. But some of you had questions or were wondering why AutoUpgrade hasn’t done certain tasks. Hence, I’d like to clarify what AutoUpgrade can do, what it can’t and what you’ll have to do at the moment.
I refer to the AutoUpgrade tool as of July 2019. In later versions, one or the other restriction may be lifted. I will blog about it then as well.
AutoUpgrade – Step-by-step
- The new AutoUpgrade Utility – Download, documentation and supported versions
- Create and adjust the config file for AutoUpgrade
- Config file for AutoUpgrade – Advanced options
- Config file for AutoUpgrade – Tweaking init parameters
- AutoUpgrade: ANALYZE, FIXUPS, UPGRADE and DEPLOY modes
- AutoUpgrade: Where do you find all the logfiles?
- UPG: The AutoUpgrade Command Line Interface
- Upgrading Multitenant databases with AutoUpgrade
- Moving to a new server with AutoUpgrade
- How to tweak the hidden settings in AutoUpgrade
- AutoUpgrade and Data Guard, RAC, Restart and non-CDB to PDB
- AutoUpgrade and Wallets
AutoUpgrade and Data Guard
Many of you may have standby solutions with Oracle Data Guard. In the current version, the AutoUpgrade tool can’t interact with the Data Guard broker and does not do any special tasks when you upgrade a database which is protected with one or more standby databases.
You will have to disable the Broker by yourself at first when you attempt to upgrade a standby database:
- When you upgrade, disable the Data Guard Broker
This blog post I wrote in September 2017 explains you how to disable the Broker, and that it is best practice to defer the log transport as well.
We are working on the inclusion of the correct Data Guard handling. Once it is there, the tool will disable the Broker and defer the log transport. And of course, enable everything correctly afterwards.
AutoUpgrade and Oracle Restart
Actually a lot of “my” reference customers use Oracle Restart. It gives you ASM and single-instance high availability. In the current version of the AutoUpgrade tool, it upgrades your databases in Oracle Restart correctly. But what it can’t do – or what you will have to do manually by yourself: You will need to prevent clusterware to control the database while you are upgrading.
And after the upgrade – at the moment – you will need to do the resource adjustment for the newer version:
In a later version of the tool we will handle this for you. We have a prototype running already but more tests are needed before we will release this to you.
AutoUpgrade and Oracle RAC (and RAC One-Node)
IMPORTANT:
With the AutoUpgrade 19.8 from March 27, 2020, the below steps are obsolete. The tool automates these now end-to-end.
See: https://mikedietrichde.com/2020/04/01/autoupgrade-new-version-with-rac-database-upgrade-support/
Exactly the same applies as I wrote in the “Restart” section before. This means, you will have to shutdown the instances on all other nodes. Otherwise you’ll hit an issue. Once you completed this, since the July 2019 version of the AutoUpgrade, we keep cluster_database=false
until the very end of our workflow. Before, the time zone change failed as we started the database with cluster_database=true
already while the resources and services weren’t upgraded. This is fixed now.
With the July 2019 version of the AutoUpgrade you don’t have to disable cluster_database
anymore either. The tool handles it correctly.
Here is an overview on the steps as an example:
- Determine the SPFILE and make a note. We will use it later:
$ srvctl config database -d myinst2 | grep spfile
- Set cluster database to false:
$ alter system set cluster_database=FALSE scope=spfile sid='*’;
- Stop the DB and start the local instance only:
$ srvctl stop database -d myinst2 $ srvctl start instance -d myinst2 -n 'hostname'
- Run AU (modes => analyze, deploy)
NOTE: Setupg1.timezone_upg=no
unless you use the Oct 2019 version of AutoUpgrade. - Manual upgrade Timezone if desired
- Update the OCR configuration for the database by running the ‘srvctl upgrade’ command from the new database home:
$ srvctl upgrade database -d myinst2 -o /u01/app/oracle/product/19.4.0/dbhome_1
- Modify the SPFILE to the original in step 1:
$ srvctl modify database -d myinst2 -spfile +DATAC3/MYINST2/PARAMETERFILE/spfile.911.1234567890
- Restart the DB in RAC mode:
$ sqlplus / as sysdba SQL> shutdown immediate $ srvctl start instance -d myinst2 -n 'hostname' SQL> alter system set cluster_database=TRUE scope=spfile sid='*’; $ srvctl stop instance -d myinst2 -n 'hostname' $ srvctl start database -d myinst2
.
AutoUpgrade and non-CDB-to-PDB
Another of our high priority projects is the conversion from non-CDB to PDB. You need to upgrade your non-CDB database first before you can plug it in and make it a PDB. We are working on the automation of this step. You will need to specify your destination CDB in the config file, but then the tool will do the upgrade, the plugin and the conversion for you. Of course, you will need to precreate the receiving CDB.
–Mike
Hi Mike,
I have interesting problem using AutoUpgrade with multi-tenant.
Basically it is failing on FILES_NEED_RECOVERY check. Did you experienced similar thing?
More details about this problem in 3-20716352819.
Regards,
Marcin.
Macin,
no, haven’t seen this before. Can you drop me an email please? (mike.dietrich — at — oracle.com)
Then I can share it with the team. Makes it just easier to reply to you.
Cheers,
Mike
Hi Mike, we are looking to upgrade many 12.2 noncdb to 19c cdb/pdb sometime next year; do you think the new tool will be ready to do that? Thx! Serge
Yes!
It can do it already but only in a very strict way.
Do the following if you want to test it:
1. Have a non-CDB
2. Create a CDB
3. Make sure the non-CDB has not more options in DBA_REGISTRY than the CDB
4. Simply add:
upg1.CDB=CDBSID
to the autoupgrade config file where CDBSID is the SID of the receiving CDB.
AutoUpgrade will upgrade your database to the target release – and then plug your non-cdb in and run noncdb_to_pdb.sql
But we want to implement more flexibility as at the moment we use NOCOPY and don’t give you an option to change the PDBs name which can be only done when you plugin.
Cheers,
Mike
Thank you Mike, unfortunately , after specifying upg1.CDB= paremeter, nothing happened : upgrade completed from 19.2 to 19.3 , but the non cdb database is still non cdb database running on its own.
Serge,
but the receiving CDB was there and open and used the same home you were upgrading to?
Cheers,
Mike
Yes sir.
Mike, the same home and up and running. Serge
Sorry Serge, my fault.
TARGET_CDB is the parameter, not CDB 🙁
upg1.TARGET_CDB=YOURCDB
Cheers,
Mike
No Mike, that parameter doesn’t work neither…. Regards, Serge
It does – see the instructions here:
https://mikedietrichde.com/hands-on-lab-autoupgrade-to-oracle-19c/
I did it several times to prepare the instructions.
Cheers,
Mike
Mike, i compared your config file with mine and there are only 2 diff. lines:
upg1.run_utlrp=yes
upg1.timezone_upg=yes
also, I don’t have upg2.restoration=no
Should I open an SR with Oracle ?
I couldn’t find any docs referencing upg1.TARGET_CDB parameter…
Regards,
Serge
Serge,
you can’t open an SR right now for this – it is not an official feature yet.
The parameters you listed shouldn’t matter I’d guess.
Sorry for the inconvenience but then you will have to wait until we support (and document) it officially.
Cheers,
Mike
Sergey,
it could be that restoration=no may be required to make this work right now.
We just saw an error during testing with the GRP.
Just a suspicion.
Cheers,
Mike
Mike, i could try that combination of parameters as well… btw, have you tried it with RAC and TDE ? my “real” databases are on on exadata with 2 node RAC and TDE enabled.
Cheers, Serge
Hi Sergey,
yes, it works with TDE as well – we copy the wallet file. And with RAC right now there are some extra steps necessary (which I documented on the blog already).
Cheers,
Mike
Serge
Did you have any luck with the plug-in – hitting a similar issue, just seems to ignore my parameter upg1.TARGET_CDB=CDBTEST
Any advice welcome
I may have found a bug. I have downloaded the 20191024 version of the autoupgrade tool.
If you specify :
upg.restoration=no
upg.drop_grp_after_upgrade=no
And you try to upgrade a non-cdb to a cdb/pdb, it tries to drop the GRP when executing the noncdbtopdb step.
Hope this helps….
Denis Sottiau
Thanks Denis – I will report this to the team!
Cheers,
Mike
Hi,
I attended a presentation of Autoupgrade tool, I tried it.
In fact, as you confirmed that the feature allowing to upgrade a noncdb db and transfer it into a cbd was working (even not documented) I tried it.
I am actually facing an issue at the end of the operation postugrade, which seems to en
Gd fine. In fact when I have a look at the logifle (postupgrade.log) at the end, I read the following :
Restart of database [DBALUD01]
[SUCCESS] [None]
– PostActions.upgPostActionsDriver
2019-11-11 15:19:27.638 INFO Finished – PostActions.upgPostActionsDriver
2019-11-11 15:19:27.638 INFO Finished – PostActions.runPostActions
2019-11-11 15:19:27.638 INFO No postupgrade user action defined – AutoUpgPostActions.runPostActions
When I check, the db is not restarted at all.
So you can imagine, when autoupgrade is moving to the next step NONCDBTOPDB it fails while trying to drop the GRP. In fact it fails because the DB is not started.
Would you have any idea, workaround, magic potion ?
Many thanks in advance.
Denis, this is a known issue which is fixed in the next version of the tool. If you eagerly would like to test it still, please send me an email from your company email account to mike.dietrich — at — oracle.com
Cheers,
Mike
Mail was sent, if you have it it would be wonderful to receive it 🙂
Denis,
I sent it already on Nov 12, 2019 to both of your email addresses. Can you please check (mail sent on Nov 12, 2019 at 11:34h).
Cheers,
Mike
Sorry, all I have on Nov. 12th is your OOO reply …
Will drop you another message.
Cheers,
Mike
Hi Mike,
I am planning to use “auto upgrade” tool to upgrade my 12.1 database to 19C and was wondering that.Do we need to mention pdb$seed and another pluggable database name in pdb list in config file ?
No – only the SID of your CDB.
I assume that you will upgrade “everything at once”.
Upgrade will take care of all PDBs including the seed.
Cheers,
Mike
Thank you very much Mike 🙂
Is it feasible to upgrade non-CDB from 11.2.0.4.1 to Multitenant 19C or even 20C using DataGuard?
At first, you can’t upgrade directly from 11.2.0.4 to 20c once it is there.
Second, I recommend you 19c.
Third, moving to 19c is an upgrade, moving to Multitenant is then a migration.
AutoUpgrade can do steps 2 and 3 for you in one pass once you precreate your CDB in 19c to plugin then (see the AutoUpgrade Hands-On Lab instructions on the blog: https://mikedietrichde.com/hands-on-lab-autoupgrade-to-oracle-19c/ ).
Thanks,
Mike
Hi Mike
It seems a serious limitation that where I have multiple databases sharing an ORACLE_HOME that I cant use Autoupgrade to upgrade just one of those databases without Autoupgrade shutting dowwn ALL databases and RAC services!
What is the reasoning behind this?
Hi Neil,
that should not happen. Why do you write it does? Can you share a bit more information with me here please?
Cheers,
Mike
Hi Mike
I have not tried yet because I cannot afford to have 30+ databases shutdown.
It was when I read MOS Note 2485457.1 and the following entries regarding RAC
When you start AutoUpgrade, and it detects an Oracle Database that is a cluster member node in an Oracle RAC cluster,
AutoUpgrade performs the following steps, in sequence:
It disables Oracle RAC services.
It disables the cluster membership of all Oracle RAC database cluster member nodes in Oracle Clusterware.
It shuts down all instances of the Oracle RAC database.
It starts the first local Oracle Database instance with the cluster parameter set to FALSE.
It updates the Oracle RAC database using the local instance to the new Oracle Database release binaries.
It starts srvctl upgrade database from the local Oracle Database instance home, and upgrades the configuration of the clusterware and all of its services to the new release.
It adds the recently upgraded Oracle RAC database to Oracle Clusterware by using srvctl enable database.
It recreates the server parameter file (SPFILE) with the updated parameters, and the parameter options you previously set for your cluster environment not affected by the release update.
It starts all instance of Oracle Real Application Clusters on the cluster.
So I also raised a SR 3-23460544431 where the analyst confirmed the behaviour?
Hi Neil,
I’m a bit worried about the answer the support engineer had given you. It is definitely NOT correct.
AU will shutdown only the database and all services for the databases you specified in your config file. It would be disastrous if we do this for all DBs running from within the same home.
What may have puzzled you is the sentence:
* It disables Oracle RAC services. ((( this refers to the database you specified – and not to all others )))
Cheers,
Mike
Thank Mike,
Yes, the engineers response and the wording in the doco combined to put serious doubt in my mind.
I have since tested this upgrade and it works as you have indicated it would.
Cheers,
Neil
Thanks Neil – and we recognized that the wording is not ideal. Sorry for that – we may need to adjust this.
Thanks,
Mike
Hi Mike
I am using the latest build of autoupgrade build.date 2020/07/10 11:20:49
i am upgrading from 11.2.0.4 RAC Non CDB to 19c CDB
not sure if this is possible but analyze was successfully but deploy failed after the upgrade and plugin into CDB
here is the output of job status
User configuration file [/home/oracle/config_pdb.txt]
General logs location [/home/oracle/logs/cfgtoollogs/upgrade/auto]
Mode [DEPLOY]
DB upg fatal errors ORA-00600,ORA-07445
DB Post upgrade abort time [60] minutes
DB upg abort time [1440] minutes
DB restore abort time [120] minutes
DB GRP abort time [3] minutes
———————— Jobs ————————
Total databases in configuration file [1]
Total Non-CDB being processed [0]
Total CDB being processed [1]
Jobs finished successfully [0]
Jobs finished/aborted [1]
Jobs in progress [0]
Jobs stage summary
Job ID: 101
DB name: DBXUPG1
SETUP <1 min
GRP <1 min
PREUPGRADE <1 min
PRECHECKS <1 min
PREFIXUPS 1 min
DRAIN <1 min
DBUPGRADE 31 min
POSTCHECKS <1 min
POSTFIXUPS 5 min
POSTUPGRADE <1 min
NONCDBTOPDB 6 min
———— Resources —————-
Threads in use [63]
JVM used memory [154] MB
CPU in use [13%]
Processes in use [11]
I can add the contents of the logfile , if needed but here is a snippnet
2020-08-17 18:36:27.185 INFO Guarantee Restore Point (GRP) successfully removed [DBXUPG][AUTOUPGRADE_9212_DBXUPG1112040]
2020-08-17 18:37:58.774 INFO No entry was found for [DBXUPG1:/u01/app/oracle/product/19.0.0/db_home1] in /etc/oratab
2020-08-17 18:42:47.950 INFO /home/oracle/logs/DBXUPG1/temp/after_upgrade_pfile_DBXUPG1.ora
2020-08-17 18:42:49.342 ERROR Dispatcher failed: AutoUpgException [ERROR3007#Errors executing [CREATE SPFILE='+DATA' FROM PFILE='/home/oracle/logs/DBXUPG1/temp/after_upgrade_pfile_DBXUPG1.ora';
CREATE SPFILE='+DATA' FROM PFILE='/home/oracle/logs/DBXUPG1/temp/after_upgrade_pfile_DBXUPG1.ora'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
] [DBXUPG1]]
2020-08-17 18:42:49.345 INFO Starting error management routine
2020-08-17 18:42:49.346 INFO Ended error management routine
2020-08-17 18:42:49.348 ERROR Error running dispatcher for job 101
Cause: Error finding error definition, contact Oracle Support
2020-08-17 18:42:49.348 ERROR Dispatcher failed:
Error: UPG-1699
[Unexpected exception error]
Cause: Error finding error definition, contact Oracle Support
For further details, see the log file located at /home/oracle/logs/DBXUPG1/101/autoupgrade_20200817_user.log; aborting job 101 for database DBXUPG
Thanks
Hi Michael,
did you open an SR – and can you share the SR number please with me?
There is one known issue with the SPFILE after upgrade where the upgrade completed successfully – but the SPFILE creation didn’t work. We fixed this is in the upcoming next version of AU.
But the ORA-600 and the ORA-7445 seem to direct to an issue in the database. AU does not through ORA-600s and core dumps.
Please upload the entire autoupgrade log directory (java -jar autoupgrade.jar -config -zip).
Upload also:
– alert.log
– trace files for the ORA-600 and ORA-7445
I guess, Support will want also an “opatch lsinventory” and a TFA.
Cheers,
Mike
HI mike
This is what oracle have said my SR no is SR 3-23811673221
*************************************
I checked internally on this.
Oracle RAC database upgrades from a non-CDB to PDB deployment of Oracle RAC using AutoUpgrade is not supported.
The details also in the MOS note:
AutoUpgrade Tool ( Doc ID 2485457.1 )
Section: Scope Limits for AutoUpgrade and Oracle RAC
***********************************************
its a shame as the upgrade works fine but the plugin into CDB kind if works as thePDB is is open write mode but datafiles are all over the place
Thanks for your help
Michael
Hi Michael,
the folks are correct here – but they should have said RAC Upgrade ___TOGETHER WITH ___ migration from non-CDB to PDB.
And this is for a simple reason:
We releases both features at the same time – full RAC AutoUpgrade Support and “migration from non-CDB to PDB”.
But we didn’t test it together as there was not enough time.
Now customers found a few nits. And we realized: “Ouch, we haven’t really tested this yet.”
So in order to make this save, and set expectations correctly, we say at the moment “it is not supported” even though it may work in most cases. And it does – but not in all cases.
That’s the story behind it. In one of the next versions of AU, it will be fully supported together.
Cheers, and thanks for your patience!
Mike
nice one mike appreciate your help and all the good work you do with oracle , I will wait for the next release for autoupgrade that will make my life so easy
Thanks
michael
Thanks for the kind feedback, Michael!
Cheers,
Mike
I’m a little unclear on what exactly has to happen when there is a data guard physical standby and you are using AutoUpgrade.
You say in this article to shutdown the broker, but I’m not clear on the rest of the steps. You linked to an older article about upgrading (not autoupdate)
Is this the process when using AutoUpdate with dataguard?
– Install the new software in new home on the standby host and patch it with the newest RU
– Install the new software in new home on the primary host as well and patch it with the newest RU
– DEFER redo shipping on Primary
– Disable the Data Guard Broker configuration
– Shutdown everything on the standby
– Autoupgrade Primary
– Restart standby listeners with the new environment
– Mount the physical standby and start redo apply
– Enable the Data Guard Broker configuration
– ENABLE redo shipping on Primary
Hi Gordon,
you need to do the extra steps right now.
In a standby env, when you upgrade the primary, you always need to disable the broker. Otherwise it will break your upgrade.
AU does not do this for you right now.
And you need to restart your standby in the correct home before starting log transport and apply again.
AU doesn’t do this either.
In RAC, in addition, you need to do the SRVCTL part on the standby which is not handled by AU.
Cheers,
Mike
Hi mike
I have notice that there is a new version of autoupgrade , see above regarding my issue upgrading and then moving it into a cdb , I have tested this out with the new version but i have having the following issue
2020-09-25 16:24:13.115 INFO End Oracle Home=/u01/app/oracle/product/19.0.0/db_home1 Oracle Sid=N/A Run command =[/u01/app/oracle/product/19.0.0/db_home1/bin/oerr, ORA, 01078] Command=None Filename=None – ExecuteProcess.doCmds
2020-09-25 16:24:13.115 INFO The process ended with an exit value 0 and the following output
01078, 00000, “failure in processing system parameters”
// *Cause: Failure during processing of INIT.ORA parameters during
// system startup.
// *Action: Further diagnostic information should be in the error stack.
– ExecuteProcess.doCmds
2020-09-25 16:24:13.115 INFO Starting – ExecuteProcess.destroyProcess
2020-09-25 16:24:13.115 INFO Begin Exiting process – ExecuteProcess.destroyProcess
2020-09-25 16:24:13.116 INFO End Exiting process – ExecuteProcess.destroyProcess
2020-09-25 16:24:13.116 INFO Finished – ExecuteProcess.destroyProcess
2020-09-25 16:24:13.116 INFO Finished – ExecuteProcess.doCmds
2020-09-25 16:24:13.116 INFO End /u01/app/oracle/product/19.0.0/db_home1/bin/oerr – CheckForErrors.getOracleError
2020-09-25 16:24:13.116 INFO End – CheckForErrors.getOracleError
2020-09-25 16:24:13.116 ERROR
DATABASE NAME: USUPG1
CAUSE: ERROR at Line 1 in [Buffer]
REASON: ORA-01078:/home/oracle/logs/USUPG1/temp/during_upgrade_pfile_usupg1.ora
ACTION: [MANUAL]
DETAILS: 01078, 00000, “failure in processing system parameters”
// *Cause: Failure during processing of INIT.ORA parameters during
// system startup.
// *Action: Further diagnostic information should be in the error stack.
– CheckForErrors.checkForErrors
looking into /home/oracle/logs/USUPG1/temp the pfile exist but its in upper case
during_upgrade_pfile_USUPG1.ora
I was hoping the plugin from non cdb into cdb for RAC would work in this version , any chance you could confirm
Thanks
michael
Hi Michael,
can you check in the alert.log please which parameter is causing this trouble? -zip” and upload it together with the alert.log?
Can you please open an SR, put all the logs from AU together with “java -jar autoupgrade.jar -config
The PLUGIN and RAC aren’t supported together yet – this will come soon. But the 19.9.2 version does not have support for it. It supports PLUGIN, and it support RAC, but not both together yet.
Cheers,
Mike
Hi Mike
I will raise an SR with oracle , one more question , How do i split the upgrade and the plugin with the config files
If i first upgrade to 19c which i can without any issues , do i just need to include upg3.target_cdb=infcont
upg3.target_pdb_name=PDB_NE
upg3.target_pdb_copy_option=file_name_convert=(‘+DATA/DLUPG’,’+DATA’)
do i need to remove any parameter from the upgrade config
will autoupgrade just know the DB has been upgraded and just do the plugin
Thanks
michael
Hi Michael,
see here for the example how to plugin when you have already an 19c database:
https://mikedietrichde.com/2020/08/04/oracle-autoupgrade-between-two-servers-and-plugin/
Cheers,
Mike
brilliant Mike much appreciated
Hi Mike, how does the sample config file for a RAC database look like ? For example in a single instance DB we have something like below . I’m specifically looking for sid parameter in RAC DB config file.
source_home=/u01/app/oracle/product/12.2.0/dbhome_1
target_home=/u01/app/oracle/product/19c/dbhome_1
dbname=testdb
sid=testdb
target_cdb=cdb1
target_pdb_name=testdb
Thanks
Sasi
Hi Sasi,
you can avoid the dbname – but SID must be set correctly to the instance’s SID on your node.
And the plugin to CDB on RAC is not fully supported yet. Version supporting it fully on RAC will be out soon.
Hence, at the moment, we’d recommend to use AU for the upgrade on RAC, but do the plug/conversion manually afterwards.
Cheers,
Mike