Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle hot backup files to DLT

Oracle hot backup files to DLT

I need to backup oracle files in online mode to a DLT tape.(I know this is not a recommanded way to use tape/for the time been until extend the HDD space).
I tend to use following command to backup (each file execute the command)
find /u01 /oracle /temp/xxxx.dbf -print | cpio -ocvB|compress|dd of=/dev/rmt/3mn bs=30k

Here I used '3mn' instead of 3m to write files non rewind mode in continous way.However the backup goes without errors, I can't restore the files back. There is only on file in the tape.
How this happen and any other suggestions to do the process.
thanks
To get out of a difficulty, one usually must go through it
9 REPLIES
Steven E. Protter
Exalted Contributor

Re: Oracle hot backup files to DLT

Backing up directly to tape is not a problem.

Its other parts of your method that might be a problem.

You probably can't use OS commands to get your database files to tape while the database is open without putting the database in backup mode. That would stop transactions and would not be desireable.

Oracle provides a very nice tool for doing hot backup, direct to tape. rman.

Take a look at using rman.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Sunil Sharma_1
Honored Contributor

Re: Oracle hot backup files to DLT

Hi,

Your command will work fine but this backup will be of no use. database backup will not be consistent.


Sunil
*** Dream as if you'll live forever. Live as if you'll die today ***
Alexander M. Ermes
Honored Contributor

Re: Oracle hot backup files to DLT

Hi there.
Just a recommendation.
Don't do it that way. After a restore it will be a pain in the neck to recover ( if it works at all because of inconsistency ).
You could also use tar to avoid streaming the data through the compress.
Rgds
Alexander M. Ermes
.. and all these memories are going to vanish like tears in the rain! final words from Rutger Hauer in "Blade Runner"
Steven E. Protter
Exalted Contributor

Re: Oracle hot backup files to DLT

The backup is referred to as Oracle as a fuzzy backup.

Oracle support says they will not support that type of recovery.

They've said that to me.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com

Re: Oracle hot backup files to DLT

Sorry I think I have express the issue not in clear form. Let me try my best. The Oracle DBA uses some sql scripts and use them to do the hot backup process by locking tablespaces one by one.(I hope this way is correct to take hot backup).
The particular command I mentioned before is called withing a sql script to copy dbf files to the tape.
My concern is why the tape contains only one file even I use the 3mn device file in the command.
Your advices are appritiated
Hope it clear.
To get out of a difficulty, one usually must go through it
The Real MD
Valued Contributor

Re: Oracle hot backup files to DLT

consider using rman, the database needs to be in archivelog mode, as its the archive logs that are used for recovery.

hope this helps

Martin.
Sanjay Kumar Suri
Honored Contributor

Re: Oracle hot backup files to DLT

Put all the tablespace in baclup mode in one go:

alter tablespace tablespace_name begin backup;
...

Give you backup commands...

alter tablespace tablespace_name end backup;
...

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Rory R Hammond
Trusted Contributor

Re: Oracle hot backup files to DLT


Your database backup method should work if you put the db into backup mode. One caveat is if the backup takes to long oracle will hang.

I would use the following command instead of your example:
find /u01 /oracle /tmp/xxxx.dbf -print |cpio -ocvB > /dev/rmt/3mn
Because:
I don't think the compress is necessary. The tape hardware will do the compression. avoiding the compress should lower your system overhead.
the cpio -B option puts the blocksize at 5120. Do you think the dd block size significantly helps tape space? any way it was interesting to for to see. The no rewind means that you are probably stacking file sets.
cd /dest
cpio -icvB < /dev/rmt/3mn
for each fileset should recover your DB.

I would try the trivalbackup and restore. Then to your more complex options.
Measure the backup time. I bet it will be faster without the compress and am pretty sure you will be able to still fit all your data on one DLT.

Good Luck
Rory
There are a 100 ways to do things and 97 of them are right
Brian Crabtree
Honored Contributor

Re: Oracle hot backup files to DLT

Oi. Ok, all. Here is a quick run down of Oracle's hot backup procedure.

To clear up some problems with the above.

1. Oracle will not hang when the hot backup is running, or runs to long. It will hang when the archive log directory fills up, but provided that this doesn't happen, you can leave a database in hot backup mode forever (don't).

2. The tables/tablespaces/datafiles/etc are not locked. All access to the datafiles is normal, and all queries will run normally. I will go over why this is true below.

3. OS commands work fine against online datafiles. The exception to this would most likely be fbackup, which tries to open a lock on the datafile. Otherwise, 'cpio', 'cp', 'tar', 'gzip', 'compress', etc all work fine and normally.

4. Rman is not a nice tool. Rman is a PITA when running to tape. You have to use a media manager, you have to have a tape drive, and you have to have some patience to get it working right. If you have less than a 50g database, back it up to disk (compressed it will run about 14g per day). If you have more than 50g, and you have enough reason, invest in Business Copy or something like it. If you use rman, specify 4-6 times backup time for the restore, minimum, and hope that it works right.

5. Online backups are consistant provided you have all of the archive logs generated during the backup.

6. Online backups are NOT fuzzy. Fuzzy backups are where the datafile is backed up using a standard backup without putting the database into backup mode.

Ok, got that out of the way.

First, a quick explaination about how Oracle works normally. Whenever an update/insert/delete occurs in Oracle, the change is written to the rollback tablespace, written to the datafile (or cached into memory to wait for a checkpoint), and the change is written into the redolog for eventual archival.

When a datafile is placed into online backup mode, the SCN of the datafile is frozen, the redolog is updated that it is in online backup mode, but all access stays the same. You will be able to query, as well as update/insert/delete rows on the database. However, instead of writing a change into the redolog, the full block of the database is written into the redolog (ie: 8k of information per insert for an 8k block size datafile). It is done this way, because Oracle doesn't know what is being backed up at the time, so it is possible for a backup to be in the middle of a block at the time of the update, so the backup would only have half of the correct information (inconsistant).

After you have completed your backup of the datafile, you have to go back in, and alter the datafile out of online backup mode. This will unfreeze the SCN number, and notify the redolog that the backup is complete for the datafile.

Now, when you restore the datafile, the controlfile (however you get it), reads the SCN number, and finds which archive log is needed to recover the datafile. The archive log is read in, and changes made to the datafile are written in full blocks to the datafile (possible consistancy problem mentioned above). As this is done, the SCN of the datafile is kept frozen, until the redolog passes the end backup command, at which point the SCN is again updated on the datafile (signaling that the datafile is consistant with itself). Once this point is passed, the changes written to the datafile are normal block changes (not full blocks).

As for the original question, do you have a media manager like Omniback available. An easy way to do this is to put all of the tablespaces in backup mode, and use omniback to backup the datafiles to tape, then take them all out. A simple pre/post exec script can do this without a problem. If not, you might want to consider using tar, or doing compressed exports to disk, although this will not give you Point-in-Time recovery.

Let me know if you have any questions.

Thanks,

Brian