Physical backup and recoveries with scenarios

 Physical Backups:
 ================
 it is categorized into cold back up and hot backup
 cold backup is a backup which is taken when DB is down and also called a offline backup
 Hot backup is a backup which is tajen when DB is up and running
 In order to take a cold backup we need to shutdown the database gracefully either using Shutdown immediate/transactional/normal
 In order to take a cold backup make sure your database maybe in archive mode or no archive mode
 If database runs in no archive mode we cannot perform recovery
 In case of complete recovery there is no loss of data 
 At the time of recovery oracle applies the archive logs in the same sequence ,if in case any archive logs were missing it will not apply the rest of the logs
 It is also one of the responsibility of the DBA to take the backup of archive logs everyday and has to delete the archive logs from the archive destination
 when we perform incomplete recovery we are going to open the database with reset logs option
 Once we open the database with resetlogs option then oracle will generate new incarnation number and creates brand new online redologs with (1,0) or (1,2)
 Whenever we loose the current online redologs and current control file the type of recovery we do is incomplete recovery



 Steps to take cold backup :
 =========================
 shutdown the database
 copy the CRD files to the desired backup location at OS level
 Note: At the time of taking backup it is always recommended to take the backup of archive logs ,pfile or spfile along with CRD files
 select * from v$database_incarnation;
 Note: whenever we create a database oracle assign one unique incarnation number to the database
 sql> select incarnation#,resetlogs_chnage#,resetlogs_id,prior_resetlogs_chnage#,status from v$database_incarnation;


 Scenarios:
 =========

 Take full database cold backup into backup location, lets consider as yesterdays backup


 Loss of full database:
 ======================
 Now login to scott account and insert some records in the employee table .......so that some archive logs will ve generated
Sql> conn scott/tiger
SQL> insert into emp1 select * from emp1;  //repeat it until it generated atleast 10 redologs
sql> select current_scn from v$database;
sql> archive log list 
SQL> shut immediate


Now go to the physical location oradata and move the CRD files to a different location
Now we are ready for the recovery

restore yesterdays backup
mount the database startup mount
recover automatic database using backup control file until cancel
alter database open resetlogs;
sql> select incarnation#,resetlogs_chnage#,resetlogs_id,prior_resetlogs_chnage#,status from v$database_incarnation;


 Loss of non system datafile:
 =========================
 Datafile offline method:
 ======================
 Alter database datafile 'c:\app\oracle\oradata\orcl\test.dbf'
 Restore the datafile using COPY command
 recover datafile 'c:\app\oracle\oradata\orcl\test.dbf'  or file#
 Alter database datafile 'c:\app\oracle\oradata\orcl\test.dbf'   online;

 Tablespace offline method:
 =======================
 sql> alter tablespace users offline;
 restore the datafile using CP command
 SQL>recover tablespace users
 sql>alter tablespace users online


 Loss of system datafile:
 ====================
 Shut abort
 restore system.dbf from the latest backup
 startup mount
 recover database
 alter database open

 Loss of sysaux datafile:
 =======================
 shut abort
 restore sysaux.dbf from latest backup
 recover datafile 'c:\app\oracle\oradata\orcl\sysaux.dbf'
 alter database datafile 'c:\app\oracle\oradata\orcl\sysaux.dbf' online;


 Loss of control file
 ====================
 shut abort
 recover control file from old backup or if you have any control file copy then create new one
 startup mount
 alter database recover automatic using backup controlfile until cancel;
 or
 recover automatic database using backup control file until cancel
 or
 recover database using backup control file until cancel;


 Loss of online redolog files:
 ==========================
 shut abort
 restore datafile from previous latest backup
 startup mount
 recover database until cancel;
 alter database open resetlogs

 Loss of datafile without having backup:
 ======================================

 Make the datafile offline  or tablespace offline
 Alter database create datafile 'c:\app\oracle\oradata\orcl\test.dbf'
 Recover datafile 'c:\app\oracle\oradata\orcl\test.dbf';
 Alter database datafile 5 online;

Point in time recovery:
======================
Actually from 10g onwards it is not required as we have flashback database concept

shut immediate
restore the latest cold backup
startup mount
alter database recover automatic using backup controlfile until time '2019-04-02:11:25:00' or yyyy-mm-dd:hh24:mi:ss
recover cancel
alter database open resetlogs;

Hot Backup:
==========
It is one kind of the backup we can implement for the 24/7 customers
It is also called as inconsisent or online backup
In order to take the online backup we need to keep the tablespace in 'BEGIN BACKUP ' mode .
When we keep a tablespace in begin backup mode headers of all the datafiles will be freezed,
Once the headers are freezed oracle will not update the SCN number which was generated
Once we end the begin backup mode oracle is going to update the SCN number
In hot backup database should run in archive mode
It is always recomended to keep the tablespace in begin backup mode for a shorter duration otherwise excessive redo information will be generated


Steps for hot backup:
====================

alter database begin backupl
take the backup of CRD files
alter database end backup
alter system switch logfile
al the end of every hot backup it is mandatory to switch the logfile . this causes a checkpoint and this checkpoint chnage number will be updated in control file and datafile header