ASM管理实践

ASM管理实践

2007-12-19 22:15

ASM管理 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 安装ASMLib.     ASMLib是操作系统与数据库之间的一个接口. ASM instance的认证 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     与正常数据库一样.要访问ASM实例有两种方法认证.     1.操作系统认证.用户属于OSDBA组     2.密码文件认证. ASM相关参数 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     INSTANCE_TYPE     ASM_POWER_LIMIT     ASM_DISKSTRING     ASM_DISKGROUPS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 准备: 添加一块新硬盘,准备为ASM磁盘使用. 先创建LVM,再在LVM基础之上创建ASM root: fdisk /dev/sdb 创建sdb1,将类型更改为lvm文件类型 pvcreate /dev/sdb1 vgcreate /dev/sdb1 oradata lvcreate -L4G -nlv_asm01 oradata /bin/chgrp -R disk /dev/mapper /dev/oradata /bin/chmod -R 670 /dev/mapper /dev/oradata 安装ASMLib,此处与通常情况不太一样,我在这里不创建ASM磁盘 rpm -Uvh .. /etc/init.d/oracleasm configure /etc/init.d/oracleasm status /etc/init.d/oracleasm disable /etc/init.d/oracleasm enable /etc/init.d/oracleasm listdisks 下面准备创建ASM实例: 1. 要使用ASM,必须先配置启动ORACLE Cluster Synchronization Service(css). 查看是否已经有css 1: test@linux:~> ps -ef|grep d.bin 2: oracle 4446 4354 0 Aug24 ? 00:00:00 /oracle/app/10.1.0.2/bin/ocssd.bin 3: 这样就表示css已经在运行了。 4: 也可以查看/etc/inittab最后一行看是否为： 5: h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null 6: 如果没有css服务则需要添加 root用户执行: 1: std:/opt/oracle/app/10.2.0.1/bin # ./localconfig add 2: /etc/oracle does not exist. Creating it now. 3: Successfully accumulated necessary OCR keys. 4: Creating OCR keys for user 'root', privgrp 'root'.. 5: Operation successful. 6: Configuration for local CSS has been initialized 7: 8: Adding to inittab 9: Startup will be queued to init within 90 seconds. 10: Checking the status of new Oracle init process... 11: Expecting the CRS daemons to be up within 600 seconds. 12: CSS is active on these nodes. 13: std 14: CSS is active on all nodes. 15: Oracle CSS service is installed and running under init(1M) 2.参数与密码文件 1: $ORACLE_HOME/dbs/init+ASM.ora 2: 3: asm_diskstring='/dev/oradata/*' 4: asm_power_limit=1 5: instance_type='asm' 6: remote_login_passwordfile=shared 7: background_dump_dest=/opt/oracle/+ASM/bdump 8: core_dump_dest=/opt/oracle/+ASM/cdump 9: user_dump_dest=/opt/oracle/+ASM/udump 10: shared_pool_size=40m 11: large_pool_size=12m 创建密码 1: orapwd file=$ORACLE_HOME/dbs/pwd+ASM password=admin 3.启动实例 1: export ORACLE_SID=+ASM 2: sqlplus / as sysdba 3: create spfile from pfile; 4: startup nomount 至此,ASM实例创建完成,下面注重ASM磁盘: 1: SQL> select name from v$fixed_table where name like 'V%ASM%'; 2: 3: NAME 4: ------------------------------ 5: V$ASM_TEMPLATE 6: V$ASM_ALIAS 7: V$ASM_FILE 8: V$ASM_CLIENT 9: V$ASM_DISKGROUP 10: V$ASM_DISKGROUP_STAT 11: V$ASM_DISK 12: V$ASM_DISK_STAT 13: V$ASM_OPERATION 14: 15: 9 rows selected. 16: 17: select * from v$asm_diskgroup; 18: 19: no rows selected 20: 21: SQL> show parameter diskstring 22: 23: NAME TYPE VALUE 24: ------------------------------------ ----------- ------------------------------ 25: asm_diskstring string /dev/oradata/* 26: SQL> 查看可用ASM磁盘 1: SQL> select NAME,HEADER_STATUS,MOUNT_STATUS,STATE,TOTAL_MB,FREE_MB,path from v$asm_disk; 2: 3: NAME HEADER_STATU MOUNT_S STATE TOTAL_MB FREE_MB PATH 4: -------------------- ------------ ------- -------- ---------- ---------- ------------------------------ 5: CANDIDATE CLOSED NORMAL 4096 0 /dev/oradata/lv_asm01 创建DISKGROUP 外部冗余 1: create diskgroup dgroup01 external redundancy disk '/dev/oradata/lv_asm01'; 查看可用asm磁盘 1: SQL> select NAME,HEADER_STATUS,MOUNT_STATUS,STATE,TOTAL_MB,FREE_MB,path from v$asm_disk 2: 3: NAME HEADER_STATU MOUNT_S STATE TOTAL_MB FREE_MB PATH 4: -------------------- ------------ ------- -------- ---------- ---------- ------------------------------ 5: DGROUP01_0000 MEMBER CACHED NORMAL 4096 4046 /dev/oradata/lv_asm01 查看ASM磁盘组 1: SQL> select group_number gno,name,block_size,state,type,total_mb,free_mb,usable_file_mb,unbalanced from v$asm_diskgroup; 2: 3: GNO NAME BLOCK_SIZE STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB U 4: ---- ---------- ---------- ----------- ------ ---------- ---------- -------------- - 5: 1 DGROUP01 4096 MOUNTED EXTERN 4096 4096 4096 N 创建数据库 1: SQL> show parameter spfile; 2: 3: NAME TYPE VALUE 4: ------------------------------------ ----------- ------------------------------ 5: spfile string +DGROUP01/orcl/spfileorcl.ora 6: 7: SQL> select name from v$controlfile; 8: 9: NAME 10: -------------------------------------------------------------------------------- 11: +DGROUP01/orcl/controlfile/current.256.639064571 12: 13: SQL> select name from v$datafile; 14: 15: NAME 16: -------------------------------------------------------------------------------- 17: +DGROUP01/orcl/datafile/system.260.639064589 18: +DGROUP01/orcl/datafile/undotbs1.261.639064633 19: +DGROUP01/orcl/datafile/sysaux.262.639064639 20: +DGROUP01/orcl/datafile/users.264.639064683 21: 22: SQL> select * from v$logfile 23: 24: GROUP# STATUS TYPE MEMBER IS_ 25: ---------- ------- ------- -------------------------------------------------- --- 26: 1 ONLINE +DGROUP01/orcl/onlinelog/group_1.257.639064573 NO 27: 2 ONLINE +DGROUP01/orcl/onlinelog/group_2.258.639064579 NO 28: 3 ONLINE +DGROUP01/orcl/onlinelog/group_3.259.639064583 NO 查看ASM DISK中可用空间 1: SQL> select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup; 2: 3: NAME STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB OFFLINE_DISKS 4: ------------------------------ ----------- ------ ---------- ---------- -------------- ------------- 5: DGROUP01 MOUNTED EXTERN 4096 3215 3215 0 6: 7: SQL> alter database datafile '+DGROUP01/orcl/datafile/system.260.639064589' resize 500m; 8: 9: Database altered. 10: 11: SQL> alter database datafile '+DGROUP01/orcl/datafile/users.264.639064683' resize 100m; 12: 13: Database altered. 14: 15: SQL> alter database tempfile '+DGROUP01/orcl/tempfile/temp.263.639064645' resize 200m; 16: 17: Database altered. 再次查看ASM DISK中可用空间 1: SQL> select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup; 2: 3: NAME STATE TYPE TOTAL_MB FREE_MB USABLE_FILE_MB OFFLINE_DISKS 4: ------------------------------ ----------- ------ ---------- ---------- -------------- ------------- 5: DGROUP01 MOUNTED EXTERN 4096 2738 2738 0 6: 7: SQL> select name,bytes/1024/1024 mb from v$datafile 8: 2 union all 9: 3 select name,bytes/1024/1024 mb from v$tempfile; 10: 11: NAME MB 12: -------------------------------------------------- ---------- 13: +DGROUP01/orcl/datafile/system.260.639064589 500 14: +DGROUP01/orcl/datafile/undotbs1.261.639064633 200 15: +DGROUP01/orcl/datafile/sysaux.262.639064639 120 16: +DGROUP01/orcl/datafile/users.264.639064683 100 17: +DGROUP01/orcl/tempfile/temp.263.639064645 200 ASM的一些测试: 1: col g# for 99 2: col d# for 99 3: SQL> select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb,path from v$asm_disk; 4: 5: G# D# NAME MOUNT_S HEADER_STATU TOTAL_MB FREE_MB PATH 6: --- --- --------------- ------- ------------ ---------- ---------- ------------------------------ 7: 1 0 DGROUP01_0000 CACHED MEMBER 4096 2738 /dev/oradata/lv_asm01 创建新磁盘: 1: pri:~ # lvs 2: LV VG Attr LSize Origin Snap% Move Log Copy% 3: lv_asm01 oradata -wi-ao 4.00G 4: root system -wi-ao 9.44G 5: swap system -wi-ao 500.00M 6: 7: pri:~ # lvcreate -L3G -nlv_asm02 oradata 8: Logical volume "lv_asm02" created 9: pri:~ # lvs 10: LV VG Attr LSize Origin Snap% Move Log Copy% 11: lv_asm01 oradata -wi-ao 4.00G 12: lv_asm02 oradata -wi-a- 3.00G 13: root system -wi-ao 9.44G 14: swap system -wi-ao 500.00M 15: 16: pri:~ # chgrp -R disk /dev/oradata/ 17: pri:~ # chgrp -R disk /dev/mapper/oradata-lv_asm02 18: pri:~ # chmod 660 /dev/mapper/oradata-lv_asm02 注意这里,oracle用户,或所属组要对这个设备有读写权限 查看V$ASM_DISK 1: SQL> select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb,path from v$asm_disk; 2: 3: G# D# NAME MOUNT_S HEADER_STATU TOTAL_MB FREE_MB PATH 4: --- --- --------------- ------- ------------ ---------- ---------- ------------------------------ 5: 0 0 CLOSED CANDIDATE 3072 0 /dev/oradata/lv_asm02 6: 1 0 DGROUP01_0000 CACHED MEMBER 4096 2738 /dev/oradata/lv_asm01 7: 8: SQL> alter diskgroup dgroup01 add disk '/dev/oradata/lv_asm02' name disk02; 9: 10: Diskgroup altered. 11: SQL> select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb,path from v$asm_disk; 12: 13: G# D# NAME MOUNT_S HEADER_STATU TOTAL_MB FREE_MB PATH 14: --- --- --------------- ------- ------------ ---------- ---------- ------------------------------ 15: 1 0 DGROUP01_0000 CACHED MEMBER 4096 2762 /dev/oradata/lv_asm01 16: 1 1 DISK02 CACHED MEMBER 3072 3046 /dev/oradata/lv_asm02 查看ASM的auto rebalance工作进度 与这个速度相关的参数为 1: SQL> show parameter power 2: 3: NAME TYPE VALUE 4: ------------------------------------ ----------- ------------------------------ 5: asm_power_limit integer 3 6: 7: asm_power_limit 8: power limit(1-11)，值越小，则Asm 利用来rebalance(asm 配置改变时)的i/o带宽越小，当然越慢，对系统的性能影响越小。 9: 10: SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation; 11: 12: GROUP_NUMBER OPERATION STAT EST_WORK SOFAR EST_RATE EST_MINUTES 13: ------------ ---------- ---- ---------- ---------- ---------- ----------- 14: 1 REBAL RUN 585 331 388 0 15: 16: SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation; 17: 18: no rows selected 19: 完成后查看磁盘使用情况: 1: SQL> select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb,path from v$asm_disk; 2: 3: G# D# NAME MOUNT_S HEADER_STATU TOTAL_MB FREE_MB PATH 4: --- --- --------------- ------- ------------ ---------- ---------- ------------------------------ 5: 1 0 DGROUP01_0000 CACHED MEMBER 4096 3319 /dev/oradata/lv_asm01 6: 1 1 DISK02 CACHED MEMBER 3072 2489 /dev/oradata/lv_asm02 创建测试用户,创建数据 1: SQL> create user zwfha identified by admin default tablespace users; 2: 3: User created. 4: 5: SQL> grant connect,resource to zwfha; 6: 7: Grant succeeded. 8: 9: SQL> conn zwfha/admin 10: Connected. 11: SQL> create table t as select * from all_objects; 12: 13: Table created. 14: 15: SQL> insert into t select * from t; 16: 33: 69904 rows created. 34: 35: SQL> commit; 36: 37: Commit complete. 38: 39: SQL> select count(1) from t; 40: 41: COUNT(1) 42: ---------- 43: 139808 44: 从ASM磁盘中删除新加的DISK2. 1: SQL> alter diskgroup dgroup01 drop disk disk2; 2: 3: Diskgroup altered. 4: ...... 11: SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation; 12: 13: GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES 14: ------------ ----- ---- ---------- ---------- ---------- ----------- 15: 1 REBAL RUN 583 96 335 1 16: 17: SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation; 18: 19: GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES 20: ------------ ----- ---- ---------- ---------- ---------- ----------- 21: 1 REBAL RUN 585 331 400 0 22: 23: SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation; 24: 25: no rows selected 26: 自动rebalance完成.查看disk使用情况 1: SQL> select group_number g#,disk_number d#,name,mount_status,header_status,total_mb,free_mb,path from v$asm_disk; 2: 3: G# D# NAME MOUNT_S HEADER_STATU TOTAL_MB FREE_MB PATH 4: --- --- --------------- ------- ------------ ---------- ---------- ------------------------------ 5: 0 0 CLOSED FORMER 3072 0 /dev/oradata/lv_asm02 6: 1 0 DGROUP01_0000 CACHED MEMBER 4096 2738 /dev/oradata/lv_asm01 7: 8: 从ASM DISKGROUP中移除一个磁盘 9: 10: SQL> select name,path,state from v$asm_disk; 11: 12: NAME PATH STATE 13: ------------------------------ ------------------------------ -------- 14: DISK02 /dev/oradata/lv_asm02 NORMAL 15: DGROUP01_0000 /dev/oradata/lv_asm01 NORMAL 16: 17: SQL> 18: SQL> 19: SQL> alter diskgroup dgroup01 drop disk DGROUP01_0000; 20: 21: Diskgroup altered. 22: 23: SQL> select name,path,state from v$asm_disk; 24: 25: NAME PATH STATE 26: ------------------------------ ------------------------------ -------- 27: DISK02 /dev/oradata/lv_asm02 NORMAL 28: DGROUP01_0000 /dev/oradata/lv_asm01 DROPPING 29: 30: SQL> select group_number,operation,state,est_work,sofar,est_rate,est_minutes from v$asm_operation; 31: 32: GROUP_NUMBER OPERA STAT EST_WORK SOFAR EST_RATE EST_MINUTES 33: ------------ ----- ---- ---------- ---------- ---------- ----------- 34: 1 REBAL RUN 945 291 337 1 35: 36: SQL> select name,path,state from v$asm_disk; 37: 38: NAME PATH STATE 39: ------------------------------ ------------------------------ -------- 40: /dev/oradata/lv_asm01 NORMAL 41: DISK02 /dev/oradata/lv_asm02 NORMAL 移除完成 http://hi.baidu.com/zwfha/blog/item/af96d8119bf376c2a7ef3f0f.html