RMAN으로 drop tablespace복구하기 ; 과거의 controlfile을 가져와서 복구해야하는 경우

뽀인트!
drop tablespace 이후에는 current control파일에는 drop된 tablespace의 정보가 없으므로,
과거의 controlfile을 복원 후 복구를 해야합니다.!
RMAN> restore controlfile from '/app/rman/02n4abi7_1_1_20120226.rman';   <<  이 명령어가 이 포스트의 핵심입니다.  
 

(하기 전에 backup database;를 통해서 미리 백업을 해두세요)
1. 현재상황 확인 및 실수로 drop tablespace

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------
KOO                            /app/oracle/oradata/testdb/koo02.dbf
FBDA                           /app/oracle/oradata/testdb/fbda01.dbf
TEST                           /app/oracle/oradata/testdb/test01.dbf
KOO                            /app/oracle/oradata/testdb/koo01.dbf
EXAMPLE                        /app/oracle/oradata/testdb/example01.dbf
USERS                          /app/oracle/oradata/testdb/users01.dbf
UNDOTBS1                       /app/oracle/oradata/testdb/undotbs01.dbf
SYSAUX                         /app/oracle/oradata/testdb/sysaux01.dbf
SYSTEM                         /app/oracle/oradata/testdb/system01.dbf

9 rows selected.

SQL> drop tablespace koo including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name, file_name from dba_data_files
  2  where tablespace_name='KOO';

no rows selected  << KOO tablespace삭제됨을 확인

2-1. RMAN으로 백업한 controlfile내역찾기

RMAN> list backupset of controlfile; << 과거에 백업한 controlfile을 보기위한 명령어


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    1.03M      DISK        00:00:01     2012-02-26:18:56:08
        BP Key: 2   Status: AVAILABLE  Compressed: YES  Tag: TAG20120226T185501
        Piece Name: /app/rman/02n4abi7_1_1_20120226.rman
  Control File Included: Ckp SCN: 938690       Ckp time: 2012-02-26:18:56:07

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

찾았습니다. /app/rman/02n4abi7_1_1_20120226.rman 을 이용하면 되는군요

2-2. 과거의 컨트롤파일을 복원하기

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1345376 bytes
Variable Size                209717408 bytes
Database Buffers             318767104 bytes
Redo Buffers                   5832704 bytes

RMAN> restore controlfile from '/app/rman/02n4abi7_1_1_20120226.rman';

Starting restore at 2012-02-26:20:31:44
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/app/oracle/oradata/testdb/control01.ctl
Finished restore at 2012-02-26:20:31:46

3. 불완전 복구수행하기(시간은 alert_log.log에 drop tablespace시점 이전으로 돌리세요)

RMAN>  run {
alter database mount;
set until time='2012-02-26:20:25:54';
restore database;
recover database;
}

** 주의사항 
복구 수행하기 전에 $ export nls_date_format='rrrr-mm-dd:hh24:mi:ss' 를 실행시킨 후 RMAN접속 하시거나,
OR 
run {}안에 set until time 이전에 sql 'alter session set nls_date_format="rrrr-mm-dd:hh24:mi:ss"'; 을 추가해주세요

4. 자료확인

RMAN> alter database open resetlogs;

database opened

SQL> select tablespace_name, file_name from dba_data_files
  2  where tablespace_name='KOO';

TABLESPACE_NAME                FILE_NAME
------------------------------ ---------------------------------------------
KOO                            /app/oracle/oradata/testdb/koo02.dbf
KOO                            /app/oracle/oradata/testdb/koo01.dbf

복구완료!!