OracleDB executes tablespace recovery at the time point

If the Restore directory is not used, the current control file does not contain the old prototype records of the restored tablespace. When the current control file containing the tablespace is used for recovery, it cannot be used in the table

If the Restore directory is not used, the current control file does not contain the old prototype records of the restored tablespace. When the current control file containing the tablespace is used for recovery, it cannot be used in the table

• List operations that will occur when the tablespace is restored at a time point (TSPITR)

• Describe the definition of terms used by TSPITR

• Determine the applicable scenarios where TSPITR is used as a solution

• Determine the correct target time for recovery at the time point

• Determine whether TSPITR cannot be used and the Solution

• Execute automatic TSPITR

Table Space Time Point recovery (TSPITR): Concept

• You can execute TSPITR to quickly restore one or more tablespaces to a previous time.

• Executing TSPITR will not affect the status of other tablespaces or objects in the database.

Using RMAN automatic tablespace time point recovery (TSPITR) can quickly restore one or more tablespaces in Oracle DB to a previous time without affecting the statuses of other tablespaces and objects in the database.

Table Space Time Point recovery (TSPITR): Terminology

• Target time: the time point at which the tablespace is restored or SCN

• Recovery set: data files that constitute the tablespace to be restored

• Secondary Set: a data file that is required to execute TSPITR on the recovery set but not in the recovery set. The auxiliary set usually includes:

-SYSTEM tablespace

-Restored tablespace

-Temporary tablespace

• Secondary goal: storage file disk location

The following terms are used to discuss TSPITR:

• Target time: the time point at which the tablespace will be restored or the system change number (SCN) during the execution of TSPITR ).

• Recovery set: data files that constitute the tablespace to be restored.

• Secondary Set: It is required to execute TSPITR on the recovery set, but it is not a data file of the recovery set. The auxiliary set usually includes:

-SYSTEM tablespace copy

-Data files that contain restoration segments from the target instance

-In some cases, temporary tablespace used during database object export from the secondary instance

• Secondary goal: store any secondary set data files, control files, and online logs of the secondary instance on the disk during TSPITR execution. After TSPITR is executed, you can delete the files stored in the secondary target.

Tablespace time point recovery: Architecture

The following TSPITR entities are displayed in the chart:

• Target database: contains the tablespace to be restored

• Control File: provide backup information to RMAN

• Backup set: from the target database, used as the source to reconstruct the tablespace

• Archive redo logs: from the target database and used as the source to reconstruct the tablespace

• Secondary instance: The Oracle DB instance used for recovery during recovery

RMAN performs the following steps during recovery at the tablespace time point:

1. Restore the backup control file at a certain time point before the target time to the secondary instance. Restore the data files of the recovery set to the target database, and restore the data files of the secondary set to the secondary instance.

2. Restore the restored data file to the specified time point.

3. Export the dictionary metadata of objects in the restored tablespace to the target database.

4. Run the SWITCH command on the target database to direct the control file of the target database to the recovered data files in the secondary instance.

5. Import the dictionary metadata in the secondary instance to the target instance to access the recovered object.

When to use TSPITR

• TSPITR can be used in the following scenarios:

-Recover data lost due to the incorrect truncate table statement.

-Restore from logical damage to the table

-Undo only affects the results of Batch jobs or DML statements in some databases

-Restore the logical solution to a different time point from the rest of the physical database

• TSPITR uses removable tablespaces and Data Pumps to provide the following new features and features:

-TSPITR can be used to restore deleted tablespaces.

-You can execute TSPITR repeatedly to restore the table space to multiple time points before it is online, without the need to restore the directory.

Rman tspitr can be used:

• Recover data lost due to incorrect truncate table statements

• Restore from logical damage to the table

• Undo results of incorrect batch processing jobs or other data manipulation language (DML) statements that only affect database Subsets

• Restore the logical solution to a time point different from the other parts of the physical database

Before Oracle Database 11g Release 2, TSPITR exports and imports data for processing. TSPITR now uses removable tablespaces and Data Pumps. Due to this change in the underlying technology, you can use TSPITR to restore the deleted tablespace. In addition, you can execute TSPITR repeatedly to restore to different time points without the need to use the Restore directory.

Prepare for TSPITR

To prepare for TSPITR, perform the following steps:

• Determine the correct target time.

• Determine the content required for the recovery set.

• Determine and retain the objects that will be lost after TSPITR is executed.

Before executing TSPITR, you must determine the correct recovery target time. Determine whether other tablespaces are required in the recovery set. You should estimate which objects will be lost after performing the TSPITR operation, and then determine how to keep these objects.

Determine the correct target time

• TSPITR cannot be executed again unless the recovery directory is used.

• After TSPITR is executed and the tablespace is connected online, earlier backups cannot be used.

• Use the following methods to determine the correct target time:

-Flashback Query

-Flashback transaction processing Query

-Flashback VERSION Query

• A simple alternative to TSPITR: Flash back data (if it is still available for restoration ).

It is extremely important to select the correct target time or SCN when executing TSPITR. After TSPITR is executed and the tablespace is brought online, no backup can be used earlier than when the tablespace is online. In fact, this means that after selecting the wrong target time for the first time, you cannot try to execute TSPITR again unless the directory is restored. However, if the directory is restored, You can execute repeated TSPITR operations to restore to different target times.

If the Restore directory is not used, the current control file will not contain the old prototype records of the restored tablespace. When the current control file containing the tablespace is used for restoration, the backup created before the tablespace is online cannot be used. However, you can restore the database to any point in time before or equal to when the tablespace is online, the premise is that the backup control file can be restored from a time point earlier than this time point.

You can use Oracle flash back query, Oracle flash back transaction processing query, and Oracle flash back version query to view the changes made to the database and help determine the correct target time of TSPITR.

NOTE: With the flash back tool and data that can still be used to restore data, it is easier to use the flash back tool to restore unsatisfactory changes than to use TSPITR for restoration.

For more details, please continue to read the 2nd page content:

Related reading:

Oracle Undo image data exploration

Oracle ROLLBACK and Undo)

The Undo tablespace cannot be opened because it is damaged.

How to handle Undo tablespace failures

Oracle Undo tablespace reconstruction and restoration