-
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
-
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
-
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.
-
Hi PAVB,
Could you please elaborate it. how to archive ?
thanks
Gopu
Thanks/Gopu
-
Originally Posted by gopu_g
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.
-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|