Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Couple of days back, I execute shutdown on my DB but it took very long time so I shutdown my m/c. After restarting my machine, I get this exception when I try to connect to my database using TOAD. I referred below mentioned threads, but issue is still not resolved:

TNS-12505: TNS:listener does not currently know of SID given in connect descriptor

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (DBD ERROR: OCIServerAttach)

Contents of my tnsnames.ora:

ORCL=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1521)
)

Output of 'lsnrctl services':

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-FEB-2012 23:53:33

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
    (CONNECT_DATA=
    (SERVER=dedicated)
    (SERVICE_NAME=orcl)
    )
    )

All my oracle services are running. I tried to start database using startup command:

C:\Users\PPPP>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 15 23:56:59 2012

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             763363792 bytes
Database Buffers          301989888 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 214624
Session ID: 5 Serial number: 3

In listener.log file, I see this error:

Thu Feb 16 00:13:28 2012
16-FEB-2012 00:13:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)*            (USER=PRITHVIRAJ-PC$))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)    (HOST=127.0.0.1)(PORT=59161)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in     connect descriptor

Please help me to sort out this issue.

share|improve this question

migrated from stackoverflow.com Feb 16 '12 at 21:38

This question came from our site for professional and enthusiast programmers.

    
Is the database crashing when you get the ORA-03113; and is there anything in the alert log? –  Alex Poole Feb 15 '12 at 19:20
    
I see follwoing error in alert.log: Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc2_214820.trc: ORA-19815: WARNING: db_recovery_file_dest_size of 3221225472 bytes is 100.00% used, and has 0 remaining bytes available. Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_214624.trc Is this the issue, my oracle is not getting started? –  user613114 Feb 16 '12 at 2:57
    
There should be a stack trace in orcl_ora_214624.trc, post the beginning of the file, including the stack trace. Even better also post the contents of alert.log file after starting up –  steve Feb 16 '12 at 7:25
    
sql>startup mount; sql>alter database clear unarchived logfile group n; it works successfully. sql>alter database open; –  user7491 Mar 16 '12 at 11:14
    
Same issue -- the ORA-03113: end-of-file on communication channel indicated a crash. Mysterious output, but through the wonders of stackexchange, was able to get a solution quickly. Editing the title to include ORA-03113 on this question might help others in the future. –  Andrew Aug 9 '12 at 22:02

1 Answer 1

As the ORA-19815 message indicates, your 3GB recovery destination is full; the database is unable to archive any more redo log files. It has reached a point where it needs to switch a full redo log file out, but there are none available for re-use because they can't be archived. I imagine this is why it wouldn't shut down cleanly, and possibly why you wanted to shut down in the first place as you wouldn't have been able to do much with the database in that state.

The short-term fix is to increase the size of the recovery area, assuming you have enough disk space, e.g.:

startup nomount
alter system set db_recovery_file_dest_size=4G scope=both;
alter database mount;
alter database open;

But you then need to work out how to manage your archives, and set the recovery area size to a sensible value for your backup and retention needs. This may be as simple as scheduling a job to remove obsolete backups, e.g. from the Enterprise Manager console's Availability tab, under Manage Current Backup; but you need to determine the best course for you (and not blindly follow advice from some random guy on the Internet who knows nothing about your requirements, and not that much about EM/RMAN either).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.