Backup &Recovery Scenarios

Backup &Recovery Scenarios
by Muhammad Abdul Halim

To check database mode
> archive log list

To bring database in archive log mode
> startup mount
> alter database archivelog;

To start ARCN process
log_archive_start=true
log_archive-max_process=2(max 10)
> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
> ALTER SYSTEM ARCHIVE LOG START STOP

To define archive format
log_archive_format=TS%S.ARC

Data Dictionary Format
V$ARCHIVED_LOG V$ARCHIVE_DEST
V$LOG_HISTORY V$ARCHIVE_PROCESSES

To define archive destination : Up to 4 destination can be defined

log_archive_dest=’/clover/archive’
log_archive_duplex_dest=’/clover/archive’
OR
log_archive_dest_1=”LOCATION=/clover/arch”
Mandatory Reopen=500(Default 300) Optional
log_archive_min_succeed_dest=2
log_archive_dest_state_3=DEFER / ENABLE

> ALTER SYSTEM SET log_archive_dest_state_3=ENABLE;
log_archive_dest_state_2=”SERVICE=standby”

To defining archive destination in open database
> ALTER SYSTEM ARCHIVE LOG START TO ’/clover/arch’;

Selectively Archiving
> ALTER SYSTEM ARCHIVE LOG SEQUENCE 052;



BACKUP AND RECOVERY

Media Failure and Recovery:
Database in NOARCHIVELOG Mode
Failure : loss of disk,data file,or corruption
Recovery : Restore all Oracle files:
- Data files
- Control files
- Redo log files
- Password file (optional)
- Parameter file (optional)
Advantage
· Easy to perform with low risk of error
· Recovery time is the time it takes to restore all files.

Disadvantage
· Data is lost and must be reapplied manually
· The entire database is restored to the point of the last whole closed backup

Recovery
> shutdown
Restore all files using operating system
> startup

Restoring to a Different Location
> startup mount
> alter database rename file ’/clover/oradata/user_01.dbf’ to ’/clover/oradata/user_01.dbf’;
> alter database open;

Media Failure and Recovery:
ARCHIVELOG Mode
Failure : loss of disk,data file,or corruption
Recovery
- Data files for restore must be offline.
- Restore only lost or damaged data files
- Do not restore the control files, redo log files, password files, or parameter files
- Recover the data files.

Advantages
· Only need to restore lost files
· Recovers all data to the time of failure
· Recovery time is the time it takes to restore lost files and apply all archived log files

Disadvantages
· Must have all archived log files since the backup from which you are restoring

Complete recovery
· Closed database recovery for
- System data files
- Rollback segment data files
- Whole database
· Opened database recovery, with database initially opened : for file loss
· Opened database recovery with database initially closed : for hardware failure
· Data file recovery with no data file backup

Incomplete recovery
· Time based
· Cancel based
· Using control file
· Change based

Closed database backup
No archive log mode
Data files, Control files, Redolog files, Parameter file, Password file

> SHUTDOWN IMMEDIATE
cp /backup
> STARTUP OPEN;

Open database backup
Archive log mode
Data files, Control files, Parameter file, Password file
Arch process must be enabled
> alter tablespace TEST begin backup;
cp /clover/data/test.dbf /clover/backup/test.dbf
> alter tablespace TEST end backup;

Creating a binary image;
> alter database backup controlfile to ‘controll.bk
Creating a text trace file:
> alter database backup controlfile to trace;
Case Study for Backup and recovery

Backups

I. Closed database backup

Note : To keep backup, create one subfolder in your main folder Every day before shutdown delete all your backups.

Database may in no archive/archive log mode
Take backup of all files Data files, Control files, Redolog files,
Parameter file, Password file (optional )

> SHUTDOWN IMMEDIATE;
Copy all files to backup location using operating system.
cp /clover/backup/
> STARTUP OPEN;

II Open database backup

Database must be in archive log made
ARCN process must be enabled
Take backup of Data files, Control files
Password file and Parameter file is optional

> alter tablespace test begin backup;
Copy datafile of test tablespace to backup location using operating system.
cp /oracle/db01/data/test.dbf /oracle/backup/test.dbf

> alter tablespace test end backup;

Note : Perform open database backup for all tablespaces

Creating a binary image of control file
> alter database backup controlfile to ‘controll.bkp; path should be specified

Creating a text trace file
> alter database backup controlfile to trace; path should be specified

Recovery

Recovery in No Archive log mode :

Advantage : Easy to perform with minimum risk
Recovery time is time taken for restore the files from backup

Disadvantage : Data lost must be reapplied manually
Database is recovered to time of last backup.

Possible media failure : Loss of disk, datafile or corruption of datafile.

Requirement : Last valid closed database backup.

Recovery : Restore all datafiles, control files, redolog files.
Recover the database.

Note : if log sequence no is not changed after taking the backup no need restore all file.

Scenario 1

Shutdown the database.
> shutdown
copy all datafiles, control files, redolog files to backup folder using operating system.
Delete one datafile using operating system.
> startup ( Database will go to mount stage giving an error )

Recovery
Restore damaged datafile from backup using operating system and recover
cp /oracle/backup /oracle/db01/data

> alter database open ;

Scenerio 2

Force the log switch to change the log seq no.
> alter system switch logfile;
> alter system switch logfile;
> alter system switch logfile;
Shutdown the database.
> shutdown
Delete one datafile using operating system.
> startup ( Database will go to mount stage giving an error )

Recovery
Restore all files.
> alter database open ;

Recovery in Archive log mode : ( Complete Recovery )

Advantage : Restore damaged datafile
Recover all data to the time od failure
Recovery time is time taken to restore datafile and apllying the archive logs.

Disadvantage : Require all archive logs since the backup from which are restored

Possible media failure : Loss of disk , datafile or corruption of datafile.

Requirement : Last valid backup after seting database in archive log mode.
All archive logs and online redologs which are not yet archived
Recovery : Startup the database in mount/open stage
Restore damaged datafile from backup using operating system recover
Recover the datafile.

Recovery syntax
Recovering in mount stage
> Recover database
> Recover datafile ’’;
> Alter database recover database;

Manual recovery

If archive log destination is not defined in the parameter file
Oracle server has to be informed about the location of the file archive logs.
> Alter system archive log start to ;
To recover also you have to define the archive log location
> recover from <> database;

Automatic recovery

Before recover set auto recovery on.
> set auto recovery on;
> recover datafile ‘’;

Enter auto when prompted for a redolog file.
> Auto
Or
> recover automatic datafile ‘’

Check V$recover_file - which file need recovery
V$recovery_log - archive log need for recovery

Recovery in Archive log mode : ( Complete Recovery )

Scenario 3 ( In mount stage )

Force the log switch to change the log seq no.
> alter system switch logfile ;
> alter system switch logfile;
> alter system switch logfile ;
shutdown the database.
> shutdown immediate ;
Delete system datafile using operating system.
> startup ( Database will go to mount stage giving an error )

ORA-01157 : cannot identify/lock data file 1 – see DBWR trace file
ORA-01110 : data file 1 : ‘/clover/system01.dbf'

Restore system datafile from backup using oprating system and recover the datafile
> Recover datafile ‘/clover/data/sys.dbf’ ;
> Alter database open ;

Scenario 4 ( Initially closed, open stage )

Shutdown the database.
> shutdown immediate ;
Delete non system datafile using operating systm.
> startup ( Database will go to mount stage giving an error )

ORA-01157 : cannot Identify/lock data file 3 – see DBWR trade file
ORA-01110 : data file 3 : ‘/clover/test.dbf’

Check V$ datafile header

Take the lost datafile offline
> Alter database datafile ‘/clover/test.dbf’ offline;

Restore the lost datafile from backup and recover the datafile.
> Recover datafile /clover/test.dbf ;

Bring the datafile online.
> Alter database datafile ‘/clover/test.dbf’ online;

Note : if you want to restore the damaged datafile to different location oracle must
Be informed about new location by renaming recovery

> Alter database rename file ‘/clover/test.dbf’ to ‘/new/test.dbf’ ;

Backup Recovery

Recovery in Archive log mode : ( Complete Recovery )

Scenario 5 ( without backup )

Shutdown the database.
> shutdown immediate ;
Delete non system datafile using operating system.
> startup ( Database will go to mount stage giving an error )

ORA-01157 : cannot identify/lock data file 3 – see DBWR trace file
ORA-01110 : data file 3 : ‘/clover/test.dbf’

Take the lost datafile oflinne
> Alter database datafile ‘/clover/test.dbf’ ofline;
open the database
> Alter database open ;
Take database offile immediate to avoid check point trying to write to datafile.
> Alter tablespace test offline immediate ;

Since you do not have backup operate create the datafile
> Alter database create datafile ‘/clover/test.dbf’ as ‘/clover/test.dbf’;
Recover the Database
> Recover datafile ‘/clover/test.dbf’;
> Alter tablespace test online ;

Scenerio 6 Recovery of file in backup mode

