본문 바로가기
Oracle/RMAN

[RMAN] 증분 백업을 사용한 Drop Table 복구

by 취미툰 2019. 12. 18.
반응형

증분 백업 받은 파일을 가지고 drop table 장애를 복구를 하는 시나리오입니다.

 

※ 실습 순서 요약

1.전체 데이터베이스를 incremental level 0으로 백업 / 아카이브 백업

2.실습용 테이블(tocsgtest.test3)을 생성 후 증분 백업 (level 2)

3.실습용 테이블에 1을 입력 후 차등 증분 백업 (level2)

4.실습용 테이블에 2을 입력 후 차등 증분 백업 (level2)

5.실습용 테이블을 drop 후 복구

 

(1) 현재 상태 확인

$ ls -al /home/oracle/rman2

합계 4

drwxr-xr-x.  2 oracle oinstall    6 12월  5 16:38 .

drwx------. 25 oracle oinstall 4096 12월  5 15:03 ..

 

(2) 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) 백업 파일 생성 확인

$ ls -al

합계 483120

drwxr-xr-x.  2 oracle oinstall       103 12월  5 16:42 .

drwx------. 25 oracle oinstall      4096 12월  5 15:03 ..

-rw-r-----.  1 oracle oinstall 345563136 12월  5 16:40 20191205_1duim4fj_1_1

-rw-r-----.  1 oracle oinstall 146952192 12월  5 16:41 arch_20191205_1fuim4i6_1_1

-rw-r-----.  1 oracle oinstall   2191872 12월  5 16:42 arch_20191205_1guim4iv_1_1

 

(4) 테스트용 데이터 생성

SQL> create user test identified by test; <- 실습 유저 생성

User created.

 

SQL> alter user test account unlock; <- 계정 잠금 해제 및 기본적인 권한 부여

User altered.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> grant unlimited tablespace to test;

Grant succeeded.

 

SQL> create table test.test(no number) tablespace users; <- 실습용 테이블 만들기

Table created.

 

(5) Incremental level 2로 백업 수행

RMAN> backup incremental level 2 tablespace users 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 incremental level 2 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/oradata/ORCL19C/users01.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_1iuim5n4_1_1 tag=TAG20191205T170124 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-13 comment=NONE

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

 

(6) 백업 파일 생성 확인

$ ls -al

합계 483180

drwxr-xr-x.  2 oracle oinstall      4096 12월  5 17:01 .

drwx------. 25 oracle oinstall      4096 12월  5 15:03 ..

-rw-r-----.  1 oracle oinstall 345563136 12월  5 16:40 20191205_1duim4fj_1_1

-rw-r-----.  1 oracle oinstall     57344 12월  5 17:01 20191205_1iuim5n4_1_1

-rw-r-----.  1 oracle oinstall 146952192 12월  5 16:41 arch_20191205_1fuim4i6_1_1

-rw-r-----.  1 oracle oinstall   2191872 12월  5 16:42 arch_20191205_1guim4iv_1_1

 

(7) 테스트용 데이터 생성

SQL> insert into test.test values(1);

1 row created.

 

SQL> commit;

Commit complete.

 

(8) Incremental level 2로 백업 수행

RMAN> backup incremental level 2 tablespace users 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=36 device type=DISK

channel ORA_DISK_1: starting incremental level 2 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/oradata/ORCL19C/users01.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_1kuim6bd_1_1 tag=TAG20191205T171213 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-14 comment=NONE

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

 

 

(9) 백업 파일 생성 확인

$ ls -al

합계 483320

drwxr-xr-x.  2 oracle oinstall      4096 12월  5 17:12 .

drwx------. 25 oracle oinstall      4096 12월  5 15:03 ..

-rw-r-----.  1 oracle oinstall 345563136 12월  5 16:40 20191205_1duim4fj_1_1

-rw-r-----.  1 oracle oinstall     57344 12월  5 17:01 20191205_1iuim5n4_1_1

-rw-r-----.  1 oracle oinstall    139264 12월  5 17:12 20191205_1kuim6bd_1_1

-rw-r--r--.  1 oracle oinstall        69 12월  5 17:06 afiedt.buf

-rw-r-----.  1 oracle oinstall 146952192 12월  5 16:41 arch_20191205_1fuim4i6_1_1

-rw-r-----.  1 oracle oinstall   2191872 12월  5 16:42 arch_20191205_1guim4iv_1_1

 

(10) 테스트용 데이터 생성

SQL> insert into test.test values(2);

1 row created.

 

SQL> commit;

Commit complete.

 

(11) Incremental level 2로 백업 수행

RMAN> backup incremental level 2 tablespace users 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=43 device type=DISK

channel ORA_DISK_1: starting incremental level 2 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/oradata/ORCL19C/users01.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_1muim6jt_1_1 tag=TAG20191205T171644 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-15 comment=NONE

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

 

(12) 백업 파일 생성 확인

$ ls -al

합계 483384

drwxr-xr-x.  2 oracle oinstall      4096 12월  5 17:16 .

drwx------. 25 oracle oinstall      4096 12월  5 15:03 ..

-rw-r-----.  1 oracle oinstall 345563136 12월  5 16:40 20191205_1duim4fj_1_1

-rw-r-----.  1 oracle oinstall     57344 12월  5 17:01 20191205_1iuim5n4_1_1

-rw-r-----.  1 oracle oinstall    139264 12월  5 17:12 20191205_1kuim6bd_1_1

-rw-r-----.  1 oracle oinstall     65536 12월  5 17:16 20191205_1muim6jt_1_1

-rw-r--r--.  1 oracle oinstall        69 12월  5 17:06 afiedt.buf

-rw-r-----.  1 oracle oinstall 146952192 12월  5 16:41 arch_20191205_1fuim4i6_1_1

-rw-r-----.  1 oracle oinstall   2191872 12월  5 16:42 arch_20191205_1guim4iv_1_1

 

(13) 테이블스페이스 상태 확인

 

SQL> select * from test.test;

 

        NO

----------

         1

         2

(14) 데이터가 존재한 시간 체크

 

SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') FROM DUAL;

 

TO_CHAR(SYSDATE,'YY

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

2019-12-05:17:21:41

 

(15) 테스트 테이블 삭제 및 확인

SQL> drop table test.test purge;

 

Table dropped.

 

SQL> select * from test.test;

 

ERROR at line 1:

ORA-00942: table or view does not exist

 

(16) Incremental 복구 수행

RMAN> run {

startup mount;

sql 'alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"';

set until time ='2019-12-05:17:21:41'; <-- 복구 전 시간을 써주는 것이 중요합니다.

restore database;

recover database;

alter database open resetlogs;

}

Oracle instance started

database mounted

 

Total System Global Area     788525944 bytes

 

Fixed Size                     8901496 bytes

Variable Size                293601280 bytes

Database Buffers             478150656 bytes

Redo Buffers                   7872512 bytes

 

sql statement: alter session set nls_date_format="YYYY-MM-DD:HH24:MI:SS"

 

executing command: SET until clause

 

Starting restore at 19/12/05

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=253 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:05

Finished restore at 19/12/05

 

Starting recover at 19/12/05

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00007: /oradata/ORCL19C/users01.dbf

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

channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1iuim5n4_1_1 tag=TAG20191205T170124

channel ORA_DISK_1: restored backup piece 1

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

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00007: /oradata/ORCL19C/users01.dbf

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

channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1kuim6bd_1_1 tag=TAG20191205T171213

channel ORA_DISK_1: restored backup piece 1

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

channel ORA_DISK_1: starting incremental datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00007: /oradata/ORCL19C/users01.dbf

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

channel ORA_DISK_1: piece handle=/home/oracle/rman2/20191205_1muim6jt_1_1 tag=TAG20191205T171644

channel ORA_DISK_1: restored backup piece 1

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

 

starting media recovery

media recovery complete, elapsed time: 00:00:02

 

Finished recover at 19/12/05

 

Statement processed

 

(17) 복구 완료 확인 및 데이터 확인

$ sqlplus test/test

 

SQL> select * from test;

 

        NO

----------

         1

         2

반응형
저작자표시

'Oracle > RMAN' 카테고리의 다른 글

[RMAN] Data Recovery Adivisor  (0) 2019.12.20
[RMAN] 전체 DATABASE(DATAFILE,CONTROLFILE,REDOLOG등)이 삭제 되었을때 복구  (0) 2019.12.19
[RMAN] Datafile 삭제 후 Mount 상태에서복구(offline 안되는 Tablespace)  (0) 2019.12.17
[RMAN]Datafile 삭제 후 복구(offline되는 Tablespace)  (0) 2019.12.12
[RMAN] 증분백업(Incremental backup)  (0) 2019.12.11

관련글

댓글

티스토리툴바