问题和解决办法来源说明：
该文档其实原创作者为renfengjun ，最初是在论坛上看到该问题，我自己也遇到了这个情况，上网一搜，正好遇到，上MOS上找到了相关凭据，在到renfengjun兄弟的博客上查找，发现没有该主题的技术文章，所以我干脆记录下来了，整理成文，以备查询。
http://blog.csdn.net/renfengjun
#################################################
rmanBUG：
现象：
描述起来非常简单，执行rman备份的时候卡住不动了，没有任何动静，1个多小时也不动。就算备份的是spfile这种小文件也是一样的情况。
解决问题的过程和思路：
就是个bug，也没有仔细整理。下面是我检查的步骤：
1.检查告警日志，考虑控制文件极限值问题
alter system archive log current ;
check the alert log file 这里是怀疑oracle无法读写controlfile
check the controlfile size 这里怀疑controlfile达到了极限值
20000*8=156M
du -sk *
2. 检查相关备份设备，如磁带等
检查是否因为空间不足等原因导致不能继续备份。
show all 看相关的设置，涉及sbt_tape全部disable --这里怀疑和media manager，第三方备份软件有关
3. 创建snapshot 监控awr
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
4. backup current controlfile 到共享硬盘
期间监控：
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, sw.STATE, CLIENT_INFO FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p WHERE sw.EVENT LIKE 'sbt%' AND s.SID=sw.SID AND s.PADDR=p.ADDR ;
5. 半个小时snapshot
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
6. 停止backup current controlfile
7. awr报告
8. debug rman
C:\Users\dex>rman target / log rman1.log trace rman1.trc debug
run{
debug on
backup current controlfile format '' ;
debug off
}
9. trace 10046 level 12
%rman target / log rman.log trace rman.trc debug
run {
sql "alter session set events ''10046 trace name context forever, level 12''";
set command id to 'rman';
allocate channel for maintenance type sbt…………..;
backup database;
}
SELECT sid, spid, client_info FROM v$process p, v$session s
WHERE p.addr = s.paddr AND client_info LIKE '%id=rman%';
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, sw.STATE, CLIENT_INFO FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p WHERE sw.EVENT LIKE 'sbt%' AND s.SID=sw.SID AND s.PADDR=p.ADDR ;
10. check snapshot controlfile
上面的几步没有任何问题，任何发现。最后在mos上找到了描述和本环境非常相似的文章。605557.1
确定可能是因为bug 6412947
解决方法是升级或者修改隐含参数
alter system set "_dra_enable_offline_dictionary "=false ;
确定_dra_enable_offline_dictionary不会对生产环境产生负面影响后，修改此参数。
OK了，备份正确运行。
MOS相关文档说明如下：
Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.6 - Release: 11.1 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 27-Dec-2011***
Symptoms
RMAN backups can take a long time in 11.1.0.6.
An increase in memory used by logminer may also be noticed. In once case, the ORA-4030 errors were reported, and lead to a database crash
This problem can affect all platforms. Additionally, 3 of the reports have been for customers with PeopleSoft installed.
Cause
This is caused by bug <<6412947>>.
Solution
To workaround the problem, switch off the logminer setup for DRA by setting:
_dra_enable_offline_dictionary = FALSE
#######################################################################
***Checked for relevance on 28-Jun-2009***
***Checked for relevance on 29-Mar-2012***
PURPOSE
=======
This note discusses resolving an RMAN Hung Job
SCOPE & APPLICATION
===================
Anyone involved in running RMAN jobs
Resolving an RMAN Hung Job
==========================
Components of an RMAN Session
The nature of an RMAN session depends on the operating system. In UNIX,
an RMAN session has the following processes associated with it:
- The RMAN process itself.
- The catalog connection to the recovery catalog database--if using a
recovery catalog, none otherwise.
- The connection to the target database, also called the default channel.
- A polling connection to the target database used for RPC testing of each
different connect string used in the allocate channel command. By default
there is no connect string in allocate channel and so there is only one
RPC connection.
- One target connection to the target database corresponding to each
allocated channel.
Process Behavior During a Hung Job
RMAN usually hangs because one of the channel connections is waiting in the
media manager code for a tape resource. The catalog connection and the default
channel seem to hang because they are waiting for RMAN to tell them what to do.
Polling connections seem to be in an infinite loop while polling the RPC under
the control of the RMAN process.
If you kill the RMAN process itself, then you also kill the catalog connection,
the default channel, and the polling connections. Target connections that are not
hung in the media manager code also terminate: only the target connection executing
in the media management layer remains active. You must manually kill this process
because terminating its session does not kill it. Even after termination, the media
manager may keep resources busy or continue processing because it does not realize
that the Oracle process is gone. This behavior is media manager-dependent.
Terminating the catalog connection does not cause RMAN to finish because RMAN is
not performing catalog operations. Removing default channel and polling connections
cause the RMAN process to detect that one of the channels has died and then proceed
to exit. In this case, the connections to the hung channels remain active as
described above.
Terminating an RMAN Session
The best way to terminate RMAN when the connections for the allocated channels
are hung in the media manager is to kill the Oracle process of the connections.
The RMAN process detects this termination and proceed to exit, removing all
connections except target connections that are still operative in the media
management layer. The caveat about the media manager resources still applies
in this case.
To identify and terminate an oracle process that is hung in the media manager code:
This procedure is system-specific. See your operating system-specific documentation
for the relevant commands.
1. Obtain the current stack trace for the desired process id using a system-specific
utility. For example, on Sun Solaris you can use the command pstack located in
/usr/proc/bin to obtain the stack.
2. After the stack is obtained, look for the process with SBTxxxx (normally sbtopen)
as one of its top calls. Note that other layers may appear on top of it.
3. Obtain the stack again after a few minutes. If the same stack trace is returned,
then you have identified the hung process.
4. Kill the hung process using a system-specific utility. For example,
on Sun Solaris execute a kill -9 command.
5. Repeat this procedure for all hung channels in the media management code.
6. Check that the media manager also clears its processes, otherwise the next
backup or restore may still hang due to the previous hang. In some media
managers, the only solution is too shut down and restart the media manager
daemons. If the documentation from the media manager is unhelpful, ask the
media manager technical support for the correct solution.
Backup Job Is Hanging
In this scenario, an RMAN backup job starts as normal and then pauses inexplicably:
Recovery Manager: Release 8.1.5.0.0 - Production
RMAN-06005: connected to target database: TORPEDO
RMAN-06008: connected to recovery catalog database
RMAN> run {
2> allocate channel t1 type "SBT_TAPE";
3> backup
4> tablespace system,users; }
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: t1
RMAN-08500: channel t1: sid=16 devtype=SBT_TAPE
RMAN-03022: compiling command: backup
RMAN-03023: executing command: backup
RMAN-08008: channel t1: starting datafile backupset
RMAN-08502: set_count=15 set_stamp=338309600
RMAN-08010: channel t1: including datafile 2 in backupset
RMAN-08010: channel t1: including datafile 1 in backupset
RMAN-08011: channel t1: including current controlfile in backupset
# Hanging here for 30 min now
Diagnosis of the Cause
If a backup job is hanging, that is, not proceeding, then several scenarios
are possible:
- The job abnormally terminated.
- A server-side or media management error occurred.
- RMAN is waiting for an event such as the insertion of a new cassette into
the tape device.
Your first task is to try to determine which of these scenarios is the most
likely cause.
To determine the cause of the hang:
1. If you are using a media manager, examine media manager process, log, and trace
files for signs of abnormal termination or other errors (see the description of
message files in "Identifying Types of Message Output"). If this information is
not helpful, proceed to the next step.
2. Restart RMAN and turn on debugging, making sure to specify a trace file to
contain the output. For example, enter:
% rman target / catalog rman/rman@catdb debug trace = /oracle/log
3. Re-execute the job:
run {
allocate channel c1 type 'sbt_tape';
backup tablespace system;
}
4. Examine the debugging output to determine where RMAN is hanging.
The output will most likely indicate that the last RPC sent from the
client to the server was SYS.DBMS_BACKUP_RESTORE.BACKUPPIECECREATE,
which is the call that causes the server to interact with the media
manager to write the backup data:
krmxrpc: xc=6897512 starting long running RPC #13 to target: DBMS_BACKUP_RESTORE.
BACKUPPIECECREATE
krmxr: xc=6897512 started long running rpc
5. Check to see what the server processes performing the backup are doing.
How many processes are hanging? If only one, check to see what it is doing
by querying V$SESSION_WAIT. For example, to determine what process 12 is
doing, enter:
SELECT * FROM v$session_wait WHERE wait_time = 0 AND sid = 12;
6. If a backup to tape stalls at the beginning, issue the following query:
SELECT * FROM v$session_longops WHERE compnam = 'dbms_backup_restore'; --> for 8.0
SELECT * FROM v$session_longops WHERE substr(opname,1,4)='RMAN'; --> for 8.1 and later
If Oracle returns no information, then the PL/SQL program performing the backup is hung.
Solution
Because the causes of a hung backup job can be varied, so are the solutions.
The best practice is to look for the simplest solutions first. For example,
it is quite common for backup jobs to hang simply because the tape device has
completely filled the current cassette and is waiting for a new tape to be
inserted. Look for the obvious in all components used for the backup when
problems occur.
RELATED DOCUMENTS
=================
If the job is not hung but just slow, check below documents:
Note:247611.1 Known RMAN Performance Problems
Note:605557.1 RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
Applies to:
Oracle Server - Enterprise Edition - Version 8.0.3.0 to 11.2.0.0. [Release 8.0.3 to 11.2]
Information in this document applies to any platform.
Purpose
Give an overview of known performance problems with RMAN Backups and Restores
In case you may want or need more about your current topic - please also access the Backup & Recover Community of Customers and Oracle Specialists directly via:
https://communities.oracle.com/portal/server.pt/community/database_backup_and_recovery/243
Troubleshooting Steps
SCOPE & APPLICATION
-------------------
This document is intended for DBA's who are facing performance issues
during the backup or restore via RMAN.
Its intention is to give an overview of the know performance
issues with RMAN and not to give any diagnosis. The diagnosis and
investigation is handled more in depth in other documents as
referenced at the bottom of this document.
KNOW RMAN PERFORMANCE ISSUES
-----------------------------
Note :
Generic Recommendation for Oracle 10G
Generate statistics on the fixed objects, using
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.
This should be done when the database has been running
for awhile with generic workload, so the information
in the fixed objects reflect a resonable state of the database load.
Gather the statistics with :
SQL> exec dbms_stats.gather_fixed_objects_stats
Alert Note 463227.1 Oracle10g RMAN Recovery Catalog Known Performance Issues
Unpublished Bug 11907498 - BACKUP SLOWS DOWN WHEN FRA IS NEARLY FULL
Version affected: 10.2.0.5, 11.2.0.2
Version fixed : 11.2.0.3,
11.2.0.2 Bundle Patch 9 for Exadata Database
11.2.0.1 Bundle Patch 11 for Exadata Database
( one-off patches available on certain versions and platforms)
Affects : Performance Of Certain Operations Affected
Workaround : see (Doc ID 11907498.8) Bug 11907498 - RMAN backups slow down when the Flash Recovery Area (FRA) is used 85%.
Bug 12585543 RMAN BACKUP PERFORMANCE DROPS SIGNIFICANTLY AFTER UPGRADE TO 11.2
Version affected: Versions >= 11.2 and < 12.1
Version fixed : Fixed in 11.2.0.4 and 12c, one-off patches available on certain versions and platforms
Affects : Target database, having DBWR_IO_SLAVES>0 and DISK_ASYNCH_IO=FALSE
Workaround : Use ASYNC IO
Bug 11872103 RMAN RESYNC CATALOG VERY SLOW:
Version affected: Versions BELOW 12.1
Version fixed : Fixed in 11.2.0.3 and 12c
Affects : Target database
Workaround : See Note 1340991.1
Reference : Note 1340991.1 RMAN RESYNC CATALOG is generating High amounts of Redo
Bug 10104567 - Poor performance querying V$DATAFILE
Version affected: Versions BELOW 12.1
Version fixed : some one-off patches for 10.2.0.5, 11.1.0.7, planned to be fixed in 11.2.0.4
Affects : Target database
Workaround : None.
Reference : Note 1354528.1: RMAN commands or SELECT on V$DATAFILE very SLOW / HANG / Poor Performance for database with many datafiles
Bug 9363515 - RMAN resync hangs when MEMORY_TARGET is set and PGA_AGGREGATE_TARGET becomes zero
Version affected: Versions >= 10.2 but BELOW 12.1
Version fixed : some one-off patches for 11.1.0.7, fixed in 11.2.0.2
Affects : Target database
Workaround : Set parameter workarea_size_policy to MANUAL.
Bug 6457435 - RMAN resync takes long time when v$deleted_object contains too many rows.
Version affected: 10.2.0.4 and 11.1.0.6
Version fixed : fixed in 10.2.0.5, 11.1.0.7 and 11.2
Affects : Target database
Workaround : Re-create the controlfile to reduce the number of rows in V$DELETED_OBJECT
Reference : Note 6457435.8 Bug 6457435 - RMAN resync takes long time when v$deleted_object contains too many rows.
Bug 7444008 - RMAN DUPLICATE COMMAND SLOW AT CATALOGING ALL DATAFILECOPIES
Version affected: 10.2.0.1 to 10.2.0.4, 11.1
Version fixed : some one-off backports for 10.2.0.4 , fixed in 11.2
Affects : RMAN-executable
Workaround : none
Bug 6051693 SLOW RESYNC AND SHOW ALL IS TAKING AROUND 2 MINUTES
Duplicate of Bug 5620640
Note 820856.1 High Waits On "Control File Sequential Read" During Rman Backup
Version affected: 10.2.0.1 to 10.2.0.3
Version fixed : some one-off backports for 10.2.0.3 , fixed in 10.2.0.4
Affects : Target database
Workaround : none
Unpublished Bug 8239313 Duplicate database for a very large database of around 3000 datafiles is very slow.
Version affected: 10.2.0.3 and higher
Version fixed : 11.2
Affects : Target database
Workaround : none
Bug 5251842 Poor plan against V$DATAFILE
Version affected: 10.2.0.3 and higher
Version fixed : 11.1.0.7 and 11.2 and some one-off for 10.2.0.3 | 4
Affects : Target database
Workaround : none
Bug 7023147: V$LOG_HISTORY queries can be slow
Version affected: 10.2.0.2 and higher
Version fixed : 10.2.0.5 and 11.2
Affects : Target database
Workaround : Recreate the controlfile
Note : 882154.1 RMAN LIST BACKUP command takes a long time querying v$log_history
Bug 7595777 RMAN TAKES A LONG TIME BEFORE STARTING THE BACKUP
Version affected: 10.2.0.3 and higher
Version fixed : 10.2.0.5 and 11.2
Affects : Target database
Workaround : Replace wasresynced function ?/rdbms/admin/recover.bsq with the following:
---------------------------------
function wasresynced(until_stamp IN number
,high_stamp IN number) return number is
nodups number; -- number of duplicates
high number;
low number;
resyncstamp number;
begin
high := high_stamp;
low := until_stamp;
nodups := 0;
resyncstamp := 0;
deb('resync', 'wasresynced high_stamp=' || high_stamp ||
' high_date=' || stamp2date(high_stamp), dbtype);
for duprec in duprec_c(low, high) loop
if (dbms_rcvcat.isDuplicateRecord(recid => duprec.recid
,stamp => duprec.stamp
,type => duprec.type)) then
if (resyncstamp = 0) then
resyncstamp := duprec.stamp;
end if;
nodups := nodups + 1;
if (nodups >= maxdups) then
deb('resync', 'wasresynced resyncstamp=' || resyncstamp ||
' resyncdate=' || stamp2date(resyncstamp), dbtype);
return resyncstamp;
end if;
else -- couldn't find 16 consecutive duplicate records.
deb('resync', 'wasresynced could not find record recid=' ||
duprec.recid || ' stamp=' || duprec.stamp || ' type=' ||
duprec.type || ' maxdups=' || nodups, dbtype);
return 0;
end if;
end loop;
-- Timestamp range not enough to satisfy the number of duplicates.
-- Retry using a higher timestamp
deb('resync', 'timestamp range not enough - nodups=' || nodups, dbtype);
return -1;
end;
-------------------------------------
Bug 7206538 PERFORMANCE ISSUE WITH RMAN BACKUP
Bug 6412947 DRA PERFORMANCE ISSUES
Version affected: 11.1.0.6
Version fixed : 11.1.0.7
Affects : Target database
Workaround : set parameter "_dra_enable_offline_dictionary"=false
Reference : Note 605557.1 RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
Bug 7173341 - CLEANUPRSR IS TAKING VERY LONG TIME CAUSING SLOW RESYNC
Version affected: 10.2.0.4
Version fixed : 11.2
Affects : Catalog Schema
Workaround : Change the 2 following SQL-statements in the recover.bsq
Replace the 'SELECT max(rsr_key)' statement in cleanupROUT:
SELECT max(rsr_key) into high_session_key
FROM rsr, dbinc
WHERE dbinc.db_key = this_db_key
AND rsr.dbinc_key = dbinc.dbinc_key
AND rsr.rsr_stamp < high_stamp;
Replace the 'DELETE FROM rsr' statement in cleanupRSR:
DELETE FROM rsr
WHERE rsr_end < nowTime-60
AND rsr.dbinc_key IN
(select dbinc_key from dbinc
where dbinc.db_key = this_db_key);
Upgrade the catalog to pick up the change in recover.bsq file.
Bug 6476935 - RMAN Resyncs may take a long time to complete
Version affected: 10.2.0.3
Version fixed : 11.2
Affects : Catalog Schema
Workaround : create index rsr_i_stamp on rsr(rsr_sstamp, rsr_srecid);
Note 433335.1 - RMAN Restore Of Incremental Backups Will Not Parallelise and Uses Only One Channel
Version affected: 10.1.0.0 to 10.2.0.4
Versions fixed : Expected behaviour - media managers can influence how we do Backups @10g
Affects : Target database
Workaround : SET PARALLELMEDIARESTORE OFF;
See Note 433335.1
Note 413098.1 - Extremely Poor RMAN Backup Performance to NFS After Upgrade to 10.2 on Solaris
Version affected: 10.2.0.0 to 10.2.0.4
Version fixed : 10.2.0.3 for NON-RAC applications only:
Affects : Target database
Diagnosis : 10.2 forces the use of directio when writing to NFS in a
RAC implementation, this is done regardless of whether or
Not you use the forcedirectio nfs mount option: this
results in very poor performance
Workaround : None - do not write backups to NFS at 10.2 when using RAC
See Note 413098.1
Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
Version affected: 10.1
Version fixed : 10.2.0.4
Affects : Catalog Schema
Diagnosis : Many rows in ROUT-table.
Workaround : See Note 378234.1 Rman Catalog Resync Operation is Very slow at 10G
Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
Version affected: 10.1
Version fixed : 10.2 due to base bug 3325292 OPTIMIZER NOT ABLE TO USE INDEXES IN QUERY USING V$SQL_PLAN
Affects : Target database
Diagnosis : Expensive statement =
select round(sum(MBYTES_PROCESSED)) ,round(sum(INPUT_BYTES)),
round(sum(OUTPUT_BYTES))
from V$RMAN_STATUS
start with (RECID=:b1 and STAMP=:b2)
connect by prior RECID=parent_recid
Workaround : RMAN> sql "alter session set optimizer_mode=RULE";
Remark : It might be that bug 5247609 is a followup / new occurence of this bug
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Version affected: 10.2
Version fixed : 10.4
Affects : Target database
Diagnosis : Expensive statement =
select round(sum(MBYTES_PROCESSED)) ,round(sum(INPUT_BYTES)),
round(sum(OUTPUT_BYTES))
from V$RMAN_STATUS
start with (RECID=:b1 and STAMP=:b2)
connect by prior RECID=parent_recid
Workaround : RMAN> sql "alter session set optimizer_mode=RULE";
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Version affected: 8.1
Version fixed : 9i
Affects : Catalog Schema
Diagnosis : The table CKP in the RMAN-catalog has many rows.
Workaround : Call Oracle Support to supply a cleanup-script.
Reference : Note.209780.1 RMAN Hangs During Restore if CKP Table is Excessively Large
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Version affected: 9.2
Version fixed : 9205, 10g
Affects : Catalog Schema
Diagnosis : Bad execution plan for SQL-statement : DELETE FROM CKP
Reference : Note 248361.1 RMAN Command 'Resync Catalog' is Very Slow
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Version affected: 9i
Version fixed : 9207, 10.1.0.5, 10gRelease2
Affects : Catalog Schema
Diagnosis : Large number of backupsets.
LIST/CROSSCHECK commands(in 9iR2 and above) taking more time.
REPORT/DELETE obsolete (in 9iR2) takes more time.
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Version affected: 8i
Version fixed : No fix available.
Affects : Catalog Schema
Diagnosis : See this note for details on how to confirm if you have hit this problem.
Workaround : RMAN> sql "alter session set optimizer_mode=RULE";
Cleanup backup history - see this note for hints on how to do this at 8i.
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Version affected: 9i and 10G
Version fixed : 10.2.0.2, 11G
Affects : RMAN executable, Target database, Catalog Schema
Diagnosis : Restore archivelog takes long compilation time either during
RESTORE command or during RECOVER command.
Workaround : None
Note 342999.1 First Resync on Production Host After Running RMAN on
Backup Server Takes a Long Time
Version affected: 8i
Version fixed : No fix available.
Affects : RMAN executable, Target database, Catalog Schema
Diagnosis : See this note for details on how to confirm if you have hit this problem.
Workaround : Recreate the controlfile.
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Version affected: 9.2
Version fixed : 9204, 10g
Affects : Target database
Diagnosis : READ_ONLY devices, like CD-rom, are used.
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Version affected: 8.1
Version fixed : 9201, 10g
Affects : Catalog Schema
Diagnosis : RMAN full resync (either implicit resync, or explicit
resync requested by RESYNC CATALOG command) takes a
very long time when there are a lot of tablespaces
(probably 500 or more) in the database)
Unpublished Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Version affected: 8.1, 9.0.1
Version fixed : 9201
Affects : Catalog Schema
Workaround : create index brl_i_dts on brl(dbinc_key, thread#, sequence#)
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Version affected: 8.1 9.0.1
Version fixed : 8174 9014 9201
Affects : Target database
Diagnosis : Many entries in X$KSFQP
Workaround : SQL> alter system set events 'immediate trace name ksfqp_limit level 1';
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Version affected: 8.1 9.0.1
Version fixed : 9202 10g
Affects : Target database
Diagnosis : Contention of ksfqpl latch
Workaround : SQL> alter system set events 'immediate trace name ksfqp_limit level 1';
or in the init.ora :
event = "logon trace name ksfqp_limit level 1"
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Version affected: 8.1 9.0.1 9.2
Version fixed : 9203 10g
Affects : Catalog Schema
Workaround : dbms_utility.analyze_schema() on RMAN- catalog schema.
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Version affected: 8.1 9.0.1 9.2
Version fixed : 9203 10g
Affects : Target database
Wor karound : Insert a tape in the tape drive, or
Set init.ora param BACKUP_TAPE_IO_SLAVES to FALSE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Version affected: 8.1 9i
Version fixed : 10g
Affects : Catalog Schema
Diagnosis : Many rows in tables BS and BP
Workaround : Create 2 additional indexes in the RMAN-catalog schema :
SQL> create index bs_i_1 on bs (db_key, bs_recid, bs_stamp);
SQL> create index db_i_2 on bp (db_key, bp_recid, bp_stamp);
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Version affected: 8.1
Version fixed : 9i
Affects : Catalog Schema
Diagnosis : Many rows in tables BDF
Workaround : Create 1 additional index in the RMAN-catalog schema :
SQL> create index bdf_i_bs_key on bdf(bs_key);
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Version affected: 9i
Version fixed : 10G Release2
Affects : RMAN Executable
Diagnosis :
1) SKIP READ ONLY or SKIP OFFLINE option takes too much
when NOT BACKED UP option is used in catalog mode.
2) SKIP READ ONLY or SKIP OFFLINE option takes too much when
BACKUP OPTIMIZATION is turned ON in catalog mode
Workaround : Run the backup in NOCATALOG mode and
perform a RESYNC CATALOG afterwards
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Version affected: 9i, 10G
Version fixed : 9207, 10105, 10G Release2
Affects : Catalog Schema
Diagnosis :
Multiple resyncs are done in parallel for same/different databases.
There is no error signaled, but the other backup job will not continue
until the resync in first job is completed.
Workaround : Create indexes in recovery catalog:
For 9i and 10G:
SQL> CREATE INDEX dfatt_i_sck on dfatt(start_ckp_key);
CREATE INDEX dfatt_i_eck on dfatt(end_ckp_key);
CREATE INDEX tsatt_i_sck on tsatt(start_ckp_key);
CREATE INDEX tsatt_i_eck on tsatt(end_ckp_key);
CREATE INDEX ckp_i_dbinc on ckp(dbinc_key);
For 10G Release 1 and 2:
SQL> CREATE INDEX rsr_i_dbinc on rsr(dbinc_key);
For 10G Release 2:
SQL> CREATE INDEX rout_i_db on rout(db_key);
CREATE INDEX rout_i_rsr on rout(rsr_key);
Generic Performance Issues which effect RMAN :
----------------------------------------------
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles
Version affected: 9
Version fixed : 9203
Affects : Target database
Diagnosis : Many datafiles
References
BUG:7595777 - RMAN TAKES A LONG TIME BEFORE STARTING THE BACKUP
BUG:9363515 - RMAN RESYNC HANGS AND NEVER COMPLETE
NOTE:1340991.1 - RMAN RESYNC CATALOG is generating High amounts of Redo
NOTE:1354528.1 - RMAN commands or SELECT on V$DATAFILE very SLOW / HANG / Poor Performance for database with many datafiles
NOTE:378234.1 - Rman Catalog Resync Operation is Very slow at 10G
NOTE:413098.1 - Extremely Poor RMAN Backup Performance to NFS After Upgrade to 10.2
BUG:10104567 - REMOVED RULE HINT WITHIN THE VIEW DEFINITION IN GV$DATAFILE CAUSED PERFORMANC
BUG:11872103 - RMAN RESYNC CATALOG VERY SLOW
BUG:12585543 - RMAN BACKUP PERFORMANCE DROPS SIGNIFICANTLY AFTER UPGRADE TO 11.2
BUG:1407413 - NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF.
BUG:1551773 - RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
BUG:1887868 - TARGET RESYNC TAKES EXCESSIVE TIME IF USING MANY DATAFILES (E.G., 5000)
BUG:2484250 - RMAN CUMULATIVE BACKUP HANGS ON LATCH FREE WAIT EVENT AFTER UPGRADE TO 8.1.7.4
BUG:2610596 - HUGE CPU RMAN-SERVERPROCESS WHEN TAPE NOT MOUNTED AND BACKUP_TAPE_IO_SLAVE=TRUE
BUG:2710321 - X$KCCFN (HENCE V$DATAFILE) MUCH SLOWER IN 9I THAN 8I FOR MANY DATAFILES
BUG:2803823 - RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
BUG:2876717 - RMAN BACKUP STARTS SLOW IF READ-ONLY DEVICE IS USED
BUG:2968394 - RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
NOTE:605557.1 - RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
NOTE:209780.1 - RMAN Hangs During Restore if CKP Table is Excessively Large
NOTE:228838.1 - RMAN Diagnostic Checklist
NOTE:248361.1 - RMAN Command 'Resync Catalog' is Very Slow
NOTE:339964.1 - RMAN 8.1.7 Crosscheck Command Is Very Slow
NOTE:342999.1 - First Resync on Production Host After Running RMAN on Backup Server Takes a Long Time
NOTE:6457435.8 - Bug 6457435 - RMAN resync takes long time when v$deleted_object contains too many rows.
NOTE:820856.1 - High Waits On "Control File Sequential Read" During Rman Backup
NOTE:433335.1 - Poor RMAN SBT Restore Performance: RMAN does not parallelise despite allocating >1 Channel
NOTE:463227.1 - Oracle10g RMAN Recovery Catalog Known Performance Issues
BUG:3966722 - CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
BUG:4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
BUG:4110489 - RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
BUG:4548861 - RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
BUG:5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
BUG:5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
BUG:5247609 - RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
BUG:5620640 - LONG WAIT ON OPEN DB RESETLOGS WHEN USING FLASHBACK DB GUARANTEED RECOVERY POINT
BUG:7206538 - PERFORMANCE ISSUE WITH RMAN BACKUP
BUG:6051693 - SLOW RESYNC AND SHOW ALL IS TAKING AROUND 2 MINUTES
BUG:6457435 - THE IMPLICIT RESYNC PRODUCES A HIGH NUMBER OF I/OS ON THE LOCAL CONTROLFILE.
BUG:6476935 - SLOW RESYNC DUE TO MISSING INDEX ON RSR TABLE
BUG:7023147 - RMAN HANGING ON V$LOG_HISTORY
BUG:7173341 - CLEANUPRSR IS TAKING VERY LONG TIME CAUSING SLOW RESYNC
___________________________________________________________________________________
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
Author: laven54 (lurou)
Email: laven54@163.com
Blog: http://blog.csdn.net/laven54