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

Question

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:

https://stackoverflow.com/questions/5661610/tns-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript

https://stackoverflow.com/questions/3267850/ora-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript

Contents of my tnsnames.ora:

ORCL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=localhost)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl)
    )
  )

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

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.

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

Answer 2

Use this command. It may solve the issue

alter system 
  set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
                                            (HOST=127.0.0.1)
                                            (PORT=1521)))';