RMAN으로 drop된 테이블스페이스 복구하기2 (TSPITR)

Tech Note 정보
카테고리: [ Oracle Database ]
조회수: 7769

Tablespace Point-in-Time Recovery (TSPITR) 방법으로 삭제된 테이블스페이스 복구를 시도해보았습니다. (thanks to durecat)

 

1. 일단 테스트를 위해 테이블스페이스생성, 테이블생성, 데이터입력

SQL> create tablespace TS_BACKUP_TEST datafile size 10m;
SQL> create table system.BACKUP_TEST_1 (no number, name varchar2(10)) tablespace TS_BACKUP_TEST;
SQL> insert into system.BACKUP_TEST_1 values (1,'AAA');
SQL> insert into system.BACKUP_TEST_1 values (2,'BBB');
SQL> commit;

 

2. 백업수행

RMAN> backup tablespace 'TS_BACKUP_TEST';
Starting backup at 20-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=+DATA/ORCL12/DATAFILE/ts_backup_test.413.917692055
channel ORA_DISK_1: starting piece 1 at 20-JUL-16
channel ORA_DISK_1: finished piece 1 at 20-JUL-16
piece handle=/backup/ARC/ORCL12/backupset/2016_07_20/o1_mf_nnndf_TAG20160720T103250_crxo62v6_.bkp tag=TAG20160720T103250 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JUL-16

 

3. 테이블스페이스 백업이 정상적으로 잘 되었는지 확인해봅니다.

RMAN> list backup of tablespace TS_BACKUP_TEST;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74      Full    1.10M      DISK        00:00:00     20-JUL-16
        BP Key: 74   Status: AVAILABLE  Compressed: NO  Tag: TAG20160720T103250
        Piece Name: /backup/ARC/ORCL12/backupset/2016_07_20/o1_mf_nnndf_TAG20160720T103250_crxo62v6_.bkp
  List of Datafiles in backup set 74
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  12      Full 4744425    20-JUL-16 +DATA/ORCL12/DATAFILE/ts_backup_test.413.917692055

 

4. 테이블스페이스 삭제

SQL> drop tablespace TS_BACKUP_TEST including contents and datafiles;
Tablespace dropped.

 

5. TSPITR방법으로 복구를 시도합니다.

RMAN> recover tablespace TS_BACKUP_TEST until scn 4744425 auxiliary destination '/u01/auxiliary_dest';
Starting recover at 20-JUL-16
current log archived
using channel ORA_DISK_1
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='bnls'
initialization parameters used for automatic instance:
db_name=ORCL12
db_unique_name=bnls_pitr_ORCL12
compatible=12.1.0.2.0
db_block_size=8192
db_files=xxx
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=xxxM
processes=xxx
db_create_file_dest=/u01/auxiliary_dest
log_archive_dest_1='location=/u01/auxiliary_dest'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
.....
media recovery complete, elapsed time: 00:00:08
Finished recover at 20-JUL-16
database opened
...
executing Memory Script
sql statement: alter tablespace  TS_BACKUP_TEST read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/auxiliary_dest''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/auxiliary_dest''
Performing export of metadata...
   EXPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
   EXPDP> Starting "SYS"."TSPITR_EXP_bnls_wcCm":
...
   EXPDP> Datafiles required for transportable tablespace TS_BACKUP_TEST:
   EXPDP>   /u01/auxiliary_dest/ORCL12/datafile/o1_mf_ts_backu_crxo9ghp_.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_bnls_wcCm" successfully completed at Wed Jul 20 10:35:35 2016 elapsed 0 00:00:25
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of metadata...
   IMPDP>
WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
   IMPDP> Master table "SYS"."TSPITR_IMP_bnls_yvAd" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_bnls_yvAd":
...
   IMPDP> Job "SYS"."TSPITR_IMP_bnls_yvAd" successfully completed at Wed Jul 20 10:35:47 2016 elapsed 0 00:00:03
Import completed

...
sql statement: alter tablespace  TS_BACKUP_TEST read write
sql statement: alter tablespace  TS_BACKUP_TEST offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
...
auxiliary instance file tspitr_bnls_96302.dmp deleted
Finished recover at 20-JUL-16

좀 보시기 복잡하실거같은데 간단하게 말씀드리면

auxiliary destination으로 지정한 디렉토리에  

백업된 정보로 임시 데이터베이스 기동한후 -> expdp / impdp를 이용하여 테이블스페이스의 내용을 원래 DB에 복구 -> 임시 DB down 및 임시파일 삭제

의 순으로 진행됩니다.

 

이제 복구가 되었으니 조회를 해볼께요.

SQL> select tablespace_name from dba_tablespaces where tablespace_name ='TS_BACKUP_TEST';

TABLESPACE_NAME
------------------------------
TS_BACKUP_TEST

SQL> select segment_name from dba_segments where tablespace_name = 'TS_BACKUP_TEST';

SEGMENT_NAME
------------------------------
BACKUP_TEST_1

SQL> select * from system.BACKUP_TEST_1;
select * from system.BACKUP_TEST_1
*
ERROR at line 1:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13:
'/u01/auxiliary_dest/ORCL12/datafile/o1_mf_ts_backu_crxo9ghp_.dbf'

 

조회가 안되어 확인해보니 해당 테이블스페이스는 현재 offline상태네요.

테이블스페이스를 online으로 전환하고 조회해보니 정상조회됩니다.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name = 'TS_BACKUP_TEST';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TS_BACKUP_TEST                 OFFLINE

SQL> alter tablespace TS_BACKUP_TEST online;

Tablespace altered.

SQL> select * from system.BACKUP_TEST_1;

        NO NAME
---------- ----------
         1 AAA
         2 BBB

 

아주 쉽게 잘 처리되었습니다.

그러나 간과하지 말하야 할것은 전체 데이터의 사이즈입니다. 제가 테스트하는 환경은 말그대로 TEST여서 데이터가 거의 없습니다.

그랬기때문에 아무 디렉토리나 지정하여 처리하여도 아무 문제가 없었으나 복구해야할 데이터의 사이즈가 크다면 시간은 얼마나 걸릴지 장담할수 없습니다.

그리고 디스크도 준비가 되어있어야겠네요. 또한 복구시 백업된 아카이브로그도 적용하므로 적용해야할 로그가 많은 경우는 그만큼 시간이 늘어납니다.

 

그러나 무엇보다도 drop에 대한 권한을 잘 관리하는게 중요하다고 생각됩니다.