Oracle Interview Question and Answer

1.     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 extension 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.

In Oracle Database 10g/11g there are two types of instances: database and ASM instances. The ASM instance, which is generally named +ASM, is started with the INSTANCE_TYPE=ASM init.ora parameter. This parameter, when set, signals the Oracle initialization routine to start an ASM instance and not a standard database instance. Unlike the standard database instance, the ASM instance contains no physical files; such as logfiles, controlfiles or datafiles, and only requires a few init.ora parameters for startup.

Upon startup, an ASM instance will spawn all the basic background processes, plus some new ones that are specific to the operation of ASM. The STARTUP clauses for ASM instances are similar to those for database instances. For example, RESTRICT prevents database instances from connecting to this ASM instance. NOMOUNT starts up an ASM instance without mounting any disk group. MOUNT option simply mounts all defined disk groups

For RAC configurations, the ASM SID is +ASMx instance, where x represents the instance number.

2. Advantages of ASM in Oracle? OR List Key benefits of ASM?

The following are some key benefits of ASM:

• ASM spreads I/O evenly across all available disk drives to prevent hot spots and maximize performance.
• ASM eliminates the need for over provisioning and maximizes storage resource utilization facilitating database consolidation.
• Inherent large file support.
• Performs automatic online redistribution after the incremental addition or removal of storage capacity.
• Maintains redundant copies of data to provide high availability, or leverages 3rd party RAID functionality.
• Supports Oracle Database as well as Oracle Real Application Clusters (RAC).
• Capable of leveraging 3rd party multipathing technologies.
• For simplicity and easier migration to ASM, an Oracle database can contain ASM and non-ASM files.
• Any new files can be created as ASM files whilst existing files can also be migrated to ASM.
• RMAN commands enable non-ASM managed files to be relocated to an ASM disk group.
• Enterprise Manager Database Control or Grid Control can be used to manage ASM disk and file activities.
• Stripes files rather than logical volumes
• Provides redundancy on a file basis
• Enables online disk reconfiguration and dynamic rebalancing
• Reduces the time significantly to resynchronize a transient failure by tracking changes while disk is offline
• Provides adjustable rebalancing speed
• Is cluster-aware
• Supports reading from mirrored copy instead of primary copy for extended clusters
• Is automatically installed as part of the Grid Infrastructure

3. What is ASM Striping?

ASM can use variable size data extents to support larger files, reduce memory requirements, and improve performance. Each data extent resides on an individual disk. Data extents consist of one or more allocation units. The data extent size is:

• Equal to AU for the first 20,000 extents (0–19999)
• Equal to 4 × AU for the next 20,000 extents (20000–39999)
• Equal to 16 × AU for extents above 40,000

ASM stripes files using extents with a coarse method for load balancing or a fine method to reduce latency.

• Coarse-grained striping is always equal to the effective AU size.
• Fine-grained striping is always equal to 128 KB.

4. 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 cannot be in open status as there are not data files. Can be in mount (although
there is no controlfile) and nomount status. When in mount status, database can use the
diskgroup. The mount status means mount disk groups.

5.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. 

Process	Description
RBAL	Opens all device files as part of discovery and coordinates the rebalance activity
ARBn	One or more slave processes that do the rebalance activity
GMON	Responsible for managing the disk-level activities such as drop or offline and advancing the ASM disk group compatibility
MARK	Marks ASM allocation units as stale when needed
Onnn	One or more ASM slave processes forming a pool of connections to the ASM instance for exchanging messages
PZ9n	One or more parallel slave processes used in fetching data on clustered ASM installation from GV$ views

6. 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.

7. 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. A value of 0 disables rebalancing. Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead and more rebalancing processes.

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. Oracle ASM  automatically removes a disk group from this parameter when the disk group is dropped or dismounted.

                                                      ASM_DISKGROUPS = DATA, FRA

The following is an example of setting the ASM_DISKGROUPS parameter dynamically:
                                                      SQL> ALTER SYSTEM SET ASM_DISKGROUPS = DATA, FRA;
