Why my new Disk Group is not getting mounted automatically when ASM instance starts?

Today I faced the issue that a new ASM Disk Group created in instance +ASM1 is not getting automatically mounted in instance +ASM2.

The first step was to check DB dependencies:

[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA
Services: reports
Database is administrator managed
You can see that only dependent Disk Groups are ‘DATA’.

Let us add ‘FRA’ also as dependent Disk Group:

[oracle@ol5-112-rac2 trace]$ srvctl modify database -d RAC -a ‘DATA,FRA’
[oracle@ol5-112-rac2 trace]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/db_1
Oracle user: oracle
Spfile: +DATA/RAC/spfileRAC.ora
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA,FRA
Services: reports
Database is administrator managed
The next step is to add the Disk Groups to ASM_DISKGROUPS parameter:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM1′;
System altered.
SQL> ALTER SYSTEM SET ASM_DISKGROUPS=’DATA’,’FRA’ scope=both sid=’+ASM2′;
System altered.
After restarting +ASM2 instance we can see that FRA Disk Group is mounted automatically:

[oracle@ol5-112-rac2 trace]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 12 15:44:44 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Real Application Clusters and Automatic Storage Management options
SQL> set lines 1000
SQL> select * from V$ASM_DISKGROUP;
GROUP_NUMBER NAME                           SECTOR_SIZE BLOCK_SIZE ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB HOT_USED_MB COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS COMPATIBILITY                                                DATABASE_COMPATIBILITY                                       V
———— —————————— ———– ———- ——————– ———– —— ———- ———- ———– ———— ———————– ————– ————- ———————————————————— ———————————————————— –
           1 DATA                                   512       4096              1048576 MOUNTED     EXTERN      15342      12616           0         2726                       0          12616     0 11.2.0.0.0                                                   10.1.0.0.0                                                   N
           2 FRA                                    512       4096              1048576 MOUNTED     EXTERN       6133       5962           0          171                       0           5962     0 11.2.0.0.0                                                   10.1.0.0.0                                                   N
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Real Application Clusters and Automatic Storage Management options
Thanks,

Alfredo

ORA-15097: cannot SHUTDOWN ASM instance with connected client

In 11g R2 Clusterware configurations the OCR is located in ASM disk, that’s why ASM can’t be shutdown while the cluster is running.  You have to shutdown your cluster in order to shutdown ASM instance.

Oracle support note 984663.1

Here’s a good blog about this:
Example, how to shutdown ASM instance on node ol5-112-rac1:
[oracle@ol5-112-rac1 ~]$ crsctl status resource -t
——————————————————————————–
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.FRA.dg
               OFFLINE OFFLINE      ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.asm
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2             Started
ora.eons
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.gsd
               OFFLINE OFFLINE      ol5-112-rac1
               OFFLINE OFFLINE      ol5-112-rac2
ora.net1.network
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
ora.ons
               ONLINE  ONLINE       ol5-112-rac1
               ONLINE  ONLINE       ol5-112-rac2
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol5-112-rac1
ora.oc4j
      1        OFFLINE OFFLINE
ora.ol5-112-rac1.vip
      1        ONLINE  ONLINE       ol5-112-rac1
ora.ol5-112-rac2.vip
      1        ONLINE  ONLINE       ol5-112-rac2
ora.rac.db
      1        ONLINE  ONLINE       ol5-112-rac2             Open
      2        ONLINE  ONLINE       ol5-112-rac1
ora.rac.reports.svc
      1        ONLINE  ONLINE       ol5-112-rac1
ora.scan1.vip
      1        ONLINE  ONLINE       ol5-112-rac1
[oracle@ol5-112-rac1 ~]$
Shutdown RAC1 instance first:

[oracle@ol5-112-rac1 ~]$ srvctl stop instance –d RAC –I RAC1 –o immediate
Then proceed to shutdown cluster (root required):

[oracle@ol5-112-rac1 ~]$ su –
[root@ol5-112-rac1 bin]#
[root@ol5-112-rac1 bin]# ./crsctl stop cluster
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘ol5-112-rac1’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac1’ succeeded
CRS-2672: Attempting to start ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac2’
CRS-2676: Start of ‘ora.ol5-112-rac1.vip’ on ‘ol5-112-rac2’ succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.asm’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.eons’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.ons’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.net1.network’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.eons’ on ‘ol5-112-rac1’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘ol5-112-rac1’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.cssdmonitor’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘ol5-112-rac1’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.cssdmonitor’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘ol5-112-rac1’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.cssd’ on ‘ol5-112-rac1’ succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘ol5-112-rac1’
CRS-2677: Stop of ‘ora.diskmon’ on ‘ol5-112-rac1’ succeeded
[root@ol5-112-rac1 bin]# ps -ef|grep pmon
root      7072  5722  0 14:45 pts/2    00:00:00 grep pmon
[root@ol5-112-rac1 bin]# ps -ef|grep +ASM1
root      7074  5722  0 14:46 pts/2    00:00:00 grep +ASM1
[root@ol5-112-rac1 bin]#
At this point ASM & Cluster Ready Services managed resources are down.

In order to start them again just need to issue:

[root@ol5-112-rac1 bin]# ./crsctl start cluster
[root@ol5-112-rac1 bin]# exit
[oracle@ol5-112-rac1 ~]$ srvctl start instance –d RAC –I RAC1

Thanks,
Alfredo

Cleanup After Failed Installation Oracle Clusterware 11gR2

In this blog I will talk about how to cleanup a failed installation of the Oracle Clusterware 11gR2 in one particular node. I was installing the new Oracle Clusterware 11.2.0.1 in my home lab by follwing Tom Kyte’s instructions (http://www.oracle-base.com/articles/11g/oracle-db-11gr2-rac-installation-on-oel5-using-virtualbox.php#install_grid_infrastructure), then went through the part that the installer GUI needs us to run the root.sh scripts.



The easy thought was to run the root.sh script in both nodes of my RAC (RAC1 & RAC2) at the same time, without reading the explicit instructions “Run the script on the local node first. After successful completion, you can run the script in parallel on all other nodes.”:



The script run successful in the RAC1 node but in the RAC2 node. So when the script finished in RAC1 tried to run it again in RAC2, but the same results “the output was that the script was already ran”.

What to do next?, do I have to start over from scratch?

Surfing the web found a good & useful article from Guenadi Jilevski (http://gjilevski.com/2010/08/12/how-to-clean-up-after-a-failed-11g-crs-install-what-is-new-in-11g-r2-2/), here shows how to perform a manual cleanup in 11gR1, but also shows the new features and scripts in 11gR2.

Summarized steps:

Deconfigure Oracle Clusterware 11.2.x.x without removing the binaries:

  • Log in as root user on the node you encountered the error. Change directory to $GRID_HOME/crs/install.
          # cd $GRID_HOME/crs/install

  • Run rootcrs.pl with the -deconfig -force flags on the node you have the issue.
          # perl rootcrs.pl -deconfig -force

  • If you are deconfiguring Oracle Clusterware on all the nodes in the cluster, then you have to add the -lastnode flag on the last one in order to deconfigure OCR and Voting disks.
          # perl rootcrs.pl -deconfig -force -lastnode

After these steps, run the root.sh script again. This time it will smoothly run.

You should also want to read the complete OSS note:
How to Deconfigure/Reconfigure(Rebuild OCR) or Deinstall Grid Infrastructure [ID 1377349.1]

Thanks,
Alfredo

Historical SQL Statistics And Execution Plan Change

How many times have you received user’s phone call stating that the DB is slow?, well sometimes this has nothing to do with the DB’s overall performance itself, but with a user’s query.

Now the question is, is this query really slow?, the best way to find this out is comparing the actual execution time with the ones in the past. Here’s a SQL statement which search inside AWR repository (Caution!!! a special license is required!!!), it compares the execution plan of all SQL_ID’s against the previous snapshot.

awr_planchanges.sql

  prompt enter the number of days in the past to scan
  SET LINES 500
  SELECT A.SNAP_ID,
         BEGIN_INTERVAL_TIME,
         SQL_ID,
         HASH_VALUE1,
         HASH_VALUE2,
         VALUE1 as “ELAPSED TIME PER EXEC 1” ,
         VALUE2 as “ELAPSED TIME PER EXEC 2”,
         ROUND (CHANGE_PERCENT) as “CHANGE PERCENT”
    FROM (  SELECT SNAP_ID,
                   SQL_ID,
                   SUM (pvalue1) HASH_VALUE1,
                   SUM (pvalue2) HASH_VALUE2,
                   SUM (value1) VALUE1,
                   SUM (value2) VALUE2,
                   (SUM (VALUE1) + 1) * 100 / (SUM (VALUE2) + 1)
                      AS CHANGE_PERCENT
              FROM (SELECT snap_id,
                           0 AS snap2,
                           sql_id,
                           plan_hash_value AS pvalue1,
                           0 AS pvalue2,
                           ROUND (
                              elapsed_time_delta / executions_delta / 1000000,
                              3)
                              AS value1,
                           0 AS value2
                      FROM dba_hist_sqlstat sql
                     WHERE executions_delta > 0
                    UNION
                    SELECT snap_id + 1,
                           snap_id AS snap2,
                           sql_id,
                           0 AS pvalue1,
                           plan_hash_value AS pvalue2,
                           0 AS vaule,
                           ROUND (
                              elapsed_time_delta / executions_delta / 1000000,
                              3)
                              AS value2
                      FROM dba_hist_sqlstat sql
                     WHERE executions_delta > 0)
          GROUP BY SNAP_ID, SQL_ID) A,
         dba_hist_snapshot B
   WHERE     A.SNAP_ID = B.SNAP_ID
         AND HASH_VALUE1 > 0
         AND HASH_VALUE2 > 0
         AND HASH_VALUE1 != HASH_VALUE2
         AND BEGIN_INTERVAL_TIME > SYSDATE – (&days)
ORDER BY A.SNAP_ID
/

This is the output of the script:

SNAP_ID BEGIN_INTERVAL_TIME       SQL_ID       HASH_VALUE1 HASH_VALUE2 SEC PER EXE 1 SEC PER EXE 2 CHANGE % 
—– ———————–  ————- ———– ———– ————- ————- ——–
9547 01-OCT-12 01.00.07.841 AM   4urszd9dt9fjv  4862331523   891004645          .015          .007       101
9585 02-OCT-12 03.00.41.798 PM   4urszd9dt9fjv  4862331523   891004645          .009          .003       101
9586 02-OCT-12 04.00.36.393 PM   4urszd9dt9fjv   891004645  4862331523          .006          .009       100
9587 02-OCT-12 05.00.25.306 PM   1k30v0pyg32vu   414828074   878600859          .179          .157       102
9587 02-OCT-12 05.00.25.306 PM   dsm86bzuqtd2r  2452407222  3005749068          .048         1.37         44
9616 03-OCT-12 10.00.34.499 PM   4urszd9dt9fjv  4862331523   891004645          .018          .023       100
9621 04-OCT-12 03.00.06.979 AM   4urszd9dt9fjv   891004645  4862331523          .03           .027       100
9640 04-OCT-12 09.00.40.250 PM   4urszd9dt9fjv  4862331523   891004645          .042          .012       103
9641 04-OCT-12 10.00.22.954 PM   dfmu8nm1cscx7  3810296266  4308029399          .938          .703       114
9710 07-OCT-12 07.00.09.269 PM   4urszd9dt9fjv  4862331523   891004645          .046          .015       103
9750 09-OCT-12 11.00.59.162 AM   1k30v0pyg32vu  1634868183   414828074          .16           .155       100
9758 09-OCT-12 07.00.10.659 PM   b70xavb9wv27v  1111647858  4256287279         5.937         6.475        93

12 rows selected.

You can clearly see how the HASH_VALUE changed and the execution time as well. If the CHANGE % is above 100% means that execution time decreased, on the other hand CHANGE % below 100% means the execution time increased.

ORAganism blog is having a really good script which searches by SQL_ID.

http://oraganism.wordpress.com/2011/12/14/a-dba_hist_sqlstat-query-that-i-am-very-fond-of/ 


Happy troubleshooting,
Alfred

Same post in Spanish here!