7
Miscellaneous RMAN Features

CERTIFICATION OBJECTIVES

7.01 Creating a duplicate database using RMAN

7.02 Using a duplicate database

7.03 Identify the situations that require TSPITR

7.04 Perform automated TSPITR

Two-Minute Drill

Self Test

Chapter 6 took a little detour away from RMAN to cover user-managed recovery techniques. It’s important that you know how to make backups outside of RMAN for three reasons:

The complexity of using RMAN might be overkill for a small read-only database.

A user-managed backup strategy (including logical backups) can be a fallback for RMAN-created backups in case of a corrupted RMAN backup or bug in RMAN itself (both of which are quite rare).

You need to know how to perform user-managed backups for the certification exam!

This chapter moves back into the world of RMAN and covers two important RMAN features: creating a duplicate database, and using tablespace point-in-time recovery (TSPITR).

RMAN makes it easy to make a full copy or a subset of a target database. The copied database has a new database ID (DBID) and can thus coexist on the same network as the source database. The copied database can even be on the same host and can use the same RMAN catalog as the source database because it has its own DBID. A copy of a database has many uses, including testing of backup and recovery procedures.

Using TSPITR is another recovery feature of RMAN that is useful for situations in which the damage to the database is local to a small subset of tablespaces or tables, such as a logical corruption of a table or an erroneous TRUNCATE TABLE statement. TSPITR is the ideal tool for the job when other methods such as Flashback Database or Flashback Table are not configured in your database. You could lose other committed transactions as part of the incomplete recovery process.

CERTIFICATION OBJECTIVE 7.01
Creating a Duplicate Database Using RMAN

You can use several methods to create a duplicate database, but they all use RMAN, either directly or indirectly. You use a combination of SQL*Plus and RMAN commands to create a duplicate database, or you use operating system commands if you need more control over the duplication process. Alternatively, you can use Enterprise Manager (also known as Enterprise Manager Database Control, EM for short, or the Grid Control), to automate the process using either a database backup or even a live instance.

The following sections provide an in-depth example of cloning our human resources database (hr) to a test database (hrtest) on the same host (srv04).

Using RMAN to Create a Duplicate Database

Before continuing with the example, it’s important to clarify some naming conventions. The source database is the database you want to copy. The duplicate database is the copy of your source database. The source instance is the instance RMAN will use to access the source database, as you might expect. The new instance is called the auxiliary instance during the copy operation. After the copy operation is complete, you can call it anything you want because it no longer has any connection to the source database!

The official Oracle documentation occasionally refers to the source database as the target database, and the exam will do this as well. This is a bit counter-intuitive, but is probably linked to the RMAN command line where the TARGET argument references the database you want to back up.

Here are the general steps to follow to create a duplicate database on another host:

1. Create a password file for the auxiliary instance.

2. Ensure network connectivity to the auxiliary instance.

3. Create an initialization parameter file for the auxiliary instance.

4. Start the auxiliary instance in NOMOUNT mode.

5. Start the source database in MOUNT or OPEN mode.

6. Create backups or copy existing backups and archived redo log files to a location accessible by the auxiliary instance unless you are using active database duplication.

7. Allocate auxiliary channels if necessary.

8. Run the RMAN DUPLICATE command.

9. Open the auxiliary instance.

In the scenario that follows, the source and destination server (srv04) already has the Oracle software installed as well as the existing hr database.

Configure the Auxiliary Instance

Some preparation on the destination server is required before you perform the database duplication. First, you must create a password file for the auxiliary instance, because you are going to duplicate from an active database instead of backups. You must also create a password file if your RMAN client runs on another host; however, this scenario runs the RMAN executable on the same host as the destination database. Create the password file with the same SYS password as the source database’s password file:

Note that the default location for all database password files is $ORACLE_HOME/dbs.

Establish Network Connectivity

If the source database was on a different host, you would have to ensure network connectivity to the source server. In this scenario, the source and destination are the same and no adjustments to the tnsnames.ora file are necessary. However, because the new instance is open in NOMOUNT mode, the process that registers the instance with the listener has not started yet. Thus you must manually add an entry such as the following and restart the listener:

Create an Initialization Parameter File

The next step is to create an initialization parameter file for the auxiliary instance. Only DB_NAME must be specified; all other parameters are optional, depending on whether you use Oracle Managed Files or you want to specify an alternative location for one or more file destinations. Table 7-1 lists the parameters you can specify in the auxiliary initialization file along with their descriptions and under what circumstances they are required.

TABLE 7-1 Initialization Parameters for the Auxiliary Instance.

Note that the DB_FILE_NAME_CONVERT parameter can be specified when you run the DUPLICATE command. Here is the initialization parameter file (inithrtest.ora) for the auxiliary instance created in $ORACLE_HOME/dbs:

Start the Auxiliary Instance in NOMOUNT Mode and Create an SPFILE

Using the initialization parameter file you just created, start the instance in NOMOUNT mode and create an SPFILE:

Note that you are setting the environment variable ORACLE_SID to the new instance name. This has the desired effect of creating any missing instance directories automatically, such as the diagnostic directory structure:

Note also that the newly created SPFILE resides in the default directory $ORACLE_HOME/dbs with other SPFILEs and password files.

Start the Source Database in MOUNT or OPEN Mode

If the source database is not already open, start it in MOUNT or OPEN mode. If you do not want users to access the database during the duplication process, open it in MOUNT mode:

Create Backups for the DUPLICATE Command

All datafile backups, including incremental backups and archived redo log files, must be available on a file system accessible by the auxiliary instance. In this scenario, you are performing an active database duplication; therefore, you do not have to create or copy backups for the operation.

Allocate Auxiliary Channels if Necessary

If you are using backups for the duplication process, you need to configure RMAN channels to be used on the auxiliary database instance. The channel on the auxiliary instance restores the backups, so you need to specify the ALLOCATE command in the RUN block, as in this example:

Even if your device type is DISK, you can allocate multiple channels to enable parallel processing of the backups and therefore reduce the time it takes to perform the copy.

For the purposes of this scenario, the default DISK channel on the auxiliary instance is sufficient. So you will not need to specify any additional channels for the DUPLICATE command.

Run the RMAN DUPLICATE Command

Here is the moment we’ve all been waiting for: starting RMAN and performing the duplication process. Start the RMAN executable and connect to the source database:

Next, connect to the auxiliary instance:

Of course, you can put all of the CONNECT statements on the RMAN command line:

Finally, run the DUPLICATE command with the ACTIVE DATABASE clause to perform the copy directly from the live datafiles:

RMAN conveniently creates a temporary script with all the appropriate SET NEWNAME commands and proceeds to copy the database:

In summary, here is what the DUPLICATE command does:

Creates a control file for the duplicate database.

Restores the target data files to the duplicate database or copies directly from the running database.

Performs incomplete recovery up to the last archived redo log file.

Shuts down and restarts the auxiliary instance.

Opens the auxiliary database with the RESETLOGS option.

Creates the online redo log files.

Generates a new DBID for the auxiliary database.

Here are some other options available with the DUPLICATE command:

SKIP READONLY Exclude read-only tablespaces from the copy operation.

SKIP TABLESPACE Exclude specific tablespaces, except for SYSTEM and UNDO.

NOFILENAMECHECK Don’t check for duplicate filenames between the source and destination databases.

OPEN RESTRICTED When the destination database is ready, open it immediately with the RESTRICTED SESSION option.

Using Enterprise Manager to Create a Duplicate Database

You can also use Enterprise Manager (EM) to create a duplicate database. You can use three sources to create the duplicate database with EM:

A running instance

A staging area common to the source and destination hosts, which is big enough to hold a new backup

