Nothing but my learning and experience with Oracle..!

Thursday, March 28, 2013

Automatic Storage Management (ASM) lessons 1

Automatic Storage Management (ASM) provides a centralized way to manage Oracle Database disk storage.ASM is somewhat like a logical volume manager, allowing you to reduce the management of Oracle ﬁles into ASM disk groups. It also provides redundancy conﬁgurations, rebalancing operations, and, when installed on top of clusterware, the ability to share database-related ﬁles.

Here are some features of ASM:

Automatic software data striping (RAID-0)
Load balancing across physical disks
Software RAID-1 data redundancy with double or triple mirrors
Elimination of fragmentation
Simplification of file management via support for Oracle Managed Files (OMF)
Ease of maintenance

Creating the ASM Instance

1. Creating the ASM Instance with the DBCA:-

Before going with dbca we need to install CSS service . ASM cannot be used until Oracle CSS service is started.

C:\Windows\system32>localconfig add

Step 1: creating new OCR repository

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'admin', privgrp ''..

Operation successful.

Step 2: creating new CSS service

successfully created local CSS service

successfully added CSS to home

C:\Windows\system32>

To create the ASM instance with the DBCA, do the following:

1. Start the Oracle DBCA.

2. The DBCA presents a list of options for you to choose from. Select Conﬁgure Automatic

Storage Management and click Next.

3. The DBCA then prompts you for the SYS password for the new ASM instance to be

created. Enter the password for the SYS account.

4. Oracle then creates the ASM instance. A new window appears giving you the option

to create new disk groups. You can choose to create disk groups (we will cover that

shortly) or you can click Finish to complete the ASM instillation.

5. The name of the resulting instance will be +ASM. You can log into the ASM instance

from SQL*Plus, as shown in this example:

C:\Windows\system32>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 26 12:06:14 2013

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME

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

+asm

SQL>

In 11g we will use sys as sysasm for connecting to asm instance.

Creating the ASM Instance Manually

Manual creation of an ASM instance is fairly straightforward. If you have ever manually

created a database, then manually creating an ASM instance should be easy for you. To

manually create an ASM instance, you would follow these steps:

1. Create directories for the ASM instance.

2. Create the instance parameter file.

3. Perform any Microsoft Windows–specific configuration.

4. Start the ASM instance.

5. Create the ASM server parameter file (spfile).

Let’s look at each of these steps in a bit more detail

The following steps can be used to create a fully functional ASM instance named +ASM. The node I am using in this example also has a regular 10g database running named TESTDB. These steps should all be carried out by the oracle UNIX user account:
Create Admin DirectoriesWe start by creating the admin directories from the ORACLE_BASE. The admin directories for the existing database on this node, (TESTDB), is located at $ORACLE_BASE/admin/TESTDB. The new +ASM admin directories will be created alongside the TESTDB database:
UNIX

mkdir -p $ORACLE_BASE/admin/+ASM/bdump mkdir -p $ORACLE_BASE/admin/+ASM/cdump mkdir -p $ORACLE_BASE/admin/+ASM/hdump mkdir -p $ORACLE_BASE/admin/+ASM/pfile mkdir -p $ORACLE_BASE/admin/+ASM/udump

Microsoft Windows

mkdir %ORACLE_BASE%\admin\+ASM\bdump mkdir %ORACLE_BASE%\admin\+ASM\cdump mkdir %ORACLE_BASE%\admin\+ASM\hdump mkdir %ORACLE_BASE%\admin\+ASM\pfile mkdir %ORACLE_BASE%\admin\+ASM\udump

Create Instance Parameter FileIn this step, we will manually create an instance parameter file for the ASM instance. This is actually an easy task as most of the parameters that are used for a normal instance are not used for an ASM instance. Note that you should be fine by accepting the default size for the database buffer cache, shared pool, and many of the other SGA memory sructures. The only exception is the large pool. I like to manually set this value to at least 12MB. In most cases, the SGA memory footprint is less then 100MB. Let's start by creating the file init.ora and placing that file in$ORACLE_BASE/admin/+ASM/pfile. The initial parameters to use for the file are:
UNIX

