Oracle无法open的恢复记录-转

原创张鹏 2023-08-10

217

Oracle Audit 审计功能

Oracle Audit 审计功能的认识与使用
1.Audit的概念
Audit是监视和记录用户对数据库进行的操作，以供DBA进行问题分析。利用Audit功能，可以完成以下任务：
监视和收集特定数据库活动的数据。例如管理员能够审计哪些表被更新，在某个时间点上有多少个并行用户统计数据；
保证用户对自己的活动负责。这些活动包括在特定模式、特定表、特定行等对象上进行的操作；
审计数据库中的可疑活动。如一个未经授权的用户正从表中删除数据，那么数据库管理员必须审计所有数据库连接，以及在数据库中所有成功和失败的删除操作。
根据审计类型不同，审计记录中的信息也有所不同。通常，一条审计记录中包含用户名、会话标识、终端标识、所操作的模式对象名称、执行的操作、执行的完整语句代码、日期和时间戳、所使用的系统权限。

2.Audit的分类
在 11g中，一共有4种审计类型：
语句审计（Statement Auditing）：对特定的SQL语句进行审计，不指定具体对象；
权限审计（Privilege Auditing）：对特定的系统权限使用情况进行审计；
对象审计（Object Auditing）：对特定的模式对象上执行的特定语句进行审计；
网络审计（Network Auditing）：对网络协议错误与网络层内部错误进行审计。
此外，根据用户是否成功执行，可以分为对执行成功的语句进行审计、对不成功的语句进行审计、无论成功与否都进行审计。
根据对同一个语句审计次数不同，可以分为会话审计和存取审计。会话审计是指对某个用户或所有用户的同一语句只审计一次，形成一条审计记录；存取审计是指对某个用户或所有用户的同一语句每执行一次审计一次，形成多条审计记录。

3.审计环境设置
使用审计功能，需要对数据库初始化参数AUDIT_TRAIL进行设置，其参数值可以为：
none：不启用审计功能；
db：启用审计功能，审计信息写入sys.aud$数据字典中，审计的结果只有连接信息（sys用户的记录以及强制性要求的记录都写入操作系统文件中）；
db_extended：审计结果除了有连接信息，还有执行的具体语句。
os：启用审计功能，审计信息写入操作系统文件；
xml：启用审计功能，审计信息写入xml格式的操作系统文件中；
查看是否启用了审计功能：
SQL> show parameter audit_trail
NAME TYPE VALUE

audit_trail string DB
修改审计环境参数：

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 3741306880 bytes
Fixed Size 2258800 bytes
Variable Size 1241516176 bytes
Database Buffers 2483027968 bytes
Redo Buffers 14503936 bytes
Database mounted.

4.审计详解
4.1 语句审计(Statement auditing)
（1）语句审计是对特定的SQL语句进行审计，与具体的对象没有关系。创建语句审计的基本语法为：
AUDIT
sql_statement_
shortcut | ALL |
ALL STATEMENTS
[ BY user_lists ] | [ IN SESSION CURRENT ] [ BY SESSION | ACCESS ] [ WHENEVER [NOT ] SUCCESSFUL]
解释：

sql_statement_shortcut：被审计的SQL语句的快捷方式；
ALL：审计大部分SQL语句，这里不在列出；
ALL STATEMENT：对最高级别的SQL语句进行审计，即对直接执行的SQL语句进行审计，而不对包含在PL/SQL程序中的SQL语句进行审计；
BY user_lists：指定审计的用户，如果不指定，则审计全部用户；
IN SESSION CURRENT：只对当前会话进行审计；
BY SESSION：会话审计，同一个SQL语句只审计一次；
BY ACCESS：存取审计，同一个SQL语句执行几次就审计几次；
WHENEVER SUCCESSFUL：只审计执行成功的SQL语句；
WHENEVER NOT SUCCESS：只审计执行不成功的SQL语句；
（2）如果要取消对某个语句的审计，只需将AUDIT命令改为NOAUDIT命令即可，其语法与创建AUDIT相同。
（3）通过数据字典DBA_STMT_AUDIT_OPTS可以了解当前数据库哪些用户进行了语句审计及审计设置信息。例如，查看与scott用户相关的语句审计：

SQL> select * from dba_stmt_audit_opts where user_name=‘SCOTT’;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE

