Oracle 12c - Table-Point-In-Time Recovery (TPITR) com o RMAN

Por Eduardo Legatti
Postado em Outubro 2015

O banco de dados Oracle possui várias técnicas para recuperação de dados. Dentre algumas existentes, podemos citar a técnica de recuperação pontual de dados já bem conhecida entre os DBAs Oracle chamada de Tablespace Point-in-Time Recovery (TSPITR) disponível desde a versão 8 do Oracle. Uma outra técnica chamada Database Point-in-Time Recovery (DBPITR) também é bem conhecida entre os DBAs. Basicamente o TSPITR (Recuperação Pontual de Tablespace) é uma funcionalidade que fornece ao DBA a opção de recuperar um conjunto de dados em um ponto específico no tempo sem afetar o restante do banco de dados. Já o DBPITR possibilita um banco de dados inteiro "voltar no tempo" da mesma forma que o recurso Flashback Database (10g) possibilita. Neste artigo irei abordar um novo recurso que veio no RMAN do Oracle 12c chamado de Table-Point-In-Time Recovery. Daqui pra frente irei fazer referência a ele apenas como TPITR. Da mesma forma que no TSPITR, iremos notar que o TPITR também se utilizará não só de uma instância auxiliar, como também do utilitário Datapump (expdp/impdp) no processo de restore/recover.  

[oracle@linux1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 1 15:36:29 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from scott.t1;
 
      ID
--------
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
 
10 linhas selecionadas.

SQL> select sysdate from dual;
 
SYSDATE
-------------------
01/06/2014 15:36:55

 

Podemos ver pelo resultado acima, que a tabela T1 de propriedade do usuário SCOTT contém 10 linhas e que a mesma existia às 15:36:55 do dia 01/06/2014. Após realização de um backup do banco de dados com o RMAN, irei dropar a tabela T1 conforme demonstrado abaixo.

SQL> drop table scott.t1 purge;
 
Tabela eliminada.
 
SQL> select * from scott.t1;
select * from scott.t1
                    *
ERRO na linha 1:
ORA-00942: a tabela ou view não existe

 

Pelo fato de eu ter utilizado a opção purge do comando drop table, a mesma não foi para a lixeira (recycle bin). Portanto, não será possível recuperá-la através do flashback drop. Bom, o objetivo então será recuperar a tabela diretamente do backup do banco de dados que fiz previamente através do RMAN. Para me certificar, segue abaixo a prova de que o backup foi realizado.

RMAN> list backup;
 
List of Backup Sets
===================
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    404.21M    DISK        00:01:59     01/06/2014
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20140508T153228
        Piece Name: 
/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
  List of Datafiles in backup set 7
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  1       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/system01.dbf
  2       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/sysaux01.dbf
  3       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/undotbs01.dbf
  4       Full 365502     01/06/2014 /u01/app/oracle/oradata/BD01/users01.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    9.61M      DISK        00:00:03     01/06/2014
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20140508T153228
        Piece Name: 
/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_ncnnf_TAG20140508T153228_9pqmkyfx_.bkp
  Control File Included: Ckp SCN: 365548       Ckp time: 01/06/2014
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    9.64M      DISK        00:00:02     01/06/2014
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20140508T153440
        Piece Name: 
/u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp
  SPFILE Included: Modification time: 01/06/2014
  SPFILE db_unique_name: BD01
  Control File Included: Ckp SCN: 365556       Ckp time: 01/06/2014


Assim como nos métodos DBPITR e TSPITR, para que o processo de recuperação utilizando a técnica TPITR seja possível, será necessário criar um diretório no sistema de arquivos para uso da instância auxiliar que será criada no processo de recover.

[oracle@linux1 ~]$ mkdir /u01/aux_dest

Como eu sei que a tabela existia exatamente às 15:36:35, irei utilizar esse mesmo horário para recuperar a tabela T1. Segue abaixo o comandos necessário para realizar essa operação.

[oracle@linux1 ~]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jun 1 15:42:17 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BD01 (DBID=3099510927)
 
RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest';
 
Starting recover at 01/06/2014
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
 
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
 
Creating automatic instance, with SID='hgkF'
 
initialization parameters used for automatic instance:
db_name=BD01
db_unique_name=hgkF_pitr_BD01
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/aux_dest
log_archive_dest_1='location=/u01/aux_dest'
#No auxiliary parameter file used
 
 
starting up automatic instance BD01
 
Oracle instance started
 
Total System Global Area    1068937216 bytes
 
Fixed Size                     2296576 bytes
Variable Size                281019648 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5480448 bytes
Automatic instance created
 
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
 
executing command: SET until clause
 
Starting restore at 01/06/2014
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=83 device type=DISK
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece 
/u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp
channel ORA_AUX_DISK_1: piece handle=
/u01/app/oracle/fast_recovery_area/BD01/autobackup/2014_06_01/o1_mf_s_847035280_9pqml24o_.bkp 
tag=TAG20140508T153440
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl
Finished restore at 01/06/2014
 
sql statement: alter database mount clone database
 
sql statement: alter system archive log current
 
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2;
switch clone datafile all;
}
executing Memory Script
 
executing command: SET until clause
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
executing command: SET NEWNAME
 
renamed tempfile 1 to /u01/aux_dest/BD01/datafile/o1_mf_temp_%u_.tmp in control file
 
Starting restore at 01/06/2014
using channel ORA_AUX_DISK_1
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux_dest/BD01/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux_dest/BD01/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/aux_dest/BD01/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece 
/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
channel ORA_AUX_DISK_1: piece handle=
/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp 
tag=TAG20140508T153228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:39
Finished restore at 01/06/2014
 
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=847035974 file name=/u01/aux_dest/BD01/datafile/o1_mf_system_9pqn2mfs_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=847035975 file name=/u01/aux_dest/BD01/datafile/o1_mf_undotbs1_9pqn2mmr_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=847035976 file name=/u01/aux_dest/BD01/datafile/o1_mf_sysaux_9pqn2mld_.dbf
 
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
 
executing command: SET until clause
 
sql statement: alter database datafile  1 online
 
sql statement: alter database datafile  3 online
 
sql statement: alter database datafile  2 online
 
Starting recover at 01/06/2014
using channel ORA_AUX_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 26 is already on disk as file 
/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc 
thread=1 sequence=26
media recovery complete, elapsed time: 00:00:02
Finished recover at 01/06/2014
 
sql statement: alter database open read only
 
contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
 
sql statement: create spfile from memory
 
database closed
database dismounted
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1068937216 bytes
 
Fixed Size                     2296576 bytes
Variable Size                285213952 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5480448 bytes
 
sql statement: alter system set  control_files =   ''/u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl'' 
comment= ''RMAN set'' scope=spfile
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area    1068937216 bytes
 
Fixed Size                     2296576 bytes
Variable Size                285213952 bytes
Database Buffers             775946240 bytes
Redo Buffers                   5480448 bytes
 
sql statement: alter database mount clone database
 
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  4;
switch clone datafile all;
}
executing Memory Script
 
executing command: SET until clause
 
executing command: SET NEWNAME
 
Starting restore at 01/06/2014
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=11 device type=DISK
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece 
/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp
channel ORA_AUX_DISK_1: piece handle=
/u01/app/oracle/fast_recovery_area/BD01/backupset/2014_06_01/o1_mf_nnndf_TAG20140508T153228_9pqmfxl6_.bkp 
tag=TAG20140508T153228
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01/06/2014
 
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=847036181 file name=/u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_9pqng2l9_.dbf
 
contents of Memory Script:
{
# set requested point in time
set until  time "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  4 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
 
executing command: SET until clause
 
sql statement: alter database datafile  4 online
 
Starting recover at 01/06/2014
using channel ORA_AUX_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 26 is already on disk as file 
/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/BD01/archivelog/2014_06_01/o1_mf_1_26_9pqmqrk0_.arc 
thread=1 sequence=26
media recovery complete, elapsed time: 00:00:01
Finished recover at 01/06/2014
 
database opened
 
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/aux_dest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/aux_dest''";
}
executing Memory Script
 
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux_dest''
 
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux_dest''
 
Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_hgkF_glDz":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SCOTT"."T1"                                5.093 KB      10 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_hgkF_glDz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_hgkF_glDz is:
   EXPDP>   /u01/aux_dest/tspitr_hgkF_20324.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_hgkF_glDz" successfully completed at Sun Jun 1 15:52:23 2014 elapsed 0 00:00:45
Export completed
 
 
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
 
Oracle instance shut down
 
Performing import of tables...
   IMPDP> Tabela-mestre "SYS"."TSPITR_IMP_hgkF_znug" carregada/descarregada com sucesso
   IMPDP> Iniciando "SYS"."TSPITR_IMP_hgkF_znug":
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . importou "SCOTT"."T1"                                5.093 KB      10 linhas
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> O job "SYS"."TSPITR_IMP_hgkF_znug" 
   foi concluido com sucesso em Dom Jun 1 15:55:09 2014 elapsed 0 00:01:49
