행위

"오라클 ASM"의 두 판 사이의 차이

DB CAFE

(→‎ASM 이란?)
(→‎Disk 그룹/패스 조회)
(같은 사용자의 중간 판 24개는 보이지 않습니다)
1번째 줄: 1번째 줄:
−
= ASM 이란?=
+
== ASM 이란? ==
−
{{Note|Foo}}
+
https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util001.htm#OSTMG01620
−
{{Note|Foo|reminder}}
+
 
−
{{Note|Foo|error}}
+
== ASMcmd 명령어 ==
−
{{Note|Foo|gotcha}}
+
=== 실행명령어 ===
−
{{Note}} Loose test
+
<source lang=sql>
 +
asmcmd
 +
</source>
 +
=== diskgroup ===
 +
 
 +
ASMCMD> lsdg
 +
=== disk 진행사항 ===
 +
ASMCMD> lsdg
 +
 
 +
=== ASM 작업 ===
 +
 
 +
* 자주 사용하는 오라클 ASM 명령어 (Command)
 +
 
 +
==== sqlplus를 사용하여 디스크 그룹 생성 및 삭제 ====
 +
 
 +
* 콘솔 로 sqlplus 접속
 +
 
 +
<source lang=shell>
 +
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>
 +
</source>
 +
 
 +
===== 디스크 그룹 생성 (external) =====
 +
<source lang=sql>
 +
SQL> create diskgroup ddgroup1 external   
 +
  2  redundancy disk '/dev/rdsk/c7t4d0s6';
 +
 
 +
Diskgroup created.
 +
</source>
 +
 
 +
===== 디스크 그룹 삭제 =====
 +
<source lang=sql>
 +
SQL> drop diskgroup DDGROUP1 including contents;
 +
 
 +
Diskgroup dropped.
 +
</source>
 +
 
 +
===== disk group 생성 (normal) =====
 +
<source lang=sql>
 +
SQL> create diskgroup ddgrouup1 normal redundancy
 +
  2  failgroup cont1 disk '/dev/rdsk/c7t4d0s6'
 +
  3  failgroup cont2 disk '/dev/rdsk/c7t5d0s6';
 +
 
 +
Diskgroup created.
 +
</source>
 +
 
 +
===== disk group 삭제 =====
 +
<source lang=sql>
 +
SQL> drop diskgroup DDGROUUP1 including contents;
 +
 
 +
Diskgroup dropped.
 +
</source>
 +
 
 +
===== disk group 생성 (high) =====
 +
<source lang=sql>
 +
 
 +
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.
 +
</source>
 +
 
 +
=====  disk 그룹 삭제 =====
 +
<source lang=sql>
 +
-> disk group 생성 (high)
 +
SQL> drop diskgroup ddgrouup1 including contents;
 +
Diskgroup dropped.
 +
</source>
 +
 
 +
===== 디스크 그룹에 디스크 추가 =====
 +
<source lang=sql>
 +
SQL> alter diskgroup DATA add disk
 +
  2  '/dev/rdsk/c7t4d0s6' name DATA_0002;
 +
 
 +
Diskgroup altered.
 +
</source>
 +
===== 디스크 그룹에 디스크 삭제 =====
 +
<source lang=sql>
 +
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.
 +
</source>
 +
 
 +
 
 +
===== sqlplus에서 메타데이터 검색 했을때쿼리 =====
 +
<source lang=sql>
 +
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';
 +
</source>
 +
 
 +
===== asmcmd에서 메타데이터 검색 했을때 =====
 +
<source lang=sql>
 +
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
 +
</source>
  
 
== ASM Disk 그룹 조회 ==
 
== ASM Disk 그룹 조회 ==
 +
* V$ASM_DISKGROUP
 
<source lang=sql>
 
<source lang=sql>
−
SELECT group_number,name,state,total_mb,free_mb
+
SELECT GROUP_NUMBER,NAME,STATE,TOTAL_MB,FREE_MB
−
   FROM v$asm_diskgroup;
+
   FROM V$ASM_DISKGROUP;
 
</source>
 
</source>
  
 
== ASM Disk 조회 ==
 
== ASM Disk 조회 ==
 +
* GV$ASM_DISK
 
<source lang=sql>
 
<source lang=sql>
−
select group_number,disk_number,name,mount_status,path,total_mb
+
SELECT GROUP_NUMBER
−
from gv$asm_disk;
+
    , DISK_NUMBER
 +
    , NAME
 +
    , MOUNT_STATUS
 +
    , PATH
 +
    , TOTAL_MB
 +
  FROM GV$ASM_DISK;
 
</source>
 
</source>
  
 
== ASM File 조회 ==
 
== ASM File 조회 ==
 
<source lang=sql>
 
<source lang=sql>
−
select group_number,file_number,round((bytes/1024/1024),1) MB, redundancy,type
+
SELECT GROUP_NUMBER
−
   from v$asm_file;
+
    , FILE_NUMBER
 +
    , ROUND ((BYTES / 1024 / 1024), 1)     MB
 +
    , REDUNDANCY
 +
    , TYPE
 +
   FROM V$ASM_FILE;
 
</source>
 
</source>
  
−
== Disk 그룹/패스 조회 ==
+
== ASM Disk 그룹/패스 조회 ==
 +
*  V$ASM_DISK , V$ASM_DISKGROUP
 
<source lang=sql>
 
<source lang=sql>
−
SELECT g.name DG_NAME, d.path PATH, d.total_mb DISK_SIZE_MB,  
+
SELECT G.NAME                      DG_NAME
−
        g.database_compatibility DB_COMPATIBILITY,
+
    , D.PATH                       PATH
−
        g.compatibility ASM_COMPATIBILITY
+
    , D.TOTAL_MB                  DISK_SIZE_MB
−
   FROM v$asm_disk d
+
    , G.DATABASE_COMPATIBILITY    DB_COMPATIBILITY
−
      , v$asm_diskgroup g
+
    , G.COMPATIBILITY              ASM_COMPATIBILITY
−
  WHERE g.group_number=d.group_number;
+
   FROM V$ASM_DISK D
 +
    , V$ASM_DISKGROUP G
 +
  WHERE G.GROUP_NUMBER = D.GROUP_NUMBER;
 
</source>
 
</source>
  
−
== 테이블 스페이스생성 ==
+
== 테이블 스페이스 정보 조회 SQL ==
 +
* V$ASM_DISK_STAT
 +
<source lang=sql>
 +
-- 그룹별 사용량
 +
SELECT GROUP_NUMBER
 +
    , NAME
 +
    , TOTAL_MB
 +
    , FREE_MB
 +
FROM V$ASM_DISK_STAT;
 +
</source>
  
−
https://docs.oracle.com/cd/E11882_01/server.112/e18951/asmdiskgrps.htm#OSTMG10205
+
* V$ASM_DISKGROUP
 +
<source lang=sql>
 +
-- 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
  
−
<source lang=sql>
+
SELECT GROUP_NUMBER                                      "Group#"
−
CREATE BIGFILE TABLESPACE TS_XXX_D01 DATAFILE
+
    , NAME
−
  SIZE 1G AUTOEXTEND ON NEXT 50M MAXSIZE 30G
+
    , TOTAL_MB / 1024                                  TOTAL_GB
−
LOGGING
+
    , ROUND ((TOTAL_MB - USABLE_FILE_MB) / 1024, 2)    USED_GB
−
DEFAULT
+
    , USABLE_FILE_MB / 1024                            USABLE_FILE_GB
−
  NO INMEMORY
+
    , FREE_MB / 1024                                    FREE_GB
−
ONLINE
+
    , 100 - ROUND (FREE_MB / TOTAL_MB * 100)            "usgae(%)"
−
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
+
    , ((FREE_MB - REQUIRED_MIRROR_FREE_MB)) / 1024      USABLE_CALC_GB
−
BLOCKSIZE 8K
+
    , TYPE
−
SEGMENT SPACE MANAGEMENT AUTO
+
    , STATE
−
FLASHBACK ON;
+
  FROM V$ASM_DISKGROUP;
 
</source>
 
</source>
  
−
== 테이블 스페이스 정보 조회 ==
+
* V$ASM_FILE , $ASM_ALIAS , V$ASM_DISKGROUP
 
<source lang=sql>
 
<source lang=sql>
−
select group_number
+
SELECT G.NAME                                    "GROUP_NAME"
−
    , name
+
          -- F.GROUP_NUMBER
−
    , TOTAL_MB
+
          --      , G.TOTAL_MB
−
    , FREE_MB
+
          , ROUND (G.FREE_MB / 1024, 1)              "GROUP_FREE(GB)"
−
from V$asm_disk_stat;
+
          --      , 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);
 +
 
 +
 
 
</source>
 
</source>
 +
[[Category:oracle]]

2024년 4월 2일 (화) 21:20 판

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"