Using Oracle ASM Chapter 1 Oracle Database 11g: Administration II exam objectives covered in this chapter: AL ÛÛ Database Architecture and ASM Describe Automatic Storage Management (ASM) NN Set up initialization parameter files for ASM and database TE instances RI NN Start up and shut down ASM instances NN Administer ASM diskgroups CO PY RI GH TE D MA NN
Automatic Storage Management (ASM) provides a centralized way to manage Oracle Database disk storage. The Oracle Database 11g OCP exam will test your knowledge of ASM, and thus ASM is the first topic we will tackle in this book. In this chapter, we will discuss what ASM is, how to configure an ASM instance, how to manage an ASM instance, and finally, how to use ASM from within an Oracle database.
Working with the ASM Instance 3 Here are some features of ASM: NN Automatic software data striping (RAID-0) NN Load balancing across physical disks NN Software RAID-1 data redundancy with double or triple mirrors NN Elimination of fragmentation NN Simplification of file management via support for Oracle Managed Files (OMF) NN Ease of maintenance ASM fits perfectly into a Real Application Clusters (RAC) environment, but you can use ASM in a non-RAC environment too.
Chapter 1 n Using Oracle ASM Using ASM in the Real World If you are an Oracle database administrator, ASM will very much be on your mind if you have to deal with Oracle database clustering (RAC). Early on in the architecting process, you will have to decide how to store shared files because RAC depends on sharing of database datafiles among the different nodes of the cluster. You have several options, including shared raw devices and vendor-supplied products.
Working with the ASM Instance 5 E x e r c i s e 1 .1 Creating an ASM Instance with the DBCA. 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 Configure 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.
Chapter 1 6 n Using Oracle ASM 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. Step 1: Creating Directories for the ASM Instance An ASM instance is almost always called +ASM. An exception exists in RAC environments where the ASM instance will be called +ASM1, +ASM2, and so on. Create the admin directories for the instance using Oracle’s OFA standards.
Working with the ASM Instance 7 # 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 # Of course – set compatible to your version of Oracle ########################################### instance_type=asm compatible=11.1.0.6.
Chapter 1 n Using Oracle ASM Note in this example that we made the start mode manual so the service will not start automatically when the system is started. You will want to configure the service startup as required by your system. Step 4: Starting the ASM Instance You are now ready to start the Oracle ASM instance. Note that until you have added a disk group, you will get an error when the ASM instance is started. This is expected. In Exercise 1.2, you will see how to start an ASM instance.
Working with the ASM Instance 9 Managing the ASM Instance Management of the ASM instance is typically done from the command-line prompt.
Chapter 1 10 NN NN n Using Oracle ASM The MARK process is responsible for marking ASM allocation units as stale following a missed write to an offline disk. The RBAL process runs in both database and ASM instances. RBAL is responsible for performing a global open of ASM disks in normal databases. RBAL coordinates rebalance activity for disk groups in ASM instances. ASM Disk Discovery ASM disk discovery is the first step to setting up an ASM disk group.
Working with the ASM Instance 11 Another thing to consider when determining how to configure the ASM_DISKSTRING parameter is performance. Leaving this parameter set to NULL, and thus taking the Oracle default, will often be sufficient. However, if you set ASM_DISKSTRING using a more restrictive set of parameters, you may find that discovery of disks will be faster.
Chapter 1 n Using Oracle ASM PATH GROUP_# DISK_# MOUNT_S -------------- ------- ------ ------/dev/raw/raw4 0 1 CLOSED /dev/raw/raw5 0 0 CLOSED /dev/raw/raw3 0 2 CLOSED /dev/raw/raw6 0 2 CLOSED ORCL:ASM01_004 1 3 CACHED ORCL:ASM01_005 1 4 CACHED ORCL:ASM01_006 1 5 CACHED ORCL:ASM01_007 1 6 CACHED ORCL:ASM01_008 1 7 CACHED ORCL:ASM01_009 1 8 CACHED ORCL:ASM01_010 1 9 CACHED ORCL:ASM01_011 1 10 CACHED ORCL:ASM01_012 1 11 CACHED ORCL:ASM01_013 1 12 CACHED HEADER_STATU -----------FOREIGN FOREIGN FOREIGN
Working with the ASM Instance 13 Redundancy, Striping, and Other ASM Topics When configuring ASM disk groups, you need to consider recoverability, performance, and other attributes. We will first cover recoverability by discussing the concept of redundancy. We will then discuss striping of ASM disk groups and ASM disk templates. Then we’ll discuss ASM disk group attributes, ASM fast disk resync features, and ASM preferred mirror read features.
Chapter 1 n Using Oracle ASM 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 (We discuss templates later in this chapter). Table 1.2 gives you some guidance about the redundancy-related settings defined within the default template. Ta b l e 1 .
Working with the ASM Instance 15 Default ASM Template Redundancy Settings So, if you create a disk group with normal redundancy using the default template and you put datafiles on it, the datafile template would be used by default. In this case, a datafile would use two-way mirroring and coarse striping (see the section “Striping”). This means you would have to allocate at least two disks to an ASM disk group when it was created, one assigned to a different failure group.
Chapter 1 n Using Oracle ASM 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 2 where group_number=2; GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE ------------ ------------ ------ -----2 0 MIRROR COARSE 2 1 MIRROR COARSE 2 2 HIGH FINE 2 3 MIRROR COARSE 2 4 MIRROR FINE 2 5 MIRROR COARSE 2 6 MIRROR COARSE 2 7 MIRROR COARSE 2 8 MIRROR COARSE 2 9 MIRROR COARSE 2 10 MIRROR COARSE 2 11 MIRROR FINE 2 12
Working with the ASM Instance 17 group attributes are set using the attribute clause of the create diskgroup and alter diskgroup commands. The following attributes can be set on a specific ASM disk group: Au_size This is the disk group allocation unit (AU) size. The value defaults to 1MB and can be set only when the disk group is created. You must modify the AU size of the disk group if you want the disk group to be able to hold larger amounts of data.
Chapter 1 n Using Oracle ASM You can change the amount of time that Oracle will wait to automatically drop the disk by setting the disk_repair_time attribute (see the discussion on attributes earlier, in the section “ASM Disk group Attributes”) 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: Alter diskgroup dgroup1 set attribute ‘disk_repair_time’=’18h’; ASM Preferred Mirror Read The ASM preferred mi
Working with the ASM Instance 19 In this case, we have created a disk group called dgroup1. It is using normal redundancy and the default template. Two named failure groups are assigned, diskcontrol1 and diskcontrol2. Each failure group represents one physical or logical disk unit, which has been discovered by ASM. Two separate disks and failure groups are required because of the normal redundancy.
Chapter 1 n Using Oracle ASM You can reference the disk group and the disk name by joining the V$ASM_DISK and V$ASM_DISKGROUP views, as shown in this query: select adg.name dg_name, ad.name fg_name, path from v$asm_disk ad right outer join v$ASM_DISKGROUP adg on ad.group_number=adg.group_number where adg.name=’DGROUP1’; DG_NAME FG_NAME PATH -------- --------------- ---------------------------------------DGROUP1 DGROUP1_0000 /oracle01/oradata/asm/disk group1.
Working with the ASM Instance 21 contents clause. The drop diskgroup statement is synchronous in nature, so once the prompt returns, the deed is done … no Recycle Bin here. When the drop diskgroup command is exe- cuted, ASM will unmount the disk from the ASM instance and write over all the ASM-related information on that disk. The ASM_DISKGROUPS parameter will also be changed if you are using an spfile.
Chapter 1 n Using Oracle ASM specific failure group. As a result, each disk will be assigned to its own failure group when it’s created.
Working with the ASM Instance 23 determine if the operation has completed, you will need to review the V$ASM_DISK view.
Chapter 1 n Using Oracle ASM Resizing Disks in an ASM Disk Group Sometimes when more space is needed, all a disk administrator needs to do is add that additional space to the disk devices that are being presented for ASM to use. If this is the case, you will want to indicate to ASM that it needs to update its metadata to represent the correct size of the disks it’s using so you get the benefit of the additional space.
Working with the ASM Instance 25 the rebalance operation has completed. The default is nowait, which will cause the operation to be synchronous in nature. You can check the status of the rebalance operation using the V$ASM_OPERATION view during asynchronous rebalance operations. If you use the wait parameter and you want to convert the operation to an asynchronous operation, you can simply press Ctrl+C on most platforms and an error will be returned along with the SQL prompt.
Chapter 1 n Using Oracle ASM Creating ASM Disk Group Directories When you create an ASM disk group, it includes a system-generated directory structure for the ASM files that will be stored in that disk group. The system-generated directory structure takes on the following format, where disk_group is the root of the directory hierarchy: +disk_group/database_name/object_type/ASM_file_name The database name will be the name of the database that the data is associated with.
Working with the ASM Instance 27 Using the ASMCMD Command-Line Utility The ASMCMD tool is a command-line utility that allows you to manage ASM instances and the disk structures and files within those instances. With ASMCMD, you can do the following: NN List contents of ASM disk groups NN Perform searches (like directory listings) NN Add or remove directories NN Display space availability and utilization ASMCMD allows you to traverse the ASM disks as you would a directory structure.
Chapter 1 n Using Oracle ASM You can start ASMCMD with the –p option and it will display the current directory level, as shown in this example: C:\oracle\product\11.1.0.6\DB01\BIN>asmcmd -p ASMCMD [+] > ASMCMD Commands ASMCMD has a basic set of commands, many of which mimic Unix commands. You can see these commands from the ASMCMD prompt if you type in help. The commands are pretty straightforward and easy to use. In the next section, we will introduce each command and provide an example of its use.
Overview of ASM Data Dictionary Views 29 Ta b l e 1 . 3 ASMCMD Commands (continued) Command Purpose Example md_restore Restores ASM metadata. md_restore –t full –g dgroup1 –i /tmp/dgbackup070222 pwd Locates where you are on the ASM directory tree. pwd remap Remaps a range of physical blocks on disk. remap data data_0003 6000-8000 rm Removes an ASM directory or file. rm Current.258.613087119 rm current* rm -r current* rmalias Removes an ASM alias.
Chapter 1 n Using Oracle ASM Ta b l e 1 . 4 ASMCMD Commands (continued) View Name In ASM Instance In Database V$ASM_DISK This view describes each disk that was discovered by the ASM instance. All disks are reported, even those not assigned to disk groups. This view describes each disk that is assigned to a database. Note that discovery will occur each time you query this view. This can have performance impacts.
Using ASM Storage 31 Using ASM Storage We have discussed management of an ASM instance. This section covers how to actually use ASM from an Oracle instance.
Chapter 1 n Using Oracle ASM ASM Files ASM files are created in a number of different ways; for example, when you execute the create tablespace command and you indicate that the resulting datafile(s) should be stored in an ASM disk group, the result will be the creation of ASM files in that ASM disk group. A goodly number of Oracle file types can be stored in ASM, including datafiles, control files, redo logs, and archived redo logs. There are some Oracle files that cannot be stored in an ASM group.
Using ASM Storage 33 ASM Numeric Filenames The ASM numeric filename is a subset of the fully qualified filename, as you might have noticed. The numeric filename for the fully qualified filename in the preceding section would be +sp_dgroup2.56.
Chapter 1 n Using Oracle ASM Adding ASM Filename Aliases to Existing Files You can add filename aliases to ASM files that have already been created. To add the alias, use the alter diskgroup command with the add alias parameter. For example, if you wanted to create an alias for ALIAS_TBS.260.613168611, you would issue the following command: Alter diskgroup cooked_dgroup1 add alias ‘+cooked_dgroup1/alias_dir/alias_tbs_01.dbf’ FOR ‘+cooked_dgroup1/11GDB/datafile/alias_tbs. 260.
Using ASM Storage 35 default ASM destinations be defining incomplete ASM filenames.
Chapter 1 n Using Oracle ASM Note in this example that Oracle went ahead and filled out the rest of the path, giving us a complete filename in the DBA_DATA_FILES view to work with.
Using ASM Storage 37 Let’s see where Oracle put the datafile now by querying the DBA_DATA_FILES view: select tablespace_name, file_name from dba_data_files Where tablespace_name=’ANOTHER_TEST’; TABLESPACE_NAME FILE_NAME --------------- -----------------------------------------------------------ANOTHER_TEST +COOKED_DGROUP1/11GDB/datafile/another_test.256.
Chapter 1 n Using Oracle ASM After you indicate that you want to use ASM, you will be prompted to create the SYS password to the ASM instance. DBCA will then present to you a list of available disk groups that you can use to create the database. DBCA will confirm your selection in the next screen. You will then be presented with a screen that asks you where you want the flash recovery area to be assigned.
Using ASM Storage 39 NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ‘C:\ORACLE\ORADATA\11GDB\REDO01.LOG’ SIZE 50M, GROUP 2 ‘C:\ORACLE\ORADATA\11GDB\REDO02.LOG’ SIZE 50M, GROUP 3 ‘C:\ORACLE\ORADATA\11GDB\REDO03.LOG’ SIZE 50M -- STANDBY LOGFILE DATAFILE ‘C:\ORACLE\ORADATA\11GDB\SYSTEM01.DBF’, ‘C:\ORACLE\ORADATA\11GDB\UNDOTBS01.DBF’, ‘C:\ORACLE\ORADATA\11GDB\SYSAUX01.DBF’, ‘C:\ORACLE\ORADATA\11GDB\USERS01.
Chapter 1 n Using Oracle ASM RMAN>shutdown RMAN>startup nomount RMAN>restore controlfile from autobackup; RMAN>recover database; RMAN>alter database open resetlogs; Note that instead of setting the DB_CREATE_FILE_DEST parameter, you could set the CONTROL_FILES parameter, as shown here: alter system set control_files= ‘+COOKED_DGROUP1/11GDB/controlfile/current.259.613088323’, ‘+COOKED_DGROUP2/11GDB/controlfile/current.257.
Using ASM Storage 41 Creating Spfiles or Parameter Files on an ASM Disk Group You can create pfiles or spfiles on an ASM disk group using the create pfile or create spfile command with the ASM disk group as the location for the parameter file. For example, you could issue this command: create spfile ‘+COOKED_DGROUP1’ from pfile; Creating Online Redo Logs in an ASM Disk Group Location Creation of redo logs on ASM disks is straightforward.
Chapter 1 n Using Oracle ASM GROUP_NUMBER NAME INCARNATION FILE_NUMBER TYPE ------------ ------------------------------ ----------- ----------- ----------2 COOKED_DGROUP2 613091705 258 ARCHIVELOG Creating Database Objects Using ASM Filename Aliases An alias includes the disk group name and then appends a user-defined name to the filename. This makes it possible to reference an ASM file with a name that makes some sense.
Using RMAN with ASM 43 Using RMAN with ASM You can use RMAN in conjunction with ASM. In the following sections, we will cover the following RMAN-related operations: NN Copying database datafiles to an ASM disk with RMAN NN Creating RMAN backups on ASM Copying Database Datafiles to an ASM Disk with RMAN If you want to move your entire database to ASM, you can easily do this with RMAN. First you make an image copy of the database, copying it to an ASM disk group.
Chapter 1 n Using Oracle ASM Configuring and Backing Up to an ASM Flash Recovery Area The flash recovery area is a directory structure that centralizes Oracle backups in one Oracle-defined backup structure (see Chapter 2 for more on the flash recovery area). You define the flash recovery area by setting the DB_ RECOVERY_FILE_DEST and DB_RECOVERY_ FILE_DEST_SIZE parameters as required. Here is an example of using the alter system command to point the flash recovery area to an ASM disk group.
Exam Essentials 45 Exam Essentials Enumerate the benefits and characteristics of Automatic Storage Management (ASM). Understand how ASM can relieve you of manually optimizing I/O across all files in the tablespace by using ASM disk groups. Show how ASM operations can be performed online with minimal impact to ongoing database transactions. Be able to create an ASM instance and configure its initialization parameters.
Chapter 1 46 n Using Oracle ASM Review Questions 1. What are three benefits of using ASM? (Choose three.) A. Ease of disk administration and maintenance 2. B. Load balancing across physical disks C. Software RAID-1 data redundancy with double or triple mirrors D. Automatic recovery of failed disks What components are present in an ASM instance? (Choose three.) A. SGA 3. B. Database processes C. Database datafiles D. Control files E.
Review Questions 5. 47 What is the default AU size of an ASM disk group? What is the maximum AU size in an ASM disk group? A. 100KB default, 10TB maximum 6. B. 256KB default, 1024MB maximum C. 10MB default, 126PB maximum D. 64KB default, 1EB maximum E. 1MB default, 64MB maximum Which initialization parameter in an ASM instance specifies the disk groups to be automatically mounted at instance startup? A. ASM_DISKMOUNT 7. B. ASM_DISKGROUP C. ASM_DISKSTRING D.
Chapter 1 48 n Using Oracle ASM In trying to determine the cause of the problem, you issue this query: SQL> show parameter asm NAME -----------------------------------asm_allow_only_raw_disks asm_diskgroups asm_diskstring asm_power_limit asm_preferred_read_failure_groups TYPE ----------boolean string string integer string VALUE -------------------------_ FALSE DGROUP1, DGROUP2, DGROUP3 1 What is the cause of the error? A.
Review Questions D. CREATE DISKGROUP dg_alliance1 MAXIMUM REDUNDANCY failgroup diskcontrol1 DISK ‘c:\oracle\asm_disk\_file_disk1’ disk1 failgroup diskcontrol2 DISK ‘c:\oracle\asm_disk\_file_disk2’ disk2 failgroup diskcontrol2 DISK ‘c:\oracle\asm_disk\_file_disk3’ disk3 failgroup diskcontrol2 DISK ‘c:\oracle\asm_disk\_file_disk4’ disk4; 49 NAME file_ NAME file_ NAME file_ NAME file_ E. None of the above 10.
Chapter 1 50 n Using Oracle ASM 12. To reference existing ASM files, you need to use a fully qualified ASM filename. Your development database has a disk group named DG2A, the database name is DEV19, and the ASM file that you want to reference is a datafile for the USERS02 tablespace. Which of the following is a valid ASM filename for this ASM file? A. dev19/+DG2A/datafile/users02.701.2 B. +DG2A/dev19/datafile/users02.701.2 C. +DG2A/dev19/users02/datafile.701.2 D. +DG2A.701.2 E.
Review Questions 51 16. How can you reverse the effects of an ALTER DISKGROUP … DROP DISK command if it has already completed? A. Issue the ALTER DISKGROUP … ADD DISK command. B. Issue the ALTER DISKGROUP … UNDROP DISKS command. C. Issue the ALTER DISKGROUP … DROP DISK CANCEL command. D. Retrieve the disk from the Recycle Bin after the operation completes. 17. Which of the following ALTER DISKGROUP commands does not use V$ASM_OPERATION to record the status of the operation? A. ADD DIRECTORY B.
Chapter 1 52 n Using Oracle ASM 20. As the DBA, you run the following query on your ASM instance. What is the implication of the results of the query? (Choose two.) SQL> select group_number, name, state from v$ASM_DISKGROUP; GROUP_NUMBER -----------0 2 3 NAME -----------------------------DGROUP1 DGROUP2 DGROUP3 STATE ----------DISMOUNTED MOUNTED MOUNTED A. The DGROUP1 disk group was unmounted by another DBA. B.
Answers to Review Questions 53 Answers to Review Questions 1. A, B, C. Option A is correct because ASM makes administration and maintenance of disks much easier. Option B is correct because ASM provides for load balancing across the physical disks for better performance. Answer C is correct because ASM provides RAID-1 redundancy via double or triple mirrors. 2. A, B, E. Option A is correct because the SGA is allocated when the ASM instance is started.
Chapter 1 n Using Oracle ASM 13. C. RBAL coordinates rebalance activity for a disk group in an ASM instance. 14. A. Note that the UNDROP operation will cancel a drop operation in progress but cannot reverse a drop operation that has already completed. For HIGH REDUNDANCY, at least three failure groups must be specified. While you can combine a drop and add operation into one command, the command can reference only one disk group. 15. A.