SCOTT TABLE BY ACCESS BY ACCESS
SCOTT INSERT TABLE BY ACCESS BY ACCESS

例子1.在scott模式下创建表test02，查看其审计信息。
查看audit_trace参数
SQL> show parameter audit_trail

NAME TYPE VALUE

audit_trail string DB
step1.对scott用户创建语句审计

audit tableby scottby access
audit insert tableby scottby access

step2.创建表test02，插入2行数据：

SQL> show user;User is “SCOTT”

SQL> create table test02
(
id number,
name varchar(40),
local varchar(50)
);
Table created
SQL> commit;
Commit complete

SQL> insert into test02 values(1,‘lihua’,‘chengdu’);
SQL> insert into test02 values(2,‘ll’,‘dd’);
1 row inserted

SQL> commit;Commit complete

step3.查看sys.aud$和sys.audit_actions视图

select
a.userid,
a.userhost,
a.terminal,
a.action#,
aa.name,
dbms_lob.substr(a.sqltext) as sqltext,
dbms_lob.substr(a.sqlbind) as sqlbind,
a.obj $creator, a.obj$ name,
a.ntimestamp#+8/24from
sys.aud$ a,
sys.audit_actions aawhere
a.obj$name = 'TEST02’and
a.action# = aa.actionand
a.ntimestamp# > to_date(‘20170412’,‘yyyymmdd’);

结果为：

USERID USERHOST TERMINAL ACTION# NAME SQLTEXT SQLBIND OBJ $CREATOR OBJ$ NAME A.NTIMESTAMP#+8/24------- -------------------------- ---------------- ------- ------------- -------------------------------------- -------- ----------- -------- ------------------SCOTT WORKGROUP\DESKTOP-TKAPD8E DESKTOP-TKAPD8E 1 CREATE TABLE SCOTT TEST02 2017/4/15 13:58:54SCOTT WORKGROUP\DESKTOP-TKAPD8E DESKTOP-TKAPD8E 2 INSERT SCOTT TEST02 2017/4/15 14:00:00SCOTT WORKGROUP\DESKTOP-TKAPD8E DESKTOP-TKAPD8E 2 INSERT SCOTT TEST02 2017/4/15 15:27:26

结合sys.aud$和sys.audit.actions，我们可与看到对数据库进行了create table和2次insert操作，但是我们还是不知道具体信息。接下来，我们将audit_trail参数改为：audit_trail = db_extended。
step4.修改audit_trail参数

SQL> alter system set audit_trail = ‘DB_EXTENDED’ scope = spfile;
System altered

SQL> shutdown immediate ;
SQL>startup;
SQL> show parameter audit_trail
NAME TYPE VALUE------------------------------------ ----------- ------------------------------audit_trail string DB_EXTENDED

此时，再向test02表插入1行数据
SQL> insert into test02 values(3,‘cc’,‘vv’);1 row inserted

SQL> commit;Commit complete
step5.再次查看sys.aud$和sys.audit_actions视图

可以看到，在SQLTEXT栏位有了具体的执行SQL语句。

4.2 权限审计
（1）权限审计是对特定的系统权限进行审计，语法为：
AUDIT
system_privilege |
[ ALL PRIVILEGES ] [ BY user_lists ] | [ IN SESSION CURRENT ] [ BY SESSION | ACCESS ] [ WHENEVER [ NOT ] SUCCESSFUL]
（2）如果要了解当前数据库中对哪些用户使用了什么权限审计，可以通过数据字典DBA_PRIV_AUDIT_OPTS来查看。
例子2.对scott用户的select any table权限进行审计
step1.查看scott的系统权限

SQL> select * from dba_sys_privs where grantee = ‘SCOTT’;

GRANTEE PRIVILEGE ADMIN_OPTION------------------------------ ---------------------------------------- ------------SCOTT CREATE ANY TABLE NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE ANY VIEW NO

step2.使用scott用户，在模式‘LIJIAMAN’下创建表test03，查询其审计信息：

step3.使用scott用户，在scott下创建表test05,查询其审计信息：

通过这个这个例子，我们可以看到，只要scott用户使用select any table权限，我们就可以通过审计得到其操作信息。

