행위

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

DB CAFE
2번째 줄: 2번째 줄:
 
https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util001.htm#OSTMG01620
 
https://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_util001.htm#OSTMG01620
  
−
== asm 명령어 ==
+
== ASM 명령어 ==
−
=== 실행 ===
+
=== 실행명령어 ===
 
<source lang=sql>
 
<source lang=sql>
 
asmcmd
 
asmcmd
 
</source>
 
</source>
 +
=== 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
  
 
== ASM Disk 그룹 조회 ==
 
== ASM Disk 그룹 조회 ==

2020년 4월 21일 (화) 08:45 판

thumb_up 추천메뉴 바로가기

1 ASM 이란?[편집]

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

2 ASM 명령어[편집]

2.1 실행명령어[편집]

asmcmd

2.2 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=4195"