Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 9, 2006

Why Oracle's Automatic Storage Management? - Page 2

By DatabaseJournal.com Staff

by Sreeram Surapaneni

How it works...

An ASM Instance

Before a database can be opened, the physical files must be located using the information provided by the control file. However, if the control file itself is part of a disk group, you might ask, how does the database instance locate the appropriate file?

ASM needs to have its own separate instance available before the actual database instance is started. As part of the ASM instance startup procedure, the various disk groups and their files are identified. The ASM instance mounts the disks, and then creates an extent map, which is passed to the database instance. However, the database instance itself is responsible for any actual input/output operations. The ASM instance is only involved during the creation or deletion of files and when disk configurations change (such as dropping or adding a disk).

When these types of changes occur, the ASM instance automatically rebalances the disks and provides the necessary information to refresh the extent map in the SGA of the database instance. Of course, this process requires that the ASM instance run concurrently with the database instance, and only shut down after the database instance is closed.

The impact of the ASM instance on performance of the database instance is minimal. The former does not process transactions affecting the individual database objects; therefore, the average SGA allocation needed by the instance is no more than 64MB. Unless the server's memory is already at the maximum recommended operating system/DBMS allocation, 64MB should have no impact on the memory available for the database instance.

Disk Groups

Depending on the number of available disk drives, controllers, and redundancy preferences, the DBA can create several disk groups. A disk group is a collection of disks managed as one logical group. By supporting mirroring and striping, it is possible to store redundant copies of the database files in the same disk group, provided sufficient space is available across the disks. However, in terms of performance and recoverability, the disk groups should be subdivided so mirrored copies of the database files reside in defined sets of disks.

This concept can be implemented through the use of failure groups. A failure group identifies a set of disks that needs to be fault-tolerant. When failure groups are assigned either by the DBA or automatically by the ASM instance, redundant copies of an extent are stored in each failure group on a 1MB-by-1MB basis. When high redundancy is specified (i.e., triple mirroring), then the ASM instance can tolerate failures in a maximum of two failure groups.

Creatinga Disk Group

Because the ASM will manage the disk groups and the database files internally, the DBA simply needs to identify the appropriate disks, desired failure groups, and the level of redundancy for the disk group. For example, if the DBA has eight drives available, and wants at least two copies of each database file, the drives can be divided into two failure groups; preferably with each group having its own controller. A sample SQL statement to create this disk group is:

CREATE DISKGROUP dg1
FAILGROUP fgroup1 DISK
'devices/disk1',
'devices/disk2',
'devices/disk3',
'devices/disk4'
FAILGROUP fgroup2 DISK
'devices/disk5',
'devices/disk6',
'devices/disk7',
'devices/disk8';

In this example, the first four disks have a different controller from the second set of disks. Notice that the statement only requires the DBA to identify which disks are to be included in the disk group and how they are to be subdivided among the failure groups. The DBA is not required to specify which database file is written to which disk; ASM takes care of that. Because ASM internally balances the workload across all the disks in the disk group, disk contention is reduced and performance increases.

An added benefit of ASM is that it automatically and dynamically rebalances the files if the DBA needs to change a size of a disk group (adding or dropping a disk). Moreover, this task can be accomplished without shutting down the database.

File Names

With ASM, the DBA has the option of simply specifying a disk group when creating a new data file. For example, to create a new tablespace with 300MB of allocated space, the following command is issued:

CREATE TABLESPACE tb1 DATAFILE '+dg1' SIZE 300MB;

With this command, ASM manages the naming and placement/distribution of the data file. The DBA only has to assign the disk group that would store the data file. This approach eliminates the time DBAs spend tuning I/O operations and correcting fragmentation problems.

If ASM, along with OMF, is responsible for file management including file names, how does the DBA reference a specific file? For example, suppose the DBA needs to perform an incomplete recovery?

Currently, the DBA can identify the file name for most database files through views. To identify a particular data file, the DBA can query the V$DATAFILE and V$TABLESPACE views and display the name and location of the data files associated with each tablespace. This process works exactly the same way with disk groups.

The file-naming structure that ASM requires to identify a copy of a data file within a failure group is structured and, when there are thousands of files to managed, difficult to un-map. However, ASM supports the use of aliases: The DBA has the option of assigning aliases to the control file, data files, and so on, which offers the flexibility of referencing individual data files when necessary. For example, in the case of an incomplete recovery, the ASM instance can also interact with RMAN, and an alias can be used to specify the appropriate data file to delete, allowing RMAN to perform the necessary recovery procedures.

Supported File Types

As with Oracle9i Database and previous database versions, the location of user trace files, the alert.log file, and so on should be specified using the USER_DUMP_DEST and BACKGROUND_DUMP_DEST parameters, respectively.

Conclusion and Implications for ASM

Each generation of Oracle software has included features designed to increase the performance of the database system while reducing administrative overhead. The use of an internal automatic storage management system lessens the administrative burden associated with a large database containing thousands of operating system files.

The greatest benefits gained by the DBA include the elimination of manual I/O tuning, ability to dynamically change storage resources, and automatic rebalancing. When there are thousands of files to manage, DBAs can spend 40% of their time performing the same operations that ASM will handle automatically. The end result is that DBAs will have more time to address other aspects of their jobs

Sreeram Surapaneni is an Oracle Certified DBA (7.3,8,8i,9i) working currently as DBA at Research In Motion Ltd, Canada and can be reached at Sreeram Surapaneni.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date