11g Active Data Guard 报表访问报 ora-01555

Oracle 11.2.0.2.0  ,  Linux AS 5.3  64bit  

11g production 系统是三节点RAC  


开启为read only 模式，供web report 查询，刚开始还比较正常，到晚上22：30多分的时候发现web report
有多个报表访问不了，  一直报错到早上， 今天早上查询发现ORA-01555 的很多错误 。   

开始怀疑是11g production 系统在22:00 的时候做系统统计信息收集的时候，过多的系统变更导致undo不够用。
但是在Oracle 10g 下原来使用Logical Standby的时候也没有发生过这种情况 (当然可能在统计信息收集上原理
有不同 )  。  

Wed Jul 06 22:20:17 2011
Archived Log entry 1683 added for thread 2 sequence 1375 ID 0x4fa453a7 dest 1:
Wed Jul 06 22:28:13 2011
RFS[1]: Selected log 222 for thread 2 sequence 1377 dbid 1336132007 branch 754063783
Wed Jul 06 22:28:13 2011
Archived Log entry 1684 added for thread 2 sequence 1376 ID 0x4fa453a7 dest 1:
Wed Jul 06 22:28:13 2011
Media Recovery Waiting for thread 2 sequence 1377 (in transit)
Recovery of Online Redo Log: Thread 2 Group 222 Seq 1377 Reading mem 0
  Mem# 0: +DATA/wsjdell/dgredo222a.log
  Mem# 1: +INDX/wsjdell/dgredo222b.log
