audit_trail与extended！

最新推荐文章于 2023-11-20 15:53:16 发布

clt3617

最新推荐文章于 2023-11-20 15:53:16 发布

阅读量683

点赞数

文章标签：数据库

在修改参数audit_trail=xml,extended时千万不要加单引号...[@more@]

受到v$parameter_valid_values显示结果的误导，我以为EXTENDED是一个单独的参数内容，所以
直接修改audit_trail=extended时实例无法启动，最后修改audit_trail='XML,EXTENDED'重启实例时依然发生了
错误ORA-01078，原因就是加上了单引号：
SQL> select * from v$parameter_valid_values where name='audit_trail';

NUM NAME ORDINAL VALUE ISDEFAULT
---------- --------------- ---------- -------------------- ----------
907 audit_trail 1 DB FALSE
907 audit_trail 2 OS FALSE
907 audit_trail 3 NONE FALSE
907 audit_trail 4 TRUE FALSE
907 audit_trail 5 FALSE FALSE
907 audit_trail 6 DB_EXTENDED FALSE
907 audit_trail 7 XML FALSE
907 audit_trail 8 EXTENDED FALSE

已选择8行。

SQL>
--================================
doc对参数audit_trail的解释：
AUDIT_TRAILProperty Description
Parameter type String
Syntax AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
Default value none
Modifiable No
Basic No

AUDIT_TRAIL enables or disables database auditing.

Values:

none

Disables database auditing.

Enables database auditing and directs all audit records to the operating system's audit trail.

Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).

db,extended

Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

xml

Enables database auditing and writes all audit records to XML format OS files.

xml,extended

Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.

You can use the SQL statement AUDIT to set auditing options regardless of the setting of this parameter.
--=================================
SQL> show parameter audit

系统已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 79692940 bytes
Database Buffers 121634816 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string G:ORACLEPRODUCT10.2.0ADMIN
TESTADUMP
audit_sys_operations boolean FALSE
audit_trail string XML, EXTENDED
SQL>
--===========================
oracle太恶心了，db_extended是个独立的参数选项，而xml和extended结合呢又是
xml,extended，真实莫名其妙，另外v$parameter_valid_values的显示结果又把extended单独列了出来，
不看文档的话还真不知道extended是对xml的扩展。

--===============================

SQL> show parameter audit

系统已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

--=============================

后来的补充：

其实EXTENDED还可以和db组合起来使用，所以oracle把EXTENDED单独列出来可能也是有道理的，EXTENDED不能和os组合：

SQL> alter system set audit_trail=db,extended scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size 1374304 bytes
Variable Size 159385504 bytes
Database Buffers 146800640 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL> show parameter audit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string E:ORACLEADMINTESTADUMP
audit_sys_operations boolean FALSE
audit_trail string DB, EXTENDED

SQL> alter system set audit_trail=os,extended scope=spfile;
alter system set audit_trail=os,extended scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00096: invalid value OS,EXTENDED for parameter audit_trail, must be from
among NONE | OS | DB | DB,EXTENDED | XML | XML,EXTENDED

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1051505/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1051505/