• List the actions that occur when you perform a table space point-in-time restore (TSPITR) • Illustrate the definition of terms used by Tspitr • Determine the conditions that are appropriate for using TSPITR as a solution • Determine the correct target time for point-in-time recovery • Identify situations where tspitr cannot be used and how to resolve them · Perform an automatic tspitr
- Tablespace Point-in-time recovery (TSPITR): Concept
• By executing TSPITR, you can quickly restore one or more table spaces to a previous time. • Executing TSPITR does not affect the state of other table spaces or objects in the database. Use Rman automatic tablespace point-in-time recovery (TSPITR) to quickly restore one or more table spaces in Oracle DB to a previous time without affecting the state of other tablespaces and objects in the database.
- Tablespace Point-in-time recovery (TSPITR): Terminology
• Target time: The point in time or SCN recovery set: The data file that makes up the tablespace to be recovered • Auxiliary set: A data file that is required to perform TSPITR on a recovery set, but is not part of a recovery set. Auxiliary sets typically include: –system tablespace – Restore Segment tablespace – temporary tablespace • Secondary target: The disk location where the file is stored the following terms are used when discussing TSPITR: • Target Time-The point in time or system change number (SCN) that the tablespace will revert to during the execution of the TSPITR. • Recovery set: A data file that makes up the tablespace to be recovered. • Auxiliary set: A data file that is required to perform TSPITR on a recovery set, but is not itself part of a recovery set. Auxiliary sets typically include:-a copy of the system tablespace-a data file containing the restore segments from the target instance-temporary tablespace used during the export of database objects from a secondary instance in some cases • Secondary target: Any auxiliary set data files that store a secondary instance on disk during Tspitr execution Control the location of files and online logs. Files stored in the secondary target can be deleted after the TSPITR is executed.
- Tablespace Point-in-time recovery: architecture
The following TSPITR entities are shown in the chart: • Target database: Contains the tablespace to be recovered • Control file: Provide backup information to Rman • Backup set: From the target database, as the source of the rebuilt tablespace • Archive redo log: From the target database, used as the source of the Rebuild table space · Secondary instances: Oracle DB instance Rman used to perform recovery during recovery performs the following steps during a tablespace point-in-time restore: 1. Restore the backup control file at a point in time before the target time to the secondary instance. Restores the data files of the recovery set to the target database and restores the data files of the secondary sets to the secondary instances. 2. Restore the restored data file to a specified point in time. 3. Export the dictionary metadata of the objects in the recovered tablespace to the target database. 4. Issue a switch command on the target database so that the target database control file points to the data file in the recovered set of recovery sets on the secondary instance. 5. Import the dictionary metadata from the secondary instance into the target instance so that the recovered object can be accessed.
Tspitr can be used in the following situations: – Recover data lost due to an incorrect TRUNCATE TABLE statement – Recover from table logical corruption – Undo the result of a batch job or DML statement that affects only a subset of the database – Restore the logical scheme to a different point in time from the rest of the physical database · Tspitr uses removable tablespace and data pumps to provide the following new features and features: –tspitr can be used to recover deleted tablespace – multiple points of time that can be repeatedly performed tspitr revert to the table space before being brought online without using the recovery catalog RMAN Tspitr available: • Recover data lost due to an incorrect TRUNCATE TABLE statement • Recover from table logical corruption • Undo the result of an incorrect batch job or other data manipulation language (DML) statement that affects only the subset of the database • Restore the logical scheme to a point in time different from the rest of the physical database in Oracle Prior to Database 11g Release 2, TSPITR was processed using export and import. TSPITR now uses removable tablespaces and data pumps. Due to this change in the underlying technology, it is now possible to use TSPITR to recover deleted table spaces. In addition, you can repeatedly perform tspitr recovery to a different point in time without using the recovery directory.
To prepare for TSPITR, follow these steps: • Determine the correct target time. • Determine what is needed in the recovery set. • Identify and retain objects that will be lost after executing tspitr. Before executing the TSPITR, you need to determine the correct target time for the recovery. You need to determine if additional tablespace is required for the recovery set. You should estimate which objects will be lost after performing the tspitr operation, and then determine how to preserve them.
- Determine the correct target time
• Tspitr cannot be executed again unless the recovery directory is used. • After you execute tspitr and bring the tablespace online, you cannot use earlier backups. • Use the following methods to determine the correct target time: – Flashback Query – Flashback transaction Query – A simple workaround for Flashback version Query TSPITR: Flashback data (if still available as restore data). It is extremely important to choose the correct target time or SCN when performing tspitr. After you execute TSPITR and bring the tablespace online, you cannot use any backups that are older than when the table space is online. In fact, this means that the wrong target time cannot be attempted again after the first selection of the TSPITR, unless the recovery directory is used. However, if the recovery directory is used, you can recover to a different target time by performing a duplicate tspitr operation. If you do not use the recovery catalog, the current control file will not contain the old prototype records for the recovered tablespace. When you restore using the current control file that contains the tablespace, you cannot use the backup that was created before the table space was brought online. However, by recovering the database to any point in time that is earlier than or equal to the table space, you can perform an incomplete recovery of the entire database, provided that you can restore the backup control file from a point in time that is earlier than that time. You can use Oracle Flashback queries, Oracle Flashback transaction queries, and Oracle Flash back version queries to view changes to the database to help determine the correct target time for TSPITR. Note: With the Flashback tool and data that can still be used as the restore data, it is easier to use the Flashback tool to restore unsatisfactory changes than to use TSPITR.
- Determining the tablespace of a recovery set
• Use the Ts_pitr_check view to identify relationships across the recovery set boundaries. • If you want to restore an object in a tablespace to a relationship with an object in another tablespace, you can: – Add a tablespace containing related objects to the appropriate recovery set – suspend the relationship during Tspitr execution – delete the relationship • Use Dbms_tts. Transport_set_check process to determine whether the table space in the recovery set is self-contained. You can use the Ts_pitr_check view to identify any other objects if you are unsure whether you have considered all objects that are related to objects in the tablespace that you want to perform TSPITR operations on. When you query this view, information about any objects that prevent Tspitr from continuing execution is displayed. The reason that tablespace point-in-time recovery cannot continue is displayed in the REASON (reason) column of the Ts_pitr_check view. For example, if you intend to perform tspitr on the users and example table spaces, determine whether there is still a relationship to the objects in other tablespaces that are not considered, by executing the following query: SELECT * from SYS. Ts_pitr_check WHERE (Ts1_name in (' Users ', ' EXAMPLE ') and Ts2_name not in (' users ', ' EXAMPLE ')) OR (Ts1_name not in (' users ', ' EXAMPLE ') and Ts2_name in (' USERS ', ' EXAMPLE '));
- Determine relationships between objects across recovery set boundaries
You must determine the recovery set before executing tspitr. If the objects in the tablespace you are recovering are associated with objects in other tablespaces, you need to provision those objects. In versions prior to Oracle Database 11g Release 2, the Sys.ts_pitr_check view was used to determine the relationship between objects across the recovery set boundary. Now you should use Dbms_tts. Transport_set_check the process and queries the Transport_set_violations view. Dbms_tts. Transport_set_check (' USERS ', ' EXAMPLE '); SELECT * from Transport_set_violations; Note: RMAN Tspitr automatically executes Dbms_tts for the recovery set table space. Transport_set_check the process and verifies that the query against Transport_set_violations did not return rows. If the query returns a row, RMAN stops TSPITR processing, and all violations of tablespace containment requirements must be resolved before the TSPITR can continue. You can also act as a precautionary measure to execute the procedure as described above and query the view.
- Identify objects that will be lost
• Objects created in the table space after the target recovery time are lost. • Query ts_pitr_objects_to_be_dropped to determine which objects are lost after the TSPITR is executed. • Use export and import to preserve and recreate lost objects before and after executing tspitr. Queries the ts_pitr_objects_to_be_dropped view to determine whether there are objects that are lost after the time-point recovery of the table space is performed. For example, to perform TSPITR on the users and example table spaces, the target time is April 3, 2006 8:30:00. Determine if there are any objects that will be lost after executing TSPITR by issuing the following query: SELECT OWNER, NAME, Tablespace_name,to_char (Creation_time, ' Yyyy-mm-dd:hh24:mi: SS ') from Ts_pitr_objects_to_be_droppedwhere tablespace_name in (' USERS ', ' EXAMPLE ') and Creation_time >to_date (' 2006-apr-03:08:30:00 ', ' Yy-mon-dd:hh24:mi:ss ') ORDER by Tablespace_name, Creation_time;
- Perform basic Rman Tspitr
• Fully automated tspitr– specify the secondary target. –rman will manage all aspects of TSPITR. – This is the recommended method. • Custom tspitr– with auto-assisted instances This is done on the basis of fully automated tspitr execution. – Customize the file location. – Specify initialization parameters. – Specify the channel configuration. • Use the tspitr– of your own secondary instance to configure and manage the secondary instances. The following options are available when executing TSPITR: • Fully automated TSPITR: Specifies the secondary target, and RMAN manages all aspects of the TSPITR operation. This is the simplest way to execute TSPITR, which is recommended unless you explicitly indicate that you need to control the location of the recovery set after you perform TSPITR, or control the location of the secondary set file during Tspitr execution, or you need to control the channel configuration or some other aspect of the secondary instance. • Custom TSPITR:TSPITR using an automatic helper instance is based on the behavior of a fully automated tspitr, and may still use the secondary target. You can customize one or more aspects of its behavior, such as the location of an auxiliary set file or a recovery set file. You can specify initialization parameters or channel configurations for the secondary instances that Rman creates and manages. • Use the tspitr of your own secondary instance: Set up, start, stop, and clear the secondary instance used in Tspitr. In addition, you can manage the TSPITR process with some of the methods in custom tspitr that use auto-assisted instances.
- Perform a fully automatic execution of the TSPITR
1. Configure the channels required to execute TSPITR on the target instance. 2. Use the Auxiliary destination option to specify the secondary target. rman> CONNECT targetrman> RECOVER tablespace users, example> UNTIL time ' 2007-06-29:08:00:00 ' > Auxiliary DEST Ination> '/u01/app/oracle/oradata/aux '; 3. Back up the recovered tablespace and bring them online. Rman>backup tablespace users, example; Rman> SQL "ALTER tablespace users, example ONLINE"; When performing a fully automated tspitr, you must also: • Configure any channels that are required to perform TSPITR on the target instance • Specify the target of Rman for the data file auxiliary set and other secondary instance files after the Tspitr is complete, back up the recovered tablespace and bring them online. After you execute TSPITR, you cannot use a backup of the tablespace that was created before TSPITR to participate in tspitr execution. Note: This time format assumes that Nls_date_format is set to ' Yyyy-mm-dd:hh24:mi:ss ' and Nls_lang is set to American_america. we8mswin1252.
- Improve Rman Tspitr performance with image replicas
CONFIGURE Auxname replaced by set NEWNAME of the recovery set data file: Run{set NEWNAME for datafile ' $ORACLE _base/oradata/orcl/users01.dbf ' to '/ BACKUP/USERS01.DBF '; RECOVER tablespace users UNTIL SEQUENCE 1300 THREAD 1;} You can improve the performance of TSPITR by instructing Rman to use the existing image copy of the recovery set and the secondary set data files. This technique enables Rman to skip the step of recovering data files from a backup. In versions prior to Oracle Database 11g Release 2, the following techniques were used to specify an existing image copy of the data file: • Copy the image of the recovery set data file or auxiliary set data file using the Configure Auxname command • An image copy of the secondary set data file, Using the Set newname command now, you should only use the Configure Auxname command for an image copy of the secondary set data file. You should use the Set newname command for a recovery set data file or an image copy of an auxiliary set data file.
- Execute TSPITR with Oracle Enterprise Manager
You can also use Oracle Enterprise Manager to perform tspitr. Navigate to Availability > Perform Recovery (Availability > Perform recovery). In the user Directed Recovery (recovery by users) section, select Tablespaces (tablespace) from the Recovery scope (recovery Range) drop-down menu. You can perform three operations on a tablespace: • Revert to the current time or a point in time: Restores the tablespace's data file if necessary. This action reverts to the specified time by redo: the current time or a time in the past. This action is a combination of the following two operations. • Restore Tablespace: Restores only the tablespace's data files. No recovery operation is performed. • Restore from previously restored tablespace: Perform recovery only for tablespace data files (Redo application).
Steps for RMAN to perform TSPITR: 1. Create a secondary instance, launch the instance, and then connect to the instance 2. Take the table space that you want to recover offline 3. Restore the backup control file at a point in time before the target time to secondary instance 4. Restores the data files of the recovery set and the secondary set to the secondary instance 5. Restores the restored data file to a specified time of 6. Use the Resetlogs option to open secondary database 7. Exports the dictionary metadata for objects in the recovered Tablespace to target database 8. Turn off secondary instance 9. Import the dictionary metadata from the secondary instance to the target instance 10. Deleting all the auxiliary set files the recovery set data file reverts to the state at the specified target time.
- Secondary instances using Rman management
Execute Rman tspitr to rename or relocate the recovery set data file. • Specify a location that is different from the secondary target for some or all of the secondary set data files. • Create an image copy backup of the data file before executing tspitr. • Use a different channel configuration for the secondary instance. • Specify different initialization parameters for the secondary instances managed by Rman. If you want to customize Rman Tspitr, you can use a secondary instance managed by Rman and make the following changes: • Use set NewName to rename the recovery set data files so that they are not restored and restored in the original location. • Control the location of the secondary set data file by using Set NewName to specify a new name for each file and using Db_file_name_convert to provide rules for converting the data file name in the target database to the data file name of the secondary database. • To improve the performance of the Rman Tspitr, use the recovery set data files on disk and the existing image copies of the secondary set data files instead of restoring them from the backup.
- Use your own secondary instance to perform Rman Tspitr
• Not recommended, but supported • Please perform the following steps: 1. Create an Oracle password file for the secondary instance. 2. Create an initialization parameter file for the secondary instance. 3. Verify the Oracle Net connection to the secondary instance. 4. Start the secondary instance in Nomount mode. 5. Connect the Rman client to the target instance and the secondary instance. 6. Execute the Recover tablespace command. Oracle recommends that Rman be used to manage the creation and deletion of the secondary instances that are utilized during the execution of Rman Tspitr. However, we also support the creation and use of our own helper instances. To create an Oracle instance that is appropriate for use as a secondary instance, follow these steps: 1. Use the Orapwd utility to create an Oracle password file for the secondary instance. 2. Create a text initialization parameter file for the secondary instance. 3. Verify the Oracle Net connection to the secondary instance using a valid network service name. To perform TSPITR, complete the following steps: 4. Start the secondary instance in Nomount mode. 5. Connect the Rman client to the target instance and the secondary instance. 6. Execute the Recover tablespace command.
- Troubleshooting RMAN Tspitr
• File name conflicts: Ensure that there is no file name conflict when using Set NEWNAME, CONFIGURE auxname, and Db_file_name_convert. Rman does not recognize tablespaces that contain restore segments: Use the Undo tablespace clause. • Restart the manual helper instance after a TSPITR failure: Shutdown and restart in Nomount mode. File name conflicts: If you use Set NEWNAME, CONFIGURE Auxname, and Db_file_name_convert to cause multiple files in the secondary set or recovery set to have the same name, you will receive an error during tspitr execution. To correct the problem, you need to specify different values for these parameters to avoid duplicate names. Rman does not recognize tablespaces that contain restore segments: During the execution of TSPITR, Rman needs information about which tablespace contains the Tspitr target time restore segment. This information is typically located in the recovery directory (if a recovery directory is used). If there is no recovery directory, or if there is no such information in the recovery directory, Rman continues to perform the operation and assumes that the set of tablespace containing the target time restore segment is the same as the set of tablespaces containing the current time restore segment. If this assumption is incorrect, the TSPITR operation fails and an error is reported. To prevent such errors from occurring, provide a list of tablespaces containing the target time restore segments in the Undo tablespace clause. Restarting a manual secondary instance after a TSPITR failure: If you manage your own secondary instance and Tspitr fails, you must close the secondary instance, correct the problem, and re-place the secondary instance into Nomount mode before retrying the TSPITR.
Source: http://blog.csdn.net/rlhua/article/details/12659769
