Oracle Live

08/05/2016

Recover table with RMAN

Filed under: Backup & Recovery,New 12c — mogukiller @ 12:16 pm
  • Con Oracle 12c permite restaurar de forma sencilla una tabla desde un backup de RMAN.

El proceso que sigue RMAN es similar al que hubiesemos hecho cualquiera de nosotros en versiones anteriores.

1.- Se crea una instancia temporal.
2.- Restaura los tablespace de SYSTEM, SYSAUX y se crea el UNDO y el temporal
3.- Restaura el tablespace donde pertenece la tabla que hemos borrado.
4.- Hace el recover y abre la base de datos.
5.- Hace un expdp de la instancia.
6.- Baja y borra la base de datos auxiliar.
7.- Importa la tabla borrada.

Cosas a tener en cuenta:

1.- Se pueden especificar las clausulas:

  • UNTIL SCN
  • UNTIL TIME
  • UNTIL SEQUENCE

2.- Si se crea la instancia auxiliar en el mismo backend que la base de datos principal es recomendable utilizar AUXILIARY DESTINATION para mapear en otra ruta los dbfs que se van a restaurar.

3.- En el proceso de restuaruacion de la tabla podemos optar, por unicamente generar el export, con la clausual NOTABLEIMPORT. O remapear la tabla con otro nombre REMAP TABLE ‘schema.new_name’

Os adjunto un ejemplo del proceso de restauracion de una tabla.

RMAN> RECOVER TABLE MOGU.TAB_HYBRID UNTIL SCN 19542229 AUXILIARY DESTINATION '+DG_RECO';

Starting recover at 2016/05/08 12:07:41
using channel ORA_DISK_1
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='tnnw'    <<<- se levanta una instancia

initialization parameters used for automatic instance:
db_name=TEST12C
db_unique_name=tnnw_pitr_TEST12C
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/export/app/oracle
_system_trig_enabled=FALSE
sga_target=2000M
processes=200
db_create_file_dest=+DG_RECO            <<-- Lo marca el parametro AUXILIARY DESTINATION
log_archive_dest_1='location=+DG_RECO'    <<-- Lo marca el parametro AUXILIARY DESTINATION
#No auxiliary parameter file used

starting up automatic instance TEST12C
/*
Oracle instance started

Total System Global Area    2097152000 bytes

Fixed Size                     2926320 bytes
Variable Size                486541584 bytes
Database Buffers            1593835520 bytes
Redo Buffers                  13848576 bytes
Automatic instance created
*/

