RMAN Slow performance with "control file sequential read" wait event

加入dbDao.com Oracle技术学习QQ群:171092051

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 400-690-3643 备用电话: 13764045638 邮箱:service@parnassusdata.com

 

We have a database which is about 40TB and the control file is about 900MB and we have about 1.5TB Archivelogs generation everyday.

So, was thinking it was because of all the above why “show all” takes about 45 minutes in RMAN…

But, for some reason I was not able to convince my self that it should take that long for just SHOW ALL command!!!.
Not only that, it takes way too long to just allocate channels and that was making our backups to take for ever…

Anyways, digging deeper found the following sql that just sits in there with “control file sequential read” wait event… This made me to hunt for the sql:

SELECT RECID, STAMP, TYPE OBJECT_TYPE, OBJECT_RECID, OBJECT_STAMP, OBJECT_DATA,
       TYPE, (CASE
                WHEN TYPE = ‘DATAFILE RENAME ON RESTORE’ THEN DF.NAME
WHEN TYPE = ‘TEMPFILE RENAME’ THEN TF.NAME
ELSE TO_CHAR (NULL)
 END)
  OBJECT_FNAME, (CASE
WHEN TYPE = ‘DATAFILE RENAME ON RESTORE’ THEN DF.CREATION_CHANGE#
WHEN TYPE = ‘TEMPFILE RENAME’ THEN TF.CREATION_CHANGE#
ELSE TO_NUMBER (NULL)
 END)
    OBJECT_CREATE_SCN, SET_STAMP, SET_COUNT
  FROM V$DELETED_OBJECT, V$DATAFILE DF, V$TEMPFILE TF
 WHERE OBJECT_DATA = DF.FILE#(+)
   AND OBJECT_DATA = TF.FILE#(+)
   AND RECID BETWEEN :b1 AND :b2
   AND (STAMP >= :b3 OR RECID = :b2)
   AND STAMP >= :b4
 ORDER BY RECID

That brought me into this metalink id# Rman Backup Very Slow After Mass Deletion Of Expired Backups (Disk, Tape) [ID 465378.1]

Work around solution made SHOW ALL to return the results in seconds from 45 minutes…

*** Please check with Oracle Support prior to deploying this workaround in your database to make user it is OK ***

Solution

Bug is fixed in 11g but until then, use the workaround of clearing the controlfile section which
houses v$deleted_object:
SQL> execute dbms_backup_restore.resetcfilesection(19);
Then clear the corresponding high water mark in the catalog :
SQL> select * from rc_database;
     –> note the ‘db_key’ and ‘dbinc_key’ of your target based on dbid

For pre-11G catalog schemas:

SQL> update dbinc set high_do_recid = 0 where db_key = ‘‘ and dbinc_key=;
SQL> commit;

For 11G+ catalog schemas:

SQL> update node set high_do_recid = 0 where db_key = ‘‘ and dbinc_key=;
SQL> commit;

关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号:

RMAN Slow performance with "control file sequential read" wait event

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569