Automatic Storage Management (ASM) lessons 2

ASM Processes
After you start your ASM instance, you will find that several of the Oracle processes you
are acquainted with will be running, such as PMON and DBWR. Additional ASM processes
will be started too. These processes include the following in 11g,

• The ARBn process, used to perform disk group rebalance operations. There may be one or more of these processes running.
• The ASMB process manages ASM storage and provides statistics. 
• The GMON process maintains disk membership in ASM disk groups. 
• The KATE process performs proxy I/O to ASM metadata files when a disk is offlined. 
• The MARK process is responsible for marking ASM allocation units as stale following a missed write to an offline disk.
• The RBAL process runs in both database and ASM instances. RBAL is responsible for performing a global open of ASM disks in normal databases. RBAL coordinates rebalance activity for disk groups in ASM instances.

ASM Disk Discovery

ASM disk discovery is the first step to setting up an ASM disk group. In this section, we

will cover configuring the ASM_DISKSTRING parameter, which helps with ASM disk discovery, and then we will discuss the topic of ASM disk discovery in general.

You may not need to set ASM_DISKSTRING. ASM_DISKSTRING has a number of different
default values depending on the platform you are using. Below table lists the platform-
specific default values (these will be set if ASM_DISKSTRING is set to a NULL value only).

#    Default asm_diskstring values for supported platforms:

#     Solaris (32/64 bit)   /dev/rdsk/*
#     Windows NT/XP         \\.\orcldisk* 
#     Linux (32/64 bit)     /dev/raw/*
#     HPUX                     /dev/rdsk/*
#     HPUX(Tru 64)          /dev/rdisk/*
#     AIX                   /dev/rhdisk/*

For example in my windows system i did'nt set any ASM_DISKSTRING, so it taken the default setting ,
SQL> show parameter asm

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
asm_diskgroups                       string      DATA, FLASH
asm_diskstring                       string
asm_power_limit                      integer     1
SQL>
SQL> select path from v$asm_disk;
PATH
-----------------------------------------------------------------------------
\\.\ORCLDISKDATA0
\\.\ORCLDISKFLASH0
SQL>
The ASM_DISKSTRING can be dynamically altered, which is nice if your friendly system administratoradds some storage to your system that you want Oracle to be able to use.

