Inquiry on Re-locating AUD$ table from system to another tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Inquiry on Re-locating AUD$ table from system to another tablespace

  1. #1
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340

    Inquiry on Re-locating AUD$ table from system to another tablespace

    Hi

    We would like to seek your advice on moving AUD$ table from SYSTEM tablespace to another tablespace(AUDIT_DATA). This going to be tested in Dev env.


    1) Currently audit is not enabled, so while enabling it will create any kind of performance issues in the database?
    2) Auditing is going to enable for a particular set of tables owned by particular user.
    3) Does any particular standard for creating tablespace for this purpose and how much size needs to be allocated?
    4) Is this a better option to use “DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION” procedure to move AUD$ table?

    BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => AUDIT_DATA);
    END;

    Please advice
    Thanks/Gopu

  2. #2
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Customer Problem Description
    ---------------------------------------------------

    Problem Summary
    ---------------------------------------------------
    Inquiry on Re-locating AUD$ table from system to another tablespace

    Problem Description
    ---------------------------------------------------
    Hi

    We would like to seek your advice on moving AUD$ table from SYSTEM tablespace to another tablespace(AUDIT_DATA). This has to be tested in dev environment and we are planning to implement the same on prod.

    1) We would like to know to know how to perform this AUD$ table re-location (step by step).
    2) Currently audit is not enabled, so while enabling it will create any kind of performance issues in the database?
    3) Auditing is going to enable for a particular set of tables owned by particular user.
    4) Does any particular standard for creating tablespace for this purpose and how much size needs to be allocated?
    5) Is this a better option to use “DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION” procedure to move AUD$ table?

    BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => AUDIT_DATA);
    END;



    Please find the database information:

    Current version of the database:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    CORE 11.1.0.7.0 Production
    TNS for Linux: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 – Production


    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ---------------
    AUD$ SYSTEM


    SEGMENT_NAME SIZE_IN_MEGABYTES
    --------------------------------------------------------------------------------- -----------------
    AUD$ 61
    Thanks/Gopu

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would let AUD$ table sitting on SYSTEM tablespace, create a AUD_HISTORY table on other tablespace and archive AUD$ data into AUD_HISTORY once a month or so, purging archived rows from AUD$
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Hi PAVB,

    Could you please elaborate it. how to archive ?

    thanks
    Gopu
    Thanks/Gopu

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gopu_g View Post
    Could you please elaborate it. how to archive ?
    Once a month insert into AUD_HISTORY everything that is 90 days or older then delete from AUD$ everything that is 90 days or older.

    That way, you keep only the last 90-120 days on AUD$ having all the historic data "archived" on AUD_HISTORY which is located on a table space other than SYSTEM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Here is some code that will create a partitioned table and migrate any data older than a day to the partitioned table. You need the RAWTOHEX("XID") function of RAW columns. You can change it to suit your needs.

    Code:
    SET ECHO ON
    
      DROP TABLE SYS.AUDIT_DATA_HISTORY;
    
      CREATE TABLE SYS.AUDIT_DATA_HISTORY
       (    SESSIONID NUMBER NOT NULL ENABLE,
            ENTRYID NUMBER NOT NULL ENABLE,
            STATEMENT NUMBER NOT NULL ENABLE,
            TIMESTAMP# DATE,
            USERID VARCHAR2(30),
            USERHOST VARCHAR2(128),
            TERMINAL VARCHAR2(255),
            ACTION# NUMBER NOT NULL ENABLE,
            RETURNCODE NUMBER NOT NULL ENABLE,
            OBJ$CREATOR VARCHAR2(30),
            OBJ$NAME VARCHAR2(128),
            AUTH$PRIVILEGES VARCHAR2(16),
            AUTH$GRANTEE VARCHAR2(30),
            NEW$OWNER VARCHAR2(30),
            NEW$NAME VARCHAR2(128),
            SES$ACTIONS VARCHAR2(19),
            SES$TID NUMBER,
            LOGOFF$LREAD NUMBER,
            LOGOFF$PREAD NUMBER,
            LOGOFF$LWRITE NUMBER,
            LOGOFF$DEAD NUMBER,
            LOGOFF$TIME DATE,
            COMMENT$TEXT VARCHAR2(4000),
            CLIENTID VARCHAR2(64),
            SPARE1 VARCHAR2(255),
            SPARE2 NUMBER,
            OBJ$LABEL RAW(255),
            SES$LABEL RAW(255),
            PRIV$USED NUMBER,
            SESSIONCPU NUMBER,
            NTIMESTAMP# TIMESTAMP (6),
            PROXY$SID NUMBER,
            USER$GUID VARCHAR2(32),
            INSTANCE# NUMBER,
            PROCESS# VARCHAR2(16),
            XID RAW(8),
            AUDITID VARCHAR2(64),
            SCN NUMBER,
            DBID NUMBER,
            SQLBIND CLOB,
            SQLTEXT CLOB,
            OBJ$EDITION VARCHAR2(30) )
        LOB (SQLBIND) STORE AS
             BASICFILE ( TABLESPACE SYSTEM ENABLE STORAGE IN ROW CHUNK 8192 RETENTION ),
        LOB (SQLTEXT) STORE AS
             BASICFILE ( TABLESPACE SYSTEM ENABLE STORAGE IN ROW CHUNK 8192 RETENTION),
        PARTITION BY RANGE (NTIMESTAMP#)
          INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
          ( PARTITION p0001 VALUES LESS THAN (TO_DATE('01-JAN-2009', 'DD-MON-YYYY')));
    
    
    CREATE OR REPLACE PROCEDURE archive_aud_data
    AS
    BEGIN
       INSERT INTO SYS.AUDIT_DATA_HISTORY
            SELECT "SESSIONID", "ENTRYID", "STATEMENT", "TIMESTAMP#", "USERID",
                   "USERHOST", "TERMINAL", "ACTION#", "RETURNCODE", "OBJ$CREATOR",
                   "OBJ$NAME", "AUTH$PRIVILEGES", "AUTH$GRANTEE", "NEW$OWNER",
                   "NEW$NAME", "SES$ACTIONS", "SES$TID", "LOGOFF$LREAD", "LOGOFF$PREAD",
                   "LOGOFF$LWRITE", "LOGOFF$DEAD", "LOGOFF$TIME", "COMMENT$TEXT",
                   "CLIENTID", "SPARE1", "SPARE2", RAWTOHEX("OBJ$LABEL"), RAWTOHEX("SES$LABEL"),
                   "PRIV$USED", "SESSIONCPU", "NTIMESTAMP#", "PROXY$SID", "USER$GUID",
                   "INSTANCE#", "PROCESS#", RAWTOHEX("XID"), "AUDITID", "SCN", "DBID",
                   "SQLBIND", "SQLTEXT", "OBJ$EDITION"
              FROM AUD$
             WHERE "NTIMESTAMP#" < TRUNC(SYSDATE);
    
       DELETE AUD$
        WHERE "NTIMESTAMP#" < TRUNC(SYSDATE);
    
       COMMIT;
    
    END archive_aud_data;
    /
    this space intentionally left blank

  7. #7
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Thanks PAVB and gandolf989's for the suggestion, i will try it out.
    Thanks/Gopu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width