Basic idea of UNIX/Linux boot sequence.

Unix support!

As noone want to swap with me to be unix support, i am going to be an unix support!!
I will put all my time into unix learning, so, my oracle knowledge improvement will be stopped.

set -o vi

Activate the vi command set as your history editor type:
set -o vi

So that you can use [esc][hjkl] to move left, down, up, right between history.
also other vi command at command line.

Backup strategy of crs and ocr (RAC)

CRS:
1, newly complete setup
2, add/delete/modify any node

OCR:
1, newly complete setup
2, add/delete/modify any db/vip/service/listener

Quiesced state

Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements. Such a state is referred to as a quiesced state, in the sense that no ongoing non-DBA transactions, queries, fetches, or PL/SQL statements are running in the system.

ALTER SYSTEM QUIESCE RESTRICTED;

ALTER SYSTEM UNQUIESCE;

select active_state from v$instance;

NORMAL: Normal unquiesced state.
QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/start.htm#sthref603

RAC status

if any state at crs_stat -t is unknown as below:

target state
------ -------
online unknown

steps:
1, srvctl stop database -d dbname
2, srvctl stop asm -n nodename
3, srvctl stop nodeapps -n nodename
4, crsctl stop crs
5, rm -rf /tmp/.oracle/
6, rm -rf /var/tmp/.oracle/
7, crsctl start crs

New Start

I transfer to new team today. A completely new life started!!

Cross

I didn't make flash a long time. After read the illustration from web, i decide to make a flash by that idea.

All flash graphic come from http://www.infunity.com/, a cool flash online game.

Cross

DBA can login as anyone..

Steps:
1, Record dba_users.password
2, Change the password
3, Immediately login as that user
4, Immediate run: alter user [username] identified by values '[recorded password hash]'

Check time executed

set timing on

Incarnation

RMAN:>LIST INCARNATION

You can recover backups through an OPEN RESETLOGS so long as:
-You have a current, backup, or created control file that knows about the prior incarnations
-You have all available archived redo logs

References:

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr006.htm

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