Solved

Having Oracle troubles loading database on linux

Posted on 2010-11-29
11
681 Views
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:

LMORACLE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = domU-12-31-38-00-E1-E2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = LMORACLE)
    )
  )

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.
Thanks!
0
Comment
Question by:indsupport
  • 5
  • 5
11 Comments
 

Author Comment

by:indsupport
Comment Utility
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 11.2.0.1.0 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 11.2.0.1.0 - 64bit Production

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

Assisted Solution

by:ReliableDBA
ReliableDBA earned 50 total points
Comment Utility
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.

0
 
LVL 76

Expert Comment

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

http://www.dbasupport.com/forums/archive/index.php/t-22954.html

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

I suggest you open an SR with Oracle Support.
0
 

Author Comment

by:indsupport
Comment Utility
@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.
 alert-LMORACLE.zip
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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?

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.

0
 

Author Comment

by:indsupport
Comment Utility
So I tried to:
SQL> Alter system set db_recovery_file_dest_size=25G scope=both;
ERROR:
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.



0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 450 total points
Comment Utility
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.
0
 

Author Comment

by:indsupport
Comment Utility
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
ERROR:
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28301/backrest003.htm

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.
0
 

Author Comment

by:indsupport
Comment Utility
Thanks so much for your advice!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now