En la versión 12c, se introdujeron muchas y muy útiles nuevas funcionalidades. Entre ellas, la posibilidad de recuperar una tabla con RMAN. Esta característica se aplica tanto a arquitecturas Multitenant como no Multitenant, permitiéndote recuperar una tabla de una PDB si ese fuera el caso.
En el siguiente enlace podemos encontrar la nota oficial:
RMAN Recover Table Feature in Oracle Database 12c and Higher (Doc ID 1521524.1)
En este ejemplo de prueba vamos a recuperar una tabla creada en el esquema raul de la PDB “ORCLPDB1” dentro de la base de datos, cuya CDB es “ORCLCDB”
SQL> show con_name
CON_NAME
——————————
ORCLPDB1
SQL> show con_id
CON_ID
——————————
3
Para ello, vamos a crear una tabla simple, e insertamos 5 valores:
SQL> create table uno (dni number);
Table created.
SQL>insert into uno values (1);
SQL>insert into uno values (2);
SQL>insert into uno values (3);
SQL>insert into uno values (4);
SQL>insert into uno values (5);
SQL> commit;
SQL> select * from uno
DNI
————–
1
2
3
4
5
Realizamos el backup de la base de datos con el que recuperaremos la tabla posteriormente.
Conectados a la CDB, realizaremos un backup completo de la base de datos y los archives:
[oracle@oracle21c ~]$ rman
Recovery Manager: Release 19.0.0.0.0 – Production on Thu Nov 3 17:43:08 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
RMAN> backup database plus archivelog;
Starting backup at 2022-11-03:17:43:30
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=293 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=1 STAMP=1104223655t
.
.
input archived log thread=1 sequence=26 RECID=8 STAMP=1119807810
channel ORA_DISK_1: starting piece 1 at 2022-11-03:17:43:31
channel ORA_DISK_1: finished piece 1 at 2022-11-03:17:43:34
.
.
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ORCLCDB/orclpdb1/sysaux01.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCLCDB/orclpdb1/users01.dbf
.
.
channel ORA_DISK_1: starting piece 1 at 2022-11-03:17:44:11
channel ORA_DISK_1: finished piece 1 at 2022-11-03:17:44:12
piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/backupset/2022_11_03/o1_mf_annnn_TAG20221103T174411_kp7zhcyq_.bkp tag=TAG20221103T174411 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-11-03:17:44:12
Starting Control File and SPFILE Autobackup at 2022-11-03:17:44:13
piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2022_11_03/o1_mf_s_1119807853_kp7zhfc2_.bkp comment=NONE
.
Finished Control File and SPFILE Autobackup at 2022-11-03:17:44:14
RMAN>
Para verificar que hemos realizado correctamente la copia de nuestra base de datos (PDB) y del tablespace donde tenemos albergada nuestra tabla, en nuestro caso ORCLPDB1:USERS, podemos ejecutar el comando «report schema».
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
using target database control file instead of recovery catalog
RMAN>
RMAN> report schema;
Report of database schema for database with db_unique_name ORCLCDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 920 SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/system01.dbf
.
.
6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
.
.
8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9 280 ORCLPDB1:SYSTEM YES /u01/app/oracle/oradata/ORCLCDB/orclpdb1/system01.dbf
10 370 ORCLPDB1:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/orclpdb1/sysaux01.dbf
.
12 342 ORCLPDB1:USERS NO /u01/app/oracle/oradata/ORCLCDB/orclpdb1/users01.dbf
14 370 ORCLPDB2:SYSAUX NO /u01/app/oracle/oradata/ORCLCDB/orclpdb2/sysaux01.dbf
16 342 ORCLPDB2:USERS NO /u01/app/oracle/oradata/ORCLCDB/orclpdb2/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 131 TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-08_01-48-43-006-AM.dbf
3 36 ORCLPDB1:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orclpdb1/temp01.dbf
4 36 ORCLPDB2:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orclpdb2/temp01.dbf
Recuperación de la tabla.
Es bastante importante definir un formato de fecha, especialmente en RMAN a la hora de recuperar la tabla, ya que este será el formato que usará la herramienta para recuperar la tabla.
En este ejemplo vamos a realizar el borrado de la tabla para luego recuperarla.
SQL> alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi:ss’;
Session altered.
SQL> select sysdate from dual;
SYSDATE
————-
03/11/2022 22:14:14
SQL> drop table uno;
Lanzamos el restore. Ejecutamos el script recover_table.bck, donde se indica que queremos restaurar la tabla UNO justo a la fecha y hora antes del borrado de la tabla. Como se puede observar, lo que hace RMAN una vez restaurado el backup de RMAN, es exportar e importar a través de un import los datos de la tabla borrada.
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
RMAN> @recover_table.bck
RMAN> run{
2> recover table «RAUL».»UNO» OF PLUGGABLE DATABASE ORCLPDB1
3> until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»
4> AUXILIARY DESTINATION ‘/u01/app/oracle/oradata/ORCLCDBRESTORE’;
5> }
Starting recover at 2022-11-03:23:10:23
using target database control file instead of recovery catalog
current log archived
allocated 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 UNDOTBS1
.
.
db_name=ORCLCDB
db_unique_name=wdum_pitr_ORCLPDB1_ORCLCDB
compatible=19.0.0
.
.
db_create_file_dest=/u01/app/oracle/oradata/ORCLCDBRESTORE
log_archive_dest_1=’location=/u01/app/oracle/oradata/ORCLCDBRESTORE’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance ORCLCDB
Oracle instance started
.
.
contents of Memory Script:
{
# set requested point in time
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
# restore the controlfile
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2022_11_03/o1_mf_s_1119807853_kp7zhfc2_.bkp
.
.
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/autobackup/2022_11_03/o1_mf_s_1119807853_kp7zhfc2_.bkp tag=TAG20221103T174413
.
.
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
.
.
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 11, 3, 10;
switch clone datafile all;
}
executing Memory Script
.
.
renamed tempfile 1 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2022-11-03:23:10:48
.
.
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
.
.
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_undotbs1_%u_.dbf
.
.
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/backupset/2022_11_03/o1_mf_nnndf_TAG20221103T174334_kp7zgp9h_.bkp
channel ORA_AUX_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/backupset/2022_11_03/o1_mf_nnndf_TAG20221103T174334_kp7zgp9h_.bkp
tag=TAG20221103T174334
channel ORA_AUX_DISK_1: restored backup piece 1
.
.
contents of Memory Script:
{
# set requested point in time
.
.
# recover and open database read only
recover clone database tablespace «SYSTEM», «ORCLPDB1″:»SYSTEM», «UNDOTBS1», «ORCLPDB1″:»UNDOTBS1», «SYSAUX», «ORCLPDB1″:»SYSAUX»;
sql clone ‘alter database open read only’;
}
executing Memory Script
executing command: SET until clause
.
.
using channel ORA_AUX_DISK_1
starting media recovery
.
.
media recovery complete, elapsed time: 00:00:04
Finished recover at 2022-11-03:23:11:17
sql statement: alter database open read only
contents of Memory Script:
{
sql clone ‘alter pluggable database ORCLPDB1 open read only’;
}
executing Memory Script
sql statement: alter pluggable database ORCLPDB1 open read only
contents of Memory Script:
{
sql clone «create spfile from memory»;
shutdown clone immediate;
startup clone nomount;
.
.
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
.
.
# set requested point in time
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
.
:
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2022-11-03:23:12:30
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=182 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
.
.
.
set until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»;
# online the datafiles restored or switched
sql clone ‘ORCLPDB1’ «alter database datafile
12 online»;
# recover and open resetlogs
recover clone database tablespace «ORCLPDB1″:»USERS», «SYSTEM», «ORCLPDB1″:»SYSTEM», «UNDOTBS1», «ORCLPDB1″:»UNDOTBS1», «SYSAUX», «ORCLPDB1″:»SYSAUX» delete
archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 12 online
Starting recover at 2022-11-03:23:12:34
using channel ORA_AUX_DISK_1
starting media recovery
.
.
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_11_03/o1_mf_1_27_kp7zhcqx_.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_11_03/o1_mf_1_28_kp8llzrd_.arc thread=1 sequence=28
media recovery complete, elapsed time: 00:00:01
Finished recover at 2022-11-03:23:12:36
database opened
contents of Memory Script:
{
sql clone ‘alter pluggable database ORCLPDB1 open’;
}
executing Memory Script
sql statement: alter pluggable database ORCLPDB1 open
contents of Memory Script:
{
# create directory for datapump import
sql ‘ORCLPDB1’ «create or replace directory
TSPITR_DIROBJ_DPDIR as »
/u01/app/oracle/oradata/ORCLCDBRESTORE»»;
# create directory for datapump export
sql clone ‘ORCLPDB1’ «create or replace directory
TSPITR_DIROBJ_DPDIR as »
/u01/app/oracle/oradata/ORCLCDBRESTORE»»;
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as »/u01/app/oracle/oradata/ORCLCDBRESTORE»
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as »/u01/app/oracle/oradata/ORCLCDBRESTORE»
Performing export of tables…
EXPDP> Starting «SYS».»TSPITR_EXP_wdum_ssEn»:
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 «RAUL».»UNO» 5.078 KB 5 rows
EXPDP> Master table «SYS».»TSPITR_EXP_wdum_ssEn» successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_wdum_ssEn is:
EXPDP> /u01/app/oracle/oradata/ORCLCDBRESTORE/tspitr_wdum_34186.dmp
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_wdum_sDFr» successfully loaded/unloaded
IMPDP> Starting «SYS».»TSPITR_IMP_wdum_sDFr»:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported «RAUL».»UNO» 5.078 KB 5 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
MPDP> Job «SYS».»TSPITR_IMP_wdum_sDFr» successfully completed at Thu Nov 3 23:14:00 2022 elapsed 0 00:00:27
import completed
Removing automatic instance
auxiliary instance file /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/8857B36632797E5CE0536210ED0ADAC7/datafile/o1_mf_undotbs1_kp8ln8dn_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/ORCLCDBRESTORE/ORCLCDB/datafile/o1_mf_undotbs1_kp8lmsb6_.dbf deleted
Finished recover at 2022-11-03:23:14:04
Comprobamos que la tabla ha sido restaurada:
oracle@oracle21c ~]$ sqlplus raul/***@ORCLPDB1
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 3 23:26:37 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Nov 03 2022 22:26:01 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
SQL> SELECT * FROM UNO;
DNI
——-
1
2
3
4
5
RESTAURAR LA TABLA CON OTRO NOMBRE DIFERENTE
Existe otra opción muy útil para restaurar la tabla borrada, es restaurarla con otro nombre o “remap” de tabla.
RMAN> connect target /
connected to target database: ORCLCDB (DBID=2778750799)
RMAN> @recover_table_remap.bck
RMAN> run{
2> recover table «RAUL».»UNO» OF PLUGGABLE DATABASE ORCLPDB1
3> until time «to_date(’03/11/2022 22:14:14′,’dd/mm/yyyy hh24:mi:ss’)»
4> AUXILIARY DESTINATION ‘/u01/app/oracle/oradata/ORCLCDBRESTORE’
5> REMAP TABLE «RAUL».»UNO»:»RAUL».»UNORESTORE»;
6> }
Starting recover at 2022-11-03:23:40:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=293 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 ORCLPDB1:SYSTEM
{
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 2022-11-03:23:40:26
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=4 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: restore complete, elapsed time: 00:00:03
Finished restore at 2022-11-03:23:40:51
datafile 1 switched to datafile copy
{
.
.
Finished restore at 2022-11-03:23:42:17
Performing export of tables…
Oracle instance shut down
Performing import of tables…
IMPDP> Master table «SYS».»TSPITR_IMP_elEq_vqzl» successfully loaded/unloaded
IMPDP> Starting «SYS».»TSPITR_IMP_elEq_vqzl»:
IMPDP> . . imported «RAUL».»UNORESTORE» 5.078 KB 5 rows
.
.
Import completed
En resumen con esta nueva funcionalidad, evitamos crear una base de datos auxiliary o dummy, exportar la tabla e importarla en la base de datos operativa y luego borrar la base de datos restaurada para este fin. Con este comando, todos los pasos que antiguamente realizábamos a mano, se pueden realizar de forma automática.
