?
Solved

Oracle 11g keeps "dying" on Windows Server 2008

Posted on 2011-10-23
11
Medium Priority
?
1,244 Views
Last Modified: 2012-05-12
I have installed Oracle 11g (11.1.0.7) on Windows Server 2008 R2 (64-bit).  For quite a while it was working fine, but lately it seems to be dying for no apparent reason.  I start up the listener and the oracle service, and I can connect via SQL Plus without any problems.  The oracle.exe process is using about 1 GB of memory.  But after a few minutes, the memory usage by oracle.exe plummets to about 20 MB.  Then when I try connecting via SQL Plus I get the following error:

ORA-12514: TNS:listener does not currently know of service requested in connect

Open in new window

If I restart Oracle (in Windows services) it is fine for a little while, but then dies again.

I can't find any logs which explain why this is happening.

What could be causing this?  How do I prevent this from happening?  

Cheers,

Paul
0
Comment
Question by:mrgordonz
  • 5
  • 3
  • 2
  • +1
11 Comments
 

Author Comment

by:mrgordonz
ID: 37016084
I should add, this is not a production system, and I have complete control over the server, so if some guru wants me to try changing some settings, I can do that.
0
 
LVL 13

Expert Comment

by:stergium
ID: 37016318
Hello mrgordonz.
What about the oracle logs? transaction files? windows logs? is any of them available?
is an antivirus present on this computer? can you (is it safe?)  try disabling it for observation?
Does oracle instance has a certain amount of time before shuting down?
Sorry for the questions storm but its a way for me to understand what is happening.
My best guess would be that oracle is trying to restore from transactions the instance, a file could be missing or gone bad (due to disk maybe) and its shuting down the instance since its not recoverable.
Logs would give us a better view of the problem.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37017396
Look in the oracle alert log.

In 11g this is under the parameter diagnostic_dest

Look in trace folder for the old style alert log.  The trace folder should be under diag\rdbms\<SID>
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 48

Expert Comment

by:schwertner
ID: 37018150
For STATIC registration of the Oracle service to the Listener:

Go to the remote 10g or 11g installation where the Oracle server resides.

Backup LISTENER.ORA file!!!!!!!

In ...\network\admin open listener.ora

see

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\Ora11g)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora11g)
    )
  )

Add the entry
    (SID_DESC =
      (SID_NAME = test)
      (ORACLE_HOME = D:\Ora11g)
    )
with regard to the particular Oracle Home and SID Name.


In Oracle 11g you have to create from scratch in the listener.ora file the section

SID_LIST_LISTENER =
…..

mentioned above.

Restart the listener:
c:>lsnrctl stop
c:>lsnctl start

Thats all.

0
 

Author Comment

by:mrgordonz
ID: 37019085
I'm not sure why, but there don't seem to be any log files.  I certainly can't find them.  The only thing I can find in the Windows event log is this:

The description for Event ID 16 from source Oracle.orcl cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

There seem to be a lot of those messages in the event log.

Here is the contents of listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hgorcl)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

And tnsnames.ora:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hgorcl)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )


@schwertner:  I have added the extra entry to listener.ora which you suggested.  I'l monitor it and see what happens.

Cheers,

Paul


0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 total points
ID: 37019331
>>I'm not sure why, but there don't seem to be any log files.

There must be.  you cannot have an Oracle database without an alert log.

What is the value of the diagnostic_dest spfile parameter?
What are the sub folders under that folder?

0
 
LVL 48

Expert Comment

by:schwertner
ID: 37019943
Check so:

C:/....>  lsnrctl status

C:/....>  lsnrctl services

From Command Prompt as Oracle user.
0
 

Author Comment

by:mrgordonz
ID: 37021241
@schwertner:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hgorcl)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.1.0.7.0 - Production
Start Date                25-OCT-2011 02:23:55
Uptime                    0 days 5 hr. 29 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\oracle\product\11.1.0\db_1\network\admin\listener.ora
Listener Log File         d:\app\oracle\diag\tnslsnr\hgorcl\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hgorcl)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hgorcl)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl" has 2 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1326 refused:0
         LOCAL SERVER
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: HGORCL, pid: 3460>
         (ADDRESS=(PROTOCOL=tcp)(HOST=hgorcl)(PORT=51098))
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
0
 

Author Comment

by:mrgordonz
ID: 37021651
@slightwv:

diagnostic_dest = D:\APP\ORACLE

The sub folders are:

admin
cfgtoollogs
diag
flash_recovery_area
oradata
product

I found the log files, and here is what they contain:

alert_orcl.log:
Tue Oct 25 09:03:35 2011
Errors in file d:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_q001_3256.trc:
ORA-00474: SMON process terminated with error
PMON (ospid: 4008): terminating the instance due to error 474
Instance terminated by PMON, pid = 4008

orcl_q001_3256.trc:
Trace file d:\app\oracle\diag\rdbms\orcl\orcl\trace\orcl_q001_3256.trc
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
Windows NT Version V6.1
CPU                 : 1 - type 8664, 1 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:1393M/3071M, Ph+PgF:3711M/6141M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 21
Windows thread id: 3256, image: ORACLE.EXE (q001)


*** 2011-10-25 09:03:35.269
*** SESSION ID:(309.7) 2011-10-25 09:03:35.269
*** CLIENT ID:() 2011-10-25 09:03:35.269
*** SERVICE NAME:(SYS$BACKGROUND) 2011-10-25 09:03:35.269
*** MODULE NAME:(STREAMS) 2011-10-25 09:03:35.269
*** ACTION NAME:(QMON Slave) 2011-10-25 09:03:35.269
 
KSV 474 error in slave process

*** 2011-10-25 09:03:35.269
ORA-00474: SMON process terminated with error
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00474: SMON process terminated with error

I have read a few forum posts which mention this error (ORA-00474: SMON process terminated with error), and it seems that most people suggest contacting Oracle Support or upgrading.

I have taken exports of all the schemas I use, so am I just better off re-installing Oracle from scratch, and then importing the schemas?  The reason I ask is that even though this is only a demo environment, getting the issue resolved is urgent because it is affecting our sales demos.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37021739
I would vote with the Support approach.  You can reinstall but would likely install the same version the same way so I doubt it would actually fix anything.

If you wanted to reinstall I would suggest 11.2.0.2.  It is a little more stable than 11.1.0.7.

0
 

Author Closing Comment

by:mrgordonz
ID: 37048342
I don't know why, but I believe the issue was somehow linked to using 64-bit Windows &/or 64-bit Oracle.  I decided to re-install everything from scratch (OS, Oracle, etc), but this time I went 32-bit, and everything is working perfectly.

Thanks to everyone for trying to assist.  I have awarded the points to slightwv because I learnt some useful information about where to find Oracle logs, which I'm sure will come in handy in the future.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question