Cold and hot backups

Offline (Cold) Backups

An offline cold backup is a physical backup of the database after it has been shutdown using the SHUTDOWN NORMAL command. If the database is shutdown with the IMMEDIATE or ABORT option, it should be restarted in RESTRICT mode and then shutdown with the NORMAL option. To have a complete cold backup the following files must be backed up.

• All datafiles.
• All control files.
• All online redo log files (optional).
• The init.ora file (can be recreated manually).

The location of all database files can be found in the data dictionary views, DBA_DATA_FILES, V$DATAFILE, V$LOGFILE and V$CONTROLFILE. These views can be queried even when the database is mounted and not open.

A cold backup of the database is an image copy of the database at a point in time. The database is consistent and restorable. This image copy can be used to move the database to another computer provided the same operating system is being used. If the database is in ARCHIVELOG mode, the cold backup would be the starting point for a point-in-time recovery. All archive logfiles necessary would be applied to the database once it is restored from the cold backup. Cold backups are useful if your business requirements allow for a shut-down window to backup the database. If your database is very large or you have 24x7 processing, cold backups are not an option, and you must use online (hot) backups.

Create a directory /u01/coldbackup.

Copy pfile to backup directory. If you are using spfile create pfile as follow.

$sqlplus / as sysdba

SQL> create pfile from spfile.

SQL> exit

$cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora /u01/coldbackup

Note down C/R/D files location

$sqlplus / as sysdba

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/redo03.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo01.log

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/sample01.dbf

Now shut down the database.

Sql>shut immediate

Copy the all C/R/D files to backup loaction

 

---------------------

Restore cold Backup in case of database corrupted.

-        

            Restore all files to corresponding locations and start database.

Online (Hot) Backups

When databases must remain operational 24 hours a day, 7 days a week, or have become so large that a cold backup would take too long, Oracle provides for online (hot) backups to be made while the database is open and being used. To perform a hot backup, the database must be in ARCHIVELOG mode. Unlike a cold backup, in which the whole database is usually backed up at the same time, tablespaces in a hot backup scenario can be backed up on different schedules. The other major difference between hot and cold backups is that before a tablespace can be backed up, the database must be informed when a backup is starting and when it is complete. This is done by executing two commands:

hot backup full database at a time:

----------------------------------------------------------

Check the database in archive log mode or not.

Sql>archive log list

If not in archivelog mode put the db in archive mode.

Unlike above scenario, we can put whole database into begin backup mode as bellow.

SQL> alter database begin backup;

Take all datafiles in the database into backup location and end the backup mode.

SQL>alter database end begin backup;

 In Server Manager, execute the ARCHIVE LOG LIST command to get the current log sequence number. This is the last logfile you will keep as part of the hot backup. Next, force a log switch so Oracle will archive the current redo log file.
 

SQL>Alter system switch logfile;

Backup all the archived log files, beginning with the log sequence.

  Backup the control file using the following command:
 

SQL>alter database backup controlfile to 'file_name';

Hotbackup with tablespace method.

In order to avoid large amount of log generation we can follow tablespace level online backup.

1. Find the oldest online log sequence number with the following command:
   
   

Sql>Archive log list

2. In Server Manager, put the tablespace you want to back up in BEGIN backup mode as follows:
    

Sql>alter tablespace tablespace_name begin backup;

3. Backup all the database files associated with the tablespace using an operating system utility.

• Set the tablespace in END backup mode by using the following command:
   

Sql>alter tablespace tablespace_name end backup;

Repeat Steps 2, 3 and 4 for each tablespace that you want to back up.

• In Server Manager, execute the ARCHIVE LOG LIST command to get the current log sequence number. This is the last logfile you will keep as part of the hot backup. Next, force a log switch so Oracle will archive the current redo log file.
   

Sql>Alter system switch logfile;

• Backup all the archived log files, beginning with the log sequence in step 1 to the log sequence in step 5.

• Backup the control file using the following command:
   

Sql>alter database backup controlfile to 'file_name';

Note: The control file should always be backed up after any structural change to the database is made.

===================================================

Restoring Hot backup:

1. Copy the database files files from the backup location to its corresponding directories ( to the same directories from where datafiles copied while taking backup). Also copy the controlfile backup.

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.

4. Start the database in mount mode

              SQL> startup mount;

5.Recover your database using the following.

             SQL> Recover database using backup controlfile until cancel;

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.