Startup online backup
> Alter tablespace test begin backup ;
switch off the system and restart .
startup the database if it is already starting shut it down then startup. It will ask for media recovery
ORA-01113 : file 3 needs media recovery
ORA-01110 : data file 3 : ‘/clover/test.dbf’
Check V$Backup

> Recover datafile '/clover/test.dbf’;
OR
> Alter database datafile 3 end backup ;

check V$Backup

Since the datafile header was frozen the database files were not synchronized.

Loss of inactive Redolog files

If redo logs are lost recovery to the time of failure is not possible but if lost redologs are not current, the redolog has been archived and proper mulltiplexing of redo logs are available no data will be lost.

Scenario 7

Check V$Logfile for current logfile and delete one fo the redlog file which is not current.
Using operating system. Force the log switch

> Alter system switch logfile;

it will give an eror
ORA- 00313 : open failed for members of log group of thread 1

Incomplete Recovery

In incomplete recovery database will be recovered before the time of failure.

Possible Failures : A failed complete recovery operation, Important table in the Database is dropped, A control file is lost, Loss of redolog files

Rquirement : Valid online or offline backup of all the database files.
Need all archived logs ,Back up of control file.

Recovery : Shutdown and backup the database.
Restore all data files.
Do not restore the control file, redo logs, password files, or
Parameter files.
Mount the database and recover the data file before time of failure.
Perform a closed database backup.

Recovery Guidelines :
· Follow all steps: Most errors occur during this type of recovery.
· Whole-database backup before and after recovery assist future recovery.
· Always verify that the recovery is successful.
· Back up the control file regularly.
· Back up and remove archived logs.
· Database are brought forward in time, not bake in time.

Time based recovery

Scenerio 8 : A table is dropped at 10 am at 11 am user comes to know that the table view or table does not exist
Create a table test.
> Create table test ( n number );
Insert values
> Insert into test values (11111);
> Commit ; > Alter system switch logfile ; Note down the commit timing. Give some time gap and drop the table
> Drop table test ;
Shutdown the database
> Shutdown
Mount the databse
> Startup mount ; Restore all datafile from backup (most recent ) using operating system. And recover the database until time (Specify time before droping the table )
> Recover database until time ‘2001-11-12:09:30:00’; > Alter database open resetlogs ; After incomplete recovery take new backup of database.

Select * from nls_database_parameter;

Cancel based recovery

Scenario 9 : Lost a redolog file
Shutdown the database.
Take back up of all logfiles, datafiles, controlfiles
Delete one log file using operating system.
Mount the database
> startup mount ;
Restore all datafile from backup using system.
> recover database until cancel ;
> Alter database open resetlogs ;
Check for log file in v$ logfile

Change based recovery

Scenerio 10 : No back up was taken after reset logs, need is to use cold backup
Backup the data file and control file. ( cold backup )
Perform incomplete recovery ( To bring database in new incarnation you can recover by time based or cancel based )
Create a table insert some records commit. Shutdown the database.
> Shutdown
Restore cold data file and control file ( from cold backup)
Startup the database. ( You will get an error )
> Startup
Check V$Log for change seq #)
Recover until Change
> recover database until change <>;
> Alter database open resetlog :
Check for table created.

Scenerio 11
Recovery using backup controlfile
Tablespace was created at 10am and backup was taken and dropped at 2pm
Create a tablespace and create one table insert records into the table.
Take backup of all datafile and controlfile ( closed/open if you are taking open database backup first take backup of controlfile than datafile )
Drop the tablespace.
> Drop tablespace test including contents ;
Shutdown the database take backup of existing controlfile first than restore controlfile and datafile.
Startup the database it will give an error after taking the database in mount stage.
Make sure that all datafiles are online before recovery by checking v$recover_file Perform recovery using backup controlfile
> recover database until time ‘2001-11-11:13:10:00’ using backup controlfile;
> Alter database open resetlogs ; Check for the table.

Scenario 12
Backup was taken of both controlfile and datafile at 10 am. At 11 am Tablespace was created and at 1pm tablespace was dropped. Recover the tablespace using cold backup.

Scenario 13
A table test was created at 10am and dropped at 10.30 am, another table test1 was dropped at 10.45am. Recover the tables without losing any records in both the table.

Scenario 14
Recover the lost current control file, or the current control file is inconsistent with files that you need to recover

Scenario 15
Recover lost online redo logs.

Scenario 16
Recover new information that was not in the backup and was only stored in the archivelog files.

Scenario 17
How to recover a database having added a datafile since the last backup.

Scenario 18
If the database crashes during a hot backup.

source and reference:
http://halimdba.blogspot.com/2009/02/backup-seneries.html