Reorganize Audit Tables, indexes to release space

Solution:-

Assuming you have your audit table, indexes on a non-system tablespace and have already deleted the rows but the disk space is not released. The following is a guide to reorganize your audit table , indexes and release the disk space back to the system.

 

Table, indexes DDL extracted from TOAD Tool

DROP TABLE SYS.AUD$ CASCADE CONSTRAINTS;

 

CREATE TABLE SYS.AUD$

(

SESSIONID        NUMBER                       NOT NULL,

ENTRYID          NUMBER                       NOT NULL,

STATEMENT        NUMBER                       NOT NULL,

TIMESTAMP#       DATE,

USERID           VARCHAR2(30 BYTE),

USERHOST         VARCHAR2(128 BYTE),

TERMINAL         VARCHAR2(255 BYTE),

ACTION#          NUMBER                       NOT NULL,

RETURNCODE       NUMBER                       NOT NULL,

OBJ$CREATOR      VARCHAR2(30 BYTE),

OBJ$NAME         VARCHAR2(128 BYTE),

AUTH$PRIVILEGES  VARCHAR2(16 BYTE),

AUTH$GRANTEE     VARCHAR2(30 BYTE),

NEW$OWNER        VARCHAR2(30 BYTE),

NEW$NAME         VARCHAR2(128 BYTE),

SES$ACTIONS      VARCHAR2(19 BYTE),

SES$TID          NUMBER,

LOGOFF$LREAD     NUMBER,

LOGOFF$PREAD     NUMBER,

LOGOFF$LWRITE    NUMBER,

LOGOFF$DEAD      NUMBER,

LOGOFF$TIME      DATE,

COMMENT$TEXT     VARCHAR2(4000 BYTE),

CLIENTID         VARCHAR2(64 BYTE),

SPARE1           VARCHAR2(255 BYTE),

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 BYTE),

INSTANCE#        NUMBER,

PROCESS#         VARCHAR2(16 BYTE),

XID              RAW(8),

AUDITID          VARCHAR2(64 BYTE),

SCN              NUMBER,

DBID             NUMBER,

SQLBIND          CLOB,

SQLTEXT          CLOB

)

LOB (SQLBIND) STORE AS (

TABLESPACE HD_AUDIT

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

LOB (SQLTEXT) STORE AS (

TABLESPACE HD_AUDIT

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

TABLESPACE HD_AUDIT

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          56K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

 

 

CREATE INDEX SYS.I_AUD2 ON SYS.AUD$

(SESSIONID, SES$TID)

LOGGING

TABLESPACE HD_AUDIT

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

INITIAL          56K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

NOPARALLEL;

 

 

GRANT SELECT ON SYS.AUD$ TO HD_MAINT;

 

 

 

DROP TABLE HD_MAINT.HD_AUD_HIST CASCADE CONSTRAINTS;

 

CREATE TABLE HD_MAINT.HD_AUD_HIST

(

SESSIONID        NUMBER                       NOT NULL,

ENTRYID          NUMBER                       NOT NULL,

STATEMENT        NUMBER                       NOT NULL,

TIMESTAMP#       DATE,

USERID           VARCHAR2(30 BYTE),

USERHOST         VARCHAR2(128 BYTE),

TERMINAL         VARCHAR2(255 BYTE),

ACTION#          NUMBER                       NOT NULL,

RETURNCODE       NUMBER                       NOT NULL,

OBJ$CREATOR      VARCHAR2(30 BYTE),

OBJ$NAME         VARCHAR2(128 BYTE),

AUTH$PRIVILEGES  VARCHAR2(16 BYTE),

AUTH$GRANTEE     VARCHAR2(30 BYTE),

NEW$OWNER        VARCHAR2(30 BYTE),

NEW$NAME         VARCHAR2(128 BYTE),

SES$ACTIONS      VARCHAR2(19 BYTE),

SES$TID          NUMBER,

LOGOFF$LREAD     NUMBER,

LOGOFF$PREAD     NUMBER,

LOGOFF$LWRITE    NUMBER,

LOGOFF$DEAD      NUMBER,

LOGOFF$TIME      DATE,

COMMENT$TEXT     VARCHAR2(4000 BYTE),

CLIENTID         VARCHAR2(64 BYTE),

SPARE1           VARCHAR2(255 BYTE),

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 BYTE),

INSTANCE#        NUMBER,

PROCESS#         VARCHAR2(16 BYTE),

XID              RAW(8),

AUDITID          VARCHAR2(64 BYTE),

SCN              NUMBER,

DBID             NUMBER,

SQLBIND          CLOB,

SQLTEXT          CLOB

)

LOB (SQLBIND) STORE AS (

TABLESPACE HD_AUDIT_ARC

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT_ARC

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

LOB (SQLTEXT) STORE AS (

TABLESPACE HD_AUDIT_ARC

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT_ARC

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

TABLESPACE HD_AUDIT_ARC

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

 

 

 

The Objects are found in two Tablespaces

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

Time Start: 09/09/2011 7:13:03 PM

 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS TABLESPACE_NAME

—————————— —————————— —————— ———- ———- ——————————

SYS                            AUD$                           TABLE              1693073408      15898 HD_AUDIT

HD_MAINT                       HD_AUD_HIST                    TABLE               858251264       8059 HD_AUDIT_ARC

SYS                            SYS_IL0000055838C00040$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            SYS_IL0000055838C00041$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            I_AUD2                         INDEX               948240384       8904 HD_AUDIT

HD_MAINT                       SYS_IL0000093888C00040$$       LOBINDEX               106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_IL0000093888C00041$$       LOBINDEX               106496          1 HD_AUDIT_ARC

SYS                            SYS_LOB0000055838C00040$$      LOBSEGMENT             106496          1 HD_AUDIT

SYS                            SYS_LOB0000055838C00041$$      LOBSEGMENT             106496          1 HD_AUDIT

HD_MAINT                       SYS_LOB0000093888C00040$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_LOB0000093888C00041$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

 

11 rows selected.

Time End: 09/09/2011 7:13:04 PM

Elapsed Time for Script Execution: 766 msecs

 

 

Shutdown the  primary and standby database

Login to primary database server hd_primary

Sqlas

Shutdown immediate

Login to hd_standby

Sqlas

Shutdown immediate

 

 

House Keep AUD$ Table

 

1.Set the database in restricted mode and make sure that no auditable users
are connected. If necessary kill these sessions.

Login as oracle in hd_primary

connect / as sysdba

startup restrict

alter system enable restricted session;

Verify the column LOGINS in V$INSTANCE.

 

LOGINS = RESTRICTED indicates that the database is in restricted mode.

Select logins from v$instance;

 

Check if sessions are still connected :

select sid, serial#, username from v$session;

If necessary kill these sessions with:

alter system kill session ‘sid , serial#’;

make a backup of current aud$, hd_aud_hist tables in the same tablespace

create table sys.aud$_backup tablespace hd_audit as select * from sys.aud$;

create table hd_main.hd_aud_hist_backup tablespace hd_audit_arc as select * from hd_maint.hd_aud_hist;

2. Move SYS.AUD$ to a different tablespace

select count(*) from SYS.AUD$;

 

create tablespace HD_AUD datafile ‘/u02/oradata/zoeoa1/hd_aud_01.dbf’ size 500m autoextend on;

alter table sys.aud$ move tablespace hd_aud;

Alter table sys.aud$ move lob (SQLBIND) STORE as (tablespace hd_aud);

Alter table sys.aud$ move lob (SQLTEXT) STORE as (tablespace hd_aud);

 

 

3

Rebuild the index / move to new tablespace

Alter index sys.i_aud2 rebuild tablespace hd_aud;

All extents are now optimally filled again.

 

————–

House Keep HD_AUD_HIST Table

Select count(*) from HD_MAINT.HD_AUD_HIST;

create tablespace HD_AUD_ARC datafile ‘/u02/oradata/zoeoa1/hd_aud_arc01.dbf’ size 500m autoextend on;

alter user hd_maint quota unlimited on HD_AUD_ARC;

 

alter table hd_maint.hd_aud_hist move tablespace hd_aud_arc;

Alter table hd_maint.hd_aud_hist move lob (SQLBIND) STORE as (tablespace hd_aud_arc);

Alter table hd_maint.hd_aud_hist move lob (SQLTEXT) STORE as (tablespace hd_aud_arc);

 

 

All extents are now optimally filled again.
Ensure the tablespace is empty except the backedup objects by running the script below before dropping the objects

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

Now drop the backedup objects

Drop table sys.aud$_backup purge;

Drop table hd_maint.hd_aud_hist_backup purge;

And finally drop the old tablespaces hd_audit and hd_audit_arc

 

Drop tablespace hd_audit including contents and datafiles ;

Drop tablespace hd_audit_arc including contents and datafiles;

 

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

no rows selected.

 

Restart the database in normal mode

Shutdown immediate

Startup

 

Startup zoeoa1 dr database

Login as oracle in hd_standby

Sqlas

Startup mount

check data guard broker and Log Gap

dgmgrl sys/******

show configuration

Expected result: success

 

Run the SC01 job from hd_primary

Log Gap=0

Also check alert log and ensure for no errors

SETUP Database Audit and House Keeping in Oracle10G

Database Audit Solution:- The following turns on auditing and places the AUD$ table in a non-system tablespace HD_AUDIT and also creates an index I_AUD2 on AUD$ table. The AUD$ table is archived to HD_AUD_HIST table and housekeeping is done on a weekly basis driven by Job scheduler.

 

Enable Auditing in Oracle Database and store the data in AUD$ Table

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE sid=’*’;

 

System altered.

 

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

 

Total System Global Area  289406976 bytes

Fixed Size                  1248600 bytes

Variable Size              71303848 bytes

Database Buffers          213909504 bytes

Redo Buffers                2945024 bytes

Database mounted.

Database opened.

 

SQL>

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_trail                          string      DB

 

Create the Tablespace to store the Audit History Table

Login as sys user

CREATE TABLESPACE HD_AUDIT_ARC DATAFILE

‘/u02/oradata/cpstest/HD_audit_arc01.dbf’ SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 500m

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 104K

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO;

 

 

 

Create HD_MAINT Database user and Grant Privileges

CREATE USER HD_MAINT

IDENTIFIED BY oracle10g

DEFAULT TABLESPACE HD_AUDIT_ARC

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

GRANT CONNECT TO HD_MAINT;

GRANT CREATE TABLE TO HD_MAINT;

GRANT ALTER SESSION TO HD_MAINT;

grant select on sys.aud$ to HD_MAINT;

grant select on dba_audit_session to HD_MAINT;

grant select on dba_audit_object to HD_MAINT;

ALTER USER HD_MAINT QUOTA UNLIMITED ON HD_AUDIT_ARC;

 

 

Create HD_AUD_HIST Table

 

alter session set current_schema =HD_MAINT;

CREATE TABLE HD_MAINT.HD_AUD_HIST AS SELECT * from sys.aud$ WHERE 1=2;

 

 

Create Procedure HD_AUD_MAINT_WEEKLY

 

alter session set current_schema =SYS;

CREATE OR REPLACE PROCEDURE SYS.HD_AUD_MAINT_WEEKLY AS

BEGIN

— REM INSERT WEEKLY RECORDS ALONE TO HD_AUD_HIST TABLE FROM AUD$

INSERT INTO HD_MAINT.HD_AUD_HIST SELECT * FROM SYS.AUD$ WHERE LOGOFF$TIME > (SELECT MAX(LOGOFF$TIME) FROM HD_MAINT.HD_AUD_HIST);

COMMIT;

— REM DELETE FROM AUD$ THE OLDER RECORDS AND LEAVE ONLY THE RECENT 60 DAYS RECORD

DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# <= SYSDATE – 60;

commit;

— REM DELETE FROM HIST TABLE RECORDS WHICH ARE OLDER THAN 7 Months

DELETE FROM HD_MAINT.HD_AUD_HIST where NTIMESTAMP# < last_day(add_months(sysdate, -7));

commit;

end;

/

 

 

 

 

Insert the existing Aud$ Records to History Table

 

alter session set current_schema =SYS;

insert into HD_maint.HD_aud_hist select * from aud$;

 

Create Scheduled Job to run for every 15 Minutes

BEGIN

sys.dbms_scheduler.create_job(

job_name => ‘”SYS”.”HD_AUD_HK_JOB”‘,

job_type => ‘STORED_PROCEDURE’,

job_action => ‘”SYS”.”HD_AUD_MAINT_WEEKLY”‘,

repeat_interval => ‘FREQ=MINUTELY;INTERVAL=15’,

start_date => systimestamp at time zone ‘Australia/Perth’,

job_class => ‘”DEFAULT_JOB_CLASS”‘,

comments => ‘CPSOA1 Audit Tables House Keeping Job’,

auto_drop => FALSE,

enabled => FALSE);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘job_priority’, value => 4);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘max_run_duration’, value

=> numtodsinterval(60, ‘minute’));

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘raise_events’, value =>

dbms_scheduler.job_failed + dbms_scheduler.job_broken + dbms_scheduler.job_disabled);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘logging_level’, value =>

DBMS_SCHEDULER.LOGGING_FULL);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘instance_stickiness’, value

=> FALSE);

sys.dbms_scheduler.enable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

END;

/

 

 

Test if the Job was successful in OEM Grid Control

 

Monitor the Records retention Period in Audit Tables using the SQL’s

below

 

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select max(LOGOFF$TIME) as max_logofftime from AUD$;

select min(LOGOFF$TIME) as min_logofftime from AUD$;

 

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select max(LOGOFF$TIME) as max_logofftime from HD_MAINT.HD_AUD_HIST;

select min(LOGOFF$TIME) as min_logofftime from HD_MAINT.HD_AUD_HIST;

 

Amend the Scheduled Job to run once in a Week

BEGIN

sys.dbms_scheduler.disable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘repeat_interval’, value =>

‘FREQ=WEEKLY;BYDAY=SUN;BYHOUR=5;BYMINUTE=0;BYSECOND=0’);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘start_date’, value =>

systimestamp at time zone ‘Australia/Perth’);

sys.dbms_scheduler.enable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

END;

/

 

The following objects are created in the two tablespaces

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS TABLESPACE_NAME

—————————— —————————— —————— ———- ———- ——————————

SYS                            AUD$                           TABLE              1693073408      15898 HD_AUDIT

HD_MAINT                       HD_AUD_HIST                    TABLE               858251264       8059 HD_AUDIT_ARC

SYS                            SYS_IL0000055838C00040$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            SYS_IL0000055838C00041$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            I_AUD2                         INDEX               948240384       8904 HD_AUDIT

HD_MAINT                       SYS_IL0000093888C00040$$       LOBINDEX               106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_IL0000093888C00041$$       LOBINDEX               106496          1 HD_AUDIT_ARC

SYS                            SYS_LOB0000055838C00040$$      LOBSEGMENT             106496          1 HD_AUDIT

SYS                            SYS_LOB0000055838C00041$$      LOBSEGMENT             106496          1 HD_AUDIT

HD_MAINT                       SYS_LOB0000093888C00040$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_LOB0000093888C00041$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC