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
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
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)
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
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
upg1.sid=orcljso2
Output of the autoupgrade.log at job level is:
DataBase Name:orcljso2
Sid 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
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.
