How 'dd-if' is used to take oracle online backup to tape drive ?

[Deepu Chakravarty]

Assistance is required to use the command 'dd-if' which will take oracle online(hot) backup into it. I have external tape drive connected to the server. Address is '/dev/rmt/c0t3d0BEST' in which backup is to be taken.

[Peter Godron]

Hi,
have alook at the documentation at
http://www.oracle.com/technology/documentation/database10g.html and man dd

Can you please clarify your question.
Is the problem the dd command or the oracle backup mechanism?

[Deepu Chakravarty]

Hi,
Thanks for the reply.

Simply put it:
I need to take online oracle database backup.
My oracle database size is 75 GB. I don't have so much of diskspace in my server. But I can use tape drive for taking backup into it. I want to know the mechanism how to take the online backup into tape drive.

[Florian Heigl (new acc)]

So You're using raw filesystems or why is it You need to use dd?

In short:
a online backup of 75GB via dd is impossible (the database has to be in archive mode until You're done, which could hardly be considered "online"), also trusting 75GB of dd on a single tape would be sheer madness.

I don't know if You can run cksum on a raw lv, I guess You can't, so You should at least pipe it through gzip and add additional recovery information (crc-32 and some fix data). this will also speed up the backup process.

But there must be better ways for the future - consider buying a cheap 200GB GigE NAS appliance with RAID1 to hold the database dump and spool it off to tape from there.

just my $.02

[Florian Heigl (new acc)]

anyway.

It should be

dd if=/dev/vgNN/rlvolNN | /usr/contrib/bin/gzip -c | dd of=/dev/rmt/c0t3d0BEST obs=32768

after taking the backup, restore it on a system that has enough space, and *only* if it gets decompressed it is okay.
(oracle should not care about endianess, I hope?)

still doing such a thing is risky.

What happens if it is not recoverable?
Who will loose his job? The person asking You to take the backup online or You? ;)

[TwoProc]

You can do it by writing a script to put each tablespace into archive log mode (one at a time).
-- example USERS and TOOLS tablespace in TEST instance
-- keep in mind to use the no-rewind interface - 0mn. In most HPUX boxes I've seen of late 0m and 0mn, etc. are already symbolically linked the "BEST" interface.
If you don't use the no-rewind interface then every next file dd command will overwrite the previous file.

-- I prefer to do this first - but not necessary.
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
-- look in your archive logs and write down the current logfile number (or previous - close enough)...
Alter tablespace USERS begin backup;
host dd if=/u2/data/TEST/users1.dbf of=/dev/rmt/0mn bs=128k
host dd if=/u2/data/TEST/users2.dbf of=/dev/rmt/0mn bs=128k
alter tablespace USERS end backup;

alter tablespace TOOLS begin backup;
host dd if=/u2/data/TEST/tools1.dbf of=/dev/rmt/0mn bs=128k
host dd if=/u2/data/TEST/tools2.dbf of=/dev/rmt/0mn bs=128k
alter tablespace TOOLS end backup;


... keep going for all tablespaces.


