oraclerecoverdroppedtablerman
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
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”1).
A partir da versão 12C (12.1) do Oracle Database, foi introduzida a feature 2) 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
oraclerecoverdroppedtablerman.txt · Última modificação: 08/02/2024 06H:10 por admin