Ora 02002 error while writing to audit trail

Smart way of Technology

Worked in Database technology for fixed the issues faced in daily activities in Oracle, MS SQL Server, MySQL, MariaDB etc.

ORA-02002: error while writing to audit trail

ORA-02002: error while writing to audit trail

Error
ORA-02002: error while writing to audit trail

Cause
Error occurred due to insufficient space while writing the audit record in audit trail table.

Solution
1. On check the Alert log, we get the complete error as follows:
ORA-02002: error while writing to audit trail
ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0
ORA-01653: unable to extend table AUDSYS.CLI_SWP$16026a73$1$1 by 8192 in tablespace SYSAUX

We have three option:
1. Extend the existing tablespace.
2. Empty the AUDIT table
3. Disable the audit(its need restart of database)

1. Extend the existing tablespace SYSAUX
a) You found the tablespace name in alert log which is filled by Audit records.

set line 999 pages 999
col FILE_NAME format a50
col tablespace_name format a15
Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 «USEDSPACE GB», maxbytes/1024/1024/1024 «MAXSIZE GB» from dba_data_files where tablespace_name=’SYSAUX’;

b) As see tablespace has one data file which is full. So, you need to add one more file.

alter tablespace SYSAUX add datafile ‘E:ORACLEORADATAORCLSYSAUX02.DBF’ size 1G autoextend on next 500M;

2. Empty the AUDIT trail table

— take EXP backup of aud$ table.
TRUNCATE TABLE SYS.AUD$;

3. Disable the AUDIT
Note: before disable we need to implement any method from 1 or 2 points. So that it has enough space in tablespace for other records.

—check the audit parameter
SQL> show parameter audit_trail
NAME TYPE VALUE
———— ——— ———-
audit_trail string DB
— Disable the audit by setting
ALTER SYSTEM SET AUDIT_TRAIL=NONE SCOPE=SPFILE;
—Restart the database
Shutdown immediate
startup

Источник

ORA-2002 : error while writing to audit trail

I am oracle 11g and on SPARC OS.

I enabled audit in my database by setting the parameter audit_trail=DB and then i restarted my instance.

On doing so the database started but whenever i am trying to connect using pl/sql developer , i am getting the following list of errors :

ORA-00604 : error occured at recursive SQL level 1
ora-00904 : «DBID» invalid identifier
Ora-02002 : error while writing to audit trail
ORA-00604 : error occured at recursive SQL level 1
ora-00904 : «DBID» invalid identifier

I checked on the net for ora-02002 and increased the size of the system tablespace from 750 M to 1000M.

My system tablespace is dictionary managed.

bounced the database also , but still getting the same errors.

on Dec 20, 2010 8:59 PM

Answers

I am oracle 11g and on SPARC OS.

I enabled audit in my database by setting the parameter audit_trail=DB and then i restarted my instance.

On doing so the database started but whenever i am trying to connect using pl/sql developer , i am getting the following list of errors :

ORA-00604 : error occured at recursive SQL level 1
ora-00904 : «DBID» invalid identifier
Ora-02002 : error while writing to audit trail
ORA-00604 : error occured at recursive SQL level 1
ora-00904 : «DBID» invalid identifier

I checked on the net for ora-02002 and increased the size of the system tablespace from 750 M to 1000M.

My system tablespace is dictionary managed.

bounced the database also , but still getting the same errors.

on Dec 20, 2010 8:59 PM

Can you try and show us a cut/paste of making a connection using the sqlplus rather than any tool?

when i type sqlplus in my putty after i sudo ,

i simply put connect as sysdba and it connects.. I am able to do all operations.. !

But when i try to connect through any other user i get this :

Enter user-name: system
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: «DBID»: invalid identifier
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: «DBID»: invalid identifier

Is there any size limit for dba_audit_trail table ?
because i have around 700000 records..

on Dec 20, 2010 9:07 PM

I am not aware of any myself.

Sorry i checked it again and updated my reply above. PLease suggest. Its not just the connectivity problem with the Toad / PL/SQL but i get the same issue while i try login in as ‘system’ into my machine with putty..

I am only able to login using sysdba.

on Dec 20, 2010 9:11 PM

my entries in the init.ora file for audit are these :

audit_trail = DB
#audit_trail = none
audit_sys_operations = true

here is my output :
Do i need to change any value for any parameter ?

on Dec 20, 2010 9:23 PM

on Dec 20, 2010 9:24 PM

on Dec 20, 2010 9:24 PM

any suggesstions , replies.. .