Wed Jul 06 22:29:34 2011
ORA-01555 caused by SQL statement below (SQL ID: bwns6y4xvbzr6, Query Duration=1 sec, SCN: 0x0002.0c4735c7):  
SELECT COUNT(0) AS TOTAL FROM ( SELECT DFMS.FN_ISCFI_FORWEB(a.serial_number) as ISCFI,a.plant_code,a.serial_number,a.product_line, b.part_no, b.part_desc,nvl(b.eee_code,'') as eee_code,b.production_type, a.work_order, nvl(a.original_wo_no,'') as original_wo_no, b.batch_no,nvl(a.wh_id,'') as wh_id,nvl(a.area_id,'') as area_id, a.pallet_no, (CASE WHEN a.work_group = '0' THEN 'Job Start' ELSE a.work_group END) AS work_group_show, a.ref_next_station AS netx_station, (SELECT DISTINCT step_sequence FROM DFMS.wip_s_route_detail j WHERE j.route_code = b.route_code AND j.plant_code in ('PNK1')  AND j.group_name = a.work_group AND j.state_flag = '0' AND ROWNUM = 1) AS step_sequence,b.route_code, TO_CHAR(a.in_station_time,'yyyy/mm/dd hh24:mi:ss') AS lastscantime,ROUND(( SYSDATE - a.in_station_time)* 24) AS nowtime, TO_CHAR(a.in_line_time,'yyyy/mm/dd hh24:mi:ss') AS in_line_time,T
Wed Jul 06 22:30:03 2011
ORA-01555 caused by SQL statement below (SQL ID: 6n4x3zgv3vx7a, Query Duration=0 sec, SCN: 0x0002.0c4735c7):
Wed Jul 06 22:30:03 2011
ORA-01555 caused by SQL statement below (SQL ID: 6n4x3zgv3vx7a, Query Duration=0 sec, SCN: 0x0002.0c4735c7):
select * from (SELECT ROWNUM as ID,TEMP.* FROM ( SELECT DFMS.FN_ISCFI_FORWEB(a.serial_number) as ISCFI,a.plant_code,a.serial_number,a.product_line, b.part_no,b.mc_code, b.part_desc,nvl(b.eee_code,'') as eee_code,b.production_type, a.work_order, (SELECT DISTINCT SOTYPE FROM DFMS.EDIBTO850_MAIN WHERE BEG03 = B.CPO_NO) AS PO_TYPE, nvl(a.original_wo_no,'') as original_wo_no, b.batch_no,nvl(a.wh_id,'') as wh_id,nvl(a.area_id,'') as area_id, a.pallet_no, (CASE WHEN a.work_group = '0' THEN 'Job Start' ELSE a.work_group END) AS work_group_show, a.ref_next_station AS netx_station, (SELECT DISTINCT step_sequence FROM DFMS.wip_s_route_detail j WHERE j.route_code = b.route_code AND j.plant_code in ('PNK1')  AND j.group_name = a.work_group AND j.state_flag = '0' AND ROWNUM = 1) AS step_sequence,b.route_code,(SELECT region FROM DFMS.wip_s_mcid_master WHERE mcid IN(NVL((SELECT MAX (mcid)KEEP (DENSE_RANK LAST ORDER BY lteditdt)FROM DFMS.label
select * from (SELECT ROWNUM as ID,TEMP.* FROM ( SELECT DFMS.FN_ISCFI_FORWEB(a.serial_number) as ISCFI,a.plant_code,a.serial_number,a.product_line, b.part_no,b.mc_code, b.part_desc,nvl(b.eee_code,'') as eee_code,b.production_type, a.work_order, (SELECT DISTINCT SOTYPE FROM DFMS.EDIBTO850_MAIN WHERE BEG03 = B.CPO_NO) AS PO_TYPE, nvl(a.original_wo_no,'') as original_wo_no, b.batch_no,nvl(a.wh_id,'') as wh_id,nvl(a.area_id,'') as area_id, a.pallet_no, (CASE WHEN a.work_group = '0' THEN 'Job Start' ELSE a.work_group END) AS work_group_show, a.ref_next_station AS netx_station, (SELECT DISTINCT step_sequence FROM DFMS.wip_s_route_detail j WHERE j.route_code = b.route_code AND j.plant_code in ('PNK1')  AND j.group_name = a.work_group AND j.state_flag = '0' AND ROWNUM = 1) AS step_sequence,b.route_code,(SELECT region FROM DFMS.wip_s_mcid_master WHERE mcid IN(NVL((SELECT MAX (mcid)KEEP (DENSE_RANK LAST ORDER BY lteditdt)FROM DFMS.label
Wed Jul 06 22:30:26 2011
ORA-01555 caused by SQL statement below (SQL ID: 66gtnjuvpr4h8, Query Duration=0 sec, SCN: 0x0002.0c4735c7):
@  
ORA-01555 caused by SQL statement below (SQL ID: 66gtnjuvpr4h8, Query Duration=0 sec, SCN: 0x0002.0c4735c7):
   SELECT A.*,HOLD.HOLD_TYPE,HOLD.HOLD_CODE,TO_CHAR   (HOLD.HOLD_TIME, 'yyyy/mm/dd hh24:mi:ss') as HOLD_TIME,   HOLD.HOLDER,HOLD.HOLD_REMARK,HOLD.HOLD_DESC FROM ( SELECT   sn.PLANT_CODE,WO.BATCH_NO,sn.serial_number, wo.route_code,   sn.work_order, wo.so_no,sn.product_line as line_name,wo.shift,   TO_CHAR (ROWNUM) || '/' || TO_CHAR (wo.target_qty) AS buildno,   TO_CHAR (wo.wo_date, 'yyyy/mm/dd hh24:mi:ss') wo_date,   wo.cust_pn, wo.target_qty, wo.part_no,   wo.part_rev,so.cust_other_ref AS customer_so, so.customer_po,   TO_CHAR (so.so_date, 'yyyy/mm/dd') so_date, so.ship_to,   so.ship_co_name1, so.addr1, so.addr2, so.country_code, so.city,   so.state, so.phone_no, (CASE WHEN sn.work_group = '0' THEN 'No'   ELSE 'Yes' END) AS started, TO_CHAR (sn.in_line_time,   'yyyy/mm/dd hh24:mi:ss') AS startdate, DECODE (sn.error_flag,   '1', 'Yes', 'No') AS repairheld, (SELECT TO_CHAR (repair_date,   'yyyy/mm/dd hh24:mi:ss') FROM wip_d_repair_master WHERE   serial_number = sn.serial_number and plant_code in   ('PNK1') and
Wed Jul 06 22:30:38 2011
ORA-01555 caused by SQL statement below (SQL ID: 66gtnjuvpr4h8, Query Duration=0 sec, SCN: 0x0002.0c4735c7):
   SELECT A.*,HOLD.HOLD_TYPE,HOLD.HOLD_CODE,TO_CHAR   (HOLD.HOLD_TIME, 'yyyy/mm/dd hh24:mi:ss') as HOLD_TIME,   HOLD.HOLDER,HOLD.HOLD_REMARK,HOLD.HOLD_DESC FROM ( SELECT   sn.PLANT_CODE,WO.BATCH_NO,sn.serial_number, wo.route_code,   sn.work_order, wo.so_no,sn.product_line as line_name,wo.shift,   TO_CHAR (ROWNUM) || '/' || TO_CHAR (wo.target_qty) AS buildno,   TO_CHAR (wo.wo_date, 'yyyy/mm/dd hh24:mi:ss') wo_date,   wo.cust_pn, wo.target_qty, wo.part_no,   wo.part_rev,so.cust_other_ref AS customer_so, so.customer_po,   TO_CHAR (so.so_date, 'yyyy/mm/dd') so_date, so.ship_to,   so.ship_co_name1, so.addr1, so.addr2, so.country_code, so.city,   so.state, so.phone_no, (CASE WHEN sn.work_group = '0' THEN 'No'   ELSE 'Yes' END) AS started, TO_CHAR (sn.in_line_time,   'yyyy/mm/dd hh24:mi:ss') AS startdate, DECODE (sn.error_flag,   '1', 'Yes', 'No') AS repairheld, (SELECT TO_CHAR (repair_date,   'yyyy/mm/dd hh24:mi:ss') FROM wip_d_repair_master WHERE   serial_number = sn.serial_number and plant_code in   ('PNK1') and
Wed Jul 06 22:30:43 2011
RFS[5]: Selected log 332 for thread 3 sequence 1352 dbid 1336132007 branch 754063783
Wed Jul 06 22:30:43 2011
Archived Log entry 1685 added for thread 3 sequence 1351 ID 0x4fa453a7 dest 1:
Wed Jul 06 22:30:45 2011
Media Recovery Waiting for thread 3 sequence 1352 (in transit)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-701571/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-701571/