drop tablespace 로 잘못 삭제된 Tablespace 복구

- drop tablespace 로 잘못 삭제된 Tablespace 복구 

 

TABLESPACE   MB FILE_NAME
---------- ---- --------------------------------------------------
UNDO         10 /home/oracle/oradata/testdb/undo01.dbf
TEST         10 /home/oracle/oradata/testdb/test01.dbf
EXAMPLE     100 /home/oracle/oradata/testdb/example01.dbf
USERS         5 /home/oracle/oradata/testdb/users01.dbf
SYSAUX      250 /home/oracle/oradata/testdb/sysaux01.dbf
SYSTEM      450 /home/oracle/oradata/testdb/system01.dbf

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

 

SQL> insert into scott.test06 values(6);

 

SQL> commit;

 

SQL> select * from scott.test06;

        NO
----------
         6

 

SQL> @tt

TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24:MI:SS
--------------------------------------
2012-01-08:03:01:13

 

★ Drop tablespace 장애 발생

 

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

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

 

# 필요한 Data file 만 복원

[oracle@study119LJS temp]$ cp /data/backup/close/system01.dbf /data/backup/temp/
[oracle@study119LJS temp]$ cp /data/backup/close/sysaux01.dbf /data/backup/temp/
[oracle@study119LJS temp]$ cp /data/backup/close/undo01.dbf /data/backup/temp/
[oracle@study119LJS temp]$ cp /data/backup/close/test01.dbf /data/backup/temp/

 

# 백업 컨트롤파일도 복사

[oracle@study119LJS temp]$ cp /data/backup/close/*.ctl /data/backup/temp/

 

# Redo logfile 은 최근에 사용했던것 복사

[oracle@study119LJS temp]$ cp /home/oracle/oradata/testdb/*.log /data/backup/temp/

 

pfile 에 있는 컨트롤파일 temp로 변경

mount후

데이터파일 리드로그파일 위치 변경 및 필요없는 데이터파일 offline drop 시키기

 

recover database until time '2012-01-08:03:01:13' using backup controlfile;

 

auto

 

alter database open resetlogs

 

조회하면 나온다.