hot backup 복구 시나리오2

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

방법 : hot backup 복구 시나리오

1. 아카이브 모드 확인

2. mmmts 테이블 스페이스 begin backup 후 datafile 백업

3. mmmts 테이블 스페이스 end backup

4. end backup 후 로그 스위치 3번 실행

5. mmmts 테이블 스페이스 datafile 삭제 후 db 강제종료

6. startup 시 장애 발생 후 정상종료

7. 복구진행

아카이브 모드 확인

SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /oracle/app/arch
Oldest online log sequence     95
Next log sequence to archive   97
Current log sequence           97

테이블 스페이스 확인

SQL> COL FILE_NAME FOR A50
SQL> SELECT FILE_ID, TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;
 
   FILE_ID TABLESPACE_NAME          FILE_NAME
---------- ------------------------------ --------------------------------------------------
     1 SYSTEM              /oradata/ORCL/system01.dbf
     2 UNDOTBS1              /oradata/ORCL/undotbs01.dbf
     6 USERS              /oradata/ORCL/users01.dbf
     7 XDB                  /oradata/ORCL/xdb01.dbf
     8 MMMTS              /oradata/ORCL/mmmts01.dbf
 
5 rows selected.

MMMTS 테이블스페이스 begin backup 진행(file_id 8번)

SQL> ALTER TABLESPACE MMMTS BEGIN BACKUP;

핫백업 상태 확인

SQL> 
SELECT A.TABLESPACE_NAME, B.*
FROM DBA_DATA_FILES A, V$BACKUP B
WHERE A.FILE_ID = B.FILE#;
 
TABLESPACE_NAME             FILE# STATUS        CHANGE# TIME
------------------------------ ---------- ------------------ ---------- ---------
SYSTEM                    1 NOT ACTIVE              0 (null)
UNDOTBS1                2 NOT ACTIVE              0 (null)
USERS                    6 NOT ACTIVE              0 (null)
XDB                    7 NOT ACTIVE              0 (null)
MMMTS                    8 ACTIVE        4712973 19-DEC-18

MMMTS의 STATUS가 ACTIVE로 변경됨

cp 명령으로 핫백업 진행

SQL> !mkdir -p /oradata/ORCL/hot/
SQL> !cp -av /oradata/ORCL/mmmts01.dbf /oradata/ORCL/hot/mmmts01.dbf

MMMTS 테이블스페이스 end backup 진행

SQL> ALTER TABLESPACE MMMTS END BACKUP;

핫백업 상태 확인

SQL> 
SELECT A.TABLESPACE_NAME, B.*
FROM DBA_DATA_FILES A, V$BACKUP B
WHERE A.FILE_ID = B.FILE#;
 
TABLESPACE_NAME             FILE# STATUS        CHANGE# TIME
------------------------------ ---------- ------------------ ---------- ---------
SYSTEM                    1 NOT ACTIVE              0 (null)
UNDOTBS1                2 NOT ACTIVE              0 (null)
USERS                    6 NOT ACTIVE              0 (null)
XDB                    7 NOT ACTIVE              0 (null)
MMMTS                    8 NOT ACTIVE        4712973 19-DEC-18

MMMTS의 STATUS가 NOT ACTIVE로 변경됨

end backup 후 로그 스위치 3번 실행

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

장애상황 만들기

원본 mmmts01.dbf 파일 삭제

SQL> !rm -rf /oradata/ORCL/mmmts01.dbf

DB 강제 종료

SQL> SHUTDOWN ABORT
ORACLE instance shut down.

DB 시작

SQL> STARTUP
ORACLE instance started.
 
Total System Global Area  320306616 bytes
Fixed Size             740792 bytes
Variable Size          285212672 bytes
Database Buffers       33554432 bytes
Redo Buffers             798720 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/oradata/ORCL/mmmts01.dbf'

data file 8번 장애 발생

복구 진행

db 정상 종료

SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.

hot backup 한 datafile 기존위치로 이동

!cp -av /oradata/ORCL/hot/mmmts01.dbf /oradata/ORCL/mmmts01.dbf

mount 상태로 startup

SQL> STARTUP MOUNT
ORACLE instance started.
 
Total System Global Area  320306616 bytes
Fixed Size             740792 bytes
Variable Size          285212672 bytes
Database Buffers       33554432 bytes
Redo Buffers             798720 bytes
Database mounted.

file_id 8번 datafile recover 진행

SQL> RECOVER DATAFILE 8;
ORA-00279: change 4712973 generated at 12/19/2018 14:00:56 needed for thread 1
ORA-00289: suggestion : /oracle/app/arch1_97.dbf
ORA-00280: change 4712973 for thread 1 is in sequence #97
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
엔터 입력
Log applied.
Media recovery complete.

db open상태로 전한

SQL> ALTER DATABASE OPEN;
 
Database altered.

복구 완료

참조 :