How to find the backup that requires the least amount of archivelogs to restore

oraclerman

Oracle 11.2.0.3 with daily full backup and hourly archivelog backup. RMAN with a catalog.

If I take a backup at 11pm at night and it finishes 5am the next day and I want to restore a backup to a different system, how can I identify either a time for 'set until' or an SCN that will require the least amount of archivelogs to be replayed? 11pm? After 5am? RMAN is my least understood part of the database.

Best Answer

I have a weekly script that automates a duplicate from prod backup. WHen I inherited it, it did a duplicate until time and was highly dependent on jobs completing on schedule so it could assume a good 'until time'. I modified it to get a good scn and so duplicate 'until scn'. I get the scn with this snippet of code querying the rman catalog, in a shell script. If you are on windows, you'll need to adjust scripting technique, but principle is the same:

    # 2. Get the scn from the most recent archlog backup

#

rcvryscn=$(sqlplus -s rman/pwd@rmcat <<EOF

set echo off feedback off verify off trimsp on head off

col maxscn for 9999999999999999

select max(first_change#) maxscn

from rc_backup_archivelog_details a

join rc_database d on a.db_key=d.db_key

where d.dbid=$dwprdid;

EOF

)

Related Solutions

Use RMAN to backup and restore data in a certain point in time

There is a feature specifically for that:

 CREATE RESTORE POINT before_test1 GUARANTEE FLASHBACK DATABASE;
 select NAME,SCN,TIME from v$restore_point; 
 exec perform_my_test1;
 SHUTDOWN IMMEDIATE;
 STARTUP MOUNT;
 FLASHBACK DATABASE TO RESTORE POINT before_test1;
 DROP RESTORE POINT before_test1 ;

This is a database's feature, not RMAN's (commands are for sqlplus), see doc for the full setup and disclaimers.

To more precisely answer your question, RMAN can also be used for the task. The general script is:

run { 
  set until scn 123123123 ;
  restore database /* from tag ... */;
  recover database;
}

Don't use bare RESTORE DATABASE FROM TAG because without SET UNTIL it doesn't work as you'd expect in some scenarios.

I wouldn't say any of these two solutions are inherently safe, both have as many pitfalls as any other Oracle's advanced features, so you need to do some research upfront.

Ubuntu – How to rename a directory via the command line

mv /home/user/oldname /home/user/newname

Best Answer

Related Solutions

Use RMAN to backup and restore data in a certain point in time

Ubuntu – How to rename a directory via the command line

Related Question