행위

오라클 ASM

DB CAFE

thumb_up 추천메뉴 바로가기

1 ASM 이란?[편집]

https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util001.htm#OSTMG01620

2 ASMcmd 명령어[편집]

2.1 실행명령어[편집]

asmcmd

2.2 diskgroup[편집]

ASMCMD> lsdg

2.3 disk 진행사항[편집]

ASMCMD> lsdg

2.4 ASM 작업[편집]

  • 자주 사용하는 오라클 ASM 명령어 (Command)

2.4.1 sqlplus를 사용하여 디스크 그룹 생성 및 삭제[편집]

  • 콘솔 로 sqlplus 접속
bash-4.1$ . oraenv

ORACLE_SID = [TEST] ? +ASM

The Oracle base remains unchanged with value /u01/app

bash-4.1$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 14 14:47:25 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Automatic Storage Management option

SQL>
2.4.1.1 디스크 그룹 생성 (external)[편집]
SQL> create diskgroup ddgroup1 external    
  2  redundancy disk '/dev/rdsk/c7t4d0s6';

Diskgroup created.
2.4.1.2 디스크 그룹 삭제[편집]
SQL> drop diskgroup DDGROUP1 including contents;

Diskgroup dropped.
2.4.1.3 disk group 생성 (normal)[편집]
SQL> create diskgroup ddgrouup1 normal redundancy 
  2  failgroup cont1 disk '/dev/rdsk/c7t4d0s6'
  3  failgroup cont2 disk '/dev/rdsk/c7t5d0s6';

Diskgroup created.
2.4.1.4 disk group 삭제[편집]
SQL> drop diskgroup DDGROUUP1 including contents;

Diskgroup dropped.
2.4.1.5 disk group 생성 (high)[편집]
SQL> create diskgroup ddgrouup1 high redundancy
  2  failgroup cont1 disk '/dev/rdsk/c7t4d0s6'
  3  failgroup cont2 disk '/dev/rdsk/c7t5d0s6'
  4  failgroup cont3 disk '/dev/rdsk/c7t6d0s6';

Diskgroup created.
2.4.1.6 disk 그룹 삭제[편집]
-> disk group 생성 (high)
SQL> drop diskgroup ddgrouup1 including contents;
Diskgroup dropped.
2.4.1.7 디스크 그룹에 디스크 추가[편집]
SQL> alter diskgroup DATA add disk 
  2  '/dev/rdsk/c7t4d0s6' name DATA_0002;

Diskgroup altered.
2.4.1.8 디스크 그룹에 디스크 삭제[편집]
SQL> alter diskgroup DATA drop disk DATA_0002;
Diskgroup altered.

-> 디스크 그룹 삭제하려고 할때 삭제가 완료되기 전 undrop하여 삭제작업 취소

SQL> alter diskgroup  DATA 
  2  drop disk DATA_0002;
Diskgroup altered.

SQL> alter diskgroup DATA undrop disks;
Diskgroup altered.

-> ASM 디스크 그룹을 만들때 속성값을 지정할 수 있다.

SQL> create diskgroup DATA2 normal redundancy
  2  disk '/dev/rdsk/c7t4d0s6','/dev/rdsk/c7t5d0s6'
  3  attribute 'compatible.asm'='11.2';

Diskgroup created.

SQL> drop diskgroup DATA2 including contents;

Diskgroup dropped.


2.4.1.9 sqlplus에서 메타데이터 검색 했을때쿼리[편집]
select f.type, f.redundancy, f.striped, f.modification_date,
       a.system_created, a.name
  from v$asm_alias a, v$asm_file f 
 where a.FILE_NUMBER=f.FILE_NUMBER 
   and a.GROUP_NUMBER=f.GROUP_NUMBER
   and type='DATAFILE';
2.4.1.10 asmcmd에서 메타데이터 검색 했을때[편집]
bash-4.1$ asmcmd
Connected to an idle instance.
 (주의 : 위에처럼 idle instance로 떨어지면 sql이던 asmcmd이건 잘못 접속 된 것이다.)

bash-4.1$ . oraenv
ORACLE_SID = [TEST] ? +ASM

The Oracle base remains unchanged with value /u01/app

bash-4.1$ asmcmd

ASMCMD> 

ASMCMD> ls -l +DATA/TEST/datafile

Type      Redund  Striped  Time             Sys  Name

