Oracle Recover Dropped Table - RMAN

Olá, nesse artigo gostaria de apresentar uma funcionalidade do RMAN pouco falada, mas muito útil: Recover Table from RMAN.

Links úteis

Recovering Tables and Table Partitions

Prerequisites for Recovering Tables and Table Partitions from RMAN Backups

Ao longo dos anos atuando como DBA em diferentes empresas e industrias, fui confrontado por inúmeras situações de total desespero por parte de alguns colegas.

Me refiro ao famoso drop de tabela em produção de forma “acidental”¹⁾.

A partir da versão 12C (12.1) do Oracle Database, foi introduzida a feature ²⁾ que permite restaurar/recuperar um ou mais objetos específicos no banco de dados através do backup via RMAN sem a necessidade de realizar o restore completo da base de dados.

Segue abaixo a sintaxe básica para realizar essa operação:

rman target <user>/<pwd>@<tnsnames_entry> catalog <user>/<pwd>@<tnsnames_entry>

run{
  recover table CSTMMART.SUBS_M2M_30JUN2023,CSTMMART.DATAMART_LOYALTY_30JUN2023
  until time "to_date('15/01/2024 20:00:00','dd/mm/yyyy hh24:mi:ss')"
  auxiliary destination '/oradata/LSL/'
  datapump destination '/oradata/LSL/export'
  dump file 'CSTMMART_SUBS_M2M_30JUN2023_DATAMART_LOYALTY_30JUN2023.dmp'
  notableimport;
}

A operação realizada pelo RMAN pode ser resumida da seguinte forma:

Conexão via RMAN na base de dados atual de produção (target).
Conexão ao catálogo do RMAN ⇒ (Não é mandatório ter um catálogo).
RMAN irá então criar uma instância auxiliar.
Irá executar um duplicate através do backup realizando skip das tablespaces e datafiles que não têm relação direta com as tabelas que estão sendo restauradas.
Realizará o export das tabelas a partir dessa base clonada via duplicate.
E então irá realizar o import das tabelas restauradas a partir da base clone na base atual de produção (target).

Quero deixar apenas uma observação com relação a sintaxe utilizada (notableimport):

Ao utilizar o comando notableimport, ao final do processo o import das tabelas não é realizado.
Nesse caso apenas o arquivo de dump é gerado através do expdp executado automaticamente pelo RMAN.
Não deixei o processo realizar o import de forma automática, apenas para manter um maior controle da operação e o fiz de forma manual ao final da operação.

Abaixo eu deixo o log do output da operação realizada, com o intuíto de servir como um guia de todas as etapas realizadas de forma automática pelo RMAN:

-bash-5.1$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jan 25 16:43:23 2024

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

RMAN> connect target <user>/<pwd>@<tnsnames_entry>
connect catalog <user>/<pwd>@<tnsnames_entry>
connected to target database: DBAHERO (DBID=2412941060)

RMAN>

connected to recovery catalog database


recovery catalog schema release 19.20.00.00. is newer than RMAN release

RMAN>
RMAN>
RMAN>

RMAN>

RMAN> run{
2>
3> recover table CSTMMART.SUBS_M2M_30JUN2023,CSTMMART.DATAMART_LOYALTY_30JUN2023
  until time "to_date('15/01/2024 20:00:00','dd/mm/yyyy hh24:mi:ss')"
  auxiliary destination '/oradata/LSL/'
  datapump destination '/oradata/LSL/export'
  dump file 'CSTMMART_SUBS_M2M_30JUN2023_DATAMART_LOYALTY_30JUN2023.dmp'
  notableimport;
4> }
5>   6> 7> 8> 9>
Starting recover at 25-JAN-24

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=540 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=72 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=545 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=836 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=83 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=136 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=244 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=426 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: NMDA Oracle v9.1.1.4
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=292 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: NMDA Oracle v9.1.1.4

Creating automatic instance, with SID='abEA'

initialization parameters used for automatic instance:
db_name=DBAHERO
db_unique_name=abEA_pitr_DBAHERO
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/oracle/app/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/oradata/LSL/
log_archive_dest_1='location=/oradata/LSL/'
#No auxiliary parameter file used


