Full Load Infopackage Error

[Former Member]

Friends/Experts,

I am fighting a lot with the below issue more then 3 Weeks , Kindly please help me out.

Data Source : 0CO_OM_CCA_9

While i try for data selection 001.2015 to 006.2015 ----> Full Load Info-package successful.

When i try for data selection 007.2015 ---------> Full Load Info package Throws an error.

I replicated the Data Source in BW and Activated all, then too i got the same error.

Please Refer the Screen shot:

Until 35,400 Data fetched after that same issue.

[Former Member]

Did you get answer to your issue ?

I am also facing same issue in one of my custom extractor, please share the resolution if you found any .

[Former Member]

Friends,

Still no improvement in my above Issue..

Kindly need all your support guys.

Pratyaksh Jeet

John Hawk

M Tibollo

[Former Member]

Hello Rgi sp,

Please share the CMOD code once for this Extractor.

Thanks

Pratyaksh Jeet

[Former Member]

as requested before already, paste the code of your user exit....else we are just guessing....

M.

[john_hawk]

Have you searched the SAP Notes?

There are multiple SAP Notes for 0CO_OM_CCA_9.

SAP Note 2161776 - BW-BCT: DataSource 0CO_OM_CCA_9 extracts forbidden characters in text field of data record caught my eye.

1701213 - Missing records in extractor 0CO_OM_CCA_9

1999420 - BI Content for CO totals extraction in delta mode

Are you using BRConnect?

See 2202967 - Collecting statistics fail with ORA-01555: snapshot too old

Other ORA-01555 errors are documented in

1611954 - CORR: Dump during migration of accounts (ORA-01555)

2315880 - Preventing ORA-01555 and packaged reading during DELTA extraction of account-based CO-PA

I will quote from

185822 - ora-1555 - cause and action

Symptom

Possibilities for a ora-1555 to happen , how to find out which event
triggered the ora-1555

Other Terms

rollback segment, snapshot too old
ORA-01555

Reason and Prerequisites

This note was created to easier analyze why an ORA-01555 happend. Even though it was origninally created having the classic rollback segment configuration in mind, all three cases described in here do also apply to the Automatic Undo Management (AUM) setup introduced with oracle 9. A detailed description on AUM can be found in note 600141.

The use of AUM has greatly reduces the occurances of this error. We therefore strongly encourage you to, if you are still using a traditional rollback segment layout, switch to AUM in order to help eliminating this problem.

Despite of using AUM, ora-1555 errors can still be happening. Unless otherwise mentioned, whatever stated about rollback segments, also applys to undo segments; when talking about the rollback tablepsace, it also applys to the undo tablespace.

Solution

From a database point of view, there are 3 main reasons why a ora-1555 could occur.

1. delayed block cleanout

              The database writer, that writes changed blocks from the SGA to the datafiles, works on the dirty list within the SGA. This means that not all blocks necessarily are commited at the time they are written out. If the commit happens later on, oracle does not want to perform a complete write just to set the commit flag for this block. So the rollback information is marked for reuse, but the block itself remains unchanged.

              Next time the block is accessed, it is detected that the block is not committed. The shadow process looks for rollback information for the latest change for this block. It looks for it in the rollback segment where the rollback was originally created. If the SCN in the rollback slot it is looking at is SMALLER than the SCN at the beginning of your select, the select will succeed. But if another totally unrelated transaction is using this particular rollback segment right now, and therefore the SCN is BIGGER than the starting SCN of your transaction, under some circumstances an ora-1555 will be reported. The block, however, will be committed and the commit flag will be set automatically.

              SOLUTION:   do a select * from all the tables that are read at this time

• --> sqlplus sap<SID>/<passwd>

• SQL> select * from <tablename>;

              if here no error message is returned, everything should be fine now- the cleanout has happened during this select. It might be possible that also during this select the ora-1555 is reported. Continuous re-execution of the select, as long as ora-1555 errors occur will cleanout the problem. The commit flag will get set when the particular block is accessed, but as the error is returned, all further blocks are not read. So each time, you run into the problem, only one block is updated and only through continuously accessing the blocks of this table ALL blocks get cleaned out. Should this be a partitioned table, and the data that you are reading is coming from a particular partition only, it is sufficient to read the data from that particular partition. Respectively if you know the select-statement that is failing (including the values of the bind variables), you can just execute that select and perform the cleanout with it - if you do that please check your code whether or not the same select follows multiple times just with different values. It is advised to read the complete dataset that is touched by the report that fails with the ora-1555 prior to re-executing the report.              There are also a few things that you can do to minimize the probability of such a block being flushed out without a commit (and with that avoid a delayed block cleanout of happening at a later stage).

