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

SETUP Database Audit and House Keeping in Oracle10G

Posted by Hendry chinnapparaj on September 9, 2011

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

 

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: