3 Database audit

• Mandatory auditing
• DBA Auditing
• Standard database audit
• Fine-Grained Auditing ( FGA)
• Value base auditing

Mandatory auditing

Mandatory audit or called auditing by default .

• Database  startup/shutdown
• Connect as SYSDBA

Audit trace is store at AUDIT_FILE_DEST. Below is the sample output of the audit file.

DBA auditing :AUDIT_SYS_OPERATIONS

When We change the AUDIT_SYS_OPERATIONS to true, the database would audit all the trace for the SYSDBA. The parameter require the database bounce to make it effective.

alter system set AUDIT_SYS_OPERATIONS=true scope=spfile;

After the database bounce, in the audit file, we can see all the SQL that has execute run by SYSDBA.

 

Standard audit

Audit_trail can be set as below levels.

none/false	Disable the auditing
db	Write the audit log to database. aud$ table
db,extended	Write the audit log to database with SQL. aud$ table
os	write to OS file	audit_file_dest
xml	write to os file with XML format	audit_file_dest
xml,extended	write to os file with XML format + SQL	audit_file_dest

 

 

• Audit target can be privileges ( SELECT ANY TABLE ..etc) , Objects ( table, index ..etc) or SQL ( select , insert .. etc)
• SELECT TABLE would record the select statement for the tables belong to the schema owner. SELECT ANY TABLE would not record the select statement for the table belong to the schema owner.
• Change the audit setting only effective after user re connect. It does not apply to the current connect session.
• The different between BY ACCESS and BY SESSION would show differently in the ACTION_NAME column within the DBA_AUDIT_TRAIL table. BY SESSION only shows SESSION REC for privileges.  It wont distinguish between INSERT, DELETE, SELECT …etc.
• DBA_AUDIT_TRIAL is the view base on AUD$

AUDIT SELECT TABLE BY HR BY ACCESS;

AUDIT SELECT TABLE BY HR BY SESSION;

AUDIT SELECT ANY TABLE BY HR BY ACCESS;

AUDIT INSERT TABLE BY HR BY SESSION;

NOAUDIT SELECT TABLE BY HR;

NOAUDIT SELECT TABLE BY HR;

NOAUDIT  SELECT ANY TABLE BY HR;

NOAUDIT INSERT TABLE BY HR;

Query the trail table

select username,userhost,owner,obj_name,action_name,extended_timestamp from dba_audit_trail where owner= 'HR';

The audit trail 18 and 19, I insert 2 records to the TEST_TAB, but because the audit is set at session, therefore the ACTION_NAME column record “SESSION REC” hence we don’t know whether it is insert or select.

I change the audit level to BY ACCESS and reconnect as HR user to insert the record. The trail 20 shows the INSERT in the ACTION_NAME.

 

Fine-Grained Auditing (FGA)

FGA audit can provide more control on the audit condition.

• DBA_AUDIT_POLICIES –> List the current FGA policy. A view base on FGA$
• DBA_FGA_AUDIT_TRAIL –> store the audit information , A view base on FGA_LOG$

 

• DBMS_FGA.ADD_POLICY  -> Create the new FGA policy
• DBMS_FGA.DROP_POLIC  -> Drop the FGA policy
• DBMS_FGA.DISABLE_POLICY –> Disable the given FGA policy
• DBMS_FGA.ENABLE_POLICY –> Enable the given FGA policy

 

DBMS_FGA.ADD_POLICY

