DB스토리

--설치 절차 요약

1. ASM 관련 rpm 설치
2. df 공간 확인, fdisk 로 partition 생성.
3. vi /etc/sysconfig/rawdevices     <= partition을 row 장비로 인식
4. chown, chmod  => row 파일들.
5. vi /etc/udev/permission/50-udev.permissions  => raw 포함줄에  oracle:oinstall 로 수정.

startup은

$ORACLE_HOME/dbs/spfile($ORACLE_SID).ora 파일을 자동으로 읽음.

$ORACEL_HOME 에 보면 +ASM 이 생긴걸 확인 할 수 있다.

ASM 인스턴스로 접속할려면 SID 를 +ASM 으로 바꾸면 접속 가능

.bash_profile 에서  바꾸면 됨.

ORACLE_SID=+ASM;export ORACLE_SID  이렇게 바꾸어도 됨.

즉 다른 인스턴스에 접속할려면 sid를 바꾸어 주면 됨.

sqlplus / as sysdba 들어가서

unique 파라미터 보면 +ASM 으로 나옴.

더 짧게.

ORACLE_SID=+ASM;sqlplus / as sysdba

주요 view

v$asm_disk

v$asm_diskgroup

SQL> select status from v$instance;

STATUS
------------
STARTED

최종적으로 마운트 모드여서 딕셔너리 뷰 검색 불가.

검색 및 실행 asm을 사용하는 db 인스턴스에서 할 것.

SQL> select path from v$asm_disk;

PATH
---------------------------------------------------------------------------------------------------------------------
/dev/raw/raw1
/dev/raw/raw2
/dev/raw/raw5
/dev/raw/raw4
/dev/raw/raw6
/dev/raw/raw3

6 rows selected.

SQL> select name from v$asm_diskgroup;

no rows selected

 그룹만들고 나면.

SQL> select name from v$asm_diskgroup;

NAME
------------------------------
DISK1

[oracle@orcl dbs]$ asmcmd
ASMCMD> cd DISK1
ASMCMD> cd orcl
ASMCMD> cd datafile
ASMCMD> ls
EXAMPLE.260.691077697
SYSAUX.259.691077697
SYSTEM.258.691077697
UNDOTBS1.261.691077697
USERS.262.691077717

명령어 리스트 보는 방법..

ASMCMD> sdfsfsf            막치면됨..ㅡㅡ;

     commands:
        --------
        cd
        du
        find
        help
        ls
        lsct
        lsdg
        mkalias
        mkdir
        pwd
        rm
        rmalias

 ASMCMD> help
        asmcmd [-p] [command]

        The environment variables ORACLE_HOME and ORACLE_SID determine the
        instance to which the program connects, and ASMCMD establishes a
        bequeath connection to it, in the same manner as a SQLPLUS / AS
        SYSDBA.  The user must be a member of the SYSDBA group.

        Specifying the -p option allows the current directory to be displayed
        in the command prompt, like so:

        ASMCMD [+DATAFILE/ORCL/CONTROLFILE] >

        [command] specifies one of the following commands, along with its
        parameters.

        Type "help [command]" to get help on a specific ASMCMD command.

        commands:
        --------
        cd
        du
        find
        help
        ls
        lsct
        lsdg
        mkalias
        mkdir
        pwd
        rm
        rmalias

ASMCMD> pwd
+DISK1/orcl/datafile
ASMCMD> ls
EXAMPLE.260.691077697
SYSAUX.259.691077697
SYSTEM.258.691077697
UNDOTBS1.261.691077697
USERS.262.691077717

 ASMCMD> pwd
+DISK1/orcl/controlfile
ASMCMD> ls
Backup.263.691077721
backup.256.691077683
backup.257.691077683

--orcl 인스턴스에서

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DISK1/orcl/controlfile/backup
                                                 .256.691077683, +DISK1/orcl/co
                                                 ntrolfile/backup.257.691077683

 SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------------------------------
+DISK1/orcl/datafile/system.258.691077697
+DISK1/orcl/datafile/undotbs1.261.691077697
+DISK1/orcl/datafile/sysaux.259.691077697
+DISK1/orcl/datafile/users.262.691077717
+DISK1/orcl/datafile/example.260.691077697

ASM 인스턴스 끈 상태에서 db 인스턴스 실행시키면 nomount 모드에서 중지됨.

