Monday, October 19, 2009

Autonomous RMAN online backup



Regardless there are many topics written about RMAN and way of making a lot of variety of backup strategy (scenarios), for me a nice approach was a challenge to create autonomous consistent backup. "Autonomous consistent backup" stand for backup which could be run in any moment on any configuration, with 100% success its own consistency against parent database using its own files and no other external resource (like RMAN catalog for an example). This backup can be used for testing as well for some reporting purpose.

Autonomous consistent backup is typically created as a “point in time” process based on some periods (monthly, quarterly, yearly etc.) in a case when company data backup retention policy fail beyond supported time (company does not save so many backups in the past) and it is based on ad hock basis… regardless time of run or configuration of database.

Except condition that database must be in archive log mode, main problem is always the same: how to determine which archived redo log files should we backup along with data files to restore database from this backup set? Here is an example one way of treating this issue.

Even thought, we could find dynamically number of groups and its members, just to make easy of understanding the procedure I'll use fixed values. In my case I’ll show solving the problem with four groups with two members in each group (number of members are not important but only number of groups). This value is easy to get:
select distinct THREAD# from v$log order by 1;
THREAD#
----------
1
2
3
4
If you use RAC then you can get the same result through gv$log view in a way:
SELECT THREAD# FROM gv$log WHERE inst_id = THREAD#;
But I see no advantage in this query so probably the best way best way is to use v$log in all situations, whether is RAC or single instance database.

Basic idea of the whole example, which can be easy copied in bash script (same may be done on Windows as well with some changes in script), should be like this:
   1. Find all redo log’s minimum sequence numbers in moment before starting of backup
   2. Switch archive log
   3. Backup database
   4. Switch archive log
   5. Find all redo log’s maximum sequence numbers
   6. Backup archived log files created between min and max sequence numbers

1. Find all redo log’s minimum sequence# numbers in moment before starting of backup
Finding minimum sequence# is doen through easy sqlplus command:
select  MIN(sequence#) from v$log where thread#=1;
select  MIN(sequence#) from v$log where thread#=2;
select  MIN(sequence#) from v$log where thread#=3;
select  MIN(sequence#) from v$log where thread#=4;
In bash script all together for four threads looks like:
MIN1=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=1;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MIN2=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=2;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MIN3=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=3;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MIN4=`echo "select 'xX '||MIN(sequence#) from v\\$log where thread#=4;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
Interesting part is how easy is in bash script to read result from sqlplus prompt back and forth.

2-4 Switch archive log, backup database, switch archive log
Here is classic RMAN script with some difference from classic backup scripts at the begging and at the end:
rman target / nocatalog log=autonomous_backup.log
run {
sql "alter system archive log current";
backup database include current controlfile;
sql "alter system archive log current";
restore database preview;
}
Do not use other command then “alter system switch logfile” on RAC environment. Command "restore database preview" gives you comprehensive explanation about all necessary files and data for this backup-very useful for restore/recover part in the future.

5 Find all redo log’s maximum sequence# numbers
Now it easy to repeat step from step 1 with assigning values in different variables in a slightly changed script.
MAX1=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=1;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MAX2=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=2;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MAX3=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=3;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`
MAX4=`echo "select 'xX '||MAX(sequence#) from v\\$log where thread#=4;" | sqlplus -s "/ as sysdba" | grep "xX" | awk '{print $2}'`

6 Backup archived log files created between min and max sequence# numbers
Now is RMAN archived log backup part where we use previously collected MINx and MAXx values:
rman target / nocatalog log=autonomous_backup_arch.log
run {
backup archivelog sequence between ${MIN1} and ${MAX1} thread 1;
backup archivelog sequence between ${MIN2} and ${MAX2} thread 2;
backup archivelog sequence between ${MIN3} and ${MAX3} thread 3;
backup archivelog sequence between ${MIN4} and ${MAX4} thread 4;
}
In all RMAN scripts I didn't wanted to place "allocate channel..." commands because this depends on your default RMAN configuration. Put it before all operation if you need it.

As you can see all scripts are run with nocatalog switch even thought it can run against the catalog as well. This solution works against RAC as well in non RAC database.

To make this script work put all together in one .sh file and run as ora_dba user with appropriate environment. If will be more interest for this topic, I can post bash script which will dynamically examine number of groups in for loop instead of (as in this example) fixed 4 values.

10 comments :

  1. This is my first blog....just for the record.

    ReplyDelete
  2. Hi Damir ,this is wonderful example of a rare and specific but at the same time very useful
    database backup procedure,so thanks for sharing it.
    When you catch some free time i would be grateful if you could you also add
    some recovery example based on this kind of backup strategy.
    cheers,
    Toni Lazarin

    ReplyDelete
  3. Toni,

    restore controlfile from 'piece';
    restore database from tag="";
    recover database noredo;

    this will do a point in time recovery and ignore any archivelogs that are known to the controlfile. hence i guess it expects the database to be opened with resetlogs.

    Anyway, you dont want your recovery script look at your FRA or archivelogs generated there after the backup. And that leads the recovery to be cancelled at some point in time back hence resetlogs should be done in your case.

    Hope this helps,
    Damir Vadas

    ReplyDelete
  4. Hi Damir,

    Recover database noredo command if it ignores the archivelog wont that leave the datafile header scn in inconistent state. alter database
    open resetlogs will still complain that
    datafile is not consistent.

    dont we need to apply
    atlease one archivelog to make the headers consistent? Please let know

    Thanks,

    ReplyDelete
  5. Recover database with "noredo" is an option when you have consistent (AKA cold) backup where no data are saved in redo logs files.
    If you have online (AKA hot) backup then in your redologs might be some important information. But this depends on point where you want to restore/recover.
    For complete recover do not use "noredo". If you use "noredo" then your recover is incomplete (you loose information store in redolog) and you might fall in problem if your restore point is somewhere "in the middle" of that-depends on redo logs inforation.
    Anyway, if you perform recover/restore from online backup I would not suggest "noredo" as primary start point. You can allways start with that option later...

    But what is your real problem. Write to me on damir.vadas@gmail.com and place backup script and problem that you have (or what do you want to test) and I'll help you.
    Do not make any step that will compromise your data (like "noredo" in fact is!)
    Cheers,
    Damir Vadas
    http://damir-vadas.blogspot.com/

    ReplyDelete
  6. What is the benefit on 11gR1 from 10gR2?

    ReplyDelete
  7. An RMAN backup lets you back up the database while users are working. Some new database administrators a little afraid of online backup, and that somehow would not work. We assure you that I have yet to see a backup is successful online not be recoverable. Most stores these days use online backups, at least for their production systems.

    carbonite offer code

    ReplyDelete
  8. >We assure you that I have yet to see a backup is successful online not be recoverable
    Maybe I have faced this scenario, but could you please explain a little bit longer your though oh event?
    Thx
    Damir

    ReplyDelete
  9. Hi Damir,

    Its really awesome. But can clear a doubt.
    What would be difference in doing :

    backup database plus archive log; and the method you mentioned above for the archive logs to be backed up with the sequence limit.

    I think oracle is capable of doing all by itself. Please correct me if iam wrong.

    Thanks

    ReplyDelete
    Replies
    1. Right. But think on this live situation.
      DBA has to save backup that will alow him/her to restore database on 1st of each month.
      And those backup are saved for 7 years.
      Then it is vital that each backup takesa only needed files, bacause it will spare tape library (or whatelse is ) space.
      And this is exactly what I wanted to show how to achieve.
      brg
      Damir

      Delete

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign