Fully Automated TSPITR

Couple of days ago, I have tested Automatic TSPITR feature which is introduced with version 10g. Maybe, many experts might have already discussed about this and might have written articles about this feature। However, I just thought of sharing my experience which could help to the DBA who is not yet aware of this feature or never tested this feature.

Starting with version 10g, Tablespace Point-in-Time Recovery(TSPITR) can also be done automatically, which is referred as fully Automated TSPITR.

This process requires the following two manual steps:

1. Specify the auxiliary location for RMAN to restore the auxiliary datafile/datasets
2. Configure the required channels on Target Instance.

Manual TSPITR is required the following procedure:

The basic procedure for performing user-managed TSPITR is as follows:

Take the tablespaces requiring TSPITR offline.

• Plan the setup of the auxiliary database.
• Create the auxiliary database and recover it to the desired point in time.
• Drop the tablespaces requiring TSPITR from the primary database.
• Use the transportable tablespace*** feature to transport the set of tablespaces from the auxiliary database to the primary database.

*** Probably in versions 8i and 9i, because transportable tablespace were first introduced with 8i (v. 8.1.5). Version which doesn’t have TTS, can do export and import of desired table/s.

To perform full automated TSPITR, you just need to add AUXILIARY DESTINATION in the RMAN along with recover tablespace.

Assuming that a table has been dropped from tablespace DATATS:

Example:

run
{ RECOVER TABLESPACE datats
UNTIL logseq thread 1 -- thread 1 indicates single instance
AUXILIARY DESTINATION '/u10/temp'; --Auxiliary Location
}

When the above script runs, RMAN then carries out the following steps:

If there is no connection to an auxiliary instance, RMAN creates the auxiliary instance, starts it up and connects to it.
Takes the tablespaces to be recovered offline in the target database.
Restores a backup control file from a point in time before the target time to the auxiliary instance.
Restores the datafiles from the recovery set and the auxiliary set to the auxiliary instance.
Files are restored either in locations you specify for each file, or the original location of the file (for recovery set files) or in the auxiliary destination (for auxiliary set files, if you used the AUXILIARY DESTINATION argument of RECOVER TABLESPACE).
Recovers the restored datafiles in the auxiliary instance to the specified time.
Opens the auxiliary database with the RESETLOGS option
Exports the dictionary metadata about objects in the recovered tablespaces to the target database.
Shuts down the auxiliary instance.
Issues SWITCH commands on the target instance, so that the target database control file now points to the datafiles in the recovery set that were just recovered at the auxiliary instance.
Imports the dictionary metadata from the auxiliary instance to the target instance, allowing the recovered objects to be accessed.
Deletes all auxiliary set files.

What I like about this feature is that, given the enough space to the auxiliary location, RMAN will do everything which required for TSPITR and once the process is done it will automatically obliterate the auxiliary instance, including files which restored during this process.

Its really worth to have a test about this feature.

Please note that I have not discussed about the prerequisites of performing TSPITR.

References:

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtspit003.htm#sthref778
Metalink Note : Getting Started with Transportable Tablespaces - Note:100693.1

Happy reading,
Jaffar

ALTER DATABASE BEGIN BACKUP

Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.

I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.

Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:

ALTER DATABASE BEGIN BACKUP;

ALTER DATABASE END BACKUP; -- to exit from the backup mode.

In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.

-- The following has done with Oracle 10gR1

SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

-- All the datafiles are in backup mode now

SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

-- All the datafiles are out of backup mode now.

Happy reading,

One more new Oracle Informative Site

Lately I have learnt hat my boss is maintaining a Oracle Informative Site. Upon visiting to his Oracle site, I feel good as he put a lot of stuff about Oracle (trying to put more), and also what we have done lately at our organization to resolve some of production issues, and couple of interesting articles for downloads.

I must appreciate for his work as he took some quality of time from his busiest schedule to put good stuff at his site and made it feel good site.
Although, the site is not fully done whith he wanted to have, still good to visit. He also promised me that he gonna put more stuff about RAC which we are doing on Stretched Clusters.

I am also going to contribute and participate for few activities on his site and we will put good articles written by us which could be useful in day-to-day business of a DBA and could help to resolve some of issues of production databases.

Following is the link for his site:

http://www.pinnacleoracle.com

Happy reading,

Jaffar

Known RMAN Performance Problems - Metalink Note : 247611.1

This morning, I have come across of a recently revised Metalink Note : 247611.1 (revised on 27-02.2007) which addressed several known performance problems with RMAN backup and recovery.

The note has workaround for the following bugs:

Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Note 342999.1 First Resync on Production Host After Running RMAN on
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles


Happy reading,

Jaffar