How to downgrade compatibility.rdbms

As you know ASM provide such option as “compatibility”. And you should advancing compatibility every time when you increase binary version for GI and RDBMS. But here there are pitfalls, you can’t to downgrade compatibility. This is confirmed by Oracle and some professionals.

(http://www.pythian.com/blog/oracle-11g-asm-diskgroup-compatibility/)

(http://www.dba-oracle.com/t_11g_new_asm_attributes.htm)

Quite frankly it is not true. We can downgrade compatibility and below I’ll show you how.

As you know ASM support some special file type, like PST, Allocation table, Free space, Password, etc… In our issue we need Attribute directory (file number 9).

1. We should find which AU’s contains file number 9.

SELECT x.disk_kffxp "Disk#",x.xnum_kffxp "Extent",x.au_kffxp "AU",d.name "Disk name"
FROM x$kffxp x, v$asm_disk_stat d
WHERE x.group_kffxp=d.group_number and x.disk_kffxp=d.disk_number and d.group_number=4 and x.number_kffxp=9
ORDER BY 1, 2;

Disk# Extent AU Disk name

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

0 0 52 TEST_DB1
 1 0 52 TEST_DB2

number_kffxp – type of file

group_number – number of group

2. We should determine in which block within AU locate attribute. In my case it is block number 8. But in your case it can be another block. You can find it like this:


for i in {0..100}; do $GRID_HOME/bin/kfed read /path/disk aun=52 blkn=$i |grep rdbms; echo $i; done

Be careful, you need to replace aun to correct number which you can take from previous sql at step 1. Also you need to replace “/path/disk” to existing full path to disk on your system. Pay attention for it because if you input not correct aun and path to disk, bash command mentioned above won’t return you correct result!

$GRID_HOME/bin/kfed read /path/TEST_DB1 aun=52 blkn=8 > /tmp/test_db1_8blkn

$GRID_HOME/bin/kfed read /path/TEST_DB2 aun=52 blkn=8 > /tmp/test_db2_8blkn
...
...
...
kfede[1].name: rdbms ; 0x1a8: length=5
kfede[1].value: 11.2.0.4.0 ; 0x1e8: length=10
...
...
...

3. We should change parameter 11.2.0.4.0, for example to 11.2.0.0.0 in file /tmp/test_db1_8blkn and /tmp/test_db2_8blkn for both disks. After this we should write modified information back to disk.

/opt/oracle/product/grid/11.2.0.4/bin/kfed write /dev/oracleasm/disks/TEST_DB1 aun=52 blkn=8 text=/tmp/test_db1_8blkn

/opt/oracle/product/grid/11.2.0.4/bin/kfed write /dev/oracleasm/disks/TEST_DB2 aun=52 blkn=8 text=/tmp/test_db2_8blkn

Before.

ASMCMD> lsattr -l -G test_dg
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.4.0
compatible.rdbms 11.2.0.4.0
content.type data
disk_repair_time 3.6h
idp.boundary auto
idp.type dynamic
sector_size 512

After.

ASMCMD> lsattr -l -G test_dg
Name Value
access_control.enabled FALSE
access_control.umask 066
au_size 1048576
cell.smart_scan_capable FALSE
compatible.asm 11.2.0.4.0
compatible.rdbms 11.2.0.0.0
content.type data
disk_repair_time 3.6h
idp.boundary auto
idp.type dynamic
sector_size 512

Enjoy!

Advertisements

16 thoughts on “How to downgrade compatibility.rdbms

  1. Raj

    Where did you get the (file number 9). and also the attribute number 8 from.

    SQL> select count(*) from x$kffxp;

    COUNT(*)
    ———-
    7827839
    As I have this many records.

    I wanted to test the above mentioned process to downgrade the parameter.

    Reply
    1. mczimm Post author

      Hi Raj. File number 9 this is special type of ASM files. Did you execute “first sql” I’ve mentioned in this article? Can you show me result?

      Reply
  2. Raj

    My Issue.

    is with this diskgroup.

    SQL> select inst_id,group_number, name, state, type, total_mb/1024 total_gb, free_mb/1024 free_gb, offline_disks from gv$asm_diskgroup where name=’DBFS02_EC11′;

    INST_ID GROUP_NUMBER NAME STATE TYPE TOTAL_GB FREE_GB OFFLINE_DISKS
    ———- ———— —————————— ———– —— ———- ———- ————-
    8 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    6 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    7 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    5 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    4 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    2 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    3 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0
    1 3 DBFS02_EC11 MOUNTED NORMAL 2365.78125 1675.83594 0

    8 rows selected.

    The output for your query with replacing the diskgroup with (3)

    SQL> SELECT x.disk_kffxp “Disk#”,x.xnum_kffxp “Extent”,x.au_kffxp “AU”,d.name “Disk name”
    FROM x$kffxp x, v$asm_disk_stat d
    WHERE x.group_kffxp=d.group_number and x.disk_kffxp=d.disk_number and d.group_number=3 and x.number_kffxp=9
    ORDER BY 1, 2; 2 3 4

    Disk# Extent AU Disk name
    ———- ———- ———- ——————————
    19 0 2 DBFS02_CD_10_EC11CEL13
    21 0 3 DBFS02_CD_08_EC11CEL12
    32 0 3 DBFS02_CD_04_EC11CEL11

    So I am not sure what is #9 here and where did this come from.

    Now the actual issue is:

    The DATABASE_COMPATIBILITY was mistakenly set to 11.2.0.4 and i have to move it to 11.2.0.3 as the database is 11.2.0.3 and it is complaning about this now.

    SQL> SELECT group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

    GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
    —————————————————————————————————————-
    3 DBFS02_EC11 11.2.0.4.0 11.2.0.4.0

    The DATABASE_COMPATIBILITY was mistakenly set to 11.2.0.4 and i have to move it to 11.2.0.3 as the database is 11.2.0.3 and it is complaning about this now.

    SQL> ALTER DISKGROUP &diskgroupname SET ATTRIBUTE ‘compatible.rdbms’ =’11.2.0.3.0′;
    Enter value for diskgroupname: DBFS02_EC11
    old 1: ALTER DISKGROUP &diskgroupname SET ATTRIBUTE ‘compatible.rdbms’ =’11.2.0.3.0′
    new 1: ALTER DISKGROUP DBFS02_EC11 SET ATTRIBUTE ‘compatible.rdbms’ =’11.2.0.3.0′
    ALTER DISKGROUP DBFS02_EC11 SET ATTRIBUTE ‘compatible.rdbms’ =’11.2.0.3.0′
    *
    ERROR at line 1:
    ORA-15032: not all alterations performed
    ORA-15242: could not set attribute compatible.rdbms
    ORA-15244: new compatibility setting less than current [11.2.0.4.0]

    Reply
  3. Raj

    Just in case as per your script:

    SQL> SELECT x.disk_kffxp “Disk#”,x.xnum_kffxp “Extent”,x.au_kffxp “AU”,d.name “Disk name”
    FROM x$kffxp x, v$asm_disk_stat d
    WHERE x.group_kffxp=d.group_number and x.disk_kffxp=d.disk_number and d.group_number=4 and x.number_kffxp=9
    ORDER BY 1, 2; 2 3 4

    Disk# Extent AU Disk name
    ———- ———- ———- ——————————
    10 0 426 DBFS_DG_CD_09_EC11CEL03
    49 0 5 DBFS_DG_CD_11_EC11CEL05
    60 0 369 DBFS_DG_CD_02_EC11CEL07

    Reply
  4. mczimm Post author

    I see. So according your sql query

    SQL> SELECT x.disk_kffxp “Disk#”,x.xnum_kffxp “Extent”,x.au_kffxp “AU”,d.name “Disk name”
    FROM x$kffxp x, v$asm_disk_stat d
    WHERE x.group_kffxp=d.group_number and x.disk_kffxp=d.disk_number and d.group_number=3 and x.number_kffxp=9
    ORDER BY 1, 2; 2 3 4

    which show us the result

    Disk# Extent AU Disk name
    ———- ———- ———- ——————————
    19 0 2 DBFS02_CD_10_EC11CEL13
    21 0 3 DBFS02_CD_08_EC11CEL12
    32 0 3 DBFS02_CD_04_EC11CEL11

    Please execute this bash command

    for i in {0..100}; do $GRID_HOME/bin/kfed read /path/DBFS02_CD_10_EC11CEL13 aun=2 blkn=$i |grep rdbms; echo $i; done

    And show me result.

    Reply
  5. Raj

    ec11db01.test.pncbank.com:/home/revora1->echo $ORACLE_HOME
    /u01/app/11.2.0.4/grid

    ec11db01.test.pncbank.com:/home/revora1->for i in {0..100}; do $ORACLE_HOME/bin/kfed read /path/DBFS02_CD_10_EC11CEL13 aun=2 blkn=$i |grep rdbms; echo $i; done
    0
    1
    2
    3
    4
    5

    99
    100

    Reply
  6. Raj

    ec11db01.test.pncbank.com:/home/revora1->cd $ORACLE_HOME

    ec11db01.test.pncbank.com:/u01/app/11.2.0.4/grid->cd bin

    ec11db01.test.pncbank.com:/u01/app/11.2.0.4/grid/bin->ls -lart kfed
    -rwxr-x–x 1 revora1 oinstall 2404094 Apr 20 10:48 kfed

    ec11db01.test.pncbank.com:/u01/app/11.2.0.4/grid/bin->echo $ORACLE_SID
    +ASM1

    Reply
  7. rujira

    i would like to downgrade compatible
    how to query aun and blkn ?

    SQL> SELECT x.disk_kffxp “Disk#”,x.xnum_kffxp “Extent”,x.au_kffxp “AU”,d.name “Disk name” FROM x$kffxp x, v$asm_disk_stat d WHERE x.group_kffxp=d.group_number and x.disk_kffxp=d.disk_number and d.group_number=1 and x.number_kffxp=9 ORDER BY 1, 2;

    Disk# Extent AU Disk name
    ———- ———- ———- ——————————
    10 0 157725 DATAC1_CD_10_DR01CELADM01
    18 0 3 DATAC1_CD_06_DR01CELADM02
    27 0 4 DATAC1_CD_03_DR01CELADM03

    Reply
  8. rujira

    [grid ~]$ for i in {0..100}; do kfed read ‘o/10.10.XX.XX;10.10.XX.XX/DATAC1_CD_10_dr01celadm01’ aun=4 blkn=$i |grep rdbms; echo $i; done
    0
    1
    2
    3
    4
    5

    99
    100

    Reply
    1. mczimm Post author

      Hi rujira, please execute this command

      for i in {0..100}; do kfed read ‘o/10.10.XX.XX;10.10.XX.XX/DATAC1_CD_10_dr01celadm01′ aun=1 blkn=$i |grep rdbms; echo $i; done

      Reply
      1. Raj

        Hi Mczimm.

        I am sorry I got tied up with the work and resolved the problem in a difeernet way.

        I made the datafile offline as I was able to afford to do that and got the database up first.

        Then I did a copy the datafile within the asm to a diffent diskgroup and dropped the diskgroups and recreated the new one with the correct compatibility and was able to do a rman copy back the diskgroup.

        I can share the complete step by step what I did if you want it.

        Meanwhile I would like to pursue the steps you have suggested too.

        Since I have now have a new diskgroup: I am retracing a couple of stuff here.
        SQL> SELECT group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

        GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
        ———— —————————— ———————————————————— ————————————————————
        3 DBFS02_EC11 11.2.0.4.0 11.2.0.2.0

        As yu can see my diskgorup is still 3:

        SQL> SELECT x.disk_kffxp “Disk#” ,x.xnum_kffxp “Extent”,x.au_kffxp “AU”,d.name “Disk name” FROM x$kffxp x, v$asm_disk_stat d WHERE x.group_kffxp=d.group_number and x.disk_kffxp=d.disk_number and d.group_number=3 and x.number_kffxp=9 ORDER BY 1, 2;

        Disk# Extent AU Disk name
        ———- ———- ———- ——————————
        3 0 2 DBFS02_CD_06_EC11CEL14
        22 0 3 DBFS02_CD_04_EC11CEL12
        63 0 2 DBFS02_CD_08_EC11CEL08

        We might have to have more info. let me know?

  9. Saroj Sahoo

    Hi mczimm,

    I have queried the ASM and find disk which contain the file type 9. But I am not able to find the block no which contain the rdbms Value.

    SQL> SELECT x.disk_kffxp “Disk#”,
    x.xnum_kffxp “Extent”,
    x.au_kffxp “AU”,
    d.name “Disk name”
    FROM x$kffxp x, v$asm_disk_stat d
    WHERE x.group_kffxp=d.group_number
    and x.disk_kffxp=d.disk_number
    and d.group_number=2
    and x.number_kffxp=9
    ORDER BY 1, 2;
    2 3 4 5 6 7 8 9 10
    Disk# Extent AU Disk name
    ———- ———- ———- ——————————
    1 0 10 DBFS02_CD_01_EYE01CELADM01
    16 0 3 DBFS02_CD_04_EYE01CELADM02
    35 0 6 DBFS02_CD_11_EYE01CELADM03

    grid@eysgteddb01t:~$ for i in {0..100}; do $ORACLE_HOME/bin/kfed read ‘o/192.168.10.1;192.168.10.2/DBFS02_CD_01_eye01celadm01’ aun=10 blkn=$i |grep rdbms; echo $i; done
    0
    1
    2
    3
    0.
    .
    .
    .
    100

    Could you please let me know how to find this Block which contain rdbms parameter

    Reply
    1. mczimm Post author

      Hi. Are you sure you have used correct path to disk? Please show me $ORACLE_HOME/bin/kfed read ‘o/192.168.10.1;192.168.10.2/DBFS02_CD_01_eye01celadm01’ aun=10 blkn=0

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s