dba-tech

RMAN (recovery table)

1-
backup realizados:

SCOTT@orcl12> select * from emp
  2  ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

2- Drop table

SCOTT@orcl12> drop table emp;

Table dropped.

Elapsed: 00:00:00.04

3- Recovery table

oel-2:orcl12:/home/oracle12/rman/$ cat rec_table.rmn

RECOVER TABLE SCOTT.EMP

#until time "to_date('07/27/2013 05:00','mm-dd-yyyy hh24:mi')"

until sequence 843

 AUXILIARY DESTINATION '/tmp/oracle/recover'

 DATAPUMP DESTINATION '/tmp/recover/dumpfiles'

 REMAP TABLE 'SCOTT'.'EMP':'EMP_RECOVERED';

oel-2:orcl12:/home/oracle12/rman/$ rman target / @rec_table.rmn

.....

Oracle instance shut down

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_qcwj_ddfx" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_qcwj_ddfx":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "SCOTT"."EMP_RECOVERED"                     8.757 KB      14 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_qcwj_ddfx" successfully completed at Sun Jun 15 10:30:23 2014 elapsed 0 00:01:18

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_temp_9svc2qoo_.tmp deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_9_9svc556f_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_8_9svc53pd_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_7_9svc50cg_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_6_9svc4w0k_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_5_9svc4s35_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_4_9svc4qo7_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/datafile/o1_mf_users_9svc4cpo_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_sysaux_9svbxqsq_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_undotbs1_9svbxqt2_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_system_9svbxqt0_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/controlfile/o1_mf_9svbxbpc_.ctl deleted

auxiliary instance file tspitr_qcwj_53892.dmp deleted

Finished recover at 15-JUN-14

Recovery Manager complete.

SCOTT@orcl12> select * from EMP_RECOVERED 

  2  ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

FIN...

Obsevaciones:

- Se requiere al menos 30% de espacio del backup para generar la instancia auxiliar

- se requiere al menos el mismo SGA de la instancia actual.