Oracle Automatic Storage Management

Oracle Automatic Storage Management By Sean Hull There's a lot of talk about ASM, but what the heck is it really? ASM is effectively an abstraction layer, allowing your Oracle database to work with abstract space called diskgroups, instead of datafiles directly. This provides lots of benefits, but also requires learning some new concepts, commands, utilities, and administration tasks. So take a look at what it solves, and what it takes to manage and weigh the pros and cons before diving in with your production systems.

Why Was It Created? The best way to answer this question is to go right to the source, Bill Bridge the original architect of Automatic Storage Management. In the Oracle Press title, Oracle ASM, Bill provides a forward where he discusses the problems with using vendor specific OS filesystems to manage Oracle datafile placement: 1. for arch logs & backups, OS vendors don't provide shared disk filesystem 2. logical volume managers hide the location of files making it difficult to manage disk I/O and provide good stats 3. existing lvm's don't work well with over 100 disks 4. OS's and Oracle don't handle databases well that have 1000's of datafiles 5. naming becomes difficult with large number of datafiles 6. features, and filesystem limitations vary across different OSs 7. users at the OS level can touch Oracle files with standard utilities, without Oracle knowing So, he set out to solve these problems by building Oracle's own filesystem. His intention was to provide these features: 1. tightly integrated with Oracle, and work with clusters (then parallel server) 2. new storage automatically used, managed as disk unit or disk group 3. thousands of disks supported 4. files won't have names, and will be hidden from the OS

Who Needs It? A quick look at the problems and solutions above should help you determine who ASM is targeted at. For starters, it was built to handle the very large databases now coming online. So if that includes your shop, you're probably already looking at it, or starting to implement ASM. If you have smaller databases, with fewer datafiles, you may still want to consider it given a few considerations.

1. You'll have some new technology to get familiar with, and should start by setting it up in your dev environment, and do some testing for a few months. 2. If you want to squeeze out more performance from your existing disk subsystem, and get better statistics for forcasting disk I/O. 3. If you're using RAC, ASM is something to consider.

Getting Started ASM is managed by an instance, much like an Oracle database. However the initialization parameters are much more limited and the startup process is simpler. a. Set your ORACLE_SID to +ASM1 b. Edit init.ora # as opposed to RDBMS for a normal Oracle instance INSTANCE_TYPE=ASM # these names will be used in place of datafile names when you create tablespaces ASM_DISKGROUPS=SEAN, AARON processes=100 # this parameter is platform specific and is the path to the raw disk device ASM_DISKSTRING='/dev/cciss/c0d0p1' # on 11g you should use diagnostic_dest instead of these background_dump_dest=/opt/oracle/admin/+ASM/bdump' core_dump_dest=/opt/oracle/admin/+ASM/cdump' user_dump_dest=/opt/oracle/admin/+ASM/udump' c. start the ASM instance $ sqlplus / as sysdba SQL> startup d. create diskgroups SQL> create diskgroup SEAN disk '/dev/cciss/c0d0p1'; e. example tablespace creation As you might guess, creating a tablespace will change slightly. The default method looks like this: SQL> create tablespace sean_space datafile '+SEAN' size 1GB; However, consider this great feature. If in your database init.ora file you set the parameter db_create_file_dest=+SEAN then you can do this:

SQL> create tablespace sean_space; Then let Oracle do the rest. In both cases, you'll find that the paths of files listed in v$datafile will be relative to the abstract +SEAN diskgroup, not a real OS datafile. f.

And Way Beyond

Of course simplifying filenames and tablespace creation is really only the tip of the iceberg for what ASM can do for you. It can also provide a level of redundancy as well. In database speak, external redundancy is basically when you have redundancy at the hardware level (RAID) or other method outside of what Oracle can see. In other words if the asm_diskstring devices are themselves logical, hiding the physical disks behind some hardware layer of redundancy, then you have external redundancy. However, if you don't already have this redundancy, ASM can provide it. You can specify redundancy, failure groups, and a whole host of other options to protect against loss of one or more disks, controllers, or even whole SAN failures. ASM also provides evenly distributed I/O across a diskgroup. Since ASM has a better picture of what's happening under the hood, Oracle can automatically provide a better balance of I/O to disk for you.

Challenges to ASM Adoption ASM is certainly a powerful technology with a lot of potential. But with every technical solution, there come a whole host of other challenges. In the case of ASM, it potentially disrupts the usual balance of power between the Unix + Systems Administration groups, and the Database/DBA groups. Traditionally the former group manages disks, hardware, and the operating system level, leaving the DBAs to coordinate with them for new resources. This would change that balance somewhat, which could cause some resistance from that group. In the end, it should be the business need that pushes this adoption. Also be aware that ASM is still in the enterprise computing sense, relatively new. There are a number of vendors whose core business has been in the logical volume manager/filesystem space for years. Quite often, maturity matters a lot when it comes to software systems, and reliability.

Conclusion ASM is powerful stuff, providing solutions for the growing very large database systems being deployed currently. It may also provide solutions for smaller database installations, or those using clustering. As with any new technology, evaluate, test, and then test some more.

Oracle 10g Automatic Storage Management: Overview By Jim Czuprynski Synopsis. Oracle 10g's new Automatic Storage Management (ASM) features allow an Oracle DBA to take advantage of a robust, flexible, scalable file storage system that any Oracle database instance can access. This article - the first in this series -- provides a high-level overview of the ASM architecture, and should be helpful to any DBA contemplating the adoption of ASM for storing Oracle database files as part of a high-volume storage solution. With the possible exception of having to rebuild a database server from a "cold metal" starting point, I have to admit that the scenario that holds the most dread for me as an Oracle DBA is to run out of disk space for a production database's datafiles or tempfiles. Though, in most cases this situation can be rectified easily - as long as there is sufficient disk space available, of course! - it always seems that these challenges seem to occur either at the busiest time of my client's business day, or between 02:00 and 03:00 on an early Sunday morning when I am scheduled to be out of town enjoying a long weekend. And I have also encountered my share of unexpected (and unlikely) disk media failures. For example, I once watched five disks in a 42-disk mirrored storage array fail within a three-week period after three years of relatively solid performance. The pattern never repeated itself, but it did teach me the value of a good mirroring strategy! While a storage area network (SAN) and other logical volume manager (LVM) storage solutions like EMC are certainly options for guaranteed storage reliability, the costs of these solutions can be prohibitive for smaller IT organizations. The good news is that Oracle 10g supplies a new set of disk media storage features called Automatic Storage Management (ASM) that are aimed directly at insuring a storage solution for smaller enterprise databases. This series of articles will delve into ASM's rich features and provide examples of how to implement some simple ASM disk configurations for evaluation purposes so that your DBA team can make some decisions about whether ASM is worth pursuing for your organization. I will concentrate this article on a general review of ASM features as well as a brief explanation of the ASM architecture.

Automatic Storage Management Features Out-Of-The-Box Functionality. First off, ASM was designed specifically for the storage of Oracle database files. This means that it is ready "out of the box" for use against any Oracle database that it services. Though ASM appears to be the intended replacement for Oracle Cluster File System (OCFS) for the Real Applications Cluster (RAC) environment, that doesn't mean that your database needs to be deployed under RAC to take advantage of ASM's myriad features; ASM can be deployed on a single processor (SMP) server just as easily as it can be deployed on a multiple-node RAC cluster. Moreover, it is simple to configure using the graphic interface provided by the Database Configuration Assistant (DBCA), an existing Oracle tool that just about every Oracle DBA has used. Oracle 10g also provides the standard Enterprise Manager (EM) interface for managing an ASM instance once it has been created. Flexible Storage Configuration. Since ASM is in essence its own file management system, this means that I can add more physical storage, change storage configurations, or remove extraneous storage without having to shut

down my Oracle database to change out physical hardware. This is a big advantage over traditional hardware configurations, and is certainly featured in storage-area networks (SANs). Using ASM also means that I can turn over the majority of my space management tasks to the ASM instance and let it monitor the growth of my database's tablespaces. In addition, ASM maintains pre-existing Oracle database functionality, so regular Oracle databases can continue to operate as usual. New database files can be created as ASM files managed by the ASM instance, existing database files can be migrated to ASM as time and opportunity permit, and other database files can continue to exist as "traditional" database files without ASM file management. Fault Tolerance. I also expect any file management system to insure that once I write database information to disk, it will never be lost - unless, of course, I tell the database to delete that data. One way that DBAs usually guarantee against the inevitable failure of disk hardware is through mirroring drives via redundant arrays of inexpensive disks (aka RAID) and striping (writing portions of disk files to different disks so that if one disk is lost the information is recoverable from another disk in the set). This guards against the inevitable failure of disk hardware, since even with extremely high estimated mean time between failures (MTBF), a disk drive will almost certainly fail eventually. Most operating systems provide controls to set up and monitor disk storage to take advantage of these redundancy features. Like any good file management system, ASM provides fault tolerance by allowing me to write duplicate or triplicate copies of any database files' contents. And if my current server and disk storage system already support fault tolerance, so much the better! ASM can also take advantage of existing vendor-supplied fault tolerance mechanisms for increased guarantees to safe data storage. Since ASM handles the mirroring of data, there is no need to purchase a thirdparty Logical Volume Manager (LVM). Mirroring is applied on a file-by-file basis, rather than on a per-volume basis, so the same disk group may contain a combination of files protected by mirroring (or perhaps not even mirrored at all, if mirroring is not required for some files). ASM also supports Oracle Real Application Clusters (RAC), so there is no need for a separate Cluster LVM or a Cluster File System. I/O Load Balancing. ASM is responsible for providing equal distribution of I/O loads across all available disk storage resources, so there is a measurable performance improvement. For example, ASM will split a datafile into its component extents and then spread those extents evenly across all defined disks that ASM is managing; those extents are then tracked via an indexing technique. ASM also automatically manages data via selection of desired reliability and performance characteristics instead of manually manipulating data file storage characteristics, and therefore it tends to reduce (or even eliminate) manual tuning and retuning of I/O. A big advantage to this approach is that when ASM storage capacity changes, ASM doesn't need to re-stripe all data - it just moves enough data in proportion to the amount of added (or reduced!) storage, thus redistributing the datafile's extents evenly and keeping a balanced data load across all disks. Moreover, since ASM can accomplish the rebalancing act while the database is active, there is virtually no impact on database availability. I can also instruct ASM to increase the speed of a space rebalancing operation if I know that sufficient system

resources are available, or I can tell ASM to reduce the speed of the rebalancing operation to limit the impact on the ASM I/O subsystem. Productivity Increases. Here is one final, intangible benefit of ASM: an increase in my productivity as a DBA. Since I am freed up from physical and logical space management worries, I can now concentrate my limited and valuable time on more critical database tuning issues, like poorly-structured SQL statements and inefficient logical storage structures that tend to be the real culprits behind database performance issues.

The ASM Instance The ASM instance is at the heart of Automatic Storage Management. Rather than "reengineer the wheel," Oracle decided to adopt the basic architecture of a normal Oracle database instance. An ASM instance never actually opens a database; instead, it is responsible for storing and processing the metadata that is required to make available the files stored within the ASM storage system to non-ASM Oracle databases (henceforth referred to as database instances). In addition to the normal database background processes like CKPT, DBWR, LGWR, SMON, and PMON, an ASM instance uses at least two additional background processes to manage data storage operations. The Rebalancer process, RBAL, coordinates the rebalance activity for ASM disk groups, and the Actual ReBalance processes, ARBn, handle the actual rebalance of data extent movements. There are usually several ARB background processes (ARB0, ARB1, and so forth). I will talk more about disk rebalancing operations in the final article in this series. Starting Up and Shutting Down an ASM Instance. The ASM instance can be can be started in NOMOUNT mode, and ASM disk groups are mounted via the MOUNT command. Similarly, disk groups can be taken offline with the SHUTDOWN command. When an ASM instance is started and appropriate ASM disk groups are mounted, all ASM files contained on those disk groups are made accessible to any Oracle database. Any database instance can use the ASM disk groups as targets for creating new ASM-managed files. Managing an ASM Instance. Managing access to the ASM instance is relatively straightforward. The DBA can issue the ALTER SYSTEM ENABLE RESTRICTED SESSION; command to prevent database instances from accessing ASM disk groups while maintenance is being performed against the ASM instance or any ASM disk groups. Alternatively, issuing the ALTER SYSTEM DISABLE RESTRICTED SESSION; command re-enables access to the ASM instance. The only way to connect to an ASM instance is via OS authentication (i.e. SYSDBA or SYSOPER) because an ASM instance does not have a data dictionary per se. Connecting to an ASM via a remote connection implies, of course, that a password file must be used. Normally, the SYSDBA privilege is granted through the use of an operating system group (on UNIX or Linux, typically the dba group). Since by default members of that group have SYSDBA privilege on all instances on the node, including the ASM instance, any user that connects to the ASM instance with the SYSDBA privilege has administrative access to all ASM disk groups in the system. The SYSOPER privilege is also supported in ASM instances and limits the set of allowable SQL commands to the minimum required for basic operation of an

already-configured system. A user with SYSOPER permissions can start up or shut down an ASM instance, take disk groups online or offline, issue a manual rebalance request, and verify a disk group. However, more advanced command sets like those for creating new ASM disk groups, resizing ASM disks, adding new ASM disks to existing ASM disk groups, and dropping ASM disk groups are reserved to users with the SYSDBA privilege.

The ASM Storage Hierarchy ASM provides a flexible storage hierarchy for managing access to its disk files, starting with the smallest unit of storage, the Allocation Unit (AU). All ASM disk space is partitioned in AUs of 1 MB each, and blocks will never be split across any allocation unit. ASM Files. Next in the hierarchy are the actual ASM files themselves. With the exception of trace files and operating system files, ASM can store every type of Oracle database file, including control files, server parameter files (SPFILEs), datafiles, tempfiles, online redo logs, archived redo logs, flashback logs, DataPump dump sets. In addition, ASM can store all types of files managed by Recovery Manager (RMAN), including backup sets, archived redo log image copies, datafile image copies, and autobackups. ASM Disk Groups. The highest level of storage unit in an ASM instance is the ASM Disk Group. An ASM disk group can support multiple files that belong to multiple Oracle database instances. Each ASM disk group consists of one or more ASM Disks, and an ASM Disk belongs to one and only one ASM Disk Group. All ASM files in the ASM Disk Group will be spread across all the ASM disks in the ASM disk group; however, a single ASM File will never span more than one ASM disk group. ASM disk groups are accessible by either ASM instances or database instances, and database instances can access the contents of ASM files directly, communicating with an ASM instance only to get information about the layout of these files. An ASM instance supports the addition of new ASM disks, deletion of existing ASM disks and disk groups, and modifications to existing ASM disk groups (e.g. adding new ASM disks). ASM File Naming Conventions and File Name Aliasing. ASM uses a fourlayer file name to identify an ASM file. The ASM file name consists of the ASM disk group name, the database instance name, and the file type; the final file name component consists of three tokens, a file type token (e.g. CF for control file) and two integer values. The combination of these file name components insures that an ASM file name is always unique and can be easily tied back to its owning ASM disk group and database instance. Since the ASM file naming convention can be cumbersome, ASM also supports file name aliases for easier referencing of the ASM file instead of using the fully-qualified ASM file name.

Implementing ASM Storage In Oracle Databases Every database instance that uses ASM for file storage will also need two new processes. The Rebalancer background process (RBAL) handles global opens of all ASM disks in the ASM Disk Groups, while the ASM Bridge process (ASMB) connects as a foreground process into the ASM instance when the regular database instance starts. ASMB facilitates communication between the ASM instance and the regular database, including handling physical file changes like data file creation and deletion. ASMB exchanges messages between both servers for statistics update and instance health validation. These two processes are automatically started by the database instance when a new Oracle file type - for example, a tablespace's datafile -- is created on an ASM disk group. When an ASM instance mounts a disk group, it registers the disk group and connect string with Group Services. The database instance knows the name of the disk group, and can therefore use it to locate connect information for the correct ASM instance. ASM Instance Failure Scenarios. There are some implications for any database instance that is using ASM files for storage: When an ASM instance is shut down, any database instance that is using that ASM instance to store ASM files will lose contact with those ASM-managed files. Oracle 10g overcomes this potential failure scenario by allowing multiple ASM instances to share ASM disk groups, so if one ASM instance should fail, another ASM instance can continue to manage the changes to data stored on those disk groups.

Next Steps The next article in this series will focus on a simple implementation of Automated Storage Management. It provides examples of how to create an example ASM instance in both the Red Hat Enterprise Linux (RHEL) and Windows NT environments as well as a demonstration of how to implement basic ASM storage for an existing Oracle database instance.

Oracle 10g Automatic Storage Management (ASM): Sample Implementation By Jim Czuprynski Synopsis. Oracle 10g's Automatic Storage Management (ASM) features offer powerful tools to Oracle DBAs to create and manage a robust, flexible, scalable file storage system ready for access by any existing Oracle database instance. This article -- the second in this series -- provides a simple yet practical demonstration of setting up an ASM instance in both the Linux and Windows NT environments for purposes of exploration and experimentation, including how to migrate existing tablespaces to the ASM storage environment. The previous article in this series presented a high-level overview of Oracle 10g's new Automatic Storage Management (ASM) features and how ASM instances can be used in concert with other Oracle database instances to provide a scalable, reliable architecture for managing and monitoring large disk volumes via the ASM file system. Please note that this article and the corresponding examples are meant to demonstrate how ASM can be implemented on a small scale as a confidence-building exercise and to show how easy it is to set up ASM in an extremely basic single-CPU server environment. It is most definitely not intended as a starting point for a full-blown ASM implementation, which does (and should!) involve significant effort. I will demonstrate how to set up an ASM instance in both the Linux and Windows NT operating systems, how to set up tablespaces in a database instance that uses ASM for data storage, and how to reconfigure Oracle 10g Enterprise Manager (EM) to monitor and manage the ASM instance. For my test bed servers I utilized Red Hat Enterprise Linux Advanced Server 3.0, kernel 2.4.1.2, and Windows XP 2002 Professional Service Pack 2 for these demonstrations; except where noted, all code listed as part of this article should work within either operating system. Fortunately, the preparations required prior to creating the ASM instance and its disk groups are the most difficult part of this demonstration; once that's done, the rest is relatively simple.

