OS: AIX 5300-06
Oracle: 9.2.0.8
I hit a strange problem. I had a problem with a performance of RMAN backup and according to Oracle Metalink note
Known RMAN Performance Problems [ID 247611.1]
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'; |
I executed on RMAN session
RMAN> sql "alter system set events ''immediate trace name ksfqp_limit level 1''"; |
sql statement: alter system set events ''immediate trace name ksfqp_limit level 1'' |
After that I wanted to check a backup performance using the views v$backup_async_io and
v$backup_sync_io but the datas weren`t up to date.
SQL> select max(open_time),max(close_time) from v$backup_async_io; MAX(OPEN_TIME) MAX(CLOSE_TIME) ------------------- ------------------- 2011-05-31 10:26:39 2011-05-31 10:26:39
SQL> select max(open_time),max(close_time) from v$backup_sync_io; MAX(OPEN_TIME) MAX(CLOSE_TIME) ------------------- ------------------- 2011-05-31 10:26:39 2011-05-31 10:26:39 |
|
|
Where are the data after 2011-05-31 10:26:39 ?
Let`s check an alert.log
... Tue May 31 10:26:38 2011 Starting control autobackup Tue May 31 10:28:01 2011 Control autobackup written to SBT_TAPE device comment 'API Version 2.0,MMS Version 5.4.1.0', media '2' handle 'c-1601303025-20110531-26' Tue May 31 10:31:19 2011 OS Pid: 2113700 executed alter system set events 'immediate trace name ksfqp_limit level 1' Tue May 31 10:41:01 2011 Thread 1 advanced to log sequence 59271 Current log# 3 seq# 59271 mem# 0: /r655/oradata/XXX/redo03.log Current log# 3 seq# 59271 mem# 1: /r656/oradata/XXX/redo03.log Tue May 31 10:41:01 2011 ARCH: Evaluating archive log 2 thread 1 sequence 59270 ARCH: Beginning to archive log 2 thread 1 sequence 59270 Creating archive destination LOG_ARCHIVE_DEST_1: '/a655/app/oracle/admin/XXX/arch/1_59270.arch' Tue May 31 10:41:01 2011 ARC0: Evaluating archive log 2 thread 1 sequence 59270 ARC0: Unable to archive log 2 thread 1 sequence 59270 Log actively being archived by another process Tue May 31 10:41:01 2011 ARCH: Completed archiving log 2 thread 1 sequence 59270 Tue May 31 10:41:58 2011 Starting control autobackup Tue May 31 10:43:41 2011 ... |
Indeed at 10:31:19 I set an event and it was stamped in the alert log.
On the metalink I found the note: Data missing from V$backup_async_io [ID 277605.1]
Symptoms
After taking Rman backup you do not see any data populated in v$backup_aysnc_io.
Cause
You have set this event in init.ora or session level
alter system set events 'immediate trace name ksfqp_limit level 1
Fix
This Event limits the maximum number of rows in v$backup_[a]sync_io table setting it to 1,
maximum number of rows is 0; by setting it to 2, maximum number of rows is 2 by setting it to 3,
maximum number of rows is 4 (ie 2 power of (3-1)) and so on. |
I fix the problem by executing
SQL> alter system set events 'immediate trace name ksfqp_limit level 20';
|
|
|
