探索ORACLE之ASM04_維護

探索ORACLE之ASM04_維護

作者：吳偉龍

一、   ASM例項相關操作：

ASM例項的管理，啟動，關閉

ASM例項的啟動和資料庫例項的啟動有嚴格的先後關係，ASM啟動一定早於資料庫例項，關閉一定晚於ASM例項，因為它是資料庫資料檔案儲存位置。如果ASM沒有起來，起資料庫將會報ORA-17503；ORA-15077的錯誤，錯誤資訊如下:

SQL> startup

ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+ASMGROUP1/WWL/spfileWWL.ora'

ORA-17503: ksfdopn:2 Failed to open file +ASMGROUP1/WWL/spfileWWL.ora

ORA-15077: could not locate ASM instance serving a required diskgroup

SQL>

1.1   ASM啟動的方法:

SQL> startup

ASM instance started

Total System Global Area   83886080 bytes

Fixed Size                  1217836 bytes

Variable Size              57502420 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

+ASM             STARTED

SQL>

1.2   ASM關閉的方法  （必須先關閉資料庫）

沒有關閉RDBMS例項關閉ASM將報錯ORA-15097，提示已連線RDBMS例項，無法關閉ASM例項，

$ export ORACLE_SID=+ASM

$ sqlplus / as sysdba

SQL> shutdown immediate

ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance

關閉RDBMS例項狀態ASM是可以正常關閉的。

$ export ORACLE_SID=WWL    ---先關閉在ASM上執行的RDBMS例項

$ sqlplus / as sysdba

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

$ export ORACLE_SID=+ASM   ---再關閉ASM例項

$ sqlplus / as sysdba

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SQL>

二、   ASM三種磁碟組及磁碟的新增和維護

1、 ASM磁碟的新增及刪除

1.1    新增這個步驟所需的磁碟（/dev/sdd -- /dev/sdm  共10塊10G的盤）

1.2    透過root使用者檢視下當前有幾個ASM磁碟，磁碟狀態，例項狀態

# oracleasm listdisks

VOL1

VOL2

# oracleasm querydisk VOL1

Disk "VOL1" is a valid ASM disk

# oracleasm querydisk VOL2

Disk "VOL2" is a valid ASM disk

# ls -l /dev/oracleasm/disks/*

brw-rw---- 1 oracle dba 8, 17 Apr 12 05:30 /dev/oracleasm/disks/VOL1

brw-rw---- 1 oracle dba 8, 33 Apr 12 05:30 /dev/oracleasm/disks/VOL2

# oracleasm status

Checking if ASM is loaded: yes

Checking if /dev/oracleasm is mounted: yes

     我們已知資料庫當有兩塊透過ASMLiB已經標記了的磁碟，並且狀態是正常的

1.3    開始透過ASMLib來標記新的磁碟,用於後面的實驗:

l  報錯了，很經典，是由於沒有建立分割槽導致：

# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd

Marking disk "VOL3" as an ASM disk: [FAILED]

l  先建立分割槽方法: fdisk /dev/sdd /n/p/1/回車/回車/w，將所有磁碟都建立分割槽。

# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd1

Marking disk "VOL3" as an ASM disk: [  OK  ]   ---可以看到，能正常建立

            # sh oracleasm   透過執行指令碼命令，新建10個磁碟已全部完成標記

Marking disk "VOL4" as an ASM disk: [  OK  ]

Marking disk "VOL5" as an ASM disk: [  OK  ]

Marking disk "VOL6" as an ASM disk: [  OK  ]

Marking disk "VOL7" as an ASM disk: [  OK  ]

Marking disk "VOL8" as an ASM disk: [  OK  ]

Marking disk "VOL9" as an ASM disk: [  OK  ]

Marking disk "VOL10" as an ASM disk: [  OK  ]

Marking disk "VOL11" as an ASM disk: [  OK  ]

Marking disk "VOL12" as an ASM disk: [  OK  ]

1.4    為ASMGROUP1磁碟組新增刪除磁碟

l  檢視磁碟組的狀態

SQL> select GROUP_NUMBER,NAME,STATE,TYPE from v$asm_diskgroup;

GROUP_NUMBER   NAME       STATE       TYPE

