Best way to completely backup a oracle 10g database

Hello,

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?
showisonManger/itAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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.
0
arnoldCommented:
See https://docs.oracle.com/cd/E14148_02/wlcp/ocsg41_otn/restore/rdbms_backup.html

Identifying what you gave in terms resources, etc. OS, ....
1
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

showisonManger/itAuthor Commented:
We are using a windows base server
1
arnoldCommented:
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.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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?
0
jtriftsMI and AutomationCommented:
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:
https://www.thegeekstuff.com/2013/08/oracle-rman-backup

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
I think that jtrifts' answer is probably the most thorough and the assisted answers provide some useful detail too.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.