Moving Oracle database SPfile from 4k to 512b

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

SSD Arrays and using 4k blocks

Some SSD arrays will perform badly or not work at all when used for Database files.
The example below was used with a Violin Memory Array, but the same is can be any of the new SSD vendors.
Note: The solution below will only work on Linux with asmlib and will not work on Solaris 11.1 and db  11.2.03.
For Solaris you have to make sure to use 512 (sector) in vilion and use 4k blocks when installing the Oracle db.
How to move the spfile from 4k to 512b after ASM is installed and configured

SQL> CREATE DISKGROUP DATA
  2  EXTERNAL REDUNDANCY
  3  DISK '/path/disk1','/path/disk2','/path/disk3'
  4  ATTRIBUTE
  5   'au_size'='64M',
  6   'sector_size'='4096',
  7   'compatible.asm' = '11.2',
  8   'compatible.rdbms' = '11.2';
  • Use asmca To configure the SP (512b) ASM group
  • asmcmd -p spget
    +DATA/asm/asmparameterfile/registry.253.849973443
  • sqlplus / as sysdba
  • create pfile=’/usr/grid/spfile-neto.ora’ from spfile=’+DATA/asm/asmparameterfile/registry.253.849973443′;

How and why 4k SPfile will not work

Helpfull asm commends

Get disk sector or block size

select name,state,sector_size,block_size from v$asm_diskgroup;

NAME                           STATE       SECTOR_SIZE BLOCK_SIZE
------------------------------ ----------- ----------- ----------
DATA1                          MOUNTED             512       4096

Get disk info

select MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,NAME,PATH,TOTAL_MB,FREE_MB from v$asm_disk;

MOUNT_S HEADER_STATU MODE_ST NAME
------- ------------ ------- ------------------------------
PATH                                                                                                                                                                                                                                                               TOTAL_MB    FREE_MB
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
CLOSED  CANDIDATE    ONLINE
/dev/rdsk/c0t6001B970285E503D285E503C682A7F14d0                                                                                                                                                                                                                           0          0

CLOSED  CANDIDATE    ONLINE
/dev/rdsk/c0t6001B970285E503D285E503CE6A5021Dd0                                                                                                                                                                                                                           0          0

CACHED  MEMBER       ONLINE  BH_DATA_0000
/dev/rdsk/c0t6001B970285E503D285E503C682A7F14d0s0                                                                                                                                                                                                                   1048565     300723

CACHED  MEMBER       ONLINE  BH_FRA_0000
/dev/rdsk/c0t6001B970285E503D285E503CE6A5021Dd0s0                                                                                                                                                                                                                   1042000     804836

Leave a Reply

avatar
3000
  Subscribe  
Notify of