Difference between revisions of "Oracle errors"

Revision as of 12:55, 18 April 2013

ORA-24247: network access denied by access control list (ACL)

Cause: No access control list (ACL) has been assigned to the target host or the privilege necessary to access the target host has not been granted to the user in the access control list.

Action: Ensure that an access control list (ACL) has been assigned to the target host and the privilege necessary to access the target host has been granted to the user.

Your application will encounter an ORA-24247 error if it relies on one of the network packages and no proper ACL has been created. For the use of the following packages it is mandatory to have an ACL for the application user in place in 11g:

• UTL_TCP
• UTL_SMTP
• UTL_MAIL
• UTL_HTTP
• UTL_INADDR

Here is a very good explanation of how to setup the necessary ACLs: [whitehorses.nl]

ORA-12162: TNS:net service name is incorrectly specified

Probably nothing to do with TNS. Check your ORACLE_SID is set correctly!

ORA-00054: resource busy and acquire with NOWAIT specified

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

select object_name, s.sid, s.serial#, p.spid 
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

then

alter system kill session 'sid,serial#';

ORA-12514: TNS:listener does not currently know of service

Excellent description of how Oracle TNS listeners work and a checklist on how to fix this error

• Check the local_listener parameter
• Try and connect with the service name or directly with sqlplus 'sys/*******'@<host>:1522/<SID> as sysdba
• [TNS — Ed Stevens]

ORA-12547: TNS lost contact

If

sqlplus <user>/<pass>

fails with above error, try

sqlplus <user>/<pass>@<db connection>

If this works, it is most probably a permissions error.
Re-run the

$ORACLE_HOME/root.sh

script that was run as part of the original installation. This will reset the permissions on some important files.

Database trigger to capture ORA errors

From ora-ssn.blogspot.be

CREATE TABLE stats$error_log (
        err_dt          TIMESTAMP,
        db_user         VARCHAR2(30),
        msg_stack       VARCHAR2(2000),
        sqltxt          VARCHAR2(1000))
tablespace users;

Now, create a trigger on the database server.

CREATE OR REPLACE TRIGGER log_server_errors
  AFTER SERVERERROR
  ON DATABASE
DECLARE
          v_sqltext VARCHAR2(1000);
          nl_sqltext ora_name_list_t;
  BEGIN
          -- Capture entire error text
          FOR i IN 1 .. ora_sql_txt(nl_sqltext) LOOP
            v_sqltext := v_sqltext || nl_sqltext(i);
          END LOOP;
          INSERT INTO STATS$ERROR_LOG
          (err_dt, db_user, msg_stack, sqltxt)
          VALUES
          (systimestamp,
           sys.login_user,
           dbms_utility.format_error_stack, v_sqltext);
  END log_server_errors;
/