DATAFILE  UNPROT  COARSE   APR 13 20:00:00  Y    EXAMPLE.265.844406343
DATAFILE  UNPROT  COARSE   APR 13 20:00:00  Y    SYSAUX.257.844406197
DATAFILE  UNPROT  COARSE   APR 13 20:00:00  Y    SYSTEM.256.844406195
DATAFILE  UNPROT  COARSE   APR 13 20:00:00  Y    UNDOTBS1.258.844406197

3 ASM Disk 그룹 조회[편집]

  • V$ASM_DISKGROUP
SELECT GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB
  FROM V$ASM_DISKGROUP;

4 ASM Disk 조회[편집]

  • GV$ASM_DISK
SELECT GROUP_NUMBER
     , DISK_NUMBER
     , NAME
     , MOUNT_STATUS
     , PATH
     , TOTAL_MB
  FROM GV$ASM_DISK;

5 ASM File 조회[편집]

SELECT GROUP_NUMBER
     , FILE_NUMBER
     , ROUND ((BYTES / 1024 / 1024), 1)     MB
     , REDUNDANCY
     , TYPE
  FROM V$ASM_FILE;

6 ASM Disk 그룹/패스 조회[편집]

  • V$ASM_DISK , V$ASM_DISKGROUP
SELECT G.NAME                       DG_NAME
     , D.PATH                       PATH
     , D.TOTAL_MB                   DISK_SIZE_MB
     , G.DATABASE_COMPATIBILITY     DB_COMPATIBILITY
     , G.COMPATIBILITY              ASM_COMPATIBILITY
  FROM V$ASM_DISK D
     , V$ASM_DISKGROUP G
 WHERE G.GROUP_NUMBER = D.GROUP_NUMBER;

7 테이블 스페이스 정보 조회 SQL[편집]

  • V$ASM_DISK_STAT
-- 그룹별 사용량 
SELECT GROUP_NUMBER
     , NAME
     , TOTAL_MB
     , FREE_MB
 FROM V$ASM_DISK_STAT;
  • V$ASM_DISKGROUP
-- col name format a10
-- col USABLE_FILE_GB format 999,999.00
-- col TOTAL_GB       format 999,999.00
-- col FREE_GB        format 999,999.00
-- col USABLE_CALC_GB format 999,999.00

SELECT GROUP_NUMBER                                      "Group#"
     , NAME
     , TOTAL_MB / 1024                                   TOTAL_GB
     , ROUND ((TOTAL_MB - USABLE_FILE_MB) / 1024, 2)     USED_GB
     , USABLE_FILE_MB / 1024                             USABLE_FILE_GB
     , FREE_MB / 1024                                    FREE_GB
     , 100 - ROUND (FREE_MB / TOTAL_MB * 100)            "usgae(%)"
     , ((FREE_MB - REQUIRED_MIRROR_FREE_MB)) / 1024      USABLE_CALC_GB
     , TYPE
     , STATE
  FROM V$ASM_DISKGROUP;
  • V$ASM_FILE , $ASM_ALIAS , V$ASM_DISKGROUP
SELECT G.NAME                                    "GROUP_NAME"
           -- F.GROUP_NUMBER
           --       , G.TOTAL_MB
           , ROUND (G.FREE_MB / 1024, 1)               "GROUP_FREE(GB)"
           --       , F.FILE_NUMBER
           , ROUND (SPACE / 1024 / 1024 / 1024, 1)     "REAL_SIZE(GB)"
           , ROUND (BYTES / 1024 / 1024 / 1024, 1)     "USABLE_SIZE(GB)"
           --       , A.ALIAS_DIRECTORY
           , MAX (A.NAME)                              "FILE_NAME"
        FROM V$ASM_FILE F, V$ASM_ALIAS A, V$ASM_DISKGROUP G
       WHERE     F.GROUP_NUMBER = A.GROUP_NUMBER
             AND F.FILE_NUMBER = A.FILE_NUMBER
             AND A.GROUP_NUMBER = G.GROUP_NUMBER
             AND SYSTEM_CREATED = 'Y'
    GROUP BY (G.NAME
            , ROUND (G.FREE_MB / 1024, 1)
            , ROUND (SPACE / 1024 / 1024 / 1024, 1)
            , ROUND (BYTES / 1024 / 1024 / 1024, 1))
    ORDER BY G.NAME, MAX (A.NAME);
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_ASM&oldid=11385"