Search This Blog

Saturday, February 11, 2012

Oracle Database Best Practices.

1) Maintain atleast three controlfiles.

2) Set CONTROL_FILE_RECORD_KEEP_TIME Large Enough.

(a) The CONTROL_FILE_RECORD_KEEP_TIME specifies the number of days
    that records are kept within the controlfile before being reused.

(b) Set the parameter to slightly longer than the oldest backup file
    that is to be kept on disk.

3) Configure the Size of Redo Log Files and Groups Appropriately.

(a) All online redo log files should be of the same size.
(b) A logswitch should approximately happen once an hour.
(c) The logswitch should not happen more frequently than every
    20 minutes during peak activities.
(d) There should be atleast 4 log groups to prevent LGWR wait.


4) Multiplex Online Redo Log Files.

(a) Atleast 3 members and 1 member on a different disk
    controller.

5) Enable ARCHIVELOG Mode.

(a) Production database should be in archive log mode.

6) Enable Block Checksums.

(a) Set DB_BLOCK_CHECKSUM to TRUE. This checks for data and log block checksums.
    Block level corruptions are also detected by RMAN whenever a backup is taken.
(b) It will incur overhead in you system.

7) Enable Database Block Checking.

(a) Set DB_BLOCK_CHECKING to true. With this setting whenever a block is modified,
    Oracle checks the block for consistency. If its inconsistent it is marked corrupt
    and an ORA-1578 error is returned.
(b) It will cause an overhead between 1 to 10 percent.

8) Log Checkpoints to the Alert Log.

(a) Set LOG_CHECKPOINT_TO_ALERT to TRUE. This setting will cause the checkpoints to be logged
    in the alert logfile.

9) Use Fast-Start Checkpointing to Control Instance Recovery Time.

(a) Set FAST_START_MTTR_TARGET to appropriate value to to speed up
    instance recovery time.

10) Capture Performance Statistics About Timing.

(a) Set TIMED_STATISTICS to TRUE to gather Oracle event timing data.
    This parameter is set to true if STATISTICS_LEVEL parameter is
    set to TYPICAL.

11) Use Automatic Undo Management.

(a) Use Automatic Undo Management in your Database.
    New Oracle Technologies such as "FLASHBACK"
    require that Undo Management should be auto.

(b) Set a UNDO_RETENTION value.
(c) Enable RETENTION GUARNANTEE for your Undo Tablespace.

12) Use Locally Managed Tablespaces.

(a) Locally management tablespaces are better in performance than Dictionary
    Managed Tablespaces. Also certain advanced features like "Segment Advisor"
    require that the tablespace should be Locally Managed.

(b) Can be set by using "EXTENT MANAGEMENT LOCAL" clause at the time of tablespace
    creation. 

13) Use Automatic Segment Space Management.
 
(a) Automatic segment space management simplifies space administration tasks,
    thus reducing the chance of human error.

(b) Can be set by using "SEGMENT SPACE MANAGEMENT AUTO" clause at the time of tablespace
    creation. 

14) Use Temporary Tablespaces and Specify a Default Temporary Tablespace.

15) Use Resumable Space Allocation.

(a) This feature provides a mechanism to suspend and resume database operations
    if there are ay space allocation failures.
    The affected operation is suspended instead of the database returning an error.
    When the space problem is resolved, the suspended operation automatically resumes.
(b) Set RESUMABLE_TIMEOUT parameter to appropriate number of seconds.

16) Use a Flash Recovery Area.

(a) It provides a centralized location for backup and recovery files.

17) Enable Flashback Database.

(a) Provides an much faster alternative to point-in-time-recovery, because it does
    not require restore and recovery operations.

18) Set Up and Follow Security Best Practices.

(a) Follow best security practices.
    Threat to corporate data comes from employees
    and contractors with internal access to networks and 
    facilities.
    
(b) Use Column level encryption(TDE)(10g).
    Tablespace level encryption(11g).

19) Use a Server Parameter File.

0 comments:

Post a Comment