DOAG Conference 2020

What crazy times we have. Most conferences are online only, some are also on-premises, but without the necessary hygiene concept to avoid corona (or without enough control), as I have seen at twitter. 

The big DOAG conference this year is an online conference AND an on-premises one - but with a big hygiene concept. Roundabout half of the presentations are streamed online, all others can only be seen at Nuremberg. 

I was selected as a speaker again, which makes me still proud. This year, I will talk about migrating and upgrading a database using the autoupgrade tool over to nodes. I will show this with a Windows environment - because it's a little bit more to do manually, but I will also cover the things you need to do at Linux (as this means I will just say: you don't need to do that step on Linux 😉). Unfortunately, the presentation will not be streamed online, this means, if you want to see that, you have to go to Nuremberg (and speak german, as my presentation will be in german). 

So, stay safe and see you there - hopefully!




Autoupgrade.jar on MS Windows: Database is open but "Error Cause: The database <--> is currently open with status of CLOSED."

People are using autoupgrade.jar to upgrade the Oracle databases more and more, this is the good news.

But the more people using autoupgrade.jar - the more things arise, while working with it. That's the bad news (or maybe it's also good news, because we do get a better feeling if things happen, WHY they happen).
At the blog of my employer, one was asking for a solution of the problem that autoupgrade in analyze mode is reporting a status of closed, but the database is open. He is connecting with an application and sqlplus, tnsping, etc. is working.

He was running autoupgrade on a 12c database on a MS Windows server in analyze mode, when he hits this error, also with the latest downloaded version of autoupgrade.jar (you should always download the latest autoupgrade.jar from Oracle Support).

The error was similar to this one:

Unsupported language [de], defaulting to English
AutoUpgrade tool launched with default options
------------ ERROR ------------
Error Cause: The Database orcljso2 appears to be down or open with the incorrect binaries for the mode ANALYZE. Ensure it is open with C:\app\oracle\product\12.2.0\dbhome_1

------------ ERROR ------------
Error Cause: The database orcljso2 is currently open with a status of CLOSED.  For ANALYZE mode, it needs to be open in one of the following: [OPEN, MOUNTED].

Unable to connect to database orcljso2 for entry upg1


To be honost, this is NOT an autoupgrade error, because there are some constraints for autoupgrade you have to fullfil, so it can do its work. One of the main things is, autoupgrade does connect to the database using "sqlplus / as sysdba". There isn't any tnsnames.ora or password involved. If this doesn't work in your environment, you need to fix that. It is also not related to MS Windows, but in Linux environments, typically connecting "/ as sysdba" is working (as you do everything using your "oracle" user and the default groups).

But how to know, what is the root problem, as autoupgrade does only tells you "the database is closed"?
Autoupgrade does write very, very detailed logs (or better traces). As you have specified a log directory for autoupgrade, you can find the logs there.
There are 2 possibilities where autoupgrade does put the logs into. If you don't specify a local entry, it puts it into the global directory, otherwise in the local one of your database specification in the config file.
  • global.autoupg_log_dir=C:\app\oracle\autoupgrade
  • upg1.log_dir=C:\app\oracle\autoupgrade\orcljso2
In my environment, I can then find the log files "autoupgrade.log" and "autoupgrade_err.log" in the directory C:\app\oracle\autoupgrade\cfgtoollogs\upgrade\auto

Some examples, of what can go wrong while (not) connecting with "/ as sysdba" and what "autoupgrade.log" does show as output.

1. A missing part in the database home of the source

I have specified
  • upg1.source_home=C:\app\oracle\product\12.2.0\
while the right entry would be
  • upg1.source_home=C:\app\oracle\product\12.2.0\dbhome_1
The entry at the log file then looks like
2020-07-09 14:52:45.844 ERROR Invalid value for source_home [C:\app\oracle\product\12.2.0\dbhome1] - Utilities.validateDirectory
2020-07-09 14:53:19.251 ERROR Unexpected IOException [null] [SELECT STATUS FROM SYS.V$INSTANCE;] - ExecuteSql$SQLClient.run
java.io.IOException: Cannot run program "C:\app\oracle\product\12.2.0\bin\sqlplus" (in directory "C:\Users\JOERG~1.SOB\AppData\Local\Temp"): CreateProcess error=2, The System cannot find the File specified
    at java.lang.ProcessBuilder.start(Unknown Source)
    at oracle.upgrade.commons.processes.ExecuteProcess.startSqlPlusProcess(ExecuteProcess.java:315)
    at oracle.upgrade.commons.sql.ExecuteSql$SQLClient.run(ExecuteSql.java:882)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.io.IOException: CreateProcess error=2, The System cannot find the File specified
    at java.lang.ProcessImpl.create(Native Method)
    at java.lang.ProcessImpl.<init>(Unknown Source)
    at java.lang.ProcessImpl.start(Unknown Source)
    ... 4 more

2020-07-09 14:53:19.259 ERROR The database orcljso2 appears to be down or open with the incorrect binaries for the mode ANALYZE,
ensure it is open with C:\app\oracle\product\12.2.0 - UpgradeConfigDBValidator.initializeIsDBUp
2020-07-09 14:53:19.262 ERROR The database orcljso2 is currently open with a status of CLOSED. 
For ANALYZE mode, it needs to be open in one of the following: [OPEN, MOUNTED]. - UpgradeConfigDBValidator.initializeIsDBUp

Solution: Correct the Source home path in your configuration. I think the same error can happen, if the Source Home is not the same as the home the service was started from.
Note: If you specify e.g. the home and miss the "_", you do get another error message:
Invalid value for source_home [C:\app\oracle\product\12.2.0\dbhome1]
It was not possible to validate the directory for source_home of entry upg1)

2. Wrong SID in the configuration file

If you have specified a wrong SID in the configuration file, the error is a little bit hidden.

2020-07-16 14:33:53.786 ERROR The database orcljso appears to be down or open with the incorrect binaries for the mode ANALYZE,
ensure it is open with C:\app\oracle\product\12.2.0\dbhome_1 - UpgradeConfigDBValidator.initializeIsDBUp
2020-07-16 14:33:53.798 ERROR The database orcljso is currently open with a status of CLOSED.  For ANALYZE mode, it needs to be open in one of the following: [OPEN, MOUNTED]. - UpgradeConfigDBValidator.initializeIsDBUp
2020-07-16 14:33:53.803 INFO Content of the file C:\app\oracle\product\12.2.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


So it halts at this state in the log. The only thing where you can see that you have specified a wrong SID is at the error itself (my database is ORCLJSO2 and not ORCLJSO).

Solution: Correct the SID in your configuration file.
Note: If the SID is correct but the dbname is wrong, the autoupgrade tool connects to the database and starts working.

Wrong configuration is:
    upg1.dbname=orcljso
    upg1.sid=orcljso2

Output of the autoupgrade.log at job level is:
   DataBase Name:orcljso2
   Sid Name     :orcljso2

3. Wrong SQLNET configuration or ORA_DBA group missing

Another reason, why the autoupgrade tool cannot connect to the database is that you have some values at your sqlnet configuration, which don't allow this or your (windows shell) user is not part of the ORA_DBA group.

As example, you have specified:
SQLNET.AUTHENTICATION_SERVICES = (NONE)

The output at the logfile is pretty same as for a wrong SID:

2020-07-16 15:06:19.805 ERROR The database orcljso2 appears to be down or open with the incorrect binaries for the mode ANALYZE,
ensure it is open with C:\app\oracle\product\12.2.0\dbhome_1 - UpgradeConfigDBValidator.initializeIsDBUp
2020-07-16 15:06:19.821 ERROR The database orcljso2 is currently open with a status of CLOSED.  For ANALYZE mode, it needs to be open in one of the following: [OPEN, MOUNTED]. - UpgradeConfigDBValidator.initializeIsDBUp
2020-07-16 15:06:19.825 INFO Content of the file C:\app\oracle\product\12.2.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



Solution: Check your SQLNET configuration, e.g. set SQLNET.AUTHENTICATION_SERVICES = (NTS), start "lusrmgr" to check the the user account is part of the ORA_DBA group. Be aware, that there can be also some kind of "ORA_<ORACLE_HOME>_DBA" in the "lusrmgr"!

As it is a little bit more special using MS Windows, the documentation for authentication can be found here.
If you use a domain account, have a special look at the documentation part "Overview of Operating System Authentication Enabled at Installation". You need to put this domain account into ORA_DBA AND grant local administrative rights.

Hope that helps you running autoupgrade.jar (even on MS Windows) successful.



Register of a standby database fails (within Oracle GI/repository) on Oracle Database appliance

A couple of days ago I tried to register a standby database on ODA with a newer relase of ODA (in my case 18.8) and Grid Infrastructure. I followed the procedure of the Dbvisit Standby documentation.
In there, it is described that you need to start the database on the standby environment as "read only".
Afterwards, you should be able to register the database in the Grid Infrastructure using odacli.
The procedure is described at this manual in detail and should work with Dbvisit Standby Version 8 and 9.

Unfortunately, if you run the ODACLI to register the database, it fails.

odacli register-database -c OLTP -s odb6 -t SI -sn <mydb.mydbdomain.com> -p <somepassword>

The job at the ODA is started, but it errors out:

