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 temp
[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/

Rman 테이블 복구

DB CAFE

rman 테이블 복구 (12c NF,Enterprise Edition)[편집]