Fine-grained audit usage

-DBMS_FGA
-ADD_POLICY Procedure
    DBMS_FGA.ADD_POLICY(
       object_schema VARCHAR2,
       object_name VARCHAR2,
       policy_name VARCHAR2,
       audit_condition VARCHAR2,
       audit_column VARCHAR2,
       handler_schema VARCHAR2,
       handler_module VARCHAR2,
       enable BOOLEAN,
       statement_types VARCHAR2,
       audit_trail BINARY_INTEGER IN DEFAULT,
       audit_column_opts BINARY_INTEGER IN DEFAULT);
-DROP_POLICY Procedure
    DBMS_FGA.DROP_POLICY(
       object_schema VARCHAR2,
       object_name VARCHAR2,
       policy_name VARCHAR2 );
-ENABLE_POLICY Procedure
    DBMS_FGA.ENABLE_POLICY(
       object_schema VARCHAR2,
       object_name VARCHAR2,
       policy_name VARCHAR2,
       enable BOOLEAN);
-DISABLE_POLICY Procedure
    DBMS_FGA.DISABLE_POLICY(
       object_schema VARCHAR2,
       object_name VARCHAR2,
       policy_name VARCHAR2 );

Flashback query usage

-Initial setup - turn on Supplemental logging
    SQL>alter database add supplemental log data;
-Tunes the system to give the best possible undo retention for the undo tablespace.
    UNDO_MANAGEMENT
    UNDO_RETENTION
-Grant flashback privileges to users
    For the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK
    For Flashback Transaction Query, grant the SELECT ANY TRANSACTION privilege.
    For Flashback Query and Flashback Version Query, grant FLASHBACK and SELECT privileges on specific objects to be accessed during queries or grant the FLASHBACK ANY TABLE privilege to allow queries on all tables.
-Using Flashback Query
    SELECT ... AS OF
    INSERT INTO ...(SELECT ... AS OF)
    CREATE ... AS SELECT ... AS OF

Logminer usage

-Initial setup - turn on Supplemental logging
    SQL>alter database add supplemental log data;
-Extract dictionary
    There has 3 method to extract dictionary

    In the online catalog:
       SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR(-
       OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
    In the redo log files:
       SQL>EXECUTE DBMS_LOGMNR_D.BUILD( -
       OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
    A Flat file:
       (need to set UTL_FILE_DIR of init.ora and restart DB)
       SQL>EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora', -
       '/oracle/database/', -
       DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
-Add redo log files for analysis
    SQL>EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => '/oracle/logs/log1.f', -
    OPTIONS => DBMS_LOGMNR.NEW);
-Start logminer session
    SQL>Execute DBMS_LOGMNR.START_LOGMNR(options=>…..)
-query v$logmnr_contents
    SQL>SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE...
-End logminer session
    Execute DBMS_LOGMNR.END_LOGMNR;

Logminer vs Fine-grained audit vs Flashback query

Logminer
-Started: 8i
-Data source: redo log, archive redo log
-Target user: DBA
-Performance overhead: Nil to low (supplemental log)

Regular audit trail
-Started at: ?
-Data source: DBA_AUDIT_TRAIL (from AUD$)
-Target user: DBA
-Performance overhead: Low to medium (extra logging)

Fine-grained audit
-Started: 9i
-Data source: DBA_FGA_AUDIT_TRAIL (from FGA_LOG$)
-Target user: DBA
-Performance overhead: Low to medium (triggers)

Flashback query
-Started at 9i
-Data source: undo segment, flash recovery area/archive
-Target user: end-users, DBA
-Performance overhead: low (background process FBDA)

References:
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-transactionmanagement.html http://www.oracle.com/technology/deploy/availability/pdf/32494_OW2002_PPT.pdf http://echo.sharera.com/blog/BlogTopic/5555.htm http://blog.roodo.com/mywork/archives/4513199.html

Pass parameter to awk

POS=4
awk '{print $POSV'} POSV=$POS

RAID

These images are well self explained.

References:
http://en.wikipedia.org/wiki/Nested_RAID_levels
http://en.wikipedia.org/wiki/RAID

Sequential Read vs Scattered Read

DB file sequential read waits:

single block read into one SGA buffer

contention on index reads

V$SESSION_WAIT parameter P3 (The number of blocks) should be 1

DB file scattered read waits:

multiblock read into many discontinuous SGA buffers

contention on full table scans

V$SESSION_WAIT parameter P3 (The number of blocks) should be greater than 1

Direct read:

single or multiblock read into the PGA, bypassing the SGA

Parameter:
db_file_multiblock_read_count which might affect the chose of read method, however, starting at Oralce 10g, it is auto tuned parameter if it is not set explicitly.

Reference:
http://book.csdn.net/bookfiles/626/10062620033.shtml
http://www.dba-oracle.com/art_builder_io_speed.htm

http://www.orafaq.com/node/1

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref720