投稿
  1. 极客之音首页
  2. 技术随笔

【Unified Auditing】统一审计的存储（12.2）

飞熊 • • 技术随笔 • 阅读 283

勤奋不是嘴上说说而已，而是实际的行动，在勤奋的苦度中持之以恒，永不退却。业精于勤，荒于嬉；行成于思，毁于随。在人生的仕途上，我们毫不迟疑地选择勤奋，她是几乎于世界上一切成就的催产婆。只要我们拥着勤奋去思考，拥着勤奋的手去耕耘，用抱勤奋的心去对待工作，浪迹红尘而坚韧不拔，那么，我们的生命就会绽放火花，让人生的时光更加的闪亮而精彩。

导读：本篇文章讲解 【Unified Auditing】统一审计的存储（12.2），希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

统一审计的存储对象（12.2）

从12.2.0.1版本开始统一审计UNIFIED_AUDIT_TRAIL视图的内部存储对象变成了AUDSYS.AUD$UNIFIED表，无论数据库的版本是SE2还是EE，该表都是一个默认间隔为1个月的分区表。

查看统一审计的AUDSYS Schema的存储内容：（12.2.0.1环境）

SQL> set pagesize 200
SQL> set linesize 200
SQL> col OWNER format a10
SQL> col SEGMENT_NAME format a25
SQL> col SEGMENT_TYPE format a20
SQL> col PARTITION_NAME format a20
SQL>  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          .0625
AUDSYS     AUD$UNIFIED               TABLE PARTITION      SYS_P268             SYSAUX           .875
AUDSYS     AUD$UNIFIED               TABLE PARTITION      SYS_P752             SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00097$  INDEX PARTITION      SYS_IL_P758          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00031$  INDEX PARTITION      SYS_IL_P756          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00030$  INDEX PARTITION      SYS_IL_P754          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00097$  INDEX PARTITION      SYS_IL_P207          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00031$  INDEX PARTITION      SYS_IL_P205          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00030$  INDEX PARTITION      SYS_IL_P203          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00097$  INDEX PARTITION      SYS_IL_P274          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00031$  INDEX PARTITION      SYS_IL_P272          SYSAUX          .0625
AUDSYS     SYS_IL0000017939C00030$  INDEX PARTITION      SYS_IL_P270          SYSAUX          .0625
AUDSYS     SYS_LOB0000017939C00030$ LOB PARTITION        SYS_LOB_P202         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00031$ LOB PARTITION        SYS_LOB_P204         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00097$ LOB PARTITION        SYS_LOB_P206         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00030$ LOB PARTITION        SYS_LOB_P269         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00031$ LOB PARTITION        SYS_LOB_P271         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00097$ LOB PARTITION        SYS_LOB_P273         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00030$ LOB PARTITION        SYS_LOB_P753         SYSAUX           .125
AUDSYS     SYS_LOB0000017939C00031$ LOB PARTITION        SYS_LOB_P755         SYSAUX           .125
AUDSYS     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 0
SQL> set longchunksize 3000
SQL> set long 2000000000
SQL> 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
这里写图片描述

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/141616.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！