How to Hot backup & restore of an Oracle 11gR2 database

How to take Hot backup & restore in  Oracle 11gR2 database:-

Step 1:-
Prerequisites:-
$Select log_mode from v$database;
If the database is not in archivelog mode then put the database in the archive log mode
sql>shut immediate;
sql>startup mount;
sql>alter database archivelog ;
sql>alter database open;

Step 2:-
Taking a hot backup-

Now that we have prepared our database for a hot backup, we can go ahead with actually backing up the files.

Follow the following steps to take hot backup of the tablespaces

1. Find out the number of tablespaces associated with the database

sql>Select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

2. Find out if the tablespaces are ready for hot backup

sql>select * from v$backup;

If the output says not active then it is not in hot backup mode

3. Put the tablespaces in hot backup mode

sql>Alter database begin backup;

sql>Select * from v$backup;
 
     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                1128871 30-JUN-14
         2 ACTIVE                1128871 30-JUN-14
         3 ACTIVE                1128871 30-JUN-14
         4 ACTIVE                1128871 30-JUN-14
         5 ACTIVE                1128871 30-JUN-14

4. Copy the tablespace files on the hard drive to the backup location.

5. Put the tablespaces out of the backupmode

sql> Alter database end backup;

6. Verify that the tablespaces indeed have come out of the backup mode

sql>Select * from v$backup;

7. Switch the archive log

sql>Alter system archive log current;

8. Backup the control file

Note- Don’t use the operating system’s copy command to do this

sql> Alter database backup controlfile to '/u01/app/oracle/backup/controlfile.ctl';

9. Copy the archive logs to the backup location

Backup of the database finished.


Step 3:-
Restoring the oracle database from a hot backup

1. Copy the tablespace files from the backup location to the installation directory of the database instance. Also copy the controlfilebackup.

2. Rename it to CONTROL01.CTL as it was earlier.

Note- If you had another copy of the control file with the name CONTROL02.CTL, then just create a second copy of the CONTROL01.CTL and rename it CONTROL02.CTL

3. DO NOT COPY OR restore the REDO logs. If the REDO logs from the previous backup period persist then delete them

4. Start the database in mount mode

sql>startup mount;

5. Recover your database using the following
sql> Recover database until cancel using backup controlfile;

Note- The oracle system will suggest an ‘archive log file name’ to use for recovery, if you have copied the backup logs to the same location which was being used for storing the logs by the database, then u can just keep on pressing enter. Or you may give the full path to the log file.

6. When you have applied all the logs that you had used to take the backup, then write cancel on the prompt and press enter.

7. The transaction logs have been applied. Run the following query to open the database for transactions.

sql>Alter database open resetlogs;

Restore complete...