Categorias
Picture of Roberto Sobrinho
Roberto Sobrinho

22/07/2024

RMAN Recuperação Rápida de Tabelas Passo a Passo: Table Point In Time Recovery (PITR) 

Recentemente, fui acionado por um cliente preocupado com uma tabela que havia sido atualizada incorretamente dois dias atrás. A solução envolveu a recuperação da tabela para um estado de 50 horas atrás utilizando o RMAN através do recurso Table Point In Time Recovery. Este post detalha o procedimento, com exemplos práticos e considerações técnicas. A tabela em questão foi criada especificamente para a elaboração deste post.

É importante mencionar que essa funcionalidade está disponível a partir da versão 12c do Oracle Database.

Pré-requisitos

Área Temporária:

  • Crie uma área temporária para os dados restaurados.
  • Exemplo: /u01/app/oracle/recover.

Procedimento de Recuperação

1. Renomear a Tabela Atual:

Para evitar conflitos, renomeie a tabela atual antes de iniciar a recuperação ou user a opçao UNTIL TIME COM REMAP.

ALTER TABLE CR.PEDIDOS_FAKE RENAME TO PEDIDOS_FAKE_OLD;

    2. Configuração do Canal no RMAN

    • No meu caso, estou usando a ferramenta de backup Commvault. A configuração do canal no RMAN deve ser ajustada de acordo com sua ferramenta de backup.
    rman target /

configure channel device type sbt_tape PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so, BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2)" TRACE 0;

    2. Executar a Recuperação da Tabela:

    # UNTIL TIME SEM REMAP
RECOVER TABLE CR.PEDIDOS_FAKE UNTIL TIME "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD HH24:MI:SS')" AUXILIARY DESTINATION '/u01/app/oracle/recover';

# UNTIL TIME COM REMAP
RECOVER TABLE CR.PEDIDOS_FAKE UNTIL TIME "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD HH24:MI:SS')" AUXILIARY DESTINATION '/u01/app/oracle/recover' REMAP TABLE 'CR'.'PEDIDOS_FAKE':'PEDIDOS_FAKE_ANTES';

# UNTIL SCN
RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/app/oracle/recover'  
  REMAP TABLE 'CR'.'PEDIDOS_FAKE':'PEDIDOS_FAKE_ANTES';

    Vamos fazer UNTIL TIME SEM REMAP porque já renomeamos a tabela.

    [prod1.oracle@SERVER01] rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jul 16 10:30:34 2024
Version 19.21.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD1 (DBID=4211885143)

RMAN> configure channel device type sbt_tape PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so, BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2)" TRACE 0;

using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  "SBT_LIBRARY=/opt/commvault/Base/libobk.so, BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2)" TRACE 0;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  "SBT_LIBRARY=/opt/commvault/Base/libobk.so, BLKSIZE=1048576 ENV=(CV_mmsApiVsn=2)" TRACE 0;
new RMAN configuration parameters are successfully stored

RMAN> RECOVER TABLE CR.PEDIDOS_FAKE UNTIL TIME "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD HH24:MI:SS')" AUXILIARY DESTINATION '/u01/app/oracle/recover';

Starting recover at 16-JUL-24
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=414 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=454 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_SBT_TAPE_3
channel ORA_SBT_TAPE_3: SID=474 device type=SBT_TAPE
channel ORA_SBT_TAPE_3: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_SBT_TAPE_4
channel ORA_SBT_TAPE_4: SID=504 device type=SBT_TAPE
channel ORA_SBT_TAPE_4: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=530 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='eEAu'

initialization parameters used for automatic instance:
db_name=PROD1
db_unique_name=eEAu_pitr_PROD1
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=81920M
processes=200
db_create_file_dest=/u01/app/oracle/recover
log_archive_dest_1='location=/u01/app/oracle/recover'
#No auxiliary parameter file used


starting up automatic instance PROD1

Oracle instance started

