Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


domingo, 1 de junho de 2014

Abordando o Table-Point-In-Time Recovery (TPITR) na prática com o RMAN do Oracle 12c

Por Eduardo Legatti

Olá,  

No artigo de Março/2010 eu abordei um pouco da 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 do Oracle 8 e também uma outra técnica chamada Database Point-in-Time Recovery (DBPITR). Como já explicado anteriormente naquele artigo, 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. Vamos então a prática.
 
[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

Google+

4 comentários:

Antonio Marcos de Almeida Silva disse...

Bom dia Eduardo,


Outro dia já entrei em seu blog e verifiquei alguns procedimento,

Hoje estou com um problema, tenho na minha maquina o Oracle 10G XE com o conjunto de caracteres WE8MSWIN1252

Estou tentando importar no Oracle 11G XE o conjunto de caracteres é AL32UTF8

Mas não é compatível.

Gostaria verificar a possibilidade de alterar o Conjunto de Caracteres no 11G XE para WE8MSWIN1252

Já verifiquei em alguns sites mas não tive sucesso.

Eduardo Legatti disse...

Olá Antonio,

Você já tentou executar o comando ALTER DATABASE no Oracle 11g XE para alterar o CHARACTER SET? Se não me engano o banco de dados ter que estar aberto no modo restrito.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESS=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESS=0;
SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Abraços

Legatti

Antonio Marcos de Almeida Silva disse...

Bom dia quando fui dar um ALTER SYSTEM.

deu um erro de opção ivalida para SYSTEM

Eduardo Legatti disse...

Olá Antônio,

Me parece que os procedimentos são esses mesmos. Se você não conseguir, já pensou em reinstalar o Oracle XE com o novo CHARACTER SET?

Veja se esse artigo pode te ajudar.

Abraços

Legatti

Postagens populares