------------------------  -----------------  --------------    --------

1  ASMGROUP1  CONNECTED   NORMAL

            SQL> SELECT a.name GRPNAME,b.group_number GR_NUMBER,b.disk_number DK_NUMBER,b.name ASMFILE,b.path,b.mount_status,b.state FROM v$asm_diskgroup a,v$asm_disk b;

GRPNAME     GR_NUMBER  DK_NUMBER ASMFILE                        PATH                      MOUNT_S STATE

---------- ---------- ---------- ------------------------------ ------------------------- ------- --------

ASMGROUP1  1    0 ASMGROUP1_0000    /dev/oracleasm/disks/VOL1 OPENED  NORMAL

ASMGROUP1  1   1ASMGROUP1_0001     /dev/oracleasm/disks/VOL2 OPENED  NORMAL

l  檢視磁碟組ASMGROUP1中的成員

SQL> select group_number,disk_number, failgroup,name,path from v$asm_disk where FAILGROUP like 'ASMGROUP1%';

GROUP_NUMBER DISK_NUMBER FAILGROUP                      NAME                           PATH

------------ ----------- ------------------------------ ------------------------------ ----------------------------------------

           2           1 ASMGROUP1_0001                 ASMGROUP1_0001                 /dev/oracleasm/disks/VOL2

           2           0 ASMGROUP1_0000                 ASMGROUP1_0000                 /dev/oracleasm/disks/VOL1

SQL>

l  新增為ASMGROUP1新增磁碟

SQL> alter diskgroup ASMGROUP1 add disk '/dev/oracleasm/disks/VOL10';

Diskgroup altered.

l  我們可以看到已經新增成功了

SQL>  select group_number,disk_number, failgroup,name,path from v$asm_disk where FAILGROUP like 'ASMGROUP1%';

GROUP_NUMBER DISK_NUMBER FAILGROUP                      NAME                           PATH

------------ ----------- ------------------------------ ------------------------------ ----------------------------------------

           2           2 ASMGROUP1_0002                 ASMGROUP1_0002                 /dev/oracleasm/disks/VOL10

           2           1 ASMGROUP1_0001                 ASMGROUP1_0001                 /dev/oracleasm/disks/VOL2

           2           0 ASMGROUP1_0000                 ASMGROUP1_0000                 /dev/oracleasm/disks/VOL1

2、 ASM三種磁碟組的建立及刪除（High  Normal  Extermal）

2.1   建立High級別的ASM磁碟組，最少需要三塊磁碟來建立。

SQL> create diskgroup asmhigh high redundancy disk '/dev/oracleasm/disks/VOL3','/dev/oracleasm/disks/VOL4','/dev/oracleasm/disks/VOL5';

Diskgroup created.

2.2   建立Normal級別的ASM磁碟，最少需要兩個磁碟來建立。

SQL> create diskgroup asmnormal normal redundancy disk '/dev/oracleasm/disks/VOL6','/dev/oracleasm/disks/VOL7';

Diskgroup created.

2.3   建立Extermal級別的ASM磁碟，最少需要一個磁碟來建立。

SQL> create diskgroup asmexternal external redundancy disk '/dev/oracleasm/disks/VOL8';

Diskgroup created.

2.4   檢視剛才建立的磁碟狀態

SQL> select name,state,type from v$asm_diskgroup;

NAME            STATE       TYPE

--------------- ----------- ------

ASMGROUP1       MOUNTED    NORMAL

ASMHIGH         MOUNTED     HIGH

ASMNORMAL      MOUNTED     NORMAL

ASMEXTERNAL     MOUNTED     EXTERN

2.5          為ASM磁碟組新增成員，在這裡我們就以Normal磁碟組來進行成員新增的例子：

SQL> alter diskgroup ASMNORMAL add disk '/dev/oracleasm/disks/VOL9';

Diskgroup altered.

SQL> select group_number,disk_number, failgroup,name,path from v$asm_disk where FAILGROUP like 'ASMNORMAL%';

GROUP_NUMBER DISK_NUMBER FAILGROUP                      NAME                           PATH

------------ ----------- ------------------------------ ------------------------------ ----------------------------------------

           4           2 ASMNORMAL_0002                 ASMNORMAL_0002                 /dev/oracleasm/disks/VOL9

           4           1 ASMNORMAL_0001                 ASMNORMAL_0001                 /dev/oracleasm/disks/VOL7

           4           0 ASMNORMAL_0000                 ASMNORMAL_0000                 /dev/oracleasm/disks/VOL6

SQL>

2.6          刪除磁碟組成員，在這裡我們同樣以NORMAL磁碟組來進行成員刪除的例子：

SQL> alter diskgroup ASMNORMAL drop disk ASMNORMAL_0002;

Diskgroup altered.

SQL> select group_number,disk_number, failgroup,name,path from v$asm_disk where FAILGROUP like 'ASMNORMAL%';

GROUP_NUMBER DISK_NUMBER FAILGROUP                      NAME                           PATH

------------ ----------- ------------------------------ ------------------------------ ----------------------------------------

           4           1 ASMNORMAL_0001                 ASMNORMAL_0001                 /dev/oracleasm/disks/VOL7

           4           0 ASMNORMAL_0000                 ASMNORMAL_0000                 /dev/oracleasm/disks/VOL6

SQL>

三、   模擬磁碟故障

3.1      在AMSGROUP1(NORMAL型別)磁碟組中寫資料

SQL> select tablespace_name,file_name,bytes/1024/1024 M from dba_data_files;

TABLESPACE_NAME FILE_NAME                                              M

--------------- --------------------------------------------- ----------

USERS           +ASMGROUP1/wwl/datafile/users.259.780215953            5

SYSAUX          +ASMGROUP1/wwl/datafile/sysaux.257.780215951         230

UNDOTBS1        +ASMGROUP1/wwl/datafile/undotbs1.258.78021595         25

                3

SYSTEM          +ASMGROUP1/wwl/datafile/system.256.780215951         480

ASM             +ASMGROUP1/wwl/datafile/asm.270.780300769            100

ASM             +ASMGROUP1/wwl/datafile/asm.271.780300809            100

6 rows selected.

如上我們可以看到，我們所有的表空間均是放在ASMGROUP1中的，一會兒我們將對錶空間寫如資料，並刪除一磁碟。

3.2      我們檢視下該表空間的預設使用者

SQL> select username,default_tablespace from dba_users where DEFAULT_TABLESPACE='ASM';

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

WWL                            ASM

3.3      在ASM表空間寫入資料。

透過WWL使用者登入到系統建立一張表，用來測試.

SQL> conn wwl/wwl

Connected.

SQL> create table wwl (id varchar(5),name varchar(10));

Table created.

SQL>  begin

  2  for i in 1..1000 loop

  3  insert into wwl values (15,'wwl15');

  4  end loop;

  5  end;

  6  /

PL/SQL procedure successfully completed.

我們建立了一張wwl的表，並且插入了1000行資料

SQL> select count(*) from wwl;

  COUNT(*)

----------

      1000

3.4      模擬磁碟突然損壞

[root@wwl ~]# oracleasm deletedisk VOL2;

Clearing disk header: done

Dropping disk: done

[root@wwl ~]#

仔細看下面，我們透過如上的命令刪除了VOL2後，現在只認到一個磁碟了。

SQL> select group_number,disk_number, failgroup,name,path from v$asm_disk where FAILGROUP like 'ASMGROUP%';

GROUP_NUMBER DISK_NUMBER FAILGROUP                      NAME                           PATH

------------ ----------- ------------------------------ ------------------------------ ----------------------------------------

           2           0 ASMGROUP1_0000                 ASMGROUP1_0000                 /dev/oracleasm/disks/VOL1

SQL>

但是我們的例項和我們剛才建立的表資料都沒有丟失，這就是冗餘的好處，NORMAL模式它是用犧牲一塊磁碟的空間來保障資料的安全性的，hight模式是至少犧牲一塊硬碟來保障資料的安全性。

SQL> select count(*) from wwl;

  COUNT(*)

----------

      1000

3.5       而且業務是不會中斷的，但是在日誌和硬碟指示燈上會有告警：

ASM日誌資訊如下：

WARNING: offlining disk 2.3916240783 (ASMGROUP1_0002) with mask 0x1

