ASM資料庫的一個測試

oracle_ace發表於2008-01-01

本次測試通過新增和刪除磁碟重點關注於"自動資料平衡"的功能
其中有三個檢視是需要關注的:
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, Oracle.  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.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12361284/viewspace-69573/,如需轉載,請註明出處,否則將追究法律責任。

相關文章