Rman 테이블 복구
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
rman 테이블 복구 (12c NF,Enterprise Edition)[편집]
- 12c 전까지 RMAN을 사용하여 테이블 하나를 복구하기 위해 DBA가 직접 수동 복원 및 복구
- 12c 부터는 RMAN table recovery 기능이 추가
- DATAPUMP DESTINATION, DUMP FILE, REMAP TABLE, NOTABLEIMPORT의 구문이 사용되며, UNTIL TIME, UNTIL SCN, UNTIL SEQUENCE 구문을 통해 복구
※ 테스트
(1) RMAN Full 백업
[oracle@OEL5u10 ~]$ rman
RMAN> connect target "sys/oracle as sysbackup"
RMAN> backup database plus archivelog;
RMAN> exit
(2) 테스트 테이블 생성
[oracle@OEL5u10 ~]$ sqlplus scott/tiger@product
SQL> create table tb1 (no number, name varchar2(20));
Table created.
SQL> insert into tb1 values (1,'TEST1');
1 row created.
SQL> insert into tb1 values (2,'TEST2');
1 row created.
SQL> commit;
Commit complete.
SQL> select no, name from tb1;
NO NAME
---------- --------------------
1 TEST1
2 TEST2
SQL> conn sys/oracle@product as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2058610
SQL> alter session set nls_date_format = 'YYYY-MM-DD:HH24:MI:SS';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-01-27:20:11:17
SQL> alter system switch logfile;
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@OEL5u10 ~]$ rman
RMAN> connect target "sys/oracle as sysbackup"
RMAN> backup pluggable database product plus archivelog;
RMAN> exit
(3) 테스트 테이블 삭제
[oracle@OEL5u10 ~]$ sqlplus scott/tiger@product
SQL> drop table tb1 purge;
Table dropped.
SQL> select * from tb1;
select * from tb1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit
[oracle@OEL5u10 ~]$ mkdir tmep
[oracle@OEL5u10 ~]$ cd temp
(4) 복구
[oracle@OEL5u10 ~]$ rman
RMAN> connect target "sys/oracle as sysbackup"
RMAN> set auxiliary instance parameter file to '/home/oracle/temp/initaux.ora';
RMAN> recover table scott.tb1 of pluggable database product
until scn 2058610 ## until time "to_date('2014-01-27:20:11:17','YYYY-MM-DD:HH24:MI:SS')"
auxiliary destination '/home/oracle/temp'
datapump destination '/home/oracle/temp' dump file 'tb1_dump.dmp';
RMAN> exit
[oracle@OEL5u10 ~]$ sqlplus scott/tiger@product
SQL> select * from tb1;
NO NAME
---------- --------------------
1 TEST1
2 TEST2
보다 상세한 테스트 https://www.thegeekdiary.com/oracle-database-12c-new-feature-rman-recover-table/