starting up automatic instance DBAHERO

Oracle instance started

Total System Global Area    2684354560 bytes

Fixed Size                     3704072 bytes
Variable Size                671095544 bytes
Database Buffers            1979711488 bytes
Redo Buffers                  29843456 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/01/2024 20:00:00','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';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 25-JAN-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=204 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=211 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=218 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=225 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=232 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=239 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=246 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=253 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=260 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: NMDA Oracle v19.3.0.3
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=267 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: NMDA Oracle v19.3.0.3

new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO2_c-2412941060-20240115-04"
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 DBAHERO2_c-2412941060-20240115-04
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO2_c-2412941060-20240115-04 tag=TAG20240115T193829
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
output file name=/oradata/LSL/DBAHERO2/controlfile/o1_mf_lv50k4x5_.ctl
Finished restore at 25-JAN-24

sql statement: alter database mount clone database

sql statement: alter system archive log current

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/01/2024 20:00:00','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  116 to new;
set newname for clone datafile  162 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone datafile  168 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, 116, 162, 3, 2, 168;

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

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/LSL/DBAHERO2/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 2 to /oradata/LSL/DBAHERO2/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 25-JAN-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2

new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_o72gk2o2_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_p72gk5kt_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_qf2gk5s3_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_nh2gjudd_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_p12gk5db_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_p92gk5lr_1_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 00003 to /oradata/LSL/DBAHERO2/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_nh2gjudd_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 /oradata/LSL/DBAHERO2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_o72gk2o2_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_o72gk2o2_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:03:36
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 00002 to /oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_p12gk5db_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_nh2gjudd_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:04:51
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 00116 to /oradata/LSL/DBAHERO2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_p72gk5kt_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_p12gk5db_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:01:22
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 00168 to /oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_p92gk5lr_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_p72gk5kt_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:02
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 00162 to /oradata/LSL/DBAHERO2/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_qf2gk5s3_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_p92gk5lr_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_qf2gk5s3_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
Finished restore at 25-JAN-24

datafile 1 switched to datafile copy
input datafile copy RECID=188 STAMP=1159203244 file name=/oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50m2v1_.dbf
datafile 116 switched to datafile copy
input datafile copy RECID=189 STAMP=1159203284 file name=/oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50w32l_.dbf
datafile 162 switched to datafile copy
input datafile copy RECID=190 STAMP=1159203322 file name=/oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50y7cm_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=191 STAMP=1159203358 file name=/oradata/LSL/DBAHERO2/datafile/o1_mf_undotbs1_lv50m1rq_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=192 STAMP=1159203395 file name=/oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_lv50sqos_.dbf
datafile 168 switched to datafile copy
input datafile copy RECID=193 STAMP=1159203438 file name=/oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_lv50wb0t_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/01/2024 20:00:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  116 online";
sql clone "alter database datafile  162 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  168 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  116 online

sql statement: alter database datafile  162 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  168 online

Starting recover at 25-JAN-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_vi2glvuf_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_0i2gm03d_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_1q2gm07m_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_us2glvo2_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_0c2gm02e_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_0k2gm03j_1_1"
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /oradata/LSL/DBAHERO2/datafile/o1_mf_undotbs1_lv50m1rq_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_us2glvo2_1_1
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50m2v1_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_vi2glvuf_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_vi2glvuf_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_lv50sqos_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_0c2gm02e_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_0c2gm02e_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00116: /oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50w32l_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_0i2gm03d_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_0i2gm03d_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:46
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00168: /oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_lv50wb0t_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_0k2gm03j_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_0k2gm03j_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00162: /oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50y7cm_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_1q2gm07m_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_us2glvo2_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:03:46
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_1q2gm07m_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:55

starting media recovery

new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_262gm09a_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "282gm53b_1_1"
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=227056
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_262gm09a_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=227057
channel ORA_AUX_SBT_TAPE_2: reading from backup piece 282gm53b_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_262gm09a_1_1 tag=TAG20240115T193816
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:55
archived log file name=/oradata/LSL/1_227056_880191520.dbf thread=1 sequence=227056
channel ORA_AUX_SBT_TAPE_2: piece handle=282gm53b_1_1 tag=TAG20240115T210025
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:57
archived log file name=/oradata/LSL/1_227057_880191520.dbf thread=1 sequence=227057
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-JAN-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 =
  ''/oradata/LSL/DBAHERO2/controlfile/o1_mf_lv50k4x5_.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    2684354560 bytes

