Add, delete, modify, and query an Oracle 11g monitoring table

Add, delete, modify, and query an Oracle 11g monitoring table

Preface:
The data in a table in the online Oracle database is a little messy. No change was found based on the application database log and Application log, therefore, we decided to perform detailed insert, update, and delete monitoring on this single table.

I. Use the audit function provided by the database
1. Check whether the audit function is enabled.
SQL> show parameter audit

NAME TYPE VALUE
-----------------------------------------------------------------------------
Audit_file_dest string/oracle/app/oracle/admin/power
Des/adump
Audit_sys_operations boolean FALSE
Audit_syslog_level string
Audit_trail string NONE
SQL>
If the audit function is not enabled, You need to enable it yourself.

2. Enable the audit function
You need to use sysdba. Note that audit_trail must be DB_EXTENDED to record the specific statement to be executed...
Alter system set audit_sys_operations = TRUE scope = spfile;
SQL> alter system set audit_sys_operations = TRUE scope = spfile;

System altered.

SQL>

Check whether the audit function is enabled again
SQL> show parameter audit;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Audit_file_dest string/oracle/app/oracle/admin/power
Des/adump
Audit_sys_operations boolean FALSE
Audit_syslog_level string
Audit_trail string NONE
SQL>

You need to restart the instance to see the status.

3. Disable the audit function.
SQL> alter system set audit_trail = none scope = spfile;

4. Audit function for a table
Audit update, DELETE, insert on T_TEST by access;

5. Perform Various DML operations on the table

6. query audit information
Select EXTENDED_TIMESTAMP, SESSION_ID, SQL _TEXT from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC;

Ii. Trigger
We can see that the load on the online database has increased a lot compared with the previous one. It consumes resources to enable audit for a single table, and some of them are not cost-effective. So we can use another method to create a trigger on the table.

1. Create a test table first:
View the created tables aaa_test and trig_ SQL.
SQL> describe plas. aaa_test;
Name Null? Type
-----------------------------------------------------------------------------
ID NUMBER
NAME VARCHAR2 (100)
LOGIN_TIME DATE

SQL>
SQL> describe plas. trig_ SQL;
Name Null? Type
-----------------------------------------------------------------------------
LT DATE
SID NUMBER
SERIAL # NUMBER
USERNAME VARCHAR2 (30)
OSUSER VARCHAR2 (64)
MACHINE VARCHAR2 (32)
TERMINAL VARCHAR2 (16)
PROGRAM VARCHAR2 (64)
SQLTEXT VARCHAR2 (2000)
STATUS VARCHAR2 (30)
CLIENT_IP VARCHAR2 (60)

SQL>

2. Add an index to the trig_ SQL table:
Create index idx_time on plas. trig_ SQL (LT );

3. Create a trigger
Create or replace trigger pri_test
After insert or update or delete on plas. aaa_test
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
Insert into plas. trig_ SQL
Select sysdate, s. SID, s. SERIAL #, s. USERNAME, s. OSUSER,
S. MACHINE, s. TERMINAL, s. PROGRAM, q. SQL _text line,
'Insert ',
Sys_context ('userenv', 'IP _ address ')
From v $ SQL q, v $ session s
Where s. audsid = (select userenv ('sessionid') from dual)
And s. prev_ SQL _addr = q. address
AND s. PREV_HASH_VALUE = q. hash_value;
COMMIT;
ELSIF deleting then
Insert into plas. trig_ SQL
Select sysdate, s. SID, s. SERIAL #, s. USERNAME, s. OSUSER,
S. MACHINE, s. TERMINAL, s. PROGRAM, q. SQL _text line,
'Delete ',
Sys_context ('userenv', 'IP _ address ')
From v $ SQL q, v $ session s
Where s. audsid = (select userenv ('sessionid') from dual)
And s. prev_ SQL _addr = q. address
AND s. PREV_HASH_VALUE = q. hash_value;
COMMIT;
ELSIF updating then
Insert into plas. trig_ SQL
Select sysdate, s. SID, s. SERIAL #, s. USERNAME, s. OSUSER,
S. MACHINE, s. TERMINAL, s. PROGRAM, q. SQL _text line,
'Update ',
Sys_context ('userenv', 'IP _ address ')
From v $ SQL q, v $ session s
Where s. audsid = (select userenv ('sessionid') from dual)
And s. prev_ SQL _addr = q. address
AND s. PREV_HASH_VALUE = q. hash_value;
COMMIT;
End if;
END;

4. Start the data operation test:
Insert into plas. aaa_test1 select 2, 'Tom ', sysdate from dual;
Update plas. aaa_test1 a set a. name = 'Tom _ up' where a. id = 2;
Update plas. aaa_test a set a. name = 'Tom _ up1' where a. id = 1;
......
Commit;

5. view the table records and you will find the following:
SQL> select * from plas. trig_ SQL;
Lt sid serial # username osuser machine terminal program sqltext status CLIENT_IP
----------- ---------- ------------------------------ -------------------------------- -------------------- ---------------------------- Accept -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1273 33297 POWERDESK Administrator WORKGROUP \ WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin: id: = sys. dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
1273 33297 POWERDESK Administrator WORKGROUP \ WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin: id: = sys. dbms_transaction.local_transaction_id; end; DELETE 192.168.170.180
1273 33297 POWERDESK Administrator WORKGROUP \ WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin: id: = sys. dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
1352 40155 POWERDESK Administrator WORKGROUP \ WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin: id: = sys. dbms_transaction.local_transaction_id; end; INSERT 192.168.170.180
1273 33297 POWERDESK Administrator WORKGROUP \ WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin: id: = sys. dbms_transaction.local_transaction_id; end; UPDATE 192.168.170.180
1273 33297 POWERDESK Administrator WORKGROUP \ WIN-18P5Q5AREH9 WIN-18P5Q5AREH9 plsqldev.exe begin: id: = sys. dbms_transaction.local_transaction_id; end; UPDATE 192.168.170.110
25 39527 SYS oracle localhost. localdomain pts/1 sqlplus@localhost.localdomain (TNS V1-V3) update plas. aaa_test a set a. name = 'Tom _ Update' where id = 2 update
25 39527 SYS oracle localhost. localdomain pts/1 sqlplus@localhost.localdomain (TNS V1-V3) update plas. aaa_test a set a. name = 'Tom _ update3 'where id = 3 UPDATE
8 rows selected

SQL>

PS: Some SQLTEXT values are begin: id: = sys. dbms_transaction.local_transaction_id; end. This is because the insert、delete、updatesentence executed on the plsqldev.exe client is not recorded. Some of the update statements that have been executed are recorded when they are executed over a sqlplus@localhost.localdomain (TNS V1-V3) Client connection.

6. Check which users and ip addresses are performing dml operations.
SQL> select username, client_ip from plas. trig_ SQL group by username, client_ip;
USERNAME CLIENT_IP
------------------------------------------------------------------------------------------
PLAS 192.168.170.180
DESKER 192.168.170.110

SQL>

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian