Having Oracle troubles loading database on linux

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.
Who is Participating?
slightwv (䄆 Netminder) Commented:
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.
indsupportAuthor Commented:
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.
Chakravarthi AyyalaDatabase AdministratorCommented:
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.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
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.
indsupportAuthor Commented:
@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.
slightwv (䄆 Netminder) Commented:
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?

slightwv (䄆 Netminder) Commented:
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.

indsupportAuthor Commented:
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: http://tinyurl.com/298bo8l

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.

indsupportAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>>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.
indsupportAuthor Commented:
Thanks so much for your advice!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.