Oracle 管理ASM实例（oracle asm failgroup）

一：查看ASM磁盘及磁盘组状态

SQL> select * from v$asm_disk;

SQL> select * from v$asm_diskgroup;

二：创建external 冗余磁盘组,添加，删除磁盘，删除磁盘的时候需要指定磁盘的name而不是path

SQL> create diskgroup FRA external redundancy disk '/dev/asm-disk4';

Diskgroup created.

SQL> alter diskgroup fra add disk '/dev/asm-disk5' rebalance power 10;

Diskgroup altered.

SQL> select name,failgroup,path from v$asm_disk;

NAME FAILGROUP PATH

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

DATA_0000 DATA_0000 /dev/asm-disk1

DATA_0001 DATA_0001 /dev/asm-disk3

DATA_0002 DATA_0002 /dev/asm-disk2

FRA_0000 FRA_0000 /dev/asm-disk4

FRA_0001 FRA_0001 /dev/asm-disk5

/dev/asm-disk6

/dev/asm-disk7

SQL> alter diskgroup fra drop disk 'FRA_0000' rebalance power 10;

Diskgroup altered.

三：创建normal redundancy磁盘组

SQL> conn /as sysasm

Connected.

SQL> create diskgroup fra normal redundancy

failgroup fg1 disk '/dev/asm-disk4','/dev/asm-disk5'

failgroup fg2 disk '/dev/asm-disk6','/dev/asm-disk7'

attribute 'compatible.rdbms'='11.2','compatible.asm'='11.2';

Diskgroup created.

SQL> select name, total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup;

NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB

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

DATA 61440 54873 0 18291

FRA 81920 81592 20480 30556

四：failgroup测试；FAILGROUP是用于将磁盘分组，以保证丢失任何一组FAILGROUP磁盘，数据还是完整的,多用于多阵列，通过ASM来完成冗余的环境！

1：查看FRA磁盘组中failgroup信息及磁盘状态

SQL> select name,path,failgroup,mount_status from v$asm_disk where group_number=2;

NAME PATH FAILGROUP MOUNT_STATUS

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

FRA_0003 /dev/asm-disk7 FG2 CACHED

FRA_0002 /dev/asm-disk6 FG2 CACHED

FRA_0000 /dev/asm-disk4 FG1 CACHED

FRA_0001 /dev/asm-disk5 FG1 CACHED

2：在rdbms实例中创建表空间，建表并插入数据，收集表统计信息

SQL> create tablespace test01 datafile '+FRA';

Tablespace created.

ASMCMD> pwd

+fra/db/datafile

ASMCMD> ls

TEST01.256.800622493

SQL> create table t1 tablespace test01 as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYS','T1');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

COUNT(*)

----------

74501

3：删除udev相关规则，重启数据库实例和ASM实例，验证数据是否存在

SQL> conn /as sysasm

Connected.

SQL> alter diskgroup fra mount;

alter diskgroup fra 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 "2"

ORA-15042: ASM disk "0" is missing from group number "2"

SQL> alter diskgroup fra mount force;

Diskgroup altered.

SQL> select name,path,failgroup,mount_status from v$asm_disk;

NAME PATH FAILGROUP MOUNT_STATUS

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

FRA_0000 FG1 MISSING

FRA_0001 FG1 MISSING

FRA_0002 /dev/asm-disk6 FG2 CACHED

FRA_0003 /dev/asm-disk7 FG2 CACHED

DATA_0000 /dev/asm-disk1 DATA_0000 CACHED

DATA_0002 /dev/asm-disk2 DATA_0002 CACHED

DATA_0001 /dev/asm-disk3 DATA_0001 CACHED

7 rows selected.

SQL> conn /as sysdba

Connected.

SQL> select count(*) from t1;

COUNT(*)

----------

74501

五：删除ASM磁盘组，默认磁盘故障条件下，经过12960秒即3.6个小时后自动删除；删除磁盘组之前需要保证无数据存放在磁盘组上且无到该磁盘组的active连接

[grid@localhost ~]$ tail -f /u01/app/grid/diag/asm/+asm/+ASM/trace/alert_+ASM.log

ORA-15062: ASM disk is globally closed

Thu Nov 29 12:00:53 2012

WARNING: Disk 0 (FRA_0000) in group 2 will be dropped in: (12960) secs on ASM inst 1

WARNING: Disk 1 (FRA_0001) in group 2 will be dropped in: (12960) secs on ASM inst 1

Thu Nov 29 12:00:58 2012

asm实例：

SQL> select path,name,repair_timer from v$asm_disk where group_number=2;

PATH NAME REPAIR_TIMER

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

FRA_0000 12960

FRA_0001 12960

/dev/asm-disk6 FRA_0002 0

/dev/asm-disk7 FRA_0003 0

SQL> alter diskgroup fra online disk 'FRA_0000';

Diskgroup altered.

SQL> alter diskgroup fra online disk 'FRA_0001';

Diskgroup altered.

SQL> select name,path,failgroup,mount_status from v$asm_disk;

NAME PATH FAILGROUP MOUNT_STATUS

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

FRA_0002 /dev/asm-disk6 FG2 CACHED

FRA_0003 /dev/asm-disk7 FG2 CACHED

DATA_0000 /dev/asm-disk1 DATA_0000 CACHED

DATA_0002 /dev/asm-disk2 DATA_0002 CACHED

DATA_0001 /dev/asm-disk3 DATA_0001 CACHED

FRA_0001 /dev/asm-disk5 FG1 CACHED

FRA_0000 /dev/asm-disk4 FG1 CACHED

SQL> drop diskgroup fra;

drop diskgroup fra

*

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15053: diskgroup "FRA" contains existing files

rdbms实例：

SQL> drop tablespace test01 including contents;

Tablespace dropped.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

asm实例：

SQL> drop diskgroup fra including contents;

Diskgroup dropped.

rdbms实例：

SQL> startup

ORACLE instance started.

Total System Global Area 313860096 bytes

Fixed Size 1344652 bytes

Variable Size 192940916 bytes

Database Buffers 113246208 bytes

Redo Buffers 6328320 bytes

Database mounted.

Database opened.