• The main trigger for blocks being flushed out is checkpoints. Make sure the checkpointing frequency is not too small. Usually the size of the redologfiles should be set up so that log switches happen about every 4 minutes during peak load time. If logswitches happen more frequently, consider increasing the size of the redologfiles.

• Avoid incremental checkpointing. Incremental checkpointing is forced by the following parameters

• log_checkpoint_timeout -->set it to 0 to disable it

• log_checkpoint_interval-->set it to >= 3000000

• fast_start_io_target   -->set it to 0 or the number of db_block_buffers                           (db_cache_size/8192)

• fast_start_mttr_target -->set it to 0

• try to find out what programm  is performing the updates to the table (e.g. data load, long running batch job). Most likely it is performing inserts/updates to a huge number of records at a time. check whether it is possible to increase the commit frequency.

• See whether re-reads of the records that just have been processed by the batch job right after processing is finished can be implemented. This will allow you to just do the select on the specific records that where just updated and will cleanup any missing commit flags.

              It is equally likely for this problem to occur with the old rollback segment setup than with the new AUM setup.

2. rollback segment or rollback/undo tablespace is too small.

              Is the rollback segment too small (only rollback segment setup, this does not apply to undo segments), it may also result in an ora-01555 error. You can check the current maximum size of each rollback segment as follows: sqlplus '/ as sydba'
SQL> select hwmsize from v$rollstat;              This will return the maximum size all your rollback segments had been growing to. please make sure, that

a) maxextents were not reached for that  rollback segment SQL> select initial_extent,next_extent,

        max_extents from dba_rollback_segs;

                       hwmsize should be smaller than initial_extent+(next_extent*max_extents).                       Otherwise the paramters have to be enlarged.

b) Extents could still be allocated within the tablespace:

• hwmsize for the biggest rollback from segment + optimal_size*number of rollback segments  should not be more than 80% of the size of the rollback tablespace.

• Otherwise add a datafile to the tablespace

3. a running select statement needs rollback information of an update ,that had been taken place between the start of the select statement and the fetch of the data. As the update is finished, the rollback segment was freed and had been overwritten by another transaction.

                        SOLUTION: Add more rollback segments so that the probability of overwriting specific statements decreases. Also orientate the optimal size of your rollback segment on the hwmsize of the majority of the rollback segments.

                       To get an estimate on how long rollback information on average will remain on your system you can use the following formula: SQL> SELECT
       trunc(24 * (sysdate - i.startup_time) / v.cycles)  "IN HOURS",
       trunc(1440 * (sysdate - i.startup_time) / v.cycles)  "IN MINUTES"
     FROM  sys.v_$instance  i,
     (SELECT
        max(
           (r.writes + 24 * r.gets) /   -- bytes used /
             nvl(least(r.optsize, r.rssize), r.rssize) * -- segment size
             (r.extents - 1) / r.extents                 -- minus 1 ext.
            ) cycles
       FROM  sys.v_$rollstat  r
      WHERE   r.status = 'ONLINE'  )v ;

                       Since the result is just an AVERAGE, it does not mean that there cannot be any overwrites before that time. Should your job run longer than the result of this query (best would be if the result is at least 2x the runtime of your job) then you defenitely need to increase the number of rollback segments to extend the time until the rollback information is overwritten.

                       Although oracle usually evenly distributes transactions evenly in between the segments, and therefore the time before an overwrite of rollback information is happening should be consistant in between the rollback segments, there unfortunately is no guarantee for that.

                       Beginning with oracle 9.2 , oracle introduced a new concept to store undo information - rather than using rollback segments, the undo space is administrated automatically by oracle. Switching to this new concept might help for the occurances of this 3rd possibility of ora-1555 errors. Details on automatic undo management and how it can be set up can be found in note 600141. Since it is possible to set a  retention time for rollback, oracle gives us a bit more of a guarantee that the rollback information will not be overwritten within that specified time. However, if your undo tablespace is not big enough to hold transactions for that long of a period, transactions will still be overwritten within less than the specified time. Make sure thatas well as having a retention time specified that exceeds the runtime of the job you are getting the ora-1555's for, your undo tablespace is capable to store the amount of undo that is written within that time.

                       Please note that this will not automatically eliminate your ora-1555 errors. It still requires some testing and tuning, especially with the setup of these two variables, the value for retention time and the size of the undo tablespace.

                       The 3rd possibility is the most likely. Please perform checks 1)+2) and if the result looks like you did not run into one of these problems,please add more rollback segments to minimize the likelihood that possibility 3 occurs.

If you have reviewed all the Notes, attempted a fix,, and are still unsuccessful -consider opening an SAP Incident.

This problem is beyond your skill set. You need to involve your Basis team and SAP to correct the problem.

Please don't forget to record your final solution and to mark the question as answered.

Good luck,

John Hawk