Wednesday, May 29, 2013

Alert: ASH performed an emergency flush

Alert

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 123...7 bytes.


Cause

This alert indicate sudden increase of database active sessions,  that fill-up ASH buffer faster then usual and therefore raise this alert. Actually this is not a problem but an indication of more  ASH buffer is needed to  support peak activity on DB.


Solution

 _ASH_SIZE is a hidden parameter and oracle not suggested to set such parameter without consulting their support. There is a meta link doc ( id 1385872.1) suggest we can increase 50% of current  ASH buffer, if we repeatedly receive this alert.   To monitor use this 

SELECT total_size,awr_flush_emergency_count FROM v$ash_info;

Monday, May 13, 2013

Oracle Wallet: secure external password storage


Some times we need database connection from shell script stored on file system. This can be a security issue, if the script contains database connection credential. To nullify this problem oracle provide a solution called wallet. Oracle wallet is a client-side secure external password container where DB login credentials are stored. Using this shell scripts can connect to DB using the "/@db_alias" syntax.

Step 1 : Set location for wallet

we Like to put the wallet files in $ORACLE_HOME/network/admin. Thus the location will be '/oracle/product/11.2.0/dbhome_1/network/admin'. Add following lines to sqlnet.ora

WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/oracle/product/11.2.0/dbhome_1/network/admin)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0


Step 2: Set DB alias

Add the following lines to listerer.ora

ora_db =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA.DB1.ORACLE.COM) ) )


Step 3: Create Wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -create

You will ask to enter password. The password length must be 8+ containing alpha-numeric characters.


Step 4: Add database login credentials into wallet

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -createCredential ora_db scott tiger

This will ask for password conformation and you shout give the same password which you gave when creating wallet.


Step 5: Listing credentials present in wallet 

mkstore -wrl '/oracle/product/11.2.0/dbhome_1/network/admin' -listCredential


Step 6: Connect Db using wallet

sqlplus /@ora_db

Wednesday, May 1, 2013

Oracle Recovery Manager (RMAN) Overview

In general, backup & recovery combines several strategies & procedures which are protecting database against data lose and reconstructing the database after any kind of data loss.


There are two types of backup strategies:
  1. Physical Backup – are backups of the physical files used in restoring &recovering database, such as datafiles, controlfiles and archived redo logs. 
  2. Logical Backup – contains logical data exported from database with oracle utilities (i.e, datapump) and stored in a binary file.

Physical backup is the foundation of any sound backup & recovery strategy. Logical backup is a useful supplement to physical backup but not ensure sufficient protection against data loss without physical backup. Unless otherwise specified, the term backup refers to physical backup.


Oracle provides two backup & recovery solutions –
  • Recovery Manager (RMAN) managed backup & recovery.
  • User managed backup & recovery.

RMAN can take backups of –
  1. Datafiles
  2. Controlfiles
  3. Redo logs / archived redo logs
  4. Serve parameter file (SPFiles)

A database recovery involved two tasks:
  • Restore- retrieve datafiles or controlfiles from backup.
  • Recover- apply archived & on-line redo log changes on restored datafiles ( it is also known as media recovery)