Thursday, July 31, 2008
Wednesday, July 9, 2008
How oracle makes media recovery
Oracle Instance shutdown with command SQL > Shutdown immediate Oracle Instance shutdown with command SQL > SHUTDOWN ABORT 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. OR 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 inconsistent. Here see a demo RMAN> backup database 2> ; Meanwhile backup is going on the other hand i am doing some actitivity SQL*Plus: Release 10.1.0.2.0 - 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 10.1.0.2.0 - 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. C:\>rman Recovery Manager: Release 10.1.0.2.0 - 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 / CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# RECOVERY_FUZZY_CHANGE# ------------------ ---------------------- ---------------------- 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 CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# RECOVERY_FUZZY_CHANGE# ------------------ ---------------------- ---------------------- 684218 0 0 816371 0 0 816371 0 0 816371 0 0 816371 816527 0 16 rows selected. SQL> 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 recovery
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
controller.
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 1
JAN 2
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
backup.
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.
Subscribe to:
Posts (Atom)
