Oracle Migrate AUDIT & FGA Tablespace
来源：互联网 发布：淘宝网企业店铺靠谱吗 编辑：程序博客网 时间：2024/06/11 16:59
Oracle 10g audit与FGA基表默认的表空间为system,如果开启audit或FGA后,会给system带来使用空间上的压力.建议将audit和FGA的基表表空间进行迁移.
迁移包含两部分内容
(1),table
(2),index
1,查询audit和FGA基表,在sys下查询user_tables
13-MAR-12 SYS @ hdb>select table_name,tablespace_name from user_tables where table_name LIKE '%AUD%' or table_name like '%FGA%';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUDIT$ SYSTEM
STMT_AUDIT_OPTION_MAP SYSTEM
FGA$ SYSTEM
FGACOL$ SYSTEM
AUD$ SYSTEM
FGA_LOG$ SYSTEM
AUDIT_ACTIONS SYSTEM
audit的基表:
(1),AUD$
(2),AUDIT$
(3),AUDIT_ACTIONS
FGA的基表:
(1),FGA$
(2),FGACOL$
(3),FGA_LOG$
要迁移的就是这6张表,计划从system表空间分别迁移到AUDIT_TS和FGA_TS表空间.
2,查询索引,在sys下查询user_ind_column
13-MAR-12 SYS @ hdb>col column_name for a30
13-MAR-12 SYS @ hdb>select table_name,index_name,column_name from user_ind_columns where table_name like '%AUD%' or table_name like '%FGA%';
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
AUDIT$ I_AUDIT OPTION#
AUDIT$ I_AUDIT PROXY#
AUDIT$ I_AUDIT USER#
STMT_AUDIT_OPTION_MAP I_STMT_AUDIT_OPTION_MAP NAME
STMT_AUDIT_OPTION_MAP I_STMT_AUDIT_OPTION_MAP OPTION#
FGA$ I_FGA OBJ#
FGA$ I_FGAP PNAME
FGA$ I_FGAP OBJ#
FGACOL$ I_FGACOL INTCOL#
FGACOL$ I_FGACOL PNAME
FGACOL$ I_FGACOL OBJ#
AUD$ I_AUD1 SES$TID
AUD$ I_AUD1 SESSIONID
AUDIT_ACTIONS I_AUDIT_ACTIONS NAME
AUDIT_ACTIONS I_AUDIT_ACTIONS ACTION
可以看见6张表对应的索引
(1),AUD$ -- I_AUD1
(2),AUDIT$ -- I_AUDIT
(3),AUDIT_ACTIONS-- I_AUDIT_ACTIONS
(4),FGA$ -- I_FGA,I_FGAP
(5),FGACOL$ -- I_FGACOL
(6),FGA_LOG$ -- 无
3,查询索引所在的表空间
13-MAR-12 SYS @ hdb>select segment_name index_name,tablespace_name,segment_type from user_segments where segment_type = 'INDEX' and (segment_name like '%AUD%' or segment_name like '%FGA%');
INDEX_NAME TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
I_STMT_AUDIT_OPTION_MAP SYSTEM INDEX
I_FGA SYSTEM INDEX
I_FGAP SYSTEM INDEX
I_FGACOL SYSTEM INDEX
I_AUD1 SYSTEM INDEX
I_AUDIT SYSTEM INDEX
I_AUDIT_ACTIONS SYSTEM INDEX
7 rows selected.
查询结果显示,以上的索引全部在system表空间.
4,迁移audit的表和索引,在sys下执行.
13-MAR-12 SYS @ hdb>create tablespaceAUDIT_TSdatafile '/u01/app/oracle/oradata/hdb/AUDIT_TS.ora' size 16m reuse
autoextend on maxsize unlimited;
13-MAR-12 SYS @ hdb>alter table aud$ move tablespace AUDIT_TS;
Table altered.
13-MAR-12 SYS @ hdb>alter index I_AUD1 rebuild online tablespace AUDIT_TS;
Index altered.
13-MAR-12 SYS @ hdb>alter table AUDIT$ move tablespace AUDIT_TS;
Table altered.
13-MAR-12 SYS @ hdb>alter index I_AUDIT rebuild online tablespace AUDIT_TS;
Index altered.
13-MAR-12 SYS @ hdb>alter table AUDIT_ACTIONS move tablespace AUDIT_TS;
Table altered.
13-MAR-12 SYS @ hdb>alter index I_AUDIT_ACTIONS rebuild online tablespace AUDIT_TS;
Index altered.
5,迁移FGA的表和索引,在sys下执行.
13-MAR-12 SYS @ hdb>create tablespaceFGA_TSdatafile '/u01/app/oracle/oradata/hdb/FGA_TS.ora' size 16m reuse
autoextend on maxsize unlimited;
13-MAR-12 SYS @ hdb>alter table FGA$ move tablespace FGA_TS;
Table altered.
13-MAR-12 SYS @ hdb>alter index I_FGA rebuild online tablespace FGA_TS;
Index altered.
13-MAR-12 SYS @ hdb>alter index I_FGAP rebuild online tablespace FGA_TS;
Index altered.
13-MAR-12 SYS @ hdb>alter table FGACOL$ move tablespace FGA_TS;
Table altered.
13-MAR-12 SYS @ hdb>alter index I_FGACOL rebuild online tablespace FGA_TS;
Index altered.
13-MAR-12 SYS @hdb>alter table FGA_LOG$ move tablespace FGA_TS;
alter table FGA_LOG$ move tablespace FGA_TS
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
6,结果除了FGA_LOG$没有迁移成功外,其他的表和索引都成功.
13-MAR-12 SYS @ hdb>select table_name,tablespace_name from user_tables where table_name LIKE '%AUD%' or table_name like '%FGA%';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STMT_AUDIT_OPTION_MAP SYSTEM
FGA_LOG$ SYSTEM
AUD$ AUDIT_TS
AUDIT$ AUDIT_TS
AUDIT_ACTIONS AUDIT_TS
FGA$ FGA_TS
FGACOL$ FGA_TS
7 rows selected.
13-MAR-12 SYS @ hdb>select segment_name index_name,tablespace_name,segment_type from user_segments where segment_type = 'INDEX' and (segment_name like '%AUD%' or segment_name like '%FGA%');
INDEX_NAME TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
I_STMT_AUDIT_OPTION_MAP SYSTEM INDEX
I_AUD1 AUDIT_TS INDEX
I_AUDIT AUDIT_TS INDEX
I_AUDIT_ACTIONS AUDIT_TS INDEX
I_FGAP FGA_TS INDEX
I_FGA FGA_TS INDEX
I_FGACOL FGA_TS INDEX
7,解决上述问题,继续中...
ORA-00997: illegal use of LONG datatype导致出现这个问题的原因是FGA_LOG$中含有LONG字段,而Oracle10g及之前的版本在处理LONG时不能够进行迁移table的表空间;同时含有LONG时在进行create table table_name as select,语句也无法使用.
需要按照下面的方法进行迁移
(1),在FGA_TS中创建表FGA_LOGNEW$;可以通过sql/develop收集原有的FGA_LOG$的DDL语句,将表名改为FGA_LOGNEW$,如下.
CREATE TABLE "SYS"."FGA_LOGNEW$" (
"SESSIONID" NUMBER NOT NULL ENABLE,
"TIMESTAMP#" DATE,
"DBUID" VARCHAR2(30 BYTE),
"OSUID" VARCHAR2(255 BYTE),
"OSHST" VARCHAR2(128 BYTE),
"CLIENTID" VARCHAR2(64 BYTE),
"EXTID" VARCHAR2(4000 BYTE),
"OBJ$SCHEMA" VARCHAR2(30 BYTE),
"OBJ$NAME" VARCHAR2(128 BYTE),
"POLICYNAME" VARCHAR2(30 BYTE),
"SCN" NUMBER,
"SQLTEXT" VARCHAR2(4000 BYTE),
"LSQLTEXT" CLOB,
"SQLBIND" VARCHAR2(4000 BYTE),
"COMMENT$TEXT" VARCHAR2(4000 BYTE),
"PLHOL" LONG,
"STMT_TYPE" NUMBER,
"NTIMESTAMP#" TIMESTAMP (6),
"PROXY$SID" NUMBER,
"USER$GUID" VARCHAR2(32 BYTE),
"INSTANCE#" NUMBER,
"PROCESS#" VARCHAR2(16 BYTE),
"XID" RAW(8),
"AUDITID" VARCHAR2(64 BYTE),
"STATEMENT" NUMBER,
"ENTRYID" NUMBER,
"DBID" NUMBER,
"LSQLBIND" CLOB
) TABLESPACE FGA_TS;
如下表明创建成功
13-MAR-12 SYS @ hdb>select table_name,tablespace_name from user_tables where table_name = 'FGA_LOGNEW$';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOGNEW$ FGA_TS
(2),导出FGA_LOG$数据
13-MAR-12 SYS @ hdb>!exp \'sys/oracle as sysdba\' file=fga_log$.dmp tables=fga_log$ compress=n
Export: Release 10.2.0.1.0 - Production on Tue Mar 13 15:36:46 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
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table FGA_LOG$ 3 rows exported
Export terminated successfully without warnings.
(3),重命名FGA_LOG$为FGA_LOGOLD$;
13-MAR-12 SYS @ hdb>rename fga_log$ to fga_logold$;
Table renamed.
(4),重命名FGA_LOGNEW$为FGA_LOG$;
13-MAR-12 SYS @ hdb>rename fga_lognew$ to fga_log$;
Table renamed.
(5),将导出的数据导入FGA_LOG$;
13-MAR-12 SYS @ hdb>!imp \'sys/oracle as sysdba\' file=fga_log$.dmp tables=fga_log$ ignore=y
Import: Release 10.2.0.1.0 - Production on Tue Mar 13 15:41:35 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
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "FGA_LOG$" 3 rows imported
Import terminated successfully without warnings.
(6),注意表在exp,imp时表名必须相同,所以只能先将新旧表重命名,再导入;最后验证如下.
查询表和表空间对应关系,说明表空间迁移完成.
13-MAR-12 SYS @ hdb>select table_name,tablespace_name from user_tables where table_name LIKE '%AUD%' or table_name like '%FGA%';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STMT_AUDIT_OPTION_MAP SYSTEM
FGA_LOGOLD$ SYSTEM
AUD$ AUDIT_TS
AUDIT$ AUDIT_TS
AUDIT_ACTIONS AUDIT_TS
FGA$ FGA_TS
FGACOL$ FGA_TS
FGA_LOG$ FGA_TS
8 rows selected.
13-MAR-12 SYS @ hdb>select segment_name index_name,tablespace_name,segment_type from user_segments where segment_type = 'INDEX' and (segment_name like '%AUD%' or segment_name like '%FGA%');
INDEX_NAME TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
I_STMT_AUDIT_OPTION_MAP SYSTEM INDEX
I_AUD1 AUDIT_TS INDEX
I_AUDIT AUDIT_TS INDEX
I_AUDIT_ACTIONS AUDIT_TS INDEX
I_FGAP FGA_TS INDEX
I_FGA FGA_TS INDEX
I_FGACOL FGA_TS INDEX
从客户端更新一个记录
SQL> update employees set salary = salary + 1000 where FIRST_NAME = 'Kevin' and last_name = 'Feeney';
1 row updated.
SQL> commit;
Commit complete.
查看audit和FGA结果
13-MAR-12 SYS @ hdb>select os_username,username,to_char(extended_timestamp,'dd-mon-yyyy hh24:mi:ss') timestamp,owner,obj_name,action_name from dba_audit_trail where owner = 'HR' order by timestamp;
OS_USERNAME USERNAME TIMESTAMP OWNER OBJ_NAME ACTION_NAME
-------------------- -------------------- -------------------- -------------------- -------------------- ---------
oracle HR 10-mar-2012 20:03:51 HR EMPLOYEES DELETE
oracle HR 10-mar-2012 20:12:56 HR JOB_HISTORY DELETE
oracle HR 10-mar-2012 20:13:15 HR EMPLOYEES DELETE
jeron HR 13-mar-2012 16:15:59 HR EMPLOYEES UPDATE
15 rows selected.
13-MAR-12 SYS @ hdb>select to_char(timestamp,'mm-dd-yyyy hh24:mi:ss') time,db_user,sql_text from dba_fga_audit_trail order by time;
TIME DB_USER SQL_TEXT
-------------------- ------ -----------------------------------------------------------------------------------------
03-10-2012 16:39:58 HR update employees set salary = 9000 where first_name = 'Jerron' and last_name = 'Peng'
03-10-2012 16:55:20 HR update employees set salary = 9100 where first_name = 'Jerron' and last_name = 'Peng'
03-10-2012 20:13:16 HR delete from employees where employee_id = 200
03-13-2012 16:15:59 HR update employees set salary = salary + 1000 where FIRST_NAME = 'Kevin' and last_name = 'Feeney'
可以看见audit与FGA的table和index成功完成表空间迁移,同时视图数据已经成功更新.
- Oracle Migrate AUDIT & FGA Tablespace
- Oracle Audit & FGA
- Oracle FGA(Fine-Grained Audit)细粒度审计的用法
- Oracle FGA
- Oracle FGA
- Oracle FGA
- Oracle FGA细粒度审计
- migrate table to another tablespace
- Oracle Audit
- oracle audit
- ORACLE AUDIT
- Oracle Audit
- oracle audit
- 利用Oracle FGA实现审计
- oracle 细颗粒审计FGA
- Migrate the index segment to another tablespace
- Oracle TableSpace
- Oracle Tablespace
- Oracle 10G RAC Cross-Instance Archivelog delete steps
- 选择排序
- 《C++Primer Plus》学习笔记(二)
- Oracle Audit & FGA
- 《C++Primer Plus》学习笔记(三)
- Oracle Migrate AUDIT & FGA Tablespace
- 你知道哪些表面优惠其实稳赚的销售手段
- CentOS 5.6创建NFS文件共享服务器
- 《C++Primer Plus》学习笔记(四)
- 《C++Primer Plus》学习笔记(五)
- 《C++Primer Plus》学习笔记(六)
- 《C++Primer Plus》学习笔记(七)
- ORA-00997: illegal use of LONG datatype
- Oracle SQL Having Where Group by
