Re: Slow 'alter tablespace begin backup'

There are two possible waits with setting a tablespace into backup mode. The first
is that it can't be done until all current transactions on the database have completed (although new ones can start). If you are waiting for a transaction to complete, your session will be reporting a TX lock request in mode 4.

The other is the tablespace checkpoint. When you issue 'begin backup' all the dirty blocks in the tablespace have to be written to disc - and these can only be found by walking the entire checkpoint queue (and it is possible that Oracle still finds them by scanning the entire buffer rather than walking the queue). If this is happening, you will see your session holding (or possibly waiting for) a TC enqueue in mode 6 while the flush takes place.

The increase in the size of the block cache is likely to make the tablespace checkpoint take longer, whether or not Oracle was doing it the right way or the wrong way. You could reduce the time by changing your log_checkpoint_timeout, or the fast_start_mttr_target to make Oracle write dirty blocks more aggressively between checkpoints, but this might have other effects on the I/O subsystem that you don't want to put up with.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Andy Pennington" <andy_pennington_at_yahoo.com> wrote in message
news:60313e96.0312172149.32ba7799_at_posting.google.com...


> What influences the time taken for the 'alter tablespace begin backup'

> statement?

> Until a couple of weeks ago, I could get my entire database (50

> tablespaces, 1TB) into backup mode in about 10-15 minutes, but then

> all of a sudden it started taking 45-50 minutes to do the same action.

> The only change I am aware of which coincided, was an increase by 4GB

> (from 12GB to 16GB) in the data cache (db_block_buffers). I am

> interested to understand what actually happens as the 'begin backup'

> statement is executed. What does it wait for?