What happens when you change the auditing logged to OS rather than to DB?

Sorry fo the late reply.. I changed audit_trail= Db_extended and also i changed the parameter audit_sys_operation to False from True .. But still the same issue.

I cant change it to OS since the OS is installed on some other location and maintained by some other organisation.. So i think we cant modify/add anything to there disk without there knowledge and without clients knowledge.. but we can make changes that we can do at our side.

I dont know what is the reason for this error 2002.. I checked everywhere , on metalink , on google.. everywhere its telling increase the system tablespace .. i increased system tablespace , also truncated aud$ table. now all are empty.. now it should work.

I am able to login only as sysdba..

do we need to give some specific audit grants to all users ?

Источник

Ora 02002 Error While Writing To Audit Trail

We have collected for you the most relevant information on Ora 02002 Error While Writing To Audit Trail, as well as possible solutions to this problem. Take a look at the links provided and find the solution that works. Other people have encountered Ora 02002 Error While Writing To Audit Trail before you, so use the ready-made solutions.

Reader: Unexpected Oracle error: ORA-02002: error while .

https://support.quest.com/shareplex/kb/205256/reader-unexpected-oracle-error-ora-02002-error-while-writing-to-audit-trail-ora-09817-write-to-audit-file-failed

The auditing facility is unable to write to the AUDIT_TRAIL table. If this error occurs, SQL statements that are currently being audited may also fail. This error will occur if …

ORA-02002: Error While Writing To Audit Trail on table .

https://support.oracle.com/knowledge/Oracle%20Database%20Products/1523813_1.html

Feb 13, 2019 · Oracle Database — Enterprise Edition — Version 10.2.0.3 and later: ORA-02002: Error While Writing To Audit Trail on table insert After Enabling Audit On Database

https://support.oracle.com/knowledge/Middleware/2628019_1.html

Oracle GoldenGate — Version 12.3.0.1.2 and later: Extract Abends after DB patching: ORA-02002: error while writing to audit trail

Ora-00600 Ora-02002 using AUDIT_TRAIL = XML

https://support.oracle.com/knowledge/Oracle%20Database%20Products/1386463_1.html

Ora-00600 Ora-02002 using AUDIT_TRAIL = XML (Doc ID 1386463.1) Last updated on FEBRUARY 27, 2019. Applies to: Oracle Database — Enterprise Edition — Version 11.2.0.2 and later Oracle Database Exadata Cloud Machine — Version N/A and later Oracle Cloud Infrastructure — Database Service — …

«ORA-02002: Error While Writing To Audit Trail» In Read .

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2299616_1.html

Aug 17, 2020 · Oracle Database — Enterprise Edition — Version 12.1.0.1 to 12.1.0.2 [Release 12.1]: «ORA-02002: Error While Writing To Audit Trail» In Read Only Transactions

Oracle DB Error ORA-02002 error while writing to audit trail

https://www.paked.net/q/detail/oracle-db-error-ora-02002-error-while-writing-to-audit-trail_1543.html

Oracle DB Error ORA-02002 error while writing to audit trail The following is the cause of this error: The auditing facility is unable to write to the AUDIT_TRAIL table. If this error occurs, SQL statements that are currently being audited may also fail.

ORA-02002 AUD: OS Error tips — Burleson Oracle Consulting

http://www.dba-oracle.com/t_ora_02002_aud_os_error.htm

Cause: The auditing facility is unable to write to the AUDIT_TRAIL table. If this error occurs, SQL statements that are currently being audited may also fail. This error will occur if the SYSTEM tablespace runs out of disk space. The key problem here is a lack of space, either in the OS syslog or in the Oracle database tablespaces.

AW: ORA-02002: error while writing to audit trail — oracle .

https://www.freelists.org/post/oracle-l/AW-ORA02002-error-while-writing-to-audit-trail

Nov 10, 2020 · AW: ORA-02002: error while writing to audit trail, oracle-l at FreeLists

ORA-02002 — orasite.com

https://orasite.com/errores/ORA/ORA-02002

ORA-02002: error while writing to audit trail

Ora 02002 Error While Writing To Audit Trail Fixes & Solutions

We are confident that the above descriptions of Ora 02002 Error While Writing To Audit Trail and how to fix it will be useful to you. If you have another solution to Ora 02002 Error While Writing To Audit Trail or some notes on the existing ways to solve it, then please drop us an email.

Источник

DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

ORA-02002 and ORA-00942 while upgrading OWM to 19c