이유는 control 파일이 ASM 에 있기 때문에..

DB 켜는 단계에서

lsnrctl start

ORACLE_SID=+ASM;sqlplus / as sysdba

sqlplus / as sysdba

isqlplusctl start

emctl start dbconsole   or ORACLE_SID=orcl;emctl start dbconsole

start.sh

lsnrctl start
ORACLE_SID=+ASM;sqlplus / as sysdba<<EOF
startup
exit
EOF
ORACLE_SID=orcl;sqlplus / as sysdba<<EOF
startup
exit
EOF
isqlplusctl start
ORACLE_SID=orcl;emctl start dbconsole

stop.sh

ORACLE_SID=orcl;emctl stop dbconsole
isqlplusctl stop
ORACLE_SID=orcl;sqlplus / as sysdba<<EOF
shutdown immediate
exit
EOF
ORACLE_SID=+ASM;sqlplus / as sysdba<<EOF
shutdown immediate
exit
EOF
lsnrctl stop

--디스크 그룹에서 디스크 지우기.

 SQL> select group_number, name from v$asm_diskgroup;

GROUP_NUMBER NAME
------------ ------------------------------
           1 DISK1

SQL> select name from v$asm_disk where group_number=1;

NAME
------------------------------
DISK1_0000
DISK1_0001
DISK1_0002
DISK1_0003

SQL> alter diskgroup disk1 drop disk disk1_0000,disk1_0001;

Diskgroup altered.

-- Failue Group 만들기

-만들기 전 확인

SQL> select group_number,path,name from v$asm_disk
  2  where group_number is null;

미리 만들어서 확인을 못했을뿐...만들기 전 확인하면 나옴.

 나오는게 빈 것 이기 때문에 추가해주면 됨.

CREATE DISKGROUP DISK2 NORMAL REDUNDANCY
FAILGROUP DIKS2_B DISK
        '/dev/raw/raw3' SIZE 392 M ,
        '/dev/raw/raw4' SIZE 392 M
FAILGROUP DIKS2_A DISK
        '/dev/raw/raw1' SIZE 392 M ,
        '/dev/raw/raw2' SIZE 392 M
/

 SQL> @asm_addDiskGroup

Diskgroup created.

-확인

SQL> select group_number,name from v$asm_diskgroup;

GROUP_NUMBER NAME
------------ ------------------------------
           1 DISK1
           2 DISK2

SQL> select failgroup,path,name from v$asm_disk
  2  where group_number=2;

FAILGROUP                      PATH                 NAME
------------------------------ -------------------- ------------------------------
DIKS2_A                        /dev/raw/raw1        DISK2_0002
DIKS2_A                        /dev/raw/raw2        DISK2_0003
DIKS2_B                        /dev/raw/raw3        DISK2_0000
DIKS2_B                        /dev/raw/raw4        DISK2_0001

managing Disk Groups

-db

SQL> create tablespace x datafile '+DISK2';

Tablespace created.

-asm

SQL> !
[oracle@orcl ~]$ asmcmd
ASMCMD> cd disk2
ASMCMD> cd ORCL
ASMCMD> cd datafile
ASMCMD> ls -als
Type      Redund  Striped  Time             Sys  Block_Size  Blocks      Bytes      Space  Name
DATAFILE  MIRROR  COARSE   JUL 01 17:00:00  Y          8192   12801  104865792  214958080  none => X.256.691089699

SQL> drop diskgroup disk2;
drop diskgroup disk2
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "DISK2" contains existing files

 SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------------------------------
+DISK1/orcl/datafile/system.258.691077697
+DISK1/orcl/datafile/undotbs1.261.691077697
+DISK1/orcl/datafile/sysaux.259.691077697
+DISK1/orcl/datafile/users.262.691077717
+DISK1/orcl/datafile/example.260.691077697
+DISK2/orcl/datafile/x.256.691089699

6 rows selected.

SQL> drop tablespace x;

Tablespace dropped.

SQL>

SQL> drop diskgroup disk2;

Diskgroup dropped.

SQL>

 --ASM File

SQL> create tablespace ts3 datafile '+DISK1' size 10M;

Tablespace created.

SQL> create tablespace ts4 datafile size 10M;

Tablespace created.

SQL> show parameter db_create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DISK1
SQL>