【Unified Auditing】统一审计的存储（12.2）
来源：互联网 发布：python 内容管理系统 编辑：程序博客网 时间：2024/06/10 13:58
- 统一审计的存储对象122
- 升级后121的存储对象CLI迁移到122的存储对象AUDUNIFIED
统一审计的存储对象（12.2）
从12.2.0.1版本开始统一审计UNIFIED_AUDIT_TRAIL视图的内部存储对象变成了AUDSYS.AUD$UNIFIED表，无论数据库的版本是SE2还是EE，该表都是一个默认间隔为1个月的分区表。
查看统一审计的AUDSYS Schema的存储内容：（12.2.0.1环境）
SQL> set pagesize 200SQL> set linesize 200SQL> col OWNER format a10SQL> col SEGMENT_NAME format a25SQL> col SEGMENT_TYPE format a20SQL> col PARTITION_NAME format a20SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,PARTITION_NAME,TABLESPACE_NAME，BYTES/1024/1024 "sizeMB" from DBA_SEGMENTS where OWNER='AUDSYS';OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE sizeMB---------- ------------------------- -------------------- -------------------- ---------- ----------AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P201 SYSAUX .0625AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P268 SYSAUX .875AUDSYS AUD$UNIFIED TABLE PARTITION SYS_P752 SYSAUX .0625AUDSYS SYS_IL0000017939C00097$ INDEX PARTITION SYS_IL_P758 SYSAUX .0625AUDSYS SYS_IL0000017939C00031$ INDEX PARTITION SYS_IL_P756 SYSAUX .0625AUDSYS SYS_IL0000017939C00030$ INDEX PARTITION SYS_IL_P754 SYSAUX .0625AUDSYS SYS_IL0000017939C00097$ INDEX PARTITION SYS_IL_P207 SYSAUX .0625AUDSYS SYS_IL0000017939C00031$ INDEX PARTITION SYS_IL_P205 SYSAUX .0625AUDSYS SYS_IL0000017939C00030$ INDEX PARTITION SYS_IL_P203 SYSAUX .0625AUDSYS SYS_IL0000017939C00097$ INDEX PARTITION SYS_IL_P274 SYSAUX .0625AUDSYS SYS_IL0000017939C00031$ INDEX PARTITION SYS_IL_P272 SYSAUX .0625AUDSYS SYS_IL0000017939C00030$ INDEX PARTITION SYS_IL_P270 SYSAUX .0625AUDSYS SYS_LOB0000017939C00030$ LOB PARTITION SYS_LOB_P202 SYSAUX .125AUDSYS SYS_LOB0000017939C00031$ LOB PARTITION SYS_LOB_P204 SYSAUX .125AUDSYS SYS_LOB0000017939C00097$ LOB PARTITION SYS_LOB_P206 SYSAUX .125AUDSYS SYS_LOB0000017939C00030$ LOB PARTITION SYS_LOB_P269 SYSAUX .125AUDSYS SYS_LOB0000017939C00031$ LOB PARTITION SYS_LOB_P271 SYSAUX .125AUDSYS SYS_LOB0000017939C00097$ LOB PARTITION SYS_LOB_P273 SYSAUX .125AUDSYS SYS_LOB0000017939C00030$ LOB PARTITION SYS_LOB_P753 SYSAUX .125AUDSYS SYS_LOB0000017939C00031$ LOB PARTITION SYS_LOB_P755 SYSAUX .125AUDSYS SYS_LOB0000017939C00097$ LOB PARTITION SYS_LOB_P757 SYSAUX .125通过上面的输出我们可以看到在12.2的环境中，在统一审计的AUDSYS Schema下有AUD$UNIFIED表和分区索引和大数据段。
对于AUD$UNIFIED表的DDL定义，我们可以通过dbms_metadata.get_ddl来查看：
SQL> set pages 0SQL> set longchunksize 3000SQL> set long 2000000000SQL> select dbms_metadata.get_ddl('TABLE','AUD$UNIFIED','AUDSYS') from dual;SQL> CREATE TABLE "AUDSYS"."AUD$UNIFIED" SHARING=METADATA ( "INST_ID" NUMBER, "AUDIT_TYPE" NUMBER, "SESSIONID" NUMBER, "PROXY_SESSIONID" NUMBER, "OS_USER" VARCHAR2(128), "HOST_NAME" VARCHAR2(128), "TERMINAL" VARCHAR2(30), "INSTANCE_ID" NUMBER, "DBID" NUMBER, "AUTHENTICATION_TYPE" VARCHAR2(1024), "USERID" VARCHAR2(128), "PROXY_USERID" VARCHAR2(128), "EXTERNAL_USERID" VARCHAR2(1024), "GLOBAL_USERID" VARCHAR2(32), "CLIENT_PROGRAM_NAME" VARCHAR2(48), "DBLINK_INFO" VARCHAR2(4000), "XS_USER_NAME" VARCHAR2(128), "XS_SESSIONID" RAW(33), "ENTRY_ID" NUMBER NOT NULL ENABLE, "STATEMENT_ID" NUMBER NOT NULL ENABLE, "EVENT_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE, "ACTION" NUMBER NOT NULL ENABLE, "RETURN_CODE" NUMBER NOT NULL ENABLE, "OS_PROCESS" VARCHAR2(16), "TRANSACTION_ID" RAW(8), "SCN" NUMBER, "EXECUTION_ID" VARCHAR2(64), "OBJ_OWNER" VARCHAR2(128), "OBJ_NAME" VARCHAR2(128), "SQL_TEXT" CLOB, "SQL_BINDS" CLOB, "APPLICATION_CONTEXTS" VARCHAR2(4000), "CLIENT_IDENTIFIER" VARCHAR2(64), "NEW_OWNER" VARCHAR2(128), "NEW_NAME" VARCHAR2(128), "OBJECT_EDITION" VARCHAR2(128), "SYSTEM_PRIVILEGE_USED" VARCHAR2(1024), "SYSTEM_PRIVILEGE" NUMBER, "AUDIT_OPTION" NUMBER, "OBJECT_PRIVILEGES" VARCHAR2(35), "ROLE" VARCHAR2(128), "TARGET_USER" VARCHAR2(128), "EXCLUDED_USER" VARCHAR2(128), "EXCLUDED_SCHEMA" VARCHAR2(128), "EXCLUDED_OBJECT" VARCHAR2(128), "CURRENT_USER" VARCHAR2(128), "ADDITIONAL_INFO" VARCHAR2(4000), "UNIFIED_AUDIT_POLICIES" VARCHAR2(4000), "FGA_POLICY_NAME" VARCHAR2(128), "XS_INACTIVITY_TIMEOUT" NUMBER, "XS_ENTITY_TYPE" VARCHAR2(32), "XS_TARGET_PRINCIPAL_NAME" VARCHAR2(128), "XS_PROXY_USER_NAME" VARCHAR2(128), "XS_DATASEC_POLICY_NAME" VARCHAR2(128), "XS_SCHEMA_NAME" VARCHAR2(128), "XS_CALLBACK_EVENT_TYPE" VARCHAR2(32), "XS_PACKAGE_NAME" VARCHAR2(128), "XS_PROCEDURE_NAME" VARCHAR2(128), "XS_ENABLED_ROLE" VARCHAR2(128), "XS_COOKIE" VARCHAR2(1024), "XS_NS_NAME" VARCHAR2(128), "XS_NS_ATTRIBUTE" VARCHAR2(4000), "XS_NS_ATTRIBUTE_OLD_VAL" VARCHAR2(4000), "XS_NS_ATTRIBUTE_NEW_VAL" VARCHAR2(4000), "DV_ACTION_CODE" NUMBER, "DV_ACTION_NAME" VARCHAR2(30), "DV_EXTENDED_ACTION_CODE" NUMBER, "DV_GRANTEE" VARCHAR2(128), "DV_RETURN_CODE" NUMBER, "DV_ACTION_OBJECT_NAME" VARCHAR2(128), "DV_RULE_SET_NAME" VARCHAR2(90), "DV_COMMENT" VARCHAR2(4000), "DV_FACTOR_CONTEXT" VARCHAR2(4000), "DV_OBJECT_STATUS" VARCHAR2(1), "OLS_POLICY_NAME" VARCHAR2(128), "OLS_GRANTEE" VARCHAR2(128), "OLS_MAX_READ_LABEL" VARCHAR2(4000), "OLS_MAX_WRITE_LABEL" VARCHAR2(4000), "OLS_MIN_WRITE_LABEL" VARCHAR2(4000), "OLS_PRIVILEGES_GRANTED" VARCHAR2(128), "OLS_PROGRAM_UNIT_NAME" VARCHAR2(128), "OLS_PRIVILEGES_USED" VARCHAR2(128), "OLS_STRING_LABEL" VARCHAR2(4000), "OLS_LABEL_COMPONENT_TYPE" VARCHAR2(12), "OLS_LABEL_COMPONENT_NAME" VARCHAR2(30), "OLS_PARENT_GROUP_NAME" VARCHAR2(30), "OLS_OLD_VALUE" VARCHAR2(4000), "OLS_NEW_VALUE" VARCHAR2(4000), "RMAN_SESSION_RECID" NUMBER, "RMAN_SESSION_STAMP" NUMBER, "RMAN_OPERATION" VARCHAR2(20), "RMAN_OBJECT_TYPE" VARCHAR2(20), "RMAN_DEVICE_TYPE" VARCHAR2(5), "DP_TEXT_PARAMETERS1" VARCHAR2(512), "DP_BOOLEAN_PARAMETERS1" VARCHAR2(512), "DIRECT_PATH_NUM_COLUMNS_LOADED" NUMBER, "RLS_INFO" CLOB, "KSACL_USER_NAME" VARCHAR2(128), "KSACL_SERVICE_NAME" VARCHAR2(512), "KSACL_SOURCE_LOCATION" VARCHAR2(48), "CON_ID" NUMBER ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSAUX" LOB ("SQL_TEXT") STORE AS SECUREFILE ( TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("SQL_BINDS") STORE AS SECUREFILE ( TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("RLS_INFO") STORE AS SECUREFILE ( TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH) (PARTITION "AUD_UNIFIED_P0" VALUES LESS THAN (TIMESTAMP' 2014-07-01 00:00:00') SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSAUX" LOB ("SQL_TEXT") STORE AS SECUREFILE ( TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("SQL_BINDS") STORE AS SECUREFILE ( TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("RLS_INFO") STORE AS SECUREFILE ( TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) )另一种方法是，通过查看在数据库升级用的脚本catuat.sql 中的内容来了解
$ORACLE_HOME/rdbms/admin/catuat.sql catuat.sql 的内容摘要：
declare create_tab_temp_sql varchar2(32000); timestamp_format varchar2(30); first_part_timestamp varchar2(30); partition_interval varchar2(2); tablespace_clause varchar2(30); partitioning_clause varchar2(300); create_table_sql varchar2(32500); db_edition varchar2(7); begin first_part_timestamp := '2014-07-01 00:00:00'; timestamp_format := 'YYYY-MM-DD HH24:MI:SS'; partition_interval := '1'; tablespace_clause := 'TABLESPACE SYSAUX'; partitioning_clause := 'PARTITION BY RANGE (EVENT_TIMESTAMP) INTERVAL(INTERVAL '||''''||partition_interval||''''|| ' MONTH) (PARTITION aud_unified_p0 VALUES LESS THAN (TO_TIMESTAMP('||''''||first_part_timestamp||''''||', '|| ''''||timestamp_format||''''|| ')) TABLESPACE SYSAUX) '; create_tab_temp_sql := 'CREATE TABLE AUDSYS.AUD$UNIFIED ( INST_ID NUMBER, AUDIT_TYPE NUMBER,... DIRECT_PATH_NUM_COLUMNS_LOADED NUMBER, RLS_INFO CLOB, KSACL_USER_NAME VARCHAR2(128), KSACL_SERVICE_NAME VARCHAR2(512), KSACL_SOURCE_LOCATION VARCHAR2(48), CON_ID NUMBER ) LOB (SQL_TEXT, SQL_BINDS, RLS_INFO) STORE AS(TABLESPACE SYSAUX) '; select edition into db_edition from v$instance; if db_edition in ('EE', 'HP', 'XP') -- Enterprise Edition Oracle then -- Create Partitioned table create_table_sql := create_tab_temp_sql || partitioning_clause|| tablespace_clause; begin execute immediate create_table_sql; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN (-00955) AND db_not_122 THEN -- Table already exists alter_tab_def; NULL; ELSE RAISE; END IF; end; else -- Create Non-Partitioned Table create_table_sql := create_tab_temp_sql || tablespace_clause; begin execute immediate create_table_sql; EXCEPTION WHEN OTHERS THEN IF SQLCODE IN (-00955) AND db_not_122 THEN -- Table already exists alter_tab_def; NULL; ELSE RAISE; END IF; end; end if;end;/通过上面的输出，可以看到：
1.AUDSYS.AUD$UNIFIED表都是一个默认间隔为1个月的分区表2.AUDSYS.AUD$UNIFIED是以EVENT_TIMESTAMP列作为分区键3.和12.1版本一样"SQL_TEXT"和"SQL_BINDS" 列为CLOB类型的存储。升级后12.1的存储对象（CLI）迁移到12.2的存储对象（AUD$UNIFIED）
在12.2版本上，Oracle提供一个DBMS_AUDIT_MGMT.TRANSFER_UNIFIED_AUDIT_RECORDS 程序包，可以把12.1版本上存储对象（CLI）中的数据迁移到12.2的存储对象（AUD$UNIFIED）中。
参考：
http://docs.oracle.com/database/122/UPGRD/recommended-and-best-practices-complete-upgrading-oracle-database.htm#UPGRD-GUID-4BC5F146-BF0D-4BCF-8A0B-1B67B767EEF1
Transfer Unified Audit Records After the Upgrade
版权声明：本文为博主原创文章，转载必须注明出处，本人保留一切相关权力！http://blog.csdn.net/lukeunique
欢迎关注微信订阅号：TeacherWhat
0 0
- 【Unified Auditing】统一审计的存储（12.2）
- 【Unified Auditing】统一审计的存储架构体系
- 【Unified Auditing】统一审计的进程架构体系
- 【Unified Auditing】Oracle 12c 统一审计基础介绍
- 【Security】传统审计（Traditional Auditing）
- ORACLE 数据库 （DBA）之 标准审计AUDITING （审计）
- RUP（Rational Unified Process, Rational统一过程, 统一软件开发过程）的测试分类
- Rational统一过程（Rational Unified Process）
- UML- 统一建模语言（Unified Modeling Language）创建项目的序列图及类图
- 利用MariaDB Auditing Plugin实现社区版MySQL的审计功能
- UC（Unified Communication）统一通讯故事 2010-11-3
- UC（Unified Communication）统一通讯故事 2011-4-20
- 统一建模语言（UML，Unified Modeling Language）
- 统一存储的进化
- 信息系统审计（IT审计）的实施
- Fine-Grained Auditing test (精细审计：FGA测试)
- SQLSERVER数据审计的存储过程
- 笔记20151210: 统一建模语言图（Unified Modeliing Language, UML）
- RecycleView简单实现滑动删除Item
- 特征值和特征向量（二）
- 栈及其实现
- 数据结构与算法
- scala（一）
- 【Unified Auditing】统一审计的存储（12.2）
- 欢迎使用CSDN-markdown编辑器
- LeetCode 98. Validate Binary Search Tree Add to List
- Android使用Retrofit请求WebService
- 二叉树遍历：已知前序中序输出后序/已知后序中序输出前序
- python基础学习（二）：数据类型
- 哈希hash
- POJ 2485（建高速公路__最小生成树）
- domain、entity和model的package的不同用法