Import completed
 
 
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_temp_9pqn8x8x_.tmp deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_3_9pqngcjb_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_2_9pqngbsx_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/onlinelog/o1_mf_1_9pqng9r7_.log deleted
auxiliary instance file /u01/aux_dest/HGKF_PITR_BD01/datafile/o1_mf_users_9pqng2l9_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_sysaux_9pqn2mld_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_undotbs1_9pqn2mmr_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/datafile/o1_mf_system_9pqn2mfs_.dbf deleted
auxiliary instance file /u01/aux_dest/BD01/controlfile/o1_mf_9pqn27bq_.ctl deleted
auxiliary instance file tspitr_hgkF_20324.dmp deleted
Finished recover at 01/06/2014


Pronto. Após a execução do processo de restore/recover, poderemos ver abaixo que a tabela foi recuperada com sucesso.

[oracle@linux1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 1 16:24:10 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
 
SQL> select * from scott.t1;
 
      ID
--------
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
 
10 linhas selecionadas.
  

Bom, da mesma forma que podemos restaurar uma tabela com o nome original, podemos também restaurá-la com um nome diferente. Para isso basta apenas utilizarmos a opção REMAP TABLE. Segue abaixo alguns trechos do processo de recuperação na qual eu irei restaurar a tabela com T1 com o nome de T1_DROP.

RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest'
4> remap table SCOTT.T1:T1_DROP; 
 
Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_isFB_xdwa":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SCOTT"."T1"                               5.093 KB      10 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_isFB_xdwa" successfully loaded/unloaded
   EXPDP> *****************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_isFB_xdwa is:
   EXPDP>   /u01/aux_dest/tspitr_isFB_93535.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_isFB_xdwa" successfully completed
Export completed
 
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
 
Oracle instance shut down
 
Performing import of tables...
   IMPDP> Tabela-mestre "SYS"."TSPITR_IMP_isFB_EpCe" carregada/descarregada com sucesso
   IMPDP> Iniciando "SYS"."TSPITR_IMP_isFB_EpCe":
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . importou "SCOTT"."T1_DROP"                        5.093 KB      10 linhas
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processando o tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> O job "SYS"."TSPITR_IMP_isFB_EpCe" foi concluido com sucesso
Import completed
  

Pronto. Após a finalização do processo de recover podemos ver abaixo a tabela T1_DROP.

RMAN> select owner,table_name from dba_tables where owner = 'SCOTT';
using target database control file instead of recovery catalog
 
OWNER      TABLE_NAME
---------- ---------------
SCOTT      T1
SCOTT      T1_DROP

 

Para finalizar, existe uma outra opção que podemos utilizar no processo TPITR. Essa opção consiste em recuperar a tabela, mas não importá-la de volta para o banco de dados. Neste caso, apenas um dump contendo a tabela será gerado. Caso queiramos importá-la posteriormente poderemos fazer isso manualmente. Segue abaixo as opções que devemos utilizar para realizar essa operação.

RMAN> recover table SCOTT.T1
2> until time = "to_date('01/06/2014 15:36:55','DD/MM/YYYY HH24:MI:SS')"
3> auxiliary destination '/u01/aux_dest'
4> datapump destination '/tmp'
5> dump file 't1.dmp'
6> notableimport; 
 
Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_pBbn_otbe":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "SCOTT"."T1"                               5.093 KB      10 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_pBbn_otbe" successfully loaded/unloaded
   EXPDP> *****************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_pBbn_otbe is:
   EXPDP>   /tmp/t1.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_pBbn_otbe" successfully completed
Export completed
 
Not performing table import after point-in-time recovery


Ao final, poderemos ver que o dump foi gerado no diretório especificado.

[oracle@linux1 tmp]$ ls -l /tmp/*.dmp

-rw-r----- 1 oracle oinstall 131072 Mai  8 17:03 /tmp/t1.dmp
 


Eduardo Legatti é Analista de Sistemas e Administrador de banco de dados. É pós graduado em Gerência da Tecnologia da Informação, possui as certificações OCA 9i - OCP 9i/10g/11g – OCE SQL Expert, e vem trabalhando como DBA Oracle desde a versão 8.0.5. Se interessa particularmente em planejar estratégias de backup/recovery, performance tuning e projetos de bancos de dados (modelagem física e lógica) atuando como consultor. Como Oracle ACE, ele tem o hábito de disseminar seu conhecimento através de artigos, grupos de discussão (Oracle OTN Forums) e dedica-se a compartilhar informações de forma a motivar novos DBAs. Frequentemente posta artigos em seu Oracle blog http://eduardolegatti.blogspot.com.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.