This chapter describes how to recover tables and table partitions to a specified point in time. This chapter contains the following topics:
This section describes the purpose and basic concepts of recovering tables and table partitions from RMAN backups.
Note:
There are other methods of recovering tables to a specified point in time such as Oracle Flashback and TSPITR. For more information about the scenarios in which these methods are useful and how to recover tables using these methods, see:
Prior to recovering a table or table partition, you must perform a full backup of undo, SYSTEM
, SYSAUX
, and the tablespace that contains the table or table partition.
To recover tables in a PDB, you need a backup of the root’s undo, SYSTEM, and SYSAUX tablespaces along with a backup of the PDB’s SYSTEM and SYSAUX tablespaces..
If the indexes or partitions for a table in tablespace tbs1
are contained in tablespace tbs2
, then you can recover the table only if tablepsace tbs2
is also included in the recovery set. To recover a table, all partitions that contain the dependent objects of the table must be included in the recovery set.
RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. You can use previously-created RMAN backups to recover tables and table partitions to a specified point in time.
Recovering tables and table partitions from RMAN backups is useful in the following scenarios:
You need to recover a very small number of tables to a particular point in time. In this situation, TSPITR is not the most effective solution because it moves all the objects in the tablespace to a specified point in time.
You need to recover tables that have been logically corrupted or have been dropped and purged.
Flashback Table is not possible because the desired point-in-time is older than available undo.
You want to recover data that is lost after a DDL operation modified the structure of tables. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. Flashback Table cannot rewind tables through structural changes such as a truncate table operation.
RMAN uses the RECOVER
command to recover tables or table partitions to a specified point in time.
To recover tables and table partitions from an RMAN backup, you need to provide the following information:
Names of tables or table partitions that must be recovered
Point in time to which the tables or table partitions must be recovered
Whether the recovered tables or table partitions must be imported into the target database
RMAN uses this information to automate the process of recovering the specified tables or table partitions. As part of the recovery process, RMAN creates an auxiliary database that is used to recover tables or table partitions to the specified point in time.
The steps used by RMAN to automate the recovery process are described in Steps Performed By RMAN to Recover Tables and Table Partitions.
RMAN performs the following steps while automating the process of recovering tables or table partitions from an RMAN backup:
To recover the specified tables or table partitions, RMAN creates an auxiliary database that it uses during the recovery process. Use one of the following techniques to specify the location used to store data files for the auxiliary database:
AUXILIARY DESTINATION
clause in the RECOVER
command
SET NEWNAME
command
Use a RUN
block containing the RECOVER
command and required SET NEWNAME
commands that rename the data files.
See Also:
Oracle Database Backup and Recovery Reference for information about these commands and clauses
It is recommended that you provide a location for data files in the auxiliary database by using the AUXILIARY DESTINATION
clause. When you use the SET NEWNAME
command, if you omit the name of even one data file required for the recovery process, the tables or table partitions cannot be recovered.
After recovering tables or table partitions to the specified point in time on the auxiliary database, RMAN creates a Data Pump export dump file that contains the recovered objects. You can either specify a name and location for this dump file or allow RMAN to use a default name and location.
Use the DATAPUMP DESTINATION
clause of the RECOVER
command to specify the location in which the Data Pump export dump file is created. The location is typically the path of the operating-system directory that stores the dump file. If you omit this clause, the dump file is stored in the location specified by the AUXILIARY DESTINATION
parameter. If you do not specify an auxiliary destination, the dump file is stored in a default operating system-specific location. On Linux, this default location is $ORACLE_HOME/dbs
. On Windows, the default location is %ORACLE_HOME\database
.
Use the DUMP FILE
clause of the RECOVER
command to specify the name of the Data Pump export dump file. If you omit this clause, RMAN uses a default operating system-specific name for the dump file. On Linux and Windows, the default dump file name is tspitr_
SID-of-clone_n
.dmp
, where SID-of-clone
is the Oracle SID of the auxiliary database created by RMAN to perform the recovery and n
is any randomly-generated number. If a file with the name specified by DUMP FILE
exists in the location in which the dump file must be created, then the recover operation fails.
By default, RMAN imports the recovered tables or table partitions, which are stored in the export dump file, into the target database. However, you can choose not to import the recovered tables or table partitions by using the NOTABLEIMPORT
clause of the RESTORE
command.
When NOTABLEIMPORT
is used, RMAN recovers them to the specified point and then creates the export dump file. However, this dump file is not imported into the target database. You must manually import this dump file into your target database, when required, by using the Data Pump Import utility.
If an error occurs during the import operation, RMAN does not delete the export dump file at the end of the table recovery. This enables you to manually import the dump file.
When you recover tables or table partitions, you can rename the recovered objects after they are imported into the target database. The REMAP TABLE
clause enables you to rename recovered tables or table partitions in your target database. To import the recovered tables or table partitions into a tablespace that is different from the one in which these objects originally existed, use the REMAP TABLESPACE
clause of the RECOVER
command. Only the tables or table partitions that are being recovered are remapped, the existing objects are not changed.
If a table with the same name as the one that you recovered exists in the target database, RMAN displays an error message indicating that the REMAP TABLE
clause must be used to rename the recovered table.
When you recover table partitions, each table partition is recovered into a separate table. Use the REMAP TABLE
clause to specify the table names into which each recovered partition must be imported. If you do not explicitly specify table names, RMAN generates table names by concatenating the recovered table name and partition name. The generated names are in the format tablename_partitionname
. If a table with this name exists in the target database, then RMAN appends _1 to the name. If this name too exists, then RMAN appends _2 to the name and so on.
Note:
When you use the REMAP
option, any named constraints and indexes are not imported. This is to avoid name conflicts with existing tables.
When you use the RECOVER
command to recover tables or table partitions contained in an RMAN backup, the following limitations exist.
Tables and table partitions belonging to SYS
schema cannot be recovered.
Tables and table partitions from SYSTEM
and SYSAUX
tablespaces cannot be recovered.
Single table partitions can be recovered only if your Oracle Database version is Oracle Database 11g Release 1 or later.
Tables and table partitions on standby databases cannot be recovered.
Tables with named NOT NULL
constraints cannot be recovered with the REMAP
option.
The preparation for recovering tables or table partitions from RMAN backups involves the following steps:
Verifying that the prerequisites required to recover tables or table partitions are met
Determining the point in time to which the tables or table partitions must be recovered
Deciding if the recovered tables or table partitions must be imported into the target database
By default, RMAN imports the recovered tables or table partitions into the target database. However, you can specify that RMAN must not import the recovered objects.
In addition to these, review the limitations described in "Limitations of Recovering Tables and Table Partitions from RMAN Backups".
The target database must be in read-write mode.
The target database must be in ARCHIVELOG
mode.
You must have RMAN backups of the tables or table partitions as they existed at the point in time to which you want recover these objects.
To recover single table partitions, the COMPATIBLE
initialization parameter for target database must be set to 11.1.0 or higher.
It is very important to determine the exact point in time to which you want to recover the tables or table partitions. RMAN enables you to specify the required point in time using one of the following:
SCN
Recovers tables or table partitions to the state that they were at the time specified by the SCN.
Time
Recovers tables or table partitions to the state they were in at the specified time. Use the date format specified in the NLS_LANG
and NLS_DATE_FORMAT
environment variables. You can also use data constants such as SYSDATE
to specify the time, for example SYSDATE-30
.
Sequence number
Recovers tables or table partitions to the state they were at the time specified by the log sequence number and thread number.
This section describes the steps required to recover tables or table partitions in a non-CDB to a specified point in time.
To recover tables or table partitions to a specified point in time:
See Also:
For examples on recovering tables and table partitions, see:
"Example: Recovering Table Partitions to a Specified Log Sequence Number"
Oracle Database Backup and Recovery Reference for more examples on recovering tables and table partitions
RMAN enables you to recover one or more tables or table partitions in a pluggable database (PDB) to a specified point-in-time without impacting other objects in the PDB. The steps used to recover tables or table partitions in a PDB are similar to the ones used for non-CDBs, with the differences described in this section.
To recover tables or table partitions in a PDB:
This section contains the following examples that demonstrate how to recover tables and table partitions to a specified point in time by using RMAN backups:
In this example, assume that you want to recover two tables EMP
and DEPT
to the state they were in two days ago, before some logical corruption occurred. However, you do not want RMAN to import these tables into the target database. RMAN must only create the export dump file, called emp_dept_exp_dump.dat
, in the location /tmp/recover/dumpfiles
. Using NOTABLEIMPORT
indicates that these tables must not be imported into the target database. You can import these tables, when required, by using the Data Pump import utility. The auxiliary destination used during the recovery process is /tmp/oracle/recover
.
To recover tables EMP
and DEPT
without importing them into the target database:
See Also:
Oracle Database Backup and Recovery Reference for additional examples about recovering tables to a specified point in time
In this example, the table sales
, in the schema sh
, contains the following partitions: sales_1998
, sales_1999
, sales_2000
, and sales_2001
. This table is stored in the sales_ts
tablespace. You need to recover two partitions, sales_1998
and sales_1999
, to a point in time that is specified by a redo log sequence number. The recovered tables must be automatically imported into the target database and mapped to the tablespace SALES_PRE_2000_TS
.
To recover the partitions sales_1998
and sales_1999
to a specified log sequence number: