Table PITR and the Keep Auxiliary Option

31 Aug 2023

You may have seen my blog entry about the deadly hidden Unicode characters and how they can make your life miserable during table PITR as there is a small bug in RMAN.

Now if you have a large tablespace in which the table resides, table point in time recovery can take a considerable amount of time as it creates an auxiliary instance , restores the System and Sysaux tablespaces and then restores the tablespace in which the table resides including recovering all the tablespaces to the point you require. If there is an issue with the table or the dump file location you have specified all that work can go to waste as the default action is to delete and clean up the auxiliary instance .

However there is an undocumented option in Oracle which preserves the auxiliary database and helps to troubleshoot such issues.

Sadly we only heard about this from Oracle in the post mortem after our table point in time recovery failed and then I found one, count that one blog entry in the whole of the internet that mentions this capability.

As with all undocumented options it can go away any time so use with caution and your mileage may vary.

The magic option is to add keep auxiliary at the end of your script. This will prevent automatic clean up of the auxiliary even if your operation was successful, so you will have to clean it up manually, which is why probably its not documented anywhere. It is available out of the box in 19c.

With recover table you have the option of notableimport which allows you to create a datapump export of your table that you can use to import in another instance .

RECOVER TABLE Robin.Test

UNTIL SCN <x>

AUXILIARY DESTINATION '/u01/tempdb'

DATAPUMP DESTINATION '/u01/export'

DUMP FILE 'test.dmp'

NOTABLEIMPORT

KEEP AUXILIARY

;

or you also have the option to remap the table to a new name.

RECOVER TABLE Robin.Test

UNTIL SCN <x>

AUXILIARY DESTINATION '/u01/tempdb'

REMAP TABLE 'ROBIN'.'TEST':'TESTOLD'

KEEP AUXILIARY;

Remember under the covers this is a tablespace point in time recovery followed by a data pump export and in the second case an import with a new name back into the original db.

the advantage is with the NOTABLEIMPORT option there is no write activity to your original database hence there is no chance of anything additional bad happening.

Given that something really bad has happened to panic you into recovering this table without the ability to flashback query the old data( drop table or truncate table nudge nudge wink wink) you do not want writes to be happening to the source database at this point in time.

So that you can appreciate how convoluted the process actually is I have reproduced the steps to restore a normal tablespace to a point in time, remember after all these steps there is still a data pump export to be done before the job is complete.

Hope you liked this blog entry !

From the documentation

How RMAN TSPITR Works With an RMAN-Managed Auxiliary Instance

Having selected tablespaces from the recovery set, an auxiliary destination and a target time, you are now ready to perform Fully Automated RMAN TSPITR (default). The automated mode of RMAN TSPITR shares many of these high-level processing steps.

RMAN TSPITR automatically performs the following actions:

If the tablespaces in the recovery set have not been dropped, checks to see if they are self-contained by executing the DBMS_TTS.TRANSPORT_SET_CHECK for the recovery set tablespaces and then checking that the view TRANSPORT_SET_VIOLATIONS is empty. If the query returns rows, RMAN stops TSPITR processing. You must resolve any tablespace containment violations before TSPITR can proceed. Example 21-1 shows you how to set up and run the query before invoking RMAN TSPITR.
Checks to see if a connection to a user-managed auxiliary instance was provided. If it is, then RMAN TSPITR uses it. If not, RMAN TSPITR creates the auxiliary instance, starts it, and connects to it.
Takes the tablespaces to be recovered offline in the target database, if the tablespaces in the recovery set have not been dropped.
Restores a backup control file from a point in time before the target time to the auxiliary instance.
Restores the data files from the recovery set and the auxiliary set to the auxiliary instance.
Files are restored either in the:
- Locations that you specify for each file
- Original location of the file (for recovery set data files)
- Auxiliary destination (if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE and an RMAN-managed auxiliary instance)
Recovers the restored data files in the auxiliary instance to the specified time.
Opens the auxiliary database with the RESETLOGS option.
Makes the recovery set tablespaces read-only in the auxiliary instance.
Exports the recovery set tablespaces from the auxiliary instance using the Data Pump utility to produce a transportable tablespace dump file.
Shuts down the auxiliary instance.
Drops the recovery set tablespaces from the target.
Data Pump utility reads the transportable tablespace dump file and plugs the recovery set tablespaces into the target.
Makes the tablespaces that were put in the target database read/write and immediately takes them offline.
Deletes all auxiliary set files.

At this point, RMAN TSPITR has finished. The recovery set data files are returned to their contents at the specified point in time, and belong to the target database.

The recovery set tablespaces are left offline for you to back up and then bring back online. These last steps follow Oracle's recommendation and best practice of backing up recovered tablespaces as soon as TSPITR completes."

Note that in case of Recover table the steps with the strikethrough do not occur as it uses a single table data pump export import process and not a transportable tablespace dump.