Oracle Backup

Database backup and recovery are among the most important and critical tasks that a DBA must perform. In Oracle you can take the backup of your database in two ways. These two ways are namely as Cold Backup and Hot Backup. Cold Backup is taken when the database is down or closed while Hot Backup is taken when the database is online or open. In order to take the Cold Backup DBA first shutdown the database and make it unavailable to the users and then manually copy the database files, control files, redo log files, network configuration files and parameter files to the database device and finally open the database for normal operations again.

Cold Backup is easy to take but is not ideal for organizations that cannot afford downtime. Most of the organizations today don’t want their database to be down for such a long time especially databases that support online businesses. The prime examples are Google, Yahoo, and Amazon etc. With that in mind Oracle database allows you to take the hot backup. While taking the Hot Backup your database remains open for the user operations. Hot Backup is taken tablespace by tablespace. This means that first you take Backup of one tablespace and then the other. The process continues until all the tablespaces are backed up.

Now suppose you want to take the backup of your “Users” tablespace which is automatically created when Oracle is installed. It is also the default tablespace for all users’ data unless you change it. First you will issue the following command:
SQL>alter tablespace users begin backup;
This command will put the tablespace in backup mode. When a tablespace is in backup mode no changes can be made on the data it contained. However users can still work because their changes are recorded in redo log files and when tablespace gets normal DBWR will apply the changes seamlessly.
The next step is to copy the datafiles belonging to “Users” tablespace. You can issue the following command to do this:
SQL>host copy ‘C:\oracle\product\10.1.0\oradata\orcl\users01.dbf’ ‘F:\backup\users01.dbf’;
This command will copy the datafile to the backup location. “Host” keyword is used to run OS commands from SQL*PLUS. This command will repeat for each datafile in tablespace. Further more you can manually copy and paste the datafile using Explorer instead of issuing this command.
The next command will bring the tablespace backup to normal mode:
SQL>alter tablespace users end backup;
The last command will insure that the database gets into consistent state by forcing a log switch.
SQL>alter system switch logfile;

Normally instead of issuing all these commands manually each time, DBA writes a script consisting of these commands and runs the script. For that purpose open a text editor like Notepad and write all the four commands in it. Be sure to write each command on a separate line and save the file as SQL script file (.sql). To run the script issue the following command:
SQL>@C:\HotBackup.sql