at the end...
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
(repeat for number of redolog files you have - once again, you don't have to - I just prefer it this way b/c it gives me a nice boundary around my backup. However do this one *at least once* ).

-- write down the last archive log file from above. All those in the range from the first until the last are the ones you need to process your recovery and open your database.

One more thing. Make sure to do this when system is the least busy. All the while that a tablespace is in backup mode, data is written to the redo-logs regarding whole blocks, instead of just individual data areas. This means that during this time - you're going to chew through archive logs much faster than usual. Also, the longer you stay in backup log, the more archive logs generated, the longer the recovery on your destination database becomes to open...

Also, consider using a faster way to write to tape. Tar would probably be faster and work great, if each file you're reading/writing is less than 2G. But, do some simple timings on your system to figure out which is faster for you.

Practice on a cloned test system, perfect your scripts.

Make sure at the end that you query from v$backup and look at the status column to make sure that no tablespaces are in backup mode when you're done - or you hang in mid-test, etc. If you join v$backup to dba_data_files via file_id to file# you can see what files are in backup state. Always check this b/c you don't want your db to be left in part in a backup state when you do your next shutdown - that would be ugly.

[Deepu Chakravarty]

Hi,
Hiegl, reply to you first. I think you have misunderstood that I intend to take complete 75GB backup in one shot. Pls read the details of John who has correctly understood my issue.

Now only thing I need to know how to list out contents of media after using 'dd'.

[Florian Heigl (new acc)]

Hi,

seems I got You wrong, yes.

dd keeps no inventory, You will have to keep a file list Yourself. Probably it'll be best if You collect it to a temp file and then dd that of to the end of the last tape.

Better document that in a header file otherwise noone searches for a index at the tape end.

[TwoProc]

Deepu - If you like my answer (and anyone else's) - the polite thing to do is to assign points.

[TwoProc · Accepted solution]

Florian's right - dd'ing to a tape drive and just adding files is not pretty. You'll have to get a log file and keep if everything you're putting in there. But, you know what you're going to put - so put a "contents" file at the beginning of the tape.

[Deepu Chakravarty]

Hi,
John, I have assigned points. But I have few more queries it would be appreciated if you or anyone else answer those queries.

In my database, oracle blocksize is 8192. I should keep in mind the oracle OS header of 64K.

1. What would be correct value of 'oseek' and 'count' parameters considering the value of one datafile size 500M ? Others data

2. As you mentioned 'tar', Can I take hotbackup through tar ?

3. How to put a contents file at the begining of the tape ?

[Indira Aramandla]

Hi Deepu,

Yes you can take hotbackup through tar. We do this to backup from disk to disk and then use omniback to backup onto the tape.

# Alter the tablespace to backup mode begin

alter tablespace $TBS_NAME begin backup ;

# tar and compress the datafiles (with relative path '.' )

tar cvf - ${file_path} | compress > ${BKP_DEST}/bkp_${TBS_NAME}_${ORACLE_SID}.tar.Z

# Alter the tablespace to end the backup mode
alter tablespace $TBS_NAME end backup ;

Likewise do for all the tablespaces in the database and then tar and compress the REDO logs , then tar and compress the controlfiles with relative path.

In your case if you want to directly backup to the tape, so specify the device file for the tape.

Indira A

[Deepu Chakravarty]

Hi,
Indira. Thanks for the updates.
'tar' file_path and destination_path is not clear. Pls one example.

Suppose I have few files in /working/archs/test and wants to archive them and put into the same directory using your 'tar' command (tar cvf - ${file_path} | compress > ${BKP_DEST}/bkp_${TBS_NAME}_${ORACLE_SID}.tar.Z) , how I should do ?

[TwoProc]

1. What would be correct value of 'oseek' and 'count' parameters considering the value of one datafile size 500M ?
Q) what tool/command are you referring to? mt?

2. As you mentioned 'tar', Can I take hotbackup through tar ? Yes, tar can certainly be used. instead of the dd command - use
" host tar cvf /dev/rmt/0mn /u2/data/TEST/tools1.dbf "

But, what's nicer here is that you can have just ONE tar command per tablespace - **if** you have named ALL of the datafiles per table consistently. so, like in my example - if ALL of tools' files are of the form tools1.dbf,tools2.dbf,tools3.dbf,tools4.dbf.

Well then you can put ALL of the TOOLS tablespace files together in one tar file.
so it would become ...

alter tablespace TOOLS begin backup;
"host tar cvf /dev/rmt/0mn /u2/data/TEST/tools*.dbf"
alter tablespace TOOLS end backup;

This would put ALL of the tools files for the tools tablespace in the tarball on the tape in a single set - greatly simplifying your restore.

This could also be useful for you if you've got *LOTS OF LITTLE TABLESPACES*...
Like so,

alter tablespace NOTES begin backup;
alter tablespace PUNS begin backup;
alter tablespace JOKES begin backup;
alter tablespace TASKS begin backup;
alter tablespace FUNNY begin backup;

host tar cvf /dev/rmt/0mn /u2/data/TEST/notes*.dbf /u2/data/TEST/puns*.dbf /u2/data/TEST/jokes*.dbf /u2/data/TEST/tasks*.dbf /u2/data/TEST/funny1.dbf

alter tablespace NOTES end backup;
alter tablespace PUNS end backup;
alter tablespace JOKES end backup;
alter tablespace TASKS end backup;
alter tablespace FUNNY end backup;

Now, you've got tablespaces NOTES,PUNS,JOKES,TASKS,FUNNY in one tarfile on the tape. Again - simplifying your restores.

JUST BE AWARE that the above example is good for small lightly used tablespaces - ONLY.

more info: how to read the contents of a tar file ...
tar tvf /dev/rmt/0mn
more info: how to restore...
tar xvf /dev/rmt/0mn
note that this will RESTORE RIGHT ON TOP of your open database if you've still got it there - so BEEE CAREFUL.

Play with using a "cd" command at the beginning of your script to put you on the root drive before you start - that way you can put files on the tarball in a relative directory path.

host "cd / ; tar cvf ./u2/data/TEST/notes*.dbf "

This would allow you to restore files relative to any place in the system you wish - such as /mytestdb/ - instead of root.

So, if you wanted to make a copy of the database - but not on top of the other database...

cd /mytestdb/
tar xvf /dev/rmt/0mn

this would put - for examples the notes tablespace at
/mytestdb/u2/data/TEST/notes1.dbf
/mytestdb/u2/data/TEST/notes2.dbf

OK?

3. How to put a contents file at the begining of the tape

Well. You know what files you're going to backup - because you had to write a script to back them up. So, if you just grep through that command script to give a list of files and store that in another file...

grep "\/u2\/data\/TEST\/" ora_hot_back.sql >
filelist_orahot.Jan27_2005

Now, at the beginning of your backup - before your start writing to tape.
a) rewind tape "mt -t /dev/rmt/0m rew"
b) dd or tar the file list to the tape drive... "tar cvf /dev/rmt/0mn filelist_orahot.Jan27_2005"

