Real case: Audit of table insertion operations in Oracle databases

In Oracle 10 Gb, the following table's OBJECT_ID_FACTORY record is repeated. This is a dictionary table used to generate record primary key values for some tables. Duplicate key values cause some business operations to fail.

This problem has occurred once before. Logminer cannot find the operation application, so we use audit technology to solve this problem.

For how to use logminer, see this article. Link: or

1. Solution Process

In the Oracle 10g database, this table records an exception, adding a record for no reason. Logminer cannot be used to find out what the application is doing. Therefore, we enable the audit function to capture incorrect operations to solve this problem.

The audit function of Oracle is an advanced option, which is disabled by default. You need to modify the audit class initialization parameters to use it.

Step 1: Check audit parameters

SQL> show parameter audit

NAME TYPE VALUE

-----------------------------------------------------------------------------

Audit_file_dest string/u01/app/oracle/admin/zxdb/adu

Mp

Audit_sys_operations boolean TRUE

Audit_syslog_level string

Audit_trail string DB

If audit_trail is empty, you need to modify and restart the instance to make it take effect before continuing the Object audit operation.

Adjust the tablespace of the storage table aud $ for audit results. It turns out to be a system tablespace, so you need to migrate it to another tablespace to prevent the system tablespace from soaring.

SQL> alter table sys. aud $ move tablespace users;

Step 2: Set object Operation Audit

Audit the insert operations of each access to the table OBJECT_ID_FACTORY in bkjia, whether successful or failed, are recorded;

Audit the update operations of each session in the table OBJECT_ID_FACTORY of bkjia user, whether successful or failed, are recorded;

Audit insert on bkjia. OBJECT_ID_FACTORY by access;

Audit update on bkjia. OBJECT_ID_FACTORY; -- by session [WHENEVER [NOT] SUCCESSFUL] can be omitted.

Step 3: check which objects, actions, and permissions are audited.

Select * from dba_stmt_audit_opts;

Select * from dba_priv_audit_opts;

Select * from dba_obj_audit_opts;

For example, the following table OBJECT_ID_FACTORY of bkjia sets audit for insert and update operations.

SQL> Select * from dba_obj_audit_opts;

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE

Crere REA WRI FBK

-------------------------------------------------

Bkjia OBJECT_ID_FACTORY TABLE-/-A/A-/-S/S -/-- /-

-/--/--/--/-

Step 4: Use noaudit to cancel the audit of object operations

Noaudit insert on bkjia. OBJECT_ID_FACTORY by access;

Noaudit update on bkjia. OBJECT_ID_FACTORY;

If the audit objectives have been achieved, you need to close the audit settings in time. This is important and should be noted during implementation.

1 2 Next Page