ORA-15040: diskgroup is incomplete

I received this error while I was trying to bring up cluster on the second in my 2-node cluster.

When I tried to start cluster, ora.asm resource was found in intermediate state.

[root@host02 ~]# crsctl start cluster
CRS-5702: Resource 'ora.evmd' is already running on 'host02'
CRS-2800: Cannot start resource 'ora.asm' as it is already in the INTERMEDIATE state on server 'host02'
CRS-4000: Command Start failed, or completed with errors.

– Since asm was not up, OCR could not be read and hence cluster could not come up

[root@host02 ~]# crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  INTERMEDIATE host02                   OCR not started

– The alert log showed that DATA diskgroup could not be mounted.

2013-09-04 13:55:38.896
[/u01/app/11.2.0/grid/bin/oraagent.bin(28674)]CRS-5019:All OCR locations are on ASM disk groups [DATA], and none of these disk groups are mounted. Details are at “(:CLSN00100:)” in “/u01/app/11.2.0/grid/log/host02/agent/ohasd/oraagent_grid/oraagent_grid.log”.

– I tried to mount DATA diskgroup using SQL but I got ORA-15040 which indicated that disk “0” could not be read

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk "0" is missing from group number "1" 

– When I listed ASM disks on second node, ASMDISK01 was not listed

[root@host02 bin]# oracleasm listdisks
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

– I issued oracleasm scandisks which led to discovery of ASMDISK01

[root@host02 bin]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ASMDISK01"

– Now when I issued oracleasm listdisks, ASMDISK01 was also listed.

[root@host02 bin]# oracleasm listdisks
ASMDISK01
ASMDISK010
ASMDISK011
ASMDISK012
ASMDISK013
ASMDISK014
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
ASMDISK09

— Now I could mount DATA diskgroup successfully

SQL> alter diskgroup data mount;

Diskgroup altered.

Reference:http://oracleinaction.com/ora-15040/

Gather Optimizer statistics, 11g

Gather Optimizer statistics, 11g

In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.

Below are the steps in case if we want to delete & gather schema stats on adhoc basis.

Gathering statistics for all objects in a schema

1. Ensure that the automatic statistics gathering job is scheduled, using the following SQL.

SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Output should be 'TRUE'.

Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.

2. Delete any existing statistics, using the following command from SQLPLUS.

EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');

3. Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:

EXEC DBMS_STATS.SET_PARAM('ESTIMATE_PERCENT','100');

d. Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', DEGREE => 2, CASCADE => TRUE);

Gathering statistics an individual table

exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

with

method_opt => 'FOR ALL COLUMNS SIZE 1'

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

• ESTIMATE_PERCENT: defaults:
•  9i : 100%
• 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
• 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)

• METHOD_OPT: defaults:
• 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
• 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan. 

Automated Maintenance Tasks in Oracle 11g

Oracle Database 11g comes with three predefined automatic maintenance tasks:

• The Automatic Optimizer Statistics Collection task collects statistics for all objects with no or stale statistics.
• The Automatic Segment Advisor task provides advice on which database segments have free space that can be reclaimed.
• Automatic SQL Tuning Advisor task, which examines SQL statement performance and makes SQL profile recommendations to improve the statements.

You can query the DBA_AUTOTASK_OPERATION to find out the names and status of the automatic tasks in your database. as shown in the following example:

Oracle PL/SQL

1 2 3 4 5 6 7

SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  ENABLED   auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED

These tasks are enabled by default.

All automated maintenance tasks are scheduled to run during the Oracle Scheduler maintenance window. The Oracle Scheduler creates a job when the window (period) starts. After the automated task job completed, the Oracle Scheduler automatically drops that job.

You can view all the predefined maintenance windows by querying the DBA_AUTOTASK_SCHEDULE view:

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10

SELECT * FROM dba_autotask_schedule;   WINDOW_NAME          START_TIME    DURATION     -------------------- ------------- ----------- MONDAY_WINDOW        08/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   MONDAY_WINDOW        15/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   MONDAY_WINDOW        22/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0   ...   SUNDAY_WINDOW        21/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0   SUNDAY_WINDOW        28/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::

Oracle PL/SQL

1	EXEC dbms_auto_task_admin.disable;

If you want to enable all the automated tasks, issue the following command:

Oracle PL/SQL

1	EXEC dbms_auto_task_admin.enable;

You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:

Oracle PL/SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

EXEC dbms_auto_task_admin.disable( 'auto optimizer stats collection', NULL, NULL );   SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  DISABLED auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED   EXEC dbms_auto_task_admin.enable( 'auto optimizer stats collection', NULL, NULL );   SELECT client_name, status FROM dba_autotask_operation;   CLIENT_NAME                                                      STATUS   ---------------------------------------------------------------- -------- auto optimizer stats collection                                  ENABLED   auto space advisor                                               ENABLED   sql tuning advisor                                               ENABLED

You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.