Difference between revisions of "Oracle errors"

Revision as of 11:11, 15 July 2013

ORA-27092: size of file exceeds file size limit of the process

Cause: an attempt was made to open a file that exceeds the process's file size limit (ulimit), additional information shows the current limit (logical blocks) and the size of the file (logical blocks)
Action: increase the processes file size limit (ulimit) and retry

truncate table pibadm.test_ph
                      *
Error at line 1 :
ORA-00604: error ocurred at recursive SQL level 1
ORA-01116: error in opening database file 2
ORA-01110: datafile 2 : '/opt/u01/data/PIBQA/UNDO_01.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 262143
Additional information: 2621441

Check the fs_size limit for this user in /etc/security/limits
Compare this with another user, say oracle. Probably -1 (unlimited)

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

lsnrctl status <listener_name>

• Check the local_listener parameter
• Try and connect with the service name or directly with

sqlplus 'sys/*******'@<host>:<port>/<SID> as sysdba

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

• [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.

ORA-04030: out of process memory when trying to allocate 2520 bytes

expdp problem (ORA-04030: out of process memory when trying to allocate 2520 bytes (pga heap,koh-kghu call heap))

select value from v$pgastat where name='maximum PGA allocated';

SHOW PARAMETER TARGET

ORA-04030: out of process memory when trying to allocate 2520 bytes (pga heap,koh-kghu call heap)

alter system reset pga_aggregate_target scope=spfile;

show sga

select * from v$pgastat;

show parameter process

select sum(value)/1024/1024 Mb
from   v$sesstat s, v$statname n
where  1=1
and    n.STATISTIC# = s.STATISTIC#
and    name = 'session pga memory'

show parameter WORKAREA

select value from v$pgastat where name='maximum PGA allocated';

alter system set memory_max_target=10G scope=spfile;

alter system set memory_target=3G scope=both;

select * from v$memory_target_advice order by memory_size;

RMAN-20033: control file SEQUENCE# too low

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
===RMAN-20033: control file SEQUENCE# too low===

From forums.oracle.com
This error occurs occasionally. I believe the cause is if an archive backup runs during a database backup, the controlfile is backed up by both and may (but not always) result in these errors. The database backup is usually successful and it is just the controlfile backup that complains.

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;
/

ORA-19809: limit exceeded for recovery files

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/15/2013 10:10:59
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 274716160 bytes disk space from 1468006400 limit

    ORA-19809: limit exceeded for recovery files
    Cause: The limit for recovery files specified by the db_recovery_file_dest_size was exceeded.

    Action: The error is accompanied by 19804. See message 19804 for further details

    ORA-19804: cannot reclaim string bytes disk space from string limit
    Cause: Oracle cannot reclaim disk space of specified bytes from the DB_RECOVERY_FILE_DEST_SIZE limit.

    Action: There are five possible solutions:
       1) Take frequent backup of recovery area using RMAN.
       2) Consider changing RMAN retention policy.
       3) Consider changing RMAN archivelog deletion policy.
       4) Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
       5) Delete files from recovery area using RMAN.

The solution here is to increase the value of db_recovery_file_dest_size but it might also indicate a problem with tapes if the archivelogs are being backed up to tape via tdpo.

SYS@DN7> alter system set db_recovery_file_dest_size=4000M scope=both;

System altered.

Looking into int further, saw RMAN parameters missing...

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO 'LOG_%d_controlfile_%F.rman';
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO BACKUPSET;