Audit parameter audit_trail in Oracle

 

I always wanted to provide an interface on/off audit at the application system layer. That is to say, you can enable or disable the database audit function at the application system level. However, after several days of trying to enable or disable the audit function, we can conclude that the audit_trail parameter is not a dynamic modification parameter, that is, when modifying through alter system, only scope = spfile can be added. That is to say, if you want to enable/disable the audit function, you must restart the database ....

In this case, you naturally cannot flexibly enable/disable the audit function, so you cannot provide a truly valuable on/off interface to the application system layer. Because the database needs to be restarted every time, this has a great impact on the operation of the application system.

The following is the result of my experiment in Oracle 10g (because 9i always encounters an error when it is disabled and then started again, the specific reason is unknown ......), I think, 10 Gb is still the same, so 9i is definitely not playing ......

-- Display the current audit_trail parameter value first

 

SQL> show parameter audit_trail NAME TYPE VALUE audit_trail string DB

-- Modify the parameter value of audit_trail

 

SQL> alter system set audit_trail='FALSE'; alter system set audit_trail='FALSE'

 

ERROR at line 1: ORA-02095: specified initialization parameter cannot be modifie

 

SQL> alter system set audit_trail='FALSE' scope = both; alter system set audit_trail='FALSE' scope = both

 

ERROR at line 1: ORA-02095: specified initialization parameter cannot be modifie

-- Only spfile can be modified.

 

SQL> alter system set audit_trail='FALSE' scope=spfile;

System altered.

-- The audit_trail parameter value is displayed, indicating that the current instance has not been changed.

 

SQL> show parameter audit_trail NAME TYPE VALUE audit_trail string DB SQL> shutdown normal ORA-01031: insufficient privileges SQL> conn / as sysdba; Connected. SQL> shutdown normal; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 171966464 bytes Fixed Size 787988 bytes Variable Size 144964076 bytes Database Buffers 25165824 bytes Redo Buffers 1048576 bytes Database mounted. Database opened.

-- After the restart, the audit_trail parameter value has been changed ......

 

SQL> show parameter audit_trail; NAME TYPE VALUE audit_trail string FALSE

Here, we only describe the initialization and modification of an audit parameter. If there are Oracle experts passing by, how can we perform such operations to enable dynamic modification, or is there any other solution to implement audit, instead of restarting the database every time .......