ASM_DISKSTRING – specifies a comma-delimited list of strings that limits the set of disks that an Oracle ASM instance discovers. Pattern matching is supported.

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.
            For example, on a Linux server that does not use ASMLib, to limit the discovery process to only include disks that are in the /dev/rdsk/mydisks directory, set the ASM_DISKSTRING initialization parameter to:            /dev/rdsk/mydisks/*

ASM_PREFERRED_READ_FAILURE_GROUPS- a comma-delimited list of strings that specifies the failure groups that should be preferentially read by the given instance. 

DB_CACHE_SIZE- The setting for the DB_CACHE_SIZE parameter determines the size of the buffer cache. You do not have to set a value for the DB_CACHE_SIZE initialization parameter if you use automatic memory management. 

DIAGNOSTIC_DEST-specifies the directory where diagnostics for an instance are located. The default value for an Oracle ASM instance is the $ORACLE_BASE directory for the Oracle Grid Infrastructure installation. 

LARGE_POOL_SIZE-You do not have to set a value for the LARGE_POOL_SIZE initialization parameter if you use automatic memory management. The setting for the LARGE_POOL_SIZE parameter is used for large allocations. 

SHARED_POOL_SIZE- You do not have to set a value for the SHARED_POOL_SIZE initialization parameter if you use automatic memory management. The setting for the SHARED_POOL_SIZE parameter determines the amount of memory required to manage the instance. 

REMOTE_LOGIN_PASSWORDFILE- specifies whether the Oracle ASM instance checks for a password file. This parameter operates the same for Oracle ASM and database instances

8. 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.

9. 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).

10. 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. 

11.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;

12. What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchical filesystem structure is created.

13. How does this filesystem structure appear?
Oracle ASM diskgroup’s filesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.

14. 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.

15. How are the Oracle ASM files stored within the Oracle ASM filesystem structure?
Oracle 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.

16. How can you access a database file 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.

17. 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

18. 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 does not change.

19. 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.

20. Where ASM instance spfile reside and from where get details about for spfile?

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

21.  How does database connects to ASM Instance?

The database communicates with ASM instance using the ASMB (umblicus process) process. Once the database obtains the necessary extents from extent map, all database IO going  forward is processed through by the database processes, bypassing ASM. Thus we say ASM is not really in the IO path. So, the question how do we make ASM go faster…..you don’t have to.

22. How does the database interact with the ASM instance and how do I make ASM go faster?

ASM is not in the I/O path so ASM does not impede the database file access. Since the RDBMS instance is performing raw I/O, the I/O is as fast as possible.

23. Do I need to define the RDBMS FILESYSTEMIO_OPTIONS parameter when I use ASM?

No. The RDBMS does I/O directly to the raw disk devices, the FILESYSTEMIO_OPTIONS  parameter is only for filesystems.

24. Why Oracle recommends two diskgroups?

Oracle recommends two diskgroups to provide a balance of manageability, utilization, and performance.

25. We have a 16 TB database. I’m curious about the number of disk groups we should use; e.g. 1 large disk group, a couple of disk groups, or otherwise?

For VLDBs you will probably end up with different storage tiers; e.g with some of our large customers they have Tier1 (RAID10 FC), Tier2 (RAID5 FC), Tier3 (SATA), etc. Each one of these is mapped to a diskgroup.

26. We have a new app and don’t know our access pattern, but assuming mostly sequential access, what size would be a good AU fit?

For 11g ASM/RDBMS it is recommended to use 4MB ASM AU for disk groups.

27. Would it be better to use BIGFILE tablespaces, or standard tablespaces for ASM?

The use of Bigfiletablespaces has no bearing on ASM (or vice versa). In fact most database object related decisions are transparent to ASM.

28. What is the best LUN size for ASM?

There is no best size! In most cases the storage team will dictate to you based on their standardized LUN size. The ASM administrator merely has to communicate the ASM Best Practices and application  characteristics to storage folks :
• Need equally sized / performance LUNs
• Minimum of 4 LUNs
• The capacity requirement
• The workload characteristic (random r/w, sequential r/w) & any response time SLA
Using this info , and their standards, the storage folks should build a nice LUN group set for you.

29. 11g RAC we want to separate ASM admins from DBAs and create different users and groups. How do we set this up?

For clarification
• Separate Oracle Home for ASM and RDBMS.
• RDBMS instance connects to ASM using OSDBA group of the ASM instance.
Thus, software owner for each RDBMS instance connecting to ASM must be a member of ASM’s OSDBA group.
• Choose a different OSDBA group for ASM instance (asmdba) than for RDBMS instance (dba)
• In 11g, ASM administrator has to be member of a separate SYSASM group to separate ASM Admin and DBAs.

30.  Can my RDBMS and ASM instances run different versions?

Yes. ASM can be at a higher version or at lower version than its client databases. There’s two
components of compatiblity:
Software compatibility
Diskgroup compatibility attributes:
compatible.asm
compatible.rdbms

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.

61. How do you stop an Oracle ASM Instance?

Ensure that the ORACLE_SID environment variable is set to the Oracle ASM SID and run SHUTDOWN command in SQL*Plus.

IMPORTANT : If Oracle Cluster Registry (OCR) or voting files are stored in a disk group, the disk group can only be dismounted by shutting down the Oracle ASM instance as part of shutting down the clusterware on a node. To shut down the clusterware, run crsctl stop crs.

62. What will happen if the version of ASM is different than database ? (take both scenarios : higher and lower)

Oracle Automatic Storage Management (Oracle ASM) in Oracle Database 11g Release 2 (11.2) supports 11g Release 2 (11.2) or older software versions of Oracle database instances, including Oracle Database 10g. BUT it is not true vice versa ..

An Oracle ASM instance must be at 11g Release 2 (11.2) to support an 11g Release 2 (11.2) Oracle Database.

Also , When using different software versions, the database instance supports Oracle ASM functionality of the earliest release in use. For example, a 10.1 database instance operating with an 11.2 Oracle ASM instance supports only Oracle ASM 10.1 features.

63. How many Oracle ASM instance are supported on a server?

Only one Oracle ASM instance is supported on a server.

64. Can an ASM file be contained in more than one disk groups ?

No, Any Oracle ASM file is completely contained within a single disk group. However, a disk group might contain files belonging to several databases and a single database can use files from multiple disk groups.

65. What are various ASM redundancy levels?

The redundancy level controls how many disk failures are tolerated without dismounting the disk group or losing data. When you create a disk group, you specify an Oracle ASM disk group type based on one of the following three redundancy levels:

1) Normal for 2-way mirroring
2) High for 3-way mirroring
3) External to not use Oracle ASM mirroring, such as when you configure hardware RAID for redundancy

66. Why it is advised that different Oracle ASM disks should not share the same physical drive?

Oracle ASM spreads the files proportionally across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because Oracle ASM load balances among all of the disks in a disk group, different Oracle ASM disks should not share the same physical drive.

67. What are ‘Extents’ in context of Asm?

The contents of Oracle ASM files are stored in a disk group as a set, or collection, of extents  that are stored on individual disks within disk groups.

Each extent resides on an individual disk. Extents consist of one or more allocation units (AU). To accommodate increasingly larger files, Oracle ASM uses variable size extents. The initial extent size equals the disk group allocation unit size and it increases by a factor of 4 or 16 at predefined thresholds. ( for a file extent is same as AU till 20000 extent ; 4*AU size for the next 20000 extent sets (20000 – 39999); 16*AU size for the next 20000 and higher extent sets (40000+))

68. What are general recommendations for ASM Storage Preparation?

Following are the basic guidelines for preparing storage for use with Oracle ASM:

·        Configure two disk groups, one for data and the other for the fast recovery area.

• A minimum of four LUNs (Oracle ASM disks) of equal size and performance is recommended for each disk group.
• Ensure that all Oracle ASM disks in a disk group have similar storage performance and availability characteristics.
• Ensure that Oracle ASM disks in a disk group have the same capacity to maintain balance as Oracle ASM data distribution policy is capacity-based.
• Create external redundancy disk groups when using high-end storage arrays.
• For Linux, use the Oracle ASMLib feature to provide consistent device naming and permission persistency.
• Choose a hardware RAID stripe size that is a power of 2 and less than or equal to the size of the Oracle ASM allocation unit.

69. How do you Start Up an Oracle ASM Instance?

Starting an Oracle ASM instance is similar to the way in which you start an Oracle database instance

• set the ORACLE_SID environment variable to the Oracle ASM system identifier (SID). (for a single-instance database is +ASM, and the default SID for Oracle ASM for an Oracle RAC node is +ASMnode_number where node_number is the number of the node.)
• initialization parameter file must contain the entry: INSTANCE_TYPE = ASM
• remember that ASM instance doesn’t mount a database but mount Oracle ASM disk groups.
• The associated Oracle database instance does not have to be running when you start the associated Oracle ASM instance
• STARTUP FORCE will issue a SHUTDOWN ABORT to the Oracle ASM instance before restarting it.
• STARTUP MOUNT or OPEN : Mounts the disk groups specified in the ASM_DISKGROUPS initialization parameter. This is the default if no command parameter is specified.
• STARTUP NOMOUNT : Starts up the Oracle ASM instance without mounting any disk groups.
• STARTUP RESTRICT : Starts up an instance in restricted mode that enables access only to users with both the CREATE SESSION and RESTRICTED SESSION system privileges. You can use the RESTRICT clause in combination with the MOUNT, NOMOUNT, and OPEN clauses.In restricted mode, database instances cannot use the disk groups.
• STARTUP PFILE=<pfile_path> can also be used if you want to start with pfile instead of spfile

70. How to find out the databases, which are using the ASM instance?
ASMCMD>lsct
SQL> select DB_NAME from V$ASM_CLIENT;

71. What is Oracle Restart?

Oracle Restart improves the availability of Oracle database. Oracle Restart runs out of the Oracle Grid Infrastructure home. Oracle Restart provides managed startup and restart of a single-instance (non-clustered) Oracle Database, Oracle ASM instance, service, listener, and any other process running on the server. If an interruption of a service occurs after a hardware or software failure, Oracle Restart automatically takes the necessary steps to restart the component.

You need to add a component, such as an Oracle ASM instance, to Oracle Restart and enable the protection for using this feature.

72. What is Kfed?

kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is

         kfed read devicename

73.  Can we use BCV to clone the ASM Diskgroup on same host?

Diskgroup Cloning is not supported on the same host using BCV. You have no other option to use except RMAN DUPLICATE. You can find more detail on BCV and ASM in below whitepaper

http://www.oracle.com/technology/products/database/asm/pdf/asm-on-emc-5_3.pdf

74. How can we copy the files from/to ASM?

You can use RMAN or DBMS_FILE_TRANSFER.COPY_FILE procedure to copy the files to/from ASM from/to Filesystem. Starting from Oracle 11g, you can use cp command in asmcmd to perform the same between ASM Diskgroups and also to OS Filesystem.

75. Can we edit the ASM Disk header to change the Diskgroup Name?

No. This cannot be done.

76. What is ASMLIB?

ASMLIB is the support library for the ASM. ASMLIB allows an Oracle database using ASM more efficient and capable access to diskgroups. The purpose of ASMLIB, is to provide an alternative interface to identify and access block devices. Additionally, the ASMLIB API enables storage and operating system vendors to supply extended storage-related features. You can read more about ASMLIB in

Note: 359266.1 – FAQ ASMLIB CONFIGURE, VERIFY, TROUBLESHOOT

ASMLIB has following benefits:
• Simplified disk discovery
• Persistent disk names
• Efficient use of system resources
o Reduced Overhead

77. I am unable to open the ASM instance. What is the reason?

ASM instance does not have open stage. It has got only two options

• Nomount- This starts the ASM instance
• Mount- At this stage, Diskgroup defined in ASM_DISKGROUPS parameter are mounted

When you try to open the ASM instance , you get error

<span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>SQL&gt; alter database open;
alter database open
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type</span>

78. Can ASM be used as replacement for RAID?

ASM is supposed to stripe the data and also mirror the data (if Using Normal, High Redundancy). So this can be used as a alternative for RAID 0+1 solutions

79. Is it possible to do rolling upgrades on ASMLIB in a RAC configuration
ASMLIB is independent of Oracle Clusterware and Oracle Database, and thus can be upgraded on its own Upgrading ASMLIB one a given node will require that ASMLIB be disabled/stop, will require the database and ASM to also be shutdown on that node. Once the ASMLIB is upgarded then the stack can be restarted.

80. Can DBMS_FILE_TRANSFER be used to upload text files into ASM ?

This is not possible because DBMS_FILE_TRANSFER can be used for Archive logs, RMAN backups, datafile copies, spfiles, DataGuard configuration files, change tracking file, flashback logs, cross platform transportable tablespaces, and datapump files.

Oracle Binaries, trace files and other OS files cannot be stored on ASM diskgroups.

81. Can External Tables Be Placed inside ASM diskgroups?  

ASM cannot store external tables in ASM diskgroups since external tables are flat ASCII files which are not a file type supported in ASM, therefore external tables must be allocated on regular filesystem files.

82. Can you create a second voting disk in a different ASM diskgroup when using External Redundancy in 11.2?

You cannot create more than 1 voting disk in the same or on another/different diskgroup disk when using External Redundancy in 11.2. The rules are as follows:

External = 1 voting disk

Normal= 3 voting disk

High= 5 voting disk

83. Is it possible to create disk at single-path device, then reconfigure disc-array to multipath and still use this asm-disk?

yes. ASM will scan the disks based on the parameter asm_diskstring and the permissions. When the disk is scanned and the data is found, it does not matter the path or the major,minor numbers.

ASM does not change anything in the metadata when using different paths.

There is a discovery phase when starting up the ASM instance based on the ASM_DISKSTRING parameter. There is nothing in the dictionary.

All the needed information is located in the disk header and so it doesn’t care to the device name.

This is also the reason why you must take care to avoid to have the same device twice visible with

different names, because from ASM point of view this will be duplicate disks.

84. Whether the failover of ASM based instance using HA-Agent like VCS/SG is supported or not.

At the moment, single instance Oracle databases using ASM cannot be failed over. That’s because when a database instance is halted, and the ASM disk groups are dropped, ASM processes still keep some file descriptors to the ASM disk groups open.

First of all if a 3rd party clusterware controls applications running on a server (including ASM instance, db instance, listener, …) these applications are not required to support this setup in any way. And in fact ASM does not ‘support’ HA clusters in any meaning of that word.

The best way to solve this problem is to run ASM instances as a cluster all the time on all nodes. All the ASM instances mount all the diskgroups.The databases can fail over to another node and access the same files via the local ASM instance. This does not require a RAC license even though ASM is using

Oracle clusterware.

85. can we execute add disk & drop disk statements in one operation, in that way only one rebalance operation will be started as

SQL> alter diskgroup<diskgroup name>

add disk ‘<new device physical name 1>’, .., ‘<new device physical name N>’

drop disk <old disk logical name 1>, <old disk logical name 2>, ..,<old disk logical name N>

rebalance power <#>;

This is more efficient than separated commands (add disk & drop disk statements).

86. ASMLIB INSTALLATION

ASMLIB is distributed as Linux RPMs which are specific for the different Linux distributions based on:

– Linux distribution (RedHat 2.1, 3.0, 4.0, 5.0 or Suse Enterprise Server 8,9,10)

– Linux Kernel. (smp, highmem, or release)

– Type of CPUs: AMD64, Intel 64, Itanium 64bits, Intel 32 bits.

To verify the RPM installed execute rpm -qa |grep oracleasm.

$ rpm -qa |grep oracleasm

oracleasm-support-2.0.4-1.el5

oracleasm-2.6.18-53.el5-2.0.4-1.el5

oracleasmlib-2.0.3-1.el5

The following syntax will print at the end of the file, the platform:

$rpm -qa –qf “%{NAME}-%{VERSION}-%{RELEASE}.%{ARCH}.rpm\n” | grep asm

oracleasm-support-2.0.4-1.el5.x86_64.rpm

oracleasm-2.6.18-53.el5-2.0.4-1.el5.x86_64.rpm

oracleasmlib-2.0.3-1.el5.x86_64.rpm

As mentioned before, this set of files is different for each platform, but once you have selected the correct set, files oracleasm-support and oracleasmlib are identical for all the possible kernels (smp,highmem,etc). Only the third RPM is unique to the kernel release installed, which is obtained executing command uname -a or uname -r

$ uname -a

Linux jfrac1.us.oracle.com 2.6.18-53.el5 #1 SMP Sat Nov 10 19:37:22 EST 2007 x86_64

In this example using RedHat 5.0 for 64bits on Intel processors, checking the files in ASMLIB download page,

notice there are four different kernels :

oracleasm-2.6.18-53.el5xen-2.0.4-1.el5.x86_64.rpm

oracleasm-2.6.18-53.el5debug-2.0.4-1.el5.x86_64.rpm

oracleasm-2.6.18-53.el5-2.0.4-1.el5.x86_64.rpm

Downloading the correct RPM is required and for this kernel, it will be oracleasm-2.6.18-53.el5-2.0.4-1.el5.x86_64.rpm. The installation of an incorrect RPM will produce different errors, during the configuration of ASMLIB or later during the discovery of the disks.

* Is /dev/oracleasm created

When ASMLIB is configured, a special filesystem is created and mounted: /dev/oracleasm.

$ df -ha

Filesystem Size Used Avail Use% Mounted on

/dev/hdc2 13G 11G 1.9G 85% /

none 0 0 0 – /proc

none 0 0 0 – /dev/pts

usbdevfs 0 0 0 – /proc/bus/usb

/dev/hdc1 101M 14M 81M 15% /boot

none 250M 0 250M 0% /dev/shm

/dev/sda1 8.4G 4.8G 3.2G 60% /oradata2

/dev/sde1 8.3G 6.6G 1.4G 84% /oradata3

oracleasmfs 0 0 0 – /dev/oracleasm

When command oracleasm createdisk is executed, a block device is created under /dev/oracleasm/disks. This is the device discovered by ASMLIB using the string ORCL:*.

brw-rw—- 1 usupport dba 8, 33 Feb 23 10:54 VOL1

* Checking if ASMLIB was installed properly:

[root@arlnx2 asm_tar]# /etc/init.d/oracleasm status

Checking if ASM is loaded: [ OK ]

Checking if /dev/oracleasm is mounted: [ OK ]

If the command fails, use strace and generate a log file:

strace -f -o asm_status.out /etc/init.d/oracleasm status

* Listing the ASMLIB disks:

/etc/init.d/oracleasm listdisks

TESTX

VOL1

You will find an entry under /dev/oracleasm/disks. This is the block device associated to the physical device. If the file exist the command will return information, but if not, please execute:

strace -f -o asm_listd.out /etc/init.d/oracleasm listdisks.

87. Error Device “/dev/sdg” is not a partition when creating the ASMLIB device

This problem is reported during the creation of the ASMLIB device:

[root@arlnx2 asm_tar]# /etc/init.d/oracleasm createdisk mydisk /dev/sdg

Marking disk “/dev/sdg” as an ASM disk: asmtool: Device “/dev/sdg” is not a partition [FAILED]

The message indicates the disk is not a partition and ASMLIB requires the disk has at least one partition.

How to check this: using command fdisk -l <device name>

Example:

[root@arlnx2 asm_tar]# /sbin/fdisk -l /dev/sdg

Disk /dev/sdg: 9105 MB, 9105018880 bytes

64 heads, 32 sectors/track, 8683 cylinders

Units = cylinders of 2048 * 512 = 1048576 bytes

Device Boot Start End Blocks Id System

/dev/sdg1 1 2862 2930672 83 Linux

The command shows disk /dev/sdg having one partition /dev/sdg1 and this is the device that should be referenced on command oracleasm createdisk. If you still have problems, then use strace:

# strace -f -o asm_create.out /etc/init.d/oracleasm createdisk <disk name> <physical disk>

88. How to identify the physical disk bound to the ASMLIB disk.

Use /etc/init.d/oracleasm querydisk <NAME> where NAME is any name under /dev/oracleasm/disks.

Example:

[root@arlnx2 asm_tar]# /etc/init.d/oracleasm querydisk -d VOL1

Disk “VOL1” is a valid ASM disk on device [8, 33]

The command reports the device identified with major,minor numbers which are unique numbers associated to each disk. File /proc/partitions can be used to find the name of the device associated with those numbers:

major minor #blocks name rio rmerge rsect ruse wio wmerge wsect wuse running use aveq

8 0 8891620 sda 39715 78016 941080 417000 156198 242472 3189752 214180 0 420630 631180

8 1 8891376 sda1 39691 77970 940922 416780 156198 242472 3189752 214180 0 420410 630960

8 16 8891620 sdb 87 250 803 740 0 0 0 0 0 740 740

8 17 8891376 sdb1 57 193 632 480 0 0 0 0 0 480 480

8 32 17783250 sdc 745 2993 8321 8300 0 0 0 0 0 5250 8300

8 33 977904 sdc1 87 139 644 1040 0 0 0 0 0 1040 1040

8 34 977920 sdc2 35 193 456 230 0 0 0 0 0 230 230

8 35 1 sdc3 4 0 8 40 0 0 0 0 0 40 40

8 37 977904 sdc5 57 193 632 1240 0 0 0 0 0 1240 1240

8 38 977904 sdc6 57 193 632 1170 0 0 0 0 0 1170 1170

Also connected as root you can run the same command but referencing the physical device:

[root@arlnx2 dbs]# /etc/init.d/oracleasm querydisk /dev/sdc1

Disk “/dev/sdc1” is marked an ASM disk with the label “VOL1”

Any error on this command will require using strace:

strace -f -o asm_query.out /etc/init.d/oracleasm querydisk <NAME>.

89. ASM disks are not discovered when using asm_diskstring=’ORCL:*’

In 10gR2 if the disks are not discovered using string ORCL:*, the alternative is using /dev/oracleasm/disks. This could be set in parameter asm_diskstring or using this path in the DDL statement when creating a diskgroup or adding a disk. This is possible because in this release Oracle

can open directly the block device. 10.1 requires binding the block device to the character device known as /dev/raw/rawX.

Notice the output of ls -l /dev/oracleasm/disks

[root@arlnx2 asm_tar]# ls -l /dev/oracleasm/disks

total 0

brw-rw—- 1 usupport dba 7, 1 Feb 20 13:30 TESTX

brw-rw—- 1 usupport dba 8, 33 Feb 17 09:41 VOL1

The b at the beginning indicates this is a block device. When referencing /dev/oracleasm/disks/VOL1 there is not ASMLIB used as we are accessing

directly the block device. Using /dev/sdg1 or /dev/oracleasm/disks/VOL1 is exactly the same.

This situation could affect environments where ASM is using 10.2 but there are databases using 10.1 and 10.2. Databases using 10.2 can use the diskgroup but 10.1 will fail when a file is created. Example: creating a tablespace will fails with error ora-600:

ORA-00600: internal error code, arguments: [kfioSubmitIO07], [], [], [], [], [], [], []

CallStack:

kgeasnmierr kfioSubmitIO kfioRequest ksfd_osmcrt ksfd_create ksfdcre kcfcedtf tbsafl ctsdrv1 ctsdrv.

kfioSubmitIO is trying to obtain the extent map of the disks associated to the diskgroup but can not identify the disk. The reason is because 10.1 can

not reference directly the block device /dev/oracleasm/disks/.

* How to diagnose why ORCL:* is not working: Use /usr/sbin/oracleasm-discover ‘ORCL:*’.

When ORCL:* is resolved, the following output is presented:

[root@arlnx2 asm_tar]# /usr/sbin/oracleasm-discover ‘ORCL:*’

Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so

[ASM Library – Generic Linux, version 2.0.0 (KABI_V1)]

Discovered disk: ORCL:TESTX [819200 blocks (419430400 bytes), maxio 128]

Discovered disk: ORCL:VOL1 [1955808 blocks (1001373696 bytes), maxio 128]

If something is wrong, you could get error Unable to open ASMLib. Use strace to debug the command:

strace -f -o asm_discover.out /usr/sbin/oracleasm-discover ‘ORCL:*’

Check for the reference to library libasm.so.

open(“/opt/oracle/extapi/32/asm/orcl/1/libasm.so”, O_RDONLY) = 3.

If you get open(“/opt/oracle/extapi/32/asm/orcl/1/libasm.so”, O_RDONLY) = -1 EACCES (Permission denied), then that is the cause of the problem.

Check if the library exists or if the permissions are correct: 755. Also validate that the directories in the path also have the correct permissions (755).

90. Starting ASM instance report errors ORA-604, ORA-15183, ORA-15180 after deleting file libasm.so

When all the files under /opt/oracle/extapi path are deleted, if ASMLIB is used, when mounting diskgroups, following errors will be reported:

ORA-00604 : error occurred at recursive SQL level 2

ORA-15183 : ASMLIB initialization error [/opt/oracle/extapi/64/asm/orcl/1/libasm.so]

ORA-15180 : Could not open dynamic library /opt/oracle/extapi/64/asm/orcl/1/libasm.so, error

This set of directories and files are created during the installation of the ASMLIB rpms, particularly oracleasmlib-*. The diskgroups can not be mounted because there are not ASMLIB devices available.

During the installation, the following directories and files are created:

/opt/oracle/extapi

/opt/oracle/extapi/32

/opt/oracle/extapi/32/asm

/opt/oracle/extapi/32/asm/orcl

/opt/oracle/extapi/32/asm/orcl/1

/opt/oracle/extapi/32/asm/orcl/1/libasm.so

For 64bit platforms the 32 directory will be renamed by 64 directory. You can verify the existence of these elements executing command find

/opt/oracle/extapi

Also executing /usr/sbin/oracleasm-discover (strace -f -o asm_discover.out /usr/sbin/oracleasm-discover ‘ORCL:*’), will report errors when trying to read the missing directories/files.

In order to create this objects, oracleasmlib-* rpm needs to be reinstalled, first deleting from disk the rpm image.

For example, in this environment those are the rpms installed:

[usupport@arlnx2 admin]$ rpm -qa |grep oracleasm

oracleasm-support-2.0.0-1

oracleasmlib-2.0.0-1

oracleasm-2.4.21-EL-1.0.0-1

oracleasm-2.4.21-27.0.4.EL-1.0.4-2

To remove the rpm as root execute: rpm -e oracleasmlib-2.0.0-1

To reinstall the rpm as root execute: rpm -Uvh oracleasmlib-2.0.0-1

Note: Use –force flags to reinstall the rpms if the normal flags Uvh don’t work.

Due to Unpublished BUG:9824267 you can get this:

ORA-15183: ASMLIB initialization error [driver/agent not installed]

WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so

91. ASM instance is not discovering disks when asm_diskstring is ‘ORCL:*’

You have reviewed the previous five points and still the disks are not discovered using the string ‘ORCL:*’ and only discover the disks if using the native path ‘/dev/sdX’, ‘/dev/emcpowerxy’ or ‘/dev/oracleasm/disks/XXX’. If you are in 10gR2 this is possible because from this version, Oracle can

execute IO referencing the block device and the devices under /dev/oracleasm/disks are block devices, linked to the physical device.

SOLUTION

We can simulate the disk discovery from the operating system level, using tool kfod. ($ORACLE_HOME/bin). The execution syntax is:

[usupport@jfrac1 bin]$ kfod asm_diskstring=’ORCL:*’ disks=all

——————————————————————————–

Disk Size Path

================================================================================

1: 954 Mb ORCL:ASMLIB1

2: 955 Mb ORCL:ASMLIB2

——————————————————————————–

ORACLE_SID ORACLE_HOME

================================================================================

+ASM2 /oracle/10gR2/asm

+ASM1 /oracle/10gR2/asm

That is the normal output when executed by oracle user. Sometimes, the command reports (discover) the disks when executed as root. This is an indication of an access problem in one of the files or directories under /opt/oracle. Make sure the permissions are 755 for all the directories and the

files under /opt/oracle.

If after verifying the permissions the disks are still not discovered, it is probably a faulty installation of the oracleasm rpms. The symptoms for this problem are:

The filesystem /dev/oracleasm is mounted

ASMLIB disks can be created using /etc/init.d/oracleasm createdisk command, and the block devices exist under /dev/oracleasm/disks

Commands like /etc/init.d/oracleasm listdisks or /etc/init.d/oracleasm querydisks return the expected results.

The first 5 points referenced in this note have been validated. The rpms are installed, at least what is rpm -qa oracleasm reports. At this point, the recommendation is reinstalling the rpms for ASMLIB. Use the –force flag.

92. ASMLIB disks are bound to the individual path device and not to the pseudo device created by the multipath software.

Multipathing allows to stablish multiple I/O access paths for an individual disk, providing features like load balancing, automatic failover. There is a pseudo device, which is created under /dev and it can be referenced by ASM. Examples are /dev/emcpowerxx (EMC PowerPath), /dev/vpath (IBM

SDD), /dev/md-x (Linux MD), /dev/dm (Linux DM).

ASMLIB disks can be created referencing this pseudo devices when /etc/init.d/oracleasm createdisk is used.

There is a situation during scandisks operation where the ASMLIB is binded to the single/individual path and not to the pseudo device. You can verify this running an ls -l /dev/oracleasm/disks and checking the major,minor numbers of the devices.

[usupport@jfrac1 bin]$ ls -l /dev/oracleasm/disks

total 0

brw-rw—- 1 usupport dba 8, 17 Jul 11 17:08 ASMLIB1

brw-rw—- 1 usupport dba 8, 18 Jul 11 17:08 ASMLIB2

In this particular case, ASMLIB1 is binded to disk identified with major,minor 8,17. File /proc/partitions contains the mapping of the major,minor and the name of the device.

Although some multipath vendors will trap the IO even if the individual path is referenced, it is prefered to bind the ASMLIB disks with the pseudo device created by the multipath layer.

SOLUTION

Modify parameter ORACLEASM_SCANORDER on file /etc/sysconfig/oracleasm and set the string associated with the pseudodevices.

Examples are:

ORACLEASM_SCANORDER=emcpower

ORACLEASM_SCANORDER=dm

ORACLEASM_SCANORDER=vpath

ORACLEASM_SCANORDER=md

If you are running a cluster, make sure to modify the file in all the nodes and restart ASMLIB.

93. ASMLIB Logging

ASMLIB provides additional logging for the following areas:

ENTRY /* func call entry */

EXIT /* func call exit */

DISK /* Disk information */

REQUEST /* I/O requests */

BIO /* bios backing I/O */

IOC /* asm_iocs */

ABI /* ABI entry points */

The settings are recorded in file /proc/fs/oracleasm, where those are the default values:

ENTRY deny

EXIT deny

DISK off

REQUEST off

BIO off

IOC off

ABI off

ERROR allow

NOTICE allow

There are three possible values:

deny

off

allow

To change the values, run this simple command:

echo “DISK allow” > /proc/fs/oracleasm/log_mask

This will change the particular value for the DISK entry without affecting others values. It is recommended to run the command for each change individually or you can use the following shell script:

1.log_mask=”/proc/fs/oracleasm/log_mask”

2.echo ” ***************** Current values ********************”

3.echo

4.cat $log_mask

5.echo REQUEST > /tmp/logmask.out

6.echo DISK >> /tmp/logmask.out

7. cat /tmp/logmask.out | (

8. while read bit status; do

9. # $1 is “allow” or “off

10. echo “$bit $1” > $log_mask

11. done

12. )

13.echo

14.echo “****************** New Values ***********************”

15.cat $log_mask

and execute sh x.sh <new value>.

Ex: x.sh allow

Setting the value to off will disable the extra logging.

This changes does not require restarting ASMLIB or restarting ASM Instance.

