Saturday, 2 September 2017

ORA-12537 While Connecting To Database Via Listener

Connection to the database server fails with ORA-12537
Local connections working fine but the connection via listener fails with ORA-12537

[oracle@hostname1]$ sqlplus username/password@prodDB
SQL*Plus: Release
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ORA-12537: TNS:connection closed

Check the listener status :

$lsnrctl status Listener_Name

The listener log for the failing connection shows following error message -

TNS-12518: TNS:listener could not hand off client connection
  TNS-12546: TNS:permission denied
   TNS-12560: TNS:protocol adapter error
    TNS-00516: Permission denied
      Linux Error: 13: Permission denied

Check the listener.log file for additional details.

The OS level error message in the listener log "Linux Error: 13: Permission denied" indicates that the listener was not able to hand off the connection to oracle processes or not able to spawn a new dedicated user process because of lack of permission at the OS level. 

Permissions on oracle binary ( $ORACLE_HOME/bin/oracle ) would not have set correctly.

 To resolve the error -

-          1. Check and correct the permission on /var/tmp/.oracle directory. Permissions on this directory should be 777 with sticky bit set.

            # chmod 01777 /var/tmp/.oracle 

-          2. Check and correct permissions on $ORACLE_HOME/bin/oracle binary and make sure that it is set to 6751.

              $ chmod 6751 $ORACLE_HOME/bin/oracle

Reference Metalink Note :1050756.6

 See Also

Friday, 25 August 2017

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.

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.

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.


sqlplus usrname/pwd@PRODDB

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

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

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

Ø  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 :   


Thursday, 10 August 2017

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

--------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’,);

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

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

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