Fixed Size                     3704072 bytes
Variable Size                687872760 bytes
Database Buffers            1962934272 bytes
Redo Buffers                  29843456 bytes

sql statement: alter system set  control_files =   ''/oradata/LSL/DBAHERO2/controlfile/o1_mf_lv50k4x5_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2684354560 bytes

Fixed Size                     3704072 bytes
Variable Size                687872760 bytes
Database Buffers            1962934272 bytes
Redo Buffers                  29843456 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/01/2024 20:00:00','dd/mm/yyyy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  103 to new;
set newname for datafile  104 to new;
set newname for datafile  105 to new;
set newname for datafile  106 to new;
set newname for datafile  128 to new;
set newname for datafile  160 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  103, 104, 105, 106, 128, 160;

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

Starting restore at 25-JAN-24
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=204 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=211 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=218 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=225 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=232 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=239 device type=DISK
allocated channel: ORA_AUX_DISK_7
channel ORA_AUX_DISK_7: SID=246 device type=DISK
allocated channel: ORA_AUX_DISK_8
channel ORA_AUX_DISK_8: SID=253 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=260 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: NMDA Oracle v19.3.0.3
allocated channel: ORA_AUX_SBT_TAPE_2
channel ORA_AUX_SBT_TAPE_2: SID=267 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_2: NMDA Oracle v19.3.0.3

new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_la2gjcld_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_lb2gjcrh_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_lc2gjd28_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_ld2gjda7_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_ma2gjjit_1_1"
new media label is "DDBISBU.001,DDBETCL.201" for piece "DBAHERO_p22gk5ep_1_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 00103 to /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_la2gjcld_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 00104 to /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_lb2gjcrh_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_la2gjcld_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:05:15
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 00105 to /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_lc2gjd28_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_lb2gjcrh_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:05:23
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 00106 to /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_ld2gjda7_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_lc2gjd28_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:04:32
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 00128 to /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_ma2gjjit_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_ld2gjda7_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:04:28
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 00160 to /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_%u_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_p22gk5ep_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_p22gk5ep_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:01:45
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_ma2gjjit_1_1 tag=TAG20240114T193100
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:04:48
Finished restore at 25-JAN-24

datafile 103 switched to datafile copy
input datafile copy RECID=206 STAMP=1159204840 file name=/oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv51pgmy_.dbf
datafile 104 switched to datafile copy
input datafile copy RECID=207 STAMP=1159204882 file name=/oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv51pgn3_.dbf
datafile 105 switched to datafile copy
input datafile copy RECID=208 STAMP=1159204925 file name=/oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv5203pg_.dbf
datafile 106 switched to datafile copy
input datafile copy RECID=209 STAMP=1159204963 file name=/oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv520bs5_.dbf
datafile 128 switched to datafile copy
input datafile copy RECID=210 STAMP=1159205000 file name=/oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv528n3o_.dbf
datafile 160 switched to datafile copy
input datafile copy RECID=211 STAMP=1159205035 file name=/oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv528q2w_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('15/01/2024 20:00:00','dd/mm/yyyy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  103 online";
sql clone "alter database datafile  104 online";
sql clone "alter database datafile  105 online";
sql clone "alter database datafile  106 online";
sql clone "alter database datafile  128 online";
sql clone "alter database datafile  160 online";
# recover and open resetlogs
recover clone database tablespace  "TS_CSTMMART", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  103 online

sql statement: alter database datafile  104 online

sql statement: alter database datafile  105 online

sql statement: alter database datafile  106 online

sql statement: alter database datafile  128 online

sql statement: alter database datafile  160 online

Starting recover at 25-JAN-24
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
using channel ORA_AUX_DISK_7
using channel ORA_AUX_DISK_8
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_SBT_TAPE_2
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_sn2glv49_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_so2glv4g_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_sp2glv4o_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_sq2glv4v_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_tl2glvcv_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_0d2gm02l_1_1"
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00103: /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv51pgmy_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_sn2glv49_1_1
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00104: /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv51pgn3_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_so2glv4g_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_sn2glv49_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00105: /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv5203pg_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_sp2glv4o_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_so2glv4g_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00106: /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv520bs5_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_sq2glv4v_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_sp2glv4o_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00128: /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv528n3o_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_tl2glvcv_1_1
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_sq2glv4v_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:55
channel ORA_AUX_SBT_TAPE_2: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00160: /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv528q2w_.dbf
channel ORA_AUX_SBT_TAPE_2: reading from backup piece DBAHERO_0d2gm02l_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_tl2glvcv_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:56
channel ORA_AUX_SBT_TAPE_2: piece handle=DBAHERO_0d2gm02l_1_1 tag=TAG20240115T191626
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:55

starting media recovery

new media label is "DDBISBU.001,DDBETCL.202" for piece "DBAHERO_262gm09a_1_1"
new media label is "DDBISBU.001,DDBETCL.202" for piece "282gm53b_1_1"
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=227056
channel ORA_AUX_SBT_TAPE_1: reading from backup piece DBAHERO_262gm09a_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=227057
channel ORA_AUX_SBT_TAPE_2: reading from backup piece 282gm53b_1_1
channel ORA_AUX_SBT_TAPE_1: piece handle=DBAHERO_262gm09a_1_1 tag=TAG20240115T193816
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
archived log file name=/oradata/LSL/1_227056_880191520.dbf thread=1 sequence=227056
channel clone_default: deleting archived log(s)
archived log file name=/oradata/LSL/1_227056_880191520.dbf RECID=143295 STAMP=1159205306
channel ORA_AUX_SBT_TAPE_2: piece handle=282gm53b_1_1 tag=TAG20240115T210025
channel ORA_AUX_SBT_TAPE_2: restored backup piece 1
channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:47
archived log file name=/oradata/LSL/1_227057_880191520.dbf thread=1 sequence=227057
channel clone_default: deleting archived log(s)
archived log file name=/oradata/LSL/1_227057_880191520.dbf RECID=143296 STAMP=1159205306
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-JAN-24

database opened

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

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oradata/LSL/export''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/oradata/LSL/export''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_abEA_aqxk":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 1.393 GB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> . . exported "CSTMMART"."DATAMART_LOYALTY_30JUN2023"     704.9 MB  605259 rows
   EXPDP> . . exported "CSTMMART"."SUBS_M2M_30JUN2023"             408.9 MB  727945 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_abEA_aqxk" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_abEA_aqxk is:
   EXPDP>   /oradata/LSL/export/CSTMMART_SUBS_M2M_30JUN2023_DATAMART_LOYALTY_30JUN2023.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_abEA_aqxk" successfully completed at Thu Jan 25 17:31:16 2024 elapsed 0 00:02:09
Export completed

Not performing table import after point-in-time recovery

Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_temp_lv51lrgj_.tmp deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_temp_lv51lrlv_.tmp deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/onlinelog/o1_mf_3_lv531q9g_.log deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/onlinelog/o1_mf_2_lv531mwf_.log deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/onlinelog/o1_mf_1_lv531jg1_.log deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv528q2w_.dbf deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv528n3o_.dbf deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv520bs5_.dbf deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv5203pg_.dbf deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv51pgn3_.dbf deleted
auxiliary instance file /oradata/LSL/ABEA_PITR_DBAHERO/datafile/o1_mf_ts_cstmm_lv51pgmy_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_lv50wb0t_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_sysaux_lv50sqos_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_undotbs1_lv50m1rq_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50y7cm_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50w32l_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/datafile/o1_mf_system_lv50m2v1_.dbf deleted
auxiliary instance file /oradata/LSL/DBAHERO2/controlfile/o1_mf_lv50k4x5_.ctl deleted
Finished recover at 25-JAN-24

RMAN>
RMAN>

- Até a próxima.

Oracle Recover Dropped Table - RMAN

— Autor: Leonardo Lopes 08/02/2024 06H:09

¹⁾

Pura falta de atenção…

²⁾

Feature ⇒ Funcionalidade