94. What is the default memory allocation for ASM?

In 10g the default SGA size is 1G in 11g it is set to 256M and in 12c ASM it is set back to 1G.

95. How do your backup ASM Metadata?

You can use md_backup to restore the ASM diskgroup configuration in-case of ASM diskgroup storage loss.

96. What files can be stored in the ASM diskgroup?

In 11g the following files can be stored in ASM diskgroups.

·       Datafiles

·       Redo logfiles

·       Spfiles

In 12c the files below can also new be stored in the ASM Diskgroup

·       Password file

97. What it the ASM POWER_LIMIT?

This is the parameter which controls the number of Allocation units the ASM instance will try to rebalance at any given time. In ASM versions less than 11.2.0.3 the default value is 11 however it has been changed to unlimited in later versions.

98. What are different types of redundancies in ASM & explain?
External redundancy,
Normal redundancy,
High redundancy.

99. What is stripping and mirroring.
Striping is spreading data across multiple disks so that IO is spread across multiple disks and hence increase in throughput. It provides read/write performance but fail over support.
ASM offers two types of striping, with the choice depending on the type of database file. Coarse striping uses a stripe size of 1MB, and you can use coarse striping for every file in your database, except for the control files, online redo log files, and flashback files. Fine striping uses a stripe size of 128KB. You can use fine striping for control files, online redo log files, and flashback files.
 Mirroring means redundancy. It may add performance benefit for read operations but overhead for write operations. It’s basic purpose is to provide fail over support.
There are three ASM mirroring options:
High Redundancy – In this configuration, for each primary extent, there are two mirrored extents. For Oracle Database Appliance this means, during normal operations there would be three extents (one primary and two secondary) containing the same data, thus providing “high” level of protection. Since ASM distributes the partnering extents in a way that prevents all extents to be unable due to a component failure in the IO path, this configuration can sustain at least two simultaneous disk failures on Oracle Database Appliance (which should be rare but is possible).
Normal Redundancy – In this configuration, for each primary extent, there is one mirrored (secondary) extent. This configuration protects against at least one disk failure. Note that in the event a disk fails in this configuration, although there is typically no outage or data loss, the system operates in a vulnerable state, should a second disk fail while the old failed disk replacement has not completed. Many Oracle Database Appliance customers thus prefer the High Redundancy configuration to mitigate the lack of additional protection during this time.
External Redundancy – In this configuration there are only primary extents and no mirrored extents. This option is typically used in traditional non-appliance environments when the storage sub-system may have existing redundancy such as hardware mirroring or other types of third-party mirroring in place. Oracle Database Appliance does not support External Redundancy.

100. How does ASM provides Redundancy?
When you create a disk group, you specify an ASM disk group type based on one of the following three redundancy levels:
Normal for 2-way mirroring – When ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
High for 3-way mirroring. In this case the extent is mirrored across 3 disks.
External to not use ASM mirroring. This is used if you are using Third party Redundancy mechanism like RAID, Storage arrays.

101. Can ASM instance and database (rdbms) be on different servers?
ASM instance and Database (rdbms) have to be present on same server. Otherwise it will not work.

102. Can we see the files stored in the ASM instance using standard unix commands.
No, you cannot see the files using standard unix commands like ls. You need to use utility called asmcmd to do this. This is present in 10.2 and above.e.g
/home/oracle>asmcmd
Asmcmd>
You can use help command to see the options.

103. What is SYSASM role?
Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk ,etc
<span style=”font-size: small; font-family: arial,helvetica,sans-serif;”>Alert entry
WARNING: Deprecated privilege SYSDBA for command ‘STARTUP'</span>

Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk, etc

Alert entry

WARNING: Deprecated privilege SYSDBA for command ‘STARTUP’

104. Is it mandatory to use disks of same size and characteristics for Diskgroups?
No, it is not mandatory to use the disks of same size and characteristics for Diskgroups though it is a Recommended Practice.
Same size disk for Failuregroups in Normal/High redundancy will prevent issues like ORA-15041 as the file extents needs to be mirrored across the disks. Also as Oracle distributes data based on capacity, so larger disk will have more data stored in it and which will result in higher I/O to disk and eventually can lead to sub-optimal performance.
Moreover having disks of different characteristic like varying disk speed can impact the performance.
When managing disks with different size and performance capabilities, best practice is to group them into disk groups according to their characteristics. So you can use higher speed disks for your database files while other disks can be part of Diskgroup used for Flash Recovery Area.