The asterisk is required when defining the ASM_DISKSTRING parameter. Here are
some examples of setting the ASM_DISKSTRING parameter. In this first example, ASM will
look for disks in devices when we create disk groups:
SQL> Alter system set ASM_DISKSTRING=’/devices/*‘;
In the next example, we are pointing ASM_DISKSTRING to ORACLE_HOME/disks:
SQL> Alter system set ASM_DISKSTRING=’?/disks/*‘;

ASM Disk Discovery on Instance Start:
When the ASM instance is started, it will use the paths listed in the ASM_DISKSTRING parameter and discover the disks that are available.Once discovery is complete and the ASM instance is open, you can review the disks discovered by looking at the V$ASM_DISK view, as

SQL> column path format a20

set lines 132

set pages 50

select path, group_number group_#, disk_number disk_#, mount_status,

header_status, state, total_mb, free_mb

from v$asm_disk

order by group_number;

PATH GROUP_# DISK_# MOUNT_S HEADER_STATU STATE TOTAL_MB FREE_MB

-------------- ------- ------ ------- ------------ ----- -------- ---------

/dev/raw/raw4 0 1 CLOSED FOREIGN NORMAL 39 0

/dev/raw/raw5 0 0 CLOSED FOREIGN NORMAL 39 0

/dev/raw/raw3 0 2 CLOSED FOREIGN NORMAL 39 0

/dev/raw/raw6 0 2 CLOSED CANIDATE NORMAL 2048 2048

ORCL:ASM01_004 1 3 CACHED MEMBER NORMAL 34212 30436

ORCL:ASM01_005 1 4 CACHED MEMBER NORMAL 34212 30408

ORCL:ASM01_006 1 5 CACHED MEMBER NORMAL 34212 30420

In this view, you see that there are three disks (/dev/raw/raw3,/dev/raw/raw4,/dev/raw/raw5) that are not assigned to any group (thosewith GROUP_# set to 0). These are unassigned disks that ASM has discovered but that have not been assigned to a disk group. Note the mount status of CLOSED on those three disks, which also indicates that the disk is not being accessed by ASM. The HEADER_STATUS of FOREIGN indicates that these disks contain data already and are owned by some process other than ASM (in this case, these are voting disks for a RAC). If the HEADER_STATUS says CANIDATE, as with /dev/raw/raw6, then we could add this disk to an ASM disk group.

Notice that most of the disks have a MOUNT_STATUS of CACHED and a HEADER_STATUS of

MEMBER. This means that the disk is currently part of an ASM disk group (which we will

discuss more in the next section).

Redundancy, Striping, and Other ASM Topic

Redundancy:-

When configuring an ASM disk group, you can use one of three different ASM redundancy

setting options to protect the data in your disk group:

Normal : Typically employs two-way mirroring by default and thus requires allocation

of two failure groups.

High  : Typically employs three-way mirroring by default and thus requires allocation of

three failure groups.

External  : Does not employ any mirroring. This setting is typically used when the disk

group is being assigned to an external disk that is attached to some device that already

employs some disk redundancy (rely on hardware mirroring) and have only one failure group.

Each failure group represents a logical allocation of one or more disks to the ASM disk group and provides for mirroring within that disk group. Thus, when you create an ASM disk group, you might have one disk assigned to failure group 1 and one disk assigned to failure group 2. This way your data is protected from failure.When you’re using ASM mirroring, ASM will allocate an extent on a disk that becomes the primary copy (one of the failure groups) and then allocate copies of that extent to the mirrored copies (the other failure groups). 

When you define the redundancy setting for a disk group, you are defining things such

as what kind of striping occurs and whether the data will be mirrored. These attributes are defined based on which template you have assigned to the ASM disk group. By default, when you create a disk group, Oracle will assign it the default template setting. You can optionally assign another ASM template to a given disk group. Each Oracle file type has its own default template.

Here is a list of the "default" (built-in) templates and their attributes:

Template name	Striping	Mirroring when using a normal redundancy disk group	Mirroring when using a high redundancy disk group	Mirroring when using an external redundancy disk group
Controlfile	Fine	3-Way Mirroring	3-Way Mirroring	No Mirroring
Datafile	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Onlinelog	Fine	2-Way Mirroring	3-Way Mirroring	No Mirroring
Archivelog	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Tempfile	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Backupset	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Parameterfile	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Dataguardconfig	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Flashback	Fine	2-Way Mirroring	3-Way Mirroring	No Mirroring
Changetracking	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Dumpset	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Xtransport	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring
Autobackup	Coarse	2-Way Mirroring	3-Way Mirroring	No Mirroring

In addition to these "named" templates you can also create user-defined templates.  These user-defined templates appear in the "name" column of the v$asm_template view.

Default ASM Template Redundancy Settings

So, if you create a disk group with normal redundancy using the default template and

you put datafiles on it, the datafile template would be used by default. In this case, a

datafile would use two-way mirroring and coarse striping (see the section “Striping”).

This means you would have to allocate at least two disks to an ASM disk group when

it was created, one assigned to a different failure group.The DBA can mention whether the files created via the template should be 2-way or 3-way mirrored and Coarse or fine striped.

Template attributes

Striping Attribute Value	Description
FINE	Striping in 128 KB chunks.
COARSE	Striping in 1 MB chunks.

Redundancy Attribute Value	Resulting Mirroring in Normal Redundancy Disk Group	Resulting Mirroring in High Redundancy Disk Group	Resulting Mirroring in External Redundancy Disk Group
MIRROR	Two-way mirroring	Three-way mirroring	(Not allowed)
HIGH	Three-way mirroring	Three-way mirroring	(Not allowed)
UNPROTECTED	No mirroring	(Not allowed)	No mirroring

Example for add template,

ALTER DISKGROUP disk_group_name ADD TEMPLATE template_name 
  ATTRIBUTES ([{MIRROR|HIGH|UNPROTECTED}] [{FINE|COARSE}]);

The following statement creates a new template named reliable for the normal redundancy disk group dgroup2:

ALTER DISKGROUP dgroup2 ADD TEMPLATE reliable ATTRIBUTES (HIGH FINE);

When you add a template to a disk group, the template cannot be retroactively

applied to files already in that disk group. As a result, you will need to use RMAN

to back up and then restore files that already exist in the disk group in order for them to

take on the attributes of the new template.

You can see the templates associated with a given disk group by querying the V$ASM_

TEMPLATE view, as shown in this example:

SQL> select * from v$asm_template where group_number=1;

We can alter diskgroup command. Notice in this example that we are actually changing one of the attributes of the default templates. You cannot drop the default templates, but you can modify them, as shown here:

SQL> ALTER DISKGROUP sp_dgroup2 ALTER TEMPLATE datafile ATTRIBUTES (coarse);

ASM Disk Group Attributes

Oracle Database 11g also allows you to define specific disk group attributes. Disk group attributes are set using the attribute clause of the create diskgroup and alter

diskgroup commands. The following attributes can be set on a specific ASM disk group:

Au_size-This is the disk group allocation unit (AU) size. The value defaults to 1MB and

can be set only when the disk group is created. You must modify the AU size of the disk

group if you want the disk group to be able to hold larger amounts of data. A disk group

with the default AU size will be able to grow to 35TB (normal redundancy). Increasing the

AU size will significantly increase the maximum size of the disk group. The maximum AU

size is 64MB.

Compatible.rdbms- Indicates the database version that the disk group is compatible with

at a minimum (default is 10.1). This value should be equal to or greater than the compatibility parameter of the database(s) accessing the ASM disk group. This value cannot be rolled back once set.

Compatible.asm- Indicates the ASM instance version that the disk group is compatible with ata minimum (default is 10.1). Compatible.asm must always be set to a value equal to or greater than compatible.rdbms. Once compatible.asm is set for a disk group, it can not be rolled back to an earlier value.

Disk_repair_time- Indicates the length of time that the disk resync process should maintain

change tracking before dropping an offline disk. The default for this parameter is 3.6 hours. Disk group attributes can be viewed using the V$ASM_ATTRIBUTE view. You can see some examples of setting compatibility here:

SQL>Create diskgroup robert01 external redundancy

Disk ’/oracle/asm/ASM_DISKGROUP_robert01.asm’

Attribute ’ccompatible.asm’=’11.1.0’;

SQL>Alter diskgroup robert01 set attribute ’DISK_REPAIR_TIME’=’1200M’;

SQL>Alter diskgroup robert01 set attribute ’compatible.asm’=’11.1.0’;

ASM Fast Disk Resync

Disk loss in ASM can result from a number of reasons, such as loss of controller cards, cable failures, or power-supply errors. In many cases, the disk itself is still intact. To allow for sufficient time to recover from disk failures that do not involve the actual failure of a disk, ASM provides the ASM fast disk resync feature.

By default, when a disk in an ASM disk group fails the disk will be taken offline automatically. The disk will be dropped some 3.6 hours later. As a result, you have only 3.6 hours by default to respond to a disk outage. If you correct the problem and the physical disk media is not corrupted, then ASM fast disk resync will quickly resynchronize the disk when it comes back online, correcting the problem very quickly.You can change the amount of time that Oracle will wait to automatically drop the disk by setting the disk_repair_time attribute for the individual disk groups using the alter diskgroup command, as shown in this example, where we set the disk_repair_time attribute to 18 hours:

SQL> Alter diskgroup dgroup1 set attribute ‘disk_repair_time’=’18h’;

ASM Preferred Mirror Read

Your ASM configuration may involve remote mirroring to disks that are a fair distance away. When some of your disk mirrors are far away then those disks may not be the best set of disks for a given instance to read from. For example, you might have a Real Application Cluster database with local and remote mirrored disks. In this case, you want to have the RAC instances primarily read from the local disks to ensure the best performance. ASM preferred mirror read is designed to indicate to Oracle which disk failgroup is the preferred read disk group.
ASM preferred mirror read is only available if you are using RAC. Also preferred mirror read is generally used only with clustered ASM instances, but this is not a requirement. To take advantage of ASM preferred mirror read, you should configure each disk failure group with specific geographically located set of disks. Use the Oracle 11g parameter, asm_preferred_read_failure_groups, to configure a databse instance with a list of preferred disk failure group names to use when that instance accesses ASM disks. The format of the values of the asm_preferred_read_failure_groups parameter is diskgroupname.failuregroupname where diskgroupname is the name of the disk group that the failure group belongs to and failuregroupname is the preferred failure groups name. Include multiple diskgroup/failgroup names by separating each preferred read group defined with a comma as seen in this example:

asm_preferred_read_failure_groups=dgroup1.fdisk2, dgroup2.fdisk2

In the event ASM cannot read from the preferred disk failure group, then the non-preferred failure groups will be read. To determine if a given disk file group is a preferred disk group you can use the PREFERRED_READ column of the V$ASM_DISK view.

Adding an ASM Disk Group

You use the create diskgroup command to create an ASM disk group,

SQL> CREATE DISKGROUP DATA_AREA NORMAL REDUNDANCY
failgroup diskcontrol1 DISK
‘/devices/diska1’,‘/devices/diska2’,‘/devices/diska3’,‘/devices/diska4’
failgroup diskcontrol2 DISK
‘/devices/diskb1’,‘/devices/diskb2’,‘/devices/diskb3’,‘/devices/diskb4’;

we created as diskgroup named DATA_AREA with normal redundancy having two failure group, each contains four disks.From the following figure you can see how data is mirrored in disk group.

If we used high redundancy, we would need to add a third disk to the command,

as shown here:

SQL> CREATE DISKGROUP DATA_AREA HIGH REDUNDANCY

failgroup diskcontrol1 DISK
‘/devices/diska1’,‘/devices/diska2’,‘/devices/diska3’,‘/devices/diska4’
failgroup diskcontrol2 DISK
‘/devices/diskb1’,‘/devices/diskb2’,‘/devices/diskb3’,‘/devices/diskb4’;

failgroup diskcontrol3 DISK
‘/devices/diskc1’,‘/devices/diskc2’,‘/devices/diskc3’,‘/devices/diskc4’;

 You can also name the disks being assigned to the ASM disk group using the name
clause of the create diskgroup command . For example we can use
‘/devices/diska1’ name diska1,‘/devices/diska2’ name diska2,‘/devices/diska3’ name diska3,‘/devices/diska4’ name diska4  instead of ‘/devices/diska1’,‘/devices/diska2’,‘/devices/diska3’,‘/devices/diska4’ in the above command. Failure to use the name clause will result in each disk receiving its own system-default assigned name.

Dropping an ASM Disk Group

To remove an ASM disk group, you use the drop diskgroup command. By default, if any

files exist in the disk group, ASM will not allow you to drop it unless you use the including

contents clause.

Here is an example of removing an ASM disk group with the drop diskgroup command:

SQL> Drop diskgroup sp_dgroup2;

If the ASM disk group has files in it, use this version:

SQL> Drop diskgroup sp_dgroup2 including contents;

Adding Disks to an ASM Disk Group

As databases grow, you need to add disk space. The alter diskgroup command allows

you to add disks to a given disk group to increase the amount of space available. Adding

a disk to an existing disk group is easy with the alter diskgroup command,

SQL>alter diskgroup cooked_dgroup1 add disk ‘c:\oracle\asm_disk\_file_disk3’

name new_disk;

In the preceding example we did not assign the disk to a specific failure group. As a result, each disk will be assigned to its own failure group when it’s created. For example, when we added the disk to the cooked_dgroup1 disk group, a new failure group called cooked_dgroup1_0002 was created, as shown in this output:

SQL> select disk_number, group_number, failgroup from v$asm_disk;

DISK_NUMBER GROUP_NUMBER failgroup

----------- ------------ ------------------------------

1 0

0 1 DISKCONTROL1

1 1 DISKCONTROL2

2 1 COOKED_DGROUP1_0002

We can add a disk to an existing failure group by using the failgroup parameter, as

shown in this example:

SQL> alter diskgroup cooked_dgroup1 add failgroup DISKCONTROL1

disk ‘c:\oracle\asm_disk\_file_disk4’ name new_disk;

Removing Disks from an ASM Disk Group

The alter diskgroup command allows you to remove disks from an ASM disk group using

the drop disk parameter. ASM will first rebalance the data on the disks to be dropped,

assuming enough space is available. If insufficient space is available to move the data from

the disk to be dropped to another disk, then an error will be raised. Here is an example

of dropping a disk from a disk group:

SQL>alter diskgroup cooked_dgroup1 drop disk ‘c:\oracle\asm_disk\_file_disk4’;

The alter diskgroup command also gives you the option to drop from a failure group

all disks that are assigned to the disk group. Use the in failgroup keyword and then indicatethe name of the failure group, as shown in this example:

SQL>alter diskgroup cooked_dgroup1 drop disks in failgroup diskcontrol1;

When you drop a disk from a disk group, the operation is asynchronous. Therefore,

when the SQL prompt returns, this does not indicate that the operation has completed. To

determine if the operation has completed, you will need to review the V$ASM_DISK view.

When the disk drop is complete the column HEADER_STATUS will take on the value of FORMER,as shown in this example:

SQL> select disk_number, header_status from v$asm_disk;

DISK_NUMBER HEADER_STATU

----------- ------------

0 FORMER

1 FORMER

1 MEMBER

2 MEMBER

If the drop is not complete (the V$ASM_DISK column STATE will read dropping), you can

check the V$ASM_OPERATION view and it will give you an idea of how long the operation is expected to take before it is complete. Here is an example query that will provide you with this information:

SQL> select group_number, operation, state, power, est_minutes from v$asm_operation;

Undropping Disks from an ASM Disk Group

If you have accidentally dropped a disk, simply use the alter diskgroup command with the undrop disks parameter, as shown here:

SQL> alter diskgroup sp_dgroup2 undrop disks;

To undrop a disk, the header_status from v$asm_disk view must be dropping. You cannot undrop a completely dropped disk.

Resizing Disks in an ASM Disk Group

ASM diskgroup is full and we want to add space to diskgroup. We have 2 options here,
1. Add new disks depending on type of diskgroup / number of failgroups
2. Increase the size of the existing LUN / LV / Disk partition

First approach is pretty straight forward For example, if we have a NORMAL redundancy ASM diskgroup DATADG with 2 failgroups then we can add two disk in each failure group as follows ,

SQL> alter diskgroup DATADG add 
FAILGROUP DATADG_FG1 disk '/dev/raw/raw3' 
FAILGROUP DATADG_FG2 disk '/dev/raw/raw4' ;

This approach does not need any down time i.e. disks can be added when ASM & DB are running (Rebal operation will have some overhead, so we should be doing this when system is not heavily used).
Now second approach , 

First we need to extend LUN or LV or disk partition, so that OS can see the revised size. But here is the twist, If we are using oracle 10g, we’ll have to bounce the ASM instance (and database), so that ASM can read the new disk size. This is due to Bug 4110313 : So if you try to increase the diskgroup size using following command, command will not have any effect (although OS can see revised disk size)

SQL> select name, total_mb, usable_file_mb from v$asm_diskgroup;
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
DATADG 69688 3460

SQL> alter diskgroup DATADG resize all;
Diskgroup altered.

SQL> select name, total_mb, usable_file_mb from v$asm_diskgroup;
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
DATADG 69688 3460

This problem is fixed in 11g and we don’t have to recycle ASM instance

SQL> select name, total_mb, usable_file_mb from v$asm_diskgroup;
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
DATADG 69688 3460

SQL> alter diskgroup DATADG resize all;
Diskgroup altered.

SQL> select name, total_mb, usable_file_mb from v$asm_diskgroup;
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
DATADG 139320 38276

Some good question and answer from 10g OCP Certification Exam Guide part 1

Questions
1. The location of the alert log is controlled by a parameter. Which one? (Choose
the best answer.)
A. AUDIT_FILE_DEST
B. BACKGROUND_DUMP_DEST
C. CORE_DUMP_DEST
D. DB_CREATE_FILE_DEST
E. USER_DUMP_DEST

2. What will be written to a background trace file? (Choose the best answer.)
A. Critical errors
B. Informational messages about background process activity
C. User activity information
D. It will depend on whether the SQL_TRACE parameter has been set

3. If a “stateless,” or “nonthreshold,” alert condition is raised, where will it be
displayed? (Choose the best answer.)

A. In the alert log

B. In a background trace file, generated by the process that detected it

C. In a user trace file, generated by the session that detected it

D. In the DBA_OUTSTANDING_ALERTS view

E. In the DBA_ALERT_HISTORY view

4. To enable collection of metrics for all the server alerts, what should the

STATISTICS_LEVEL parameter be set to? (Choose the best answer.)

A. BASIC

B. TYPICAL

C. ALL

D. Either TYPICAL or ALL

E. Any of BASIC, TYPICAL, or ALL

5. When the server alert system raises an alert, what will it do? (Choose all

that apply.)

A. Write it out to the alert log.

B. E-mail the alert to registered alert recipients.

C. Record the alert in the Automatic Workload Repository.

D. Invoke the appropriate procedure in DBMS_SERVER_ALERT.

6. Which of the following commands will be written to the alert log? (Choose

four answers.)

A. ALTER SESSION SET SQL_TRACE=TRUE;

B. ALTER SYSTEM SET SHARED_POOL_SIZE=400M;

C. ALTER TABLESPACE USERS BEGIN BACKUP;

D. ALTER DATABASE DATAFILE 10 RESIZE 1000M;

E. DROP TABLESPACE EXAMPLE CASCADE;

F. DROP USER SCOTT CASCADE;

G. DROP TABLE HR.EMPLOYEES;

H. SET AUTOTRACE ON;

7. Your users are connecting to the database through the shared server

mechanism, and you want to trace one user’s session. How can this

be done? (Choose the best answer.)

A. Enable tracing for his user process.

B. Trace the dispatcher servicing his session.

C. You can trace his session if you can identify the session’s SID and SERIAL#.

D. You cannot trace individual sessions through shared server.

8. Which process is responsible for updating metrics? (Choose the best answer.)

A. MMON, the manageability monitor

B. SMON, the system monitor

C. PMON, the process monitor

D. The server processes

9. Under which of the following circumstances must you subscribe to the

ALERT_QUE queue? (Choose the best answer.)

A. To receive e-mail notifications of alerts

B. To view alerts in Database Control

C. To write your own alert handler

D. To clear alerts from the ALERT_QUE queue manually

10. A SHUTDOWN ABORT is issued. By default, where will this be recorded?

(Choose the best answer.)

A. As an alert in the ALERT_QUE queue

B. In the alert log

C. As a user trace file

D. As background trace file

Answers

1. B. The alert log is written to the BACKGROUND_DUMP_DEST.

2. A. Background trace files are generated when a background process

encounters a severe problem, critical to the running of the instance.

3. E. Stateless alerts are displayed in the DBA_ALERT_HISTORY view. They are

never in the DBA_OUTSTANDING_ALERTS view, because they are solved as

soon as they occur.

4. D. Either TYPICAL or ALL will enable collection of the metrics used by the

server alert system. BASIC will disable the server alert system.

5. B and C. Server alerts are recorded in the AWR and mailed to anyone

registered to receive them.

6. B, C, D, and E. All ALTER SYSTEM and ALTER DATABASE commands are

written out to the alert log, so B and D are correct. Tablespace operations are

also written out, so C and E are correct too.

7. C. A shared server session is still a session, so you enable tracing for the SID

and SERIAL# that uniquely identify the session.

8. A. It is the manageability monitor that updates the AWR with metrics.

9. C. Subscribing to the queue is only needed if you want to write your own

routines to capture alert.

10. B. All SHUTDOWN commands are recorded in the alert log, and nowhere else.

Note:- 
Alert log contains following information :
All start up and shutdown commands ,  alter database and alter system , tablespace operation such as drop and create , putting tablespace in hot backup mode ,  log switches and archives .
Also contains warning and errors like :

Checkpoint incomplete, Unable to open file , Corrupt block , Problems with archiving,Deadlocks etc . 

Server-Generated Alerts

The server-generated alert system is a completely configurable mechanism that monitors 

the database, the instance, and user’s sessions and issues warnings when certain limits 

are reached and when certain events occur.

The background process that does this is the manageability monitor (or MMON)
process, assisted by the manageability monitor light (or MMNL) process.In earlier releases of the database the conversion of statistics to useful metrics
usually had to be done by the DBA; now Oracle does it for you, storing the results in
the Automatic Workload Repository, or AWR, in the SYSAUX tablespace.

Alert Types

Alerts are classed as threshold and nonthreshold. For example Tablespace usage is a threshold alert. Nonthreshold alerts inform you of events that occurred unpredictably. Such an alert is the “ORA-1555: snapshot too old”, error that is signaled when a query fails because the undo data it needs has been overwritten.

Threshold  alerts are also referred to as “stateful” alerts because they persist for some time until cleared . the query—and the alert becomes a historical record. Nonthreshold

alerts are also referred to as “stateless” alerts, because they do not persist: they occur,

and are gone.

The metrics for which alerts can be configured are listed in the view V$ALERT_

TYPES; there are more than a hundred of them. Many of the more useful metrics are

“x per second”–type metrics; these all have names suffixed “_ps”. To list them,

SQL> select internal_metric_name from v$alert_types where

internal_metric_name like '%_ps';

INTERNAL_METRIC_NAME

-----------------------------transactions_ps

physreads_ps

physwrites_ps

physreadsdir_ps

physwritesdir_ps

physreadslob_ps

physwriteslob_ps

redosize_ps

logons_ps

<output truncated....>

Viewing Alert Information

Alert information can be viewed by querying two data dictionary views. DBA_

OUTSTANDING_ALERTS will list all stateful alerts that have been raised and not yet

dealt with. Once cleared, the alerts are removed from this view and entered into DBA_

ALERT_HISTORY. Stateless alerts go directly to DBA_ALERT_HISTORY. This query,

SQL> select reason,object_type type,object_name name from

dba_outstanding_alerts;

We can edit thresold  by the DBMS_SERVER_ALERT package, which includes a procedure SET_THRESHOLD that can be used to set the criteria for issuing warning and critical alerts.

Value based and Fine grained auditing in oracle

A. value base auditing
The standard database auditing just described can catch the fact that a command was executed against a table, but not necessarily the rows that were affected. For example, issuing AUDIT INSERT ON HR.EMPLOYEES will cause an audit record to be generated whenever a row is inserted into the named table, but the record will not include the actual values of the row that was inserted. On occasion, you may want to capture these. This can be done by using  Value-based auditing. It used database triggers for its functioning.

When a user inserts, updates, or deletes data from a table with the appropriate trigger attached, the trigger works in the background to copy audit information toa table that is designed to contain the audit information. Value-based auditing tends to degrade performance more than standard database auditing because the audit trigger code must be executed each time the insert, update, or delete operation occurs.
--creation of trigger_table
SQL> create table system.audit_employees (osuser long, changedt date, ipaddr varchar2(90), newsalary varchar2(500));
Table created.
SQL>

--creation of trigger
SQL> CREATE OR REPLACE TRIGGER system.hrsalary_audit
  2  AFTER UPDATE OF salary
  3  ON hr.employees
  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7  IF :old.salary != :new.salary THEN
  8  INSERT INTO system.audit_employees
  9  VALUES (sys_context('userenv','os_user'), sysdate,
 10  sys_context('userenv','ip_address'),
 11  :new.employee_id ||
 12  ' salary changed from '||:old.salary||
 13  ' to '||:new.salary);
 14  END IF;
 15  END;
 16  /
Trigger created.
SQL>

--now if anyone change the salary in the hr.employees table the entry will be made in the system's audit_employees table.

SQL> update hr.employees set salary=4500 where employee_id=124;
1 row updated.

--entries done in the audit table.

SQL> select * from audit_employees;
OSUSER                         CHANGEDT  IPADDR       NEWSALARY
------------------------------ --------- ------------ -------------------------------------
KSNDMCPC2012004\Admin          16-FEB-13              124 salary changed from 5800 to 4500

Fine grained auditing
Database auditing can capture all accesses to a table, whether SELECT or DML
operations. But it cannot distinguish between rows, even though it might well be that
only some rows contain sensitive information.

Fine-grained auditing, or FGA, can be configured to generate audit records only when
certain rows are accessed, or when certain columns of certain rows are accessed. It can
also run a block of PL/SQL (through audit handler)  code when the audit condition is breached. FGA is configured with the package DBMS_FGA. To create an FGA audit policy,
use the ADD_POLICY procedure, which takes these arguments:

OBJECT_SCHEMA:- The name of the user who owns the object to be audited. This
defaults to the user who is creating the policy.
OBJECT_NAME:- The name of the table to be audited.
POLICY_NAME:- Every FGA policy created must be given a unique name.
AUDIT_CONDITION:- An expression to determine which rows will generate an audit
record. If left NULL, access to any row is audited.
AUDIT_COLUMN:- A list of columns to be audited. If left NULL, then access to any
column is audited.
HANDLER_SCHEMA:- The username that owns the procedure to run when the audit
condition is met. Default is the user who is creating the policy.
HANDLER_MODULE:- A PL/SQL procedure to execute when the audit condition is met.
ENABLE:- By default, this is TRUE: the policy will be active and can be
disabled with the DISABLE_POLICY procedure. If FALSE, then the ENABLE_POLICY procedure must be used to activate the policy.
STATEMENT_TYPES:- One or more of SELECT, INSERT, UPDATE, or DELETE to define
which statement types should be audited. Default is SELECT only.
AUDIT_TRAIL:- Controls whether to write out the actual SQL statement and its
bind variables to the FGA audit trail. The default is to do so.
AUDIT_COLUMN_OPTS:- Determines whether to audit if a statement addresses any or all
of the columns listed in the AUDIT_COLUMNS argument. Options
are DBMS_FGA.ANY_COLUMNS, the default, or DBMS_FGA_ALL_COLUMNSThe other DBMS_FGA procedures are to enable, disable, or drop FGA policies.
To see the results of fine-grained auditing, query the DBA_FGA_AUDIT_TRAIL view.

For example for FGA auditing click on following link

http://vishwanath-dbahelp.blogspot.in/2011/09/setting-fga-at-schema-level.html

Auditing in Oracle needs to know - part3

Next is privilege level auditing,

B. Auditing on privilege Level
All system privileges can be audited. The different privileges can be selected from system_privilege_map. Statement and privilege auditing are seperated in the oracle documentation and data dictionary views . However they use identical syntax and considering them as identical will simplify things greater. For example ,

SQL> enabe audit drop any table;

the reflect will populate both in DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS .
First note that the AUDIT syntax does not make a distinction between statement
and privilege options. Is “CREATE SESSION” a statement or a privilege option?
What about “DROP ANY TABLE?” According to the Oracle views, each appears
to be both, but only one audit trail record results from each audited action.

Example: auditable system privileges

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from system_privilege_map;
PRIVILEGE NAME
---------- ----------------------------------------
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE

..... not entire result is shown ......

-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP

We are giving alter system privileges to test user and enabling audit on it,

SQL> grant alter system to test;

Grant succeeded.

SQL> audit alter system by access;

Audit succeeded.

SQL> select sid, serial# from v$session where username='TEST';

SID SERIAL#
---------- ----------
149 1568

SQL> conn test
Enter password:
Connected.
SQL> alter system kill session '149,1568' immediate;

System altered.

SQL> conn /as sysdba
Connected.
SQL> select username, action_name from dba_audit_trail;

USERNAME                      ACTION_NAME
------------------------------ ----------------------------
TEST                               ALTER SYSTEM
TEST                                LOGOFF BY CLEANUP
TEST                                 LOGOFF

SQL>

C. Auditing on object Level

Objects that can be audited are : tables, views, sequences, packages, stored
procedures/functions. Note that because some objects may be dependant of other
objects (example function -> view -> table ) as a result several audit records
might be inserted when these objects are audited.

Object auditing options are set for all users of the database and cannot be set
for individual users.What options can be set? This can be seen from all_def_audit_opts.

Example object auditing options

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
--- --- --- --- --- --- --- --- --- --- --- --- ---
-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

These correspond to the following object options respectively:

alter, audit, comment, delete, grant, index, insert, lock, rename, select, update, reference and execute .

All except reference and execute can be applied to tables, otherwise you will get the error ora-1982 "invalid auditing option for tables".

SQL> audit select on scott.emp by session;

Audit succeeded.

Check: Which objects are audited

SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner='SCOTT' and OBJECT_NAME='EMP';

OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
------- ------- --------- --- --- --- --- --- --- --- --- --- --- --- --- ---
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

Generate some audit information:

SQL> connect scott/scott
SQL> select * from emp;
=> all scott.emp's rows are shown
SQL> connect hr/hr
SQL> select * from scott.emp;
=> ERROR at line 1:
ORA-00942: table or view does not exist
SQL> connect system/system
SQL> select * from scott.emp;
=> all scott.emp's rows are shown

Results of auditing:

SQL> connect system/system
SQL> select username, priv_used, ses_actions from dba_audit_object where obj_name='EMP' and owner='SCOTT';
USERNAME    PRIV_USED                  SES_ACTIONS
----------       -------------------           -------------------
SCOTT                                            ---------S------
MAHI            SELECT ANY TABLE      ---------S------
HR                                                  ---------F------
SYSTEM         SELECT ANY TABLE      ---------S------

Auditing in Oracle needs to know - part2

Apart from SYSDBA auditing, Oracle provides three auditing techniques:

1. standart auditing

Auditing based on statement, Privileges and object level. 

In standard auditing we won't get any information of the event like what actually happened on tables ,for example what got inserted or deleted etc . Value-based audit will give this feature .

2. Value-based auditing
Value-based auditing uses database triggers. Whenever a row is inserted, updated,
or deleted, a block of PL/SQL code will run that can (among other things)
record complete details of the event. 

3.Fine-grained auditing
Fine-grained auditing allows tracking access to tables according to which rows
(or which columns of the rows) were accessed. It is much more precise than
either database auditing or value-based auditing, and it can limit the number
of audit records generated to only those of interest.

The standard auditing having 3 levels of auditing:

Statement: Audit all action at any type of objects.

Privilege: audit action on system level privileges

Object_level: Specific audit action lie select, update, insert or delete.

There is no clear cut demarcation between these three auditing , all are some how related to each other.

For all the 3 level of auditing you can choose to audit by access (audit every time you access) or by session (audit only once per access during the session), you can also audit on if the access was successful (whenever successful) or not (whenever not successful)

The difference between BY SESSION and BY ACCESS is that when you specify BY SESSION Oracle will try to merge multiple audit entries into one record when the session and the action audited match.

That is ,

audit by access- audit every time you access- will give accurate picture about what is going on .

audit by session - audit only once per access during the session - Here after session got created and if you are going to insert 100 times on a table ,then it will logged only for the first attempt .

 CREATE SESSION is a DDL statement Oracle audits this statement by access.

So for statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

Oracle database can write to an operating system audit file but cannot read it to detect whether an entry has already been written for a particular operation. Therefore, if you are using an operating system file for the audit trail (that is, the AUDIT_TRAIL initialization parameter is set to OS), then the database may write multiple records to the audit trail file even if you specify BY SESSION.

A. statemente level auditing

Statement auditing is the selective auditing of related groups of statements for a type of database structure or type of schema object for ddl (data definition language) or dml (data manipulation language) statements. For example when 'audit table' is audited create , drop and truncate table statements are audited. Statement level auditing is broad .

The statements that can be audited can be seen from stmt_audit_option_map.

SQL> select * from stmt_audit_option_map;

SQL> select * from stmt_audit_option_map where name like '%TABLE%'; etc,

How to use;

SQL> sho user;

USER is "SYS"

SQL> audit table by scott, system; -- ddl level

Audit succeeded.

SQL> conn scott/scott

Connected.

SQL> create table aud_test as select * from EMP;

Table created.

SQL> drop table aud_test;

Table dropped.

SQL>

SQL> alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL> conn sys as sysdba
Enter password:
Connected.

SQL>  select USERNAME,obj_name,ACTION_NAME,TIMESTAMP from DBA_AUDIT_TRAIL;

USERNAME     OBJ_NAME     ACTION_NAME         TIMESTAMP

------------ ------------ ------------------- --------------------

SCOTT        AUD_TEST     CREATE TABLE        15-FEB-2013 14:02:35

SCOTT        AUD_TEST     DROP TABLE          15-FEB-2013 14:22:32

Statement level auditing at DML level :-

Eg:-

SQL> audit insert table by scott whenever successful;

 Note:- In statement level auditing we are not specifying any object name to audit , that is statement level auditing broad in its audit action .

we can audit the session level event by using following statement audit ,

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> audit session by access;

Audit succeeded.

SQL> conn scott/scott
Connected.
SQL> conn hr/hr
Connected.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL>  select username,timestamp,logoff_time,ACTION_NAME from dba_Audit_session;

USERNAME     TIMESTAMP            LOGOFF_TIME          ACTION_NAME

------------ -------------------- -------------------- -------------------

SYSMAN       15-FEB-2013 16:25:18 15-FEB-2013 16:31:29 LOGOFF

SCOTT        15-FEB-2013 16:25:31 15-FEB-2013 16:25:34 LOGOFF

HR           15-FEB-2013 16:25:35 15-FEB-2013 16:26:36 LOGOFF

SQL>

Audit that ebabled on statement level can be viewed by ,

 SQL> select * from DBA_STMT_AUDIT_OPTS;

Note the following views gives you which audits are enabled:

dba_obj_audit_opts
dba_priv_audit_opts
dba_stmt_audit_opts

Disabling Audit:-

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement, privilege and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

noaudit table by scott;

noaudit insert table by scott;

noaudit session by access;