Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Having Oracle troubles loading database on linux

Posted on 2010-11-29
Medium Priority
Last Modified: 2012-08-14
I have a java webapp with an Oracle 11.2 db that was working fine for several months.  Out of the blue, it stoped working.
I went into my webserver's log (tomcat 5) and saw that it was having trouble connecting to the DB with an error # 12505.

That error is: ORA-12505, TNS:listener does not currently know of SID given in connect

I tried to tnsping my SID and it worked:

I tried to lsnrctl services and it said, "The listener supports no services".  That can't be correct, right?

My tsnames.ora file is as follows:

    (ADDRESS = (PROTOCOL = TCP)(HOST = domU-12-31-38-00-E1-E2)(PORT = 1521))

Please keep in mind that this app was working fine for about 3 months and it all of a sudden stopped working.  No one logged in and changed anything (I checked the system logs).

Any ideas?  I tried googling the heck out of this issue.
Question by:indsupport
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5

Author Comment

ID: 34235365
Also, forgot to mention.  Whenever I try to start up the instance, even on server reboot, I get an ORA-03113: end-of-file on communication channel error.

Below is a paste from the oracle startup.log:

SQL*Plus: Release Production on Mon Nov 29 19:13:58 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2217632 bytes
Variable Size            2231372128 bytes
Database Buffers          956301312 bytes
Redo Buffers               16945152 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1970
Session ID: 125 Serial number: 5

SQL> Disconnected from Oracle Database 11g Release - 64bit Production

/u01/app/oracle/product/11.2.0/db_1/bin/dbstart: Database instance "LMORACLE" warm started.

Assisted Solution

by:Chakravarthi Ayyala
Chakravarthi Ayyala earned 200 total points
ID: 34235631
Looks like the database is not up and running.
After "Database Mounted", not seeing a message "Database Opened".
Are you sure the the DB is up and running?
Could you please attach your alert log file.

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34238375
the article is pretty old but check the troubleshooting post from Metalink here:

Since you claim nothing changed it might be a disk space issue.

I suggest you open an SR with Oracle Support.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 34239411
@slightwv: I thought that might be it, but when I checked the disk space, I'm only using 29%.
Here is my disk usage:
$ df -k
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1             51606140  13926140  35058560  29% /
none                   3932160         0   3932160   0% /dev/shm

@ReliableDBA:  I don't think the DB is up and running.  As mentioned above, every time I try and start it up, either with dbstart, /etc/rc.d/init.d/oracle, or whatever, as you said, it isn't saying "Database Opened".
alert.log file is attached.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34239487
Check the last alert log entry:

Errors in file /u01/app/diag/rdbms/lmoracle/LMORACLE/trace/LMORACLE_arc1_1974.trc:
ORA-16038: log 1 sequence# 124 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/oradata/LMORACLE/redo01.log'
Instance terminated by USER, pid = 1970

Just because you have filesystem space does not mean Oracle has 'allowed' space for archived redo logs.

Check the value for the parameter: db_recovery_file_dest_size

You might need to clean up some archived redo logs.  How are you currently backup up the database?

LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34239501
Also look at the value for db_recovery_file_dest.  Then see how much space is used in that directory.  Bet it is about the size of db_recovery_file_dest_size.

You can increase the value of db_recovery_file_dest_size for a short term fix.


Author Comment

ID: 34239573
So I tried to:
SQL> Alter system set db_recovery_file_dest_size=25G scope=both;
ORA-03114: not connected to ORACLE

So my error is getting in the way of changing that size.

I wanted to try "rman BACKUP RECOVERY AREA" but apparently that ONLY backs up to tape, not disk by default.

I KNOW that there has to be a way to do it:

I'm just kind of unsure.  I don't mind deleting the files with an RMAN DELETE, but I'm not sure which files I should delete without messing anything up.  If I could get rid of them all, I'm cool with that.

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 1800 total points
ID: 34239608
from a sqlplus prompt:

SQL> conn / as sysdba
SQL> startup mount
then try:
SQL> Alter system set db_recovery_file_dest_size=25G scope=both;

>>I'm just kind of unsure.

Don't delete anything until you are sure about what you are deleting.  Once we get the database up you will need to spend some time in the RMAN docs so you can make sure you have a proper backup and recovery strategy so you do not fill up the recovery_dest again.

Author Comment

ID: 34239773
Yeah.  I tried using RMAN to list the backups, but it didn't work because the DB isn't up:
RMAN> list backup;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/30/2010 10:36:41
RMAN-06171: not connected to target database

Tried to do the above sql commands:
Enter user-name: sys/**** as sysdba
Connected to an idle instance.

SQL> conn / as sysdba
ORA-01031: insufficient privileges

Warning: You are no longer connected to ORACLE.

I tried it just as sys:
SQL> startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter system set db_recovery_file_dest_size=25G scope=both;

System altered.
SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2217632 bytes
Variable Size            2231372128 bytes
Database Buffers          956301312 bytes
Redo Buffers               16945152 bytes
Database mounted.
Database opened.

So I think that worked.

So I restarted my tomcat and GOAAAAL!! It is working again!

Many thanks to both of you!

Anything I should do to check to make sure the DB is being backed up successfully?  Being a noob, i've had a cronjob running the exp command to file, rotating it every 30 days.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34239806
>>Being a noob, i've had a cronjob running the exp command to file, rotating it every 30 days.

exp really isn't considered a backup.  I suggest you spend some time in the RMAN docs.

There's a 2-day dba section for this:

I use what is called a rolling incremental.  This basically performs an initial full ( level0) backup.  Then Daily I do an incremental (level1).  It then takes that level1 and rolls it back into the level0 so I basically always have a full level0 backup.

Also make sure you read up on: block change tracking.

Author Comment

ID: 34239907
Thanks so much for your advice!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

662 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