An existing backup of the source database

Behind the scenes, EM uses a combination of SQL commands, operating system commands, and RMAN commands to perform the cloning operation. Whether you use the RMAN methods presented earlier in the chapter or the EM GUI depends on your level of expertise and comfort level with the command line, as well as how many databases you want to clone. If you need to clone several databases, it can take less time to create SQL and RMAN scripts to automate the process versus using EM repeatedly.

In the next scenario, you will use EM to clone a running database. The human resources database HR runs on the server srv04, and you will clone it to run as a third database on the same server, srv04, to create a database called HRTEST2.

From the EM home page for the source database (in this case the hr database), select the Data Movement tab and click the Clone Database link under the Move Database Files column. You will see the screen shown in Figure 7-1.

FIGURE 7-1     Selecting clone source type

Select the A Running Database and the Copy Database Files Over Oracle Net radio buttons, and then click the Continue button. In the screen shown in Figure 7-2, specify the username and password for the owner of the Oracle software on the source server, which is typically oracle.

FIGURE 7-2     Specifying host credentials for the source server

Click the Next button. In the screen shown in Figure 7-3, specify the destination Oracle home directory and host (in this case srv04), destination host credentials, and destination database name. Even if the source database is using Automatic Storage Management (ASM) or raw disks for datafile storage, you will use the file system on the destination server for the cloned database.

FIGURE 7-3     Specifying destination credentials and database name

Click Next again. The next screen, shown in Figure 7-4, estimates the disk space required for the new database. Select the Use Database Area And Flash Recovery Area radio button to enable the use of Oracle Managed Files (OMF), freeing you from explicitly naming every database object, such as archived redo log files. In addition, you can select the Customize link to refine the destination locations for the database files, control files, and online redo log files.

FIGURE 7-4     Specifying and customizing database file locations

After you click the Next button, the screen shown in Figure 7-5 appears, where you specify the location of the network files that EM will modify using the new database name. In addition, you specify the passwords for the SYS, SYSMAN, and DBSNMP accounts, as well as optionally registering this database with EM.

FIGURE 7-5     Specifying network configuration file location and passwords

Click the Next button to open the Scheduler page shown in Figure 7-6. The cloning process will run as a batch job. You can run it immediately or schedule it to run at a later time.

FIGURE 7-6     Specifying schedule options for a cloning job

Click Next to open the Review screen shown in Figure 7-7. You can optionally cancel the entire operation or go back one or more pages to correct any options.

FIGURE 7-7     Clone database job review page

If all options are specified correctly, click the Submit Job button to initiate the cloning operation. The next screen confirms the successful submission of the job; click the View Status button to monitor the status of the cloning procedure. When the job completes with no errors, the new database hrtest2 is up and running.

EXERCISE 7-1
Clone a Running Database

In this exercise, clone a database from a running instance to another database on the same server using the instructions provided previously to clone a database using EM.

CERTIFICATION OBJECTIVE 7.02
Using a Duplicate Database

A duplicate database can be used for many things, including the following:

Test backup and recovery procedures without disrupting the production database.

Test a database upgrade.

Test the effect of application upgrades on database performance.

Generate reports that would otherwise have a detrimental effect on the response time for an online transaction processing (OLTP) production system.

Export a table from a duplicate database that was inadvertently dropped from the production database, and then import it back into the production database; this assumes that the table is static or read-only.

EXERCISE 7-2
Recover a Dropped Table Using a Cloned Database

In this exercise, you will use the cloned database to recover the table HR.JOB_HISTORY. The SQL< prompt is changed in the following examples on each database using the SET SQLPROMPT command to indicate clearly what commands you execute on each database.

1. "Accidentally” drop the table HR.JOB_HISTORY in the HR database:

2. Create the Data Pump transfer directory on the source database, and then grant permissions on the directory object to the user RJB:

