Skip to content

Oracle Issues [ORA-]

March 27, 2012

Index

 

1) Unable to connect with password having special characters.

 maldevsrv:/opt/oracle: sqlplus test/“login@12345″@D041
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 13 05:53:54 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:

Issue:
========
sqlplus test/login@12345″@D041  => @ is a special character , use escape characters \ to overcome the issue.

Solution:
==========
sqlplus test/\login@12345\”@D041

2) OEM Alert Occurred At=Apr 3, 2012 8:04:35 AM

EDT  Target Name=MWI ,Message=Failed to connect to database instance: ORA-12537: TNS:connection closed

(DBD ERROR: OCIServerAttach).

This happened to us when database processes exceeds the defined limit in parameter file and Alert.log shows clear evidence for the same, but there may be different reasons for  ORA-12537 or TNS-12518, you can see below the V$resource_limit , which says database reached upto 200 processes at some point in time and  currently 78 processes are in use.
 
To overcome the same issue for future occurance , the Process parameter was increased, which needs database re-start/bounce.
 
Listener LOG
=============

03-APR-2012 08:04:35 * (CONNECT_DATA=(SID=MWI)(CID=(PROGRAM=perl@maldevsrv)(HOST=maldevsrv)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=161.194.91.103)(PORT=39969)) * establish * MWI * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe

Listener Service Status :
===================
Service “MWI” has 1 instance(s).
Instance “MWI”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:83597 refused:1364
         LOCAL SERVER

Alert.Log
============

Tue Apr 03 08:03:41 2012
ORA-00020: maximum number of processes 200 exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Tue Apr 03 08:05:23 2012
ORA-00020: maximum number of processes 200 exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.

SQL> select name from v$database;

NAME
---------
IMW

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                       78             200        200        200
sessions                                        81             204        352        352
enqueue_locks                                  379             523       4996       4996
SQL>  show parameter processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     200
set line 200
col name for a40
col VALUE for a30
  1  select NAME,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE
  2  from v$parameter
  3* where NAME like ('%&value%')
SQL> /
Enter value for value: processes
NAME                                     VALUE                          ISDEFAULT ISSES ISSYS_MOD
---------------------------------------- ------------------------------ --------- ----- ---------
processes                                200                            FALSE     FALSE FALSE
System Modifiable is False , which means if the parameter is changed then database needs to be restarted/bounced, to take into effect.

3) ORA-01031: insufficient privileges on windows

Click Here the post

4) ORA-01031: insufficient privileges on Solaris

-bash-3.00$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin

-bash-3.00$ cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS)

-bash-3.00$
-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Oct 27 11:44:40 2013

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Existing sqlnet.ora
=====================================================
SQLNET.AUTHENTICATION_SERVICES= (NTS)

Modified sqlnet.ora as below :
=====================================================
SQLNET.AUTHENTICATION_SERVICES= (ALL), 
again this would give issues for connections like conn scott/tiger@d041 as below.
OR
Comment the above parameter 

-bash-3.00$ cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (ALL)

-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Oct 27 11:47:59 2013

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
D041

SQL> show user
USER is "SYS"

SQL> conn scott/tiger@d041
ERROR:
ORA-12641: Authentication service failed to initialize

Warning: You are no longer connected to ORACLE.
#################################################################################
Then try Below
----------------
-bash-3.00$ cat sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (BEQ,ALL)

-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Sun Oct 27 12:11:59 2013

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"
SQL>
SQL> conn scott/tiger@d041
Connected.
SQL> show user
USER is "SCOTT"

Authentication Methods Available with Oracle Net Services: 
===========================================================
NONE - for no authentication methods, including Windows native operating system authentication 
(to use Windows native operating system authentication, set this parameter to nts). 
When SQLNET.AUTHENTICATION_SERVICES is set to none, a valid user name and password can be used to access the database.

ALL - for all authentication methods

NTS - for Windows native operating system authentication

http://docs.oracle.com/cd/B28359_01/network.111/b28317/sqlnet.htm#BIIDAFFD

5) ORA-01450: maximum key length (6398) exceeded

Click Here the post

Advertisements

From → ORA- Issues

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: