Flash Disk Replacement due to poor performance in Exadata X3-2 Environment

We have Exadata X3-2 Environment, where one of our Flashdisk was showing,

To identify a poor performance flash disk, use the following command:

CellCLI> LIST PHYSICALDISK WHERE DISKTYPE=flashdisk AND STATUS= 'warning - poor performance' DETAIL

name:                   FLASH_1_0

diskType:               FlashDisk

luns:                   1_0

makeModel:              "Sun Flash Accelerator F40 PCIe Card"

physicalFirmware:       TI35

physicalInsertTime:     2012-10-01T13:45:57-07:00

physicalSerial:         5L0039YS

physicalSize:           93.13225793838501G

slotNumber:             "PCI Slot: 1; FDOM: 0"

status:                 warning - poor performance

This flash disk is in poor performance status.

Recommended Action  

The flash disk has entered poor performance status. A white cell locator LED has been lit to help locate the affected cell. Please replace the flash disk.

If the flash disk is used for flash cache, then flash cache will be disabled on this disk thus reducing the effective flash cache size. If the flash disk is used for flash log, then flash log will be disabled on this disk thus reducing the effective flash log size. If the flash disk is used for grid disks, then Oracle ASM rebalance will automatically restore the data redundancy.

Sun Oracle Exadata Storage Server is equipped with four PCIe cards. Each card has four flash disks (FDOMs) for a total of 16 flash disks. The 4 PCIe cards are present on PCI slot numbers 1, 2, 4, and 5. The PCIe cards are not hot-pluggable such that Exadata Cell must be powered down before replacing the flash disks or cards.

Hence DataCenter Team replaced a flash disk in co-ordination with us (DBA) because the flash disk was in poor performance status.

1. Shut down the cell.

The following procedure describes how to power down Exadata Cell.Run the following command to check if there are offline disks on other cells that are mirrored with disks on this cell:

CellCLI > LIST GRIDDISK ATTRIBUTES name WHERE asmdeactivationoutcome != 'Yes'

If any grid disks are returned, then it is not safe to take the storage server offline because proper Oracle ASM disk group redundancy will not be intact. Taking the storage server offline when one or more grid disks are in this state will cause Oracle ASM to dismount the affected disk group, causing the databases to shut down abruptly.

Inactivate all the grid disks when Oracle Exadata Storage Server is safe to take offline using the following command:

CellCLI> ALTER GRIDDISK ALL INACTIVE

The preceding command will complete once all disks are inactive and offline. Depending on the storage server activity, it may take several minutes for this command to complete.

Verify all grid disks areINACTIVEto allow safe storage server shut down by running the following command.

CellCLI> LIST GRIDDISK

If all grid disks areINACTIVE, then the storage server can be shutdown without affecting database availability.

Stop the cell services using the following command:

CellCLI> ALTER CELL SHUTDOWN SERVICES ALL

Shut down the cell.

2. Replace the failed flash disk based on the PCI number and FDOM number.

3. Power up the cell. The cell services will be started automatically.

4.Bring all grid disks are online using the following command:

CellCLI> ALTER GRIDDISK ALL ACTIVE

5. Verify that all grid disks have been successfully put online using the following command:

CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus

        Wait until asmmodestatus from SYNCING to ONLINE for all grid disks. 
        The following is an example of the output:

         CellCLI> LIST PHYSICALDISK WHERE DISKTYPE=flashdisk
         FLASH_1_0       FL0034E1        normal
         FLASH_1_1       FL0034LC        normal
         FLASH_1_2       FL0034LL        normal
         FLASH_1_3       FL0034KL        normal
         FLASH_2_0       FL00339T        normal
         FLASH_2_1       FL00330H        normal
         FLASH_2_2       FL0032HH        normal
         FLASH_2_3       FL0033DA        normal
         FLASH_4_0       FL0033SS        normal
         FLASH_4_1       FL00347V        normal
         FLASH_4_2       FL0034PB        normal
         FLASH_4_3       FL0034PS        normal
         FLASH_5_0       FL0032JZ        normal
         FLASH_5_1       FL0034KC        normal
         FLASH_5_2       FL0035VV        normal
         FLASH_5_3       FL00365H        normal

CellCLI> LIST GRIDDISK ATTRIBUTES name, asmmodestatus
         DATA_DR_CD_00_inblrdrceladm03   ONLINE
         DATA_DR_CD_01_inblrdrceladm03   SYNCING
         DATA_DR_CD_02_inblrdrceladm03   ONLINE
         DATA_DR_CD_03_inblrdrceladm03   ONLINE
         DATA_DR_CD_04_inblrdrceladm03   ONLINE
         DATA_DR_CD_05_inblrdrceladm03   ONLINE
         DBFS_DG_CD_02_inblrdrceladm03   ONLINE
         DBFS_DG_CD_03_inblrdrceladm03   ONLINE
         DBFS_DG_CD_04_inblrdrceladm03   ONLINE
         DBFS_DG_CD_05_inblrdrceladm03   ONLINE
         RECO_DR_CD_00_inblrdrceladm03   ONLINE
         RECO_DR_CD_01_inblrdrceladm03   ONLINE
         RECO_DR_CD_02_inblrdrceladm03   ONLINE
         RECO_DR_CD_03_inblrdrceladm03   ONLINE
         RECO_DR_CD_04_inblrdrceladm03   ONLINE
         RECO_DR_CD_05_inblrdrceladm03   ONLINE

Oracle ASM synchronization is only complete when all grid disks show attribute asmmodestatus=ONLINE. Before taking another storage server offline, Oracle ASM synchronization must complete on the restarted Oracle Exadata Storage Server. If synchronization is not complete, then the check performed on another storage server will fail.

The new flash disk will be automatically used by the system. If the flash disk is used for flash cache, then the effective cache size will increase. If the flash disk is used for grid disks, then the grid disks will be recreated on the new flash disk. If those gird disks were part of an Oracle ASM disk group, then they will be added back to the disk group and the data will be rebalanced on them based on the disk group redundancy and asm_power_limit parameter.

Oracle ASM rebalance occurs when dropping or adding a disk. To check the status of the rebalance, do the following:

• • The rebalance operation may have been successfully run. Check the Oracle ASM alert logs to confirm

• • The rebalance operation may be currently running. Check the GV$ASM_OPERATION view to determine if the rebalance operation is still running.

• • The rebalance operation may have failed. Check the GV$ASM_OPERATION.ERROR view to determine if the rebalance operation failed.

• • Rebalance operations from multiple disk groups can be done on different Oracle ASM instances in the same cluster if the physical disk being replaced contains ASM disks from multiple disk groups. One Oracle ASM instance can run one rebalance operation at a time. If all Oracle ASM instances are busy, then rebalance operations will be queued.

Doc ID Referred :

HALRT-02011: Flash disk poor performance status (Doc ID 1206015.1)

Steps to shut down or reboot an Exadata storage cell without affecting ASM (Doc ID 1188080.1)

About OPTIMIZER_INDEX_CACHING & OPTIMIZER_INDEX_COST_ADJ

Oracle's Optimizer_index_caching and Optimizer_index_cost_adj parameters are the most 
important parameters among all the init.ora (SPFILE) parameters. The Cost based optimizer 
extensively uses these 2 parameters values during the cost calculation for arriving the optimum 
execution plan.

OPTIMIZER_INDEX_CACHINGThis parameter controls the costing of an index probe in conjunction with a nested loop.  The range of 
values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer 
cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list 
iterators.  A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache 
and the optimizer adjusts the cost of an index probe or nested loop accordingly. Use caution when using 
this parameter because execution plans can change in favor of index caching.

OPTIMIZER_INDEX_COST_ADJ
This parameter can be used to adjust the cost of index probes.  The range of values is 1 to 10000.  The 
default value is 100, which means that indexes are evaluated as an access path based on the normal 
costing model.  A value of 10 means that the cost of an index access path is one-tenth the normal cost 
of an index access path.

It is very easy to understand the impact of the first parameter, OPTIMIZER_INDEX_CACHING. It 
tells us that percentage of index blocks in the buffer cache affect the cost calculation.
However, the 2nd parameter, OPTIMIZER_INDEX_COST_ADJ confuses many DBAs and developers 
because the range of values accepted is 1 through 10000. Many experts interpret the meaning in 
a different way.  Any change in OPTIMIZER_INDEX_COST_ADJ results in table access cost being 
scaled down (up) to the current value divided by 100.

In other words, I would say, if the value is less than 100, then the Optimizer may choose  indexed 
access path OR if the value is equal to (greater than 100), then Optimizer may choose FULL TABLE 
SCAN access path.

You can also interpret the meaning in this way: less value of OPTIMIZER_INDEX_COST_ADJ 
indicates single block read time is cheaper;   more value of OPTIMIZER_INDEX_COST_ADJ indicates 
multi block read time is cheaper.

Optimizer_Index_Cost_Adj values …………………………………………………………………..>
1….10..20..30……..………………………..100…200…..500……1000…4000….8000……10000
<Consider Index Access …>                 <Consider FULL TABLE SCAN ………………………>         

Among the 2 parameters, Optimizer_Index_Cost_Adj is more aggressive than 
OPIMIZER_INDEX_CACHING.Note that all tests were conducted on 9iR2 where the system statistics was not collected.
First let us set up 2 tables to test the theory.

SQL> desc sales
Name                Null?                    Type
----------------- --------                 ------------------
CUST_ID          NOT NULL         NUMBER(12)
STATE              NOT NULL         CHAR(2)
SALE_YEAR                               NUMBER(4)
SALE_MONTH                            NUMBER(2)
PROD_ID                                   NUMBER
QTY                                          NUMBER
AMT                                          NUMBER
COMM_1                                   VARCHAR2(50)
COMM_2                                   VARCHAR2(200)


SQL> select /*+ full(a) parallel(a,16) */ count(*) from sales a ;

COUNT(*)
----------
14,336,064 -----------à Big table to test different values.
SQL> desc state
Name                       Null?            Type
--------------------- --------         ------------------
STATEID                NOT NULL    CHAR(2)
NAME                                        VARCHAR2(30)
SOMETXT                                  VARCHAR2(3900)
9 rows there are in the table.
Case 1
Let us first see what will be the cost for a query with default values of 0 and 100 for 
optimizer_index_caching and optimizer_index_cost_adj respectively.

SQL> alter session set optimizer_index_caching = 0;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 100 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id  | Operation                                         |  Name         | Rows   | Bytes   | Cost  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                    |   573K |    15M  | 45551 |
|   1 |  NESTED LOOPS                                |                    |   573K |    15M  | 45551 |
|   2 |   TABLE ACCESS BY INDEX ROWID      | STATE         |     1    |    21     |     1    |
|*  3 |    INDEX UNIQUE SCAN                     | STATE_PK    |     1    |             |           |
|*  4 |   TABLE ACCESS FULL                       | SALES          |   573K |  4480K | 45550 |
------------------------------------------------------------------------------------------------------

So, the total cost of the query is 45,551 IO. Note also that when CPU costing is off, then the COST 
is nothing but total number of IO.

This is our base line data that will be compared with the remaining tests.Case 2
I changed the OPTIMIZER_INDEX_COST_ADJ to 10 but kept the “default” value for 
OPTIMIZER_INDEX_CACHING.

SQL> alter session set optimizer_index_caching = 0 ;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 10 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         |  Name              | Rows  | Bytes    | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                         |   573K|    15M   | 37683   (1) |
|   1 |  NESTED LOOPS                                |                         |   573K|    15M   | 37683   (1) |
|   2 |   TABLE ACCESS BY INDEX ROWID    | STATE               |     1    |    21     |     2  (50)    |
|*  3 |    INDEX UNIQUE SCAN                    | STATE_PK         |     1    |             |                   |
|   4 |   TABLE ACCESS BY INDEX ROWID    | SALES               |   573K | 4480K | 37682   (1) |
|*  5 |    INDEX RANGE SCAN                      | SALES_IDX_2    |   573K |           | 30095   (0) |
----------------------------------------------------------------------------------------------------------------

The Query plan is completely changed – from full table scan to indexed access on the SALES table.  
The total cost of the query came down to 37,683 from 45,551, the “TABLE ACCESS BY INDEX 
ROWID” cost is 37,682 that includes the cost of “ INDEX RANGE SCAN “ for SALES_IDX_2 30,095.Case 3
In real life situation some amount of index leaf blocks are always cached in the memory.
Now I tell Oracle that 50 % of index blocks are cached in the SGA by changing the 
OPTIMIZER_INDEX_CACHING parameter.

Let us study the cost of the query.

SQL> alter session set optimizer_index_caching = 50 ;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 10 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
| Id  | Operation                                       |  Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT                       |                      |   573K|    15M| 36207   (0)  |
|   1  |  NESTED LOOPS                             |                      |   573K|    15M| 36207   (0)  |
|   2  |   TABLE ACCESS BY INDEX ROWID | STATE           |     1   |    21   |     2  (50)    |
|*  3 |    INDEX UNIQUE SCAN                  | STATE_PK      |     1   |           |                   |
|   4  |   TABLE ACCESS BY INDEX ROWID | SALES           |   573K|  4480K| 36206   (0) |
|*  5 |    INDEX RANGE SCAN                    | SALES_IDX_2 |   573K|          | 15344   (0) |
--------------------------------------------------------------------------------------------------------------


In Case -2 , the “INDEX RANGE SCAN” for SALES_IDX_2 was 30095, after changing the parameter 
OPTIMIZER_INDEX_CACHING to 50, it came to 15344, just half of what it was before.
The “TABLE ACCESS BY INDEX ROWID” for SALES table came down to 36206 from 37682.
Finally the total cost came down to 36,207 from 37,683.Case 4
If you further increase the parameter OPTIMIZER_INDEX_CACHING, then the cost of INDEX SCAN 
will further come down.
Let us test this concept with a new value, 90.  

SQL> alter session set optimizer_index_caching = 90 ;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 10 ;

Session altered.

SQL> explain plan for
2  select     a.cust_id , b.name
3  from   sales a, state b
4  where  a.state = b.stateid and
5         b.stateid  = 'GA'  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
| Id  | Operation                                   |  Name             | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                     |                       |   573K|    15M | 34980   (0)|
|    1 |  NESTED LOOPS                           |                       |   573K|    15M | 34980   (0)|
|    2 |   TABLE ACCESS BY INDEX ROWID | STATE            |     1   |    21    |     2  (50)  |
|*  3 |    INDEX UNIQUE SCAN                | STATE_PK        |     1   |           |                  |
|    4 |   TABLE ACCESS BY INDEX ROWID | SALES            |   573K|  4480K| 34979   (0)|
|*  5 |    INDEX RANGE SCAN                  | SALES_IDX_2  |   573K|           |  3069   (0) |
--------------------------------------------------------------------------------------------------------

The cost of INDEX RANGE SCAN has been reduced to 3069.

In conclusion

One thing you should remember, the above tests and various costs obtained did not tell you which 
query will run faster.
I just demonstrated the impact of changing those 2 parameters.

There is no rule of thumb to fix those 2 values. How ever, One thing is sure that the default value 
0 and 100 for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ respectively are set 
for Data Warehouse System.

If your system is OLTP, then these 2 parameters values should be changed.  I would test the 
system with different values before choosing the correct values.

Setting 90 to OPTIMIZER_INDEX_CACHING and 10 (or 15) to OPTIMIZER_INDEX_COST_ADJ will 
perform good for OLTP system.

If you collect system statistics, then these 2 parameters play less importance role in deciding the 
execution plan.

Oracle Database Appliance / Exadata Database / SuperCluster Machine 3D Tour

For ODA

Click Here for Oracle Database Appliance 3-D Tour

For Exadata

Click Here for Oracle Exadata Database Machine 3-D Tour (X2-2)
          and for Oracle Exadata Database Machine 3-D Tour (X2-8)

For SuperCluster

Click Here for Oracle SuperCluster T5-8 3D Tour
           and for Oracle SuperCluster M6-32 3D Tour

Above Links require JDK Installed,

So Enjoy Exadata / SuperCluster Tour...

killing expdp/impdp job.

some time it is really necessary  to terminate  a expdb/impdp jobs because of any valid reason which impact database.

here are the steps for quick references, how can we terminate the expdp/impdp job,

step 1:  take the job_name from below views
select * from USER_DATAPUMP_JOBS;
select * from DBA_DATAPUMP_SESSIONS;

step 2: Attach to that job_name which you find out in step1.

oracle@aa: /u02/backups # impdp system/***** attach=test_DUMP11(job_name)

step 3:  stop job immediate

impdp> STOP_JOB=IMMEDIATE
impdp>kill_job

- Manish Nashikkar

Disable ntpd and make ctssd active – 11gR2 RAC

We have two options to synchronize time on our clusters,

1. Using NTP or

2. Using Oracle Cluster Synchronization Services Deamon (ctssd)

By default, if a time sync software is running like NTP, ctssd will run in observer mode. In this tutorial we will see how to start ctssd in active mode

1. Check if ctssd is active or not

[grid@rac1 ~]$ crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.
[grid@rac2 ~]$ crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

We can see that CTSSD is in observer mode

2. De-configure NTP.

#crsctl stop crs
#/sbin/service ntpd stop
#chkconfig ntpd off
#chkconfig --list ntpd
#mv /etc/ntp.conf /etc/ntp.conf.original
#rm /var/run/ntpd.pid

3. Start the cluster on all nodes

#crsctl start cluster

4. Check that ctssd automatically starts in active mode as

CRS-4701: The Cluster Time Synchronization Service is in Active mode.
CRS-4702: Offset (in msec): 0
[root@rac2 etc]# crsctl check ctss
CRS-4701: The Cluster Time Synchronization Service is in Active mode.
CRS-4702: Offset (in msec): 0

Hourly/Daily Archive generation

The below query comes handy to understand the archivelog generation of an Oracle database on an hourly /daily basis, per thread – in case of RAC databases.Archivelog generation on a daily basis:

set pages 1000select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_loggroup by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Archive log generation on an hourly basis:

set pages 1000select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives from v$archived_loggroup by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Sample output:

HOUR                   THREAD#         MB   ARCHIVES
------------------- ---------- ---------- ----------
2013 08 20 12:00:00          1      31268        339
2013 08 20 13:00:00          1       4994         55
2013 08 20 14:00:00          1       4412         48
2013 08 20 15:00:00          1       4805         52
2013 08 20 16:00:00          1       3364         37
2013 08 20 17:00:00          1         22          1
2013 08 20 21:00:00          1          9          1

Also, the following script is useful to find the archivelog switches on an hourly basis that happened in the past one week, I got this from http://kubilaykara.blogspot.com/2008/02/redo-log-generation.html and is quite an useful one.

SELECT to_date(first_time) DAY,to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"fromv$log_historywhere to_date(first_time) > sysdate - 8 -- or else change it you want for more days to viewGROUP byto_char(first_time,'YYYY-MON-DD'), to_date(first_time)order by to_date(first_time)
/

Sample output:

DAY 00 01 02 03 04 05 06 09 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -
01-NOV-13 0 1 1 0 1 0 1 0 1 4 1 1 0 1 0 1 1 0 1 1 1 1 1 1
02-NOV-13 0 1 1 1 1 0 1 1 1 1 0 1 1 7 1 1 1 1 1 2 1 1 1 1
03-NOV-13 1 2 2 1 1 1 1 1 1 1 1 2 1 1 1 1 1 2 1 1 2 1 1 2
04-NOV-13 1 1 8 1 7 2 1 1 1 2 1 1 2 1 2 1 2 1 2 1 2 1 2 2
05-NOV-13 2 1 2 1 2 2 1 2 1 2 2 1 2 2 1 2 2 2 1 2 2 2 2 2
06-NOV-13 2 1 2 2 2 2 1 2 2 2 2 2 1 2 2 1 0 2 0 0 0 0 1 0
09-NOV-13 0 0 1 7 0 0 1 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0

Diskgroup is not compatible for database usage

At the time of database creation using dbca on X3 Exadata Box, found this compatibility mismatch issue.

Diskgroup "+DATA_DR" is not compatible for database usage. Database compatible attribute of diskgroup should be the same or lower than the database compatible initialization parameter value.Database compatible parameter is set to "11.2.0.0.0"and the diskgroup database compatibility attribute is set to "11.2.0.2".  

The error was easy to understand as compatible parameter set in the database initialization parameter was lower than the compatible rdbms set for the diskgroup in ASM. The db compatible parameter was set to 11.2.0.0.0 whereas for the diskgroup compatible rdbms was set to 11.2.0.2.0.

DBCA (choose General Purpose) doesn’t provide any screen wherein we can change the parameter value. But as we want to create the database using dbca, we need to change the parameter value in the template stored in ORACLE_HOME/assistants/dbca/templates

[oracle@xxxxx ~]$ cd {ORACLE_HOME}/assistants/dbca/templates
[oracle@xxxxx templates]$ vi General_Purpose.dbc 

         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
         <initParam name="compatible" value="11.2.0.2.0"/>

         <initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>

Edit above compatible parameter to 11.2.0.2.0 and save the file and start creating database using dbca, you won't be faced this issue again.

Thank you,
Manish Nashikkar