Interview Q and A for Oracle ASM Part - 2

31. Where do I run my database listener from; i.e., ASM HOME or DB HOME?

It is recommended to run the listener from the ASM HOME. This is particularly important for RAC env, since the listener is a node-level resource. In this config, you can create additional [user] listeners from the database homes as needed.

32. How do I backup my ASM instance?

Not applicable! ASM has no files to backup, as its does not contain controlfile,redo logs etc.

33. When should I use RMAN and when should I use ASMCMD copy?

RMAN is the recommended and most complete and flexible method to backup and transport database files in ASM.
ASMCMD copy is good for copying single files
• Supports all Oracle file types
• Can be used to instantiate a Data Guard environment
• Does not update the controlfile
• Does not create OMF files

34. I’m going to do add disks to my ASM diskgroup, how long will this rebalance take?

Rebalance time is heavily driven by the three items:
1) Amount of data currently in the diskgroup
2) IO bandwidth available on the server
3) ASM_POWER_LIMIT or Rebalance Power Level

35. We are migrating to a new storage array. How do I move my ASM database from storage A to storage B?

Given that the new and old storage are both visible to ASM, simply add the new disks to the ASM disk group and drop the old disks. ASM rebalance will migrate data online.

Note 428681.1 covers how to move OCR/Voting disks to the new storage array

ASM_SQL> alter diskgroup DATA
drop diskdata_legacy1, data_legacy2,data_legacy3
add disk‘/dev/sddb1’, ‘/dev/sddc1’,‘/dev/sddd1’;

36. Is it possible to unplug an ASM disk group from one platform and plug into a server on another platform (for example, from Solaris to Linux)?

No. Cross-platform disk group migration not supported. To move datafiles between endian-ness platforms, you need to use XTTS, Datapump or Streams.

37. How does ASM work with multipathing software?

It works great! Multipathing software is at a layer lower than ASM, and thus is transparent.
You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.

Multipathing tools provides the following benefits:

--Provide a single block device interface for a multi-pathed LUN
--Detect any component failures in the I/O path; e.g., fabric port, channel adapter, or HBA.
--When a loss of path occurs, ensure that I/Os are re-routed to the available paths, with no process disruption.
--Reconfigure the multipaths automatically when events occur.
--Ensure that failed paths get revalidated as soon as possible and provide autofailback capabilities.
--Configure the multi-paths to maximize performance using various load balancing methods; e.g., round robin, least I/Os queued, or least service time.

38. Is ASM constantly rebalancing to manage “hot spots”?

No…No…Nope!! ASM provides even distribution of extents across all disks in a disk group. Since each disk will equal number of extents, no single disk will be hotter than another. Thus, the answer NO, ASM does not dynamically move hot spots, because hot spots simply do not occur in ASM configurations. Rebalance only occurs on storage configuration changes (e.g. add, drop, or resize disks).

39. What are the file types that ASM support and keep in disk groups?

Control files
Flashback logs
Data Pump dump sets

Data files
DB SPFILE
Data Guard configuration

Temporary data files
RMAN backup sets
Change tracking bitmaps

Online redo logs
RMAN data file copies
OCR files

Archive logs
Transport data files
ASM SPFILE

40. How many ASM Diskgroups can be created under one ASM Instance?

ASM imposes the following limits:

• 63 disk groups in a storage system
• 10,000 ASM disks in a storage system
• Two-terabyte maximum storage for each ASM disk (non-Exadata)
• Four-petabyte maximum storage for each ASM disk (Exadata)
• 40-exabyte maximum storage for each storage system
• 1 million files for each disk group
• ASM file size limits (database limit is 128 TB):

1. External redundancy maximum file size is 140 PB.
2. Normal redundancy maximum file size is 42 PB.
3. High redundancy maximum file size is 15 PB.

41.  What is a diskgroup?

A disk group consists of multiple disks and is the fundamental object that ASM manages. Each disk group contains the metadata that is required for the management of space in the disk group. The ASM instance manages the metadata about the files in a Disk Group in the same way that a file system manages metadata about its files. However, the vast majority of I/O operations do not pass through the ASM instance. In a moment, we will look at how file I/O works with respect to the ASM instance.

42. What are Failuregroups?

