1

I have tried with RMAN. But is there any other possible way that we can backup database and tables. backup using queries or stored procedures is possible?

  • What problem do you have with RMAN exactly? Why are you looking for something else than the standard backup tool to do backups? – Mat Jan 9 '15 at 10:34
  • No I am just looking for any possible methods that i can try – Vignesh Jan 9 '15 at 11:41
  • Stop "looking for any possible methods that you can try" and use RMAN - that is the tool for the job and the only answer. – Jack says try topanswers.xyz Jan 9 '15 at 16:39
2

The only correct way to back up an Oracle database is to use RMAN. There are alternatives but they are not as reliable as RMAN.

RMAN has a ton of features and this might make it look complicated. But in fact it's not difficult and should always be the choice to back up a database.

RMAN does not do logical backups like tables, indexes, procedures. RMAN does physical backups which means that it backs up the datafiles block wise. It does not care what is inside this block.

I think there are a lot of beginner tutorials on the internet. I personally like the descriptions on http://oracle-base.com/.

To automatically back up a database use the operating system scheduler and run a shell or batch script. This script should do the following tasks:

  • call RMAN an execute an RMAN script
    • backup database
    • backup archivelogs
    • delete archivelogs
  • delete old backups (with RMAN commands)
  • send the logfile to the admin

Some people additionally run an export with datapump after the daily RMAN backup. This can be useful sometimes. But I never use this option. And it should never replace an RMAN backup!

  • I'd disagree that alternatives are not as reliable as rman. Maybe they don't have as many features as rman, but reliable? sure. – Raj Jan 23 '15 at 15:52
  • What alternatives are you talking about? exp or expdp is not an option. Backups with begin backup and end backup can cause problems on huge databases. 3ed party tools like netbackup use RMAN to backup the database. – ora-600 Jan 26 '15 at 11:41
  • @ora-600: setting datafiles in backup mode is a reliable method for doing backups. It was the method that was used before RMAN was released and it is used today by tools that uses snapshots of file systems for the backup like Netapps SnapManager for Oracle. – miracle173 Feb 25 '15 at 6:59
  • Back then when setting tablespaces into backup mode was the default method we did not have tablespaces with TB size. Snapshots are nice but what do you do when you get block corruption? I'm still pretty sure that I would not want to rely on a backup other than an rman backup. – ora-600 Feb 25 '15 at 9:41
  • In the last months I experienced the problems which snapshots can cause. A customer uses snapshots additionally to RMAN. When the snapshot is created the database enters backup mode for a very short period of time. Somehow it happened several times that the snapshot mechanism failed to release the backup mode which caused to database to crash after some hours (for obvious reasons). In other databases this will not take hours on some it might take days. – ora-600 May 18 '16 at 11:33
0

At the risk of annoying the RMAN fanatics and also actually answering this question by giving possible viable alternatives, it can be done.

  • you can use datapump (or for that matter imp/exp) to create consistent full logical backups scheduled via cron (or however Windows does scheduling).

  • you can also take cold physical backups by shutting the database down and using O/S commands in a batch file to copy away the variety of data and config files before restarting the database.

These methods worked fine before RMAN existed and will work fine now, but they do have a number of drawbacks that can impact business continuity rather heavily, but if you have a small database and it doesn't need to be available all the time or you can take a long time to recover if something goes wrong and you don't mind losing changes since the last backup point, then these can still be good working choices. As a bonus these options don't need archivelog mode either so you can save yourself the performance overhead.

  • you could do cold backups and archivelogging, scripting logs to tape periodically yourself but you'd have to follow a more complex manual recovery process to roll-forward.

  • you could also use the export option in SQL Developer to save your database schema objects as DDL and INSERT statements. This would only be consistent if you closed your application first but might be a viable alternative in some cases (small read-only datasets for example). Good for migrating data to other database platforms too; one thing that RMAN sucks at.

  • you can improve on the cold backup by using a split mirror solution of some kind (this was a really massive thing on HPUX for a while) to reduce the downtime to a minute or less. Cheap RAID 5 arrays all but killed this off but if you have RAID mirroring you can split it and take the backups from the mirror and then resync the mirror you could party like it was 1999.

  • you can also do all these things via a data guard instance without interrupting or loading your main production database.

Note:

RMAN is very handy at backing up Oracle stuff so you really should use it as a first choice solution. It's not at all hard to use and much less expensive and complex than trying never to use it, especially as your database grows and the expectation for downtime shrinks.

RMAN is also really good at the recovery bit; much better than most DBAs are at scripting stuff themselves IME - and this is what really makes it worth while as a backup tool. I know that you didn't ask about recovery but I'll take it that you actually want backups that you can use to get back to a working database (update your question if recovery isn't an important factor because then we can talk about the no-backup solution). Backups are boring but recovery isn't. Performing a full point-in-time recovery after an actual failure is fantastic - it makes you feel like a god (almost for a minute anyways) even if you only used two statements to do it. Finding out that recovery isn't possible after working most of the night because the former DBA's script missed the first log file created after the full backup from the tape gives you a homicidal headache. RMAN is your friend.

  • Cold backups hurt the performance of Oracle a lot. I would try to avoid it when ever possible. – ora-600 May 18 '16 at 11:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.