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

Many a times, When user try connecting to the database though an application, user gets the error as below.

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

Error: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

This is very comman issue, and there are many reasons for this error.


Cause: User was unable to connect to database through connect descriptor that we use to connect to database from remote server or from application.

Solution: 

Since listener is running,try to connect the database manually through connect descriptor that is usually stored in tnsnames.ora file.  Always make sure that connect descriptor has correct database name, hostname and port name.

Example:

sqlplus usrname/pwd@PRODDB

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 20 04:39:03 2012

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

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

Ø  Then check your database name in connect descriptor available at tnsnames.ora file.

Ø  If that is correct then check your services in listener to find whether requested database is being listened by the listener. You can use below command to check the same.

$lsnrctl services listener_name

Ø  If database is found, then wait for few seconds and check the connection.

Ø  If database is not found in the services, then manually register your database to the listener by using below command.

SQL> alter system register;

Check after few seconds, you will be able to connect to the database using connect descriptor.

If still not working, check the connection string of the database a provide the connection string to the user, to make sure the same string is used by users.

Also, check the mount point of the host. Sometimes the iNodes get occupied on the host, which make the listener state as hung and will not allow users to connect. If so, perform necessary housekeeping on the respective mount-points and check the listener Status again.

Happy Learning  :) 

Keep Sharing . . .

See Also :   

Slowness in Database 

Reading AWR Report

-

Interview Tips  

-

Houekeeping Tasks

How to generate ADDM task and generate its report for Tuning Purpose

In this post, I will explain How to create ADDM task  and check its report

We are using begin snapshot as 500 And end snapshots as 550

BEGIN

--------At First, we need to Create an ADDM task using the below procedure---------.

DBMS_ADVISOR.create_task (
advisor_name      => ‘ADDM’,
task_name         => ‘500_550_AWR_SNAPSHOT’,
task_desc         => ‘ADDM for snapshots 500 to 550.’);

--------— Now, Set the start and end snapshots as parameters to display. ------------.

DBMS_ADVISOR.set_task_parameter (
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘START_SNAPSHOT’,
value     => 500);
DBMS_ADVISOR.set_task_parameter (
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘END_SNAPSHOT’,
value     => 550);

 

—------ Now, Execute the task to display the report as parsing the task name. --------.

DBMS_ADVISOR.execute_task(task_name => ‘500_550_AWR_SNAPSHOT’,);
END;
/

 

—-------Finally, execute the following proc to display the report on console. -----------.

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report(‘500_550_AWR_SNAPSHOT’) AS report
FROM   dual;
SET PAGESIZE 24

ADDM Related Views for DBA

DBA_ADVISOR_TASKS – Basic information about existing tasks.
DBA_ADVISOR_LOG – Status information about existing tasks.
DBA_ADVISOR_FINDINGS – Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS – Recommendations for the problems identified by an existing task.

See Also :   Performace Tuning Scripts

20 ASM Real-time Interview Questions