contents of Memory Script:
{
# set requested point in time
set until  scn 19542229;
# 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 2016/05/08 12:08:06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DG_RECO/TEST12C/AUTOBACKUP/2016_05_08/s_911303880.269.911303881
channel ORA_AUX_DISK_1: piece handle=+DG_RECO/TEST12C/AUTOBACKUP/2016_05_08/s_911303880.269.911303881 tag=TAG20160508T115800
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=+DG_RECO/TEST12C/CONTROLFILE/current.365.911304489
Finished restore at 2016/05/08 12:08:11

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  scn 19542229;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2;
 
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to +DG_RECO in control file

Starting restore at 2016/05/08 12:08:16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DG_RECO
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DG_RECO
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DG_RECO
channel ORA_AUX_DISK_1: reading from backup piece +DG_RECO/TEST12C/BACKUPSET/2016_05_08/nnndf0_tag20160508t112843_0.357.911302123
channel ORA_AUX_DISK_1: piece handle=+DG_RECO/TEST12C/BACKUPSET/2016_05_08/nnndf0_tag20160508t112843_0.357.911302123 tag=TAG20160508T112843
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2016/05/08 12:10:32

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=911304632 file name=+DG_RECO/TEST12C/DATAFILE/system.349.911304497
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=911304632 file name=+DG_RECO/TEST12C/DATAFILE/undotbs1.347.911304497
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=911304632 file name=+DG_RECO/TEST12C/DATAFILE/sysaux.348.911304497

contents of Memory Script:
{
# set requested point in time
set until  scn 19542229;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

Starting recover at 2016/05/08 12:10:33
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 55 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_55.359.911302203
archived log for thread 1 with sequence 56 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_56.361.911303785
archived log for thread 1 with sequence 57 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_57.362.911303873
archived log for thread 1 with sequence 58 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_58.358.911303895
archived log for thread 1 with sequence 59 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_59.364.911303901
archived log for thread 1 with sequence 60 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_60.371.911303903
archived log for thread 1 with sequence 61 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_61.263.911303963
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_55.359.911302203 thread=1 sequence=55
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_56.361.911303785 thread=1 sequence=56
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_57.362.911303873 thread=1 sequence=57
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_58.358.911303895 thread=1 sequence=58
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_59.364.911303901 thread=1 sequence=59
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_60.371.911303903 thread=1 sequence=60
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_61.263.911303963 thread=1 sequence=61
media recovery complete, elapsed time: 00:00:06
Finished recover at 2016/05/08 12:10:40

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 = 
  ''+DG_RECO/TEST12C/CONTROLFILE/current.365.911304489'' 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    2097152000 bytes

Fixed Size                     2926320 bytes
Variable Size                503318800 bytes
Database Buffers            1577058304 bytes
Redo Buffers                  13848576 bytes

sql statement: alter system set  control_files =   ''+DG_RECO/TEST12C/CONTROLFILE/current.365.911304489'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2097152000 bytes

Fixed Size                     2926320 bytes
Variable Size                503318800 bytes
Database Buffers            1577058304 bytes
Redo Buffers                  13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 19542229;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  4 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  4;
 
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2016/05/08 12:11:41
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DG_RECO
channel ORA_AUX_DISK_1: reading from backup piece +DG_RECO/TEST12C/BACKUPSET/2016_05_08/nnndf0_tag20160508t112843_0.357.911302123
channel ORA_AUX_DISK_1: piece handle=+DG_RECO/TEST12C/BACKUPSET/2016_05_08/nnndf0_tag20160508t112843_0.357.911302123 tag=TAG20160508T112843
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 2016/05/08 12:11:44

datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=911304705 file name=+DG_RECO/TNNW_PITR_TEST12C/DATAFILE/users.346.911304703

contents of Memory Script:
{
# set requested point in time
set until  scn 19542229;
# online the datafiles restored or switched
sql clone "alter database datafile  4 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  4 online

Starting recover at 2016/05/08 12:11:45
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 55 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_55.359.911302203
archived log for thread 1 with sequence 56 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_56.361.911303785
archived log for thread 1 with sequence 57 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_57.362.911303873
archived log for thread 1 with sequence 58 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_58.358.911303895
archived log for thread 1 with sequence 59 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_59.364.911303901
archived log for thread 1 with sequence 60 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_60.371.911303903
archived log for thread 1 with sequence 61 is already on disk as file +DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_61.263.911303963
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_55.359.911302203 thread=1 sequence=55
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_56.361.911303785 thread=1 sequence=56
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_57.362.911303873 thread=1 sequence=57
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_58.358.911303895 thread=1 sequence=58
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_59.364.911303901 thread=1 sequence=59
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_60.371.911303903 thread=1 sequence=60
archived log file name=+DG_RECO/TEST12C/ARCHIVELOG/2016_05_08/thread_1_seq_61.263.911303963 thread=1 sequence=61
media recovery complete, elapsed time: 00:00:01
Finished recover at 2016/05/08 12:11:46

database opened

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

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DG_RECO''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DG_RECO''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_tnnw_BbcC":  
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 2 MB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "MOGU"."TAB_HYBRID"                         1.232 MB   40110 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_tnnw_BbcC" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_tnnw_BbcC is:
   EXPDP>   +DG_RECO/tspitr_tnnw_33495.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_tnnw_BbcC" successfully completed at Sun May 8 12:12:45 2016 elapsed 0 00:00:24
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_tnnw_BmdF" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_tnnw_BmdF":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "MOGU"."TAB_HYBRID"                         1.232 MB   40110 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_tnnw_BmdF" successfully completed at Sun May 8 12:13:09 2016 elapsed 0 00:00:17
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file +DG_RECO/TEST12C/TEMPFILE/temp.276.911304643 deleted
auxiliary instance file +DG_RECO/TNNW_PITR_TEST12C/ONLINELOG/group_3.344.911304717 deleted
auxiliary instance file +DG_RECO/TNNW_PITR_TEST12C/ONLINELOG/group_2.370.911304713 deleted
auxiliary instance file +DG_RECO/TNNW_PITR_TEST12C/ONLINELOG/group_1.345.911304707 deleted
auxiliary instance file +DG_RECO/TNNW_PITR_TEST12C/DATAFILE/users.346.911304703 deleted
auxiliary instance file +DG_RECO/TEST12C/DATAFILE/sysaux.348.911304497 deleted
auxiliary instance file +DG_RECO/TEST12C/DATAFILE/undotbs1.347.911304497 deleted
auxiliary instance file +DG_RECO/TEST12C/DATAFILE/system.349.911304497 deleted
auxiliary instance file +DG_RECO/TEST12C/CONTROLFILE/current.365.911304489 deleted
auxiliary instance file tspitr_tnnw_33495.dmp deleted
Finished recover at 2016/05/08 12:13:11

 

1 comentario »

  1. […] la restauracion a nivel de tabla. En este post podeis ver un […]

    Pingback por CAP 7.- Mejoras en Backup and Recovery | Oracle Live — 08/05/2016 @ 12:24 pm


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: