Thursday, February 28, 2013

0 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.

image

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;

image

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

image

 

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

 

image

 

  • 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';

image

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')

image

DBA_AUDIT_POLICIES

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

image

DBA_FGA_AUDIT_TRAIL

select DB_USER,sql_text,extended_timestamp from  DBA_FGA_AUDIT_TRAIL

image

DBMS_FGA.DROP_POLIC

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

image

DBMS_FGA.DISABLE_POLICY

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

image

 

DBMS_FGA.ENABLE_POLICY

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

image

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.

image

image

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)
  );

image

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;

 

image

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;

image

Query the logon_audit table

select * from logon_audit

image

Clean the env

drop trigger logon_audit_trig;
drop trigger logoff_audit_trig;
drop table logon_audit;

image

Resource

Wednesday, February 27, 2013

1 Oracle Tablespace usage

DBA_TABLESPACE_USAGE_METRICS

select * from DBA_TABLESPACE_USAGE_METRICS;

 

image

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;

image

 

Reference

Sunday, February 24, 2013

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.

 

image

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

image

image

0 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

image

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

0 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.

image

0 Maximum Availability Architecture (MAA) Advisor

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

image

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.

 

image

image

image

Undo Advisor

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

 

image_thumb[5]

image_thumb[3]

image_thumb[1]

 

Reference

0 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

image

image

image

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;

image

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;

image

Change the AST parameter

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

image

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;

image

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

image

DBA_AUTOTASK_WINDOW_CLIENTS

List work window

select * from DBA_AUTOTASK_WINDOW_CLIENTS

image

Reference

Saturday, February 23, 2013

0 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;

image

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';

 

image

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';

image

Test user create the index on the test table

image

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')  );

 

image

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

 

image

View the report in the OEM cloud control

image

image

image

Use OEM to preform the SPA

image

In this test, I choice “Parameter change”

image

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

image

Here is the report.

image

DROP the analysis task

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

image

DROP The SQL Tuning set

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

image

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

image

Reference

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates