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 ***
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:
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;
--> 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 = '
SQL> commit;
For 11G+ catalog schemas:
SQL> update node set high_do_recid = 0 where db_key = '
SQL> commit;
