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 < location
> 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 < change seq#>;
>
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.
No comments:
Post a Comment