ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ASM数据库的一个测试

ASM数据库的一个测试

原创 Linux操作系统 作者:oracle_ace 时间:2008-01-01 22:48:26 0 删除 编辑

本次测试通过添加和删除磁盘重点关注于"自动数据平衡"的功能
其中有三个视图是需要关注的:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    284
  • 访问量
    796850