105. Do we need to install ASM and Oracle Database Software in different ORACLE_HOME?
No. Again installing ASM and Oracle Database Software in different ORACLE_HOME is not mandatory but a best practice. This is useful in cases when we need to have multiple databases using same ASM instance and you need to patch only one of them. E.g You need to apply a CBO patch to one of 10.2 database while your other 10.1 database using different installation does not require it. In this case having a ASM_HOME separate from 10.2 ORACLE_HOME will allow your 10.1 database to keep running. Thus this approach is useful for High Availability.

105. What is the maximum size of Disk supported by ASM?
ASM supports disks upto 2Tb, so you need to ensure that lun size should be less then 2Tb. 10.2.0.4 and 11g database will give error if you try to create a diskgroup with ASM disks having disk size >2Tb.

106. I have created Oracle database using DBCA and having a different home for ASM and Oracle Database. I see that listener is running from ASM_HOME. Is it correct?
This is fine. When using different home for ASM, you need to run the listener from ASM_HOME instead of ORACLE_HOME.

107. How do we identify if we are connected to Normal Instance or ASM instance?

Issue following command to identify this

SQL> show parameter instance_type

NAME TYPE VALUE

———————————— ———– ——————————

instance_type string asm

In case you are connected to ASM instance, it will display value as asm, otherwise it will display value as RDBMS.