$ORACLE_BASE/admin/+ASM/pfile/init.ora

########################################### # Automatic Storage Management ########################################### # _asm_allow_only_raw_disks=false # asm_diskgroups='TESTDB_DATA1' # 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/* # asm_diskstring='' ########################################### # Diagnostics and Statistics ########################################### background_dump_dest=/u01/app/oracle/admin/+ASM/bdump core_dump_dest=/u01/app/oracle/admin/+ASM/cdump user_dump_dest=/u01/app/oracle/admin/+ASM/udump ########################################### # Miscellaneous ########################################### instance_type=asm compatible=10.1.0.4.0 ########################################### # Pools ########################################### large_pool_size=12M ########################################### # Security and Auditing ########################################### remote_login_passwordfile=exclusive

Microsoft Windows

%ORACLE_BASE%\admin\+ASM\pfile\init.ora

########################################### # Automatic Storage Management ########################################### # _asm_allow_only_raw_disks=false # asm_diskgroups='TESTDB_DATA1' # 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/* # asm_diskstring='' ########################################### # Diagnostics and Statistics ########################################### background_dump_dest=C:\oracle\product\10.1.0\admin\+ASM\bdump core_dump_dest=C:\oracle\product\10.1.0\admin\+ASM\cdump user_dump_dest=C:\oracle\product\10.1.0\admin\+ASM\udump ########################################### # Miscellaneous ########################################### instance_type=asm compatible=10.1.0.4.0 ########################################### # Pools ########################################### large_pool_size=12M ########################################### # Security and Auditing ########################################### remote_login_passwordfile=exclusive

After creating the $ORACLE_BASE/admin/+ASM/pfile/init.ora file, UNIX users should create the following symbolic link:
$ ln -s $ORACLE_BASE/admin/+ASM/pfile/init.ora $ORACLE_HOME/dbs/init+ASM.ora

Identify RAW Devices

Before starting the ASM instance, we should identify the RAW device(s) (UNIX) or logical drives (Windows) that will be used as ASM disks. For the purpose of this article, I have four RAW devices setup on Linux:
# ls -l /dev/raw/raw[1234]
crw-rw----  1 oracle dba 162, 1 Jun  2 22:04 /dev/raw/raw1
crw-rw----  1 oracle dba 162, 2 Jun  2 22:04 /dev/raw/raw2
crw-rw----  1 oracle dba 162, 3 Jun  2 22:04 /dev/raw/raw3
crw-rw----  1 oracle dba 162, 4 Jun  2 22:04 /dev/raw/raw4
Attention Linux Users!This article does not use Oracle's ASMLib I/O libraries. If you plan on using Oracle's ASMLib, you will need to install and configure ASMLib, as well as mark all disks using:

/etc/init.d/oracleasm createdisk <ASM_VOLUME_NAME> <LINUX_DEV_DEVICE>
. For more information on using Oracle ASMLib, see "Installing Oracle10g Release 1 (10.1.0) on Linux - (RHEL 4)".

Attention Windows Users!A task that must to be performed for Microsoft Windows users is to tag the logical drives that you will want to use for ASM storage. This is done using a new utility that is included with Oracle Database 10g called asmtool. This tool can be run either before or after creating the ASM instance. asmtool is responsible for initializing the drive headers and marks drives for use by ASM. This really assists in reducing the risk of overwriting a usable drive that is being used for normal operating system files.

Starting the ASM Instance

Once the instance parameter file is in place, it is time to start the ASM instance. It is important to note that an ASM instance never mounts an actual database. The ASM instance is responsible for mounting and managing disk groups.

Attention Windows Users!If you are running in Microsoft Windows, you will need to manually create a new Windows service to run the new instance. This is done using the ORADIM utility which allows you to create both the instance and the service in one command.

UNIX
# su - oracle
$ ORACLE_SID=+ASM; export ORACLE_SID
$ sqlplus "/ as sysdba"