This is a quick post about a problem that we have had while upgrading a DB to 19c.

At 91% of the upgrade, the OWM (Workspace Manager) upgrade was failing with this error error:

Indeed, executing the statement was leading consistently to this problem:

and we have had this result:

So, resuming the autoupgrade job was not a solution.

The view definition is:

but the package wmsys.ltUtil is wrapped, so no chance to understand what was happening.

As a quick fix, we have recompiled the binaries with mixed auditing:

and put the audit_trail=DB in the upgrade pfile (was NONE in this specific case).

After that, restarted the DB in upgrade mode using the same pfile.

After that, the view was giving no errors anymore and we resumed the autoupgrade job.

This is an old troubleshooting method that I call “Database Administration by guess”: I am not sure about the real cause, but the workaround just worked fine for us.

It would be interesting to know if anyone of you have had the same problem, and what were the auditing parameters in your case…

Ludovico

Latest posts by Ludovico (see all)

• Video: Where should I put the Observer in a Fast-Start Failover configuration? — November 29, 2022
• Video: The importance of Fast-Start Failover in an Oracle Data Guard configuration — November 29, 2022
• Find Ludovico at Oracle Cloud World 2022! — October 14, 2022

2 thoughts on “ ORA-02002 and ORA-00942 while upgrading OWM to 19c ”

Hi Ludovico,
I had the same issue while updating my 12cr1 instance (with OWM) to 19c.
What i did :
– test if OWM is used
– if not : remove it on 12c
– upgrade to 19c
-and reInstall it again

Now , i have an other issue with DVSYS schema :

…
2:01:58 13 END;
12:01:58 14
12:01:58 15 IF (simTblExists = TRUE) THEN
12:01:58 16 — If no simulation log records exist, drop the existing table. It is
12:01:58 17 — created in rdbms/admin/catmacc.sql.
12:01:58 18 IF (cnt = 0) THEN
12:01:58 19 EXECUTE IMMEDIATE ‘drop table dvsys.simulation_log$’;
12:01:58 20 ELSE
12:01:58 21 — If simulation log records exist, move them to
12:01:58 22 — dvsys.old_simulation_log$ table.
12:01:58 23 EXECUTE IMMEDIATE ‘ALTER TABLE dvsys.simulation_log$
12:01:58 24 RENAME TO old_simulation_log$’;
12:01:58 25 sys.dbms_registry.set_progress_value(‘DV’, ‘SIMULATION LOGS’,
12:01:58 26 ‘The existing simulation logs have been moved to dvsys.old_simulation_log$’);
12:01:58 27 END IF;
12:01:58 28 END IF;
12:01:58 29 END;
12:01:58 30 /
DECLARE
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-06512: at line 11
ORA-00942: table or view does not exist
ORA-00942: table or view does not exist
ORA-06512: at line 6
…

Have you met this situation ?

Hi Hafedh,
I cannot remember, but indeed your case looks very similar. Which type of audit trail do you have? Mixed or unified only?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Источник

Ora 02002 error while writing to audit trail

Users who have been granted the AUDIT_ADMIN role can manage the audit trail, archive the audit trail, and purge audit trail records.

• Managing the Unified Audit Trail
  Auditing is enabled by default, but you can control when audit records are written to disk.
• Archiving the Audit Trail
  You can archive the traditional operating system, unified database, and traditional database audit trails.
• Purging Audit Trail Records
  The DBMS_AUDIT_MGMT PL/SQL package can schedule automatic purge jobs, manually purge audit records, and perform other audit trail operations.
• Audit Trail Management Data Dictionary Views
  Oracle Database provides data dictionary views that list information about audit trail management settings.

23.1 Managing the Unified Audit Trail

Auditing is enabled by default, but you can control when audit records are written to disk.

• When and Where Are Audit Records Created?
  Auditing is always enabled. Oracle Database generates audit records during or after the execution phase of the audited SQL statements.
• Activities That Are Mandatorily Audited
  Certain security sensitive database activities are always audited and such audit configuration cannot be disabled.
• How Do Cursors Affect Auditing?
  For each execution of an auditable operation within a cursor, Oracle Database inserts one audit record into the audit trail.
• Writing the Unified Audit Trail Records to the AUDSYS Schema
  Oracle Database automatically writes audit records to an internal relational table in the AUDSYS schema.
• When Audit Records Are Written to the Operating System
  In situations where the database table is unable to accept unified audit records, these records will be written to operating system spillover audit files ( .bin format).
• Moving Operating System Audit Records into the Unified Audit Trail
  Audit records written to the spillover audit files can be moved to the unified audit trail database table.
• Disabling Unified Auditing
  You can disable unified auditing.

Related Topics

23.1.1 When and Where Are Audit Records Created?

Auditing is always enabled. Oracle Database generates audit records during or after the execution phase of the audited SQL statements.

Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.

To improve read performance of the unified audit trail, the unified audit records are written immediately to disk to an internal relational table in the AUDSYS schema. In the previous release, the unified audit records were written to SecureFile LOBs. If you had migrated to unified auditing in Oracle Database 12 c release 1 (12.1), then you can manually transfer the unified audit records from the SecureFile LOBS to this internal table. If the version of the database that you are using supports partitioned tables, then this internal table is a partitioned table. In this case, you can modify the partition interval of the table by using the DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL procedure. The partitioned version of this table is based on the EVENT_TIMESTAMP timestamp as a partition key with a default partition interval of one month. If the database version does not support partitioning, then the internal table is a regular, non-partitioned table.

The generation and insertion of an audit trail record is independent of the user transaction being committed. That is, even if a user transaction is rolled back, the audit trail record remains committed.

Statement and privilege audit options from unified audit policies that are in effect at the time a database user connects to the database remain in effect for the duration of the session. When the session is already active, setting or changing statement or privilege unified audit options does not take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

In contrast, changes to schema object audit options become immediately effective for current sessions.

By default, audit trail records are written to the AUDSYS schema in the SYSAUX tablespace. You can designate a different tablespace, including one that is encrypted, by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION procedure.

Writing the Unified Audit Trail Records to the AUDSYS Schema for more information about how audit records are written to AUDSYS

Oracle Database Upgrade Guide for information about transferring unified audit records after an upgrade

Источник

This is a quick post about a problem that we have had while upgrading a DB to 19c.

At 91% of the upgrade, the OWM (Workspace Manager) upgrade was failing with this error error:

Indeed, executing the statement was leading consistently to this problem:

and we have had this result:

So, resuming the autoupgrade job was not a solution.

The view definition is:

but the package wmsys.ltUtil is wrapped, so no chance to understand what was happening.

As a quick fix, we have recompiled the binaries with mixed auditing:

and put the audit_trail=DB in the upgrade pfile (was NONE in this specific case).

After that, restarted the DB in upgrade mode using the same pfile.

After that, the view was giving no errors anymore and we resumed the autoupgrade job.

This is an old troubleshooting method that I call “Database Administration by guess”: I am not sure about the real cause, but the workaround just worked fine for us.

It would be interesting to know if anyone of you have had the same problem, and what were the auditing parameters in your case…

—

Ludovico

Connected.

SQL> show user

USER is «SYS»

SQL> audit create session

  2  /

Audit succeeded.

SQL> select value from v$parameter

  2  where name = ‘audit_trail’

  3  /

VALUE

——————————————————-

TRUE

  2  /

  COUNT(*)

———-

         0

SQL> conn andrew/reid

Connected.

SQL> show user

USER is «ANDREW»

SQL> conn / as sysdba

Connected.

SQL> show user

USER is «SYS»

SQL> select count(*) from sys.aud$

  2  /

  COUNT(*)

———-

         2

  2  where owner = ‘SYS’

  3  and segment_name = ‘AUD$’

  4  /

  COUNT(*)

———-

         1

SQL> select next_extent, max_extents

  2  from dba_tables

  3  where owner = ‘SYS’

  4  and table_name = ‘AUD$’

  5  /

NEXT_EXTENT MAX_EXTENTS

———— ————

    1048576  2147483645

SQL>

  2  /

Table altered.

  2  /

alter table sys.aud$ storage (maxextents 10)

*

ERROR at line 1:

ORA-25150: ALTERING of extent parameters not permitted

  2  /

Table altered.

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:37:44 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 — 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> USER is «ANDREW»

SQL> Connected.

SQL> USER is «SYS»

SQL>   2

AUDIT_ROWS

———-

         4

SQL>   2    3    4

LOGONS_CUMULATIVE

——————

             4505

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 — 64bit Production

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:51:36 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 — 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> USER is «ANDREW»

SQL> ERROR:

ORA-02002: error while writing to audit trail

ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.AUD$ by 8 in tablespace USERS

Connected.

SQL> USER is «SYS»

SQL>   2

AUDIT_ROWS

———-

      3973

SQL>   2    3    4

LOGONS_CUMULATIVE

——————

             8484

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 — 64bit Production