Job details
----------------------------------------------------------------
ID: 432c4c54-0d2f-XXXX-XXXX-aa15f27ea3ab
Description: Database service registration with db service name: <mydb.mydbdomain.com>
Status: Failure
Created: May 5, 2020 3:40:06 PM CEST
Message: DCS-10001:Internal error encountered: Failed to archive log all
Session altered.

alter system archive log current
*
ERROR at line 1:
ORA-01109: database not open

This also means, that you see the "failed" configurations in odacli list-databases.

I have created a Service Request with Dbvisit to check the manual/the process of registering the database on ODA. At the meantime, the only solution is to open the database "read/write" to register it at the ODA.
To open the standby read/write it's necessary to "switchover" the primary to the standby you want to register and run odacli register-database on the -now- primary. Afterwards you can switch the roles of the databases again, so your original standby is a standby again at the end.

For your application that means, that it is necessary to have a small maintenance window where the end users can't work.

I will edit this blog post as soon as I do have more information, so come back regularly if you are interested in any news.

Autoupgrade.jar - Skip Fixups Step-by-Step

Autoupgrade will be more and more important to upgrade Oracle databases to the newer releases (18c, 19c, 20c). Mike Dietrich e.g. has told at the DOAG conference 2019, that autoupgrade will be the future of Oracle Exadata database upgrades also.

The autoupgrade tool is a very quick evolving tool and it becomes more stable and more helpful with every new release (nearly every quarter, sometimes every 3 or 4 weeks, you can download a new version from support.oracle.com).

But there are still things, you should know and which can be misunderstood at the documentation, if you start first with the autoupgrade tool. And then you may don't get the result(s) you are expecting.

One of the things I write today about is how you can skip automatically running fixups.This is e.g. useful if you want to prepare the database with the pre-upgrade part and run the autoupgrade.jar later on at a specific time (e.g. at the weekend or late at the evening) and not in dialog mode.This means, you spent less time at the "upgrade" phase itself (e.g. part of the normal prefixups is gathering statistics).
It is also helpful if you run autoupgrade.jar with "deploy" or "upgrade" mode, because BOTH modes automatically run POST fixups (and you may want to skip one or more and do them (later) manually).

Like everytime, first step is to download the latest autoupgrade.jar from MyOracleSupport!

Then, you check the java environment and create your own configuration file. (A small how to (see preparation at https://a-different-view-by-js.blogspot.com/2019/12/everything-goes-auto-1.html) can be found within my slide deck from DOAG 2019 "Alles wird Auto": https://a-different-view-by-js.blogspot.com/p/blog-page.html or at Mikes Blog https://mikedietrichde.com/2019/06/13/create-and-adjust-the-config-file-for-autoupgrade-19c/).

I typically use "create_sample_file config" and change that to fit my needs.


After everything is specified in the config file, the autoupgrade.jar is started in analyze mode.


 After some minutes the job has finished. Now it is essential to note down the job number, because you will need it later on in your configuration file. 

 

At the specified log path, you have a subfolder <job-number>/prechecks with a couple of files.


First is, check carefully ALL the issues at the preupgrade-HTML report, because you want to find the ones you don't want the autoupgrade.jar let fix "automatically" (in pre or post phase) while running later on. 

Then, you need to edit the fixup file in the same directory. It's the <database>_checklist.cfg file. 

E.g. if you want to skip the "DICTIONARY_STATS" fixup, you have to change the part at [runfix] from YES (default, as you can see in the picture) to NO and save your file.



Now have a look at the documentation for overriding the default fixups. The documentation clearly states "By default, AutoUpgrade uses the most recent generated file." So this means, if you run the autoupgrade tool later on with the "deploy" mode, the new job creates a new config file and does not recognize yours automatically!


If you use the autoupgrade mode "upgrade" then the prefixup phase is completely skipped, so also the fixups you want to get done will not run.

You have to create a new entry inside of your autoupgrade-configuration.
The parameter is called "checklist" and there you can specify the checklist configuration with the modified prefixup steps.
I like to copy/paste that from another default configuration file I do have, so I only need to uncomment the last line and put the right directory/filename into the configuration.



So the configuration for checklist in my file after the change is:

upg1.checklist=c:\oracle\autoupgrade\logs\ECKES\ECKES\100\prechecks\eckes_checklist.cfg

I typically fix also some other things manually, so I let run the analyze phase more than once and skip all the manually fixed things in the eckes_checklist.cfg of my FIRST job. This makes it easier to handle and I don't need to remember which is the fixup skip configuration file I have specified in my autoupgrade configuration. 

Last step is to run the autoupgrade again in "deploy" mode. It will take the checklist configuration file specified in your config instead of a new one, makes the fixups in there, skips the one with "NO" and proceeds with the upgrade, makes the post fixups which are not set to "NO" and finishes the upgrade.

Isn't that easy in comparison with all the manual things you had to do before when upgrading your database?