시나리오12: 불완전복구-Time Based: 실수로 Drop한 table복구
작성자 관리자 작성시간 2003-07-24 13:48:25
 

SQL> !date
2003. 07. 24. (목) 11:52:02 KST

1) 실수로 table을 Drop하였다면,

SQL>
SQL>
SQL> drop table hr.emphist cascade constraints;

Table dropped.

SQL> select * from hr.emphist;
select * from hr.emphist
                *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> shutdown immediate;

2) Recovery 수행

SQL> !cp $HOME/ONBACKUP/*.dbf $HOME/oradata/HUMAN/
중요한 Table이므로 Drop된 시점 직전으로 DB전체를 되돌린다.

SQL> startup
ORACLE instance started.

Total System Global Area                          235999352 bytes
Fixed Size                                          450680 bytes
Variable Size                                    201326592 bytes
Database Buffers                                  33554432 bytes
Redo Buffers                                        667648 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/human/oradata/HUMAN/system01.dbf'

SQL> set autorecovery on
SQL> recover database until time '2003-07-24:11:52:02'
....
Log applied.
Media recovery complete.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        1          1        40  104857600          2 YES INACTIVE                825618 24-JUL-03
        2          1        41  104857600          2 NO  CURRENT                825622 24-JUL-03

SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
        1          1          0  104857600          2 YES UNUSED                0
        2          1          1  104857600          2 NO  CURRENT                825651 24-JUL-03


3) log sequence number 가 Reset 되었으므로  Whole-Olinebackup을 수행

SQL> !rm $HOME/arch/log/*

SQL> @LABS/onlinebackup.sql


목록 | 입력 | 수정 | 답변 | 삭제