[RMAN] 전체 DATABASE(DATAFILE,CONTROLFILE,REDOLOG등)이 삭제 되었을때 복구

Datafile, control file, redo file, undo file등 모든 파일이 삭제 되었을 때의 시나리오입니다.

 

※ 실습 순서 요약

1.level 0 백업

2.archive log 백업

3.운영 중인 datafile, redo, control file 삭제 – 디스크 장애 시나리오 시작

4.Control file복구

5.DB 마운트 후 백업 파일 적용 및 복구

6.데이터 확인

 

(1) Incremental level 0 백업 수행

RMAN> backup as compressed backupset incremental level 0 database format '/home/oracle/rman2/%T_%U';

 

Starting backup at 19/12/05

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=275 device type=DISK

channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/oradata/ORCL19C/system01.dbf

input datafile file number=00003 name=/oradata/ORCL19C/sysaux01.dbf

input datafile file number=00007 name=/oradata/ORCL19C/users01.dbf

input datafile file number=00004 name=/oradata/ORCL19C/undotbs01.dbf

input datafile file number=00005 name=/oradata/ORCL19C/test01.dbf

channel ORA_DISK_1: starting piece 1 at 19/12/05

channel ORA_DISK_1: finished piece 1 at 19/12/05

piece handle=/home/oracle/rman2/20191205_1duim4fj_1_1 tag=TAG20191205T164019 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

Finished backup at 19/12/05

 

Starting Control File and SPFILE Autobackup at 19/12/05

piece handle=/u01/app/oracle/product/19.5.0/db_1/dbs/c-1490444115-20191205-11 comment=NONE

Finished Control File and SPFILE Autobackup at 19/12/05

 

RMAN> backup as compressed backupset format '/home/oracle/rman2/arch_%T_%U' archivelog all;

<- 아카이브 백업

Starting backup at 19/12/05

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=14 RECID=1 STAMP=1026066670

input archived log thread=1 sequence=15 RECID=2 STAMP=1026162059

input archived log thread=1 sequence=16 RECID=8 STAMP=1026227893

input archived log thread=1 sequence=17 RECID=9 STAMP=1026227893

input archived log thread=1 sequence=18 RECID=10 STAMP=1026227894

channel ORA_DISK_1: starting piece 1 at 19/12/05

channel ORA_DISK_1: finished piece 1 at 19/12/05

piece handle=/home/oracle/rman2/arch_20191205_1fuim4i6_1_1 tag=TAG20191205T164142 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=1 RECID=11 STAMP=1026232902

channel ORA_DISK_1: starting piece 1 at 19/12/05

channel ORA_DISK_1: finished piece 1 at 19/12/05

piece handle=/home/oracle/rman2/arch_20191205_1guim4iv_1_1 tag=TAG20191205T164142 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 19/12/05

 

Starting Control File and SPFILE Autobackup at 19/12/05

piece handle=/u01/app/oracle/product/19.5.0/db_1/dbs/c-1490444115-20191205-12 comment=NONE

Finished Control File and SPFILE Autobackup at 19/12/05

 

(3) 데이터 파일 상태 확인 및 데이터 파일 삭제

SQL> select name from v$datafile; <- datafile 확인

 

NAME

--------------------------------------------------------------------------------

/oradata/ORCL19C/system01.dbf

/oradata/ORCL19C/sysaux01.dbf

/oradata/ORCL19C/undotbs01.dbf

/oradata/ORCL19C/test01.dbf

/oradata/ORCL19C/users01.dbf

 

SQL> select member from v$logfile;

 

MEMBER

--------------------------------------------------------------------------------

/oradata/ORCL19C/redo03.log

/oradata/ORCL19C/redo02.log

/oradata/ORCL19C/redo01.log

 

SQL> select name from v$controlfile;

 

NAME

--------------------------------------------------------------------------------

/oradata/ORCL19C/control01.ctl

/oradata/ORCL19C/control02.ctl

 

(4) DB 장애 발생

$ rm -rf *

$ ls -al

합계 0

drwxr-x---. 2 oracle oinstall  6 12월  5 23:31 .

drwxr-xr-x. 7 oracle oinstall 83  6월 27 16:36 ..

$ pwd

/oradata/ORCL19C

 

SQL> shutdown abort

ORACLE instance shut down.

 

(5) DB 복구 수행

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area     788525944 bytes

 

Fixed Size                     8901496 bytes

Variable Size                293601280 bytes

Database Buffers             478150656 bytes

Redo Buffers                   7872512 bytes

 

RMAN> restore controlfile from '/u01/app/oracle/product/19.5.0/db_1/dbs/c-1490444115-20191205-16';

 

Starting restore at 19/12/05

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/oradata/ORCL19C/control01.ctl

output file name=/oradata/ORCL19C/control02.ctl

Finished restore at 19/12/05

 

 

$ ls

control01.ctl  control02.ctl <- control file 생성 됨

 

 

RMAN> alter database mount;

 

using target database control file instead of recovery catalog

Statement processed

 

RMAN> restore database; <- 백업받은 파일을 복원

 

Starting restore at 19/12/05

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 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 00001 to /oradata/ORCL19C/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oradata/ORCL19C/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /oradata/ORCL19C/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /oradata/ORCL19C/test01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /oradata/ORCL19C/users01.dbf

channel ORA_DISK_1: reading from backup piece /home/oracle/rman2/20191205_1duim4fj_1_1

channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1duim4fj_1_1 tag=TAG20191205T164019

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:45

Finished restore at 19/12/05

 

(6) 복구 완료 상태 확인 및 추가 사항 진행

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 23:32:29 2019

Version 19.5.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

SQL> select status from v$Instance;

 

STATUS

------------

MOUNTED

 

 

SQL> recover database until cancel using backup controlfile;

 

ORA-00279: change 2847596 generated at 12/05/2019 16:40:19 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_1_1026227892.dbf

ORA-00280: change 2847596 for thread 1 is in sequence #1

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 2847661 generated at 12/05/2019 16:41:41 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_2_1026227892.dbf

ORA-00280: change 2847661 for thread 1 is in sequence #2

ORA-00278: log file

'/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_1_1026227892.dbf' no longer

needed for this recovery

 

 

ORA-00279: change 2851281 generated at 12/05/2019 18:02:56 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_1_1026237776.dbf

ORA-00280: change 2851281 for thread 1 is in sequence #1

ORA-00278: log file

'/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_2_1026227892.dbf' no longer

needed for this recovery

 

 

ORA-00308: cannot open archived log

'/u01/app/oracle/product/19.5.0/db_1/dbs/arch1_1_1026237776.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 7

 

SQL> alter database open resetlogs;

 

Database altered.

 

SQL> select status from v$instance;

 

STATUS

------------

OPEN

 

SQL> select name from v$datafile;

 

NAME

--------------------------------------------------------------------------------

/oradata/ORCL19C/system01.dbf

/oradata/ORCL19C/sysaux01.dbf

/oradata/ORCL19C/undotbs01.dbf

/oradata/ORCL19C/test01.dbf

/oradata/ORCL19C/users01.dbf

 

복구완료했습니다.