Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Database instance blocked

Cristian OrellanaJul 29 2009 — edited Jul 30 2009

Hello guys,

I have a very particular problem here, let me explain the issue. I installed Oracle 10g R1 in a windows 2003 server SP2, create a new instance and test it, al fine until here.
After that I installed the Oracle Forms and Reports 10g, here start all my problems, I can't connect to my database with any client, I have the following error message when tried the connection:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

The listener status show the instance status as BLOCKED.

Investigating the ORA error message the resolution say that bouncing the server could solve the problem but not for me.
I can startup the database but is a a manual procedure and don't want to do it each time the server restart, so checking the registry I can see that the ORACLE_HOME variables are pointing to the last product (application server) and not to the database, here are the export of ORACLE registry:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
"inst_loc"="C:\\Program Files\\Oracle\\Inventory"
"ORA_CRS_HOME"="F:\\oracle\\product\\10.1.0\\Db_1"
"ORACLE_HOME"="F:\\oracle\\product\\10.1.0\\Db_1"
"ORACLE_HOME_NAME"="DEFAULT_HOME1"
"API"="E:\\oracle\\dbs"
"ORACLE_GROUP_NAME"="Oracle - DEFAULT_HOME1"
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"TNS_ADMIN"="F:\\oracle\\product\\10.1.0\\Db_1\\network\\admin"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES]
"HOME_COUNTER"="2"
"DEFAULT_HOME"="DEFAULT_HOME1"
"LAST_HOME"="0"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0]
"NAME"="FRServicesHome"
"PATH"="E:\\oracle\\appserver"
"NLS_LANG"="AMERICAN_AMERICA.UTF8"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID1]
"NAME"="DEFAULT_HOME1"
"PATH"="E:\\oracle"
"NLS_LANG"="AMERICAN_AMERICA.UTF8"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]
"ID"="0"
"ORACLE_GROUP_NAME"="Oracle - FRServicesHome"
"ORACLE_HOME_NAME"="FRServicesHome"
"ORACLE_HOME"="E:\\oracle\\appserver"
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"ORACLE_BUNDLE_NAME"="Enterprise"
"ORACLE_OPMN_SERVICE"="OracleFRServicesHomeProcessManager"
"d2docrep"="E:\\oracle\\appserver\\reports\\doc"
"FORMS90_PROJECT"=""
"FORMS90_SCCONVERT"=""
"FORMS90_SCDRIVER"=""
"FORMS90_SCVIEWER"=""
"FORMS90_SOURCECONTROL"=""
"FORMS90"="E:\\oracle\\appserver\\forms90"
"DE90"="E:\\oracle\\appserver\\tools\\common90"
"TK90"="E:\\oracle\\appserver\\tools\\common90"
"DOC90"="E:\\oracle\\appserver\\tools\\doc90"
"UI90"="E:\\oracle\\appserver\\tools\\common90"
"VGS90"="E:\\oracle\\appserver\\tools\\common90"
"CA_UPREFS"="E:\\oracle\\appserver"
"CA_GPREFS"="E:\\oracle\\appserver"
"DOC9O"="E:\\oracle\\appserver\\tools\\DOC90"
"MM90"="E:\\oracle\\appserver\\tools\\common90"
"REPORTS_PATH"="E:\\oracle\\appserver\\reports\\templates;E:\\oracle\\appserver\\reports\\samples\\demo;E:\\oracle\\appserver\\reports\\integ;E:\\oracle\\appserver\\reports\\printers;C:\\WINDOWS\\fonts"
"REPORTS_TMP"="C:\\DOCUME~1\\csc00018\\LOCALS~1\\Temp\\2"
"REPORTS_RESOURCE"="E:\\oracle\\appserver\\reports\\res"
"RW"="E:\\oracle\\appserver\\reports"
"REPORTS_TAGLIB_URI"="/WEB-INF/lib/reports_tld.jar"
"SQLPATH"="E:\\oracle\\appserver\\dbs"
"BROWSER"="E:\\oracle\\appserver\\browser"
"ORACLE_HOME_KEY"="Software\\ORACLE\\HOME0"
"REPORTS_CLASSPATH"="E:\\oracle\\appserver\\reports\\jlib\\rwrun.jar;E:\\oracle\\appserver\\jlib\\zrclient.jar;E:\\oracle\\appserver\\j2ee\\home\\oc4j.jar;E:\\oracle\\appserver\\j2ee\\home\\lib\\ojsp.jar;"
"TNS_ADMIN"="E:\\oracle\\appserver\\network\\admin"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\HTTP_Server]
"Path"="E:\\oracle\\appserver\\bin;E:\\oracle\\appserver\\Perl\\5.6.1\\bin\\MSWin32-x86;E:\\oracle\\appserver\\Apache\\fastcgi;E:\\oracle\\appserver\\opmn\\bin;E:\\oracle\\appserver\\opmn\\lib"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1]
"ID"="1"
"ORACLE_GROUP_NAME"="Oracle - DEFAULT_HOME1"
"ORACLE_HOME_NAME"="DEFAULT_HOME1"
"ORACLE_HOME"="E:\\oracle"
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"TNS_ADMIN"="E:\\oracle\\appserver\\network\\admin"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\iSuites]
"MDAC Session Update Status"="Complete"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1]
"ORACLE_HOME"="F:\\oracle\\product\\10.1.0\\Db_1"
"ORACLE_HOME_NAME"="OraDb10g_home1"
"ORACLE_GROUP_NAME"="Oracle - OraDb10g_home1"
"ORACLE_BUNDLE_NAME"="Standard"
"NLS_LANG"="AMERICAN_AMERICA.UTF8"
"OLEDB"="F:\\oracle\\product\\10.1.0\\Db_1\\oledb\\mesg"
"OO4O"="F:\\oracle\\product\\10.1.0\\Db_1\\oo4o\\mesg"
"SQLPATH"="F:\\oracle\\product\\10.1.0\\Db_1\\dbs"
"ORACLE_HOME_KEY"="SOFTWARE\\ORACLE\\KEY_OraDb10g_home1"
"ORACLE_BASE"="F:\\oracle\\product\\10.1.0"
"MSHELP_TOOLS"="F:\\oracle\\product\\10.1.0\\Db_1\\MSHELP"
"RDBMS_CONTROL"="F:\\oracle\\product\\10.1.0\\Db_1\\DATABASE"
"RDBMS_ARCHIVE"="F:\\oracle\\product\\10.1.0\\Db_1\\DATABASE\\ARCHIVE"
"SQLPATH_OLD"="F:\\oracle\\product\\10.1.0\\Db_1\\dbs"
"NLS_LANG_OLD"="AMERICAN_AMERICA.WE8MSWIN1252"
"ORA_VPRM_AUTOSTART"=hex(2):54,00,52,00,55,00,45,00,00,00
"ORA_VPRM_SHUTDOWN"=hex(2):54,00,52,00,55,00,45,00,00,00
"ORA_VPRM_SHUTDOWNTYPE"=hex(2):69,00,6d,00,6d,00,65,00,64,00,69,00,61,00,74,00,\
  65,00,00,00
"ORA_VPRM_SHUTDOWN_TIMEOUT"=hex(2):39,00,30,00,00,00
"ORACLE_SID"="test"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\ODP.NET]
"TraceFileName"="C:\\ODPNET.trc"
"TraceLevel"="0"
"TraceOption"="0"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\OLEDB]
"CacheType"="Memory"
"ChunkSize"="100"
"DistribTX"="1"
"FetchSize"="100"
"OSAuthent"="0"
"PLSQLRset"="0"
"PwdChgDlg"="1"
"SchRstLng"="10000"
"UserDefFn"="0"
"DisableRetClause"="1"
"VCharNull"="1"
"TraceCategory"="0"
"TraceFileName"="c:\\OraOLEDB.trc"
"TraceLevel"="0"
"TraceOption"="0"
"SPPrmDefVal"="0"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1\OO4O]
"CacheBlocks"="20"
"FetchLimit"="100"
"FetchSize"="4096"
"HelpFile"="F:\\oracle\\product\\10.1.0\\Db_1\\oo4o\\doc\\oracleo.hlp"
"PerBlock"="16"
"SliceSize"="256"
"TempFileDirectory"="c:\\temp"
"OO4O_HOME"="F:\\oracle\\product\\10.1.0\\Db_1\\oo4o"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\Ocr]
"ocrconfig_loc"="F:\\oracle\\product\\10.1.0\\Db_1\\cdata\\localhost\\local.ocr"
"Local_Only"="TRUE"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES\ORACLE0]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOMES\ORACLE1]
"NLS_LANG"="AMERICAN_AMERICA.UTF8"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\SCR]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\SYSMAN]

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\SYSMAN\OracleDBConsolevprm]
"EMDROOT"="F:\\oracle\\product\\10.1.0\\Db_1"
"ORACLE_HOME"="F:\\oracle\\product\\10.1.0\\Db_1"
"EMSTATE"="F:\\oracle\\product\\10.1.0\\Db_1\\test_machine"
"CONSOLE_CFG"="dbconsole"
"ORACLE_SID"="test"
"TIMEOUT"="15"
"TRACE_LEVEL"="16"

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\SYSMAN\OracleFRServicesHomeASControl]
"EMDROOT"="E:\\oracle\\appserver"
"ORACLE_HOME"="E:\\oracle\\appserver"
"TIMEOUT"="15"
"TRACE_LEVEL"="16"
"CONSOLE_CFG"="iasconsole"

I already copy the files from F:\oracle\product\10.1.0\Db_1\network\admin to E:\oracle\appserver\network\admin just in case.

If anybody want to know how I can solve this manually, here are the steps:
- Open a command window
- SET ORACLE_SID=TEST
- sqlplus /nolog
- connect sys/... as sysdba
- issue a shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
- startup

After that I can connect to the database, but how can I solve this issue?

Hope you can help me, best regards.

This post has been answered by 178340 on Jul 30 2009

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 27 2009

Added on Jul 29 2009

#general-database-discussions

6 comments

2,509 views