Drop 된 Table 복구하기

예제 : 1. create table space test -> create table guest tablespace test -> insert into guest -> rm test datafile -> drop table guest

 

temp 폴더에서 사용하던 오라클을 temp2 폴더로 임시로 지정하여 guest 테이블이 나오는지 본다.

 

[oracle@server122 temp2]$ cp -av /data/temp/control01.ctl ./
`/data/temp/control01.ctl' -> `./control01.ctl'
[oracle@server122 temp2]$ cp -av /data/temp/redo* ./
`/data/temp/redo01_a.rdo' -> `./redo01_a.rdo'
`/data/temp/redo01_b.rdo' -> `./redo01_b.rdo'
`/data/temp/redo01_c.rdo' -> `./redo01_c.rdo'
`/data/temp/redo02_a.rdo' -> `./redo02_a.rdo'
`/data/temp/redo02_b.rdo' -> `./redo02_b.rdo'
`/data/temp/redo02_c.rdo' -> `./redo02_c.rdo'
[oracle@server122 temp2]$ cp -av /data/backup/close/*.dbf ./
`/data/backup/close/example01.dbf' -> `./example01.dbf'
`/data/backup/close/sysaux01.dbf' -> `./sysaux01.dbf'
`/data/backup/close/system01.dbf' -> `./system01.dbf'
`/data/backup/close/temp01.dbf' -> `./temp01.dbf'
`/data/backup/close/temp_web01.dbf' -> `./temp_web01.dbf'
`/data/backup/close/test01.dbf' -> `./test01.dbf'
`/data/backup/close/ts_web01.dbf' -> `./ts_web01.dbf'
`/data/backup/close/ts_web_idx01.dbf' -> `./ts_web_idx01.dbf'
`/data/backup/close/undotbs01.dbf' -> `./undotbs01.dbf'
`/data/backup/close/users01.dbf' -> `./users01.dbf'

백업된 datafile과 현재 사용하던 control file & redo log file 을 temp2 로 폴더로 복사한다.

백업된 Control file 을 쓰면 어떻게 될까?_?

[oracle@server122 temp2]$ cd ~
[oracle@server122 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jul 4 17:40:35 2013

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

Connected to an idle instance.

SYS> startup mount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1344616 bytes
Variable Size             272632728 bytes
Database Buffers          142606336 bytes
Redo Buffers                6086656 bytes
Database mounted.
SYS> @df

TS_NAME    FILE_NAME                                             MB STATUS
---------- -------------------------------------------------- ----- -------
SYSTEM     /data/temp2/system01.dbf                            2010 SYSTEM
SYSAUX     /data/temp2/sysaux01.dbf                             520 ONLINE
UNDOTBS1   /data/temp2/undotbs01.dbf                             90 ONLINE
USERS      /data/temp2/users01.dbf                                5 ONLINE
EXAMPLE    /data/temp2/example01.dbf                            346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf                              100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf                           10 OFFLINE
TEST       /data/temp/test01.dbf                                  0 ONLINE
TS_NEW     /data/temp/ts_new.dbf                                 10 RECOVER

9 rows selected.

SYS> !
[oracle@server122 ~]$ vi /app/oracle/product/11g/dbs/inittestdb.ora
[oracle@server122 ~]$ exit
exit

SYS> alter database create datafile '/data/temp/test01.dbf'
  2  as '/data/temp2/test01.dbf';

Database altered.

test01.dbf 파일이 지워졌으니 대체품을 새로 만든다.

 

SYS> @df

TS_NAME    FILE_NAME                                             MB STATUS
---------- -------------------------------------------------- ----- -------
SYSTEM     /data/temp2/system01.dbf                            2010 SYSTEM
SYSAUX     /data/temp2/sysaux01.dbf                             520 ONLINE
UNDOTBS1   /data/temp2/undotbs01.dbf                             90 ONLINE
USERS      /data/temp2/users01.dbf                                5 ONLINE
EXAMPLE    /data/temp2/example01.dbf                            346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf                              100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf                           10 OFFLINE
TEST       /data/temp2/test01.dbf                                 5 ONLINE
TS_NEW     /data/temp/ts_new.dbf                                 10 RECOVER

9 rows selected.

temp2 로 모두 변경 완료

SYS> @log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC
------ --------------------------------------------- ---- ---- -------- -----
     1 /data/temp/redo01_a.rdo                         10    1 CURRENT  NO
     1 /data/temp/redo01_b.rdo                         10    1 CURRENT  NO
     1 /data/temp/redo01_c.rdo                         10    1 CURRENT  NO
     2 /data/temp/redo02_a.rdo                         10    0 UNUSED   YES
     2 /data/temp/redo02_b.rdo                         10    0 UNUSED   YES
     2 /data/temp/redo02_c.rdo                         10    0 UNUSED   YES

6 rows selected.

SYS> alter database rename file '/data/temp/redo01_a.rdo'
  2  to '/data/temp2/redo01_a.rdo';

Database altered.

SYS> ed
Wrote file afiedt.buf

  1  alter database rename file '/data/temp/redo01_b.rdo'
  2* to '/data/temp2/redo01_b.rdo'
SYS> /

Database altered.

SYS> ed
Wrote file afiedt.buf

  1  alter database rename file '/data/temp/redo01_c.rdo'
  2* to '/data/temp2/redo01_c.rdo'
SYS> /

Database altered.

SYS> ed
Wrote file afiedt.buf

  1  alter database rename file '/data/temp/redo02_c.rdo'
  2* to '/data/temp2/redo02_c.rdo'
SYS> /

Database altered.

SYS> ed
Wrote file afiedt.buf

  1  alter database rename file '/data/temp/redo02_b.rdo'
  2* to '/data/temp2/redo02_b.rdo'
SYS> /

Database altered.

SYS> ed
Wrote file afiedt.buf

  1  alter database rename file '/data/temp/redo02_a.rdo'
  2* to '/data/temp2/redo02_a.rdo'
SYS> /

Database altered.

SYS> ed
Wrote file afiedt.buf

  1  alter database rename file '/data/temp/redo02_a.rdo'
  2* to '/data/temp2/redo02_a.rdo'
SYS> @log

GROUP# MEMBER                                          MB SEQ# STATUS   ARC
------ --------------------------------------------- ---- ---- -------- -----
     1 /data/temp2/redo01_a.rdo                        10    1 CURRENT  NO
     1 /data/temp2/redo01_b.rdo                        10    1 CURRENT  NO
     1 /data/temp2/redo01_c.rdo                        10    1 CURRENT  NO
     2 /data/temp2/redo02_a.rdo                        10    0 UNUSED   YES
     2 /data/temp2/redo02_b.rdo                        10    0 UNUSED   YES
     2 /data/temp2/redo02_c.rdo                        10    0 UNUSED   YES

6 rows selected.

temp2 로 리두 파일들도 변경 완료

SYS> @df

TS_NAME    FILE_NAME                                             MB STATUS
---------- -------------------------------------------------- ----- --------
SYSTEM     /data/temp2/system01.dbf                            2010 SYSTEM
SYSAUX     /data/temp2/sysaux01.dbf                             520 ONLINE
UNDOTBS1   /data/temp2/undotbs01.dbf                             90 ONLINE
USERS      /data/temp2/users01.dbf                                5 ONLINE
EXAMPLE    /data/temp2/example01.dbf                            346 ONLINE
TS_WEBHARD /data/temp/ts_web01.dbf                              100 OFFLINE
TS_WEB_IDX /data/temp/ts_web_idx01.dbf                           10 OFFLINE
TEST       /data/temp2/test01.dbf                                 5 ONLINE
TS_NEW     /data/temp/ts_new.dbf                                 10 RECOVER

9 rows selected.

SYS> recover database time until '2013-07-04:16:44:00' ; (미리 알고 있는 시간으로 복구)(시간모를땐 어케하지?ㅠㅠ)
ORA-00905: missing keyword

SYS> recover database until time '2013-07-04:16:44:00';
ORA-00279: change 1271471 generated at 07/04/2013 12:37:04 needed for thread 1
ORA-00289: suggestion : /data/arc1/1_1_819894588.arc
ORA-00280: change 1271471 for thread 1 is in sequence #1

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

Log applied.
Media recovery complete.
SYS> alter database open resetlogs;

Database altered.

SYS> select * from guest;

        NO
----------
         3
         6
         7
         9