Then proceed with your hotback up to the tape drive.

Of course the above step can be fully automated with the "date" function (man on date) to automatically generate a date file extension, and your shell script that calls sqplus can put the file on tape first for you. Right? You can also - with sql scripts easily write scripts to generate the file listing scripts with hot backup commands for you right?

Meaning you can make this whole event as nice and automated as you wish - and you probably should.

[Deepu Chakravarty]

Thanks John, you are placing very informative guidelines not only for me but for others also.

I need to mention here that I may follow 'tar' option of taking hotbackup.

As you mentioned taking backup of all datafiles per tablespacewise,
1. Don't you think inconsistencies may happen ? Or
2. will tar complained with any error if in the process of backup some datafiles are being used for ex:system tablespace ?
3.what would be the plan of action if tar utility complained of any error due the factor as per 2 ?
4.Suppose tablespace 'system' has following datafiles, I have script as follows. This is valid for other tablespaces also.

alter tablespace SYSTEM begin backup;
!tar cvf /dev/rmt/c0t3d0BEST /sysprod/proddata/proddata/system11.dbf
alter tablespace SYSTEM end backup;

alter tablespace SYSTEM begin backup;
!tar cvf /dev/rmt/c0t3d0BEST /sysprod/proddata/proddata/system10.dbf
alter tablespace SYSTEM end backup;

alter tablespace SYSTEM begin backup;
!tar cvf /dev/rmt/c0t3d0BEST /sysprod/proddata/proddata/system01.dbf
alter tablespace SYSTEM end backup;

alter tablespace SYSTEM begin backup;
!tar cvf /dev/rmt/c0t3d0BEST /sysprod/proddata/proddata/system02.dbf
alter tablespace SYSTEM end backup;

will it be correct ?

This online/hotbackup is only for recovery purposes. So I am using absolute path.