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.
Sql>Archive log list
- In
Server Manager, put the tablespace you want to back up in BEGIN backup
mode as follows:
Sql>alter tablespace tablespace_name
begin backup;
- 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.