exec DBMS_FGA.ADD_POLICY (    object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST', audit_column=>'SALARY', audit_condition =>'SALARY> 10000',STATEMENT_TYPES=>'SELECT,UPDATE')

DBA_AUDIT_POLICIES

select object_schema,object_name,policy_name,policy_column,policy_text,enabled from dba_audit_policies;

DBA_FGA_AUDIT_TRAIL

select DB_USER,sql_text,extended_timestamp from  DBA_FGA_AUDIT_TRAIL

DBMS_FGA.DROP_POLIC

exec DBMS_FGA.DROP_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')

DBMS_FGA.DISABLE_POLICY

exec DBMS_FGA.DISABLE_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')

 

DBMS_FGA.ENABLE_POLICY

exec DBMS_FGA.ENABLE_POLICY( object_schema => 'HR',object_name =>'employees',policy_name => 'FGA_TEST')

KNOWN ISSUE:

If I try to create the audit_condition for the string comparison, like audit_condition =>'JOB_ID = "SH_CLERK"'. The policy would create successfully , but I would get the ORA-28112: failed to execute policy function when execute the query.

Value bases auditing with System trigger

We can create the system trigger that audit the system event such as startup, shutdown, log on ..etc

Before demo the logon trigger and logoff trigger audit.

Create table to store the audit data

CREATE TABLE logon_audit

  (

    EVENT VARCHAR2(10),

    username   VARCHAR2(30),

    sid        NUMBER,

    SESSIONID     NUMBER,

    logon_time DATE,

    logoff_time DATE,

    host       VARCHAR2(20),

    program    VARCHAR2(100)

  );

Create logon trigger

CREATE OR REPLACE TRIGGER logon_audit_trig AFTER LOGON ON DATABASE

  DECLARE

    var_program VARCHAR2(100);

  BEGIN

    SELECT program

    INTO var_program

    FROM v$session

    WHERE sid = sys_context('userenv', 'SID');

    INSERT

    INTO logon_audit VALUES

      (

        'LOGIN',

        USER,

        sys_context('userenv', 'SID'),

        sys_context('userenv', 'sessionid'),

        sysdate,

        NULL,

        sys_context('userenv', 'host'),

        var_program

      );

  END;

 

Create logoff trigger

CREATE OR REPLACE TRIGGER logoff_audit_trig BEFORE LOGOFF ON DATABASE

  DECLARE

    var_program VARCHAR2(100);

  BEGIN

    SELECT program

    INTO var_program

    FROM v$session

    WHERE sid = sys_context('userenv', 'SID');

    INSERT

    INTO logon_audit VALUES

      (

        'LOGOFF',

        USER,

        sys_context('userenv', 'SID'),

        sys_context('userenv', 'sessionid'),

        NULL,

        sysdate,

        sys_context('userenv', 'host'),

        var_program

      );

  END;

Query the logon_audit table

select * from logon_audit

Clean the env

drop trigger logon_audit_trig;

drop trigger logoff_audit_trig;

drop table logon_audit;

Resource

• Standard audit: http://www.oracle.com/technetwork/database/security/index-085803.html
• Audit syntax document http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#BCGIDBFI
• http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php
• DBA auditing: AUDIT_SYS_OPERATIONS http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams014.htm
• http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm
• http://oracledba.ezpowell.com/oracle/databaseAuditing.html

1 Oracle Tablespace usage

DBA_TABLESPACE_USAGE_METRICS

select * from DBA_TABLESPACE_USAGE_METRICS;

 

The size is base on the block.

 

DBA_HIST_TBSPC_SPACE_USAGE

DBA_HIST_TBSPC_SPACE_USAGE contains the historical usage information collect by AWR.

select TABLESPACE_ID,NAME,TABLESPACE_SIZE,TABLESPACE_USEDSIZE,RTIME from DBA_HIST_TBSPC_SPACE_USAGE,v$tablespace where TABLESPACE_ID=TS# order by RTIME,dba_hist_tbspc_space_usage.tablespace_id;

 

Reference

• http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5053.htm
• http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_3195.htm

0 Oracle interactive document

I just realize that the Oracle has the interactive version of their document. The doc is made by flash, therefore we can click or zoom in with some high level components . This file can be download from oracle site.

 

We can move the green area to zoom in. very cool …

2 Resource Manager

Resource manager provide the more flexible way to control the resource usage than using the profile.

• Resource consumer group:  Consumer group is the high level profile which can assign to the user.
• Resource plan and sub plan: Plan can includes the sub plan. The plan is grouping of the plan directive for the resource restrictions.
• Resource plan directive : Itemize resource and the limit. We can define the directive and assign it to the plan/sub plan  . The plan directive must assign to the consumer group. OTHER_GROUPS is required when create the plan directive. This group will be used by all other user which does not have explicitly consumer group assign .

 

Related tables:

• DBA_RSRC_CONSUMER_GROUPS
• DBA_RSRC_PLANS
• DBA_RSRC_PLAN_DIRECTIVES
• DBA_USERS
• DBA_RSRC_CONSUMER_GROUP_PRIVS

This demo is going to include below topics:

• Show current active resource manager
• Create consumer group
• Create plan and plan directive
• Update plan
• Assign the plan to the user
• Change or disable the resource manager
• Delete the plan
• Delete the consumer group

2 Mean Time to recovery advisor (MTTR) Advisor

To find out the current estimate MTTR, we can get the value from MTTR_Advisor or v$instance_recovery.

1 Maximum Availability Architecture (MAA) Advisor

MAA advisor provide the list of the recommendation which can improve the availability of the database.system.

The list also provide us the description of the benefits when we implement the setting .

1 Segment Advisor & Undo Advisor

Segment Advisor

Segment advisor is the job that oracle use to detect the unused space and advise DBA can reclaim the space. The segment advisor is now part of automatic maintenance job.

 

Undo Advisor

To enable the Automatic Undo management ( AUM), we need to set up below 3 parameters. Please see here for more detail.

 

 

Reference

• http://docs.oracle.com/cd/E11882_01/server.112/e10595/schema003.htm
• http://www.oracle-base.com/articles/10g/space-object-transaction-management-10g.php

1 Automated Database Maintenance Task (Auto task)& Automatic SQL Tuning (AST)

automated database maintenance task also called auto task. DBMS_AUTO_TASK_ADMIN package is used to manage the tasks.

• View the AST report from OEM
• Disable the AST
• Enable the AST
• Change the AST parameter
• Show the AST report

Related tables

• DBA_AUTOTASK_TASK
• DBA_AUTOTASK_CLIENT
• DBA_AUTOTASK_CLIENT_HISTORY
• DBA_AUTOTASK_CLIENT_JOB
• DBA_AUTOTASK_JOB_HISTORY
• DBA_AUTOTASK_OPERATION
• DBA_AUTOTASK_WINDOW_CLIENTS

 

View the AST report from OEM

Disable the AST

EXEC DBMS_AUTO_TASK_ADMIN.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);

