ANBOB™

今天朋友问我个有意思的问题，
10g r2版本的库修改一个审计参数时很费解，下面看我的还原问题

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 31 10:33:04 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@ANBOB>show parameter audit

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
audit_file_dest                      string                 /u01/app/oracle/admin/anbob/ad
                                                            ump
audit_sys_operations                 boolean                FALSE
audit_syslog_level                   string
audit_trail                          string                 NONE

我看们一下官方文档的参数值

Oracle® Database Reference
10g Release 2 (10.2)
Part Number B14237-04

AUDIT_TRAIL
Property 	Description
Parameter type 	String
Syntax 	AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
Default value 	none
Modifiable 	No
Basic 	No

sys@ANBOB>alter system set audit_trail ='db,extended' scope=spfile;
alter system set audit_trail ='db,extended' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value db,extended for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db

这个错误很蹊跷
Oracle® Database Reference
10g Release 1 (10.1)
Part Number B10755-01 	

AUDIT_TRAIL
Property 	Description
Parameter type 	String
Syntax 	AUDIT_TRAIL = { db | os | none | true | false | db_extended }

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.

可以看到这个值是10.1版本中的
sys@ANBOB>show parameter compa

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
compatible                           string                 10.2.0.1.0
plsql_v2_compatibility               boolean                FALSE

我先设置成db_extended 试试
sys@ANBOB>alter system set audit_trail ='db_extended' scope=spfile;

System altered.

sys@ANBOB>startup force 
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  1223392 bytes
Variable Size             532677920 bytes
Database Buffers          511705088 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

为什么不在文档中的值也可以呢？再试试其它的
sys@ANBOB>alter system set audit_trail ='anbob' scope=spfile;
alter system set audit_trail ='anbob' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value anbob for parameter audit_trail, must be from among extended, xml, db_extended, false, true, none, os, db


sys@ANBOB>alter system set audit_trail ='extended' scope=spfile;

System altered.

sys@ANBOB>startup force
ORA-01078: failure in processing system parameters
悲剧了，手动改下参数吧
sys@ANBOB>create pfile from spfile
  2  ;
ERROR:
OCI-21710: argument is expecting a valid memory address of an object



File created.
[oracle@dbserver1 ~]$ cd $ORACLE_HOME/dbs
[oracle@dbserver1 dbs]$ vi initanbob.ora 
modify *.audit_trail='extended' to *.audit_trail='none'

[oracle@dbserver1 dbs]$ rm spfileanbob.ora
idle>create spfile from pfile
  2  ;

File created.

idle>startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  1223392 bytes
Variable Size             541066528 bytes
Database Buffers          503316480 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

sys@ANBOB>audit all on anbob.obj;

Audit succeeded.

sys@ANBOB>select count(*) from anbob.obj;

  COUNT(*)
----------
     56593

sys@ANBOB>conn anbob/anbob
Connected.
anbob@ANBOB>delete obj where object_id=300;

1 row deleted.

anbob@ANBOB>commit;

Commit complete.

anbob@ANBOB>conn / as sysdba
Connected.
sys@ANBOB>select sqltext from aud$;

SQLTEXT
--------------------------------------------------------------------------------
delete obj where object_id=300

note: my system initparameter audit_sys_operations = FALSE

sys@ANBOB>truncate table aud$;

Table truncated.

sys@ANBOB>noaudit all on anbob.obj;

Noaudit succeeded.

是文档写错了？不是的，后来我发现了原因
sys@ANBOB>alter system set audit_trail =db,extended scope=spfile;

System altered.

sys@ANBOB>startup force
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  1223392 bytes
Variable Size             549455136 bytes
Database Buffers          494927872 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
sys@ANBOB>show parameter audit_tr

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
audit_trail                          string                 DB, EXTENDED

note:只是ORACLE的错误提示信息在这个版本中没有更新，希望以后出类似问题还是去看看官方文档给出的值

打赏

微信扫一扫，打赏作者吧～