Total System Global Area   85899344952 bytes

Fixed Size                    37248056 bytes
Variable Size              10200547328 bytes
Database Buffers           75430363136 bytes
Redo Buffers                 231186432 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD 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 16-JUL-24
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=302 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=309 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=316 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=323 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=330 device type=DISK

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-4211885143-20240715-09
channel ORA_AUX_SBT_TAPE_1: piece handle=c-4211885143-20240715-09 tag=TAG20240715T112304
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
output file name=/u01/app/oracle/recover/PROD1/controlfile/o1_mf_m9dxl9rs_.ctl
Finished restore at 16-JUL-24

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('2024-07-15 11:30:00', 'YYYY-MM-DD 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  2 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  6 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  2 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 2, 4, 3, 6;

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_temp_au_%u_.tmp in control file
renamed tempfile 2 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 16-JUL-24
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_DISK_1

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00006 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2894640_PROD1_0p2vtr0o_9241_1_1
channel ORA_AUX_SBT_TAPE_2: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00001 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00002 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00004 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: restoring datafile 00003 to /u01/app/oracle/recover/PROD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece 2894640_PROD1_0r2vtr33_9243_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2894640_PROD1_0p2vtr0o_9241_1_1 tag=TAG20240714T200127
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_SBT_TAPE_2: piece handle=2894640_PROD1_0r2vtr33_9243_1_1 tag=TAG20240714T200127
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:04:55
Finished restore at 16-JUL-24

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1174473418 file name=/u01/app/oracle/recover/PROD1/datafile/o1_mf_system_m9dxmoxs_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=1174473418 file name=/u01/app/oracle/recover/PROD1/datafile/o1_mf_system_m9dxmoxt_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1174473418 file name=/u01/app/oracle/recover/PROD1/datafile/o1_mf_undotbs1_m9dxmoxr_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1174473418 file name=/u01/app/oracle/recover/PROD1/datafile/o1_mf_sysaux_m9dxmoxq_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=1174473418 file name=/u01/app/oracle/recover/PROD1/datafile/o1_mf_sysaux_m9dxlrrg_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  6 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  2 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  6 online

Starting recover at 16-JUL-24
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_DISK_1

Executing: alter database datafile 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18 offline
starting media recovery

channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15484
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15485
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2894640_PROD1_0v2vtr84_9247_1_1
channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=15486
channel ORA_AUX_SBT_TAPE_2: reading from backup piece 2894783_PROD1_112vtrm0_9249_1_1
channel ORA_AUX_SBT_TAPE_3: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=15487
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=15488
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=15489
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15492
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15493
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15494
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2895607_PROD1_162vu3o5_9254_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=2894783_PROD1_112vtrm0_9249_1_1 tag=TAG20240714T201248
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:07
archived log file name=/u01/app/oracle/recover/1_15486_1161538212.dbf thread=1 sequence=15486
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_SBT_TAPE_2: piece handle=2900031_PROD1_2a2vvl5u_9290_1_1 tag=TAG20240715T123406
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:03
channel ORA_AUX_SBT_TAPE_4: piece handle=2899680_PROD1_272vvh0h_9287_1_1 tag=TAG20240715T112257
channel ORA_AUX_SBT_TAPE_4: restored backup piece 1
channel ORA_AUX_SBT_TAPE_4: restore complete, elapsed time: 00:00:04
archived log file name=/u01/app/oracle/recover/1_15547_1161538212.dbf thread=1 sequence=15547
archived log file name=/u01/app/oracle/recover/1_15548_1161538212.dbf thread=1 sequence=15548
archived log file name=/u01/app/oracle/recover/1_15551_1161538212.dbf thread=1 sequence=15551
archived log file name=/u01/app/oracle/recover/1_15552_1161538212.dbf thread=1 sequence=15552
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-JUL-24

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/app/oracle/recover/PROD1/controlfile/o1_mf_m9dxl9rs_.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   85899344952 bytes

Fixed Size                    37248056 bytes
Variable Size              10200547328 bytes
Database Buffers           75430363136 bytes
Redo Buffers                 231186432 bytes

sql statement: alter system set  control_files =   ''/u01/app/oracle/recover/PROD1/controlfile/o1_mf_m9dxl9rs_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area   85899344952 bytes

Fixed Size                    37248056 bytes
Variable Size              10200547328 bytes
Database Buffers           75430363136 bytes
Redo Buffers                 231186432 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  16 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  16;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 16-JUL-24
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=8 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=16 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_SBT_TAPE_3
channel ORA_AUX_SBT_TAPE_3: SID=23 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_3: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_SBT_TAPE_4
channel ORA_AUX_SBT_TAPE_4: SID=30 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_4: CommVault Systems for Oracle: Version 11.0.0(BUILD80)
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=37 device type=DISK
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00016 to /u01/app/oracle/recover/EEAU_PITR_PROD1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2894640_PROD1_0r2vtr33_9243_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2894640_PROD1_0r2vtr33_9243_1_1 tag=TAG20240714T200127
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:25
Finished restore at 16-JUL-24

datafile 16 switched to datafile copy
input datafile copy RECID=12 STAMP=1174473676 file name=/u01/app/oracle/recover/EEAU_PITR_PROD1/datafile/o1_mf_users_m9dy1rvt_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "TO_DATE('2024-07-15 11:30:00', 'YYYY-MM-DD HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile  16 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  16 online

Starting recover at 16-JUL-24
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
using channel ORA_AUX_SBT_TAPE_3
using channel ORA_AUX_SBT_TAPE_4
using channel ORA_AUX_DISK_1

Executing: alter database datafile 5, 7, 8, 9, 10, 11, 12, 13, 14, 15, 17, 18 offline
starting media recovery

channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15484
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15485
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2894640_PROD1_0v2vtr84_9247_1_1
channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=15486
channel ORA_AUX_SBT_TAPE_2: reading from backup piece 2894783_PROD1_112vtrm0_9249_1_1
channel ORA_AUX_SBT_TAPE_3: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=15487
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=15488
channel ORA_AUX_SBT_TAPE_3: restoring archived log
archived log thread=1 sequence=15489
channel ORA_AUX_SBT_TAPE_3: reading from backup piece 2895181_PROD1_132vtvln_9251_1_1
channel ORA_AUX_SBT_TAPE_4: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_4: restoring archived log
archived log thread=1 sequence=15490
channel ORA_AUX_SBT_TAPE_4: restoring archived log
archived log thread=1 sequence=15491
channel ORA_AUX_SBT_TAPE_4: reading from backup piece 2895181_PROD1_142vtvln_9252_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2894640_PROD1_0v2vtr84_9247_1_1 tag=TAG20240714T200524
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/recover/1_15484_1161538212.dbf thread=1 sequence=15484
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/recover/1_15484_1161538212.dbf RECID=15647 STAMP=1174473682
archived log file name=/u01/app/oracle/recover/1_15485_1161538212.dbf thread=1 sequence=15485
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/recover/1_15485_1161538212.dbf RECID=15646 STAMP=1174473682
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15492
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15493
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15494
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2895607_PROD1_162vu3o5_9254_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2895607_PROD1_162vu3o5_9254_1_1 tag=TAG20240714T223029
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15495
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15496
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2895607_PROD1_172vu3o5_9255_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2895607_PROD1_172vu3o5_9255_1_1 tag=TAG20240714T223029
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15497
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15498
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15499
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2896008_PROD1_192vu7sl_9257_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2896008_PROD1_192vu7sl_9257_1_1 tag=TAG20240714T234109
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15500
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15501
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2896008_PROD1_1a2vu7sl_9258_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2896008_PROD1_1a2vu7sl_9258_1_1 tag=TAG20240714T234109
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15502
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15503
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2896390_PROD1_1c2vubua_9260_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2896390_PROD1_1c2vubua_9260_1_1 tag=TAG20240715T005018
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15504
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15505
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15506
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2896390_PROD1_1d2vubua_9261_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2896390_PROD1_1d2vubua_9261_1_1 tag=TAG20240715T005018
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15507
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15508
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15509
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2896830_PROD1_1f2vug2i_9263_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=2894783_PROD1_112vtrm0_9249_1_1 tag=TAG20240714T201248
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/recover/1_15486_1161538212.dbf thread=1 sequence=15486
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/recover/1_15486_1161538212.dbf RECID=15665 STAMP=1174473715
channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=15510
channel ORA_AUX_SBT_TAPE_2: restoring archived log
archived log thread=1 sequence=15511
channel ORA_AUX_SBT_TAPE_2: reading from backup piece 2896830_PROD1_1g2vug2i_9264_1_1
channel ORA_AUX_SBT_TAPE_4: piece handle=2895181_PROD1_142vtvln_9252_1_1 tag=TAG20240714T212055
channel ORA_AUX_SBT_TAPE_4: restored backup piece 1
channel ORA_AUX_SBT_TAPE_4: restore complete, elapsed time: 00:00:36
channel ORA_AUX_SBT_TAPE_4: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_4: restoring archived log
archived log thread=1 sequence=15512
channel ORA_AUX_SBT_TAPE_4: restoring archived log
archived log thread=1 sequence=15513
channel ORA_AUX_SBT_TAPE_4: restoring archived log
archived log thread=1 sequence=15514
channel ORA_AUX_SBT_TAPE_4: reading from backup piece 2897291_PROD1_1i2vuk6t_9266_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=2896830_PROD1_1f2vug2i_9263_1_1 tag=TAG20240715T020050
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15515
channel ORA_AUX_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=15516
channel ORA_AUX_SBT_TAPE_1: reading from backup piece 2897291_PROD1_1j2vuk6t_9267_1_1
channel ORA_AUX_SBT_TAPE_3: piece handle=2895181_PROD1_132vtvln_9251_1_1 tag=TAG20240714T212055
channel ORA_AUX_SBT_TAPE_3: restored backup piece 1
channel ORA_AUX_SBT_TAPE_3: restore complete, elapsed time: 00:00:37

Finished recover at 16-JUL-24

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/recover''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/recover''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/recover''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/recover''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_eEAu_wvEx":
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> . . exported "CR"."PEDIDOS_FAKE"                         11.85 MB  103440 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_eEAu_wvEx" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_eEAu_wvEx is:
   EXPDP>   /u01/app/oracle/recover/tspitr_eEAu_73830.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_eEAu_wvEx" successfully completed at Tue Jul 16 10:43:58 2024 elapsed 0 00:00:21
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> Master table "SYS"."TSPITR_IMP_eEAu_sogr" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_eEAu_sogr":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "CR"."PEDIDOS_FAKE"                         11.85 MB  103440 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_eEAu_sogr" successfully completed at Tue Jul 16 10:44:44 2024 elapsed 0 00:00:15
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_temp_m9dxyd8w_.tmp deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_temp_au_m9dxyd72_.tmp deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_2_m9dy5to3_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_1_m9dy5tns_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_17_m9dy5v2k_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_16_m9dy5v2k_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_15_m9dy5v2k_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_14_m9dy5v2j_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_13_m9dy5tpd_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_12_m9dy5toy_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/onlinelog/o1_mf_11_m9dy5tok_.log deleted
auxiliary instance file /u01/app/oracle/recover/EEAU_PITR_PROD1/datafile/o1_mf_users_m9dy1rvt_.dbf deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_sysaux_m9dxlrrg_.dbf deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_sysaux_m9dxmoxq_.dbf deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_undotbs1_m9dxmoxr_.dbf deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_system_m9dxmoxt_.dbf deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/datafile/o1_mf_system_m9dxmoxs_.dbf deleted
auxiliary instance file /u01/app/oracle/recover/PROD1/controlfile/o1_mf_m9dxl9rs_.ctl deleted
auxiliary instance file tspitr_eEAu_73830.dmp deleted
Finished recover at 16-JUL-24

RMAN>

    3. Validar a Recupeção da Tabela

    Considerações

    • Versão do Oracle: Esta funcionalidade está disponível a partir da versão 12c.
    • Configurações Adicionais: Certifique-se de ter backups recentes e de verificar as configurações do seu ambiente para evitar problemas durante a recuperação.
    • Valide o Espaço da Instância Auxiliar: Garanta que há espaço suficiente na área temporária onde a instância auxiliar será criada para a recuperação da tabela

    Detalhamento do Processo

    O comando RECOVER TABLE realiza as seguintes ações:

    1. Cria uma instância auxiliar:
      A partir do backup físico, uma instância auxiliar é criada para isolar o ambiente de recuperação do ambiente de produção.
    2. Exporta a tabela via Data Pump:
      A tabela ou conjunto de tabelas é exportado usando a ferramenta Data Pump, garantindo que os dados recuperados estejam consistentes e atualizados.
    3. Importa a(s) tabela(s) na base de produção:
      Após a exportação, os dados são importados de volta para a base target, conforme especificado no comando de recuperação.
    4. Exclui a instância auxiliar:
      A instância auxiliar e os arquivos gerados durante o processo de recuperação são removidos para liberar espaço.

    Este método de recuperação é extremamente útil para situações onde apenas uma tabela específica precisa ser restaurada, evitando a necessidade de restaurar todo o banco de dados ou utilizar backups lógicos que podem não estar atualizados. A configuração correta dos canais e a criação de uma área temporária adequada são cruciais para o sucesso do procedimento. Este processo está disponível a partir da versão 12c do Oracle.

    🏃‍♂️🪢 #20240722 #GuinaNãoTinhaDó #TrânsitoNaBelmira #GuinaNoPortão #SeReagirBúmViraPó 🪢🏃‍♂️


    Compartilhe

    Facebook
    Twitter
    LinkedIn
    WhatsApp
    Email
    Print

    Pesquisar

    Roberto Sobrinho

    Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.

    Oracle ACE Associate

    2025

    Specialist

    Exadata Database Machine X9M

    Professional

    Oracle Database Administration

    Professional

    Oracle Database 19c: RAC, ASM, & Grid Infra Administrator

    Professional

    Oracle Autonomous Database Cloud

    Professional

    Oracle Cloud Database Migration and Integration

    Professional

    Oracle Database PL/SQL Developer

    Associate

    Oracle Cloud Infrastructure Architect

    Associate

    Oracle Cloud Infrastructure Foundations

    Categorias

    Categorias

    Tags

    Tags
    23ai auditoria backup cdb Configuração crontab dba ddl dml drop drop database ErroORA error erro_patch fbi find full table scan index lentidão linux Listener locate log ojvm OPW ORA ORA-01017 oracle orapwd patch pdb pl/sql RAC RAC 19c recuperação refresh rman senha senha; password; alter user; alter profile; PASSWORD_ROLLOVER_TIME; shell source sql SYSDBA tuning view

    Por que o Oracle ignora o INDEX e faz Full Table Scan?

    Como encontrar a última alteração de senha de um USER no Oracle

    AWR e Colored SQL: capture consultas fora do Top SQL

    INSERT de grandes volumes de dados no Oracle: BULK COLLECT e FORALL na prática

    RMAN Recuperação Rápida de Tabelas Passo a Passo: Table Point In Time Recovery (PITR) 

    11 97160-0775
    contato@dbasobrinho.com.br
    LinkedIn
    Política de Privacidade
    Termos de Uso

    Desenvolvido por: Hallan Design

    Todos os direitos reservados © 2025 Roberto Sobrinho