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


Anonymous said...

On the subject of file backup, sharing and storage ...

Online backup is becoming common these days. It is estimated that 70-75% of all PC's will be connected to online backup services with in the next decade.

Thousands of online backup companies exist, from one guy operating in his apartment to fortune 500 companies.

Choosing the best online backup company will be very confusing and difficult. One website I find very helpful in making a decision to pick an online backup company is:


This site lists more than 400 online backup companies in its directory and ranks the top 25 on a monthly basis.

tjay said...
This comment has been removed by the author.
tjay said...

Hey Khurram

used for?


About Me

My photo
Melbourne, Victoria, Australia