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?
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!
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.
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.