in ,

How to tune instance recovery in Oracle?

For tuning instance recovery,you need to have knowledge about database architecture.Log writer(LGWR) process flushes redo log buffers every 3s or when buffer is 1/3rd full or every commit.

Now the thing is if I made redo log file groups then there is double overhead for log writer to write entries into these groups.Next step is writing these data in data files.

Consider R1,R2,R3 as redo log files. and RA1,RA2,RA3 as their copies(Backup). R1-RA1,R2-RA2,R3-RA3 are respective redo log groups.

So what is recovery here?

Its writing all the transactions in a  data file from redo log fie after instance failure(crash).It takes more time to write in a data file than redo log file.

Every 3s checkpoint process(CKPT) records information into control files about checkpoint info in redo log.Time requuired for instance recovery is the time required to bring the datafiles from their last checkpoint to latest SCN recorded in control file.

Need to set following parameters to tune it:

FAST_START_MTTR_TARGET  to service level agreement that your business required. In short in how much time you want recovery after last commit.

MTTR is effctive mean time to recovery and expressed in seconds..

Small the MTTR target less the time required to write transactions in data files from last commit or last checkpoint.

Default value to specify MTTR is 0s and maximum is 3600s.

Author Profile

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

What do you think?

400 Points
Upvote Downvote
Tax Calculation

Tax Calculation Logic Based On Tax Profile Of An Item


How to tune database buffer cache?