I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Wednesday, July 9, 2008

How oracle makes media recovery

Oracle Instance shutdown with command 
SQL > Shutdown immediate

Oracle Instance shutdown with command 

Let me know How Oracle SCN concept will work in above mentioned scenario ? 

Oracle first check controlfile checkpoint SCN number ,if checkpoint SCN in the 
datafile header earlier then controlfile checkpoint SCN then oracle need 
redo,recovery is overall the process of applying redo to data files.In more 
depth recovery will synchronize the checkpoint SCN in the data files header to 
checkpoint SCN in control file header Or in broader term we can say goal of 
recovery is to make sure the CKPT SCN in each datafile header matches the CKPT 
SCN in the control file.

How SCN Synronization will happen ? 

When you shutdown database with the immediate/normal/transactional clause then 
oralce trigger a checkpoint where oracle makes the control files and datafiles 
consistent to the same checpoint SCN during a database checkpoint.

How Oracle Database will understand what file Needs Restoration and Recovery ? 

For restoration database file oracle ,at startup oracle probe controlfile 
(metadata) of yours database ,if it does not find the database file physically 
where it knows then it needs restoration of that concerned database file.Lets 
say you take online backup,if yours database file which is not available then 
restoration comes into play,yours restored file is some time back then now 
yours existence others database file specifiaclly with control file,controlfile 
checkpoint SCN header will be ahead with restored lost database file,oracle 
decide requires recovery.

What the meaning of SCN Synronization,If Database synronize Control File SCN 
number with DBF File SCN then how it will work when we loose Control File?

Always restoration of controlfile require recovery why..

As i understand it does not apply the redo to data files but an appearance just 
like to apply redo which is not actual (you can also check this appearance by 
restoring controlfile and then make recovery at sql by recover database until 
cancel and then dont apply redo at first attempt and apply cancel),it does like 
that cause old control file which doesn't know where the redo chain ends ,it 
may comes into situation where redo log ahead to old backup control file this 
appearance of recovery make datafile and controlfile consistent by 
compeling/forcing open resetlogs.


restored control file which doesn't know where the redo chain ends ,it may 
comes into situation where redo log ahead to old backup control file, after 
restoring controlfile which does not know the current log sequence number of 
redo log ,the existing current redo log sequence number does not match to 
restored controlfile for current log sequence number ,the controlfile 
checkpoint scn # would be earlier to datafile checkpoint scn #,this appearance 
of recovery make datafile and controlfile consistent as well restored 
controlfile adopt the new resetting log sequence number by compeling/forcing 
open resetlogs.

I can’t understand yet why online backup is inconsistent? I know only a restored Backup which has
data files SCN header (i.e. CKPT SCN) does not match to control files SCN header (i.e. CKPT SCN)
considered to be inconsistent backup?

You are right but more or less a backup in which some of the files in the backup contain changes
that were made after the files were check pointed. This type of backup needs recovery before it
can be made consistent.

Inconsistent backups are usually created by taking online database backups; that is the database
is open while the files are being backed up.

RMAN backup at blocks level ,whenever backup start a checkpoint also triggered  say in time "x"
at the same time "x" RMAN started to take blocks backup e.g. b1,b2,b3.

Say block b1 SCN would be 100 and CKPT SCN at the time "x" of backup started is 500, there is no
guarantee that block b1 SCN would be same throught the backup completion. It may be change and at
every change block b1 SCN will be incremented, and at some time block b1 SCN may go
beyond/greater then CKPT SCN 500, if that’s the case then  yours block b1 backup would be

Here see a demo

RMAN> backup database
2> ;

Meanwhile backup is going on the other hand i am doing some actitivity

SQL*Plus: Release - Production on Wed Jul 16 14:56:53 2008

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table v as select * from all_objects
  2  /

Table created.

SQL> create table v1 as select * from all_objects
  2  /

Table created.

SQL> create table v2 as select * from all_objects
  2  /

Table created.

SQL> create table v3 as select * from all_objects
  2  /

Table created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete alls datafiles here except redo log and control files after shutting down database..

After that start up database

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\SDS\SYSTEM01.DBF'

What i will do i will restore database files from inconsistent/online/hot backup.


Recovery Manager: Release - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@sds

connected to target database: SDS (DBID=2656018210)

RMAN> restore database
2> ;

Starting restore at 16-JUL-08
Finished restore at 16-JUL-08

After restore check yours inconsistent backup would have fuzzy block

