1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. Chap.12 RAC Backup and Recovery

Chap.12 RAC Backup and Recovery (by openlsw) [2015.12.25]

  • RAC 공유 스토리지 구현
    • ASM(Automatic Storage Management)
    • Raw Device
    • NFS
    • GPFS

12.1 OCR 개념 및 관리

OCR Disk

Oracle Cluster Repository의 약자
Cluster 전체 정보 저장소 (모든 노드 와 자원에 대한 정보)
자동 백업 (4시간, 매일, 매주)

OCR file 복구


-- OCR 정보 조회
[oracle@ol6-112-rac1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       2880
	 Available space (kbytes) :     259240
	 ID                       :  803780385
	 Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

	 Cluster registry integrity check succeeded

	 Logical corruption check bypassed due to non-privileged user

-- OCR 백업 경로
[oracle@ol6-112-rac1 ~]$ ocrconfig -showbackup

ol6-112-rac1     2015/12/25 13:54:45     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup00.ocr

ol6-112-rac1     2015/12/25 13:54:45     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/day.ocr

ol6-112-rac1     2015/12/25 13:54:45     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/week.ocr
PROT-25: Manual backups for the Oracle Cluster Registry are not available

-- 장애 유발 (안됨)
ASMCMD> pwd
+DATA/ol6-112-scan/OCRFILE
ASMCMD> ls -l
Type     Redund  Striped  Time             Sys  Name
OCRFILE  UNPROT  COARSE   DEC 25 18:00:00  Y    REGISTRY.255.863962989
ASMCMD> rm -f REGISTRY.255.863962989
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATA/ol6-112-scan/OCRFILE/REGISTRY.255.863962989' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

-- 장애 유발 (Raw Device)
# dd if-/dev/zero of=/dev/raw/raw1 bs=8k

-- 복구
[root@ol6-112-rac1 oracle]# ocrconfig -restore /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup00.ocr
PROT-19: Cannot proceed while the Cluster Ready Service is running

# crsctl stop crs
[root@ol6-112-rac1 oracle]# ocrconfig -restore /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup00.ocr
PROT-35: The configured Oracle Cluster Registry locations are not accessible

-- -excl : 배타적 모드
# crsctl start crs -excl
# crsctl stop resource ora.crsd -init
[root@ol6-112-rac1 oracle]# ocrconfig -restore /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup00.ocr
[root@ol6-112-rac1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       2880
	 Available space (kbytes) :     259240
	 ID                       :  803780385
	 Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

	 Cluster registry integrity check succeeded

	 Logical corruption check succeeded

# crsctl stop crs -f
# crsctl start crs

-- 수동 export
[root@ol6-112-rac1 oracle]# ocrconfig -export /home/oracle/ocr.dat
[root@ol6-112-rac1 oracle]# ls -al /home/oracle/ocr.dat
-rw-------. 1 root root 103713 2015-12-25 19:14 /home/oracle/ocr.dat

-- 수동 import
[root@ol6-112-rac1 oracle]# ocrconfig -import /home/oracle/ocr.dat
PROT-19: Cannot proceed while the Cluster Ready Service is running

-- nocrs : crsd 프로세스 및 OCR이 시작 되지 않도록 함
[root@ol6-112-rac1 oracle]# crsctl start crs -excl -nocrs
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.mdnsd' on 'ol6-112-rac1'
CRS-2676: Start of 'ora.mdnsd' on 'ol6-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'ol6-112-rac1'
CRS-2676: Start of 'ora.gpnpd' on 'ol6-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'ol6-112-rac1'
CRS-2672: Attempting to start 'ora.gipcd' on 'ol6-112-rac1'
CRS-2676: Start of 'ora.cssdmonitor' on 'ol6-112-rac1' succeeded
CRS-2676: Start of 'ora.gipcd' on 'ol6-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'ol6-112-rac1'
CRS-2672: Attempting to start 'ora.diskmon' on 'ol6-112-rac1'
CRS-2676: Start of 'ora.diskmon' on 'ol6-112-rac1' succeeded
CRS-2676: Start of 'ora.cssd' on 'ol6-112-rac1' succeeded
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'ol6-112-rac1'
CRS-2672: Attempting to start 'ora.ctssd' on 'ol6-112-rac1'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'ol6-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'ol6-112-rac1'
CRS-2676: Start of 'ora.ctssd' on 'ol6-112-rac1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'ol6-112-rac1' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'ol6-112-rac1'
CRS-2676: Start of 'ora.asm' on 'ol6-112-rac1' succeeded

[root@ol6-112-rac1 oracle]# ocrconfig -import /home/oracle/ocr.dat
[root@ol6-112-rac1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
	 Version                  :          3
	 Total space (kbytes)     :     262120
	 Used space (kbytes)      :       2880
	 Available space (kbytes) :     259240
	 ID                       : 1206249750
	 Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

	 Cluster registry integrity check succeeded

	 Logical corruption check succeeded

# crsctl stop crs -f
# crsctl start crs

-- 수동 백업
[root@ol6-112-rac1 oracle]# ocrconfig -manualbackup

ol6-112-rac1     2015/12/25 19:21:49     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup_20151225_192149.ocr

[root@ol6-112-rac1 oracle]# ocrconfig -showbackup

ol6-112-rac1     2015/12/25 13:54:45     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup00.ocr

ol6-112-rac1     2015/12/25 13:54:45     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/day.ocr

ol6-112-rac1     2015/12/25 13:54:45     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/week.ocr

ol6-112-rac1     2015/12/25 19:21:49     /u01/app/11.2.0.3/grid/cdata/ol6-112-scan/backup_20151225_192149.ocr

Vote disk 관리

  • 11.2 부터 OCR 백업에 포함 됨 (ASM)

[oracle@ol6-112-rac1 ~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   2415456edd054f28bfc5474bd77dccce (/dev/asm-disk1) [DATA]
Located 1 voting disk(s).

RAC Backup and Recovery

  • Archive Log Mode 필수
    • Archive Log File 은 각 로컬 노드에 저장

Hot backup


-- F/S 와 같음 (cp → dd)
SQL> alter tablespace users begin backup;
SQL> !dd if=/dev/raw/raw10 of=/home/oracle/open/raw10_users bs=8k
SQL> alter tablespace users end backup;

Cold backup


-- F/S 와 같음 (cp → dd)
SQL> shutdown immediate (양노드)
$ dd if=/dev/raw/raw10 of=/home/oracle/open/raw10_users bs=8k
SQL> startup

Offline 가능 TS 장애 (Online Redo Log File 만 필요)


-- F/S 와 같음

-- TS 생성
create tablespace ts_new datafile '/dev/raw/raw29';

-- 백업
alter tablespace ts_new begin backup;
dd if=/dev/raw/raw29 of=/home/oracle/open/raw29_ts_new bs=8k
alter tablespace ts_new end backup;

-- TBL 생성
create table scott.tt100 (no number) tablespace ts_new;
insert into scott.tt100 values (1);
commit;
select * from scott.tt100;

-- 장애 유발 (rm → dd)
dd if=/dev/zero of=/dev/raw/raw29 bs=8k

alter tablespace ts_new offline immediate;
alter tablespace ts_new online; -- 오류

-- 복구
dd if=/home/oracle/open/raw29_ts_new of=/dev/raw/raw29 bs=8k
recover tablespace ts_new;
alter tablespace ts_new online;
select * from scott.tt100; -- 정상 조회

Offline 가능 TS 장애 (Archive Redo Log File 필요)


-- F/S 와 같음

-- TS 생성
create tablespace ts_new datafile '/dev/raw/raw29';

-- 백업
alter tablespace ts_new begin backup;
dd if=/dev/raw/raw29 of=/home/oracle/open/raw29_ts_new bs=8k
alter tablespace ts_new end backup;

-- TBL 생성
create table scott.tt100 (no number) tablespace ts_new;
insert into scott.tt100 values (1);
commit;
alter system switch logfile;
insert into scott.tt100 values (2);
commit;
alter system switch logfile;
insert into scott.tt100 values (3);
commit;
alter system switch logfile;
alter system switch logfile;
select * from scott.tt100;

-- 장애 유발 (rm → dd)
dd if=/dev/zero of=/dev/raw/raw29 bs=8k

alter tablespace ts_new offline immediate;
alter tablespace ts_new online; -- 오류

-- 복구
dd if=/home/oracle/open/raw29_ts_new of=/dev/raw/raw29 bs=8k
recover tablespace ts_new; -- 복구 시 로컬/리모트 노드 Archive Log File 필요
alter tablespace ts_new online;
select * from scott.tt100; -- 정상 조회

-- RAC 는 모든 노드가 통합된 SCN을 사용 하므로, 복구 시 원격 노드의 아카이브 파일이 필요 할 수 있음

Offline 불 가능 TS 장애


-- F/S 와 같음

-- 백업
alter tablespace system begin backup;
dd if=/dev/raw/raw6 of=/home/oracle/open/raw6_system bs=8k
alter tablespace system end backup;

-- TBL 생성
create table scott.tt100 (no number) tablespace system;
insert into scott.tt100 values (1);
insert into scott.tt100 values (2);
insert into scott.tt100 values (3);
commit;
select * from scott.tt100;

-- 장애 유발 (rm → dd)
dd if=/dev/zero of=/dev/raw/raw6 bs=8k
-- DB 강제 종료 됨

-- 복구
shutdown immediate
dd if=/home/oracle/open/raw6_system of=/dev/raw/raw6 bs=8k
startup mount
recover database; -- 복구 시 로컬/리모트 노드 Archive Log File 필요
alter database open;
select * from scott.tt100; -- 정상 조회

백업이 없는 DBF 장애 복구


-- F/S 와 같음

-- TS 생성
create tablespace test datafile '/dev/raw/raw34';

-- TBL 생성
create table scott.tt100 (no number) tablespace test;
insert into scott.tt100 values (1);
commit;
select * from scott.tt100;

-- 장애 유발 (rm → dd)
dd if=/dev/zero of=/dev/raw/raw34 bs=8k
alter tablespace test offline immediate;
alter tablespace test online; -- 오류

-- 복구(여분의 Raw device 있음)
alter database create datafile '/dev/raw/raw34' as '/dev/raw/raw30';
recover tablespace test;
alter tablespace test online;
select * from scott.tt100; -- 정상 조회

-- 복구(여분의 Raw device 없음/1차)
-- F/S 에 응급 복구
alter database create datafile '/dev/raw/raw34' as '/home/oracle/temp/test.dbf';
recover tablespace test;
alter tablespace test online;
select * from scott.tt100; -- 정상 조회

-- 복구(여분의 Raw device 없음/2차)
-- Raw device 증설(/dev/raw/raw35)
alter tablespace test offline;
alter database create datafile '/home/oracle/temp/test.dbf' as '/dev/raw/raw35';
recover tablespace test;
alter tablespace test online;
select * from scott.tt100; -- 정상 조회

Drop table 복구 (무중단)


-- F/S 와 같음

-- 전체 TS 백업
...
alter tablespace users begin backup;
dd if=/dev/raw/raw10 of=/home/oracle/open/raw10_users bs=8k
alter tablespace users end backup;
...

-- USER/TBL 생성
create user scott identified by tiger default tablespace users temporary tablespace temp quota unlimited on users;
grant connect, resource to scott;
create table scott.tt10 (no number);
insert into scott.tt10 values (1);
commit;
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual; -- 2015.12.25 20:56:12
select * from scott.tt10;

-- 장애 유발
drop table scott.tt10 purge;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

-- 복구 (노드1 에서 clone DB 생성)
create pfile from spfile;
mv initrac1.ora initclone.ora
vi initclone.ora -- 노드2 내용 삭제, db_name, control_files 경로 수정
alter database backup controlfile to trace as '/home/oracle/re.sql';
vi /home/oracle/re.sql -- DB이름, REDO/DBF 경로 수정
-- 백업된 DBF 전체 복원
...
dd if=/home/oracle/open/raw10_users of=/home/oracle/temp/raw10 bs=8k
...
export ORACLE_SID=clone
sqlplus / as sysdba
@/home/oracle/re.sql -- Control file 재생성 됨
-- 노드2 아카이브 파일을 노드1로 복사
recover database until time '2015.12.25 20:56:12' using backup controlfile;
alter database open resetlogs;
select * from scott.tt10; -- 정상 조회

-- 테이블 export/import
exp scott/tiger file=/home/oracle/scott.dmp tables=tt10
export ORACLE_SID=RAC1
imp scott/tiger file=/home/oracle/scott.dmp ignore=y

OPEN RESETLOGS Operations

  • 가능한 경우 current online redo logs 를 아카이빙
  • online redo log 파일의 내용을 지우고 log sequence number 를 1로 초기화
  • online redo log file 이 없는 경우 생성
  • control file 내 redo thread records 와 online redo log records 를 초기화 (closed, 1)
  • 모든 dbf 와 online redo logs 및 앞으로 archived redo logs 는 새로운 SCN 및 timestamp 로 초기화 된다.

일부 Control file 장애


-- Control file 목록
/dev/raw/raw24
/dev/raw/raw25
/dev/raw/raw26

-- 장애 유발
dd if=/dev/zero of=/dev/raw/raw24 bs=8k
alter system checkpoint; -- 인스턴스 다운

-- 복구 (정상 파일 복사)
dd if=/dev/raw/raw25 of=/dev/raw/raw24 bs=8k
startup

모든 Control file 장애


-- 백업
alter database backup controlfile to '/home/oracle/open/control01.ctl';

-- 장애 유발
dd if=/dev/zero of=/dev/raw/raw24 bs=8k
dd if=/dev/zero of=/dev/raw/raw25 bs=8k
dd if=/dev/zero of=/dev/raw/raw26 bs=8k
alter system checkpoint; -- 인스턴스 다운

-- 복구
dd if=/home/oracle/open/control01.ctl of=/dev/raw/raw24 bs=8k
dd if=/home/oracle/open/control01.ctl of=/dev/raw/raw25 bs=8k
dd if=/home/oracle/open/control01.ctl of=/dev/raw/raw26 bs=8k
startup -- 오류
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
alter database open noresetlogs; -- 오류
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

-- Control file 재생성
alter database backup controlfile to trace as '/home/oracle/re.sql';
shutdown abort;
vi /home/oracle/re.sql -- NORESETLOGS 선택
startup nomount;
alter system set cluster_database=false scope=spfile;
shutdown immediate;
startup nomount;
@/home/oracle/re.sql
alter system set cluster_database=true scope=spfile;
shutdown immediate;
startup mount;
alter database open; -- 오류
ORA-01113: file 1 needs media recovery
recover database;
alter database open;

CURRENT 아닌 Redo Log File 장애 (아카이브 완료)


-- Redo Log File 상태
NODE#1	GROUP#1	/dev/raw/raw12	/dev/raw/raw13	CURRENT
NODE#1	GROUP#2	/dev/raw/raw14	/dev/raw/raw15	INACTIVE	<<< 장애 대상
NODE#1	GROUP#3	/dev/raw/raw16	/dev/raw/raw17	INACTIVE
NODE#2	GROUP#4	/dev/raw/raw18	/dev/raw/raw19	CURRENT
NODE#2	GROUP#5	/dev/raw/raw20	/dev/raw/raw21	INACTIVE
NODE#2	GROUP#6	/dev/raw/raw22	/dev/raw/raw23	INACTIVE

-- 장애 유발#1
dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd if=/dev/zero of=/dev/raw/raw15 bs=8k
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
-- 문제 없음

-- 장애 유발#2
dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd if=/dev/zero of=/dev/raw/raw15 bs=8k
shutdown immediate
startup -- 오류
ORA-03113: end-of-file on communication channel

-- 복구
startup mount;
alter database drop logfile group 2; -- INACTIVE & 아카이브 완료 되었으므로 삭제 가능
alter database open;
alter database add logfile thread 1 group 1 ('/dev/raw/raw14', '/dev/raw/raw15') size 99M;

CURRENT Redo Log File 장애


-- Redo Log File 상태
NODE#1	GROUP#1	/dev/raw/raw12	/dev/raw/raw13	INACTIVE
NODE#1	GROUP#2	/dev/raw/raw14	/dev/raw/raw15	INACTIVE
NODE#1	GROUP#3	/dev/raw/raw16	/dev/raw/raw17	CURRENT		<<< 장애 대상
NODE#2	GROUP#4	/dev/raw/raw18	/dev/raw/raw19	CURRENT
NODE#2	GROUP#5	/dev/raw/raw20	/dev/raw/raw21	INACTIVE
NODE#2	GROUP#6	/dev/raw/raw22	/dev/raw/raw23	INACTIVE

-- 장애 유발#1
dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd if=/dev/zero of=/dev/raw/raw17 bs=8k
alter system switch logfile; -- 오류
ORA-03113: end-of-file on communication channel
-- 아래 오류와 함께 양 노드 중단
LGWR: terminating instance due to error 316

-- 확인
startup
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw16'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'

-- 복구 타입#1 - Cold 백업 DBF 사용
dd if=/home/oracle/close/raw6 of=/dev/raw/raw6 bs=8k
dd if=/home/oracle/close/raw7 of=/dev/raw/raw7 bs=8k
dd if=/home/oracle/close/raw8 of=/dev/raw/raw8 bs=8k
dd if=/home/oracle/close/raw9 of=/dev/raw/raw9 bs=8k
dd if=/home/oracle/close/raw10 of=/dev/raw/raw10 bs=8k
scp rac2:/home/oracle/archive/2_* /home/oracle/archive
recover database until cancel; -- 아카이브 파일 적용
alter database open resetlogs;

-- 복구 타입#2 - Open 백업 DBF 사용
dd if=/home/oracle/open/raw6 of=/dev/raw/raw6 bs=8k
dd if=/home/oracle/open/raw7 of=/dev/raw/raw7 bs=8k
dd if=/home/oracle/open/raw8 of=/dev/raw/raw8 bs=8k
dd if=/home/oracle/open/raw9 of=/dev/raw/raw9 bs=8k
dd if=/home/oracle/open/raw10 of=/dev/raw/raw10 bs=8k
scp rac2:/home/oracle/archive/2_* /home/oracle/archive
recover database until cancel; -- 아카이브 파일 적용 (요구하는 SEQUENCE 에 따라 Redo Log File 적용)
alter database open resetlogs;

-- CURRENT Redo Log File 내용은 복구 안됨

양 노드 CURRENT Redo Log File 장애


-- Redo Log File 상태
NODE#1	GROUP#1	/dev/raw/raw13	/dev/raw/raw14	INACTIVE
NODE#1	GROUP#2	/dev/raw/raw15	/dev/raw/raw16	CURRENT
NODE#1	GROUP#3	/dev/raw/raw17	/dev/raw/raw18	INACTIVE
NODE#2	GROUP#4	/dev/raw/raw19	/dev/raw/raw20	CURRENT
NODE#2	GROUP#5	/dev/raw/raw21	/dev/raw/raw22	INACTIVE
NODE#2	GROUP#6	/dev/raw/raw23	/dev/raw/raw24	INACTIVE

-- TBL 생성
create table tt03 (no numbeR) tablespace users;
insert into tt03 values (1); -- GROUP#2 저장
commit;
alter system switch logfile;
insert into tt03 values (2); -- GROUP#3 저장
commit;
alter system switch logfile;
insert into tt03 values (3); -- GROUP#1 저장 (복구안됨)
commit;

-- 장애 유발
dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd if=/dev/zero of=/dev/raw/raw19 bs=8k
dd if=/dev/zero of=/dev/raw/raw20 bs=8k
shutdown abort;

-- 확인
startup
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw13'
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw14'

-- 복구
dd if=/home/oracle/close/raw6 of=/dev/raw/raw6 bs=8k
dd if=/home/oracle/close/raw7 of=/dev/raw/raw7 bs=8k
dd if=/home/oracle/close/raw8 of=/dev/raw/raw8 bs=8k
dd if=/home/oracle/close/raw9 of=/dev/raw/raw9 bs=8k
dd if=/home/oracle/close/raw10 of=/dev/raw/raw10 bs=8k
scp rac2:/home/oracle/archive/2_* /home/oracle/archive
recover database until cancel; -- 아카이브 파일 적용 (요구하는 SEQUENCE 에 따라 Redo Log File 적용)
alter database open resetlogs;

Redo log file 전체 손상


-- Redo Log File 상태
NODE#1	GROUP#1	/dev/raw/raw13	/dev/raw/raw14	CURRENT
NODE#1	GROUP#2	/dev/raw/raw15	/dev/raw/raw16	INACTIVE
NODE#1	GROUP#3	/dev/raw/raw17	/dev/raw/raw18	INACTIVE
NODE#2	GROUP#4	/dev/raw/raw19	/dev/raw/raw20	INACTIVE
NODE#2	GROUP#5	/dev/raw/raw21	/dev/raw/raw22	CURRENT
NODE#2	GROUP#6	/dev/raw/raw23	/dev/raw/raw24	INACTIVE

-- TBL 생성
create table tt04 (no number) tablespace users;
insert into tt04 values (1);	-- GROUP#1 저장
commit;
alter system switch logfile;
insert into tt04 values (2);	-- GROUP#2 저장
commit;
alter system switch logfile;
insert into tt04 values (3);	-- GROUP#3 저장 (복구 안됨)
commit;

-- 장애 유발
dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd if=/dev/zero of=/dev/raw/raw18 bs=8k
shutdown abort;

-- 확인
startup
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw13'
ORA-00316: log 1 of thread 1, type 0 in header is not log file
ORA-00312: online log 1 thread 1: '/dev/raw/raw14'

-- 복구
dd if=/home/oracle/close/raw6 of=/dev/raw/raw6 bs=8k
dd if=/home/oracle/close/raw7 of=/dev/raw/raw7 bs=8k
dd if=/home/oracle/close/raw8 of=/dev/raw/raw8 bs=8k
dd if=/home/oracle/close/raw9 of=/dev/raw/raw9 bs=8k
dd if=/home/oracle/close/raw10 of=/dev/raw/raw10 bs=8k
scp rac2:/home/oracle/archive/2_* /home/oracle/archive
recover database until cancel; -- 아카이브 파일 적용 (요구하는 SEQUENCE 에 따라 원격 Redo Log File 적용)
alter database open resetlogs;

양쪽 노드 Redo log file 전체 손상


-- Redo Log File 상태
NODE#1	GROUP#1	/dev/raw/raw13	/dev/raw/raw14	CURRENT
NODE#1	GROUP#2	/dev/raw/raw15	/dev/raw/raw16	INACTIVE
NODE#1	GROUP#3	/dev/raw/raw17	/dev/raw/raw18	INACTIVE
NODE#2	GROUP#4	/dev/raw/raw19	/dev/raw/raw20	INACTIVE
NODE#2	GROUP#5	/dev/raw/raw21	/dev/raw/raw22	INACTIVE
NODE#2	GROUP#6	/dev/raw/raw23	/dev/raw/raw24	CURRENT

-- TBL 생성
create table tt05 (no number) tablespace users;
insert into tt04 values (1);	-- GROUP#1 저장
commit;
alter system switch logfile;
insert into tt04 values (2);	-- GROUP#2 저장
commit;
alter system switch logfile;
insert into tt04 values (3);	-- GROUP#3 저장 (복구 안됨)
commit;

-- 장애 유발
dd if=/dev/zero of=/dev/raw/raw13 bs=8k
dd if=/dev/zero of=/dev/raw/raw14 bs=8k
dd if=/dev/zero of=/dev/raw/raw15 bs=8k
dd if=/dev/zero of=/dev/raw/raw16 bs=8k
dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd if=/dev/zero of=/dev/raw/raw18 bs=8k
dd if=/dev/zero of=/dev/raw/raw19 bs=8k
dd if=/dev/zero of=/dev/raw/raw20 bs=8k
dd if=/dev/zero of=/dev/raw/raw21 bs=8k
dd if=/dev/zero of=/dev/raw/raw22 bs=8k
dd if=/dev/zero of=/dev/raw/raw23 bs=8k
dd if=/dev/zero of=/dev/raw/raw24 bs=8k
shutdown abort;

-- 확인
startup
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw18'

-- 복구 (close/open 같음)
dd if=/home/oracle/close/raw6 of=/dev/raw/raw6 bs=8k
dd if=/home/oracle/close/raw7 of=/dev/raw/raw7 bs=8k
dd if=/home/oracle/close/raw8 of=/dev/raw/raw8 bs=8k
dd if=/home/oracle/close/raw9 of=/dev/raw/raw9 bs=8k
dd if=/home/oracle/close/raw10 of=/dev/raw/raw10 bs=8k
scp rac2:/home/oracle/archive/2_* /home/oracle/archive
recover database until cancel; -- 아카이브 파일 적용 (요구하는 SEQUENCE 에 따라 원격 Redo Log File 적용)
alter database open resetlogs;

Redo log file 삭제 (백업 없음)


-- Redo Log File 상태
NODE#1	GROUP#1	/dev/raw/raw13	/dev/raw/raw14	CURRENT
NODE#1	GROUP#2	/dev/raw/raw15	/dev/raw/raw16	INACTIVE
NODE#1	GROUP#3	/dev/raw/raw17	/dev/raw/raw18	INACTIVE
NODE#2	GROUP#4	/dev/raw/raw19	/dev/raw/raw20	CURRENT
NODE#2	GROUP#5	/dev/raw/raw21	/dev/raw/raw22	INACTIVE
NODE#2	GROUP#6	/dev/raw/raw23	/dev/raw/raw24	INACTIVE

-- TBL 생성
create table tt80 (no number) tablespace users;
insert into tt04 values (1);	-- GROUP#1 저장
commit;
alter system switch logfile;
insert into tt04 values (2);	-- GROUP#2 저장
commit;
alter system switch logfile;
insert into tt04 values (3);	-- GROUP#3 저장
commit;

-- 장애 유발 (Current redo log file 삭제)

-- Redo Log File 상태#1
NODE#1	GROUP#1	/dev/raw/raw13	/dev/raw/raw14	ACTIVE	-- DBF 에 저장 안됨
NODE#1	GROUP#2	/dev/raw/raw15	/dev/raw/raw16	ACTIVE	-- DBF 에 저장 안됨
NODE#1	GROUP#3	/dev/raw/raw17	/dev/raw/raw18	CURRENT

-- Redo Log File 상태#2
alter system checkpoint;
NODE#1	GROUP#1	/dev/raw/raw13	/dev/raw/raw14	INACTIVE	-- DBF 에 저장됨
NODE#1	GROUP#2	/dev/raw/raw15	/dev/raw/raw16	INACTIVE	-- DBF 에 저장됨
NODE#1	GROUP#3	/dev/raw/raw17	/dev/raw/raw18	CURRENT

dd if=/dev/zero of=/dev/raw/raw17 bs=8k
dd if=/dev/zero of=/dev/raw/raw18 bs=8k
shutdown abort;

-- 확인
startup
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw17'
ORA-00316: log 3 of thread 1, type 0 in header is not log file
ORA-00312: online log 3 thread 1: '/dev/raw/raw18'

-- 복구 (복원 없음)
recover database until cancel; -- 아카이브 파일 적용
alter database open resetlogs; -- 오류
ORA-01194: file 1 needs more recovery to be consistent

alter system set "_allow_resetlogs_corruption" = true scope = spfile;
shutdown immediate;
startup -- 오류
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
alter database open resetlogs;

-- Redo Log File 상태#1 결과
select * from tt80; -- 오류
ORA-00942: table or view does not exist

-- Redo Log File 상태#2 결과
select * from tt80; -- 1, 2, 3 모두 조회 됨
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2015년 하반기 - 오라클 데이터베이스 스터디
  4. Chap.12 RAC Backup and Recovery