Best way to completely backup a oracle 10g database

showison used Ask the Experts™

i've just just taken over management of an oracle 10g database and i've noticed there are no backups in place in the event of hardware or operating system failure.

I'm looking for the best way to schedule automatic backups of an oracle 10g database to a remote location.

I'm looking for a complete backup as the database has tables, views, packages, functions and triggers all of which i need backed up.

Preferably i'd need my backup destination to be either a remote server on vlan, a remote ftp server or possibly an AWS S3 bucket.

What would other oracle database administrators suggest?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Setup local first, for faster recovery in the event of Sara issues, then work with what exists in the environment, us there no backup agent tivoli, networker, etc.

You are not specifying the OS.
Distinguished Expert 2017


Identifying what you gave in terms resources, etc. OS, ....
Martyn SpencerSoftware Developer / Linux System Administrator
If the database can be taken offline, you could take a file based backup of the entire database. This would not be my preferred route because of the downtime, however, in certain circumstances it can offer the opportunity for the most rapid restore.

You can also set it up so that the database can be in archivelog mode. This is a special database operation mode that allows you to back up the main data files and once the backup is complete you back up the archive logs, which effectively gives you a complete backup.

Alternatively, use the Oracle 'exp' command to dump the entire database or the required schemas. This could be scripted to create the dump, compress it and then transfer it to the locations where you wish to store copies. If you are using Linux, this could be done with cron. If using Windows, with the scheduler. The database can be imported using 'imp'. There are newer versions of the import and export utilities that could also be used. More info can be found here

There are also backup tools that specifically have agents for Oracle databases. Many of these will help make use of features like archivelog mode. What backup tool do you currently use?

Arnold's link above is also very good.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.



We are using a windows base server
Distinguished Expert 2017

Double check installed apps to confirm you do not have backup agent backup exec, tivoli, networker, etc that backs up the db.
Which OS version, how much space resources do you have available.
Martyn SpencerSoftware Developer / Linux System Administrator

If you have no solution in place, a low-cost product that I found (Handy Backup) can be found here. I don't use it (most of my backups are scripted), but maybe it will help you?
MI and Automation
Backups depend on need and availability.
If you have a maintenance window (ie downtime), you can take a "cold" backup, which is, essentially, a straight file copy of all the files required for the Oracle DB.
However, this is typically used only for "local" databases which a developer or DBA manages themselves.
My preference, whether online or offline is to use RMAN (Recovery Manager).
You can take a level zero (full backup) or level 1 (incremental backup). Incrementals can be cumulative or differential. A cumulative incremental requires that you have a level zero (full) backup, and the cumulative backup backs up all block changes since the full backup was taken. A differential incremental backup may require successive backups to be restored since the full backup.

A simple guide for backups using RMAN may be found here:

Your backups can be written to a locally mounted disk (the default RMAN configuration) or you may write to a backup device by configuring the SBT_TAPE parameter.

Once you are ready for the next step, you can configure other devices, such as Netbackup Data Domain, as the target for SBT_TAPE and use a scheduler (e.g. Netbackup Admin Console) for setting up regular scheduled backups.

Whether you take online or offline backups depends on your system availability requirements and the maintenance windows you have at your disposal. Personally, I always like to have an online cumulative incremental backup to be run every evening (or early morning), with weekly full backups run (typically early Sunday morning). I also like to take a monthly backup too. The reason I take all these different backups is so that I can apply different retention rules to each. For example, I might keep daily cumulative incremental backups only for a week or two...weekly full backups for a month or two, and monthly incrementals for up to a year. You might even want to take an annual full backup which is written to tape.
Mark GeerlingsDatabase Administrator
The comments by Martyn Spencer and jtrifts are very good.  No matter which approach you choose, I recommend that you do local backups and/or exports first.  These are faster to create and to restore from.  Then, if you want to copy those to a secondary or remote location, that is another optional step.  

I like to do both backups (using Oracle's RMAN or another utility that can handle backing up Oracle databases) plus exports (and in Oracle10 and later, the newer DataPump export is faster) because these offer different recovery options and advantages.  If you ever need to recover the entire database, recovering from a full database backup is the fastest.  If however you ever need to recover just some individual database objects (tables, views, procedures, etc.) this can be much faster and easier from an export.  And, an export takes much less space than a full backup, since Oracle export files only contain the actual structure and data from the database (with no free space included) and the commands to rebuild all of the indexes, but not the actual space used by the indexes in production.  

Be aware though that Oracle export files can never be combined with archived redo logs.  A recovery from an export is always to the point in time the export was taken.  In contrast, if your database is in archivelog mode, and you have all of the archived redo logs created after the last backup, you can recover the database (and/or any portion of the database) to any point in time after the backup up to the time of your latest archived redo log.
Martyn SpencerSoftware Developer / Linux System Administrator

I think that jtrifts' answer is probably the most thorough and the assisted answers provide some useful detail too.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial