Oracle ASM Interview Questions for the experienced - Part 2

 Click Here for ASM Interview Questions - Part 1

10. What is ASM rebalance, and how does it work?

• Expected Answer: ASM rebalance is the process of redistributing data across the disks in a disk group when there is a change in the disk group (e.g., adding or dropping a disk). The rebalance operation ensures that data is evenly spread across the available disks to optimize performance and storage. It occurs automatically when disk group changes are made and can be monitored with the v$asm_operation view.

11. How does ASM handle disk failure?

• Expected Answer: When a disk fails in a redundant disk group (using mirroring), Oracle ASM automatically rebalances the data to the remaining healthy disks. If you are using external redundancy, you may need to rely on external RAID for recovery. ASM detects disk failures via periodic disk checks and logs the failure, making it easy for administrators to take action, such as adding a replacement disk.

12. How do you migrate data from one ASM disk group to another?

• Expected Answer: To migrate data from one ASM disk group to another, you can:
  1. Use the ALTER DISKGROUP command to move data:

     
     ALTER DISKGROUP <source_diskgroup> MOVE <file_name> TO <target_diskgroup>;
     

  2. Use DBMS_FILE_TRANSFER or other tools like RMAN for moving data files between disk groups.
  3. Alternatively, you can use Data Pump for migrating large datasets.

13. How would you recover from a disk failure in Oracle ASM?

• Expected Answer: To recover from a disk failure in ASM:
  1. Identify the failed disk using V$ASM_DISK.
  2. Ensure that the disk group is still operational (in case of mirroring, data is still available on the other disks).
  3. Replace the failed disk physically.
  4. Add the new disk to the ASM disk group using ALTER DISKGROUP ADD DISK.
  5. Oracle ASM will automatically rebalance the data across the disks, ensuring data is mirrored correctly.

14. Explain the role of the ASM instance and the Oracle database instance in an ASM-enabled database.

• Expected Answer: The ASM instance manages the physical storage (disk groups and disks) and provides the storage abstraction for the Oracle database. It operates independently from the Oracle database instance, which connects to the ASM instance for reading/writing data files, control files, and redo logs. The database instance communicates with the ASM instance via Oracle background processes (e.g., DBWR, LGWR).

15. What is the difference between ASM and RAID?

• Expected Answer: ASM is a software-based storage management solution that operates within the Oracle Database ecosystem. While it provides features similar to RAID (redundancy, striping, etc.), it is tightly integrated with Oracle databases and handles file management and storage distribution automatically. RAID, on the other hand, is a hardware or software-based technology used for disk redundancy and performance at the hardware level, but it lacks the database-level integration that ASM offers.

16. Can you configure Oracle RAC (Real Application Clusters) with ASM?

• Expected Answer: Yes, Oracle RAC can be configured with ASM for shared storage across multiple nodes. In RAC, multiple database instances run on different nodes, and ASM provides shared disk storage, which ensures that all instances have access to the same database files stored in ASM disk groups. ASM simplifies the storage configuration for RAC by handling disk management in a cluster environment.

17. What are the ASM parameters you can modify to tune performance?

• Expected Answer: Some key ASM parameters for tuning performance include:
  • ASM_DISK_REPAIR_TIME: Defines the time allowed for disk repairs.
  • ASM_POWER_LIMIT: Controls the amount of CPU resources ASM can use during rebalancing.
  • ASM_DISKGROUP_REPAIR_TIME: Specifies the time allowed for repairing the disk group in case of a failure.

18. How do you monitor ASM performance?

• Expected Answer: You can monitor ASM performance using the following methods:
  • V$ASM views: Use views like V$ASM_DISK, V$ASM_DISKGROUP, and V$ASM_OPERATION to track ASM performance and disk operations.
  • Oracle Enterprise Manager (OEM): OEM provides a graphical interface to monitor ASM performance, including disk group usage, rebalance status, and storage health.

Oracle ASM interview Questions for the experienced- Part 1

 Here’s a list of Oracle ASM (Automatic Storage Management) interview questions designed for experienced candidates. These questions cover a range of advanced topics and scenarios, testing not only technical knowledge but also practical experience with Oracle ASM in real-world environments.

1. What is Oracle ASM, and how does it work?

• Expected Answer: Oracle ASM is a feature in Oracle Database that provides a simple and efficient storage management solution by managing disk groups, volumes, and files. It uses a volume manager and file system to manage data files, redo logs, control files, and backups. ASM abstracts the underlying storage hardware, allowing database administrators to focus on database management rather than disk management.

2. Explain the architecture of Oracle ASM.

• Expected Answer: Oracle ASM consists of the following components:
  • ASM Instance: An instance that provides the interface to ASM storage. It manages ASM disks and disk groups.
  • ASM Disk Group: A collection of disks managed by ASM. Disk groups store database files, control files, and archived logs.
  • ASM Disk: Physical disks or Logical Volume Manager (LVM) devices that are part of an ASM disk group.
  • ASM Metadata: Data stored in the control files of the ASM instance that tracks information about disk groups, file locations, and other configurations.

3. What is the difference between ASM and traditional file systems?

• Expected Answer: Oracle ASM is specifically designed for Oracle databases, providing optimized storage management. Unlike traditional file systems, ASM handles the distribution of data across multiple disks (striping), offers redundancy (mirroring or RAID), and automatically manages storage with minimal administrative effort. Traditional file systems require manual management for partitioning, file storage, and recovery, while ASM abstracts these tasks for database administrators.

4. What are the advantages of using Oracle ASM?

• Expected Answer: The key benefits of Oracle ASM include:
  • Simplified storage management.
  • Automatic file striping across multiple disks for performance.
  • Redundancy options like mirroring to ensure high availability.
  • Scalable storage architecture.
  • Integrated with Oracle database, simplifying backup, recovery, and performance management.
  • No need for third-party volume managers or file systems.
  • High performance for Oracle workloads.

5. How does Oracle ASM provide redundancy?

• Expected Answer: Oracle ASM provides redundancy through two primary methods:
  • Mirroring: Data is duplicated across multiple disks (normal redundancy or high redundancy).
  • RAID-like configurations: ASM uses a technique similar to RAID for striping and mirroring data across multiple disks.
  • Redundancy can be configured at the disk group level to ensure that if one disk fails, data remains accessible from another mirrored disk.

6. What are the different types of redundancy available in ASM?

• Expected Answer: ASM offers three levels of redundancy:
  • Normal Redundancy: Each file in the disk group is mirrored (2-way redundancy). Each file has two copies of the data (primary and mirror).
  • High Redundancy: Each file is mirrored three times (3-way redundancy). It provides higher availability, suitable for critical systems.
  • External Redundancy: No mirroring in ASM. This relies on external RAID devices or hardware to handle redundancy. Useful when external hardware redundancy is already in place.

7. What is the purpose of the ASM Disk Group?

• Expected Answer: A disk group is a collection of disks managed by ASM that stores Oracle database files such as data files, redo logs, and control files. Disk groups provide flexibility in storage management, allowing multiple disks to be treated as a single logical unit for the database. Disk groups can be configured for different levels of redundancy and performance.

8. How can you add a disk to an ASM Disk Group?

• Expected Answer: You can add a disk to an ASM disk group using the following steps:
  1. Ensure the disk is available and properly configured.
  2. Use the ALTER DISKGROUP SQL command to add the disk:

     
     ALTER DISKGROUP <diskgroup_name> ADD DISK '<disk_path>';
     

  3. You can also use asmcmd or Oracle Enterprise Manager (OEM) to add disks to the disk group.

9. How do you check the status of ASM disks and disk groups?

• Expected Answer: You can check the status using the following commands:
  • To check the status of ASM disks:

    
    SELECT * FROM V$ASM_DISK;
    

  • To check the status of disk groups:

    
    SELECT * FROM V$ASM_DISKGROUP;
    

  • Using asmcmd:

    
    asmcmd lsdg
    asmcmd lsdisk
    

How to add space in ACFS filesystem?

The below post explains the step-by-step process to add storage to a mountpoint in ACFS filesystem

1.Before extending the mountpoint storage we need to add an LVM of required Storage Capacity on the server.

For checking the LUN's available on the server, click here

For now, assume the LUN added is /dev/sdf1

2. As root User, check the available disks by running the below command

$ /usr/sbin/oracleasm listdisks
DATA01
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03

3. Create a new disk using the below command

/usr/sbin/oracleasm createdisk DATA02 /dev/sdf1 

Where DATA02 is the name of new disk added
/dev/sdf1 is the LUN added for storage extension.

4. Verify the disk is added by running the below command.

 $/etc/init.d/oracleasm  listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03

5. Navigate to the other cluster nodes and run the scandisks command to reflect the newly added disk on all the nodes of cluster.

$/etc/init.d/oracleasm  scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

 $/etc/init.d/oracleasm  listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03

6. Now login to the database server as GRID user (Where ASM instance is running) to add space to ACFS.

Set the environment as required


export ORACLE_HOME=/u01/app/12.1.0/grid
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM1

$sqlplus / as sysasm

SQL> alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA02';

Diskgroup altered.


7. Increase the space on filesystem

As root user,

$acfsutil size +1G /oradata
acfsutil size: new file system size: 591833071616 (564416MB)

How to Resolve ACFS-03008 : The volume expansion limit has been reached?

Issue: ACFS Filesystem resize operation fails with "ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached."

$acfsutil size +1G /oradata
acfsutil size: ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required version (11.2.0.4.0) for unlimited volume expansions.

Analysis:

Compatible attribute for advm and rdbms is below the required version.

 SQL>  select group_number, name, value from v$asm_attribute where name like 'compatible%' ;

GROUP_NUMBER
------------
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
           1
compatible.asm
12.1.0.0.0

           1
compatible.rdbms
11.2.0.0.0

           1
compatible.advm
11.2.0.0.0

           2
compatible.asm

12.1.0.0.0

           2
compatible.rdbms
10.1.0.0.0




Solution:

SQL> ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.advm'='11.2.0.4.0' ;

Diskgroup altered.

SQL>  ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.rdbms'='11.2.0.4.0' ;

Diskgroup altered.

Now verify the issue is resolved.
$ acfsutil size +10G /oradata
acfsutil size: new file system size: 603644231680 (575680MB)

ACFS-03171: Insufficient contiguous free ASM Diskgroup space

Issue:  

ACFS file resize operation operation fails with ACFS-03171: Insufficient contiguous free ASM Diskgroup space


[root@erptestdb01 ~]# cd /sbin
[root@erptestdb01 sbin]# acfsutil size +100M /oradata
acfsutil size: ACFS-03171: Insufficient contiguous free ASM Diskgroup space.  Check the ASM alert log.

Solution:

Increase size in smaller chunks until the required size is reached
[root@erptestdb01 bin]# acfsutil size +10M /oradata
acfsutil size: new file system size: 590625112064 (563264MB)

Command to check LUN details on a Linux server

Logical Unit Number (LUN) is part of Storage Management in Oracle Automated Storage Management (ASM).

Below command is useful to check the available LUN's on a linux server.

As root user,

Run the command ls -la /dev/disk/by-id/  


Sample Output below:

[root@erptestdb ~]# ls -la /dev/disk/by-id/
total 0
drwxr-xr-x 2 root root 240 Jan 29 16:17 .
drwxr-xr-x 6 root root 120 Jan 29  2018 ..
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdd
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdd1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdf
lrwxrwxrwx 1 root root  10 Jan 29 16:17 scsi-3********************************-part1 -> ../../sdf1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sdc
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdc1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3********************************-> ../../sdb
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sdb1
lrwxrwxrwx 1 root root   9 Jan 29  2018 scsi-3******************************** -> ../../sde
lrwxrwxrwx 1 root root  10 Jan 29 15:40 scsi-3********************************-part1 -> ../../sde1
[root@erptestdb ~]#

Oracle DBA Concepts: Background Processes in Oracle ASM (Automatic storage Management)

The following background processes are an integral part of Automatic Storage Management:

ARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.

ASMB runs in a database instance that is using an ASM disk group. ASMB communicates with the ASM instance, managing storage and providing statistics. ASMB can also run in the ASM instance. ASMB runs in ASM instances when the ASMCMD cp command runs or when the database instance first starts if the SPFILE is stored in ASM.

GMON maintains disk membership in ASM disk groups.

MARK marks ASM allocation units as stale following a missed write to an offline disk. This essentially tracks which extents require resync for offline disks.

RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.

How to change Rebalance Power in ASM Instances?

Whenever a new diskgroup is added or dropped, ASM automatically performs REBALANCING operation.Power used by rebalacing defaults to value specified for asm_power_limit initialization parameter.We can check the status of rebalancing from v$asm_operation view.

Increasing the value of ASM_POWER_LIMIT, reduces the estimated time for completion of Rebalance Operation.

ASM power limit can be increased using the below command

alter system set asm_power_limit =11;

To know more about values for this parameter, check this link:

http://www.appsdbadiaries.com/2017/06/asm-features-rebalancing.html

But this does not increase the rebalancing power of current operation. 

To increase the rebalancing power of ongoing operation, use the below command

 alter diskgroup dg1 rebalance power 11;

We can also specify rebalance power at the time of adding or dropping disks.

alter diskgroup dg1 add disk d01 rebalance power 11;

When to use asm_power_limit 0?

Whenever there is a need to add/drop several disks (like migrations), setting the power limit to 0 is highly beneficial.We can avoid the waiting time for rebalance operation to complete,before the next disk is rebalanced.In such cases, set the asm_power_limit to 0, add/drop all the disks and set the value of asm_power_limit to a non zero value.

Rebalance operation will be parallelized and completes faster.

Oracle Database ASM Features: REBALANCING

ASM has the ability to rebalance data across the disks whenever a disk is added,dropped or replaced.
The performance of rebalance operation is controlled by initialization parameter ASM_POWER_LIMIT.

ASM_POWER_LIMIT Parameter

Default value 1

Range of Values 0 to 11 (Prior to 11gR2)

Range of Values 0 to 1024(From 11gR2)

For 10g Databases:

SQL> alter diskgroup DG1 rebalance power 12;
alter diskgroup DG1 rebalance power 12
                                              *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup DG1 rebalance power 11;

Diskgroup altered.


For 11g AND 12c Databases

SQL> alter diskgroup DG1 rebalance power 1025;
alter diskgroup DG1 rebalance power 1025
                                      *
ERROR at line 1:
ORA-15102: invalid POWER expression

SQL> alter diskgroup DG1 rebalance power 1024;

Diskgroup altered.

The higher the value of rebalancing power, faster would be rebalancing operation.

Initialization Parameters Required for Oracle ASM (Automatic Storage Management)

The below parameters are required for ASM instance.

1. INSTANCE_TYPE 

INSTANCE_TYPE specifies whether the instance is a database instance or an Automatic Storage Management instance.

Values:

RDBMS

The instance is a database instance.

ASM

The instance is an Automatic Storage Management instance.


2. DB_UNIQUE_NAME 

DB_UNIQUE_NAME specifies a globally unique name for the database

Default value
Database instances: the value of DB_NAME
Automatic Storage Management instances: +ASM

3. ASM_POWER_LIMIT 

ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.

Default value 1

Range of Values 0 to 11 (Prior to 11gR2)

Range of Values 0 to 1024(From 11gR2)

4. ASM_DISKGROUPS 

ASM_DISKGROUPS specifies a list of names of disk groups to be mounted by an Automatic Storage Management instance at instance startup or when an ALTER DISKGROUP ALL MOUNT statement is issued.

Automatic Storage Management (ASM) automatically adds a disk group to this parameter when the disk group is successfully created or mounted, and automatically removes a disk group from this parameter when the disk group is dropped or dismounted.

Issuing the ALTER DISKGROUP...ALL MOUNT or ALTER DISKGROUP...ALL DISMOUNT command does not affect the value of this parameter.

5.ASM_DISKSTRING 

ASM_DISKSTRING specifies an operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. When a new disk is added to a disk group, each Automatic Storage Management instance that has the disk group mounted must be able to discover the new disk using the value of ASM_DISKSTRING.

6. ASM_PREFERRED_READ_FAILURE_GROUPS 

ASM_PREFERRED_READ_FAILURE_GROUPS specifies the failure groups that contain preferred read disks. Preferred disks are instance specific. This parameter is only valid in ASM instances.