ASMLIB COnfiguration (root.sh on Node 2 Fails with ORA-15018 ORA-15072 ORA-15080 errors)

References: 

1) Configuring Oracle ASMLib on Multipath Disks 

http://www.oracle.com/technetwork/topics/linux/multipath-097959.html

2) Oracle support document: 1670374.1

For a RAC installation we had two nodes to configure. Multipath disks are already configured 

with storage tools provided (e.g EMC power path). We assume the multipath disks have been given the name 

" multipatha", so as to be storage vendor agnostic. 

Error Faced:

While running the root.sh on node 2 the following error was returned 

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

Disk Group OCRDG creation failed with the following message:

ORA-15018: diskgroup cannot be created

ORA-15072: command requires at least 1 regular failure groups, discovered only 0

ORA-15080: synchronous I/O operation to a disk failed

Configuration of ASM ... failed

see asmca logs at /u01/app/oracle/cfgtoollogs/asmca for details

Did not successfully configure and start ASM at /u01/grid/oracle/crs/product/11.2.0.3/crs/install/crsconfig_lib.pm line 6763.

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

Background:

There are two issues with using multipath disks in ASM. ASM cannot handle seeing the same disk more than once. 

If it does, it will cause an error. A single disk can appear multiple times in a multipath configuration: 

1)Unique paths to the disk ( in our case we had four different paths to the disk)

2)The multipath access point

For example the server has disk /dev/sda presented from an external storage; the server has four (in our case)

connections, or paths, to that external storage. The Linux SCSI driver 

will see all paths. They will appear as /dev/sdb ,/dev/sdc, /dev/sdd and /dev/sde. Accessing the paths /dev/sdb ,/dev/sdc, /dev/sdd and /dev/sde

will end up on the same disk.

If multipathing is enabled, there will be a multipath disk, for example /dev/multipatha, that can access all paths. That is, any I/O to multipatha can use any of the above path. If a system were using the sdb path, and that cable is unplugged, the system will get an error. But the multipath disk will know to switch to any of the three paths available. 

Mutipath configuration to transparent to most applications. It can use any of the paths, sdb, sdc, or multipatha, and it won't know the difference. ASMLib is the solution. The default configuration does not care which path it uses. 

ASMLib will choose only one of the paths, because ASM cannot handle seeing the same disk more than once. This solves the first issue. ASM only sees one path, and it works. now the second issue is which path does ASM see In its default configuration, ASMLib will choose the first path it finds. This is the first path as reported by Linux. Depending on the storage driver, it could be the multipath, or it could be one of the single paths.

The system administrator wants ASMLib to always use the multipath disk. What's the point of having it if Oracle is not using it? There is no way, however, for ASMLib to know what a multipath looks like. It must be told via its configuration.

Disk Scan Ordering 

Disks are marked for ASMLib using a process described in ASMLib Installation. ASMLib learns what disk are marked during a process called disk scanning. ASMLib runs this scan every time it starts up. The system administrator can also force a scan via the /etc/init.d/oracleasm scandisks command. ASMLib examines each disk in the system. It checks if the disk has been marked for ASMLib. Any disk that has been marked will be made available to ASMLib. 

Normally, ASMLib checks each disk in the exact order the OS lists them. This is fine for most systems. In the last section, we described a case where the OS order isn't good enough. The system administrator wants ASMLib to see multipath disks before it sees single path disks. This allows ASMLib to choose the multipath disk and make it available to Oracle.

ASMLib allows two modifications to the disk scan order. First, it allows exclusion of certain disks. In other words, ASMLib will ignore those disks completely. Second, the system administrator can specify disks that are to be scanned first. Disks in this list are scanned before the rest of the disks in the system.

A multipath configuration can use either facility. The system administrator could choose to exclude all the single path disks. ASMLib will then ignore them, only scanning the multipath disks. Conversely, the administrator could specify that the multipath disks are scanned first. ASMLib will see them first, and prefer them over the single paths.

Solution: Configuration of ASMLIB Scan order

The Oracle ASMLib configuration file is located at /etc/sysconfig/oracleasm. It is a link to file /etc/sysconfig/oracleasm-_dev_oracleasm and tools   actually read the later file. It contains all the startup configuration that the system administrator   specified via the /etc/init.d/oracleasm configure command. That command cannot configure scan ordering.  

The configuration file contains many configuration variables. The "ORACLEASM_SCANORDER" variable specifies disks to be scanned first. The ORACLEASM_SCANEXCLUDE variable specifies the disks that are to be ignored. 

The variables take a whitespace-seperated list of prefix strings to match. In other words, if a disk starts with one of the prefix strings, it matches. For example, the prefix string sd will match all SCSI devices. The disk sda starts with sd. Note that these are not globs. They do not use wildcards. They are simple prefixes. Also note that the /dev/ path is not part of the 

prefix. 

NOTE: When scanning, only the device names known by the kernel are scanned. With device-mapper, the kernel sees the devices as /dev/dm-XX. The /dev/mapper/XXX names are created by udev for human readability. Any configuration of ORACLEASM_SCANORDER or ORACLEASM_SCANEXCLUDE must use the dm prefix. 

A couple examples:

NOTE: If you are hand editing file /etc/sysconfig/oracleasm, make sure the link to /etc/sysconfig/oracleasm-dev_oracleasm is not broken.

Multipath Disks First 

The system administrator configures ASMLib to scan the multipath disks first. In the ASMLib configuration file she edits the ORACLEASM_SCANORDER variable to look like so: 

 ORACLEASM_SCANORDER="multipath dm"

 or in our case we used 

 ORACLEASM_SCANORDER="dm"

  

During a scan, ASMLib first tries all disks that start with "multipath".  The multipath device /dev/multipatha certainly matches.  It is scanned first. Next, ASMLib tries all disks that start with "dm". This is all the SCSI disks. The local disk /dev/sda will be scanned, but it is not an ASM disk. The single path disks /dev/sdb and /dev/sdc are also scanned.  They are ASM disks, but ASMLib will see that it already has a path to that disk. It will ignore them.  Finally, ASMLib will scan any other disks that did not match either prefix. 

Excluding Single Path Disks 

The system administrator configures ASMLib to ignore the single path disks. In the ASMLib configuration, he edits the ORACLEASM_SCANEXCLUDE variable to look like so: 

ORACLEASM_SCANEXCLUDE="sd"

Running the root.sh after configuring the following two parameters; the issue was resolved and the root.sh was successful.

• ORACLEASM_SCANORDER
• ORACLEASM_SCANEXCLUDE

ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary.

It  was a very interesting case i came across. We have a Production database with a Standby database opened in Read-only mode for reporting (the Active data guard Feature). An  user account was locked in the production database. I issued the following command to unlock it.

SQL> alter user <username> account unlock;

wasn't that easy. Now as per my understanding the user account should have been unlocked in the standby database as well. Just to check i logged in the standby database

SQL> conn <username>/<password>

ERROR:
ORA-28000: the account is locked


now what !!!!    Still, the user cannot connect to the standby, getting the same error ORA-28000.

Luckily i found the following document on the Oracle Support. "ORA-28000 "the account is locked" in the standby database, even after the account was unlocked in the primary. (Doc ID 1600401.1)".

which states the cause of the issue as

"A privileged user (a database administrator) must unlock account in the standby database.  It is not enough to unlock the same account in the primary database.  This is because the standby is open read-only and cannot update any tables.  When a user's account has to be locked on the standby database, it is locked only in memory there."


The solution to the problem is

"A privileged user (sysdba, the database administrator) must logon to the standby and unlock the account there.  A message ORA-28015 "Account unlocked, but the database is open for read-only access" confirms that the account is now unlocked in the standby database.  From then on, the user can logon to the standby database without getting any error."

Truncate Table with Foreign Key constraint

Truncating the Tables i always a better option when we want to delete all rows in the table as Dropping and re-creating a table invalidates dependent objects of the table, requires you to re grant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and re specify its storage parameters. Truncating has none of these effects.

The "Truncate Table" also performs of following tasks

• Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
• Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process 

But there is one challenge in truncating a table when there is referential integrity constraint involved. I have written a small script to first disable the foreign key constraint on the child table. After then perform the truncate operation and enable the foreign key constraints again. Lets perform a small demo.

Parent table:

SQL> CREATE TABLE TEST1 (

  2  id    NUMBER        PRIMARY KEY,

  3  name  VARCHAR2(20)  NOT NULL

  4  );

Table created.

Child table:

SQL> CREATE TABLE TEST2 (

  2  ctry_id         NUMBER        NOT NULL,

  3  cont_id         NUMBER        NOT NULL REFERENCES TEST1

  4  );

Table created.

Now While trying to truncate the Parent table. The following errors pops up

SQL> truncate table TEST1;

truncate table TEST1

               *

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

The very useful script disable all the foreign keys referencing the parent table (or list of tables to be truncated)

SQL> select 'alter table '||owner||'.'||TABLE_NAME||' disable constraint '||constraint_name ||';'

  2  r_constraint_name

  3  from dba_constraints

  4  where constraint_type='R'

  5  and r_constraint_name in

  6  (select constraint_name from dba_constraints

  7  where constraint_type in ('P','U') and table_name='TEST1');

R_CONSTRAINT_NAME

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

alter table SYSTEM.TEST2 disable constraint SYS_C00821334;

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

Disable the Foreign Keys identified by the Script:

SQL> alter table SYSTEM.TEST2 disable constraint SYS_C00821334;

Table altered.

The table truncate is successful now

SQL> truncate table TEST1;

Table truncated.

The script Enable all the foreign keys referencing the parent table (or list of tables)

SQL> select 'alter table '||owner||'.'||TABLE_NAME||' ENABLE NOVALIDATE constraint '||constraint_name ||';'

  2  r_constraint_name

  3  from dba_constraints

  4  where constraint_type='R'

  5  and r_constraint_name in

  6  (select constraint_name from dba_constraints

  7  where constraint_type in ('P','U') and table_name='TEST1');

R_CONSTRAINT_NAME

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

alter table SYSTEM.TEST2 ENABLE NOVALIDATE constraint SYS_C00821334;

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

Finally Enable the Foreign Keys identified by the Script:

SQL> alter table SYSTEM.TEST2 ENABLE NOVALIDATE constraint SYS_C00821334;

Table altered.