Problem:
Unable to startup or shutdown database from OEM (Enterprise Manager/Database Control)
Unable to run backup from OEM because of a message about database already being closed.
Environment:
Oracle 11g r1
Win 7 64 bit
Investigation:
When in OEM and I choose Shutdown, the shutdown did not work. One time instead of shutdown, I choose Open the database. After clicking “view the details”. this is what came up:
—————————————
SQL*Plus: Release 11.1.0.7.0 – Production on Thu Feb 25 21:34:49 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SP2-0640: Not connected
Enter value for _i_serial:
SQL> SQL> SP2-0640: Not connected
SQL> SP2-0640: Not connected
SQL>
————————————–
I had no idea why there was a prompt for _i_serial.
I searched the internet using bing and found no applicable reference. Also metalink did not yield anything useful. In the past, I have found that no search results usually indicates that the problem is specific to my setup.
Later, while installing the Jan2010 patchset,I entered “sqlplus /nolog” and got the following:
————————————-
SQL*Plus: Release 11.1.0.7.0 – Production on Thu Feb 25 21:36:07 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SP2-0640: Not connected
Enter value for _i_serial:
————————————-
This time, I used “List” to see what was in the change buffer and saw there was a sql command.
I finally remembered that I had downloaded Tanel Poder’s scripts two months before. I had added my path for the files to the registry local Machine oracle script in the SQLPATH entry. There was indeed a login.sql script in this directory which was running.
Renaming the login.sql to login.sql_disable resulted in the issue being resolved.
My chief motivation for blogging this self-inflicted error is that it may help someone else in the future.
I’ve noticed in the oracle documentation the prevalence of using sqlplus/nolog. I can see that this helps hide the username/password from being visible in the OS environment such as through unix ps.