Failuregroups are used when using Normal/High Redundancy. They contain the mirrored ASM extents and must be containing different disks and preferably on separate disk controller.

43. Diagram that how database interacts with ASM when a request is to read or open a datafile.

1A. Database issues open of a database file
1B. ASM sends the extent map for the file to database instance. Starting with 11g, the RDBMS only receives first 60 extents the remaining extents in the extent map are paged in on demand, providing a faster open
2A/2B. Database now reads directly from disk
3A.RDBMS foreground initiates a create tablespace for example
3B. ASM does the allocation for its essentially reserving the allocation units for the file creation
3C. Once allocation phase is done, the extent map is sent to the RDBMS
3D. The RDBMS initialization phase kicks in. In this phase the initializes all the reserved AUs
3E. If file creation is successful, then the RDBMS commits the file creation Going forward all I/Os are done by the RDBMS directly.

44. What happens when you create a file/database file in ASM? What commands do you use to create database files?

A.RDBMS foreground initiates a create tablespace for example
B. ASM does the allocation for its essentially reserving the allocation units for the file creation
C. Once allocation phase is done, the extent map is sent to the RDBMS
D. The RDBMS initialization phase kicks in. In this phase the initializes all the reserved AUs
E. If file creation is successful, then the RDBMS commits the file creation Going forward all I/Os are done by the RDBMS directly.

Some common commands used for creating database files are :
1) Create tabespace
2) Add Datafile
3) Add Logfile
For example,
SQL> CREATE TABLESPACE TS1 DATAFILE '+DATA1' SIZE 10GB;
Above command creates a datafile in DATA1 diskgroup

45. Can my disks in a diskgroup can be varied size? For example one disk is of 100GB and another disk is of 50GB. If so how does ASM manage the extents?

Yes, disk sizes can be varied, Oracle ASM will manage data efficiently and intelligent by placing the extents proportional to the size of the disk in the disk group, bigger diskgroups have more extents than lesser ones.

46. ASM disk header/superblock backups?

ASM disk headers (superblocks) cannot be backed up and restored in Oracle 10g. By implication, if you use EXTERNAL REDUNDANCY and a single disk's header is accidentally overwritten, the entire disk group will have to be restored.

To solve this problem, Oracle introduced the md_backup and md_restore asmcmd commands in Oracle 11g. In Oracle 10g, the only viable method to prevent logical corruption of ASM header block is to add failgroup, storage vendor has no responsibility to verify/checksum ASM disk header blocks (EXTERNAL REDUNDANCY is not going to help). There is a kfed utility to backup ASM disk headers and restore them for LIMITED scenario. It is best to be executed under guidance of a few elite support engineers. Oracle did not advertise the utility due to the potential damage it could cause. For those unrecoverable (tedious manual fixes) cases, restoring disk group is the last resort.

47. ASMCMD is very slow. How can I speed it up?

The asmcmd utility appears to be very slow. This slowness is a result of queries against the v$asm_diskgroup view. To solve this problem, edit the $ORACLE_HOME/bin/asmcmdcore script and change all v$asm_diskgroup references to v$asm_diskgroup_stat.

V$asm_diskgroup and v$asm_diskgroup_stat provides exactly the same information, but the %_stat view operates from cache, while v$asm_diskgroup rescans all disk headers. This method is also used by Oracle in their Enterprise Manager product.

48. What kind of files are not directly supported on ASM

Oracle ASM cannot directly support some administrative files on disk groups. These include trace files, audit files, alert logs, export files, tar files, and core files.

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) and Oracle ASM Dynamic Volume Manager (Oracle ADVM) extend Oracle ASM support to include these files too.

49. How do you add/rename/drop an Alias Name for an Oracle ASM Filename?

Use the ADD ALIAS, RENAME ALIAS, DROP ALIAS clause of the ALTER DISKGROUP statement to create an alias name for an Oracle ASM filename. The alias name must consist of the full directory path and the alias itself

ALTER DISKGROUP data ADD ALIAS ‘+data/orcl/second.dbf’ FOR ‘+data/orcl/datafile/mytable.342.123456789′;

ALTER DISKGROUP data RENAME ALIAS ‘+data/orcl/datafile.dbf’ TO ‘+data/payroll/compensation.dbf’;

ALTER DISKGROUP data DROP ALIAS ‘+data/payroll/compensation.dbf’;

50. Should I create table and index tablespaces in separate diskgroups?

No, tables and indexes can be stored within a single disk group. Do not create different disk groups for tables and indexes.

51. How to check how much disk space is allocated/ in-use by an ASM instance?

Login to your +ASM instance (SYS AS SYSDBA) and execute the following query:

SQL> COL % FORMAT 99.0

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;

NAME                              FREE_MB   TOTAL_MB     %

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

DATA                               917104    1482145  61.9

RECOVER                             17387      17437  99.7

From Oracle 10g Release 2, one can also use the asmcmd command line utility:

ASMCMD> du

Used_MBMirror_used_MB

   1523                1523

ASMCMD>lsdg

State    Type    RebalUnbal  Sector  Block       AU  Total_MBFree_MBReq_mir_free_MBUsable_file_MBOffline_disks  Name

MOUNTED  EXTERN  N      N         512   4096  1048576     11264     9885                0            9885              0  DISKGROUP1/

MOUNTED  EXTERN  N      N         512   4096  1048576     10240     9906                0            9906              0  FLASH/

52. What can be the various header status that an ASM disk can assume?

MEMBER : Disks that belong to a disk group, that is, disks that have a disk group name in the disk header, show a header status of MEMBER.

CANDIDATE :Disks that were discovered, but that have not yet been assigned to a disk group, have a status of CANDIDATE

PROVISIONED :PROVISIONED status implies that an additional platform-specific action has been taken by an administrator to make the disk available for Oracle ASM. These disks are discovered by ASM

FORMER :Disks that previously belonged to a disk group and were dropped cleanly from the disk group have a status of FORMER.

FOREIGN :When adding a disk, the FORCE option must be used if Oracle ASM recognizes that the disk was managed by Oracle. Such a disk appears in the V$ASM_DISK view with a status of FOREIGN. In this case, you can only add the disk to a disk group by using the FORCE keyword.

53. Does Oracle ASM perform multiple rebalance operations on different disk groups in serial or parallel?

Oracle ASM can perform one disk group rebalance at a time on a given instance. If you have initiated multiple rebalances on different disk groups on a single node, then Oracle processes these operations in parallel on additional nodes if available; otherwise the rebalances are performed serially on the single node. You can explicitly initiate rebalances on different disk groups on different nodes in parallel.

54. What will happen when a disk is dropped from a disk group?

 ALTER DISKGROUP data1 DROP DISK diska5;

When a disk is dropped, the disk group is rebalanced by moving all of the file extents from the dropped disk to other disks in the disk group. A drop disk operation might fail if not enough space is available on the other disks.

IMPORTANT : The ALTER DISKGROUP…DROP DISK SQL statement returns to SQL prompt before the drop and rebalance operations are complete. Do not reuse, remove, or disconnect the dropped disk until the HEADER_STATUS column for this disk in the V$ASM_DISK view changes to FORMER.

You can query the V$ASM_OPERATION view to determine the amount of time remaining for the drop/rebalance operation to complete.

55. What are allocation units (AU)?  What is the allocation units (AU) size you should keep while creating diskgroups?

Every Oracle ASM disk is divided into allocation units (AU). An allocation unit is the fundamental unit of allocation within a disk group. When you create a disk group, you can set the Oracle ASM allocation unit size with the AU_SIZE disk group attribute. The values can be 1, 2, 4, 8, 16, 32, or 64 MB, depending on the specific disk group compatibility level. Larger AU sizes typically provide performance advantages for data warehouse applications that use large sequential reads.

Oracle recommends that the allocation unit (AU) size for a disk group be set to 4 megabytes (MB).

Benefits of 4 mb AU are

• Increased I/O through the I/O subsystem if the I/O size is increased to the AU size.
• Reduced SGA size to manage the extent maps in the database instance.
• Faster datafile initialization if the I/O size is increased to the AU size.
• Increased file size limits.
• Reduced database open time.

 Although for keeping OCR and voting disks file in different diskgroup in case of RAC, you can choose 1 mb AU because there is low I/O and small files on that diskgroup.

CREATE DISKGROUP disk_group_2 EXTERNAL REDUNDANCY DISK '/dev/sde1' ATRRIBUTE 'au_size' = '32M';

