ASM FailGroup验证

ASM-FailGroup验证

一、FailGroup有效性验证

创建DiskGroup，在Redundancy选项

• High：至少3块disk，至少3个failgroup，每一个extent存在1主2备，分布在不同failgroup
• Normal：至少2块disk，至少2个failgroup，每一个extent存在1主1备，分布在不同failgroup
• External：至少1块disk，默认1块disk1个failgroup，每一个extent仅存在1主

方式一：asmca

方式二：SQLPLUS命令行

CREATE DISKGROUP fgtestdg NORMAL REDUNDANCY 
FAILGROUP controller1 DISK 'ORCL:FGTEST01', <''> 
FAILGROUP controller2 DISK 'ORCL:FGTEST02', <''>;

1.存储消耗测试
在Normal DG和External DG分别建立4MB的数据文件

create tablespace fg datafile '+fgtestdg' size 4m; create tablespace nofg datafile '+nofgtestdg' size 4m;

在fgtestdg磁盘组上表空间fg的extent分布如下，每一个extent存在一个primary copy和一个mirrored copy，且不在同一块DISK上

在nofgtestdg磁盘组上表空间nofg的extent分布如下，每一个extent仅存在一个primary copy，随机分散在不同DISK上

因此，在存储消耗方面：

• Normal Size = 2 * External Size
• High Size = 3 * External Size

2.存储读写效率测试
表空间：5G大小，创建时间Normal Time = 2 * External Time，删除时间基本一致

SQL> create tablespace fg_ts_c datafile '+fgtestdg' size 5G; Elapsed: 00:00:28.80
 
SQL> SQL> create tablespace nofg_ts_c datafile '+nofgtestdg' size 5G;
Elapsed: 00:00:15.12

SQL> drop tablespace fg including contents and datafiles;
Elapsed: 00:00:04.49



SQL> drop tablespace nofg including contents and datafiles; Elapsed: 00:00:04.58

• 表：100万行记录
• 创建时间：Normal Time = External Time，删除时间基本一致
• Read时间：Normal Time < External Time
• write时间：Normal Time < External Time

CREATE TABLE test_fg tablespace fg_ts_c AS
SELECT rownum AS id
	,to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') AS inc_datetime
	,3 trunc(dbms_random.value(0, 100)) AS random_id
	,dbms_random.string('x', 20) random_string
FROM dual connect BY LEVEL <= 1000000;


Elapsed: 00:01:51.68

CREATE TABLE test_nofg tablespace nofg_ts_c AS
SELECT rownum AS id
	,to_char(sysdate + rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') AS inc_datetime
	,trunc(dbms_random.value(0, 100)) AS random_id
	,dbms_random.string('x', 20) random_string
FROM dual connect BY LEVEL <= 1000000;
 

Elapsed: 00:01:51.10

SQL> select count(*) from test_fg;
Elapsed: 00:00:00.56

SQL> select count(*) from test_nofg;
Elapsed: 00:00:00.72

SQL> update test_fg set RANDOM_STRING ='abcdefg' ; 1000000 rows updated.
Elapsed: 00:00:29.07

SQL> update test_nofg set RANDOM_STRING ='abcdefg' ; 1000000 rows updated.
Elapsed: 00:00:34.10

3.盘头破坏测试

• 分别在之前建立的Normal DG 表空间和External DG表空间上创建测试表

• 破坏前磁盘状态

select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,NAME,FAILGROUP,PATH from v$asm_disk;

• 使用dd备份盘头信息

dd if=/dev/oracleasm/disks/FGTEST02 of=/home/grid/FGTEST02.dd bs=1M count=2
dd if=/dev/oracleasm/disks/NOFGTESTDG02 of=/home/grid/NOFGTESTDG02.dd bs=1M count=2

• 使用dd清除盘头信息

dd if=/dev/zero of=/dev/oracleasm/disks/FGTEST02 bs=4096 count=1
dd if=/dev/zero of=/dev/oracleasm/disks/NOFGTESTDG02 bs=4096 count=1

• 检查磁盘状态

• 数据访问正常

SQL> alter system flush buffer_cache;

SQL> select count(*) from test_fg; COUNT(*)
----------
1000000

SQL> select count(*) from test_nofg; COUNT(*)
----------
1000000

• DG重新mount
  离线表空间，卸载DG

SQL> alter tablespace fg_ts_c offline;
Tablespace altered.

SQL> alter tablespace nofg_ts_c offline;
Tablespace altered.

SQL> alter diskgroup FGTESTDG dismount;
Diskgroup altered.

SQL> alter diskgroup NOFGTESTDG dismount; Diskgroup altered.

正常mount，失败

SQL> alter diskgroup FGTESTDG mount; alter diskgroup FGTESTDG mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "FGTESTDG" cannot be mounted
ORA-15040: diskgroup is incomplete

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

强制mount，Normal成功，External失败

SQL> alter diskgroup FGTESTDG mount force; Diskgroup altered.

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

恢复表空间，Normal业务数据访问恢复成功，External无法访问

SQL> alter tablespace fg_ts_c online; Tablespace altered.

SQL> alter tablespace nofg_ts_c online; alter tablespace nofg_ts_c online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '+NOFGTESTDG/lt/datafile/nofg_ts_c.257.919602769'

SQL> select count(*) from test_fg;

COUNT(*)
----------
1000000

SQL> select count(*) from test_nofg; select count(*) from test_nofg
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '+NOFGTESTDG/lt/datafile/nofg_ts_c.257.919602769'

• 检查磁盘状态

• 恢复磁盘状态

• External未成功mount，回写盘头，mount

dd if=/home/grid/NOFGTESTDG02.dd of=/dev/oracleasm/disks/NOFGTESTDG02 bs=4096 count=1

• Normal强制mount，被放弃，清除盘，重新加入DG，rebalance oracleasm scandisks

oracleasm createdisk FGTEST02 /dev/emcpowere1 alter diskgroup FGTESTDG add disk 'ORCL:FGTEST02';

二、Failgroup优先访问测试

在ASM实例执行

alter system set ASM_PREFERRED_READ_FAILURE_GROUPS='FGTESTDG.FGTEST01';

select instname, failgroup, sum(reads), sum(writes) from v$asm_disk_iostat group by instname,failgroup order by 1,2;