July 24, 2013

Oracle DBA Interview Questions and Answers - ASM

Oracle ASM Interview Questions and Answers 

What is ASM in Oracle?
Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2. 

ASM offers support for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or filesystems.

ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run.

ASM simplifies administration of Oracle related files by allowing the administrator to reference disk groups
rather than individual disks and files, which are managed by ASM.

The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.


Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system

Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance

Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage

Uses redundancy features available in intelligent storage arrays

The storage system can store all types of database files

Using disk group makes configuration easier, as files are placed into disk groups

ASM provides stripping and mirroring (fine and coarse gain - see below)

ASM and non-ASM oracle files can coexist

Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.

For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.

Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.

ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.

Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.

Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.

What is ASM instance in Oracle?
The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight. 

Characteristics of Oracle ASM instance
--------------------------------------
1. do not have controlfile and datafiles, do not have online redo logs

2. do have init.ora and a passwordfile
3. for connecting remotely, create passwordfile and set following in init.ora
remote_login_passwordfile=exclusive
create a password file:
$ORACLE_HOME/bin/orapwd file=orapw+ASM1 password=yourpw entries=10
4. ASM instance can not be in open status as there are not datafiles. Can be in mount (although
there is no controlfile) and nomount status. When in mount status, database can use the
diskgroup. The mount status actually means mount disk groups.


What are ASM Background Processes in Oracle?
Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances.

There are at least two new background processes added for an ASM instance:

ASM Instance Background Processes:
---------------------------------
ARBx (ASM) Rebalance working process
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.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

RBAL (Re-balancer)
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.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.

Database Instance ASM Background Processes:
------------------------------------------
In the database instances, there are three background process to support ASM, namely:

ASMB, this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.

RBAL, which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.

O00x, a group slave processes, with a numeric sequence starting at 000.

What are the components of components of ASM are disk groups?
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.

Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

What are ASM instance initialization parameters?
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.

DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.

ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.

ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.

ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system

Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance

Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage

Uses redundancy features available in intelligent storage arrays

The storage system can store all types of database files

Using disk group makes configuration easier, as files are placed into disk groups

ASM provides stripping and mirroring (fine and coarse gain - see below)

ASM and non-ASM oracle files can coexist

Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.

For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.

Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.

ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.

Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.

Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.

Why should we use separate ASM home?
ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.

How many ASM instances should one have?

Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.

For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).

How many diskgroups should one have?
Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).

Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).

To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:

CREATE DISKGROUP data    EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;

Here is an example how you can enable automatic file management with such a setup:

ALTER SYSTEM SET db_create_file_dest   = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;

You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.

What is ASM Rebalancing?

The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing.

ALTER DISKGROUP data REBALANCE POWER 11;


What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.

How does this filesystem structure appear?
Oracle ASM diskgroup'sfilesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.

Where are the Oracle ASM files stored?
Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures.

How are the Oracle ASM files stored within the Oracle ASM filesystem structure?
Oralce ASM files are stored within the Oracle ASM filesystem structures as objects that RDBMS instances/Oracle database instance access. RDBMS/Oracle instance treats the Oracle ASM files as standard filesystem files.

What are the Oracle ASM files that are stored within the Oracle ASM file hierarchy?
Files stored in Oracle ASM diskgroup/Oracl ASM filestructures include:
1) Datafile
2) Controlfiles
3) Server Parameter Files(SPFILE)
4) Redo Log files

What happens when you create a file/database file in ASM?What commands do you use to create database files?
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
 
How can you access a databasefile in ASM diskgroup under RDBMS?

Once the ASM file is created in ASM diskgroup, a filename is generated. This file is now visible to the user via the standard RDBMS view V$DATAFILE.
 
What will be the syntax of ASM filenames?

ASM filename syntax is as follows:
+diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation
where,
+diskgroup_name - Name of the diskgroup that contains this file
database_name - Name of the database that contains this file
datafile - Can be one among 20 different ASM file types
tag_name - corresponds to tablespace name for datafiles, groupnumber for redo log files
file_number - file_number in ASM instance is used to correlate filenames in database instance
incarnation_number - It is derived from the timestamp. IT is used to provide uniqueness

What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.

What is the use of an incarnation number in Oracle ASM filename?
Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted

ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts
Machine:        x86_64
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991
System parameters with non-default values:
  large_pool_size          = 12M
  instance_type            = "asm"
  remote_login_passwordfile= "EXCLUSIVE"
  asm_diskgroups           = "FLASH"
  asm_diskgroups           = "DATA"
  asm_power_limit          = 1
  diagnostic_dest          = "/opt/app/oracle"
Or using the asmcmd's spget command which shows the spfile location registered with GnP profile
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.766260991

1 comment:



  1. My name is Leah Brown, I'm a happy woman today? I told myself that any loan lender that could change my life and that of my family after having been scammed separately by these online loan lenders, I will refer to anyone who is looking for loan for them. It gave me and my family happiness, although at first I had a hard time trusting him because of my experiences with past loan lenders, I needed a loan of $300,000.00 to start my life everywhere as single mother with 2 children, I met this honest and God fearing online loan lender Gain Credit Loan who helped me with a $300,000.00 loan, working with a loan company Good reputation. If you are in need of a loan and you are 100% sure of paying the loan please contact (gaincreditloan1@gmail.com)

    ReplyDelete