행위

오라클 ASM

DB CAFE

Dbcafe (토론 | 기여)님의 2020년 4월 21일 (화) 09:02 판
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)


-> 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>

-> 디스크 그룹 생성 (external)

SQL> create diskgroup ddgroup1 external 

 2  redundancy disk '/dev/rdsk/c7t4d0s6';


Diskgroup created.


-> 디스크 그룹 삭제

SQL> drop diskgroup DDGROUP1 including contents;


Diskgroup dropped.


-> 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.


-> disk group 삭제

SQL> drop diskgroup DDGROUUP1 including contents;


Diskgroup dropped.


-> 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.


-> disk 그룹 삭제

SQL> drop diskgroup ddgrouup1 including contents;


Diskgroup dropped.


-> 디스크 그룹에 디스크 추가.

SQL> alter diskgroup DATA add disk 

 2  '/dev/rdsk/c7t4d0s6' name DATA_0002;


Diskgroup altered.


-> 디스크 그룹에 디스크 삭제

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.


-> 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';


-> 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 그룹 조회[편집]

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

4 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 Disk 그룹/패스 조회[편집]

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[편집]

-- 그룹별 사용량 
SELECT GROUP_NUMBER
     , NAME
     , TOTAL_MB
     , FREE_MB
 FROM V$ASM_DISK_STAT;
-- 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;
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=4196"