Preparing Disks for ASM: Windows NT To simulate implementation of ASM in the Windows NT environment, I first created raw disk partitions on some of my hard drives. Fortunately, I had extra space on three of the four disks in my test server, so I created several primary disk partitions sized at 100MB on each of those three drives using the Windows Disk Partitioning utility (DISKPART.EXE): C:\> C:\> C:\> C:\> C:\> C:\> C:\> C:\> C:\> C:\> C:\>

select disk 2 Disk 2 selected. create partition primary Partition created. create partition primary Partition created. create partition primary Partition created. select disk 3 Disk 3 selected. create partition primary

size=100 size=100 size=100

size=100

C:\> Partition created. ... Figure 2.1.1 shows the results of the successful disk partitioning.

Configuring Windows NT Disks for an ASM Instance

Figure 2.1.1 Listing Volumes Created via DISKPART.EXE commands (NT environment).

Once the partitions are completed, I used the ASMTOOLG.EXE utility to "stamp" each partition with an ASM label so that Oracle can recognize these partitions as candidate disks for the ASM instance. I executed the ASMTOOLG.EXE program from the /bin directory of the Oracle home path for my Windows NT database. Figure 2.1.2 shows the initial screen that this GUI tool presented, and Figure 2.1.3 shows the screen that confirmed the creation of the ASM labels. Once the labels were assigned, I then re-invoked ASMTOOLG to confirm them (see Figure 2.1.4). I will also use ASMTOOLG to remove the labels prior to removing these partitions once my simulation is completed.

Figure 2.1.2 Using ASMTOOLG.EXE to label partitions created via DISKPART.EXE (NT environment).

Figure 2.1.3 Confirming the labels chosen before attaching them to their respective volumes.

Figure 2.1.4 Using ASMTOOLG.EXE to confirm assigned labels (NT environment).

Preparing Disks for ASM: Linux It is much simpler to simulate deployment of ASM in the Linux environment. From the root login, I created two new folders on separate disk spindles. I then used the dd command to create several empty files ready for use as ASM disks, and then set the appropriate permissions for those disks. See Listing 2.1 for the Linux commands required to accomplish this. (If you do not have separate spindles available, don't (.worry; just create the two folders on the same disk Listing 2.1: Create simulated disks of 100MB each for storage of - ---------mkdir mkdir mkdir mkdir

ASM disk groups and files in Red Hat Enterprise Linux

-p -p -p -p

/u03 /u03/asmdisks /u04 /u04/asmdisks

dd dd dd dd

if=/dev/zero if=/dev/zero if=/dev/zero if=/dev/zero

of=/u03/asmdisks/disk0 of=/u03/asmdisks/disk1 of=/u03/asmdisks/disk2 of=/u03/asmdisks/disk3

bs=1024k bs=1024k bs=1024k bs=1024k

number=100 number=100 number=100 number=100

dd dd dd dd

if=/dev/zero if=/dev/zero if=/dev/zero if=/dev/zero

of=/u04/asmdisks/disk0 of=/u04/asmdisks/disk1 of=/u04/asmdisks/disk2 of=/u04/asmdisks/disk3

bs=1024k bs=1024k bs=1024k bs=1024k

number=100 number=100 number=100 number=100

chown chown chmod chown

-R -R -R -R

oracle:oinstall /u03 oracle:oinstall /u04 777 /u03 777 /u04

Creating an ASM Instance Using DBCA In Windows NT Now that I have completed preparing all my disks, I used Oracle 10g's Database Configuration Assistant (DBCA), the simplest method to create an ASM instance, since the specification of all ASM instance parameters and creation of the instance's ASM Disk Groups is quite intuitive. I invoked DBCA from a command prompt on my Windows NT server and followed these steps: • • • • • •

•

•

First, I chose the Create a Database option (Figure 2.2.1) and clicked the Next button. I selected General Purpose as the Database Type (Figure 2.2.2) and clicked the Next button. Next, I supplied values for the Global Database Name (Figure 2.2.3) and clicked the Next button. I then accepted all default values for the Management Options (Figure 2.2.4) and clicked the Next button. I supplied the same password for all database management accounts (Figure 2.2.5) and clicked on Next. At this point, I must decide to create the ASM instance. I did this by selecting the Automatic Storage Management (ASM) option (Figure 2.2.6), at which point I was presented with a screen for specification of the ASM instance's additional password (Figure 2.2.7). Note that I could also click on the ASM Parameters button to specify exactly which directories Oracle should search for candidates for ASM disks, but in this case, I decided to let Oracle find the disks on its own. I clicked on Next to let Oracle create the ASM instance. Once the ASM instance had been created successfully, the screen in Figure 2.2.8 was presented. After I clicked on the Create New button, Oracle displayed the screen in Figure 2.2.9 to show all Windows NT partitions that were candidates for ASM disk group creation. I then selected the partitions desired, supplied a name for the ASM disk group (DGROUP1), and clicked the OK button to complete the ASM disk group creation. Once the ASM disk groups were created, Oracle presented one last screen (Figure 2.2.10) to confirm the disk group creation. At this point, even though it seems counter-intuitive, I clicked the Cancel button to complete the creation of the ASM instance, as there are no further steps required.

Creating an ASM Instance Using Oracle 10g DBCA

Figure 2.2.1 Choosing Create Database Option.

Figure 2.2.2 Choosing Database Type.

Figure 2.2.3 Specifying ASM Instance service name.

Figure 2.2.4 Specifying ASM Instance management options.

Figure 2.2.5 Specifying ASM Instance credentials.

Figure 2.2.6 Specifying ASM Instance storage options.

Figure 2.2.7 Creating ASM Instance.

Figure 2.2.8 Creating ASM disk groups.

Figure 2.2.9 Selecting potential disks from candidate list (NT environment shown).

Figure 2.2.10 Confirming creation of ASM disk group.

Creating an ASM Instance Using DBCA in Linux I used this same methodology to create an ASM instance in Linux using DBCA. The only significant difference was the contents of the Disk Selection window (Figure 2.2.9), which instead showed the candidate disks I had previously created on that server for the Linux environment. In either of these cases, the end result is the same: Once I clicked on the Cancel button on the last screen, Oracle dismounted the ASM disk group I had created and then shut down the ASM instance as well.

Creating an ASM Instance without Using DBCA Of course, I don't have to use DBCA to create an ASM instance. Personally, I prefer to use command scripts to create my database because it is easier to customize the scripts to create other ASM instances in the future, and it also gives me complete control over what Oracle is doing "behind the screen." In addition, an ASM instance is extremely easy to create because no CREATE DATABASE script is required, just an initialization parameter file like the one shown in Listing 2.2. Besides the usual initialization parameters for trace file directories, there are only a few additional ones required to create an ASM instance: ------ Listing 2.2: Initialization parameter file (PFILE) for creating -the example ASM instance. This file can be translated -into an SPFILE via the CREATE SPFILE AS PFILE; command ----# Standard ASM instance initialization parameters *.asm_power_limit = 5 *.db_unique_name = '+ASM' *.instance_type = 'asm' *.large_pool_size = 16M # Background, core, and user trace file directories. These will need # to exist before starting the instance and configured for the server's # operating system as appropriate *.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' # This requires a password file for the ASM instance, needed by # Enterprise Manager for a connection as SYS: REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE # ASM candidate disk search string. It's not required, but it does help # Oracle find the candidate disks more easily *.asm_diskstring = '\\.\ORCLDISK*' # For Windows environment *.asm_diskstring = '/u03/asmdisks/*', '/u04/asmdisks/*',# For Red Hat Linux environment # ASM Mountable Disk Group(s). These can be activated after the ASM # Instance has been created. #asm_disk_groups='DGROUP1'

ASM Initialization Parameters Initialization Description Parameter INSTANCE_TYPE Defines the instance as an ASM instance. This is the only required parameter to identify an ASM instance; the remainder can be left at their defaults DB_UNIQUE_NAME Defines the service provider name for which this ASM instance manages disk groups. +ASM is the default value, and should not be modified unless multiple ASM instances are on the same node ASM_POWER_LIMIT Controls rebalance operation speed. Values range from 1 to 11, with 11 being the fastest. If omitted, this value defaults to 1. The number of slaves is derived from the parallelization level specified in a manual rebalance command (POWER), or by the ASM_POWER_LIMIT parameter ASM_DISKSTRING An operating system dependent value; used by ASM to limit the set of disks considered for discovery ASM_DISK_GROUPS Describes the list of ASM disk group names to be mounted by an ASM instance at startup, or whenever the ALTER DISKGROUP ALL MOUNT command is used LARGE_POOL_SIZE The LARGE POOL size. This must be set to a minimum of 8MB, but Oracle recommends setting this to 16MB To create the ASM instance without using DBCA, I first made sure that I had created the directories I specified for BACKGROUND_DUMP_DEST, CORE_DUMP_DEST, and USER_DUMP_DEST. I also created a password file for the instance using the ORAPWD utility. (This is important, because when you attempt to connect to the ASM instance from Enterprise Manager, it will expect the instance to have the REMOTE_LOGIN_PASSWORDFILE initialization parameter set to EXCLUSIVE so that the instance can be contacted remotely and that means a password file will be required.) ORAPWD file=c:\oracle\app\product\10.1.0\db_1\database\PWD+ASM.ora password=oracle After I had made sure that the Oracle Cluster Services service was started in my Windows NT environment - it is usually named OracleCSService in the list of Windows Services that I can start - I then simply pointed my MS-DOS command window at the database instance by setting the value for ORACLE_SID to +ASM, started a SQL*Plus session, created an SPFILE from the parameter file, and then started the ASM instance in NOMOUNT mode: C:\> C:\> SQL> SQL> SQL> SQL> SQL>

set ORACLE_SID=+ASM sqlplus "sys as sysdba" Connected to an idle instance. create spfile from pfile=c:\init+asm.ora; File created. startup nomount; ASM instance started

Creating ASM Disk Groups without Using DBCA

If I am using DBCA to create my sample ASM instance, Oracle formats the commands necessary to create the initial ASM disk group(s) for the instance during its creation. However, I can also create the ASM disk groups separately after the necessary instance creation scripts have run successfully using the code shown in Listing 2.3. I have provided two different examples, one each for the Windows NT and Linux environments. ------ Listing 2.3: Issuing CREATE DISK GROUP statements to create the -initial ASM Disk Group for the example ASM instance ------ Create ASM disk group in NT environment CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY FAILGROUP ctlr1 DISK '\\.\ORCLDISKDATA0' , '\\.\ORCLDISKDATA2' FAILGROUP ctlr2 DISK '\\.\ORCLDISKDATA1' ,'\\.\ORCLDISKDATA3' ; -- Create ASM disk group in Linux environment CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY FAILGROUP ctlr1 DISK '/u03/asmdisks/disk0' ,'/u04/asmdisks/disk2' FAILGROUP ctlr2 DISK '/u03/asmdisks/disk1' ,'/u04/asmdisks/disk3' ;

Starting and Stopping an ASM Instance An ASM instance is managed in much the same way as a database instance, with a few exceptions. The major difference is that an ASM instance is never opened like a regular Oracle instance is opened, only mounted; therefore, I will either issue the STARTUP NOMOUNT; command to start just the ASM instance's memory processes, and then finish mounting the database with the ALTER DATABASE MOUNT; command. I can also open the ASM instance immediately by issuing the STARTUP MOUNT; command. Starting a Dormant ASM Instance. To start up a dormant ASM instance, I first have to remember to set the ORACLE_SID environment variable appropriately. In addition, before I can start the ASM instance in the Windows NT environment, I also need to remember to first start the Oracle Cluster Service as well as the ASM instance's service. Of course, I do not need to worry about this in the Linux environment – the Cluster Service should have already been set up as part of the standard Linux installation of Oracle 10g.

To start up the ASM instance, I then simply issue the STARTUP MOUNT command. Here is how a successful ASM instance startup appears in the Linux environment: $> export ORACLE_SID=+ASM $> sqlplus "sys as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Tue Dec 13 16:58:17 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Enter password: ******** Connected to an idle instance. SQL> startup mount; ASM instance started Total System Global Area 100663296 bytes Fixed Size 787648 bytes Variable Size 99875648 bytes Database Buffers 0 bytes Redo Buffers 0 bytes ASM diskgroups mounted Shutting Down an Active ASM Instance. To shut down this ASM instance, I once again set the ORACLE_SID environment variable and then simply issue the SHUTDOWN IMMEDIATE; command: $> export ORACLE_SID=+ASM $> sqlplus "sys as sysdba" SQL> Connected. SQL> shutdown immediate; ASM diskgroups dismounted ASM instance shutdown SQL> Implications of Shutting Down an Active ASM Instance. Now, a warning: When an ASM instance is shut down, it is important to be aware of the implications for any regular Oracle database instance that is using ASM files stored on that ASM instance. The ASM files will not be accessible to those regular Oracle databases until the ASM instance is restarted.

Demonstrating ASM in a Sample Database Now that I have explained how to create, start, and stop an ASM instance, I will next demonstrate how to add a tablespace to an existing Oracle database instance that uses the ASM instance's disk group instead of the database instance's disk storage to store the new tablespace's datafile. Creating an ASM-Managed Tablespace. What I really like about ASM is its simplicity. I no longer need to be concerned if the tablespace's datafile will fit on the drive or spindle I have targeted for storage; I simply inform ASM that it is responsible for storing the datafile and how large the datafile is, and ASM handles the rest. All I needed to do to create a new tablespace, TBS_ASM1, was to issue the following CREATE TABLESPACE command from a SQL*Plus session running against the database instance: SQL> CREATE TABLESPACE tbs_asm1 DATAFILE '+DGROUP1' SIZE 32M; Tablespace created.

Note that I did not have to specify the exact location of the datafile, since ASM determined from the size of the tablespace exactly how it should be striped across the disks in the ASM disk group. Moreover, since ASM uses Oracle Managed Files (OMF) for naming the datafile, ASM automatically named the datafile using the appropriate OMF standard. (I will delve into ASM file naming conventions in greater detail in the next article in this series.) Even more interesting, I did not have to do anything special to inform the database instance that it needed to start up the appropriate Rebalancing (RBAL) and ASM Bridge (ASM) processes. As soon as this new tablespace was created, the database instance detected that ASM storage was in use, and it automatically started these two background processes, as this snippet from the database's alert log clearly shows: ... Sun Dec 11 18:35:00 2005 CREATE TABLESPACE tbs_asm1 DATAFILE '+DGROUP1' SIZE 32M Sun Dec 11 18:35:03 2005 Starting background process ASMB ASMB started with pid=21, OS id=776 Starting background process RBAL RBAL started with pid=22, OS id=3524 Sun Dec 11 18:35:09 2005 SUCCESS: diskgroup DGROUP1 was mounted Completed: CREATE TABLESPACE tbs_asm1 DATAFILE '+DGROUP1' SIZ ... Migrating an Existing Tablespace to ASM-Managed Storage. Another great thing about ASM: I did not have to recreate an existing tablespace when I wanted to migrate it to ASM storage. Instead, I used Recovery Manager (RMAN) to create an image copy of the tablespace's datafile, and then I simply migrated that datafile to ASM. To demonstrate, I created a new tablespace in the database instance. I then issued the appropriate RMAN commands to take the tablespace offline, create the image copy of the tablespace, transfer the tablespace to ASM, and then bring the tablespace back online. See Listing 2.4 for the SQL statements and RMAN commands that I used to complete this task. ------ Listing 2.4: Migrating an existing tablespace to ASM storage ---------- Create a new tablespace managed by the regular database instance ----SQL> CREATE TABLESPACE tbs_regular 2 DATAFILE 'f:\oradata\orcl\orcl\tbs_regular01.dbf' 3 SIZE 32M; Tablespace created. ------ Migrate the new tablespace to ASM storage ----C:\>rman nocatalog target / Recovery Manager: Release 10.1.0.2.0 - Production Copyright (c) 1995, 2004, Oracle.

All rights reserved.

connected to target database: ORCL (DBID=1099944437) using target database controlfile instead of recovery catalog RMAN> SQL "ALTER TABLESPACE tbs_regular OFFLINE"; sql statement: ALTER TABLESPACE tbs_regular OFFLINE RMAN> BACKUP AS COPY TABLESPACE tbs_regular FORMAT '+DGROUP1'; Starting backup at 14-DEC-05 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=127 devtype=DISK channel ORA_DISK_1: starting datafile copy input datafile fno=00014 name=F:\ORADATA\ORCL\ORCL\TBS_REGULAR01.DBF output filename=+DGROUP1/orcl/datafile/tbs_regular.257.1 tag=TAG20051214T185816 recid=2 stamp=577047 501 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 14-DEC-05 RMAN> SWITCH TABLESPACE tbs_regular TO COPY; datafile 14 switched to datafile copy "+DGROUP1/orcl/datafile/tbs_regular.257.1" RMAN> SQL "ALTER TABLESPACE tbs_regular ONLINE"; sql statement: ALTER TABLESPACE tbs_regular ONLINE RMAN> exit Recovery Manager complete. C:\>sqlplus "sys as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Wed Dec 14 19:00:34 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Production With the Partitioning, OLAP and Data Mining options SQL> SQL> SQL> SQL> SQL>

set pagesize 50 set linesize 100 col tablespace_name FORMAT A12 HEADING 'Tablespace' col file_name FORMAT A60 HEADING 'File Name' select tablespace_name, file_name from dba_data_files;

Tablespace File Name -------------------------------------------------------------------USERS F:\ORADATA\ORCL\ORCL\USERS01.DBF SYSAUX F:\ORADATA\ORCL\ORCL\SYSAUX01.DBF UNDOTBS1 F:\ORADATA\ORCL\ORCL\UNDOTBS01.DBF SYSTEM F:\ORADATA\ORCL\ORCL\SYSTEM01.DBF EXAMPLE F:\ORADATA\ORCL\ORCL\EXAMPLE01.DBF DATA1 F:\ORADATA\ORCL\ORCL\DATA1.DBF DATA2 F:\ORADATA\ORCL\ORCL\DATA2.DBF INDX1 F:\ORADATA\ORCL\ORCL\INDX1A.DBF INDX1 F:\ORADATA\ORCL\ORCL\INDX1B.DBF

INDX1 INDX2 LOB1 TBS_ASM1 TBS_REGULAR

F:\ORADATA\ORCL\ORCL\INDX1C.DBF F:\ORADATA\ORCL\ORCL\INDX2.DBF F:\ORADATA\ORCL\ORCL\LOB1.DBF +DGROUP1/orcl/datafile/tbs_asm1.256.5 +DGROUP1/orcl/datafile/tbs_regular.257.1

14 rows selected.

Setting Up Enterprise Manager (EM) for ASM Instance Management Oracle 10g Enterprise Manager (EM) does provide a simple and elegant way to manage ASM storage; however, I needed to reconfigure my database instance's EM configuration to take advantage of these tools via the Enterprise Manager Configuration Assistant (EMCA). Once I had created the ASM instance and had then created at least one ASM-managed file in my database instance, I removed the original EM configuration for the database instance and then replaced it with a new EM configuration that fully supports ASM. A sample set of EMCA commands are shown in Listing 2.5. ------ Listing 2.5: Reconfiguring Enterprise Manager (EM) for use with ASM: -1.) Shut down Database Console service -2.) Remove current EM configuration -3.) Recreate new EM configuration -4.) Restart Database Console service ---------- Step 1. Stop the Database console ----C:\>emctl stop dbconsole Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0 Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved. http://zdc-dbsrvr:5500/em/console/aboutApplication The OracleDBConsoleorcl service is stopping.. The OracleDBConsoleorcl service was stopped successfully. ------ Step 2. Remove the EM configuration for the ORCL database ----C:\>set oracle_sid=orcl C:\>emca -x orcl STARTED EMCA at Wed Dec 14 07:54:19 CST 2005 Dec 14, 2005 7:54:19 AM oracle.sysman.emcp.EMConfig stopOMS INFO: Stopping the DBConsole ... Enterprise Manager configuration is completed successfully FINISHED EMCA at Wed Dec 14 07:54:20 CST 2005 ------ Step 3. Recreate the EM configuration for the ORCL database, -but this time include the ASM instance information -as well (-a). The -r directive tells EMCA not to -recreate the existing EM repository on the ORCL database.

-default ------

Note that except where indicated, simply accept the settings for best results

C:\>emca -r -a STARTED EMCA at Wed Dec 14 07:57:47 CST 2005 Enter the following information about the database to Listener port number: 1521 Database SID: orcl Service name: orcl.oracle.com Email address for notification: << NULL is acceptable Email gateway for notification: << NULL is acceptable ASM ORACLE_HOME [ C:\oracle\product\10.1.0\db_1 ]: << ASM SID [ +ASM ]: << accept default ASM port [ 1521 ]: << accept default ASM user role [ SYSDBA ]: << accept default ASM user name [ SYS ]: << accept default ASM user password: ******** Password for dbsnmp: ******** Password for sysman: ******** Password for sys: ********

be configured

here here accept default

----------------------------------------------------------------You have specified the following settings Database ORACLE_HOME .............. C:\oracle\product\10.1.0\db_1 Enterprise Manager ORACLE_HOME .... C:\oracle\product\10.1.0\db_1 Database host name ................ zdc-dbsrvr Listener port number .............. 1521 Database SID ................ orcl Service name ................ orcl.oracle.com Email address for notification ............... Email gateway for notification ............... ASM ORACLE_HOME ................ C:\oracle\product\10.1.0\db_1 ASM SID ................ +ASM ASM port ................ 1521 ASM user role ................ SYSDBA ASM user name ................ SYS ----------------------------------------------------------------Do you wish to continue? [yes/no]: yes Dec 14, 2005 7:58:33 AM oracle.sysman.emcp.EMConfig updateReposVars INFO: Updating file C:\oracle\product\10.1.0\db_1\sysman\emdrep\config\repository.variabl es … Dec 14, 2005 7:58:33 AM oracle.sysman.emcp.util.PortQuery findUsedPorts INFO: Searching services file for used port Dec 14, 2005 7:58:34 AM oracle.sysman.emcp.EMConfig addPortEntries INFO: Updating file C:\oracle\product\10.1.0\db_1\install\portlist.ini … Dec 14, 2005 7:58:34 AM oracle.sysman.emcp.EMConfig updateEmdProps INFO: Updating file C:\oracle\product\10.1.0\db_1\sysman\config\emd.properties… Dec 14, 2005 7:58:34 AM oracle.sysman.emcp.EMConfig updateConfigFiles INFO: targets.xml file is updated successfully Dec 14, 2005 7:58:34 AM oracle.sysman.emcp.EMConfig updateEmomsProps

INFO: Updating file C:\oracle\product\10.1.0\db_1\sysman\config\emoms.properties ... Dec 14, 2005 7:58:34 AM oracle.sysman.emcp.EMConfig updateConfigFiles INFO: emoms.properties file is updated successfully Dec 14, 2005 7:58:36 AM oracle.sysman.emcp.EMConfig startOMS INFO: Starting the DBConsole ... Dec 14, 2005 8:00:16 AM oracle.sysman.emcp.EMConfig perform INFO: DBConsole is started successfully Dec 14, 2005 8:00:16 AM oracle.sysman.emcp.EMConfig perform INFO: >>>>>>>>>>> The Enterprise Manager URL is http://zdcdbsrvr:5500/em <<<<<<<<<<< Enterprise Manager configuration is completed successfully FINISHED EMCA at Wed Dec 14 08:00:16 CST 2005

After I restarted the EM Database Console service for my database, I was then able to view details about the ASM instance as well by clicking on the ASM link on my database instance's home page (see Figure 2.3 for an example of that screen). I will explore the various diagnostic tools and maintenance operations available via EM in more detail in the next article in this series.

Figure 2.3 Enterprise Manager (EM) reconfigured to permit ASM storage management.

Viewing ASM Instance Information Via SQL Queries Finally, there are several dynamic and data dictionary views available to view an ASM configuration from within the ASM instance itself:

ASM Dynamic Views: ASM Instance Information View Name Description V$ASM_ALIAS Shows every alias for every disk group mounted by the ASM instance V$ASM_CLIENT Shows which database instance(s) are using any ASM disk groups that are being mounted by this ASM instance V$ASM_DISK Lists each disk discovered by the ASM instance, including disks that are not part of any ASM disk group V$ASM_DISKGROUP Describes information about ASM disk groups mounted by

the ASM instance Lists each ASM file in every ASM disk group mounted by the ASM instance V$ASM_OPERATION Like its counterpart, V$SESSION_LONGOPS, it shows each long-running ASM operation in the ASM instance V$ASM_TEMPLATE Lists each template present in every ASM disk group mounted by the ASM instance V$ASM_FILE

I was also able to query the following dynamic views against my database instance to view the related ASM storage components of that instance:

ASM Dynamic Views: Database Instance Information View Name Description V$ASM_DISKGROUP Shows one row per each ASM disk group that's mounted by the local ASM instance V$ASM_DISK Displays one row per each disk in each ASM disk group that are in use by the database instance V$ASM_CLIENT Lists one row per each ASM instance for which the database instance has any open ASM files See Listing 2.6 for the SQL*Plus queries that I used to view information from the ASM and database instances. ------ Listing 2.6: Queries Against ASM Dynamic Views ----SET LINESIZE 100 SET PAGESIZE 60 ------ V$ASM_ALIAS -- Shows every alias for every ----TTITLE 'ASM Disk Group Aliases COL name FORMAT Alias' COL group_number FORMAT COL file_number FORMAT COL file_incarnation FORMAT Inc#' COL alias_index FORMAT COL alias_incarnation FORMAT COL parent_index FORMAT Index' COL reference_index FORMAT COL alias_directory FORMAT COL system_created FORMAT SELECT name ,group_number ,file_number ,file_incarnation ,alias_index ,alias_incarnation ,parent_index

disk group mounted by the ASM instance (From V$ASM_ALIAS)' A48 HEADING 'Disk Group 99999 99999 99999

HEADING 'ASM|File #' HEADING 'File #' HEADING 'ASM|File|

99999 99999 99999

HEADING 'Alias|Index' HEADING 'Alias|Incn#' HEADING 'Parent|

99999 A4 A4

HEADING 'Ref|Idx' HEADING 'Ali|Dir?' HEADING 'Sys|Crt?'

,reference_index ,alias_directory ,system_created FROM v$asm_alias ; ------ V$ASM_CLIENT -- Shows which database instance(s) are using any ASM disk groups -- that are being mounted by this ASM instance ----TTITLE 'ASM Client Database Instances (From V$ASM_CLIENT)' COL group_number FORMAT 99999 HEADING 'ASM|File #' COL instance_name FORMAT A32 HEADING 'Serviced Database Client' WRAP COL db_name FORMAT A08 HEADING 'Database|Name' COL status FORMAT A12 HEADING 'Status' SELECT group_number ,instance_name ,db_name ,status FROM v$asm_client ; ------ V$ASM_DISK -- Lists each disk discovered by the ASM instance, including disks -- that are not part of any ASM disk group ----TTITLE 'ASM Disks - General Information (From V$ASM_DISK)' COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #' COL disk_number FORMAT 99999 HEADING 'ASM|Disk|#' COL name FORMAT A32 HEADING 'ASM Disk Name' WRAP COL total_mb FORMAT 99999999 HEADING 'Total|Disk| Space(MB)' COL compound_index FORMAT 999 HEADING 'Cmp|Idx|#' COL incarnation FORMAT 999 HEADING 'In#' COL mount_status FORMAT A07 HEADING 'Mount|Status' COL header_status FORMAT A12 HEADING 'Header|Status' COL mode_status FORMAT A08 HEADING 'Mode|Status' COL state FORMAT A07 HEADING 'Disk|State' COL redundancy FORMAT A07 HEADING 'Redun-|dancy' COL path FORMAT A32 HEADING 'OS Disk Path Name' WRAP SELECT group_number ,disk_number ,name ,total_mb ,compound_index ,incarnation ,mount_status ,header_status ,mode_status ,state ,redundancy ,path FROM v$asm_disk ;

TTITLE 'ASM Disks - I/O Status (From V$ASM_DISK)' COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #' COL disk_number FORMAT 99999 HEADING 'ASM|Disk|#' COL mount_status FORMAT A07 HEADING 'Mount|Status' COL total_mb FORMAT 999999 HEADING 'Total|Disk| Space(MB)' COL free_mb FORMAT 999999 HEADING 'Free|Disk|Space(MB)' COL reads FORMAT 999999 HEADING 'Disk|Reads' COL mb_read FORMAT 999999 HEADING 'Reads|(MB)' COL read_time FORMAT 999999 HEADING 'Read|Time' COL read_errs FORMAT 999999 HEADING 'Read|Errors' COL writes FORMAT 999999 HEADING 'Disk|Writes' COL write_errs FORMAT 999999 HEADING 'Write|Errors' COL write_time FORMAT 999999 HEADING 'Write|Time' SELECT group_number ,disk_number ,mount_status ,total_mb ,free_mb ,reads ,(bytes_read / (1024*1024)) mb_read ,read_errs ,read_time ,writes ,write_errs ,write_time FROM v$asm_disk ; ------ V$ASM_DISKGROUP -- Describes information about ASM disk groups mounted by the ASM instance ----TTITLE 'ASM Disk Groups (From V$ASM_DISKGROUP)' COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #' COL name FORMAT A12 HEADING 'ASM Disk|Group Name' WRAP COL sector_size FORMAT 99999999 HEADING 'Sector|Size' COL block_size FORMAT 999999 HEADING 'Block|Size' COL au_size FORMAT 99999999 HEADING 'Alloc|Unit|Size' COL state FORMAT A11 HEADING 'Disk|Group|State' COL type FORMAT A06 HEADING 'Disk|Group|Type' COL total_mb FORMAT 999999 HEADING 'Total|Space(MB)' COL free_mb FORMAT 999999 HEADING 'Free|Space(MB)' SELECT group_number ,name ,sector_size ,block_size ,allocation_unit_size au_size ,state ,type ,total_mb ,free_mb FROM v$asm_diskgroup ; ------ V$ASM_FILE

-- Lists each ASM file in every ASM disk group mounted by the ASM instance ----TTITLE 'ASM Files (From V$ASM_FILE)' COL group_number FORMAT 99999 HEADING 'ASM|File #' COL file_number FORMAT 99999 HEADING 'File #' COL compound_index FORMAT 999 HEADING 'Cmp|Idx|#' COL incarnation FORMAT 999 HEADING 'In#' COL block_size FORMAT 999999 HEADING 'Block|Size' COL blocks FORMAT 999999 HEADING 'Blocks' COL bytes_mb FORMAT 999999 HEADING 'Size|(MB)' COL space_alloc_mb FORMAT 999999 HEADING 'Space|Alloc|(MB)' COL type FORMAT A32 HEADING 'ASM File Type' WRAP COL redundancy FORMAT A06 HEADING 'Redun-|dancy' COL striped FORMAT A07 HEADING 'Striped' COL creation_date FORMAT A12 HEADING 'Created On' COL modification_date FORMAT A12 HEADING 'Last|Modified' SELECT group_number ,file_number ,compound_index ,incarnation ,block_size ,blocks ,(bytes / (1024*1024)) bytes_mb ,(space / (1024*1024)) space_alloc_mb ,type ,redundancy ,striped ,creation_date ,modification_date FROM v$asm_file ; ------ V$ASM_OPERATION -- Like its counterpart, V$SESSION_LONGOPS, it shows each longrunning -- ASM operation in the ASM instance ----TTITLE 'Long-Running ASM Operations (From V$ASM_OPERATIONS)' COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #' COL operation FORMAT A08 HEADING 'ASM|Oper-|ation' COL state FORMAT A08 HEADING 'ASM|State' COL power FORMAT 999999 HEADING 'ASM|Power|Rqstd' COL actual FORMAT 999999 HEADING 'ASM|Power|Alloc' COL est_work FORMAT 999999 HEADING 'AUs|To Be|Moved' COL sofar FORMAT 999999 HEADING 'AUs|Moved|So Far' COL est_rate FORMAT 999999 HEADING 'AUs|Moved|PerMI' COL est_minutes FORMAT 999999 HEADING 'Est|Time|Until|Done| (MM)' SELECT group_number ,operation ,state ,power ,actual ,est_work ,sofar ,est_rate ,est_minutes

;

FROM v$asm_operation

------ V$ASM_TEMPLATE -- Lists each template present in every ASM disk group mounted -- by the ASM instance ----TTITLE 'ASM Templates (From V$ASM_TEMPLATE)' COL group_number FORMAT 99999 HEADING 'ASM|Disk|Grp #' COL entry_number FORMAT 99999 HEADING 'ASM|Entry|#' COL redundancy FORMAT A06 HEADING 'Redun-|dancy' COL stripe FORMAT A06 HEADING 'Stripe' COL system FORMAT A03 HEADING 'Sys|?' COL name FORMAT A30 HEADING 'ASM Template Name' WRAP SELECT group_number ,entry_number ,redundancy ,stripe ,system ,name FROM v$asm_template ;

Next Steps The next article in this series will concentrate on some of the more advanced features of ASM, including how to add disks to and remove disks from an ASM disk group, how to increase the survivability of ASM disk groups with additional striping and mirroring features, and how to monitor and manage ASM storage through the Enterprise Manager (EM) interface.

References and Additional Reading While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article: B10130-02 Oracle Database Installation Guide 10g (10.1.0.2) For Windows, Section 2.5.3 B10739-01 Oracle Database Administrator's Guide, Chapter 12 B10743-01 Oracle Database Concepts, Chapter 14 B10755-01 Oracle Database Reference B10759-01 Oracle Database SQL Reference

Oracle 10g Automatic Storage Management (ASM), Part 3: Advanced Features By Jim Czuprynski

Synopsis. Oracle 10g's Automatic Storage Management (ASM) features offer powerful tools to Oracle DBAs to create and manage a robust, flexible, scalable file storage system ready for access by any existing Oracle database instance. This article – the third and final in this series – navigates the myriad ASM file naming conventions and templates, provides examples of ASM storage management commands for adding and removing disks and disk groups, and covers how to convert an entire Oracle database to use ASM storage instead of regular file system storage. The previous article in this series presented complete demonstrations of how to set up an ASM instance in both Linux and Windows NT, as well as how to use Oracle 10g's Enterprise Manager (EM) to monitor and manage an ASM instance. This article will focus on ASM instance administration, including how to add, alter, and remove ASM disks and disk groups, how to increase the survivability of ASM disk groups with additional striping and mirroring features, and how to migrate an entire Oracle database from regular to ASM storage. Before I turn my attention to those topics, though, I will review how ASM's file naming conventions help to make quick work of organizing database files and components within an ASM instance.

ASM Naming Conventions The first thing I noticed when I began my ASM explorations was the unique method of file naming that ASM employs. All ASM file names are based on the Oracle Flexible Architecture (OFA) standard. ASM also employs Oracle Managed File (OMF) structures and rules for file creation, which means that Oracle will clean up after itself when files are no longer needed. For example, if I drop a tablespace, ASM will figure out which datafile(s) are no longer required and will then automatically remove the physical files without any intervention on my part. If an RMAN backup fails, ASM (through OMF) will simply delete any partial files created as part of that failed backup. One big advantage of this approach is that I have a lot less to worry about when it comes to physical file management. Instead of being concerned about whether I'm using "acceptable" names for physical files, I can simply let ASM worry about this instead, thus freeing me up to concentrate on which logical objects I need to create to support my database.

ASM File Templates ASM uses file templates to figure out how to configure the myriad attributes for a new Oracle database file. A file template is simply a named collection of attributes associated with an ASM disk group; whenever a new file is created within a disk group, these attributes are applied to the file. Templates also help to simplify file creation because they can be used to map complex file attributes to a single specification. Certain attributes of an ASM file are set forever when the file is initially created, including the file's protection policy and striping policy. Default Templates. When a disk group is created, ASM associates it with a set of initial system default templates that include default attributes for the various Oracle database file types like control files (CONTROLFILE) or data files (DATAFILE). Also, the DBA can mandate whether files created via the template should be two-way or three-way mirrored, as well as whether files created under

that template are COARSE or FINE striped. Table 3.1 lists these default templates and their characteristics. Table 3.1: ASM Default File Templates and Attributes Database File ASM Default Template ASM File Type Type Archived Redo Logs Archived Redo Log Backup Pieces Control Files Control File Autobackups Cross-Platform Converted Data Files Data Files Data File Backup Pieces Data File Backup Image Copies DataGuard Broker Configurations DataPump DUMPSet Flashback Logs Online Redo Logs Server Initialization Parameters TEMPFILEs

ASM Tag Assigned

ARCHIVELOG

archive_log

parameter

ASM Default Striping coarse

BACKUPSET

backupset

Specified by client

coarse

CONTROLFILE AUTOBACKUP

controlfile autobackup

CF or BCF Specified by client

fine coarse

XTRANSPORT

N/A

N/A

coarse

DATAFILE BACKUPSET

datafile backupset

_ Specified by client

coarse coarse

BACKUPSET

datafile

_

coarse

DATAGUARDCONFIG

drc

drc

coarse

DUMPSET

dumpset

dump

coarse

FLASHBACK ONLINELOG

rlog online_log

_ log_

fine fine

PARAMETERFILE

init

spfile

coarse

TEMPFILE

temp

_

coarse

These system default templates cannot be deleted; depending on the defined disk group redundancy characteristics, the system templates are created with the attributes exactly as shown. However, some of the attributes of the default templates can be modified (except for redundancy and striping, of course). It is also possible to add new user-defined file templates for special types of database files; this can assist in standardization of database physical and logical structures. See Listing 3.1 for examples of how to create and drop user-defined templates. There is one unfortunate drawback of ASM file templates: If an ASM file attribute needs to be modified after the file has been created, the file must be copied via RMAN into a new file with the new settings. Changing the template that was originally used to create the ASM file does not automatically populate the change to the file itself.

ASM File Names: Some Illustrations To demonstrate these sundry naming patterns, I will use an actual example of just one ASM file -- the datafile for the TBSASM tablespace – to show how templates and file naming conventions combine to help manage ASM file naming

standards. Which pattern to use for an ASM file name depends on the context of how the filename is going to be used: (a) when an existing file is being referenced; (b) during the creation of just one file; and (c) during the creation of multiple files. Fully-Qualified Names. When an ASM-managed file is created, ASM assigns the file a fully-qualified, unique file name. This file name - also known as a system alias - is the most detailed description of the ASM file, so it is the most direct method to access any ASM file. Since ASM constructs the system alias automatically when the file is created, fully-qualified names are not permitted to be specified when creating the file, and no user is permitted to modify this name. Here is an actual example of a fully-qualified ASM file name; it is the name of the file that represents the datafile for the TBSASM tablespace: ASM1DG1.ORCL.DATAFILE.TBSASM.257.1 A fully-qualified file name comprises the following five components, and always ends with a special number pair: • • • • •

. The name of the ASM disk group. . The database that owns this ASM file. . The Oracle file type (e.g. DATAFILE, ARCHIVELOG, CONTROLFILE). . Type-specific information about the file. In this case, it is the corresponding tablespace name for the datafile. .. A special number pair that ASM generates to insure uniqueness.

Numeric Names. This type consists of just two components, the disk group and the unique numeric identifier that ends the file name and that is automatically assigned by ASM during file creation. Therefore, this file name type can only be used for referencing existing ASM files. To continue the previous example, here is the file name that represents the datafile from the TBSASM tablespace: ASM1DG1.257.1 Incomplete Names. Incomplete ASM file names are used only for file creation operations. They consist of a disk group name only. ASM will then use the appropriate default template to translate the incomplete ASM file name, as defined by its file type. For example, here is the SQL command I executed originally to create the TBSASM tablespace in the ASM1DG1 disk group: SQL> CREATE TABLESPACE tbsasm DATAFILE '+ASM1DG1' SIZE 32M; Incomplete Names With Named Template. Incomplete ASM file names with named templates are used only for file creation operations. They consist of a disk group name plus a template name. The template name determines the file creation attributes of the file. For example: SQL> CREATE TEMPORARY TABLESPACE temp01 SQL> TEMPFILE '+ASM1DG1(TEMPFILE)' SIZE 32M;

ASM File Name Aliases

ASM file name aliases can be used to create new or reference existing ASM files. While aliases do specify a disk group name, they include a user-friendly name string instead of a file and incarnation number. (In fact, if I attempt to construct an alias that ended with a dotted pair of numbers, Oracle will detect the problem and will signal an error.) Aliases are implemented using a hierarchical directory structure, thus making it simple to reference ASM files via common-sense, human-recognizable names. I can create an additional alias for each file during its creation, or I can create an alias after its creation via the ALTER DISKGROUP ADD ALIAS command. ASM file name aliases are case-insensitive, and will always contain a slash (/) that separates its components; each component is stored in UTF-8 format and may be up to 48 bytes long, but the component itself cannot contain a slash. The components of alias file names can contain spaces between sets of characters, but the space should not be the first or last character of a component. This implies a 48-character limit in a single-byte language, but in a multi-byte language, this size limit will be lower depending upon how many multi-byte characters are present in the string. The total length of an aliased file name, including all components and all separators, is limited to 256 bytes. I can create directory structures as required to support whatever alias naming convention is desired, but it is important to remember that these directories will still be subject to the 256-byte limit. A common use of an ASM file name alias is during the creation of the database's control files using the CONTROL_FILES parameter, or when creating online redo log files. The following example assumes that I have already created a directory named LOGFILES: SQL ALTER DATABASE SQL> ADD LOGFILE GROUP 4 SQL> MEMBERS SQL> '+ASM1DG1/logfiles/log4a.rdo' SIZE 32M, SQL> '+ASM1DG1/logfiles/log4b.rdo' SIZE 32M; Aliased Names Plus Templates. This file name format lists the disk group name, an alias name, and a file creation template name. It is therefore used only during ASM file creation operations. For example, this statement assumes that an aliased directory, dbf, already exists, and that I am creating a server parameter file from a parameter file: CREATE SPFILE '+ASM1DG1/dbfs/(spfile)' FROM PFILE; Finally, it is important to note that an ASM file that was created by specifying a user alias will not be cleaned up automatically by ASM when the file is dropped later. Listing ASM File Name Aliases. To retrieve all ASM file name aliases stored within the database, the V$ASM_ALIAS dynamic view can be traversed, starting at the ASM disk group level and then traverse the hierarchy of the alias information stored within. Note that the REFERENCE_INDEX number is required to traverse entries that are directory entries in this view; for all other alias entry types, REFERENCE_INDEX will be zeroed out.

See Listing 3.2 for examples of how to construct directories and aliases, query the ASM instance for all available aliases, and drop aliases and directories. Using ASM File Names in SQL Commands. Instead of supplying the full ASM file name, any of the other indirect naming methods mentioned above can be used when specifying an ASM file in a SQL command. This is in fact one of the biggest benefits of ASM: the need to specify long, cumbersome file names is reduced in almost every case. Oracle recommends using "shorthand" ASM file designations or ASM aliases as much as possible except for the few specific cases where ASM file names are needed as parameters (for example, when specifying the names of the database's online redo logs during an ALTER DATABASE RENAME FILE operation). Default File Sizes. When ASM creates a data file for a permanent table space, or a temporary file for a temporary table space, the data file is set to autoextensible with an unlimited maximum size and 100 MB default size. An AUTOEXTEND clause may override this default. ASM applies attributes to the files it creates as specified by the corresponding system default template. Some Exclusions. Finally, it is important to remember that ASM does not manage any binary files (i.e. BFILEs), alert logs, user or background trace files, or Oracle database password files. These files must still be named, backed up, and managed via manual methods.

Managing An ASM Instance Now that I have detailed ASM file naming techniques, I will turn my attention to the nuts and bolts of managing an ASM instance. Besides creating new files on the instance, I also need to know how to add new disks and new disk groups, modify disk group components, mount and dismount disk groups, and remove existing disks and disk groups from the ASM instance. Since ASM disk concepts are at the heart of these operations, an expanded exploration of ASM striping, mirroring techniques, and disk failure groups is in order.

Coarse vs. Fine-Grained Striping When a new disk group is created, ASM will spread files in 1MB allocation unit (AU) chunks across all of the disks in a disk group. This default method, called coarse striping, eliminates the need for manual disk tuning. When striping is completed, all disks in the same ASM disk group should have about the same size and performance characteristics; this helps to insure that optimal I/O is attainable. For most installations, only a small number of disk groups should be sufficient (e.g., one disk group designated as a work area and one for a recovery area). Thus, even though the number of files and disks may increase, a constant number of disk groups will probably have to be managed. Most ASM files stored in the ASM instance will perform well with normal (coarse) striping. However, Files like online redo logs, flashback logs, and control files typically require low latency. For these file types, ASM provides fine-grained (128K) striping. In this case, each AU is striped, and this tends to break up medium-sized I/O operations into many smaller I/O operations that will be executed in parallel.

Failure Groups

ASM failure groups provide mechanisms to prevent the failure of a set of disks within a single particular disk group that share a common resource whose failure must be tolerated. A typical example of this concept is a series of disks connected to a single, common disk controller. In this situation, if the controller failed, all the other disks on the controller's common bus would also be unavailable, even though all of the individual disks are still working fine. (Note that what constitutes a failure group tends to be site-specific and will depend upon the failure level(s) that a site can accept.) By default, ASM assigns each disk to its own failure group. The DBA can, of course, select a different disk grouping when creating a disk group or adding a disk to a disk group. Once a set of failure groups are identified, ASM will tend to optimize file layout so that the possibility of data unavailability because of the failure of a shared resource is reduced.

Disk Group Mirroring ASM provides three types of disk group mirroring possibilities. The first type, external redundancy, does not utilize failure groups at all and thus provides no mirroring. This type of redundancy is best when either the DBA has decided to rely upon external OS-level hardware mirroring, or when data loss is an acceptable risk due to media failure. Normal-redundancy disk groups are the ASM default, and they support two-way mirroring that insures two copies of a file extent will always exist on the disk group. Finally, high-redundancy disk groups support triple mirroring, which insures that there will always be three copies of each file extent on the disk group. When ASM allocates a new primary extent for a file to one disk in a disk group, it allocates a mirror copy of that extent to another disk on one of the several available "partner" disks in the same disk group. Because ASM ensures that a primary extent and its mirror copy never reside in the same failure group, ASM can thus tolerate simultaneous failures of multiple disks in a single failure group. In addition, since ASM mirrors file extents instead of disks, ASM requires spare capacity only within that disk group, not among all disks. When a disk fails – and all disks eventually do! - ASM reads the mirrored contents from the surviving disks and then automatically rebuilds the complete contents of the disk that has failed onto the surviving disks in the disk group. This also has the advantage of spreading out the I/O hit from the recovery from a disk failure across several disks.

Disk Group Rebalancing ASM automatically rebalances a disk group whenever disks are added or dropped. Therefore, the rebalancing process requires no intervention. Because ASM uses special indexing techniques to distribute extents over all available disks in a disk group, re-striping of the data is not required; instead, ASM only moves enough data proportionally versus the amount of storage that's been added or removed. This evenly redistributes the files and keeps a balanced I/O load across all disks in the disk group. Oracle recommends that, to avoid unnecessary data movement, it is generally more efficient to add or drop multiple disks simultaneously. This tends to insure that the disks will be rebalanced in a single operation.

Creating New Disk Groups On to some practical examples! I have already set up some additional simulated disks on my Linux server, so I will next create a new ASM disk group via the CREATE DISKGROUP SQL command. See Listing 3.3 for the sample code I used.

Adding New Disks If I want to add one or more ASM disks to an existing ASM disk group, I can use the ALTER DISKGROUP ADD DISK command. ASM performs the addition of ASM disks to an ASM disk group in a single operation. I can add specific ASM disks to a disk group by name, or I can choose to use a disk discovery string. The nice thing about the disk discovery string is that if ASM detects ASM disks that are already part of an ASM disk group in the folder specified by that string, the disks will be ignored and only those ASM disks that are not yet in use will be added. When I add an ASM disk to a disk group, the ASM instance will determine if the disk is addressable and usable; if so, then the ASM disk is formatted and rebalanced. Rebalancing tends to be time-consuming because it moves extents from every file in the ASM disk group onto the new ASM disk. Though an ASM rebalancing operation does not block any ongoing database operations, rebalancing will definitely have at least some impact on the I/O load on the system. I can also tell the ASM instance to expend more resources on the rebalancing by increasing the value of the ASM_POWER initialization parameter. See Listing 3.4 for an example of adding a new disk to an existing disk group while requesting a higher than normal amount of resources for rebalancing the affected ASM disk group.

Resizing Disks I can easily resize an existing ASM disk via the ALTER DISKGROUP RESIZE <size>; command. ASM offers me the choice to resize all disks in the specified disk group, all disks in the specified failure group, or just the specified disk. Listing 3.5 shows examples of these three options.

Verifying ASM Disk Groups Keeping my ASM disk groups in ship-shape condition should be one of my primary concerns. I can issue the ALTER DISKGROUP CHECK [NOREPAIR]; command to verify internal consistency of ASM disk group metadata and repair any possible errors. This command checks each file, each disk, or all components of the ASM disk group for any errors and will attempt to repair them. Specifying the NOREPAIR directive alerts me to any errors and bypasses any repairs; all errors will still be reported in the ASM instance's alert log. Listing 3.6 shows an example of ASM disk group verification via this command.

Manually Rebalancing a Disk Group

Even though ASM will automatically rebalance a disk group when any operation other than a MOUNT, DISMOUNT, or CHECK has been issued, I also have the option to initiate a disk rebalancing manually. Issuing the ALTER DISKGROUP REBALANCE [POWER n]; command requests ASM to perform rebalancing on the specified ASM disk group, provided that any rebalancing is needed. If the optional POWER directive is specified, ASM will rebalance the disk group using the integer value to override the value that the ASM_POWER_LIMIT initialization parameter has set forth. I can thus override the speed of a potential rebalancing or change the power level of an ongoing rebalance operation by changing the POWER directive's value. In addition, if I set POWER to zero (0), any rebalancing on the disk group is halted until I directly or implicitly invoke the rebalancing operation again. See Listing 3.7 for an example of manual rebalancing a selected ASM disk group.

Mounting and Dismounting ASM Disk Groups Dismounting a mounted ASM disk group is performed via the ALTER DISKGROUP DISMOUNT; command. Its reciprocal is the MOUNT directive. See Listing 3.8 for examples of both of these commands.

Removing Disks from a Disk Group I can issue the ALTER DISKGROUP DROP DISK ; command to drop a single ASM disk from an existing ASM disk group. And I can also "undrop" (i.e. cancel the pending drop) of any ASM disks that were previously assigned to ASM disk group(s) with the ALTER DISKGROUP UNDROP DISKS; command. However, note that once a disk drop operation completes successfully, issuing UNDROP will not recover the disks. See Listing 3.9 for examples of these two commands.

Dropping a Disk Group Finally, I can drop an entire existing ASM disk group – along with all of its files via the DROP DISKGROUP command. If the disk group does still contain any files besides internal ASM metadata, then I have to specify the INCLUDING CONTENTS option as well. Also, the disk group to be dropped must be mounted. ASM will first validate that none of the disk group files are open, and then remove all of the assigned ASM disks and the disk group itself from the ASM instance. ASM will also overwrite the header of each previously used ASM disk to remove any remaining ASM formatting information. Listing 3.10 shows an example of this command.

Migrating a Database to ASM Storage As discussed in the prior article in this series, ASM files cannot be accessed through normal operating system interfaces. Therefore, RMAN is the only means for copying files from regular storage to ASM, and is the only tool a DBA can use to migrate an entire database from regular to ASM storage. Oracle recommends the following process for a full database migration:

Step 1. Take a complete backup of the database before proceeding with any migration efforts. This will provide a relatively safe fallback position in case any of the migration steps outlined below should fail. Step 2. Gather the file names of the current control files and online redo logs using V$CONTROLFILE and V$LOGFILE. Step 3. Shut down the database in consistent mode using either SHUTDOWN IMMEDIATE, SHUTDOWN TRANSACTIONAL, or SHUTDOWN NORMAL. This is a crucial step. Step 4. Change the database's server parameter file: • •

Set the necessary OMF destination parameters to the desired ASM disk group. Remove the CONTROL_FILES parameter.

Step 5. Restore a control file from backup. This will migrate the control files to ASM storage automatically. Note that if an OMF control file is created but there is a server parameter file, then a CONTROL_FILES initialization parameter entry will also be created in the server parameter file. Step 6. Issue ALTER DATABASE MOUNT; to mount the database. Step 7. Edit and run an RMAN command file that: • • •

Backs up the database Switches the current data files to the backups Renames the online redo logs. You can move only tablespaces or data files using the BACKUP AS COPY command

Step 8. Once the DBA is certain that the migration has been successful, she can delete the original database files left behind in the "regular" storage directories. An example RMAN command script to accomplish Step 7 is shown in Listing 3.11.

Conclusion While I have endeavored to introduce practical examples of how to employ Automatic Storage Management (ASM) within multiple Oracle 10g environments, I have truly just scratched the scratch on the surface of what ASM can accomplish. ASM places extremely powerful file management tools into the hands of every Oracle DBA, and it is definitely worth the effort to explore its features no matter the size of the database environment. References and Additional Reading While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article: B10739-01 Oracle Database Administrator's Guide, Chapter 12

B10743-01 Oracle Database Concepts, Chapter 14 B10755-01 Oracle Database Reference B10759-01 Oracle Database SQL Reference