Speeding up a standby recovery with incremental backup

Lately I have found a description for recovering using incremental backup. Despite speeding up a standby recovery (assuming the number of archivelogs to apply is much beyond the amount of changed data blocks) this method is useful for example for recovery from lost archivelog. I found some good articles on the topic above:

1. http://dbakerber.wordpress.com/2011/12/20/incremental-recovery-of-standby-asm-and-rman/
2. http://msutic.blogspot.com/2010/11/recover-from-incrementally-updated.html - this one is especially very precise

My personal goal was to speed up a standby recovery. I did it on the 11.2.0.2 version. I assume here disk channel, but tape is the same or even simpler as one do not need to transfer backup files from the primary to the standby host assuming centralized backup facility. The receipt is as follows:

1. make an incremental backup from SCN, at which our standby stopped (or actually it is better to backup slightly more then is needed, thus SCN should be a little lower then the really needed). This could be as simple as this:
   

   BACKUP INCREMENTAL FROM SCN  60839640548 DATABASE TAG='SCN_BEFORE_60839650548';
   

   Here a warning - this has to be a disk backup. In docs (at least for 10.2) there is stated that

   RMAN does not consider the incremental backup as part of a backup strategy at the source database.

   It vanished in later versions of docs (or at least I can not find it, but after perfoming an incremental backup to tape we were not able to list it).
2. make a copy of a current controlfile for standby:
   

   # from rman
   BACKUP CURRENT CONTROLFILE FOR STANDBY;
   # or from sqlplus
   ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS '/tmp/control01.ctl';
   

3. transfer both backups to the standby - starting from here all
4. restore control files with rman

   RESTORE STANDBY CONTROLFILE FROM '[controlfile path]';
   

   or simply replace existing control files if using sqlplus

   cp [controlfile path] [CONTROL_FILES parameter value] # for every position in CONTROL_FILES parameter of course
   

5. now mount the standby instance

   STARTUP NOMOUNT
   ALTER DATABASE MOUNT STANDBY DATABASE;
   

6. set STANDBY_FILE_MANAGEMENT to MANUAL (if set to AUTO)
7. catalog all the already existing on standby datafiles to control file

   CATALOG START WITH '[datafile path]'; # for every datafile path 
   

8. add all non-existent datafiles with sqlplus or with help of command SQL of rman

   ALTER DATABASE CREATE DATAFILE [file_id]; 
   

9. now is the time for switching the database to cataloged copies of datafiles. The simplest way is to:

   SWITCH DATABASE TO COPY;
   

   but if there were some non-existent files, which had to be added in the previous step, this will do not work. The source 2 provides a way to workaround it:

   CHANGE COPY OF DATAFILE [file_id] UNCATALOG;
   

   My way is to produce a script with a call to v$datafile:

   spool switch_files.rmn
   SELECT 'SWITCH DATAFILE '||file_id||' TO COPY;' rmn1 FROM v$datafile ORDER BY file#;
   spool off
   

   then execute it and edit the spooled content to remove unnecessary entries - among those also tries to switch lately created datafile stubs (stubs are already seen by the instance, so actually no need to switch to them).
10. now almost finish - recover database with rman - NOREDO forces rman to not use archivelogs at all

    RECOVER DATABASE NOREDO;
    

11. the rest is possibly to fix some small issues with redologs, which means usually clear logfiles and to start managed standby recovery
    • it is interesting that in order to clear standby logfiles one needs to put at work the parameter log_file_name_convert
    • it seems enabling the standby recovery may be performed at any SCN - we simply switch the recovery process from incremental backup to archived logs again. Now onwards we need to apply the logs requested by standby - assuming the managed standby, the log sequences are to be found in an alert log as entries about archive gaps.

      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
      

And now we are with automatically running standby back again :-)

Automatic TSPITR and dealing with it

The automatic TSPITR is very useful when we have an independent schema, which was logically corrupted by some of our actions. Let's say we started a batch processing, which is not a single transaction able to rollback, but rather hundreds of small changes to many tables and this processing failed being inside the run.
In the 11g database it is highly automated - theoretically one needs to run one RMAN command like below:

recover tablespace until time "to_date('2012-04-16 08:15:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/rman/aux';

Lately we use it for exactly the same reason I mentioned above - we restored a schema from a time before processing.

In the first place of course it is best not to recover at all, for example by making UNDO tablespace bigger (and I mean here a defense against ORA-01555 rather than making FLASHBACK TABLE operations possible).
However let's assume one is already in a trouble.

The automatic instance
According to our experience the most important thing is to tune the automatically run instance by providing additional settings.
The automatic instance by default runs on the following parameters:

System parameters with non-default values:
processes = 50
sga_target = 280M
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/rman/aux"
db_files = 200
db_create_file_dest = "/rman/aux"
db_name = "TEST"
db_unique_name = "xgFy_tspitr_TEST"

For us 2 things did not work well. The processes parameter was set too low - at the end of the whole TSPITR process the automatic instance failed due to exceeding the processes limit. This was probably due to some additional job processes, not sure why there were so many of them. Thus the processes value at 150 is much better setting.
Another thing to tune is the SGA_TARGET - for small transactions the default setting is probably ok - our process run smoothly through the files restore phase and majority of the recover phase, but not the whole. In our case doubling this value with setting 200M exclusively for shared pool helped, but this may vary.

What if it failed anyway
Failing the automatic/auxiliary instance is really a disaster to the TSPITR process, but we are able to run it again reusing partially the work done in the previous run. Unfortunately when failing the auxiliary instance removes all the files in auxiliary destination, which means we loose auxiliary control file and datafiles for UNDO, SYSTEM and SYSAUX.
The datafiles for recovered tablespaces are usually restored in the final production destination and they are left as of the moment of the auxiliary instance crash and this is what we could reuse, assuming the TSPITR process did not crash after open resetlogs operation. To use them we need to add to the RMAN RUN block CONFIGURE AUXNAME commands.
Depending on the TSPITR phase the whole process may fail leaving also files in the auxiliary destination, thus we are able to continue the TSPITR process manually. In such case first thing is to remove causes of the failure. One needs to create also its own pfile, where especially important is to know the value of the db_name parameter - those parameters may be taken from alert.log file of the auxiliary instance. After that we start the auxiliary instance.
There is a clone control file in use, so we need to mount database as clone database (ALTER DATABASE MOUNT CLONE DATABASE;).
Open it was a little tricky - we had to use RMAN (and not SQLPLUS) and connect as to auxiliary instance (rman auxiliary /), then simply open with resetlogs. After this moment the rest is quite simple - the best way is to follow a log from some previous TSPITR (such log may be found on Metalink for example). One needs to set the recovered files as READ-ONLY and create an alias for a dictionary to use by Data Pump (it is good to set the same dictionary as in the primary database). Then goes an expdp (expdp dumpfile= transport_tablespaces=, possibly TRANSPORT_FULL_CHECK, if needed).
Afterwards one may close the auxiliary instance, as this is not needed anymore. Then impdp to the primary database, setting tablespace to READ-WRITE again and voila - the thing is done.

Kudos to Pawel Smolarz :-)