oracle asm 18c

1. oracle asm 

   1) Oracle Automatic Storage Management (Oracle ASM)

    2) Oracle ASM  is a volume manager and file system

    3) Oracle ASM meta 

        - The disks that belong to a disk group

        - The amount of space that is available in a disk group

        - The file names of the files in a disk group

        - The location of disk group data file extents

         - A redo log that records information about atomically changing metadata blocks

2. oracle asm 구조

  1) disk type

     - Normal : 2-way mirroring

      - High : 3-way mirroring

      - External : not use Oracle ASM mirroring, such as when you configure hardware RAID for redundancy

  2) AU ( allocation units )

       An allocation unit is the fundamental unit of allocation within a disk group. A file extent consists of one or more allocation units. An Oracle ASM file consists of one or more file extent

      - The size is 1, 2, 4, 8, 16, 32, or 64 MB.

   

• Extent size always equals the disk group AU size for the first 20000 extent sets (0 - 19999).

• Extent size equals 4*AU size for the next 20000 extent sets (20000 - 39999).

• Extent size equals 16*AU size for the next 20000 and higher extent sets (40000+).

3. oracle asm instance 

1) 파라미터 백업 및 복구

   asmcmd > spget

   asmcmd > spcopy +DATA/ASM/ASMPARAMETERFILE/registry.253.849343867 +FRA/spfileCopyASM.ora

   asmcmd > ls -l --absolutepath FRA/ASM/ASMPARAMETERFILE

   asmcmd > spcopy -u +FRA/spfileCopyASM.ora +DATA/ASM/spfileASM.ora

2) 파라미터 종류

   - ASM_DISKGROUPS 

      ASM_DISKGROUPS = DATA, FRA

   - ASM_DISKSTRING  

      ASM_DISKSTRING = '/dev/rdsk/*disk3', '/dev/rdsk/*disk4'

   - ASM_IO_PROCESSES : 부하에 따라 큰값으로 조정

   - ASM_POWER_LIMIT  : 0 ~ 1024, 기본값 1이며 큰값으로 조정할수록 rebalance 속도 증가하나 부하도 증가

   - DIAGNOSTIC_DEST : 로그 파일 위치

4. oracle asm instance 관리

# sqlplus / as sysasm 계정으로 작업

1) create disk group

  CREATE DISKGROUP data NORMAL REDUNDANCY

  FAILGROUP controller1 DISK

    '/devices/diska1' NAME diska1,

    '/devices/diska2' NAME diska2,

    '/devices/diska3' NAME diska3,

    '/devices/diska4' NAME diska4

  FAILGROUP controller2 DISK

    '/devices/diskb1' NAME diskb1,

    '/devices/diskb2' NAME diskb2,

    '/devices/diskb3' NAME diskb3,

    '/devices/diskb4' NAME diskb4

  ATTRIBUTE 'au_size'='4M',

    'compatible.asm' = '18.0', 

    'compatible.rdbms' = '18.0',

    'compatible.advm' = '18.0';

  select * from v$asm_diskgroup;

  select * from v$asm_attribute;

  CREATE DISKGROUP spbidata external REDUNDANCY disk

    '/dev/oracleasm/disks/SPBIDATAD01', '/dev/oracleasm/disks/SPBIDATAD02',

    '/dev/oracleasm/disks/SPBIDATAD03', '/dev/oracleasm/disks/SPBIDATAD04',

    '/dev/oracleasm/disks/SPBIDATAD05', '/dev/oracleasm/disks/SPBIDATAD06',

    '/dev/oracleasm/disks/SPBIDATAD07', '/dev/oracleasm/disks/SPBIDATAD08',

    '/dev/oracleasm/disks/SPBIDATAD09', '/dev/oracleasm/disks/SPBIDATAD10'

  ATTRIBUTE 'au_size'='4M',  'compatible.asm' = '18.0';

2) alter disk

   - disk 추가

   ALTER DISKGROUP data1 ADD DISK

     '/devices/diska5' NAME diska5,

     '/devices/diska6' NAME diska6,

     '/devices/diska7' NAME diska7,

     '/devices/diska8' NAME diska8;

 

   ALTER DISKGROUP data1 ADD DISK

      '/devices/diskd*'

       REBALANCE POWER 5 WAIT;   ==> disk 추가하고 명령어가 끝날때까지 기다리기

  

   - disk replace

   ALTER DISKGROUP data2 REPLACE DISK diskc7 WITH '/devices/diskc18' POWER 3;

   - disk 삭제

   ALTER DISKGROUP data1 DROP DISK diska5;

   ALTER DISKGROUP data1 UNDROP DISKS;  ==> drop 작업 취소

 

   - disk group rebalance 수정

  ALTER DISKGROUP data2 REBALANCE MODIFY POWER 10;

  ALTER DISKGROUP data2 REBALANCE WITH BALANCE COMPACT;   ==> defragments and compacts extents

   - 작업 소요 시간 예측

   rebalance 작업시 작업 소요 시간 예측

   EXPLAIN WORK FOR ALTER DISKGROUP data DROP DISK data_0000

   SELECT est_work FROM V$ASM_ESTIMATE

   EXPLAIN WORK SET STATEMENT_ID='online' FOR ALTER DISKGROUP data ONLINE disk data_000;

   SELECT est_work FROM V$ASM_ESTIMATE WHERE STATEMENT_ID='online';

   - 작업 상태 확인

   SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

   - 디스크 corrupt and repair

   ALTER DISKGROUP data SCRUB POWER LOW;

   - 신규 disk 확인

   SELECT name, header_status, path FROM V$ASM_DISK WHERE path LIKE '/dev/oracleasm/disks/%'

   - disk group 용량 확인

   SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;

5. views 정보 보기

1) diskgroup

SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
     substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;

2) diskgroup내 disks 보기

SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
     d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup 
     FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;

3) diskgroup내 disk 사용 상태 확인

SELECT SUBSTR(dgs.name,1,10) AS diskgroup, SUBSTR(ds.name,1,10) AS asmdisk, 
       ds.mount_status, ds.state, ds.reads, ds.writes, ds.read_time, ds.write_time, 
       bytes_read, bytes_written 
       FROM V$ASM_DISKGROUP_STAT dgs, V$ASM_DISK_STAT ds 
       WHERE dgs.group_number = ds.group_number AND dgs.name = 'DATA';

4) asm client 확인

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
    SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
    SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible 
    FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c  
    WHERE dg.group_number = c.group_number;