分类： Oracle
2008-05-12 17:22:06
本次通过添加和删除磁盘重点关注于"自动数据平衡"的功能
其中有三个视图是需要关注的：
v$asm_operation
v$asm_diskgroup
v$asm_disk
通过查询这三个视图来体现，自动数据平衡的功能。
在ASM实例中：
************
SQL> alter diskgroup alangroup add failgroup fgroup1 disk 'ORCL:MYDISK5' failgroup fgroup2 disk 'ORCL:MYDISK6';
Diskgroup altered.
查看的分配进度:v$asm_operation
------------------------------------
SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation;
GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- -----------
1 REBAL RUN 509 289 725 0
SQL> /
GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- -----------
1 REBAL RUN 505 425 706 0
发现当一个磁盘加入一个磁盘组里面后，数据的平衡会自动化
SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation;
no rows selected
当发现没有任何记录的时候，说明磁盘的自动平衡已经完成。
查看磁盘组的总容量
------------------
SQL> select name,allocation_unit_size,total_mb from v$asm_diskgroup;
NAME ALLOCATION_UNIT_SIZE TOTAL_MB
------------------------------ -------------------- ----------
ALANGROUP 1048576 4470
在ASM数据库中：
**************
首先创建一些数据(表空间，schema,表)
[oracle@orahost01 dbs]$ export ORACLE_SID=asmdb
[oracle@orahost01 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 31 21:58:35 2007
Copyright (c) 1982, 2005, . All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL> create tablespace myspace
2 datafile '+ALANGROUP' size 100M;
Tablespace created.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+ALANGROUP/asmdb/users01.dbf
+ALANGROUP/asmdb/sysaux01.dbf
+ALANGROUP/asmdb/undotbs01.dbf
+ALANGROUP/asmdb/system01.dbf
+ALANGROUP/asmdb/datafile/myspace.268.642808759
SQL> create user alan identified by alan default tablespace myspace;
User created.
SQL> grant connect,resource,dba to alan;
Grant succeeded.
SQL> conn alan/alan
Connected.
SQL> create table mytest
2 as
3 select * from dba_objects;
Table created.
SQL> select count(1) from mytest;
COUNT(1)
----------
49747
现在将一只磁盘从alangroup中拿掉，来看看他是如何来平衡数据的。
首先登陆到ASM实例
[oracle@orahost01 dbs]$ export ORACLE_SID=ASM
[oracle@orahost01 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 31 22:02:03 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL> select name,state from v$asm_disk;
NAME STATE
------------------------------ --------
MYDISK1 NORMAL
MYDISK2 NORMAL
MYDISK3 NORMAL
MYDISK4 NORMAL
MYDISK5 NORMAL
MYDISK6 NORMAL
6 rows selected.
SQL> alter diskgroup alangroup drop disk mydisk4;
Diskgroup altered.
SQL> select name,state from v$asm_disk;
NAME STATE
------------------------------ --------
MYDISK1 NORMAL
MYDISK2 NORMAL
MYDISK3 NORMAL
MYDISK4 DROPPING
MYDISK5 NORMAL
MYDISK6 NORMAL
6 rows selected.
这个时候Oracle再重新分配数据
SQL> /
GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- -----------
1 REBAL RUN 989 339 547 1
SQL> /
GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- -----------
1 REBAL RUN 1013 483 617 0
SQL> /
GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- -----------
1 REBAL RUN 1023 648 637 0
SQL> /
no rows selected
重新分配已经完成。
我们还可以手动进行数据的平衡：
SQL> select name,state from v$asm_disk;
NAME STATE
------------------------------ --------
MYDISK1 NORMAL
MYDISK2 NORMAL
MYDISK3 NORMAL
MYDISK4 HUNG
MYDISK5 NORMAL
MYDISK6 NORMAL
6 rows selected.
-----------------查询failgroup的归属-----------------------
SQL> select label,failgroup from v$asm_disk;
LABEL FAILGROUP
------------------------------- ------------------------------
MYDISK1 FGROUP1
MYDISK2 FGROUP1
MYDISK3 FGROUP2
MYDISK4 FGROUP2
MYDISK5 FGROUP1
MYDISK6 FGROUP2
6 rows selected.
SQL> alter diskgroup alangroup rebalance;
Diskgroup altered.