SQL> select checkpoint_change#,absolute_fuzzy_change#,recovery_fuzzy_change#
  2    from v$datafile_copy
  3  /

------------------ ---------------------- ----------------------
            682999                      0                      0
            682999                      0                      0
            682999                      0                      0
            682999                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0

------------------ ---------------------- ----------------------
            684218                      0                      0
            816371                      0                      0
            816371                      0                      0
            816371                      0                      0
            816371                 816527               0
16 rows selected.


If yours any block during backup get higher SCN then checkpoint SCN it would be considered fuzzy
block which make yours whole database backup inconsistent, oracle always requires recovery (redo)
in this situation to make inconsistent restored backup to consistent.

As far as concern about yours question that you didn’t do any activity so there would not be any
absolute fuzzy change number (block SCN > CKTP SCN), you are right but Oracle will always require

Wednesday, July 2, 2008

FRA Capacity Planning

Kindly suggest some tips and how to determine my FRA?

First oracle recommended tip is to always keep yours FRA far away from yours Database location
possibly keep it at hard another hard drive, if possible keep this hard drive with separate

For FRA sizing decide how many days database backup you could maintain within Yours hard disk,
this could be determined by yours SLA ,usually there is maintained two days backup within hard
disk either it is FRA or non FRA,the rest of database backup as well current two days backup
should be transferred to tape cartridge. Typically what I do within my environment could be
helpful for you.

I keep at least last two current days backup for the entire database at hard hard disk within
FRA,beyond 2 days and these last two current days backup I move to tape cartridge, according to
business rule I maintain 30 tape cartridge for 30 days backup to revert back in case of any harm,
I rotate the same cartridge which lapse after 30 days.

i.e JAN tape1..tape30
    FEB tape1..tape30
    MAR tape1..tape30

You can dedicate yours FRA space by cheking yours database file size not with actual data by 

select sum(bytes) from v$datafile

Lets say if yours database file size about 80GB then you can either dedicate
2*80+20% extra for yours FRA.I maintain my retention policy for redundancy 2
days.After taking 3rd backup my first backup obsolete and before that any
archivelog also obsolete and can be reclaimable by deleting obsolete via RMAN.

For archivelogs pace you can observe 

select sum(blocks*block_size) archivebytes 
  from v$archived_log
where completion_time > sysdate-1 and status='A'

If you maintain yours archivelog within FRA then you also need to accomodate space for archivelog
within FRA,its recommended to put alls backup stuff within one single place which provide you FRA.

If you decide to put yours archivelog within FRA then yours estimation would 
become (2*80+archivebyte) x+20% x.

The above clue will determine you what should be the size of FRA.

Why last two current day’s backup should be at hard disk (FRA flash recovery Area) why not last
one current backup or why not last three current backup or why not last x backup or why is keep
backup at hard disk at all when I move it to tape cartridge, why to waste space when company
gives me tape cartridge space for each day backup?

the reason is that if you don’t keep any backup at hard disk then it will increase yours downtime
in case of hard disk failure or any failure which should not be for a good DBA,you will have to
have move last current backup from tape cartridge to hard disk (i.e. FRA) which will take x
hours/mins to be restored at hard disk (i.e.FRA), keeping at least one last current backup at
hard disk will save yours time from tape to hard disk. But if you keep 2 last current backup then
it would be safer in case of any mishap with last current backup, you will be able to restore
prior backup to last current one backup and then apply two days log for example

If you keep two last current backup at hard disk (FRA)

JAN 3<----------hard disk fail

You will restore Jan 2 backup but unfortunately yours 2nd Jan backup corrupted for any reason
then you can go with 1st Jan backup, you will restore 1st Jan backup apply archive log till
before the hard disk failure.

If you don’t keep last two backup just keep only last current backup ,in any case yours last
current backup corrupted then again you will have to have move last current backup from tape
cartridge to hard disk (i.e. FRA) which will take x hours/mins to be restored at hard disk (i.e.
FRA),if you came across that tape cartridge last current moved backup also not restorable from
tape cartridge then you will go with previous current backup and apply archivelogs till before
hard disk failure, think isn’t good if you save x hours/min to move backup from tape to hard disk
to make yourself and yours company downtime impact for the cost of purchasing more space for

Nevertheless if you can accommodate last 3 backups or more then that then I don’t think so there
is any harm but don’t rely at all at yours hard disk backup, move backup also to reliable tape
cartridge media.

Note : Above clue is not for flashback database technology.


About Me

My photo
Melbourne, Victoria, Australia