3. Create the same directory and permissions on the HR database (the destination database). If the databases were on different hosts, ensure that the directory object references a shared file system directory.

4. Run Data Pump export on the database containing the table to be restored (this database was originally the auxiliary database):

5. Run Data Pump import to restore the table to the source database:

6. Confirm that the table has been successfully restored:

Alternatively, you can create a database link from the HR database to the HRTEST database to restore the table. Again, this method is feasible only if the table is static or read-only. Note that you can use several other ways to recover the table, but it’s good to have yet another way to recover part or your entire database when disaster strikes.

CERTIFICATION OBJECTIVE 7.03
Identify the Situations that Require TSPITR

RMAN facilitates automatic TSPITR, making it easy to restore the contents of one or more tablespaces to a previous point in time without affecting other tablespaces or other objects in the database. TSPITR is a useful recovery tool for these scenarios:

Corruption or deletion of rows in key tables that occurs in a logically-isolated tablespace; in other words, no indexes or parent/child relationships from objects in other tablespaces.

Incorrect Data Definition Language (DDL) changes the structure of one or more tables in a tablespace, and therefore Flashback Table is not available to recover these tables.

A table is dropped with the PURGE option.

Flashback Database can perform the task, but this has two drawbacks: First, all objects in the database are rolled back (not just a tablespace you are trying to recover). Second, you must maintain flashback logs to use Flashback Database, whereas a tablespace’s TSPITR window extends back to the earliest recoverable backup for the tablespace.

TSPITR is not a cure-all for all tablespace disasters. For example, you cannot use it to recover a dropped tablespace. You also cannot recover a renamed tablespace to a point in time before it was renamed.

Before we delve into an in-depth example in the next section, you should understand the following terminology:

Target time The point in time or SCN to which the tablespace will be recovered.

Recovery set The group of datafiles containing the tablespace(s) to be recovered.

Auxiliary set Other datafiles required to recover the tablespace(s), such as the datafiles for the SYSTEM, UNDO, and TEMP tablespaces.

Auxiliary destination A temporary location to store the auxiliary set of files, including online and archived redo log files, and a copy of the control file created during the recovery process.

The key to TSPITR is an auxiliary instance to facilitate the recovery process, as covered earlier in this chapter. The auxiliary instance does the work of restoring a backup control file from before the desired point in time, restores the recovery set and the auxiliary set from the target database, and finally recovers the database for the auxiliary instance to the desired point in time. Here is a complete list of steps that RMAN performs during TSPITR:

1. Restores a backup control file to the auxiliary instance

2. Restores the data files for the recovery set to the target database

3. Restores the data files for the auxiliary set to the auxiliary instance

4. Recovers the data files to the desired point in time

5. Exports dictionary metadata for the recovered tablespace to the target database

6. Issues SWITCH commands on the target database to reference the recovered data files in the recovery set

7. Imports dictionary metadata from the auxiliary instance to the target instance so that the target instance can access the recovered objects

8. Opens the auxiliary database with the RESETLOGS option

9. Exports dictionary metadata for the recovery objects to the target database

10. Shuts down the auxiliary instance

11. Imports the dictionary metadata from the auxiliary to the target

12. Deletes all auxiliary files

CERTIFICATION OBJECTIVE 7.04
Perform Automated TSPITR

In the following scenario, your HR database includes a tablespace BI containing a key table SALES_RESULTS_2008Q1. The table is accidentally dropped around 9:58 P.M. on 7/19/2008. You conclude that the table, its indexes, and any related tables all reside in the tablespace BI. So using TSPITR is a viable option for recovering this table.

Be sure to select the correct time for TSPITR. Once you recover the tablespace and bring it online, you can no longer use any previous backups unless you are using a recovery catalog.

In the following sections, you’ll perform some preliminary steps before the actual recovery operation. First, you’ll make sure that no other tablespaces have dependencies on the tablespace to be recovered. In addition, you’ll determine whether any other objects will be lost in the target tablespace when it is recovered (any objects created after the desired point in time). Finally, you’ll perform automated RMAN TSPITR, the Oracle-recommended option.

Verifying Tablespace Dependencies

Other tablespaces may have objects with dependencies on objects in the tablespace to be recovered. Use the data dictionary view TS_PITR_CHECK to identify any dependencies, as in this example:

To resolve any issues found, you can either temporarily remove the dependencies or add the tablespace containing objects with dependencies to the recover set. You are better off with the latter, however, to ensure that your tables remain logically consistent with one another.

Identifying Objects Lost after TSPITR

In addition to resolving any dependencies with the tablespace to be recovered, you also need to identify any objects created after the target time that will be lost if you recover the tablespace. You can use the data dictionary view TS_PITR_OBJECTS_TO_BE_DROPPED to determine which objects you will lose after your target recovery time, as in this example:

To resolve these issues, you can export individual objects before the recovery operation and then import them after recovery is complete. In this case, you determine that the table SALES_RESULTS_2007_TEMP was a temporary table and you don’t need it after the recovery operation.

Performing Automated TSPITR

The most straightforward method for running TSPITR is, not surprisingly, the RMAN fully automated method. In this case, you run two RMAN commands to back up and bring the recovered tablespace (and the table SALES_RESULTS_2008Q1) online. In this example, you will recover the BI tablespace to July 19, 2008, as of 9:55 P.M.:

Note that the RMAN RECOVER command cleans up the temporary instance but does not bring the BI tablespace back online. So, back it up and bring it back online:

CERTIFICATION SUMMARY

This chapter covered two short but important RMAN topics: creating a duplicate database, and performing tablespace point-in-time recovery (TSPITR). The topics seem unrelated but have one very important thing in common: they both rely on creating an auxiliary instance for performing the requested task. After duplicating a database, RMAN keeps the temporary instance and the database; in contrast, in the case of TSPITR, RMAN drops the auxiliary instance after performing the recovery operation.

Duplicating a database using RMAN is mostly automated except for a few manual setup steps. These steps include tasks such as creating a password file, setting up network connectivity, and creating a text-based initialization parameter file with only a few required parameters, such as DB_NAME. Specifying other initialization parameters is dependent on whether you explicitly specify DB_BLOCK_SIZE in the source database, and how much pathname mapping you need to do to specify locations correctly for datafiles, control files, and online redo log files. Using Enterprise Manager (EM) to duplicate a database is the easier method but may not provide the customization available using RMAN and SQL command line commands.

The second half of the chapter covered TSPITR. A variety of recovery techniques are available to you. Which one you use depends on the type of damage to your database. If the corruption or damage is limited to tables in one tablespace with few or no dependencies to objects in other tablespaces, TSPITR is likely your best option. In addition, the database and all other tablespaces remain available to users during the TSPITR operation.

TWO-MINUTE DRILL

Creating a Duplicate Database Using RMAN

When you duplicate a database, the source database is copied to the duplicate database.

The source database is also known as the target database.

The duplicate database is also known as the auxiliary database.

Preparing to create a duplicate database includes creating a password file, ensuring network connectivity, and creating an initialization parameter file for the auxiliary instance.

At a minimum, you need to specify the DB_NAME in the auxiliary instance’s initialization parameter file. You must also specify DB_BLOCK_SIZE if it is set explicitly in the target database.

The initialization parameter DB_FILE_NAME_CONVERT specifies the file system mapping for datafile and tempfile names.

The initialization parameter LOG_FILE_NAME_CONVERT specifies the file system mapping for online redo log files.

The initialization parameter CONTROL_FILES specifies the new names for all control files, unless you are using Oracle Managed Files (OMF) as OMF will name files for you.

The RMAN command for performing database duplication is DUPLICATE TARGET DATABASE.

You can specify FROM ACTIVE DATABASE in the DUPLICATE command to create the copy from an online database instead of from a database backup.

