Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Reorganize Audit Tables, indexes to release space

Posted by Hendry chinnapparaj on September 9, 2011

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: