[Oracle DBA]ASM Instance 관리

ASM Instance 관리

ASM

스토리지관리도구와 스토리지의 통합

• ASM 사용 (Automatic Storage Management)
• SAME(Striping and Mirroring Everything) : 기능을 제공하는 stoage 관리 도구
• 소프트웨어적으로 RAID를 구성
• striping을 하면 I/O 성능이 개선
• mirroring을 하면 데이터를 다중화, 데이터를 복구하기 용이
• +DATA(Disk Group) : 논리적 구조
• 그 안에 4개의 공간이 존재한다면(각각2G) 데이터파일(100M)을 생성할때, 25M 씩 여러 디스크에 걸쳐서 만들어진다 : Striping
• RAID와는 달리 자신의 사이즈에 스트라이핑하는 것이 아닌, 데이터 파일 단위의 스트라이핑을 진행한다.(단위는 Au: Allocation Unit)
• I/O에 적합한 스트라이핑 진행
• Storage
• RAID는 논리적 볼륨 단위로 striping하지만, ASM은 file level에서 AU(Allocation Unit)단위로 (default : 1M) 할당하므로 Oracle DB I/O에 적합
• 디스크 그룹과 데이터베이스는 N : N의 관계이다

ASM Instance

• Shared Pool
• Meta Data 정보에 사용
• Large Pool
• 병렬작업에 사용
• ASM Cache
• 리밸런스 작업 중 읽기 및 쓰기 블록에 사용
• 사용 가능 메모리
• 사용 가능한 할당 해제된 메모리
• RBAL
• 검색 중에 모든 장치를 열고 리밸런스 작접 조정
• ARBn
• 리밸런스 작업을 수행하는 하나 이상의 Slave Process
• 위의 것들은 반드시 알아두자

[orcl2:~]$ . oraenv

ORACLE_SID = [orcl2] ? +ASM

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle

[+ASM:~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 26 11:14:10 2019

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Automatic Storage Management option

SQL> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATA/asm/asmparameterfile/reg

                                                 istry.253.1002621737

SQL> col name for a50

SQL> col value for a50

SQL> show parameter instance

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cluster_database_instances           integer     1

instance_name                        string      +ASM

instance_number                      integer     1

instance_type                        string      asm

SQL> show parameter asm

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

asm_diskgroups                       string      FRA

asm_diskstring                       string

asm_power_limit                      integer     1

asm_preferred_read_failure_groups    string

SQL> !ps -ef|grep +ASM

oracle    5236     1  0 09:34 ?        00:00:00 asm_pmon_+ASM

oracle    5238     1  1 09:34 ?        00:01:36 asm_vktm_+ASM

oracle    5242     1  0 09:34 ?        00:00:00 asm_gen0_+ASM

oracle    5244     1  0 09:34 ?        00:00:01 asm_diag_+ASM

oracle    5246     1  0 09:34 ?        00:00:00 asm_psp0_+ASM

oracle    5248     1  0 09:34 ?        00:00:02 asm_dia0_+ASM

oracle    5250     1  0 09:34 ?        00:00:00 asm_mman_+ASM

oracle    5252     1  0 09:34 ?        00:00:00 asm_dbw0_+ASM

oracle    5254     1  0 09:34 ?        00:00:00 asm_lgwr_+ASM

oracle    5256     1  0 09:34 ?        00:00:01 asm_ckpt_+ASM

oracle    5258     1  0 09:34 ?        00:00:00 asm_smon_+ASM

oracle    5260     1  0 09:34 ?        00:00:01 asm_rbal_+ASM

oracle    5262     1  0 09:34 ?        00:00:01 asm_gmon_+ASM

oracle    5264     1  0 09:34 ?        00:00:00 asm_mmon_+ASM

oracle    5266     1  0 09:34 ?        00:00:02 asm_mmnl_+ASM

oracle    5305     1  0 09:35 ?        00:00:00 asm_asmb_+ASM

oracle    5307     1  0 09:35 ?        00:00:01 oracle+ASM_asmb_+asm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    5392     1  0 09:35 ?        00:00:01 oracle+ASM_asmb_orcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    7603  7602  0 11:14 ?        00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    7826  7602  0 11:21 pts/1    00:00:00 /bin/bash -c ps -ef|grep +ASM

oracle    7828  7826  0 11:21 pts/1    00:00:00 grep +ASM

SQL> select name, value from v$diag_info;

NAME                                               VALUE

-------------------------------------------------- ------------------------------------------------

Diag Enabled                                       TRUE

ADR Base                                           /u01/app/oracle

ADR Home                                           /u01/app/oracle/diag/asm/+asm/+ASM

Diag Trace                                         /u01/app/oracle/diag/asm/+asm/+ASM/trace

Diag Alert                                         /u01/app/oracle/diag/asm/+asm/+ASM/alert

Diag Incident                                      /u01/app/oracle/diag/asm/+asm/+ASM/incident

Diag Cdump                                         /u01/app/oracle/diag/asm/+asm/+ASM/cdump

Health Monitor                                     /u01/app/oracle/diag/asm/+asm/+ASM/hm

Default Trace File                               /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_ora_

                                                   7603.trc

Active Problem Count                               0

Active Incident Count                              0

SQL> !ps -ef|grep asmb

oracle    5305     1  0 09:35 ?        00:00:00 asm_asmb_+ASM

oracle    5307     1  0 09:35 ?        00:00:02 oracle+ASM_asmb_+asm (DESCRIPTION=(LOCAL=YES)                                                                            (ADDRESS=(PROTOCOL=beq)))

oracle    5386     1  0 09:35 ?        00:00:00 ora_asmb_orcl

oracle    5392     1  0 09:35 ?        00:00:01 oracle+ASM_asmb_orcl (DESCRIPTION=(LOCAL=YES)                                                                            (ADDRESS=(PROTOCOL=beq)))

oracle    8259  7602  0 11:39 pts/1    00:00:00 /bin/bash -c ps -ef|grep asmb

oracle    8261  8259  0 11:39 pts/1    00:00:00 grep asmb

-- 여기서 asmb는 DB instance와 ASM 사이의 상호작용 역할을 해준다.

데이터베이스 Instance와 ASM사이의 상호작용

ASM Instance : Dynamic Performance Views

• V$ASM_DISK
• V$ASM_DISKGROUP
• V$ASM_FILE
• 등등...

SQL> select * from v$asm_diskgroup;

SQL> select * from v$asm_disk;

SQL> select * from v$asm_file;

ASM 시스템 권한

• SYSASM(1순위)
• OSASM(asmadmin)
• 모든 관리 권한
• SYSDBA(2순위)
• OSDBA(asmdba)
• ASM에 저장된 데이터에 대한 액세스 권한 및 현재 릴리스의 SYSASM
• SYSOPER(3순위)
• OSOPER(asmoper)
• 비 파괴적인 ALTER DISKGROUP 명령어와 함께 ASM Instance를 시작 및 정지할 수 있는 제한된 권한
• SYSDBA와 SYSOPER의 차이는 DBA는 CREATE를 할수 있고, RECOVER을 할때 불완전 복구를 진행 할 수 있다.

DB Instance와 ASM Instance의 관계 (startup, shutdown 순서)

• STARTUP 

1. ASM Instance startup
2. DB Instance startup

• $ srvctl start asm -o open
• $ srvctl start database -d orcl -o open

SHUTDOWN 

1. DB Instance Shutdown
2. ASM Instance Shutdown

$ srvctl stop database -d orcl -o immediate

$ srvctl stop asm -o immediate -f

DISK GROUP이 MOUNT하기 위해서는 ASM Instance를 기동해야한다.

======현재 orcl DB는 기동중이다.======

SQL> !ps -ef|grep orcl

oracle    5356     1  0 09:35 ?        00:00:01 ora_pmon_orcl

oracle    5358     1  1 09:35 ?        00:02:20 ora_vktm_orcl

oracle    5362     1  0 09:35 ?        00:00:01 ora_gen0_orcl

oracle    5364     1  0 09:35 ?        00:00:01 ora_diag_orcl

oracle    5366     1  0 09:35 ?        00:00:01 ora_dbrm_orcl

oracle    5368     1  0 09:35 ?        00:00:01 ora_psp0_orcl

oracle    5370     1  0 09:35 ?        00:00:03 ora_dia0_orcl

oracle    5372     1  0 09:35 ?        00:00:01 ora_mman_orcl

oracle    5374     1  0 09:35 ?        00:00:01 ora_dbw0_orcl

oracle    5376     1  0 09:35 ?        00:00:01 ora_lgwr_orcl

oracle    5378     1  0 09:35 ?        00:00:02 ora_ckpt_orcl

oracle    5380     1  0 09:35 ?        00:00:00 ora_smon_orcl

oracle    5382     1  0 09:35 ?        00:00:00 ora_reco_orcl

oracle    5384     1  0 09:35 ?        00:00:01 ora_rbal_orcl

oracle    5386     1  0 09:35 ?        00:00:00 ora_asmb_orcl

oracle    5388     1  0 09:35 ?        00:00:02 ora_mmon_orcl

oracle    5390     1  0 09:35 ?        00:00:04 ora_mmnl_orcl

oracle    5392     1  0 09:35 ?        00:00:02 oracle+ASM_asmb_orcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle    5394     1  0 09:35 ?        00:00:00 ora_d000_orcl

oracle    5396     1  0 09:35 ?        00:00:00 ora_s000_orcl

oracle    5398     1  0 09:35 ?        00:00:02 ora_mark_orcl

oracle    5462     1  0 09:35 ?        00:00:00 ora_qmnc_orcl

oracle    5477     1  0 09:35 ?        00:00:01 ora_cjq0_orcl

oracle    5499     1  0 09:35 ?        00:00:00 ora_q000_orcl

oracle    5501     1  0 09:35 ?        00:00:00 ora_q001_orcl

oracle    5863     1  0 09:40 ?        00:00:01 ora_smco_orcl

oracle    8529     1  0 12:00 ?        00:00:00 ora_w000_orcl

oracle    8652  7602  0 12:09 pts/1    00:00:00 /bin/bash -c ps -ef|grep orcl

oracle    8654  8652  0 12:09 pts/1    00:00:00 grep orcl

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

+ASM

SYS@ASM> shutdown immediate

ORA-15097: cannot SHUTDOWN ASM instance with connected client

-- DB Instance가 켜져있기 때문에 불가

SYS@ASM> shutdown abort

ASM instance shutdown

SYS@ASM> !ps -ef|grep orcl

oracle    8748  7602  0 12:13 pts/1    00:00:00 /bin/bash -c ps -ef|grep orcl

oracle    8750  8748  0 12:13 pts/1    00:00:00 grep orcl

-- DB Instance가 강제 종료된 것을 볼 수 있다.

[+ASM:~]$ . oraenv

ORACLE_SID = [+ASM] ? orcl

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

[orcl:~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 26 12:15:19 2019

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

Connected to an idle instance.

SYS@orcl> startup

ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'

ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora

ORA-15077: could not locate ASM instance serving a required diskgroup

-- ASM Instance가 꺼져있기 때문에 DB Instance가 실행되지 않는다.

SYS@ASM> !ps -ef|grep lgwr

oracle    5114     1  0 09:34 ?        00:00:02 ora_lgwr_orcl2

oracle    8869     1  0 12:16 ?        00:00:00 asm_lgwr_+ASM

oracle    9185     1  0 12:16 ?        00:00:00 ora_lgwr_orcl

oracle   10293  8807  0 13:36 pts/1    00:00:00 /bin/bash -c ps -ef|grep lgwr

oracle   10295 10293  0 13:36 pts/1    00:00:00 grep lgwr

SYS@orcl> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ASM> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

SYS@orcl> startup

ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/orcl/spfileorcl.ora'

ORA-17503: ksfdopn:2 Failed to open file +DATA/orcl/spfileorcl.ora

ORA-15077: could not locate ASM instance serving a required diskgroup

-- ASM이 꺼져있으므로 DB가 시작되지 않음

SYS@+ASM> startup

ASM instance started

Total System Global Area  284565504 bytes

Fixed Size                  1336036 bytes

Variable Size             258063644 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

SYS@orcl> startup

ORACLE instance started.

Total System Global Area  577511424 bytes

Fixed Size                  1338000 bytes

Variable Size             390071664 bytes

Database Buffers          180355072 bytes

Redo Buffers                5746688 bytes

Database mounted.

Database opened.

SYS@+ASM> show parameter instance

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cluster_database_instances           integer     1

instance_name                        string      +ASM

instance_number                      integer     1

instance_type                        string      asm

SYS@+ASM> show parameter asm

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

asm_diskgroups                       string      DATA, FRA

asm_diskstring                       string

asm_power_limit                      integer     1

asm_preferred_read_failure_groups    string

SYS@orcl> show parameter instance

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

active_instance_count                integer

cluster_database_instances           integer     1

instance_groups                      string

instance_name                        string      orcl

instance_number                      integer     0

instance_type                        string      RDBMS

open_links_per_instance              integer     4

parallel_instance_group              string

parallel_server_instances            integer     1

SYS@orcl> show parameter asm

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

asm_diskgroups                       string

asm_diskstring                       string

asm_power_limit                      integer     1

asm_preferred_read_failure_groups    string

ASMCMD

• 마치 파일 시스템처럼
• 리눅스와 비슷한 커맨드를 이용

[+ASM:~]$ asmcmd

ASMCMD> ls

DATA/

FRA/

-- 마운트 되어있는 디스크 그룹 목록

ASMCMD> cd DATA/

ASMCMD> ls

ASM/

ORCL/

-- DATA Disk Group에 ASM/, ORCL/ 두개의 인스턴스가 있는 것을 확인할 수 있다.

ASMCMD> ls ASM/

ASMPARAMETERFILE/

====orcl DB====

ASMCMD> ls ORCL

CONTROLFILE/

DATAFILE/

ONLINELOG/

PARAMETERFILE/

TEMPFILE/

spfileorcl.ora

ASMCMD> cd ORCL

ASMCMD> ls CONTROLFILE

Current.260.1002622879

ASMCMD> ls DATAFILE

EXAMPLE.265.1002622931

SYSAUX.257.1002622777

SYSTEM.256.1002622777

UNDOTBS1.258.1002622777

USERS.259.1002622777

ASMCMD> ls ONLINELOG

group_1.261.1002622881

group_2.262.1002622887

group_3.263.1002622893

ASMCMD> ls PARAMETERFILE

spfile.266.1002623067

VASMCMD> ls TEMPFILE

TEMP.264.1002622927

SYS@orcl> show parameter spfile

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATA/orcl/spfileorcl.ora

ASMCMD> shutdown --abort

ASM instance shutdown

Connected to an idle instance.

ASMCMD> startup

ASM instance started

Total System Global Area  284565504 bytes

Fixed Size                  1336036 bytes

Variable Size             258063644 bytes

ASM Cache                  25165824 bytes

ASM diskgroups mounted

ASM 디스크

• 할당 단위로 나뉜다(AU)
• 디스크 그룹 생성시 구성
• 기본 크기는 1MB

ASM file 생성시 extent 할당 단위

• 0~19999 : 1AU    -    1 M
• 그다음 20000~39999  : 4AU   -   4M
• 그다음 40000~ : 16AU    -    16M
• 이유
• extent 갯수가 많다는 것은 메모리 저장에 extent map에 정보가 많다는 것.
• file이 커지면 AU 갯수가 많아지니까 메모리에 저장된 extent에 대한 메타데이터 정보를 줄이기 위해서.
• datafile은 기본 AU가 1M이다 하지만 64M 까지도 가능하다.
• control file이나 redo log file은 1M가 너무 크기 때문에 기본이 128K 단위로 할당(Fine grain striping)

ASM Failure 그룹

• 미러링을 위해서 +DATA에 1,2,3,4 디스크 그룹이 존재한다 할때 1번 그룹의 A라는 데이터는 1번 그룹이 아닌 2번 혹은 3번 혹은 4번에 복제 되어야한다. 
• 하지만 1번의 A라는 데이터를 2번 디스크 그룹에 저장했다고 했을때, 1번과 2번 그룹은 disk contoroler가 묶여서 이것이 나갈경우 두 데이터를 리커버리 해야하는 문제가 발생한다.
• 그러므로 같은 diskcontroler로 묶인 2번 디스크 그룹이 아닌 다른 disk controler로 묶인 3번 혹은 4번에 미러링 이미지를 저장하는 다중화 단위를 Failure 그룹이라고 한다.

SYS@+ASM>  select group_number, disk_number,  name, failgroup_type from v$asm_disk

GROUP_NUMBER DISK_NUMBER NAME                           FAILGRO

------------ ----------- ------------------------------ -------

           0           8                                REGULAR

           0           9                                REGULAR

           0          10                                REGULAR

           0          11                                REGULAR

           0          12                                REGULAR

           1           0 ASMDISK01                      REGULAR

           1           2 ASMDISK02                      REGULAR

           1           1 ASMDISK03                      REGULAR

           1           3 ASMDISK04                      REGULAR

           2           0 ASMDISK05                      REGULAR

           2           1 ASMDISK06                      REGULAR

           2           2 ASMDISK07                      REGULAR

           2           3 ASMDISK08                      REGULAR