108. Can we change the Redundancy for Diskgroup after its creation?

No, we cannot modify the redundancy for Diskgroup once it has been created. To alter it we will be required to create a new Diskgroup and move the files to it. This can also be done by restoring full backup on the new Diskgroup. Following metalink note describes the steps

Note.438580.1 – How To Move The Database To Different Diskgroup (Change Diskgroup  Redundancy)

109. Can we use ASM for storing Voting Disk/OCR in a RAC instance?

In oracle 11gR1 and below, you cannot use ASM for storing the voting disk and OCR. It is due to the fact that Clusterware starts before ASM instance and it should be able to access these files which is not possible if you are storing it on ASM. You will have to use raw devices or OCFS or any other shared storage.

In Oracle 11gR2 we can store them in ASM.

110. Does ASM instance automatically rebalances and takes care of hot spots?

No. This is a myth and ASM does not do it. It will initiate automatic rebalance only when a new disk is added to Diskgroup or we drop a disk from existing Diskgroup.

111. Can we use block devices for ASM Disks?

Yes. Starting from Oracle Database 10.2 block devices can be used directly for ASM Disks in Linux. This is not true for other Unix based systems where block devices are not supported yet.

Along with this it is recommended to use a Device mapping functionality so that disk mapping is preserved after disk failure. This is important when you have devices as /dev/sda,/dev/sdb,/dev/sdc and due to some reason the devices are not detected at next reboot (say /dev/sdb), the system will map the incorrect device (i.e /dev/sdc will be marked as /dev/sdb). You can use following methods for preserving disk names

-udev – the role of udev is to provide device persistency and naming consistency.This is especially important for the Oracle Cluster Registry (OCR) and Voting disks required by Oracle Clusterware.

-ASMLIB – ASMLIB will provide device management specifically for ASM disk devices.

 112. Describe about ASM architecture.

Automatic Storage Management (ASM) instance

Instance that manages the diskgroup metadata

Disk Groups

Logcal grouping of disks
Determines file mirroring options
ASM Disks
LUNs presented to ASM
ASM Files
Files that are stored in ASM disk groups are called ASM files, this includes database files

Notes:

Many databases can connect as clients to single ASM instances

ASM instance name should only be +ASM only