56. How can we create a new disk group? Give example.

Disk groups can be created by ‘Create Diskgroup’ SQL command or with help of Oracle Enterprise Manager. For Example, to create diskgroups with external redundancy :

sqlplus / as sysasm

creatediskgroup data external redundancy DISK ‘ORCL:DISK2′ name DISK2, ‘ORCL:DISK3′ name DISK3, ‘ORCL:DISK4′ name DISK4;

creatediskgroup arch external redundancy DISK ‘ORCL:DISK5′ name DISK5;

 Now if you are using RAC and if you check in asm view from the node where the ‘create diskgroup command’ was run

SQL> select name,state,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME          STATE         TOTAL_MB        FREE_MB
—————————— ———– ———- ———-
OCRVOTE       MOUNTED       10236            9840
DATA          MOUNTED       30708            30654
ARCH          MOUNTED       10236            10186

 But from other RAC nodes you will find that diskgroups are visible but not mounted. You need to manually mount the diskgroups from other node

SQL> select name,state,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME                STATE          TOTAL_MB         FREE_MB
—————————— ———– ———- ———-
OCRVOTE            MOUNTED          10236             9840
DATA               DISMOUNTED       0                  0
ARCH               DISMOUNTED       0                  0

SQL> alter diskgroup DATA mount;

SQL> alter diskgroup ARCH mount;

SQL> select name,state,TOTAL_MB,FREE_MB from v$asm_diskgroup;

NAME            STATE           TOTAL_MB            FREE_MB
—————————— ———– ———- ———-
OCRVOTE         MOUNTED          10236               9840
DATA            MOUNTED          30708               30611
ARCH            MOUNTED          10236               10143

57. When is it advisable to use ‘FORCE’ option when adding a disk to a diskgroup?

You must use FORCE only when adding a disk that was dropped with FORCE. If a disk is dropped with NOFORCE, then you can add it with NOFORCE. For example, a disk might have failed and was dropped from its disk group. After the disk is repaired, it is no longer part of any disk group, but Oracle ASM still recognizes that the disk had been a member of a disk group. You must use the FORCE flag to include the disk in a new disk group. In addition, the disk must be addressable, and the original disk group must not be mounted. Otherwise, the operation fails.

58. Can the redundancy level of the disk group be changed once it is created?

After a disk group is created, you cannot alter the redundancy level of the disk group. To change the redundancy level, you must create another disk group with the appropriate redundancy and then move the files to the new disk group.

59. What is the difference in connecting to ASM as SYSDBA, SYSASM and SYSOPER?

The SYSDBA privilege cannot be used to administer an Oracle ASM instance. If you use the SYSDBA privilege to run administrative commands on an Oracle ASM instance, the operation results in an error. The SYSDBA privilege is intended to be used by the database to access disk groups. The SYSDBA has a subset of the privileges of the SYSASM. Connecting as SYSDBA to the database instance has a limited set of Oracle ASM privileges. For example, you cannot create a disk group when connected with the SYSDBA privilege.

Connecting to an Oracle ASM instance as SYSASM grants you full access to all of the available Oracle ASM disk groups and management functions.

SYSOPER privilege on the Oracle ASM instance provides operations such as startup, shutdown, mount, dismount, and check disk group. This group has a subset of the privileges of the SYSASM group

60. What are various shutdown mode for stopping ASM?

NORMAL Clause : Oracle ASM waits for all of the currently connected users to disconnect from the instance. If any database instances are connected to the Oracle ASM instance, then the SHUTDOWN command returns an error and leaves the Oracle ASM instance running. NORMAL is the default shutdown mode.

IMMEDIATE or TRANSACTIONAL Clause : Oracle ASM does not wait for users currently connected to the instance to disconnect BUT waits for any in-progress SQL to complete before performing an orderly dismount of all of the disk groups and shutting down the Oracle ASM instance. Because the Oracle ASM instance does not contain any transactions, the TRANSACTIONAL mode behaves the same as IMMEDIATE mode.

ABORT Clause : immediately shuts down without the orderly dismount of disk groups. This causes recovery to occur upon the next Oracle ASM startup. If any database instance is connected to the Oracle ASM instance, then the database instance aborts.