DATABASE(oracleDB 11g)/DBA

[Oracle DBA]ASM Instance 관리

SEUNGSAMI 2019. 3. 26. 17:16
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








      'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글

      [Oracle DBA]데이터 동시성 관리  (0) 2019.04.03
      [Oracle DBA]유저 보안 관리  (0) 2019.04.01
      [Oracle DBA]데이터베이스 저장 영역 구조 관리  (0) 2019.03.28
      [Oracle DBA]Oracle 네트워크 관리  (0) 2019.03.27
      [Oracle DBA]오라클 데이터베이스 Instance 관리  (0) 2019.03.25

      'DATABASE(oracleDB 11g)/DBA'의 다른글

      • 현재글[Oracle DBA]ASM Instance 관리

      관련글

      댓글

      티스토리툴바