Incomplete Recovery - using backup controlfile

실습 순서

1. Tablespace 생성 후 DB 전체를 Backup 한다.

2. test 테이블을 1번에서 생성한 tablespace에 생성한다.

3. drop tablespace로 1번에서 생성한 tablespace를 삭제한다.

4. 로그스위치를 수차례 발생시킨다.

5. 1번 과정에서 Backup 받았던 Data files과 Control files를 복원한다.

6. alert_testdb.log 파일에서 3번 작업을 수행했던 시간을 확인한다.

7. DB를 mount 단계로 시작한다.

8. recover database until time 'YYYY-MM-DD:HH24:MI:SS' using backup controlfile; 실행한다.

9. 8번 실행 후 alter database open resetlogs; 실행한다.

10. 복구가 완료 되었는지 확인한다.

SQL> create table scott.test(no number) tablespace ts_c;

Table created.

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

1 row created.

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

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from scott.test;

        NO

----------

         1

         2

SQL> drop tablespace ts_c including contents and datafiles;

Tablespace dropped.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

#복구 중 실수로 문제가 발생한 DB 이외에 기존 DB 까지 사용하지 못하는 경우가 발생될 수 있다.

#따라서 문제가 발생한 Data file까지 포함해 모든 Data file, Redo log file, Control file까지 Backup을 받아놓는다.

SQL> !cp -av /home/oracle/oradata/testdb/*.dbf /data/backup/temp

SQL> !cp -av /home/oracle/oradata/testdb/*.log /data/backup/temp

SQL> !cp -av /home/oracle/oradata/testdb/*.ctl /data/backup/temp

#위와 같이 Data file, Redo log file, Control file까지 Backup을 받아놓은 후 아래와 같이 복구를 시작한다.

SQL> !cp -av /data/backup/cr_close/*.dbf /home/oracle/oradata/testdb/

`/data/backup/cr_close/example01.dbf' -> `/home/oracle/oradata/testdb/example01.dbf'

`/data/backup/cr_close/sysaux01.dbf' -> `/home/oracle/oradata/testdb/sysaux01.dbf'

`/data/backup/cr_close/system01.dbf' -> `/home/oracle/oradata/testdb/system01.dbf'

`/data/backup/cr_close/temp01.dbf' -> `/home/oracle/oradata/testdb/temp01.dbf'

`/data/backup/cr_close/ts_a01.dbf' -> `/home/oracle/oradata/testdb/ts_a01.dbf'

`/data/backup/cr_close/ts_b01.dbf' -> `/home/oracle/oradata/testdb/ts_b01.dbf'

`/data/backup/cr_close/ts_c01.dbf' -> `/home/oracle/oradata/testdb/ts_c01.dbf'

`/data/backup/cr_close/undotbs01.dbf' -> `/home/oracle/oradata/testdb/undotbs01.dbf'

`/data/backup/cr_close/users01.dbf' -> `/home/oracle/oradata/testdb/users01.dbf'

SQL> !cp -av /data/backup/cr_close/*.ctl /home/oracle/oradata/testdb/

`/data/backup/cr_close/control01.ctl' -> `/home/oracle/oradata/testdb/control01.ctl'

`/data/backup/cr_close/control02.ctl' -> `/home/oracle/oradata/testdb/control02.ctl'

`/data/backup/cr_close/control03.ctl' -> `/home/oracle/oradata/testdb/control03.ctl'

SQL> startup mount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

Database mounted.

#alert_testdb.log 에서 drop tablespace ts_c including contents and datafiles 작업 수행 시간을 확인한다.

#/home/oracle/admin/testdb/bdump/alert_testdb.log 내용 중 일부

Thu Mar 11 12:27:55 2010

drop tablespace ts_c including contents and datafiles

Thu Mar 11 12:27:58 2010

Deleted file /home/oracle/oradata/testdb/ts_c01.dbf

Completed: drop tablespace ts_c including contents and datafiles

SQL> recover database until time '2010-03-11:12:27:50' using backup controlfile;

ORA-00279: change 502969 generated at 03/11/2010 11:14:17 needed for thread 1

ORA-00289: suggestion : /data/arc2/4_1_710077424.arc

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

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

auto

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from scott.test;

        NO

----------

         1

         2

SQL>