Physical Backup (Cold Backup)

Backup's

There are two types of backup's

1.Logical backup

     Exp & Imp
     Expdb & Impdb ( Datapump)


2.Physical Backup

     Cold Backup
     Hot Backup
     Rman

Physical Backup ( Cold Backup )

Cold Backup:

==>A cold backup is done when there is no user activity going on with the system. Also called as offline
backup, is taken when the database is not running and no users are logged in. all files of the database
are copied and no changes during the copy are made

==>The benefit of taking a cold backup is that it is typically easier to administer the backup
and recovery process. For cold backups the database does not require being in archive log mode and
thus there will be a slight performance gain as the database is not cutting archive logs to disk.


1. Cold backup is offline Backup, and Consistency backup

      shutdown Normal
      shutdoen transactional
      shutdown immediate

Two Different Types of failures

1.Instance Failure
2.Media failure

1) Instance Failure :

      Power failure
      Shutdown abort
      Killing Background Process

** SMON is responsible for performing instance crash recovery during the Next Startup

During the data recovery 2 process are invoked 

    1.Roll forward process
    2.Roll back process

2)Media failure

       Block Corruption
       Disk Error or Crashed
       If we loose Control,Redolog,Data files

During the media recovery 2 process are invoked

     1.Restore the previous Cold Backup
     2.Recovery --> Apply Archived & redolog files

Recovery Process:

    Is categorized into @

    1.Complete recovery -- Apply all archived log and Online redologfiles

    2.Incomplete Recovery

1.Complete Recovery will done when we have 

      present control file
      Archived redolog files
      redolog files


   -->Complete recovery can be done in 2 ways 

       1.Online
           Non system datafile

       2.Offline

           System Datafile
           Undo datafile

2. Incomplete recovery can be done 

       Until cancel -- Apply all archived logfiles generated
       Until time    -- Apply all archived redolog generated Until Time
       Until SCN    -- Applu all archived redolog generated until SCN

 After performing Incomplete Option recovery we have to open database in resetlogs

      open resetlog Option (SCN reset to 1)

commands :

To check the database is in archive log mode

  sys>>archive log list;
  sys>>select log_mode from v$database

To check the path of CRD files

   sys>> select name from v$controlfile;

   sys>> select name from v$datafile;

   sys>> select member from v$logfile;


    <============================>Loss of CRD file <=========================>

Steps to recovery when loss of CRD files:

Backup :

Take backup of CRD Files

$ mkdir Cold

$ cp * Cold/

sqlplus / as sysdba

sys>>Startup

sys>> conn u1/u1

sys>> insert records into table and commit;

sys>> alter system switch logfile

sys>> shutdown immediate


Restore the privious CRD file into

$ cd cold

$cp * ../

Recover will be done in mount state

sqlplus / as sysdba

sys>> startup mount;

sys>> alter database recover automatic using backup controlfile until cancel;

sys>> recover cancel;

sys>> alter database resetlogs;

>>desc v$logfile;

>>desc v$database_incardination

>>select incardination#,resetlogs_id from v$database_incardination;


<==========================>Loss Of Control Files<=========================>

For recovery need privious CRD files backup

Previous backup should contains  contains CRD files

Note :

If there is no previous Backup we cannot perform recovery

sqlplus / as sysdba
sys>> shut immediate;

Recovery steps :

sqlplus / as sysdba

sys>> startup;

sys>> insert records into the table

sys>> shutdown abort

Restore the previous control file backup

Recovery will done in mount state

sqlplus / as sysdba

sys>>startup mount

sys>> alter database recover automatic using backup controlfile until cancel;

sys>> recover cancel;

sys>> alter database open resetlogs;


<=======================> Loss of System Datafiles :<=========================>


For recovery need previous system data files  to recovery

Note :

If there is no privious backup of system datafiles we cannot perform the recovery

 sqlplus / as sysdba

 sys>> shutdown abort;

 $ ps -eaf |grep -i smon

 $kill -9 10250

Restore the previous systems files

 $cp * .dbf ../

 sqlplus / as sysdba

sys>> startup mount;
sys>> recover database

sys>> alter database open;

<=========================>Loss of Undo Datafile<==========================>

For recovery need previous CRD files backup

Previous backup contains CRD files

sqlplus / as sysdba

sys>> shutdown immediate

Restore the undo datafile

$ cp Undo.dbf ../

sqlplus / sysdba

sys>> startup mount

sys>> alter database datafile 3 offline;

sys>> alter database recover automatic datafile 3;

sys>> alter database datafile 3 online;

sys>> alter database open;



<=======================>Loss of Non System Datafile<========================>

For recovery need previous CRD files backup

Previous backup contains CRD files


sqlplus / as sysdba

sys>>shut immediate

Restore the non system datafile

$ cp * non system datafile

sqlplus / as sysdba

sys>> startup mount;

sys>> alter database datafile 4 offline;

sys>> alter database recover automatic datafile '/disk1/oradata/ORCL/undo.dbf';

sys>> alter database datafile 4 online;

sys>> alter database open;


<======================> Demo Unbackedup Datafile <========================>

sys>>create tablespace demo datafile '/disk1/oradata/ORCL/demo.dbf';

sys>> grant connect,resource to demo identified by demo;

sys>> alter user demo default tablespace demo;

sys>> select username,default_tablespace from dba_users;

Conn demo/demo

create table and insert some records

demo>> conn / as sysdba

sys>> alter system switch logfile;

Delete datafile

conn demo/demo

demo>>  inset records into table

Error will get and note datafile id

conn / as sysdba

sys>> alter database datafile 6 offline;

sys>> alter database create 6;

sys>> alter database datafile online;

sys>> alter database open;


<=========================>Loss of Redolog Files <=========================>


For recovery need privious CRD files backup

Privious backup contains CRD files

sqlplus / sysdba

sys>>startup

sys>> conn demo/demo

insert some records

sys>>conn / sysdba

sys>> alter system switch logfile;

sys>> conn demo/demo

sys>> insert some records into table


Delete all redolog files

 $ ps -eaf |grep -i smon

 $kill -9 10250

Copy the datafile and control file from previous backup

 $cp *.dbf ../

 $cp control.ctl ../

 sqlplus / as sysdba

 sys>>startup mount;

 sys>> alter database recover automatic using backup controlfile until cancel;

 sys>> recover cancel

 sys>> alter database open resetlogs;