The duplicate database has a new DBID, even if it has the same database name as the source database.

Using a Duplicate Database

A duplicate database can be used to test backup and recovery procedures without affecting the availability of the source database.

You can use a duplicate database to test a database upgrade or test performance of application upgrades.

You can export one or more tables from a duplicate database and import it back into the production database as a secondary recovery method.

Identify the Situations that Require TSPITR

TSPITR is useful when one or more corrupt or missing tables are isolated to a single tablespace and have minimal or no dependencies with objects in other tablespaces.

You can use TSPITR when DDL changes prohibit the use of Flashback Table to recover the objects.

You cannot use TSPITR to recover a dropped tablespace or to recover a renamed tablespace to a point in time before it was renamed.

Perform Automated TSPITR

The target time is the point in time to which the tablespace(s) will be recovered.

The recovery set is the group of datafiles containing the tablespaces to be recovered.

The auxiliary set is a set of other datafiles required to recover the datafiles in the recovery set, such as the SYSTEM tablespace.

The auxiliary destination is a temporary work area used by RMAN to store auxiliary set files, log files, and a copy of the control file.

RMAN drops the auxiliary instance and deletes all auxiliary files at the completion of TSPITR.

You use the data dictionary view TS_PITR_CHECK to discover dependencies between the objects in the tablespace to be recovered and objects in other tablespaces.

You use the data dictionary view TS_PITR_OBJECTS_TO_BE_DROPPED to determine objects you will lose after TSPITR completes.

You perform TSPITR in RMAN using the RECOVER TABLESPACE command with the UNTIL TIME and AUXILIARY DESTINATION clauses.

After TSPITR is complete, you need to back up the recovered tablespace and bring it back online.

SELF TEST

The following questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, because there might be more than one correct answer. Choose all correct answers for each question.

Creating a Duplicate Database Using RMAN

1. Identify the correct statement regarding duplicate databases created with RMAN.

A. RMAN copies the source database to the target database and both can have the same name.

B. RMAN creates an auxiliary instance for the duration of the copy operation and drops it after the copy operation is complete.

C. The auxiliary database is the same as the target database.

D. RMAN copies the database from the target to the duplicate database and both can have the same name.

E. The source database must be shut down before you can start up the destination database.

2. To create a duplicate database, put the following steps in the correct order:

1. Start the auxiliary instance as NOMOUNT.

2. Allocate auxiliary channels if necessary.

3. Run the RMAN DUPLICATE command.

4. Create a password file for the auxiliary instance.

5. Ensure network connectivity to the auxiliary instance.

6. Open the auxiliary instance.

7. Start the source database in MOUNT or OPEN mode.

8. Create an initialization parameter file for the auxiliary instance.

9. Create backups or copy existing backups and archived log files to a common location accessible by the auxiliary instance.

A. 5, 4, 8, 1, 7, 9, 3, 2, 6

B. 4, 5, 8, 1, 7, 9, 2, 3, 6

C. 4, 5, 8, 1, 7, 9, 3, 2, 6

D. 5, 4, 1, 8, 7, 9, 2, 3, 6

3. Which of the following clauses is not valid for the RMAN DUPLICATE command?

A. SKIP OFFLINE

B. SKIP READONLY

C. SKIP TABLESPACE

D. NOFILENAMECHECK

E. OPEN RESTRICTED

Using a Duplicate Database

4. Identify reasons for creating a duplicate database. (Choose all correct answers.)

A. You want to test a new application’s performance with no impact to the production database.

B. You want to perform TSPITR.

C. You want to test an upgrade from Oracle Database 11g R1 to Oracle Database 11g R2.

D. You want to test backup and recovery procedures.

Identify the Situations that Require TSPITR

5. You can use TSPITR for which of the following scenarios? (Choose two answers.)

A. You accidentally drop the USERS tablespace.