select client_name,status from DBA_AUTOTASK_CLIENT;

Enable the AST

EXEc DBMS_AUTO_TASK_ADMIN.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);

select client_name,status from DBA_AUTOTASK_CLIENT;

Change the AST parameter

EXEC DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

Show the AST report

SET PAGESIZE 0

SET LINESIZE 1000

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET TRIMSPOOL ON

SET TRIM ON

SPOOL C:\temp\AST.txt

SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(NULL,NULL,'TEXT','TYPICAL','ALL', NULL,NULL ) FROM   dual;

SPOOL OFF

 

List all the auto task

select CLIENT_NAME,TASK_NAME,TASK_TARGET_TYPE,TASK_TARGET_NAME,OPERATION_NAME,ATTRIBUTES,STATUS,CURRENT_JOB_NAME from DBA_AUTOTASK_TASK;

SELECT A.CLIENT_NAME,A.TASK_NAME,A.TASK_TARGET_TYPE,A.TASK_TARGET_NAME,A.OPERATION_NAME,A.ATTRIBUTES,A.STATUS,A.CURRENT_JOB_NAME,B.CONSUMER_GROUP,B. MEAN_JOB_DURATION

FROM DBA_AUTOTASK_TASK A,DBA_AUTOTASK_CLIENT B

where A.CLIENT_NAME = B.CLIENT_NAME

DBA_AUTOTASK_WINDOW_CLIENTS

List work window

select * from DBA_AUTOTASK_WINDOW_CLIENTS

Reference

• http://www.sqlpanda.com/2013/02/automated-database-maintenance-task.html
• http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm

1 SQL Performance Analyzer (SPA)

SPA compare the SQL Performance change before and after the database changes. The database changes includes:

• database upgrade
• Parameter changes
• Update statistic

 

• Use PL/SQL to perform the SPA
• Use OEM to preform the SPA
• Drop the analysis task
• Drop The SQL Tuning set

 

Use PL/SQL to perform the SPA

Create the test user

CREATE USER spa_user IDENTIFIED BY spa_user QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO spa_user;

 

Test user create the test table and run some quires

CREATE TABLE test_objects AS SELECT * FROM all_objects;

EXEC DBMS_STATS.gather_table_stats(USER, 'test_objects', cascade => TRUE);

SELECT COUNT(*) FROM test_objects WHERE object_id <= 1000;

SELECT object_name FROM test_objects WHERE object_id = 1000;

SELECT COUNT(*) FROM test_objects WHERE object_id <= 3000;

SELECT object_name FROM test_objects WHERE object_id = 3000;

SELECT COUNT(*) FROM test_objects WHERE object_id BETWEEN 1000 AND 3000;

Create the SQL Set

EXEC DBMS_SQLTUNE.create_sqlset(sqlset_name => 'SPA_STS');

DECLARE

  l_cursor  DBMS_SQLTUNE.sqlset_cursor;

BEGIN

  OPEN l_cursor FOR

     SELECT VALUE(a)

     FROM   TABLE(

              DBMS_SQLTUNE.select_cursor_cache(

                basic_filter   => 'sql_text LIKE ''%test_objects%'' and parsing_schema_name = ''SPA_USER''',

                attribute_list => 'ALL')

            ) a;

  DBMS_SQLTUNE.load_sqlset(sqlset_name     => 'SPA_STS',

                           populate_cursor => l_cursor);

END;

/

-- Confirm the SQL Set

SELECT sql_text FROM   DBA_SQLSET_STATEMENTS WHERE  sqlset_name = 'SPA_STS';

 

Create Analysis task

variable spa_task  VARCHAR2(64);

EXEC :spa_task :=  DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'SPA_STS', task_name => 'MY_SPA_TUNE', description => 'Task to test SPA');

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name =>'MY_SPA_TUNE',execution_type=>' test execute',execution_name=>'before_change');

select task_name,status from USER_ADVISOR_LOG  where task_name like 'MY_SPA_TUNE';

Test user create the index on the test table

Use SPA to analysis the impact after the index is being created and compare the before and after

EXECUTE DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name =>'MY_SPA_TUNE',execution_type=>' test execute',execution_name=>'after_change');

select task_name,status from USER_ADVISOR_LOG  where task_name like 'MY_SPA_TUNE'; 

exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'MY_SPA_TUNE',execution_type   => 'compare performance', execution_params => dbms_advisor.arglist( 'execution_name1', 'before_change', 'execution_name2', 'after_change')  );

 

General the report and output the report to html

SET PAGESIZE 0

SET LINESIZE 1000

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET TRIMSPOOL ON

SET TRIM ON

SPOOL C:\temp\execute_comparison_report.htm

SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('MY_SPA_TUNE','HTML','ALL') FROM   dual;

SPOOL OFF

 

View the report in the OEM cloud control

Use OEM to preform the SPA

In this test, I choice “Parameter change”

I use the same SQL SET and choice the “optimizer_index_cost_ad”, the default value is 100, I changed to 10.

Here is the report.

DROP the analysis task

 exec DBMS_SQLPA.DROP_ANALYSIS_TASK(task_name => 'MY_SPA_TUNE')

DROP The SQL Tuning set

 EXEC DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'SPA_STS');

If the SQL set is still being used by other tuning tasks, the sql set can not be dropped. If we try to drop it, we would get the error: ORA-13757

ORA-13757: "SQL Tuning Set" "SPA_STS" owned by user "SYS" is active
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13213
ORA-06512: at "SYS.DBMS_SQLTUNE", line 4563
ORA-06512: at line 1

Reference

• http://www.oracle-base.com/articles/11g/sql-performance-analyzer-11gr1.php
• http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqlpa.htm