4.3 对象审计
（1）对象审计是指对特定模式对象的操作进行审计，与用户无关，语法为：
AUDIT
sql_operation |
ALL ON [schema.]object ] [ BY user_lists ] | [ IN SESSION CURRENT ] [ BY SESSION | ACCESS ] [ WHENEVER [ NOT ] SUCCESSFUL]
其中，sql_operation指定了特定对象上要审计的SQL语句。
（2）如果要查看当前数据库哪些模式对象进行了对象审计，可以通过查询DBA_OBJ_AUDIT_OPTS获得。
例子3.对模式scott下的表dept进行对象审计
SQL> audit all on scott.dept by session;

Audit succeeded
查看其审计信息：

USERID USERHOST TERMINAL ACTION# NAME SQLTEXT SQLBIND OBJ $CREATOR OBJ$ NAME A.NTIMESTAMP#+8/24---------------- -------------------------- ---------------- ---------- ----------- ---------------------------------- -------- ------------ ---------- ------------------LIJIAMAN WORKGROUP\DESKTOP-TKAPD8E DESKTOP-TKAPD8E 103 SESSION REC select * from scott.dept SCOTT DEPT 2017/4/15 20:13:47LIJIAMAN WORKGROUP\DESKTOP-TKAPD8E DESKTOP-TKAPD8E 103 SESSION REC audit all on scott.dept by session SCOTT DEPT 2017/4/15 20:13:19

4.4 网络审计
网络审计对协议错误与网络层内部错误进行审计，网络审计捕获客户端与服务器通信过程中发生的错误，这些错误由SQL*NET网络服务抛出。网络审计的语法为：
AUDIT NETWORK
[BY SESSION | ACCESS][WHENEVER [NOT] SUCCESSFUL]

如何手动清理Audit数据。
（一）概述
Audit的数据主要存储在sys.aud表中，该表默认位于system表空间中，我们根据需求，将该表移到了sysaux表空间中。由于审计数据量较大，需要经常关注sysaux表空间的使用情况,同时根据实际情况对sys.aud表进行数据清理。
（二）清理步骤
（1）使用sys账号登陆数据库，打开计时功能，方便查看每一个命令的执行时间
SQL> set timing on
（2）在清理数据之前先查看数据量大小
SQL> select count(*) from sys.aud; （3）查看Audit表中最早一笔数据的时间,即审计表中记录的最早的时间 SQL> select min(ntimestamp#) from sys.aud;MIN(NTIMESTAMP#)----------------------------------------23-11月-16 08.18.54.496893 上午
（4）查看审计数据最后归档时间，只有归档的数据才能删除
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS----------- ------------ --------------------------------STANDARD AUDIT TRAIL 0 14-3月 -17 01.22.49.000000 下午 +00:00
（5）初始化清理Audit的功能，该命令只有在第一次执行时需要运行，default_cleanup_interval =>168代表清理周期为168小时。

SQL> BEGIN
sys.DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 168);END;/
（6）确认清除Audit功能是否开启，yes为开启
SQL >
SET SERVEROUTPUT ON
BEGIN
IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line( ’ YES ’ );
ELSE
DBMS_OUTPUT.put_line( ’ NO ’ );
END IF ;
END ;
/
（7）设置需要清理的天数，最后一个数字‘7’代表清理‘7’天前的数据（归档时间大于等于清除时间）
SQL >
BEGIN
sys.DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP - 7 /* Day */ );
END ;
/
(8) 执行清除，时间长短受数据量大小影响（经测试600万条数据大约20分钟）
SQL >
BEGIN
sys.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END ;
/
(9)在清理后，sys.aud表的大小并没有发生改变，需要收回空间解释：在上面清理表sys.aud后，实际上，数据还在磁盘上，只是数据不受保护了而已，其空间并没有释放，需要将其释放回收

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD'; --查看aud的大小SEGMENT_NAME BYTES/1024/1024-------------------------------------------------------------------------------- ---------------AUD$ 3456
SQL> alter table sys.aud$ enable row movement; --激活行移动Table altered.
SQL> alter table sys.aud$ shrink space cascade; --进行空间回收Table altered.
SQL> alter table sys.aud$ disable row movement; --关闭行移动Table altered.
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='AUD'; ----再次查看aud的大小SEGMENT_NAME BYTES/1024/1024-------------------------------------------------------------------------------- ---------------AUD$ 666.8125
经过2次查看空间大小，可以看到空间已经被释放。
参考：

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

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle无法open的恢复记录-转

评论