Friday, March 20, 2009

Restore database from old RMAN backup

1. As usual, you use catalog command to recatalog your backup pieces.
catalog backuppiece '/db/backup/MYDB/old_backup/your_piece_1_1';
2. Startup nomount and restore controlfile.
RMAN>restore controlfile;
3. Alter database mount and restore database
RMAN>alter database mount;
RMAN>restore database;
4. Try recover database;
It will give you the required archivelogs but you don't need them. You are just trying to restore from the old backup as it was. So check you max last archivelog in thi sbackup:
select max(sequence#) from v$log_history;
1425
Now do this:
Recover database until logseq=1426;
5. You are done.
Run:
Alter database open
6. And don't forget to create you tempfiles. RMAN backup does not have them!

ORA-01652:unable to extend temp segment

Don't rush to add additional tempfile or increase the size of existing ones if you are getting this.

If you are happy with your tempfile sizes (I usually allocate 10 to 20G to them), try increasing pga_aggregate_target. If this is not enouph, retrieve the sql from either v$sql or v$sqlarea (or better yet from the Grid Control) & see if execution plan is good. If not, tune it, otherwise recreate statistics on the tables involved. If you'd use cascade=true parameter in dbms_stats.gather_table_stats, indexes will be analyzed as well.