Oracle 11.2.0.3管理ASM例項

lygle發表於2013-04-06
OracleASM

ASM例項目前無論是在rac還是單例項資料庫環境下都被廣泛的採用，本文主要介紹Oracle 11.2.0.3環境下ASM例項的管理，主要包含以下內容：
1：ASM磁碟及磁碟組的狀態檢視
2：建立external 冗餘磁碟組,新增，刪除磁碟
3：建立normal redundancy磁碟組
4：normal redundancy下的failgroup測試
5：刪除ASM磁碟組
6：其他asm例項初始化引數含義

一：檢視ASM磁碟及磁碟組狀態
SQL> select * from v$asm_disk;
SQL> select * from v$asm_diskgroup;

二：建立external 冗餘磁碟組,新增，刪除磁碟，刪除磁碟的時候需要指定磁碟的name而不是path

  1. SQL> create diskgroup FRA external redundancy disk '/dev/asm-disk4';  
  2. Diskgroup created.  
  3.  
  4. SQL> alter diskgroup fra add disk '/dev/asm-disk5' rebalance power 10;  
  5. Diskgroup altered.  
  6.  
  7. SQL> select name,failgroup,path from v$asm_disk;  
  8.  
  9. NAME                 FAILGROUP            PATH  
  10. -------------------- -------------------- --------------------  
  11. DATA_0000            DATA_0000            /dev/asm-disk1  
  12. DATA_0001            DATA_0001            /dev/asm-disk3  
  13. DATA_0002            DATA_0002            /dev/asm-disk2  
  14. FRA_0000             FRA_0000             /dev/asm-disk4  
  15. FRA_0001             FRA_0001             /dev/asm-disk5  
  16.                                           /dev/asm-disk6  
  17.                                           /dev/asm-disk7  
  18.  
  19. SQL> alter diskgroup fra drop disk '/dev/asm-disk4' rebalance power 10;  
  20. alter diskgroup fra drop disk '/dev/asm-disk4' rebalance power 10  
  21. *  
  22. ERROR at line 1:  
  23. ORA-15032: not all alterations performed  
  24. ORA-15054: disk "/DEV/ASM-DISK4" does not exist in diskgroup "FRA"  
  25.  
  26. SQL> alter diskgroup fra drop disk 'FRA_0000' rebalance power 10;  
  27. Diskgroup altered. 

三：建立normal redundancy磁碟組

  1. SQL> conn /as sysasm  
  2. Connected.  
  3. SQL> create diskgroup fra normal redundancy   
  4.      failgroup fg1 disk '/dev/asm-disk4','/dev/asm-disk5'  
  5.      failgroup fg2 disk '/dev/asm-disk6','/dev/asm-disk7'  
  6.      attribute 'compatible.rdbms'='11.2','compatible.asm'='11.2';  
  7. Diskgroup created.  
  8.  
  9. SQL> select name, total_mb,free_mb,required_mirror_free_mb,usable_file_mb from v$asm_diskgroup;  
  10.  
  11. NAME         TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB  
  12. ---------- ---------- ---------- ----------------------- --------------  
  13. DATA            61440      54873                       0          18291  
  14. FRA             81920      81592                   20480          30556 

四：failgroup測試；FAILGROUP是用於將磁碟分組，以保證丟失任何一組FAILGROUP磁碟，資料還是完整的,多用於多陣列，通過ASM來完成冗餘的環境！
1：檢視FRA磁碟組中failgroup資訊及磁碟狀態

  1. SQL> select name,path,failgroup,mount_status from v$asm_disk where group_number=2;  
  2.  
  3. NAME                 PATH                 FAILGROUP  MOUNT_STATUS  
  4. -------------------- -------------------- ---------- ---------------------  
  5. FRA_0003             /dev/asm-disk7       FG2        CACHED  
  6. FRA_0002             /dev/asm-disk6       FG2        CACHED  
  7. FRA_0000             /dev/asm-disk4       FG1        CACHED  
  8. FRA_0001             /dev/asm-disk5       FG1        CACHED 

2：在rdbms例項中建立表空間，建表並插入資料，收集表統計資訊

  1. SQL> create tablespace test01 datafile '+FRA';  
  2. Tablespace created.  
  3.  
  4. ASMCMD> pwd  
  5. +fra/db/datafile  
  6. ASMCMD> ls  
  7. TEST01.256.800622493  
  8.  
  9. SQL> create table t1 tablespace test01 as select * from dba_objects;  
  10. Table created.  
  11.  
  12. SQL> exec dbms_stats.gather_table_stats('SYS','T1');  
  13. PL/SQL procedure successfully completed.  
  14.  
  15. SQL> select count(*) from t1;  
  16.  
  17.   COUNT(*)  
  18. ----------  
  19.      74501 

3：刪除udev相關規則，重啟資料庫例項和ASM例項，驗證資料是否存在

  1. SQL> conn /as sysasm  
  2. Connected.  
  3. SQL> alter diskgroup fra mount;  
  4. alter diskgroup fra mount  
  5. *  
  6. ERROR at line 1:  
  7. ORA-15032: not all alterations performed  
  8. ORA-15040: diskgroup is incomplete  
  9. ORA-15042: ASM disk "1" is missing from group number "2"  
  10. ORA-15042: ASM disk "0" is missing from group number "2"  
  11.  
  12. SQL> alter diskgroup fra mount force;  
  13. Diskgroup altered.  
  14.  
  15. SQL> select name,path,failgroup,mount_status from v$asm_disk;  
  16.  
  17. NAME       PATH                 FAILGROUP            MOUNT_STATUS  
  18. ---------- -------------------- -------------------- ---------------------  
  19. FRA_0000                        FG1                  MISSING  
  20. FRA_0001                        FG1                  MISSING  
  21. FRA_0002   /dev/asm-disk6       FG2                  CACHED  
  22. FRA_0003   /dev/asm-disk7       FG2                  CACHED  
  23. DATA_0000  /dev/asm-disk1       DATA_0000            CACHED  
  24. DATA_0002  /dev/asm-disk2       DATA_0002            CACHED  
  25. DATA_0001  /dev/asm-disk3       DATA_0001            CACHED  
  26. 7 rows selected.  
  27.  
  28. SQL> conn /as sysdba  
  29. Connected.  
  30. SQL> select count(*) from t1;  
  31.  
  32.   COUNT(*)  
  33. ----------  
  34.      74501 