SQL> startup
ASM instance started
Total System Global Area   75497472 bytes
Fixed Size                   777852 bytes
Variable Size              74719620 bytes
Database Buffers                  0 bytes
Redo Buffers                      0 bytes
ORA-15110: no diskgroups mounted

SQL> create spfile from pfile='/u01/app/oracle/admin/+ASM/pfile/init.ora';

SQL> shutdown
ASM instance shutdown

SQL> startup
ASM instance started
Microsoft Windows
C:\> oradim -new -asmsid +ASM -syspwd change_on_install 
    -pfile C:\oracle\product\10.1.0\admin\+ASM\pfile\init.ora -spfile 
    -startmode manual -shutmode immediate
Instance created.
C:\> oradim -edit -asmsid +ASM -startmode a
C:\> set oracle_sid=+ASM
C:\> sqlplus "/ as sysdba"

SQL> startup pfile='C:\oracle\product\10.1.0\admin\+ASM\pfile\init.ora';
ASM instance started
Total System Global Area    125829120 bytes
Fixed Size                    769268 bytes
Variable Size               125059852 bytes
Database Buffers                   0 bytes
Redo Buffers                       0 bytes
ORA-15110: no diskgroups mounted 

SQL> create spfile from pfile='C:\oracle\product\10.1.0\admin\+ASM\pfile\init.ora';
File created.
SQL> shutdown
ASM instance shutdown
SQL> startup
ASM instance started
You will notice when starting the ASM instance, we received the error:
ORA-15110: no diskgroups mounted
This error can be safely ignored.Notice also that we created a server parameter file (SPFILE) for the ASM instance. This allows Oracle to automatically record new disk group names in the asm_diskgroups instance parameter, so that those disk groups can be automatically mounted whenever the ASM instance is started.
Now that the ASM instance is started, all other Oracle database instances running on the same node will be able to find it.

Note the following Oracle parameters that are speciﬁc to ASM instances,

INSTANCE_TYPE : Used only with an ASM instance, this parameter indicated to Oracle

that this is an ASM instance. The default value is RDBMS, which indicates the instance is

an Oracle database instance. This parameter is not dynamic and is the only mandatory

parameter in an ASM instance.

ASM_DISKSTRING : This parameter indicates where Oracle should search for disk devices

to be used by ASM. We will discuss this parameter in more detail later in this section.

This parameter can be dynamically changed.

ASM_DISKGROUPS : This parameter lists ASM disk groups that ASM should mount when

it is started. You can also use the alter diskgroup all mount command to cause

these disk groups to be mounted. This parameter can be dynamically changed.

ASM_POWER_LIMIT : This parameter controls the rate at which ASM can rebalance disks by

increasing or decreasing the degree of parallelism used. Lower values will slow rebalancing

but will also result in less of an IO impact by those operations. Higher values may

speed up rebalancing by parallelizing the rebalance operation. The default is 1, and this

is typically sufficient. This parameter can be set dynamically.

Automatic Storage Management (ASM) lessons 2

ASM Processes
After you start your ASM instance, you will ﬁnd 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 ﬁrst step to setting up an ASM disk group. In this section, we

will cover conﬁguring 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-
speciﬁc 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 deﬁning the ASM_DISKSTRING parameter. Here are
some examples of setting the ASM_DISKSTRING parameter. In this ﬁrst 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 conﬁguring 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 deﬁne the redundancy setting for a disk group, you are deﬁning things such

as what kind of striping occurs and whether the data will be mirrored. These attributes are deﬁned 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 dataﬁles on it, the dataﬁle template would be used by default. In this case, a

dataﬁle 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 ﬁles already in that disk group. As a result, you will need to use RMAN

to back up and then restore ﬁles 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 deﬁne speciﬁc 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 speciﬁc 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 signiﬁcantly 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 ofﬂine 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 sufﬁcient 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 ofﬂine 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

ﬁles 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 ﬁles 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 speciﬁc 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 ﬁrst rebalance the data on the disks to be dropped,

assuming enough space is available. If insufﬁcient 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

Tuesday, March 12, 2013

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

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.

Saturday, February 16, 2013

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