|
|
DB:10.2.0.5
OS:solaris 10
存在的问题：
rman中执行crosscheck backup七个小时无返回结果，导致备份无法正常结束
数据库等待事件一直是控制文件顺序读，对应的SQL是：
SELECT :B15 TYPE_CON, BP.RECID KEY_CON, BP.RECID RECID_CON,
BP.STAMP STAMP_CON, BS.SET_STAMP SETSTAMP_CON, BS.SET_COUNT
SETCOUNT_CON, BS.RECID BSRECID_CON, BS.STAMP BSSTAMP_CON,
BS.RECID BSKEY_CON, BS.INCREMENTAL_LEVEL BSLEVEL_CON,
BS.BACKUP_TYPE BSTYPE_CON, BS.ELAPSED_SECONDS ELAPSESECS_CON,
BS.PIECES PIECECOUNT_CON, BP.HANDLE FILENAME_CON, BP.TAG
TAG_CON, BP.COPY# COPYNUMBER_CON, BP.STATUS STATUS_CON,
CEIL(BP.BYTES / BS.BLOCK_SIZE) BLOCKS_CON, BS.BLOCK_SIZE
BLOCKSIZE_CON, BP.DEVICE_TYPE DEVICETYPE_CON, BS.COMPLETION_TIME
COMPTIME_CON, TO_DATE(NULL) CFCREATIONTIME_CON, BP.PIECE#
PIECENUMBER_CON, BP.COMPLETION_TIME BPCOMPTIME_CON,
BP.COMPRESSED BPCOMPRESSED_CON, TO_NUMBER(NULL) TYPE_ACT,
TO_NUMBER(NULL) FROMSCN_ACT, TO_NUMBER(NULL) TOSCN_ACT,
TO_DATE(NULL) TOTIME_ACT, TO_NUMBER(NULL) RLGSCN_ACT,
TO_DATE(NULL) RLGTIME_ACT, TO_NUMBER(NULL) DBINCKEY_ACT,
TO_NUMBER(NULL) LEVEL_ACT, TO_NUMBER(NULL) DFNUMBER_OBJ,
TO_NUMBER(NULL) DFCREATIONSCN_OBJ, TO_NUMBER(NULL)
CFSEQUENCE_OBJ, TO_DATE(NULL) CFDATE_OBJ, TO_NUMBER(NULL)
LOGSEQUENCE_OBJ, TO_NUMBER(NULL) LOGTHREAD_OBJ, TO_NUMBER(NULL)
LOGRLGSCN_OBJ, TO_DATE(NULL) LOGRLGTIME_OBJ, TO_NUMBER(NULL)
LOGLOWSCN_OBJ, TO_DATE(NULL) LOGLOWTIME_OBJ, TO_NUMBER(NULL)
LOGNEXTSCN_OBJ, TO_DATE(NULL) LOGNEXTTIME_OBJ, TO_CHAR(NULL)
LOGTERMINAL_OBJ, TO_CHAR(NULL) CFTYPE_OBJ, DECODE
(BS.KEEP_OPTIONS, 'LOGS' , :B14 , 'NOLOGS' , :B13 , 'CONSISTENT'
, :B12 , :B11 ) KEEP_OPTIONS, BS.KEEP_UNTIL KEEP_UNTIL,
TO_NUMBER(NULL) AFZSCN_ACT, TO_DATE(NULL) RFZTIME_ACT,
TO_NUMBER(NULL) RFZSCN_ACT, BP.MEDIA MEDIA_CON,
BP.IS_RECOVERY_DEST_FILE ISRDF_CON FROM V$BACKUP_PIECE BP,
V$BACKUP_SET BS WHERE (BP.SET_COUNT = BS.SET_COUNT AND
BP.SET_STAMP = BS.SET_STAMP) AND (:B10 IS NULL OR
BS.COMPLETION_TIME >= :B10 ) AND (:B9 IS NULL OR
BS.COMPLETION_TIME <= :B9 ) AND (:B8 IS NULL OR BP.TAG = :B8 )
AND (:B7 = :B1 OR DBMS_RCVMAN.ISDEVICETYPEALLOCATED(BP.DEVICE_TYP
E) = :B1 ) AND DECODE(:B4 , :B6 , DECODE(BP.STATUS, 'A', :B1 ,
:B5 ), DBMS_RCVMAN.ISSTATUSMATCH(BP.STATUS, :B4 )) = :B1 AND
(:B3 = 0 OR BP.IS_RECOVERY_DEST_FILE = 'YES') AND (:B2 IS NULL
OR DBMS_RCVMAN.ISBACKUPTYPEMATCH(BS.BACKUP_TYPE, :B2 ) = :B1 )
ORDER BY BS.RECID, BP.DEVICE_TYPE, BP.TAG, BP.COPY#, BP.PIECE#
执行计划是：
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 602 | 1687 (100)| 00:00:21 |
| 1 | SORT ORDER BY | | 1 | 602 | 1687 (100)| 00:00:21 |
| 2 | NESTED LOOPS | | 1 | 602 | 1686 (100)| 00:00:21 |
|* 3 | FIXED TABLE FULL| X$KCCBS | 24396 | 3645K| 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FULL| X$KCCBP | 1 | 449 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
另外v$archived_log,和v$backup_pice两者记录都接近两万条
各位兄弟，谁遇到过相同 的情况？请给个指点，谢谢！
|
|
发表于 2014-9-1 20:21:17
楼主
