| # 요약 | |
# file system
- os memory를 거쳐 oracle memory로 load되는 방식
- memory I/O일 때는 문제가 없지만 disk I/O일 때는 거쳐가야한다.
- Conventianal Write : oracle memory의 buffer를 os memory를 거쳐 disk write 된다.
- 이런 문제를 해결하고자 direct path write 방식도 나왔다.
# raw device
- os memory를 거치지 않고 oracle memory로 바로 load 된다.
- 장점 : physical read 성능이 file system 보다는 월등히 좋다.
- 단점 : 유지 보수가 어렵다.
■ ASM(Automatic Storage Management)
- I/O 작업이 자동으로 모든 디스크에 고르게 분산, 디스크 레벨의 hot spot이나 병목(bottleneck)이 없어진다.
- 디스크를 동적으로 추가, 삭제할 수 있으며 데이터 재분산 작업을 자동화 수행한다.
SAME(Stripe And Mirror Everything)
- raid 0 : striping
- raid 1 : mirroring
■ ASM Instance
- ASM cache를 관리하는 전용 메모리 인스턴스
- 디스크에 대한 I/O관리(단, os 메모리를 거치지 않음)
- 오라클 인스턴스하고는 서로 소통하면서 asm storage 관리한다.
- shared pool : 디스크에 대한 메타 데이터 정보
- large pool : 리밸런싱을 위한 병렬작업에 사용
- asm cache : 리밸런스 작업 중 읽기 읽기 및 쓰기 블록 사용
- 최소 권장 멤리 사이즈는 256M byte
- 리밸런싱 관련 백그라운드 프로세스
- RBAL : 리밸런스 작업을 조성하고 ARBn에게 지시
- ARBn : 리밸런스 데이터 EXTENT 이동 수행
■ ASM 제한 사항
- 스토리지 시스템 당 63개의 disk group만 지운
- ASM DISK 당 최대 4 petabyte 지원(logical volume의 최대 크기가 4pbyte까지 지원)
- 스토리지 시스텐당 최대 40 pexabyte 지원
- 각 디스크 그룹 당 최대 백만개 파일 지원
- 최대 파일 크기
- normal redundancy(2-way mirroring) : 23PB
- high redundancy(3-way mirroring) : 15PB
- external redundancy(외부 raid 방식 또는 none) : 140PB
■ ASM AU(Allocation Unit)
- AU 크기는 디스크 그룹 생성 시 구성
- AU_SIZE : 기본값 1M (1, 2, 4, 8, 16, 32, 64)
- 오라클은 AU 크기를 자동으로 관리한다.(11g)
- 처음부터 2만개 : AU_SIZE
- 다음 2만개 : AU_SIZE x 4
- 다음 나머지 : AU_SIZE x 16
- 10g AU_SIZE는 1M로 고정되어 있었다. 이를 개선하게 11g 이다.
■ ASM 백업
- RMAN만 사용
- RMAN을 위한 DISK, flash recovery area를 위한 별도의 저장공간을 필요로 한다.
- 백업 디스크는 미러링이 필요없다.
# ASM1에서 sqlplus 접속
--- +ASM1 접속
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [racdb1] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
--- sqlplus 접속 시 SYSASM 사용한다. SYSDBA로 연결해도 조회는 가능하지만 안되는게 많다.
[oracle@rac1 ~]$ sqlplus / as sysasm
...
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options
SYS@+ASM1>
SYS@+ASM1> show sga
Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes -- shared pool, large pool 포함
ASM Cache 25165824 bytes
--- 디스크 그룹
SYS@+ASM1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ ------------------------------ ------ -----------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
--- 디스크 목록
SYS@+ASM1> select group_number, name, disk_number, mount_status, path, total_mb from v$asm_disk;
GROUP_NUMBER NAME DISK_NUMBER MOUNT_STATUS PATH TOTAL_MB
------------ -------------------- ----------- ------------ ------------------------------- --------
2 FRA_0001 1 CACHED /dev/oracleasm/disks/ASMDISK06 5114
2 FRA_0000 0 CACHED /dev/oracleasm/disks/ASMDISK05 5114
1 DATA_0003 3 CACHED /dev/oracleasm/disks/ASMDISK04 5114
1 DATA_0002 2 CACHED /dev/oracleasm/disks/ASMDISK03 5114
1 DATA_0001 1 CACHED /dev/oracleasm/disks/ASMDISK02 5114
1 DATA_0000 0 CACHED /dev/oracleasm/disks/ASMDISK01 5114- MOUNT_STATUS
- CACHED : 디스크 사용
- CLOESED : 디스크 미사용
--- 디스크 그룹별 디스크 상태
SYS@+ASM1> select b.name as group_name, a.name as disk_name, a.header_status, a.state,
a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
GROUP_NAME DISK_NAME HEADER_STATU STATE TOTAL_MB FREE_MB
---------- ------------------------------ ------------ -------------------- -------- ----------
FRA FRA_0001 MEMBER NORMAL 5114 4891
FRA FRA_0000 MEMBER NORMAL 5114 4897
DATA DATA_0003 MEMBER NORMAL 5114 4063
DATA DATA_0002 MEMBER NORMAL 5114 4023
DATA DATA_0001 MEMBER NORMAL 5114 4032
DATA DATA_0000 MEMBER NORMAL 5114 4029
--- 디스크 그룹 내 파일 목록
SYS@+ASM1> select group_number,file_number,bytes,redundancy,type from v$asm_file;
GROUP_NUMBER FILE_NUMBER BYTES REDUNDANCY TYPE
------------ ----------- ---------- ---------- ----------------
1 253 1536 MIRROR ASMPARAMETERFILE
1 255 272756736 MIRROR OCRFILE
1 256 713039872 MIRROR DATAFILE
1 257 513810432 MIRROR DATAFILE
1 258 99622912 MIRROR DATAFILE
1 259 5251072 MIRROR DATAFILE
1 260 18563072 HIGH CONTROLFILE
1 261 52429312 MIRROR ONLINELOG
1 262 52429312 MIRROR ONLINELOG
1 263 32514048 MIRROR TEMPFILE
1 264 104865792 MIRROR DATAFILE
1 265 26222592 MIRROR DATAFILE
1 266 52429312 MIRROR ONLINELOG
1 267 52429312 MIRROR ONLINELOG
1 268 3584 MIRROR PARAMETERFILE
2 256 18563072 UNPROT CONTROLFILE
2 257 52429312 UNPROT ONLINELOG
2 258 52429312 UNPROT ONLINELOG
2 259 52429312 UNPROT ONLINELOG
2 260 52429312 UNPROT ONLINELOG
2 261 16197120 UNPROT ARCHIVELOG
2 262 1024 UNPROT ARCHIVELOG
2 263 1024 UNPROT ARCHIVELOG
2 264 16805376 UNPROT ARCHIVELOG
2 265 4312576 UNPROT ARCHIVELOG
2 266 1024 UNPROT ARCHIVELOG
2 267 414720 UNPROT ARCHIVELOG
2 268 1024 UNPROT ARCHIVELOG
2 269 366592 UNPROT ARCHIVELOG
2 270 1024 UNPROT ARCHIVELOG
2 271 1024 UNPROT ARCHIVELOG
2 272 1536 UNPROT ARCHIVELOG
2 273 1024 UNPROT ARCHIVELOG
2 274 1011200 UNPROT ARCHIVELOG
2 275 855040 UNPROT ARCHIVELOG
2 276 1024 UNPROT ARCHIVELOG
2 277 3281920 UNPROT ARCHIVELOG
2 278 1940480 UNPROT ARCHIVELOG
2 279 1024 UNPROT ARCHIVELOG
2 280 4104704 UNPROT ARCHIVELOG
2 281 1024 UNPROT ARCHIVELOG
2 282 2006016 UNPROT ARCHIVELOG
2 283 1148928 UNPROT ARCHIVELOG
2 284 10407424 UNPROT ARCHIVELOG
2 285 3785216 UNPROT ARCHIVELOG
2 286 1024 UNPROT ARCHIVELOG
2 287 10009600 UNPROT ARCHIVELOG
2 288 1024 UNPROT ARCHIVELOG
2 289 10966528 UNPROT ARCHIVELOG
2 290 1024 UNPROT ARCHIVELOG
2 291 1024 UNPROT ARCHIVELOG
2 292 50688 UNPROT ARCHIVELOG
2 293 1024 UNPROT ARCHIVELOG> 아카이브는 FRA 그룹에 속해있다.
SYS@+ASM1> select * from v$asm_template where group_number = 1;
GROUP_NUMBER ENTRY_NUMBER REDUNDANCY STRIPE SYSTEM NAME PRIMARY_REGION MIRROR_REGION
------------ ------------ ---------- ------ ------ -------------------- -------------- -------------
1 60 MIRROR COARSE Y PARAMETERFILE COLD COLD
1 61 MIRROR COARSE Y ASMPARAMETERFILE COLD COLD
1 62 MIRROR COARSE Y ASMPARAMETERBAKFILE COLD COLD
1 63 MIRROR COARSE Y DUMPSET COLD COLD
1 64 HIGH FINE Y CONTROLFILE COLD COLD
1 65 MIRROR COARSE Y FLASHFILE COLD COLD
1 66 MIRROR COARSE Y ARCHIVELOG COLD COLD
1 67 MIRROR COARSE Y ONLINELOG COLD COLD
1 68 MIRROR COARSE Y DATAFILE COLD COLD
1 69 MIRROR COARSE Y TEMPFILE COLD COLD
1 170 MIRROR COARSE Y BACKUPSET COLD COLD
1 171 MIRROR COARSE Y AUTOBACKUP COLD COLD
1 172 MIRROR COARSE Y XTRANSPORT COLD COLD
1 173 MIRROR COARSE Y CHANGETRACKING COLD COLD
1 174 MIRROR COARSE Y FLASHBACK COLD COLD
1 175 MIRROR COARSE Y DATAGUARDCONFIG COLD COLD
1 176 MIRROR COARSE Y OCRFILE COLD COLD
1 177 MIRROR COARSE Y OCRBACKUP COLD COLD
1 178 HIGH COARSE Y ASM_STALE COLD COLD- STRIPE : 스트라이핑
- COARSE : AU_SIZE 1MB 기본값
- FINE : AU_SIZE 128KB -- 용량이 많이 필요없기 때문
# 새로운 ASM Disk 생성
--- 전체 노드 종료
[oracle@rac1 ~]$ crs_stop -all
CRS-2500: Cannot stop resource 'ora.gsd' as it is not running
CRS-2500: Cannot stop resource 'ora.oc4j' as it is not running
Attempting to stop `ora.LISTENER.lsnr` on member `rac2`
CRS-2789: Cannot stop resource 'ora.gsd' as it is not running on server 'rac1'
Attempting to stop `ora.LISTENER.lsnr` on member `rac1`
Attempting to stop `ora.ons` on member `rac1`
CRS-2789: Cannot stop resource 'ora.gsd' as it is not running on server 'rac2'
Attempting to stop `ora.DATA.dg` on member `rac2`
Attempting to stop `ora.FRA.dg` on member `rac2`
Attempting to stop `ora.racdb.db` on member `rac2`
Attempting to stop `ora.DATA.dg` on member `rac1`
Attempting to stop `ora.FRA.dg` on member `rac1`
Attempting to stop `ora.racdb.db` on member `rac1`
Attempting to stop `ora.eons` on member `rac1`
Attempting to stop `ora.eons` on member `rac2`
CRS-5016: Process "/u01/app/11.2.0/grid/opmn/bin/onsctli" spawned by agent "/u01/app/11.2.0/grid/bin/oraagent.bin" for action "stop" failed: details at "(:CLSN00010:)" in "/u01/app/11.2.0/grid/log/rac1/agent/crsd/oraagent_oracle/oraagent_oracle.log"
Stop of `ora.LISTENER.lsnr` on member `rac2` succeeded.
Attempting to stop `ora.ons` on member `rac2`
Attempting to stop `ora.rac2.vip` on member `rac2`
Stop of `ora.ons` on member `rac2` succeeded.
CRS-2789: Cannot stop resource 'ora.ons' as it is not running on server 'rac2'
Stop of `ora.rac2.vip` on member `rac2` succeeded.
Attempting to stop `ora.net1.network` on member `rac2`
Stop of `ora.net1.network` on member `rac2` succeeded.
Stop of `ora.LISTENER.lsnr` on member `rac1` succeeded.
Attempting to stop `ora.ons` on member `rac1`
Attempting to stop `ora.rac1.vip` on member `rac1`
Attempting to stop `ora.LISTENER_SCAN1.lsnr` on member `rac1`
Stop of `ora.rac1.vip` on member `rac1` succeeded.
Stop of `ora.ons` on member `rac1` succeeded.
ORA-12547: TNS:lost contact
ORA-01012: not logged on
Stop of `ora.LISTENER_SCAN1.lsnr` on member `rac1` succeeded.
Attempting to stop `ora.scan1.vip` on member `rac1`
Stop of `ora.scan1.vip` on member `rac1` succeeded.
Attempting to stop `ora.net1.network` on member `rac1`
Stop of `ora.net1.network` on member `rac1` succeeded.
Stop of `ora.eons` on member `rac2` succeeded.
Stop of `ora.eons` on member `rac1` succeeded.
Stop of `ora.racdb.db` on member `rac2` succeeded.
CRS-0216: Could not stop resource 'ora.asm rac1 1'.
CRS-0216: Could not stop resource 'ora.asm rac2 1'.
CRS-0216: Could not stop resource 'ora.gsd'.
CRS-0216: Could not stop resource 'ora.gsd rac1 1'.
CRS-0216: Could not stop resource 'ora.gsd rac2 1'.
CRS-0216: Could not stop resource 'ora.oc4j'.
CRS-0216: Could not stop resource 'ora.ons rac1 1'.
CRS-0216: Could not stop resource 'ora.ons rac2 1'.
--- 노드 연결상태 확인
[oracle@rac1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type OFFLINE ONLINE rac2
ora.FRA.dg ora....up.type OFFLINE ONLINE rac2
ora....ER.lsnr ora....er.type OFFLINE OFFLINE
ora....N1.lsnr ora....er.type OFFLINE OFFLINE
ora.asm ora.asm.type OFFLINE ONLINE rac2
ora.eons ora.eons.type OFFLINE OFFLINE
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type OFFLINE OFFLINE
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type OFFLINE OFFLINE
ora....SM1.asm application OFFLINE ONLINE rac1
ora....C1.lsnr application OFFLINE OFFLINE
ora.rac1.gsd application OFFLINE OFFLINE
ora.rac1.ons application OFFLINE OFFLINE
ora.rac1.vip ora....t1.type OFFLINE OFFLINE
ora....SM2.asm application OFFLINE ONLINE rac2
ora....C2.lsnr application OFFLINE OFFLINE
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application OFFLINE OFFLINE
ora.rac2.vip ora....t1.type OFFLINE OFFLINE
ora.racdb.db ora....se.type OFFLINE ONLINE rac2
ora.scan1.vip ora....ip.type OFFLINE OFFLINE> 깔끔하게 종료가 안되었다. 다시 시도해도 큰 차이 없어 그대로 진행
--- OS 종료
[oracle@rac1 ~]$ su -
Password:
[root@rac1 ~]# init 0
[oracle@rac2 ~]$ su -
Password:
[root@rac2 ~]# init 0
--- VM > 도구 > 미디어 > 만들기
가상 하드 디스크 파일 형식 : VDI
물리적 하드 드라이브에 저장
- 미리 전체 크기 할당 체크
파일 위치 및 크기
- 기존에 생성한 디스크와 같은 위치에
- 파일명 수정 : asm_disk7.vdi
- 2GB
완료
한번 더 반복 : asm_disk8.vdi 생성
추가한 디스크 속성
- 종류 : 일반 -> 공유가능으로 수정 및 적용 (파일 2개 각각)
rac1 > 속성 > 저장소 >
- 컨트롤러 디스크 추가 > 파일 2개를 하나씩 선택해서 연결
한번 더 반복 : rac2에도 디스크 2개 연결
--- rac1 시작
--- putty > rac1 > root
--- 물리적 디스크 목록
[root@rac1 ~]# fdisk -l
Disk /dev/sda: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 535 4192965 82 Linux swap / Solaris
/dev/sda3 536 6527 48130740 83 Linux
Disk /dev/sdb: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 652 5237158+ 83 Linux
Disk /dev/sdc: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdc1 1 652 5237158+ 83 Linux
Disk /dev/sdd: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdd1 1 652 5237158+ 83 Linux
Disk /dev/sde: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sde1 1 652 5237158+ 83 Linux
Disk /dev/sdf: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdf1 1 652 5237158+ 83 Linux
Disk /dev/sdg: 5368 MB, 5368709120 bytes
255 heads, 63 sectors/track, 652 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdg1 1 652 5237158+ 83 Linux
Disk /dev/sdh: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdh doesn't contain a valid partition table
Disk /dev/sdi: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdi doesn't contain a valid partition table
--- 디스크 포맷
[root@rac1 ~]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won`t be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n -- 입력
Command action
e extended
p primary partition (1-4)
p -- 입력
Partition number (1-4): 1
First cylinder (1-261, default 1): -- 엔터
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-261, default 261): -- 엔터
Using default value 261
Command (m for help): w -- 입력
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table.
The new table will be used at the next reboot.
Syncing disks.
--- 디스크 포맷 + 1 반복
[root@rac1 ~]# fdisk /dev/sdi
> 상위 동일 작업
--- 디스크 목록
[root@rac1 ~]# fdisk -l |perl -ne 'print if/^\/dev\/sd[b-g]\d/'
/dev/sdb1 1 652 5237158+ 83 Linux
/dev/sdc1 1 652 5237158+ 83 Linux
/dev/sdd1 1 652 5237158+ 83 Linux
/dev/sde1 1 652 5237158+ 83 Linux
/dev/sdf1 1 652 5237158+ 83 Linux
/dev/sdg1 1 652 5237158+ 83 Linux--- 디스크 연결
[root@rac1 ~]# oracleasm listdisks
ASMDISK01
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
--- 디스크 연결
[root@rac1 ~]# oracleasm createdisk ASMDISK07 /dev/sdh1;
Writing disk header: done
Instantiating disk: done
[root@rac1 ~]# oracleasm createdisk ASMDISK08 /dev/sdi1;
Writing disk header: done
Instantiating disk: done
[root@rac1 ~]# oracleasm listdisks
ASMDISK01
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08
--- rac2 시작
--- putty > rac2 > root
[root@rac2 ~]$ oracleasm listdisks
ASMDISK01
ASMDISK02
ASMDISK03
ASMDISK04
ASMDISK05
ASMDISK06
ASMDISK07
ASMDISK08> 공유스토리지로 생성했기 때문에 rac2에서도 보인다. 1개 노드에서만 포맷하면 된다.
--- 전체 노드 시작
[root@rac2 ~]$ crs_start -all
CRS-5702: Resource 'ora.DATA.dg' is already running on 'rac1'
CRS-5702: Resource 'ora.asm' is already running on 'rac1'
CRS-5702: Resource 'ora.eons' is already running on 'rac1'
CRS-2501: Resource 'ora.gsd' is disabled
CRS-5702: Resource 'ora.net1.network' is already running on 'rac1'
CRS-2501: Resource 'ora.oc4j' is disabled
CRS-5702: Resource 'ora.ons' is already running on 'rac1'
CRS-5702: Resource 'ora.asm' is already running on 'rac1'
CRS-2501: Resource 'ora.gsd' is disabled
CRS-5702: Resource 'ora.ons' is already running on 'rac1'
CRS-5702: Resource 'ora.asm' is already running on 'rac2'
CRS-2501: Resource 'ora.gsd' is disabled
CRS-5702: Resource 'ora.ons' is already running on 'rac2'
Attempting to start `ora.rac1.vip` on member `rac1`
Attempting to start `ora.scan1.vip` on member `rac2`
Start of `ora.scan1.vip` on member `rac2` succeeded.
Attempting to start `ora.LISTENER_SCAN1.lsnr` on member `rac2`
Start of `ora.rac1.vip` on member `rac1` succeeded.
Attempting to start `ora.LISTENER.lsnr` on member `rac1`
Start of `ora.LISTENER.lsnr` on member `rac1` succeeded.
Start of `ora.LISTENER_SCAN1.lsnr` on member `rac2` succeeded.
Attempting to start `ora.FRA.dg` on member `rac2`
Attempting to start `ora.rac2.vip` on member `rac2`
Attempting to start `ora.racdb.db` on member `rac1`
Start of `ora.FRA.dg` on member `rac2` succeeded.
Start of `ora.rac2.vip` on member `rac2` succeeded.
Attempting to start `ora.LISTENER.lsnr` on member `rac2`
Start of `ora.LISTENER.lsnr` on member `rac2` succeeded.
Attempting to start `ora.racdb.db` on member `rac2`
Start of `ora.racdb.db` on member `rac2` succeeded.
Start of `ora.racdb.db` on member `rac1` succeeded.
CRS-0223: Resource 'ora.DATA.dg' has placement error.
CRS-0223: Resource 'ora.asm' has placement error.
CRS-0223: Resource 'ora.eons' has placement error.
CRS-2660: Resource 'ora.gsd' or all of its instances are disabled
CRS-0223: Resource 'ora.net1.network' has placement error.
CRS-2660: Resource 'ora.oc4j' or all of its instances are disabled
CRS-0223: Resource 'ora.ons' has placement error.
CRS-0223: Resource 'ora.rac1.ASM1.asm' has placement error.
CRS-2660: Resource 'ora.rac1.gsd' or all of its instances are disabled
CRS-0223: Resource 'ora.rac1.ons' has placement error.
CRS-0223: Resource 'ora.rac2.ASM2.asm' has placement error.
CRS-2660: Resource 'ora.rac2.gsd' or all of its instances are disabled
CRS-0223: Resource 'ora.rac2.ons' has placement error.
[root@rac2 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac1
ora.FRA.dg ora....up.type ONLINE ONLINE rac1
ora....ER.lsnr ora....er.type ONLINE ONLINE rac1
ora....N1.lsnr ora....er.type ONLINE ONLINE rac2
ora.asm ora.asm.type ONLINE ONLINE rac1
ora.eons ora.eons.type ONLINE ONLINE rac1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE rac1
ora....SM1.asm application ONLINE ONLINE rac1
ora....C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application OFFLINE OFFLINE
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip ora....t1.type ONLINE ONLINE rac1
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.racdb.db ora....se.type ONLINE ONLINE rac1
ora.scan1.vip ora....ip.type ONLINE ONLINE rac2
--- rac1 > oracle
[root@rac1 ~]# su - oracle
--- SID : +ASM1
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
--- ASM에 접속 후에는 sqlplus : sysasm 로 접속하자
[oracle@rac1 ~]$ sqlplus / as sysasm
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Real Application Clusters and Automatic Storage Management options
SYS@+ASM1>
# 사용 가능한 디스크 확인
SYS@+ASM1> select group_number,mount_status,path,total_mb from v$asm_disk where mount_status = 'CLOSED';
GROUP_NUMBER MOUNT_STSTUS PATH TOTAL_MB
------------ ------------ ------------------------------- ----------
0 CLOSED /dev/oracleasm/disks/ASMDISK07 0
0 CLOSED /dev/oracleasm/disks/ASMDISK08 0
# 디스크 그룹에 디스크 추가
- 추가할 때는 디스크 PATH 정보를 입력
- rebalance power 10 : 빠르게 추가하자 (1~10 단계)
SYS@+ASM1> alter diskgroup FRA add disk '/dev/oracleasm/disks/ASMDISK07' rebalance power 10;
Diskgroup altered.
SYS@+ASM1> select b.name as group_name, a.name as disk_name, a.header_status, a.state,
a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
GROUP_NAME DISK_NAME HEADER_STATUS STATE TOTAL_MB FREE_MB
---------- ------------------------------ ------------- -------------------- -------- ----------
FRA FRA_0001 MEMBER NORMAL 5114 4916
FRA FRA_0000 MEMBER NORMAL 5114 4917
DATA DATA_0003 MEMBER NORMAL 5114 4063
DATA DATA_0002 MEMBER NORMAL 5114 4023
DATA DATA_0001 MEMBER NORMAL 5114 4032
DATA DATA_0000 MEMBER NORMAL 5114 4029
FRA FRA_0002 MEMBER NORMAL 2047 1968
# 디스크 그룹에서 디스크 삭제
- 디스크를 삭제할 때는 DISK_NAME으로 입력
SYS@+ASM1> alter diskgroup FRA drop disk FRA_0002;
Diskgroup altered.
SYS@+ASM1> select b.name as group_name, a.name as disk_name, a.header_status, a.state,
a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
GROUP_NAME DISK_NAME HEADER_STATUS STATE TOTAL_MB FREE_MB
---------- ------------------------------ ------------- -------------------- -------- ----------
FRA FRA_0001 MEMBER NORMAL 5114 4877
FRA FRA_0000 MEMBER NORMAL 5114 4879
DATA DATA_0003 MEMBER NORMAL 5114 4063
DATA DATA_0002 MEMBER NORMAL 5114 4023
DATA DATA_0001 MEMBER NORMAL 5114 4032
DATA DATA_0000 MEMBER NORMAL 5114 4029> STATE : DROPPING - 리밸런싱 + 디스크 삭제 진행중이다.
# 새로운 디스크 그룹 생성
--- 사용가능한 디스크 확인
SYS@+ASM1> select group_number,mount_status,path,total_mb from v$asm_disk where mount_status = 'CLOSED';
GROUP_NUMBER MOUNT_STSTUS PATH TOTAL_MB
------------ ------------ ------------------------------- ----------
0 CLOSED /dev/oracleasm/disks/ASMDISK07 0
0 CLOSED /dev/oracleasm/disks/ASMDISK08 0
--- 디스크 그룹 생성
SYS@+ASM1> create diskgroup asm_dg external redundancy
disk '/dev/oracleasm/disks/ASMDISK07','/dev/oracleasm/disks/ASMDISK08';
Diskgroup created.
--- 디스크 그룹 상태 확인
SYS@+ASM1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
3 ASM_DG EXTERN MOUNTED
SYS@+ASM1> select group_number,mount_status,path,total_mb from v$asm_disk where mount_status = 'CLOSED';
no rows selected
SYS@+ASM1> select b.name as group_name, a.name as disk_name, a.header_status, a.state,
a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
GROUP_NAME DISK_NAME HEADER_STATUS STATE TOTAL_MB FREE_MB
---------- ------------------------------ ------------- -------------------- -------- ----------
FRA FRA_0001 MEMBER NORMAL 5114 4877
FRA FRA_0000 MEMBER NORMAL 5114 4879
DATA DATA_0003 MEMBER NORMAL 5114 4063
DATA DATA_0002 MEMBER NORMAL 5114 4023
DATA DATA_0001 MEMBER NORMAL 5114 4032
DATA DATA_0000 MEMBER NORMAL 5114 4029
ASM_DG ASM_DG_0001 MEMBER NORMAL 2047 2022
ASM_DG ASM_DG_0000 MEMBER NORMAL 2047 2020
# 디스크 그룹 삭제
SYS@+ASM1> drop diskgroup asm_dg;
Diskgroup dropped.
SYS@+ASM1> select group_number,mount_status,path,total_mb from v$asm_disk where mount_status = 'CLOSED';
GROUP_NUMBER MOUNT_STSTUS PATH TOTAL_MB
------------ ------------ ------------------------------- ----------
0 CLOSED /dev/oracleasm/disks/ASMDISK07 0
0 CLOSED /dev/oracleasm/disks/ASMDISK08 0
--- 연습 끝 디스크 다시 추가하자.
SYS@+ASM1> create diskgroup asm_dg external redundancy
disk '/dev/oracleasm/disks/ASMDISK07','/dev/oracleasm/disks/ASMDISK08';
Diskgroup created.
SYS@+ASM1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
3 ASM_DG EXTERN MOUNTED
--- SID racdb1과 +ASM1을 계속 사용하니 창 분리 하는게 좋을 듯
--- putty > rac1 > oracle > SID : racdb1
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [radb1] ? racdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba
SYS@racdb1>
SYS@racdb1> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/racdb/spfileracdb.ora> 원래 초기 파라미터 파일(spfile)은 $ORACLE_HOME/dbs 에 있었다.
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$ ls
hc_DBUA0.dat hc_racdb1.dat init.ora initracdb1.ora orapwracdb1 peshm_DBUA0_0 peshm_racdb_1
[oracle@rac1 dbs]$ cat initracdb1.ora
SPFILE='+DATA/racdb/spfileracdb.ora'> pfile 안에 spfile 링크가 있다.
--- pfile 생성
SYS@racdb1> create pfile='/home/oracle/init.ora' from spfile;
File created.
SYS@racdb1> !
[oracle@rac1 ~]$ ls
init.ora oradiag_oracle
[oracle@rac1 ~]$ cat init.ora
racdb1.__db_cache_size=276824064
racdb2.__db_cache_size=276824064
racdb1.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=4194304
racdb2.__large_pool_size=4194304
racdb1.__pga_aggregate_target=335544320
racdb2.__pga_aggregate_target=335544320
racdb1.__sga_target=503316480
racdb2.__sga_target=503316480
racdb1.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb1.__shared_pool_size=209715200
racdb2.__shared_pool_size=209715200
racdb1.__streams_pool_size=0
racdb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA/racdb/controlfile/current.260.1162835507','+FRA/racdb/controlfile/current.256.1162835507'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='racdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
racdb2.instance_number=2
racdb1.instance_number=1
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=170
racdb2.thread=2
racdb1.thread=1
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2'
--- Data File
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
--------------- ---------- --------------------------------------------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749
--- Control File
SYS@racdb1> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/racdb/controlfile/current.260.1162835507
+FRA/racdb/controlfile/current.256.1162835507
--- Redo Log File
SYS@racdb1> select a.group#, b.thread#, b.sequence#, a.member, b.bytes/1024/1024 mb, b.archived, b.status
from v$logfile a, v$log b where a.group# = b.group# order by 1;
GROUP# THREAD# SEQUENCE# MEMBER MB ARCHIVED STATUS
------ ---------- --------- -------------------------------------------------- ----- --------- --------
1 1 21 +FRA/racdb/onlinelog/group_1.257.1162835517 50 NO CURRENT
1 1 21 +DATA/racdb/onlinelog/group_1.261.1162835513 50 NO CURRENT
2 1 20 +FRA/racdb/onlinelog/group_2.258.1162835519 50 YES INACTIVE
2 1 20 +DATA/racdb/onlinelog/group_2.262.1162835517 50 YES INACTIVE
3 2 21 +DATA/racdb/onlinelog/group_3.266.1162835841 50 YES ACTIVE
3 2 21 +FRA/racdb/onlinelog/group_3.259.1162835845 50 YES ACTIVE
4 2 20 +DATA/racdb/onlinelog/group_4.267.1162835845 50 YES INACTIVE
4 2 20 +FRA/racdb/onlinelog/group_4.260.1162835849 50 YES INACTIVE> THREAD# = instance_no . 앞으로 THREAD#도 함께 보자.
> 시퀸스번호가 중복? > 노드별로 리두 로그가 분리되어 있다.(언두도 마찬가지)
--- 디스크 그룹 상태
SYS@racdb1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL CONNECTED
2 FRA EXTERN CONNECTED
3 ASM_DG EXTERN MOUNTED
# 오류해결 > 디스크가 DISMOUNTED 상태라면..
--- +ASM1
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [racdb1] ? +ASM1
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysasm
--- 디스크 마운트 단계로 상태 변경
[oracle@rac1 ~]$ alter diskgroup asm_dg mount;
SYS@+ASM1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
3 ASM_DG EXTERN MOUNTED
--- racdb1
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? racdb1
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba
SYS@racdb1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL CONNECTED
2 FRA EXTERN CONNECTED
3 ASM_DG EXTERN MOUNTED
--- Disk Group
SYS@racdb1> select b.name as group_name, a.name as disk_name, a.header_status, a.state, a.total_mb, a.free_mb
from v$asm_disk a, v$asm_diskgroup b
where a.group_number = b.group_number;
GROUP_NAME DISK_NAME HEADER_STATUS STATE TOTAL_MB FREE_MB
---------- ------------------------------ ------------- -------------------- -------- ----------
FRA FRA_0001 MEMBER NORMAL 5114 4871
FRA FRA_0000 MEMBER NORMAL 5114 4875
DATA DATA_0003 MEMBER NORMAL 5114 4063
DATA DATA_0002 MEMBER NORMAL 5114 4023
DATA DATA_0001 MEMBER NORMAL 5114 4032
DATA DATA_0000 MEMBER NORMAL 5114 4029
ASM_DG ASM_DG_0001 MEMBER NORMAL 2047 2021
ASM_DG ASM_DG_0000 MEMBER NORMAL 2047 2021
# 테이블 스페이스 생성
SYS@racdb1> create tablespace asm_tbs datafile '+asm_dg' size 10m;
Tablespace created.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;
TABLESPACE_NAME MB FILE_NAME
--------------- ---------- --------------------------------------------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163082415
# OMF(Oracle Managed Filed)
- 테이블스페이스 생성, 추가 시 데이터파일의 위치를 표현하지 않을 경우 자동으로 db_create_file_dest 파라미터에
설정된 위치에 생성된다.
- 데이터 파일 사이즈는 100M, extent 관리 방식은 LOCAL, segment 공간 관리는 AUTO, 자동 확장 기능이 활성화
SYS@racdb1> alter tablespace asm_tbs add datafile;
Tablespace altered.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163082415 NO
ASM_TBS 100 +DATA/racdb/datafile/asm_tbs.269.1163083805 YES
SYS@racdb1> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_create_file_dest string +DATA
> 테이블스페이스는 업무별 구성, 디스크 그룹 역시 업무별로 구성하자.
SYS@racdb1> select tablespace_name, status, contents, extent_management, segment_space_management
from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
--------------- ------------- --------- ----------------- ------------------------
SYSTEM ONLINE PERMANENT LOCAL MANUAL
SYSAUX ONLINE PERMANENT LOCAL AUTO
UNDOTBS1 ONLINE UNDO LOCAL MANUAL
TEMP ONLINE TEMPORARY LOCAL MANUAL
USERS ONLINE PERMANENT LOCAL AUTO
UNDOTBS2 ONLINE UNDO LOCAL MANUAL
EXAMPLE ONLINE PERMANENT LOCAL AUTO
ASM_TBS ONLINE PERMANENT LOCAL AUTO
# 데이터 파일 삭제
SYS@racdb1> alter tablespace asm_tbs drop datafile '+DATA/racdb/datafile/asm_tbs.269.1163083805';
Tablespace altered.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163082415 NO
# 테이블스페이스 기본 위치 수정
SYS@racdb1> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_create_file_dest string +DATA
# 테이블스페이스 기본 위치 수정
SYS@racdb1> alter system set db_create_file_dest = '+ASM_DG';
System altered.
SYS@racdb1> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_create_file_dest string +ASM_DG
< 연습 > OMP방식 테이블스페이스
--- 테이블스페이스 생성
SYS@racdb1> create tablespace insa_tbs;
Tablespace created.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163082415 NO
INSA_TBS 100 +ASM_DG/racdb/datafile/insa_tbs.257.1163084461 YES
--- OMP_테이블스페이스에 데이터파일 추가
SYS@racdb1> alter tablespace insa_tbs add datafile;
Tablespace altered.
--- 테이블스페이스 삭제
SYS@racdb1> drop tablespace insa_tbs;
Tablespace dropped.
> 다른거 덧붙이지 않아도 오라클이 알아서 다 지워준다.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163082415 NO
--- 자동확장기능 활성화
SYS@racdb1> alter database datafile '+ASM_DG/racdb/datafile/asm_tbs.256.1163082415' autoextend on;
Database altered.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163082415 YES
> autoextend 기능을 기본값처럼 활성화 하자.
--- 테이블 생성
SYS@racdb1> create table hr.emp tablespace asm_tbs as select * from hr.employees;
Table created.
SYS@racdb1> select count(*) from hr.emp;
COUNT(*)
----------
107
SYS@racdb1> select tablespace_name, extents, bytes, blocks from dba_segments
where segment_name = 'EMP' and owner = 'HR';
TABLESPACE_NAME EXTENTS BYTES BLOCKS
--------------- ---------- ---------- ----------
ASM_TBS 1 65536 8
# ASM_TBS 테이블스페이스를 +ASM_DG에서 +DATA 그룹으로 이동 > RMAN 사용
--- 테이블 스페이스 오프라인 만들기
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
--- tablespace level - offline
SYS@racdb1> alter tablespace asm_tbs offline normal;
Tablespace altered.
> Normal : checkpoint 발생
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS OFFLINE
--- RMAN 접속 > COPY
SYS@racdb1> !
[oracle@rac1 ~]$ rman target /
connected to target database: RACDB (DBID=1160452274)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/racdb/datafile/system.256.1162835387
2 490 SYSAUX *** +DATA/racdb/datafile/sysaux.257.1162835389
3 95 UNDOTBS1 *** +DATA/racdb/datafile/undotbs1.258.1162835389
4 5 USERS *** +DATA/racdb/datafile/users.259.1162835389
5 100 EXAMPLE *** +DATA/racdb/datafile/example.264.1162835541
6 25 UNDOTBS2 *** +DATA/racdb/datafile/undotbs2.265.1162835749
7 0 ASM_TBS *** +ASM_DG/racdb/datafile/asm_tbs.256.1163082415
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 31 TEMP 32767 +DATA/racdb/tempfile/temp.263.1162835533
--- RMAN > COPY : 신규 위치에 생성(백업) = Image Copy
RMAN> copy datafile '+ASM_DG/racdb/datafile/asm_tbs.256.1163082415' to '+DATA/racdb/datafile/asm_tbs01.dbf';
Starting backup at 08-MAR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+ASM_DG/racdb/datafile/asm_tbs.256.1163082415
output file name=+DATA/racdb/datafile/asm_tbs01.dbf tag=TAG20240308T151732 RECID=2 STAMP=1163085454
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 08-MAR-24
RMAN> exit
Recovery Manager complete.
[oracle@rac1 ~]$ exit
SYS@racdb1>
--- Rename : 위치 변경
SYS@racdb1> alter tablespace asm_tbs rename datafile '+ASM_DG/racdb/datafile/asm_tbs.256.1163082415' to '+DATA/racdb/datafile/asm_tbs01.dbf';
Tablespace altered.
--- tablespace level : Online
SYS@racdb1> alter tablespace asm_tbs online;
Tablespace altered.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +DATA/racdb/datafile/asm_tbs01.dbf YES
# 원상복구 : ASM_TBS 테이블스페이스를 +DATA에서 +ASM_DG 그룹으로 이동
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
--- tablespace level - Offline Normal
SYS@racdb1> alter tablespace asm_tbs offline normal;
Tablespace altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS OFFLINE
--- RMAN 접속
SYS@racdb1> !
[oracle@rac1 ~]$ rman target /
connected to target database: RACDB (DBID=1160452274)
RMAN> report schema;
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/racdb/datafile/system.256.1162835387
2 490 SYSAUX *** +DATA/racdb/datafile/sysaux.257.1162835389
3 95 UNDOTBS1 *** +DATA/racdb/datafile/undotbs1.258.1162835389
4 5 USERS *** +DATA/racdb/datafile/users.259.1162835389
5 100 EXAMPLE *** +DATA/racdb/datafile/example.264.1162835541
6 25 UNDOTBS2 *** +DATA/racdb/datafile/undotbs2.265.1162835749
7 0 ASM_TBS *** +DATA/racdb/datafile/asm_tbs01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 31 TEMP 32767 +DATA/racdb/tempfile/temp.263.1162835533
--- RMAN > COPY + 그룹으로 지정
RMAN> copy datafile '+DATA/racdb/datafile/asm_tbs01.dbf' to '+ASM_DG';
Starting backup at 08-MAR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/racdb/datafile/asm_tbs01.dbf
output file name=+ASM_DG/racdb/datafile/asm_tbs.256.1163086077 tag=TAG20240308T152757 RECID=3 STAMP=1163086077
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-MAR-24
> output file name = 자동으로 생성된 위치 확인
RMAN> exit
Recovery Manager complete.
[oracle@rac1 ~]$ exit
SYS@racdb1>
--- Rename : 위치 변경
SYS@racdb1> alter tablespace asm_tbs rename datafile '+DATA/racdb/datafile/asm_tbs01.dbf' to '+ASM_DG/racdb/datafile/asm_tbs.256.1163086077';
Tablespace altered.
--- tablespace level : Online
SYS@racdb1> alter tablespace asm_tbs online;
Tablespace altered.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163086077 YES
--- racdb1
SYS@racdb1> select group_number, name, type, state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL CONNECTED
2 FRA EXTERN CONNECTED
3 ASM_DG EXTERN CONNECTED
--- +ASM1
SYS@+ASM1> select group_number, name, type, state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
3 ASM_DG EXTERN MOUNTED
# 디스크 그룹을 Dismount
# Dismount 그룹에 속한 테이블스페이스 Offline 상태에서만 가능하다.
--- +ASM1
SYS@+ASM1> alter diskgroup asm_dg dismount;
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "ASM_DG" precludes its dismount> 현재 사용중인 디스크 그룹이라 해제할 수 없다.
SYS@racdb1> select tablespace_name, bytes/1024/1024 mb, file_name, autoextensible from dba_data_files;
TABLESPACE_NAME MB FILE_NAME AUTOEXTENSIBLE
--------------- ---------- -------------------------------------------------- ---------------
USERS 5 +DATA/racdb/datafile/users.259.1162835389 YES
UNDOTBS1 95 +DATA/racdb/datafile/undotbs1.258.1162835389 YES
SYSAUX 490 +DATA/racdb/datafile/sysaux.257.1162835389 YES
SYSTEM 680 +DATA/racdb/datafile/system.256.1162835387 YES
EXAMPLE 100 +DATA/racdb/datafile/example.264.1162835541 YES
UNDOTBS2 25 +DATA/racdb/datafile/undotbs2.265.1162835749 YES
ASM_TBS 10 +ASM_DG/racdb/datafile/asm_tbs.256.1163086077 YES
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
--- racdb1 > tablespace level - offline
SYS@racdb1> alter tablespace asm_tbs offline normal;
Tablespace altered.
--- +ASM1 > 디스크 그룹 연결해제
SYS@++ASM1> alter diskgroup asm_dg dismount;
Diskgroup altered.
SYS@+ASM1> select group_number, name, type, state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
0 ASM_DG DISMOUNTED
--- racdb1 > tablespace level online 시도
SYS@racdb1> alter tablespace asm_tbs online;
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+ASM_DG/racdb/datafile/asm_tbs.256.1163086077'> 테이블스페이스가 속한 디스크그룹이 OFF상태이기 때문에 오류가 난다.
--- +ASM1 > 디스크 그룹 마운트
SYS@+ASM1> alter diskgroup asm_dg mount;
Diskgroup altered.
SYS@+ASM1> select group_number, name, type, state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
3 ASM_DG EXTERN MOUNTED
--- racdb1 > tablespace level - online
SYS@racdb1> alter tablespace level - online asm_tbs online;
Tablespace altered.
SYS@racdb1> select count(*) from hr.emp;
COUNT(*)
----------
107
--- racdb1 > test
SYS@racdb1> create table hr.emp_temp tablespace asm_tbs as select * from hr.employees;
Table created.
SYS@racdb1> select count(*) from hr.emp_temp;
COUNT(*)
----------
107
# 강제로 디스크 그룹을 dismount
SYS@+ASM1> alter diskgroup asm_dg dismount;
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "ASM_DG" precludes its dismount
> 테이블스페이스가 online 상태라 당연히 안된다.
--- force 강제 적용
SYS@+ASM1> alter diskgroup asm_dg dismount force;
Diskgroup altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
> 왜인지 계속 온라인 상태로 되어있다.
--- 테이블 생성 시도하면 역시나 오류
SYS@racdb1> create table hr.insa tablespace asm_tbs as select * from hr.employees;
ORA-01114: IO error writing block to file 7 (block # 147)
ORA-15078: ASM diskgroup was forcibly dismounted
SYS@+ASM1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ ------------------------------ ------ -----------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
0 ASM_DG DISMOUNTED
--- 디스크 그룹 마운트 연결
SYS@+ASM1> alter diskgroup asm_dg mount;
Diskgroup altered.
SYS@+ASM1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ ------------------------------ ------ -----------
1 DATA NORMAL MOUNTED
2 FRA EXTERN MOUNTED
3 ASM_DG EXTERN MOUNTED
SYS@racdb1> create table hr.insa tablespace asm_tbs as select * from hr.employees;
ORA-03135: connection lost contact
Process ID: 11029
Session ID: 29 Serial number: 258
SYS@racdb1> select count(*) from hr.emp;
ERROR:
ORA-03114: not connected to ORACLE
> db 접속이 끊겼다..
SYS@racdb1> startup
ORACLE instance started.
Database mounted.
Database opened.
-- 다른 사람과 오류가 다르다. 처리 흐름은 동일하게 진행했다.
-- ORA-00376: file 7 cannot be read at this time
-- ORA-01110: data file 7: '+ASM_DG/...
--- tablespace level - offline immediate
SYS@racdb1> alter tablespace asm_tbs offline immediate;
Tablespace altered.
SYS@racdb1> select a.file#, b.name, a.name, a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b where a.ts# = b.ts#;
FILE# NAME NAME STATUS CHECKPOINT_CHANGE#
----- --------- --------------------------------------------- ------------- ------------------
1 SYSTEM +DATA/racdb/datafile/system.256.1162835387 SYSTEM 1202720
2 SYSAUX +DATA/racdb/datafile/sysaux.257.1162835389 ONLINE 1202720
3 UNDOTBS1 +DATA/racdb/datafile/undotbs1.258.1162835389 ONLINE 1202720
4 USERS +DATA/racdb/datafile/users.259.1162835389 ONLINE 1202720
5 EXAMPLE +DATA/racdb/datafile/example.264.1162835541 ONLINE 1202720
6 UNDOTBS2 +DATA/racdb/datafile/undotbs2.265.1162835749 ONLINE 1202720
7 ASM_TBS +ASM_DG/racdb/datafile/asm_tbs.256.1163086077 RECOVER 1161652
--- recover datafile
SYS@racdb1> recover datafile '+ASM_DG/racdb/datafile/asm_tbs.256.1163086077';
ORA-00279: change 1161652 generated at 03/08/2024 16:07:15 needed for thread 1
ORA-00289: suggestion : +FRA/racdb/archivelog/2024_03_08/thread_1_seq_21.298.1163089973
ORA-00280: change 1161652 for thread 1 is in sequence #21
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto -- 입력
Log applied.
Media recovery complete.
--- datafile level - open
SYS@racdb1> alter database datafile '+ASM_DG/racdb/datafile/asm_tbs.256.1163086077' online;
Database altered.
SYS@racdb1> select a.file#, b.name, a.name, a.status, a.checkpoint_change#
from v$datafile a, v$tablespace b where a.ts# = b.ts#;
FILE# NAME NAME STATUS CHECKPOINT_CHANGE#
----- --------- --------------------------------------------- ------------- ------------------
1 SYSTEM +DATA/racdb/datafile/system.256.1162835387 SYSTEM 1202720
2 SYSAUX +DATA/racdb/datafile/sysaux.257.1162835389 ONLINE 1202720
3 UNDOTBS1 +DATA/racdb/datafile/undotbs1.258.1162835389 ONLINE 1202720
4 USERS +DATA/racdb/datafile/users.259.1162835389 ONLINE 1202720
5 EXAMPLE +DATA/racdb/datafile/example.264.1162835541 ONLINE 1202720
6 UNDOTBS2 +DATA/racdb/datafile/undotbs2.265.1162835749 ONLINE 1202720
7 ASM_TBS +ASM_DG/racdb/datafile/asm_tbs.256.1163086077 ONLINE 1203010
SYS@racdb1> select group_number,name,type,state from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE STATE
------------ --------------------------------------------- ------- --------------------
1 DATA NORMAL CONNECTED
2 FRA EXTERN CONNECTED
3 ASM_DG EXTERN CONNECTED
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS OFFLINE
> 처음에 테이블스페이스 레벨로 오프라인 만들었기 때문에 다시 테이블스페이스 레벨에서 온라인으로 만들자.
--- tablespace level - online
SYS@racdb1> alter tablespace asm_tbs online;
Tablespace altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
SYS@racdb1> select count(*) from hr.emp;
COUNT(*)
----------
107
SYS@racdb1> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 3852M
SYS@racdb1> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Next log sequence to archive 23
Current log sequence 23
> rac는 항상 아카이브 모드로 구성한다.
# RAM Backup
--- RMAN
SYS@racdb1> !
[oracle@rac1 ~]$ rman target /
connected to target database: RACDB (DBID=1160452274)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name RACDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 680 SYSTEM *** +DATA/racdb/datafile/system.256.1162835387
2 490 SYSAUX *** +DATA/racdb/datafile/sysaux.257.1162835389
3 95 UNDOTBS1 *** +DATA/racdb/datafile/undotbs1.258.1162835389
4 5 USERS *** +DATA/racdb/datafile/users.259.1162835389
5 100 EXAMPLE *** +DATA/racdb/datafile/example.264.1162835541
6 25 UNDOTBS2 *** +DATA/racdb/datafile/undotbs2.265.1162835749
7 10 ASM_TBS *** +ASM_DG/racdb/datafile/asm_tbs.256.1163086077
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 31 TEMP 32767 +DATA/racdb/tempfile/temp.263.1162835533
RMAN> show all;
RMAN configuration parameters for database with db_unique_name RACDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb1.f'; # default
--- 컨트롤파일 자동 백업 활성화
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
--- 백업이 필요한 대상 확인
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 +DATA/racdb/datafile/system.256.1162835387
2 0 +DATA/racdb/datafile/sysaux.257.1162835389
3 0 +DATA/racdb/datafile/undotbs1.258.1162835389
4 0 +DATA/racdb/datafile/users.259.1162835389
5 0 +DATA/racdb/datafile/example.264.1162835541
6 0 +DATA/racdb/datafile/undotbs2.265.1162835749
--- 전부 압축 백업
RMAN> backup as compressed backupset database;
Starting backup at 08-MAR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/racdb/datafile/system.256.1162835387
input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.257.1162835389
input datafile file number=00005 name=+DATA/racdb/datafile/example.264.1162835541
input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.258.1162835389
input datafile file number=00006 name=+DATA/racdb/datafile/undotbs2.265.1162835749
input datafile file number=00007 name=+ASM_DG/racdb/datafile/asm_tbs.256.1163086077
input datafile file number=00004 name=+DATA/racdb/datafile/users.259.1162835389
channel ORA_DISK_1: starting piece 1 at 08-MAR-24
channel ORA_DISK_1: finished piece 1 at 08-MAR-24
piece handle=+FRA/racdb/backupset/2024_03_08/nnndf0_tag20240308t171116_0.300.1163092279 tag=TAG20240308T171116 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 08-MAR-24
Starting Control File and SPFILE Autobackup at 08-MAR-24
piece handle=+FRA/racdb/autobackup/2024_03_08/s_1163092332.301.1163092335 comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAR-24
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 273.95M DISK 00:00:52 08-MAR-24
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20240308T171116
Piece Name: +FRA/racdb/backupset/2024_03_08/nnndf0_tag20240308t171116_0.300.1163092279
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1204340 08-MAR-24 +DATA/racdb/datafile/system.256.1162835387
2 Full 1204340 08-MAR-24 +DATA/racdb/datafile/sysaux.257.1162835389
3 Full 1204340 08-MAR-24 +DATA/racdb/datafile/undotbs1.258.1162835389
4 Full 1204340 08-MAR-24 +DATA/racdb/datafile/users.259.1162835389
5 Full 1204340 08-MAR-24 +DATA/racdb/datafile/example.264.1162835541
6 Full 1204340 08-MAR-24 +DATA/racdb/datafile/undotbs2.265.1162835749
7 Full 1204340 08-MAR-24 +ASM_DG/racdb/datafile/asm_tbs.256.1163086077
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 17.77M DISK 00:00:02 08-MAR-24
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20240308T171212
Piece Name: +FRA/racdb/autobackup/2024_03_08/s_1163092332.301.1163092335
SPFILE Included: Modification time: 08-MAR-24
SPFILE db_unique_name: RACDB
Control File Included: Ckp SCN: 1204531 Ckp time: 08-MAR-24
--- +ASM
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle
# ASMCMD : ASM 관리 툴
[oracle@rac1 ~]$ asmcmd
ASMCMD>
# 현재 생성된 디스크 그룹
ASMCMD> ls
ASM_DG/
DATA/
FRA/
-- 명령어는 리눅스와 차이가 없다.
ASMCMD> cd ASM_DG
ASMCMD> ls
RACDB/
ASMCMD> cd ASM_DG
ASMCMD> ls
RACDB/
ASMCMD> cd RACDB
ASMCMD> ls
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> ls
ASM_TBS.256.1163086077
ASMCMD> pwd
+ASM_DG/RACDB/DATAFILE
ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> ls
ASMCMD> pwd
+
ASMCMD> ls
ASM_DG/
DATA/
FRA/
ASMCMD>
--- 파일 찾기
ASMCMD> find --type datafile asm_dg asm*
+asm_dg/RACDB/DATAFILE/ASM_TBS.256.1163086077
ASMCMD> cd ASM_DG/RACDB/DATAFILE
ASMCMD> ls
ASM_TBS.256.1163086077
ASMCMD> ls -al
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 08 17:00:00 Y none => ASM_TBS.256.1163086077
--- 장애 유발 : 데이터 파일 삭제 시도
ASMCMD> rm -f ASM_TBS.256.1163086077
ORA-15032: not all alterations performed
ORA-15028: ASM file '+ASM_DG/RACDB/DATAFILE/ASM_TBS.256.1163086077' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
> 운영중이니 삭제가 안되는 건 당연하다.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
--- tablespace offline normal
SYS@racdb1> alter tablespace asm_tbs offline normal;
Tablespace altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS OFFLINE
SYS@racdb1> select tablespace_name, file_name, status from dba_data_files;
TABLESPACE_NAME FILE_NAME STATUS
--------------- -------------------------------------------------- -------------
USERS +DATA/racdb/datafile/users.259.1162835389 AVAILABLE
UNDOTBS1 +DATA/racdb/datafile/undotbs1.258.1162835389 AVAILABLE
SYSAUX +DATA/racdb/datafile/sysaux.257.1162835389 AVAILABLE
SYSTEM +DATA/racdb/datafile/system.256.1162835387 AVAILABLE
EXAMPLE +DATA/racdb/datafile/example.264.1162835541 AVAILABLE
UNDOTBS2 +DATA/racdb/datafile/undotbs2.265.1162835749 AVAILABLE
ASM_TBS +ASM_DG/racdb/datafile/asm_tbs.256.1163086077 AVAILABLE
ASMCMD> ls -al
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 08 17:00:00 Y none => ASM_TBS.256.1163086077
--- 다시 장애 유발 : 데이터파일 삭제
ASMCMD> rm -f ASM_TBS.256.1163086077
--- 재확인
ASMCMD> rm -f ASM_TBS.256.1163086077
ASMCMD-08002: entry 'RACDB' does not exist in directory '+ASM_DG/'
--- tablespace level - online 시도
SYS@racdb1> alter tablespace asm_tbs online;
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+ASM_DG/racdb/datafile/asm_tbs.256.1163086077'
--- 복구해보자 > RMAN
SYS@racdb1> !
[oracle@rac1 ~]$ rman target /
connected to target database: RACDB (DBID=1160452274)
RMAN> list failure;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 03/08/2024 17:27:25
RMAN-05533: LIST FAILURE is not supported on RAC database
> rac에서는 failure 목록을 지원하지 않는다..
--- restore
RMAN> restore datafile 7;
Starting restore at 08-MAR-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=155 instance=racdb1 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to +ASM_DG/racdb/datafile/asm_tbs.256.1163086077
channel ORA_DISK_1: reading from backup piece +FRA/racdb/backupset/2024_03_08/nnndf0_tag20240308t171116_0.300.1163092279
channel ORA_DISK_1: piece handle=+FRA/racdb/backupset/2024_03_08/nnndf0_tag20240308t171116_0.300.1163092279 tag=TAG20240308T171116
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-MAR-24
--- recover
RMAN> recover datafile 7;
Starting recover at 08-MAR-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 08-MAR-24
--- 데이터파일 복구 확인
ASMCMD> ls
ASM_TBS.256.1163093325
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS OFFLINE
8 rows selected.
--- tablespace level - online
SYS@racdb1> alter tablespace asm_tbs online;
Tablespace altered.
SYS@racdb1> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------------- -------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
ASM_TBS ONLINE
'Oracle 11g > RAC' 카테고리의 다른 글
| 99 RAC GRD, Cache Fusion (1) | 2024.03.07 |
|---|---|
| 98 RAC RAC STAT, srvctl, OCR, Vote Disk, LOAD BALANCING, CTF, TAF (3) | 2024.03.06 |
| 97-3 Oracle S/W, ASM Disk Group, RAC용 DB 설치 (0) | 2024.03.05 |
| 96-2 그리드 삭제 후 설치 시 (0) | 2024.03.04 |
| 96-1 Grid 11g 설치 (3) | 2024.03.04 |