If you do not import the export dump file as part of the recovery process, you must manually import it later using the Data Pump Import utility. You can also use data constants such as SYSDATE to specify the time, for example SYSDATE-30. If a table with this name exists in the target database, then RMAN appends _1 to the name. The schema EXAMPLE exists at the time this example is run. You can specify the location on the target host to which the recovered data files are stored in the auxiliary database. See "About Importing Recovered Tables and Table Partitions into the Target Database". Depending on your requirements, you may also need to use the one or more of the following clauses: DUMP FILE, DATAPUMP DESTINATION, NOTABLEIMPORT, REMAP TABLE, or REMAP TABLESPACE. Only the tables or table partitions that are being recovered are remapped, the existing objects are not changed. Example: Recovering Tables to a Specified Point in Time, Example: Recovering Table Partitions to a Specified Log Sequence Number. Recovering tables and table partitions from RMAN backups by using the RECOVER command is subject to certain limitations. (Optional) Renames the recovered tables or table partitions in the target database. On the target host that is used to store data files for the auxiliary database, use one of the following techniques: AUXILIARY DESTINATION clause in the RECOVER command. 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. Use the REMAP TABLE clause to rename recovered tables or table partitions in the target database. The auxiliary destination used during the recovery process is /tmp/oracle/recover. RECOVER command syntax in Oracle Database Backup and Recovery Reference, SET command syntax in Oracle Database Backup and Recovery Reference. However, this dump file is not imported into the target database. The auxiliary destination used during the recovery process is /tmp/oracle/recover. Oracle Database Backup and Recovery Reference for information about the RECOVER command. Certain prerequisites must be met before you recover tables or table partitions from RMAN backups. 
The following RECOVER command performs the required table recovery: If the required space is not available, then RMAN displays an error and exits the recovery operation. Flashback Table is not possible because the desired point-in-time is older than available undo. For logical standby databases, objects that are recovered on the primary database are propagated to the logical standby. The location is typically the path of the operating-system directory that stores the dump file. When you use the REMAP option, any named constraints and indexes are not imported. When you recover table partitions, each table partition is recovered into a separate table. On Windows, the default location is %ORACLE_HOME\database. 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. Use the DATAPUMP DESTINATION clause of the RECOVER command to specify the location in which the Data Pump export dump file is created. Tables and table partitions on standby databases cannot be recovered. Flashback Table cannot rewind tables through structural changes such as a truncate table operation. Tables with named NOT NULL constraints cannot be recovered with the REMAP option. RMAN performs a series of steps while automating the process of recovering tables or table partitions from an RMAN backup. It is important to determine the exact point in time to which you want to recover the tables or table partitions. The REMAP TABLE clause enables you to rename objects and recover them into a different schema. In this example, the table sales, in the schema sh, contains the following partitions: sales_1998, sales_1999, sales_2000, and sales_2001. You must manually import this dump file into your target database, when required, by using the Data Pump Import utility. 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. This chapter describes how to recover tables and table partitions to a specified point in time. See "About the Data Pump Export Dump File Used During RMAN Table Recovery" for information. This example recovers multiple tables to a specified point in time that is represented using SYSDATE. This example uses RMAN backups to recover multiple table partitions. To recover tables or table partitions in a non-CDB to a specified point in time: You can use the following additional clauses in the RECOVER command: Specifies the name of the export dump file containing recovered tables or table partitions and the location in which it must be stored. If you do not specify an auxiliary destination, the dump file is stored in a default operating system-specific location. (Optional) Imports the Data Pump export dump file into the target instance. You can also import recovered objects into a tablespace or schema that is different from the one in which they originally existed. 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. 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. Determines if there is sufficient space on the target host to create the auxiliary instance that will be used during the table or partition recovery process. Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered. Creates an auxiliary database on the target host and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database. The new schema must exist in the target database before you perform the recovery. When NOTABLEIMPORT is used, RMAN recovers them to the specified point and then creates the export dump file. If you omit this clause, the dump file is stored in the location specified by the AUXILIARY DESTINATION parameter. Starting with Oracle Database 12c Release 2 (12.2), you can recover tables or table partitions into a schema that is different from the source schema (the schema in which they originally existed). This table is stored in the sales_ts tablespace. 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. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. To recover tables in a PDB, you need backups of the following: SYSTEM, SYSAUX, and undo tablespace of the root, CDB seed, and the PDB containing the tables, Tablespace containing the tables or partitions. RMAN uses the RECOVER command to recover tables or table partitions to a specified point in time. Use the RESTORE and RECOVER commands to recover tables or table partitions. RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. The following command recovers the table PDB_EMP in the PDB HR_PDB to the state that it was in 4 days before the current date. 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. For more information about the scenarios in which these methods are useful and how to recover tables using these methods, see: Performing Flashback and Database Point-in-Time Recovery, Performing RMAN Tablespace Point-in-Time Recovery (TSPITR), Purpose of Recovering Tables and Table Partitions from RMAN Backups, RMAN Backups Required to Recover Tables and Table Partitions, Basic Concepts of Recovering Tables and Table Partitions from RMAN Backups. 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. 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. 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. See "About Renaming Recovered Tables and Table Partitions During RMAN Recovery and About Recovering Tables and Partitions Into a New Schema". Use a RUN block containing the RECOVER command and required SET NEWNAME commands that rename the data files. The recovered tables must be automatically imported into the target database and mapped to the tablespace SALES_PRE_2000_TS. There are other methods of recovering tables to a specified point in time such as Oracle Flashback and TSPITR. However, the recovered tables must not be imported in to the target database. You must perform some preliminary tasks before you prepare to recover tables or table partitions . There are multiple ways to specify the point in time to which objects must be recovered. About Importing Recovered Tables and Table Partitions into the Target Database. 
This chapter contains the following topics: Overview of Recovering Tables and Table Partitions from RMAN Backups, Preparing to Recover Tables and Table Partitions, Recovering Tables and Table Partitions in PDBs, Examples: Recovering Tables and Table Partitions From RMAN Backups. Oracle Database Backup and Recovery Reference for additional examples about recovering tables to a specified point in time. RMAN enables you to use the RECOVER command 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. In this example, you need to recover tables to a point in time specified by an expression that uses, Start an RMAN session and connect to the target database as described in, Prerequisites for Recovering Tables and Table Partitions from RMAN Backups, Limitations of Recovering Tables and Table Partitions from RMAN Backups, Determining the Point-in-time to Which Tables and Table Partitions Must be Recovered. However, you can specify that RMAN must not import the recovered objects. Deciding if the recovered tables or table partitions must be renamed, mapped to a new tablespace, or mapped to a new schema. Recovering tables or table partitions into a different schema enables you to avoid name conflicts that may be caused by constraint, index, or trigger names that already existing in the source schema. 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. Use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables. You can import these tables, when required, by using the Data Pump import utility. HR is the name of the schema that contains the table. You can rename tables and remap the schema in a single recovery operation. "About Importing Recovered Tables and Table Partitions into the Target Database", "Determining the Point-in-time to Which Tables and Table Partitions Must be Recovered", "About Renaming Recovered Tables and Table Partitions During RMAN Recovery", "About Recovering Tables and Partitions Into a New Schema". RMAN uses this information to automate the process of recovering the specified tables or table partitions. If the recovered tables or table partitions need to be renamed, mapped to a new tablespace, or mapped to a new schema, then you must specify the new names for the tables, tablespaces, or schemas. Table recovery is not supported on physical standby databases. 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. The RMAN RECOVER command enables you to recover tables and table partitions from RMAN backups. Steps Performed By RMAN to Recover Tables and Table Partitions, About the Location of Auxiliary Database Files During RMAN Table Recovery, About the Data Pump Export Dump File Used During RMAN Table Recovery, About Renaming Recovered Tables and Table Partitions During RMAN Recovery, About Recovering Tables and Partitions Into a New Schema. If you omit this clause, RMAN uses a default operating system-specific name for the dump file. On Linux, this default location is $ORACLE_HOME/dbs. Indicates that the recovered tables or table partitions must not be imported into the target database. 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. The preparation for recovering tables or table partitions from RMAN backups includes 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. Recover the selected tables or table partitions to the specified point in time by using the, Start RMAN and connect to the root as a user with the, Recover the tables or table partitions to the specified point in time by using the, Perform the planning tasks required to recover tables from RMAN backups. Recovers tables or table partitions to the state they were in at the specified time. If you omit the REMAP TABLE clause, RMAN uses default names for the imported tables. You want to recover data that is lost after a DDL operation modified the structure of tables. However, you do not want RMAN to import these tables into the target database. RMAN creates an auxiliary database that it uses during the process of recovering the specified tables or table partitions. To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher. This section contains examples that cover multiple scenarios for recovering tables and table partitions. You can specify the name and the location of the Data Pump export dump file used to store the metadata of the recovered tables or table partitions. For example, you can recover the HR.EMPLOYEES table either into the NEW_HR.EMPLOYEES table, the HR.NEW_EMPLOYEES table, or the NEW_HR.NEW_EMPLOYEES table. Recovers the tables or table partitions into a tablespace that is different from the one in which these objects originally existed. In this example, the HR.DEPARTMENTS and SH.CHANNELS tables need to be recovered to the state that they were in one day ago, before a logical corruption occurred. If this name too exists, then RMAN appends _2 to the name and so on. The REMAP TABLE clause is used to indicate how the source schema is mapped to a new target schema. Recovers tables or table partitions to the state they were at the time specified by the log sequence number and thread number. By default, RMAN imports the recovered tables or table partitions into the target database. Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery. If an error occurs during the import operation, RMAN does not delete the export dump file at the end of the table recovery. The recovered table is renamed to EMP_RECVR. If you do not explicitly specify table names, RMAN generates table names by concatenating the recovered table name and partition name. It is recommended that you provide a location for data files in the auxiliary database by using the AUXILIARY DESTINATION clause. The auxiliary destination used during the recovery process is /tmp/auxdest. Recovers tables or table partitions to the state that they were at the time specified by the SCN. The REMAP TABLE clause specifies the names used for the imported tables. You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. The name is a combination of the original table name and the partition name. RMAN must only create the export dump file, called emp_dept_exp_dump.dat, in the location /tmp/recover/dumpfiles. Renames the recovered tables or table partitions in the target database. The generated names are in the format tablename_partitionname.
The following RECOVER command performs the required table recovery: If the required space is not available, then RMAN displays an error and exits the recovery operation. Flashback Table is not possible because the desired point-in-time is older than available undo. For logical standby databases, objects that are recovered on the primary database are propagated to the logical standby. The location is typically the path of the operating-system directory that stores the dump file. When you use the REMAP option, any named constraints and indexes are not imported. When you recover table partitions, each table partition is recovered into a separate table. On Windows, the default location is %ORACLE_HOME\database. 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. Use the DATAPUMP DESTINATION clause of the RECOVER command to specify the location in which the Data Pump export dump file is created. Tables and table partitions on standby databases cannot be recovered. Flashback Table cannot rewind tables through structural changes such as a truncate table operation. Tables with named NOT NULL constraints cannot be recovered with the REMAP option. RMAN performs a series of steps while automating the process of recovering tables or table partitions from an RMAN backup. It is important to determine the exact point in time to which you want to recover the tables or table partitions. The REMAP TABLE clause enables you to rename objects and recover them into a different schema. In this example, the table sales, in the schema sh, contains the following partitions: sales_1998, sales_1999, sales_2000, and sales_2001. You must manually import this dump file into your target database, when required, by using the Data Pump Import utility. 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. This chapter describes how to recover tables and table partitions to a specified point in time. See "About the Data Pump Export Dump File Used During RMAN Table Recovery" for information. This example recovers multiple tables to a specified point in time that is represented using SYSDATE. This example uses RMAN backups to recover multiple table partitions. To recover tables or table partitions in a non-CDB to a specified point in time: You can use the following additional clauses in the RECOVER command: Specifies the name of the export dump file containing recovered tables or table partitions and the location in which it must be stored. If you do not specify an auxiliary destination, the dump file is stored in a default operating system-specific location. (Optional) Imports the Data Pump export dump file into the target instance. You can also import recovered objects into a tablespace or schema that is different from the one in which they originally existed. 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. 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. Determines if there is sufficient space on the target host to create the auxiliary instance that will be used during the table or partition recovery process. Tables and table partitions from SYSTEM and SYSAUX tablespaces cannot be recovered. Creates an auxiliary database on the target host and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database. The new schema must exist in the target database before you perform the recovery. When NOTABLEIMPORT is used, RMAN recovers them to the specified point and then creates the export dump file. If you omit this clause, the dump file is stored in the location specified by the AUXILIARY DESTINATION parameter. Starting with Oracle Database 12c Release 2 (12.2), you can recover tables or table partitions into a schema that is different from the source schema (the schema in which they originally existed). This table is stored in the sales_ts tablespace. 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. Using Flashback Table is not possible because a DDL was run on the tables between the desired point in time and the current time. To recover tables in a PDB, you need backups of the following: SYSTEM, SYSAUX, and undo tablespace of the root, CDB seed, and the PDB containing the tables, Tablespace containing the tables or partitions. RMAN uses the RECOVER command to recover tables or table partitions to a specified point in time. Use the RESTORE and RECOVER commands to recover tables or table partitions. RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. The following command recovers the table PDB_EMP in the PDB HR_PDB to the state that it was in 4 days before the current date. 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. For more information about the scenarios in which these methods are useful and how to recover tables using these methods, see: Performing Flashback and Database Point-in-Time Recovery, Performing RMAN Tablespace Point-in-Time Recovery (TSPITR), Purpose of Recovering Tables and Table Partitions from RMAN Backups, RMAN Backups Required to Recover Tables and Table Partitions, Basic Concepts of Recovering Tables and Table Partitions from RMAN Backups. 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. 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. 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. See "About Renaming Recovered Tables and Table Partitions During RMAN Recovery and About Recovering Tables and Partitions Into a New Schema". Use a RUN block containing the RECOVER command and required SET NEWNAME commands that rename the data files. The recovered tables must be automatically imported into the target database and mapped to the tablespace SALES_PRE_2000_TS. There are other methods of recovering tables to a specified point in time such as Oracle Flashback and TSPITR. However, the recovered tables must not be imported in to the target database. You must perform some preliminary tasks before you prepare to recover tables or table partitions . There are multiple ways to specify the point in time to which objects must be recovered. About Importing Recovered Tables and Table Partitions into the Target Database. This chapter contains the following topics: Overview of Recovering Tables and Table Partitions from RMAN Backups, Preparing to Recover Tables and Table Partitions, Recovering Tables and Table Partitions in PDBs, Examples: Recovering Tables and Table Partitions From RMAN Backups. Oracle Database Backup and Recovery Reference for additional examples about recovering tables to a specified point in time. RMAN enables you to use the RECOVER command 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. In this example, you need to recover tables to a point in time specified by an expression that uses, Start an RMAN session and connect to the target database as described in, Prerequisites for Recovering Tables and Table Partitions from RMAN Backups, Limitations of Recovering Tables and Table Partitions from RMAN Backups, Determining the Point-in-time to Which Tables and Table Partitions Must be Recovered. However, you can specify that RMAN must not import the recovered objects. Deciding if the recovered tables or table partitions must be renamed, mapped to a new tablespace, or mapped to a new schema. Recovering tables or table partitions into a different schema enables you to avoid name conflicts that may be caused by constraint, index, or trigger names that already existing in the source schema. 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. Use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables. You can import these tables, when required, by using the Data Pump import utility. HR is the name of the schema that contains the table. You can rename tables and remap the schema in a single recovery operation. "About Importing Recovered Tables and Table Partitions into the Target Database", "Determining the Point-in-time to Which Tables and Table Partitions Must be Recovered", "About Renaming Recovered Tables and Table Partitions During RMAN Recovery", "About Recovering Tables and Partitions Into a New Schema". RMAN uses this information to automate the process of recovering the specified tables or table partitions. If the recovered tables or table partitions need to be renamed, mapped to a new tablespace, or mapped to a new schema, then you must specify the new names for the tables, tablespaces, or schemas. Table recovery is not supported on physical standby databases. 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. The RMAN RECOVER command enables you to recover tables and table partitions from RMAN backups. Steps Performed By RMAN to Recover Tables and Table Partitions, About the Location of Auxiliary Database Files During RMAN Table Recovery, About the Data Pump Export Dump File Used During RMAN Table Recovery, About Renaming Recovered Tables and Table Partitions During RMAN Recovery, About Recovering Tables and Partitions Into a New Schema. If you omit this clause, RMAN uses a default operating system-specific name for the dump file. On Linux, this default location is $ORACLE_HOME/dbs. Indicates that the recovered tables or table partitions must not be imported into the target database. 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. The preparation for recovering tables or table partitions from RMAN backups includes 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. Recover the selected tables or table partitions to the specified point in time by using the, Start RMAN and connect to the root as a user with the, Recover the tables or table partitions to the specified point in time by using the, Perform the planning tasks required to recover tables from RMAN backups. Recovers tables or table partitions to the state they were in at the specified time. If you omit the REMAP TABLE clause, RMAN uses default names for the imported tables. You want to recover data that is lost after a DDL operation modified the structure of tables. However, you do not want RMAN to import these tables into the target database. RMAN creates an auxiliary database that it uses during the process of recovering the specified tables or table partitions. To recover single table partitions, the COMPATIBLE initialization parameter for target database must be set to 11.1.0 or higher. This section contains examples that cover multiple scenarios for recovering tables and table partitions. You can specify the name and the location of the Data Pump export dump file used to store the metadata of the recovered tables or table partitions. For example, you can recover the HR.EMPLOYEES table either into the NEW_HR.EMPLOYEES table, the HR.NEW_EMPLOYEES table, or the NEW_HR.NEW_EMPLOYEES table. Recovers the tables or table partitions into a tablespace that is different from the one in which these objects originally existed. In this example, the HR.DEPARTMENTS and SH.CHANNELS tables need to be recovered to the state that they were in one day ago, before a logical corruption occurred. If this name too exists, then RMAN appends _2 to the name and so on. The REMAP TABLE clause is used to indicate how the source schema is mapped to a new target schema. Recovers tables or table partitions to the state they were at the time specified by the log sequence number and thread number. By default, RMAN imports the recovered tables or table partitions into the target database. Determines which backup contains the tables or table partitions that need to be recovered, based on the point in time specified for the recovery. If an error occurs during the import operation, RMAN does not delete the export dump file at the end of the table recovery. The recovered table is renamed to EMP_RECVR. If you do not explicitly specify table names, RMAN generates table names by concatenating the recovered table name and partition name. It is recommended that you provide a location for data files in the auxiliary database by using the AUXILIARY DESTINATION clause. The auxiliary destination used during the recovery process is /tmp/auxdest. Recovers tables or table partitions to the state that they were at the time specified by the SCN. The REMAP TABLE clause specifies the names used for the imported tables. You can choose not to import the export dump file that contains the recovered tables or table partitions into the target database. The name is a combination of the original table name and the partition name. RMAN must only create the export dump file, called emp_dept_exp_dump.dat, in the location /tmp/recover/dumpfiles. Renames the recovered tables or table partitions in the target database. The generated names are in the format tablename_partitionname.