Real case: Audit of table insertion operations in Oracle databases

In Oracle10g, the record of a user's table OBJECT_ID_FACTORY is repeated again. This is a dictionary table used to generate record primary key values for some tables. Duplicate key-value export

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-value export

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

Audits the insert operations for each access to the table OBJECT_ID_FACTORY in the linuxidc User table, regardless of whether the operation is successful or failed;

Audits the update operations of each session in the table OBJECT_ID_FACTORY in linuxidc, regardless of whether the session is successful or failed;

Audit insert on linuxidc. OBJECT_ID_FACTORY by access;

Audit update on linuxidc. 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;

As shown in the following table, OBJECT_ID_FACTORY, the user of linuxidc 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

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

Objecidc OBJECT_ID_FACTORY TABLE-/-A/A-/-S/S -/-- /-

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

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

Noaudit insert on linuxidc. OBJECT_ID_FACTORY by access;

Noaudit update on linuxidc. 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.