B. You dropped two columns in a table.

C. You renamed a tablespace and want to recover the tablespace with the old name.

D. A user deleted most of the rows in a table that does not have any dependencies on objects in other tablespaces.

Perform Automated TSPITR

6. Identify the correct statement regarding TSPITR terminology.

A. The auxiliary set contains all datafiles for recovery except the SYSTEM and UNDO tablespaces.

B. The recovery set is the group of datafiles containing the tablespace(s) to be recovered.

C. The auxiliary destination is the permanent location for the auxiliary instance’s datafiles.

D. The target time is any time after the corrupted tables were modified or tables were accidentally dropped.

7. Identify the data dictionary view you can use to check for object dependencies between tablespaces.

A. TS_PITR_CHECK

B. TS_PITR_DEPENDENCY_CHECK

C. TS_PITR_CHECK_DEPENDENCY

D. TS_PITR_OBJECTS_TO_BE_DROPPED

8. Identify the steps you must perform manually after using automated TSPITR in RMAN. (Choose all that apply.)

A. Bring the recovered tablespace(s) online.

B. Back up the recovered tablespace(s).

C. Delete the temporary files created in the auxiliary location.

D. Create a text-based initialization parameter file for the auxiliary instance.

SELF TEST ANSWERS

Creating a Duplicate Database Using RMAN

1. D. You can keep the same name because RMAN creates a new DBID and therefore you can use the same recovery catalog for both databases.

A is wrong because the target database is the same as the source database. B is wrong because RMAN does not drop the auxiliary instance or database after the copy operation is complete. C is wrong because the target database is the source database and the auxiliary database is the destination database. E is wrong because both databases can be open at the same time, even on the same host and with the same recovery catalog.

2. B. These steps are in the correct order.

A, C, and D are in the wrong order.

3. A. The SKIP OFFLINE option is not valid for the DUPLICATE command.

B is wrong because the SKIP READONLY clause excludes read-only tablespaces. C is wrong because SKIP TABLESPACE excludes one or more tablespaces from the copy operation; you cannot skip the SYSTEM or UNDO tablespaces. D is wrong because NOFILENAMECHECK doesn’t check for duplicate filenames between the source and destination. E is wrong because OPEN RESTRICTED opens the destination database with the RESTRICTED SESSION option.

Using a Duplicate Database

4. A, C, and D. These are all valid reasons to create a duplicate database.

B is incorrect because you cannot use a duplicate database to perform TSPITR.

Identify the Situations that Require TSPITR

5. B and D. You can use TSPITR to recover from DDL changes to a table; in addition, you can recover a table that has corrupted or erroneously altered rows. TSPITR is also useful if the table is dropped with the PURGE option and therefore not reclaimable from the recycle bin.

A is wrong because you cannot use TSPITR for dropped tablespaces. C is wrong because you cannot use TSPITR to recover the tablespace with a previous name; in other words, the tablespace was renamed at some point in the past.

Perform Automated TSPITR

6. B. RMAN creates the recovery set and uses it in the auxiliary instance.

A is wrong because the auxiliary set does include the SYSTEM, UNDO, and TEMP tablespaces, if required. C is wrong because the auxiliary set is deleted after the recovery operation is complete. D is wrong because the target time is any time before the corruption or deletion of table rows.

7. A. You use the view TS_PITR_CHECK to identify object dependencies between one or more tablespaces and all other tablespaces in the database.

B and C are wrong because there are no such views in the data dictionary. D is wrong because TS_PITR_OBJECTS_TO_BE_DROPPED is used to identify other objects that you can lose if you perform TSPITR.

8. A and B. These steps must be performed manually after RMAN completes the automated portion of TSPITR.

C is wrong because RMAN automatically shuts down the auxiliary instance and removes all temporary files used for the recovery operation. D is wrong because you need to manually create a text-based initialization parameter file for duplicating a database, but not for TSPITR.