NOTE: PST update: grp = 2, dsk = 2, mode = 0x6

NOTE: cache closing disk 2 of grp 2: ASMGROUP1_0002

NOTE: PST update: grp = 2

NOTE: erasing header on grp 2 disk ASMGROUP1_0002

3.6      這個時候我們需要儘快更換新的硬碟，因為發生這問題之後如果另外一個磁碟再損壞的話那將是不可彌補的資料丟失，更換新硬碟後，資料將會再次進行同步。

3.7       

四、   ASM別名管理

別名就是外號，比如說當系統自動產生的名稱太過複雜不怎麼好記，DBA可以透過別名，為它建立一個簡單化的名稱，而又不會對其現有名稱造成任何影響。ASM中建立別名是透過alter diskgroup的alias子句實現，支援增加/修改/刪除等多項操作。V$ASM_ALIAS檢視中可以查詢到當前例項中建立的別名。

4.1    新增別名

SQL> alter diskgroup ASMGROUP1 add alias '+ASMGROUP1/wwl/datafile/asm01.dbf' for '+ASMGROUP1/wwl/datafile/asm.270.780300769';

Diskgroup altered.

4.2    修改別名

SQL> alter diskgroup ASMGROUP1 rename alias '+ASMGROUP1/wwl/datafile/asm01.dbf' for '+ASMGROUP1/wwl/datafile/asm.270.780300769';

Diskgroup altered.

4.3    刪除別名

SQL> alter diskgroup ASMGROUP1 drop alias '+ASMGROUP1/wwl/datafile/asm01.dbf' for '+ASMGROUP1/wwl/datafile/asm.270.780300769';

Diskgroup altered.

   無論是新增、刪除或是修改別名，對原檔案路徑均不會有影響。

五、   目錄及目錄檔案管理

5.1   建立目錄

SQL> alter diskgroup ASMGROUP1 add directory '+ASMGROUP1/WWL1';

Diskgroup altered.

5.2   修改目錄

SQL> alter diskgroup ASMGROUP1 rename directory '+ASMGROUP1/WWL1' to '+ASMGROUP1/WWL2';

Diskgroup altered.

5.3   刪除目錄

SQL> alter diskgroup ASMGROUP1 drop directory '+ASMGROUP1/WWL2';

Diskgroup altered.

六、   手動平衡磁碟組

一般情況下ASM都會自動對其下的磁碟組進行平衡，不過ORACLE也提供了手動平衡磁碟組的方式，透過alter diskgroup ... power 語句。前面提到過磁碟組的平衡度有0到11多個級別，預設是按照ASM_POWER_LIMIT初始化引數中設定的值，手動平衡的話，設定的平衡度可以與初始化引數中並不相同，例如，設定磁碟組平衡度為5，語句如下：

     SQL> alter diskgroup asmgroup1 rebalance power 5;

Diskgroup altered.

七、   透過ASMCMD工具管理ASM

[oracle@wwl ~]$ which asmcmd

/oracle/orahome/10.2.0/db_1/bin/asmcmd

[oracle@wwl ~]$ cd /oracle/orahome/10.2.0/db_1/bin/

ASMCMD> ls 

ASMEXTERNAL/

ASMGROUP1/

ASMHIGH/

ASMNORMAL/

ASMCMD>

ASMCMD> help

        asmcmd [-p] [command]

        The environment variables ORACLE_HOME and ORACLE_SID determine the

        instance to which the program connects, and ASMCMD establishes a

        bequeath connection to it, in the same manner as a SQLPLUS / AS

        SYSDBA.  The user must be a member of the SYSDBA group.

        Specifying the -p option allows the current directory to be displayed

        in the command prompt, like so:

        ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >

        [command] specifies one of the following commands, along with its

        parameters.

        Type "help [command]" to get help on a specific ASMCMD command.

        commands:

        --------

cd：------------------------------------------進入下級目錄或進入所需要的目錄

du：------------------------------------------顯示指定的ASM目錄下ASM檔案佔用的所有磁碟空間

find：-----------------------------------------查詢所需的檔案

help：-----------------------------------------顯示幫助資訊

ls：---------------------------------------------列出ASM目錄下的內容及其屬性

lsct：-------------------------------------------列出當前ASM客戶端的資訊

lsdg：-------------------------------------------列出所有磁碟組及其屬性

mkalias：--------------------------------------為系統生成的檔名建立別名

mkdir：----------------------------------------建立新目錄

pwd：------------------------------------------顯示當前目錄路徑

rm：--------------------------------------------刪除ASM目錄下的某個檔案或資料夾

rmalias：--------------------------------------刪除別名

ASMCMD>

要檢視某個命令的相信透過在命令前新增help來檢視，如下:

ASMCMD> help cd

        cd

        Change the current directory to

ASMCMD> help du

        du [-H] [dir]

        Display total space used for files located recursively under [dir],

        similar to "du -s" under UNIX; default is the current directory.  Two

        values are returned, both in units of megabytes.  The first value does

        not take into account mirroring of the diskgroup while the second does.

        For instance, if a file occupies 100 MB of space, then it actually

        takes up 200 MB of space on a normal redundancy diskgroup and 300 MB

        of space on a high redundancy diskgroup. 

        [dir] can also contain wildcards.

        The -H flag suppresses the column headers from the output.

ASMCMD> help find

        find [-t ]

        Find the absolute paths of all occurrences of under

        can be a directory and may include wildcards. 

        include wildcards.  Note that directory names in the results have the

        "/" suffix to clarify their identity.

        The -t option allows searching by file type.  For instance, one can

        search for all the control files at once.  must be one of the

        valid values in V$ASM_FILE.TYPE.

ASMCMD>

八、   oracleasm工具的使用和語法介紹

[root@wwl ~]# oracleasm --help

Usage: oracleasm [--exec-path=] [ ]

       oracleasm --exec-path

       oracleasm -h

       oracleasm -V

The basic oracleasm commands are:

    configure        Configure the Oracle Linux ASMLib driver

    init             Load and initialize the ASMLib driver

    exit             Stop the ASMLib driver

    scandisks        Scan the system for Oracle ASMLib disks

    status           Display the status of the Oracle ASMLib driver

    listdisks        List known Oracle ASMLib disks

    querydisk        Determine if a disk belongs to Oracle ASMlib

    createdisk       Allocate a device for Oracle ASMLib use

    deletedisk       Return a device to the operating system

    renamedisk       Change the label of an Oracle ASMlib disk

    update-driver    Download the latest ASMLib driver

[root@wwl ~]#

九、   ASM相關檢視（V$）和資料字典（X$）

ASM由於其高度的封裝性，使得我們很難知道窺探其內部的原理。可以透過一下檢視和資料字典來來檢視ASM 的資訊。

相關檢視和資料字典

This list is obtained querying v$fixed_view_definitionwhere view_name like '%ASM%' which exposes all the v$ and gv$ views with theirdefinition. Fixed tables are exposed by querying v$fixed_table where name like'x$kf%' (ASM fixed tables use the 'X$KF' prefix).

SQL>select * fromv$fixed_view_definition whereview_name like '%ASM%';

SQL>select * from sys.v$fixed_tablewhere name like 'X$KF%' ;

十、   ASM常見的錯誤處理

錯誤一、

ORA-15097：cannot SHUTDOWN ASM instance with connected RDBMS instance

解決辦法：

發生這個問題，唯一的一個原因就是Oracle例項沒有關閉，或ORACLE例項正在關閉或處於掛起狀態，導致ASM例項無法關閉，解決辦法，關閉RDBMS例項後再關閉ASM例項。

錯誤二、

[root@wwl ~]# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd

Marking disk "VOL3" as an ASM disk: [FAILED]

   報這個錯的原因在於磁碟為分割槽導致。在建立ASM的之前必須線將磁碟分割槽，但不能格式化，後執行建立就不會有問題了。

十一、        ASM 擴充套件性

• 最多支援63個磁碟組；
• 最多支援10000個磁碟；
• 最大支援4pb/磁碟；
• 最大支援40 exabyte/ASM儲存；
• 最大支援1百W個檔案/磁碟組；
• 外部冗餘時單個檔案最大35tb，標準冗餘時單個檔案最大5.8tb，高冗餘度時單個檔案最大3.9tb

十二、        ASM其它資訊請參考如下連線：