oracle database backup

[Jade Bulante]

I'm currently using cpio to backup our oracle server running on 11.00. The backup starts at the root level all the way down to all subdirectory. How can I just backup only the database which is /u01, /u02, /u03, and /u04? This is the backup script:
cd /
find . -print:cpio -ovcB>/dev/rmt/0mb

I already have a script to shutdown the database. Need help!!

[Jordan Bean · Accepted solution]

We've used these to archive all table spaces:

# find /u*/oradata/ -xdev | cpio
# tar c /u*/oradata/

And we've use these to isolate a specific database where its table spaces reside in directories by the same name:

# find /u*/oradata/${DB}/ -xdev | cpio
# tar c /u*/oradata/${DB}/

But now we use Legato and will soon be implementing EMC TimeFinder to eliminate downtime.

[Jeff Schussele]

Hi Jade,

A simple mod to your find command would be to replace the start for the find from "." to "./u0*"
This will force find to ONLY look in dirs that start with u0 (zero).

But I would also recommend that if the backup will only be restored to an HP system, you take a look at the fbackup command.
Do a man fbackup
Much more versatile & faster to boot.

HTH,
Jeff

[Bill Hassell]

After paying all that money for Oracle, I would definitely not recommend cpio, tar, pax or dump as a backup tool. These classic Unix utilities were never designed to backup gigabytes of data and tar/cpio/pax cannot backup files larger than 2Gb. They were not designed to be used on DDS and DLT drives, and they have no error recovery capability.

The minimum backup program I would recommend is fbackup (which sam can configure for you) for a number of reasons: reliability, speed, error recovery, multi-tape support, labeled tapes, central index, etc

But a commercial backup program such as HP's Omniback is much more robust and is designed specifically for databases.

[Yogeeraj_1]

Hi,
using OS commands to backup Oracle Database!!

Ask the Oracle Experts, they will never recommend that!

HOT BACKUP. This is all you need. You should be running the latest versions of Oracle (8i?) Use RMAN. period.

My advice, you have paid so much for Oracle and all those nice features that come with it, so why don't you use them...

Best Regards
Yogeeraj

[Alexander M. Ermes]

Hi there.
Using Unix tools may be ok for small databases.
If you have larger databases, you should go for atleast fbackup, which allows you to pick single files very easy. The unix tools capacity is mostly limited to a size of 2 GB for single files.
You should really think about OmniBack.
This product is reliable, conmfortable ( GUI ) and it works with a lot of different devices for backups ( DAT, DLT, libraries etc ). We use this since 1994 and we can still restore files from that period quick enough.
Just my 0.02 ???
Rgds
Alexander M. Ermes

[Gururaj Gudi]

I would recommend a combination of hot backups and tar backup the database. Ensure that you do a test retrieval at least once a month (more often if the database is mission critical).

I use following strategy.
1. Hot backup and stored in a tape using tar.
2. Oracle export (exp command) on the data and this output is again stored using tar.

Second point is good for small databases but for large onces, it requires a substantial amount of diskspace.

HTH.

Thanks

[Wodisch]

Hello,

be careful to include ALL your database's datafiles! Someone might have added a datafile somewhere else and then your whole backup is worth NOTHING!
So your backup procedure has to query the database instance for the list of datafiles!
You really need a appropriate tool for this, and I do not consider tar/cpio/pax/dd to be appropriate...

Just my $0.02,
Wodisch

[Joseph C. Denman]

Jade,

The best way to identify the datafiles is to query the database. You could build a script to spool the output to a file. Then use that file to backup the datafiles.

Examples of the querys I use are:

spool /tmp/datafiles.dat
set head off
select file_name from dba_data_files;
select member from v$logfile;
select name from v$controlfile;
select file_name from dba_temp_files; (8i and above)
spool off


Hope this points you in the right direction. I usually always going the wrong way!!!


...jcd...

[Wodisch]

Hi again,

since we all seem to be talking about "offline backups":
do not forget to shutdown your instance with a CHECKPOINT *before* you do the backup:

shutdown immediate

else your backup is not worth a thing...

Regards,
Wodisch