ASM instance in INTERMEDIATE state + CHECK TIMED OUT state_details

Yet another clash with ASM 11gR2 BUG during a new database creation under a 6 node development RAC environment. Got a requirement to create a new database, thereby, created a new ASM diskgroup manually and began creating the database with DBCA tool. However,  ran over the following error when the newly created ASM diskgroup was selected to host the data files of the database:

After getting the error, I thought I would better ensure once again that the diskgroup is properly mounted on the local ASM instance, despite the fact that the diskgroup in very well mount state, the issue persists. I therefore check over the resource (asm and dg) status in the cluster issuing the 'crsctl stat res -t' command. The output displays that the diskgroup in the question has OFFLINE STATE and scrolling further down it was a bit strange and scary moment. The ora.asm resource was in INTERMEDIATE state with CHECK TIMED OUT state_details.

My curiosity let me do some research over the MOS and quickly come across the following document:

oraagent.bin Exits After Check Timed Out [ID 1323679.1]

The issue was nearly identical to what has been explained in the document and the behavior is due to bug 11807012 that apparently fixed in 11.2.0.3, 12.1 and above. However, the document also did mention that the Interim patch 11807012 exists for certain platform/version and advised to engage Oracle Support to request if it does not exist for your platform/version. Unfortunately, we couldn't find the interim patch for our OS (HPUX). We then opened a SR and the engineer indeed confirmed it is due to the bug in the context. As a workaround, recommend the following action plan followed ASM instance restart:

crsctl modify resource "ora.asm" -attr "CHECK_TIMEOUT=132"

The above change was applied and bounced the ASM instance to reflect the change.(We seek the downtime to deploy the change, since it was a dev. environment, we easily get it). The ora.asm resource then cam back to normal condition and the database was successfully created subsequently..

Out of six ASM instances, we found three of them were in the same state. However, on a positive side, it will not cause any troubles with the ongoing operations unless you wanna do something what we needed. The following image outlines the status of the ASM resources:

Happy reading,

Jaffar

Oracle 11g upgrade aftermath - ORA-24247: network access denied by access control list (ACL)

Following a successful Oracle 10g Clusterware and databases upgrade to Oracle 11g R2, some of the database user defined procedures execution were failed with the 'ORA-24247: network access denied by access control list (ACL)' error and it was brought to our notice instantly.

With a little bit of research over the internet about the 'ORA-24247' error,  it was clear that it is an expected behavior post 11g upgrade if  any of Oracle supplied package, like, UTL_TCP, UTL_HTTP, UTL_SMTP or UTL_MAIL is being referred or used in any user defined database procedures/packages/functions. This behavior is largely due to an enhanced security level with the Access Control Lists(ACL) with Oracle 11g to access External Network Services.

The problem can be resolved by explicitly granting the user or role in the question to access the External network Services by making use of the new Oracle supplied package, DBMS_NETWORK_ACL_ADMIN.

The following example demonstrates the steps that are required to address the error:

BEGIN

  dbms_network_acl_admin.create_acl(acl => 'http_service_acl.xml',
                                    description => 'HTTP ACL Access',
                                    principal   => 'USER_NAME',
                                    is_grant    => TRUE,
                                    privilege   => 'connect',
                                    start_date  => null,
                                    end_date    => null);
  dbms_network_acl_admin.add_privilege(acl=> 'http_service_acl.xml',
                                    principal  => 'USER_NAME',
                                    is_grant   => TRUE,
                                    privilege  => 'resolve',
                                    start_date => null,
                                    end_date   => null);
  dbms_network_acl_admin.assign_acl(acl => 'http_service_acl.xml',
                                    host       => '*',
                                    lower_port => 8801,
                                    upper_port => 8810);
  commit;
END;

In a nutshell, the above script does the following (ensure the user got the DBA privileges to execute the above script successfully on the database):

• Creates a new ACL with and grants the 'connect' privilege to the specified user.
• Grants the 'resolve' privilege to the user.
• Finally assign the ACL to specific (or open) host with defined ports.

 You can subsequently query the dictionary table dba_network_acls to extract the ACL configured information.

In order to modify the host and port information, you must unassign the ACL using the UNASSIGN_ACL procedure.

Punchline:
Ensure you go through the above exercise after you upgrade your 10g database to Oracle 11g, if the above mentioned Oracle Supplied packages are referred.

References
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_networkacl_adm.htm#BABBJCJD
MOS: [ID 1209644.1]

crs_stat has been deprecated in 11gR2

While reading whats new and whats deprecated in Oracle 11gR2, I came across of the following:

• Command crs_stat has been deprecated  in 11gR2, do not use it anymore, to find out all user resource state use, $GRID_HOME/bin/crsctl stat res -t

•  By default ora.gsd is OFFLINE if there's no 9i database in the cluster, ora.oc4j is OFFLINE in 11.2.0.1 as Database Workload Management(DBWLM) is unavailable.

By the way, in 11gR2 or above, you can use the below command to find out the clusterware process state:

• $GRID_HOME/bin/crsctl stat res -t -init

 

Clusterized (Cluster Aware) Commands

Unlike the previous Oracle versions, beginning with Oracle 11gR2,  Oracle Clusterware stack on all nodes in a cluster can be managed from any node using the following Clusterized (Cluster Aware) commands:

$ crsctl check cluster -all - to check the status of the Clusterware on all nodes
$ crsctl stop cluster -all - to stop the Oracle Clusterware stack on all nodes
$ crsctl start cluster -all - to start the Oracle Clusterware stack on all nodes 

Voting Disk Backup Procedure Changed in Oracle 11g Release 2

Surely we are going to have too many posts on Oracle 11gR2 new feature by many people and here is mine first post.

As part of our new forthcoming book on 'Oracle 11g Clusterware' by Packt Publications, I have done Oracle 11g R2 clusterware upgrade from Oracle 11gR1 and it was a good learning experience.

Voting Disk backup Procedure Change
In prior releases, backing up the voting disks using a dd command was a required postinstallation task. With Oracle Clusterware release 11.2 and later, backing up and restoring a voting disk using the dd command is not supported.

Backing up voting disks manually is no longer required, as voting disks are backed up automatically in the OCR as part of any configuration change and voting disk data is automatically restored to any added voting disks.