五：刪除ASM磁碟組，預設磁碟故障條件下，經過12960秒即3.6個小時後自動刪除；刪除磁碟組之前需要保證無資料存放在磁碟組上且無到該磁碟組的active連線

  1. [grid@localhost ~]$ tail -f /u01/app/grid/diag/asm/+asm/+ASM/trace/alert_+ASM.log   
  2. ORA-15062: ASM disk is globally closed  
  3. Thu Nov 29 12:00:53 2012  
  4. WARNING: Disk 0 (FRA_0000) in group 2 will be dropped in: (12960) secs on ASM inst 1  
  5. WARNING: Disk 1 (FRA_0001) in group 2 will be dropped in: (12960) secs on ASM inst 1  
  6. Thu Nov 29 12:00:58 2012  
  7.  
  8. asm例項：  
  9. SQL> select path,name,repair_timer from v$asm_disk where group_number=2;  
  10.  
  11. PATH                 NAME       REPAIR_TIMER  
  12. -------------------- ---------- ------------  
  13.                      FRA_0000          12960  
  14.                      FRA_0001          12960  
  15. /dev/asm-disk6       FRA_0002              0  
  16. /dev/asm-disk7       FRA_0003              0  
  17.  
  18. SQL> alter diskgroup fra online disk 'FRA_0000';  
  19. Diskgroup altered.  
  20.  
  21. SQL> alter diskgroup fra online disk 'FRA_0001';  
  22. Diskgroup altered.  
  23.  
  24. SQL> select name,path,failgroup,mount_status from v$asm_disk;  
  25.  
  26. NAME       PATH                 FAILGROUP            MOUNT_STATUS  
  27. ---------- -------------------- -------------------- ---------------------  
  28. FRA_0002   /dev/asm-disk6       FG2                  CACHED  
  29. FRA_0003   /dev/asm-disk7       FG2                  CACHED  
  30. DATA_0000  /dev/asm-disk1       DATA_0000            CACHED  
  31. DATA_0002  /dev/asm-disk2       DATA_0002            CACHED  
  32. DATA_0001  /dev/asm-disk3       DATA_0001            CACHED  
  33. FRA_0001   /dev/asm-disk5       FG1                  CACHED  
  34. FRA_0000   /dev/asm-disk4       FG1                  CACHED  
  35.  
  36. SQL> drop diskgroup fra;  
  37. drop diskgroup fra  
  38. *  
  39. ERROR at line 1:  
  40. ORA-15039: diskgroup not dropped  
  41. ORA-15053: diskgroup "FRA" contains existing files  
  42.  
  43. rdbms例項：  
  44. SQL> drop tablespace test01 including contents;  
  45. Tablespace dropped.  
  46.  
  47. SQL> shutdown immediate  
  48. Database closed.  
  49. Database dismounted.  
  50. ORACLE instance shut down.  
  51.  
  52. asm例項：  
  53. SQL> drop diskgroup fra including contents;  
  54. Diskgroup dropped.  
  55.  
  56. rdbms例項：  
  57. SQL> startup  
  58. ORACLE instance started.  
  59.  
  60. Total System Global Area  313860096 bytes  
  61. Fixed Size                  1344652 bytes  
  62. Variable Size             192940916 bytes  
  63. Database Buffers          113246208 bytes  
  64. Redo Buffers                6328320 bytes  
  65. Database mounted.  
  66. Database opened. 

六：其他asm例項初始化引數含義

instance_type：Defines the type of the instance, such as RDBMS and ASM. However, this has been made optional in an Oracle Grid Infrastructure ASM.

asm_power_limit：Manages the degree of parallelism to speed up the ASM disk rebalance operations, for example, whenever a disk is being dropped from an existing disk group or when the disk group rebalance is initiated manually. This is a dynamic parameter that can be set in the range from 0 to 11 (1024 in 11gR2 and above). Considering the size of a disk group, you may increase the limit of the  POWER to speed up the rebalancing operation. Multiple ASM instances can hold the different values across a cluster. When no limit is specified, it uses the default value for the rebalancing operation.

asm_diskstring: Used by the ASM instance to identify and discover the disks mentioned in the paths. Once the disks are discovered, they will appear in the V$ASM_DISK dynamic view. The disk discovery occurs when an ASM instance is initiated, when you issue a query against the  V$ASM_DISK/V$ASM_DISKGROUP dynamic views, or when you  MOUNT,  UNMOUNT,  RESIZE,  ADD  a disk. It is a dynamic parameter and you can specify multiple paths within this parameter

processes: Apart from the SGA initialization parameter value, the PROCESSES initialization parameter value has some influence over the ASM instance. Therefore, you may use the following formula to tune the  PROCESSES initialization parameter when multiple database instances are accessing the instance:
Processes = 50+50*n (where n indicates the number of instances used to connect to the ASM instance)

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

相關文章