ORA-1555/ORA-22924 error while export LOB objects

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Getting ORA-1555/ORA-22924 error while export LOB objects.




LOB objects are located in MSSM tablespace (Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace)

Modify the PCTVERSON of LOB segment to 50


Solution: alter table lobpctversion modify lob(lobLoc) (pctversion 50);




SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);

SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('LOBPCTVERSION');

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800

SQL> alter table lobpctversion modify lob(lobLoc) (pctversion 50);

Table altered.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to modify a the PCTVERSION for a LOB segment?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: alter table ecs.CS_B_DOCUMENT MODIFY LOB SYS_LOB0000356268C00007$$ (PCTVERSION 50);

ERROR at line 1:
ORA-00902: invalid datatype


Main Issue:


Getting ORA-1555/ORA-22924 error while export LOB objects.

LOB objects are located in MSSM tablespace (Note 800386.1 ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace)

Have advised customer to modify the PCTVERSON of LOB segment to 50 but it failing with the following error:


alter table ecs.CS_B_DOCUMENT MODIFY LOB SYS_LOB0000356268C00007$$ (PCTVERSION 50);

ERROR at line 1:
ORA-00902: invalid datatype


Solution by Example: alter table lobpctversion modify lob(lobLoc) (pctversion 50);




SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);

SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('LOBPCTVERSION');

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800

SQL> alter table lobpctversion modify lob(lobLoc) (pctversion 50);

Table altered.

Get Oracle Certifications for all Exams
Free Online Exams.com

Expdp ends with ORA-31693,ORA-02354,ORA-015555,ORA-22924 with lobobjects

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Expdp ends with ORA-31693,ORA-02354,ORA-015555,ORA-22924


### Export Data Pump parameters ###
exp using DP,schema level
expdp bkp/*** dumpfile=work_dir:expdp_ecs.dmp logfile=DATA_PUMP_DIR:expdp_ecs.log schemas=ecs


While exporting using DP:
ORA-31693: Table data object "ECS"."CS_B_DOCUMENT":"OUTBOXDATE_MAXVAL" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old


Problem does not show while using normal exp , however during imp it gave the following errors:


Here what I got after I imported the healthy exp dmp file !

. . importing partition "CS_B_DOCUMENT":"OUTBOXDATE_2013" 0 rows imported
. . importing partition "CS_B_DOCUMENT":"OUTBOXDATE_MAXVAL"
IMP-00064: Definition of LOB was truncated by export
IMP-00028: partial import of previous table rolled back: 351622 rows rolled back
. . importing table "CS_B_DOCUMENT_FOLLOWUP" 0 rows imported




Explanation:


ORA-1555/ORA-22924 may occur when accessing LOB columns, even when the LOB RETENTION seems to be sufficient.
This may occur when LOB column resides in a MSSM (Manual Segment Space Management) tablespace.

It is also confirmed that the LOB column resides in a MSSM.

Please note that LOB RETENTION parameter has no effect if the LOB resides in a tablespace using MANUAL space management (MSSM). In order for LOB RETENTION to honour the UNDO_RETENTION period Automatic Segment Space Managemetn (ASSM) should be used.


If you want to use LOB retention for LOB columns to avoid ORA-1555, you must use ASSM (Automatic Segment Space Management) tablespace.


You cannot change the segment space management mode of a tablespace. If your LOB column is store on MSSM tablespace and you would like to use the ASSM option, you'd have to create a new tablespace using 'segment space management auto' followed by moving the objects to the new tablespace which was created to use Automatic Segment Space Management.

create tablespace assm_ts datafile
...
autoextend on
extent management local
segment space management auto; <==

alter table <table_name> move tablespace <ASSM_tablespace_name>;


If you can't move to ASSM and you need to store your LOB data on MSSM tablespace, you'd have to use PCTVERSION instead of RETENTION.

-- Example: Setting PCTVERSION to 20 percent
SQL> alter table <table_name> modify lob (<LOB_column>) (pctversion 20);


Please also refer the following note:

Note 800386.1
ORA-1555 - UNDO_RETENTION is silently ignored if the LOB resides in a MSSM tablespace

Solution:


From the above query identify the LOB name and also the PCT version.
If the PCT version is less than 50, then advise you to increase the PCTversion to 50% for the table CS_B_DOCUMENT_FOLLOWUP as follows:


SQL>alter table CS_B_DOCUMENT_FOLLOWUP MODIFY LOB <LOB NAME> ( PCTVERSION 50);

Once done, take a fresh export using exp. and re-attempt the import.


if the output for the file contains row data, then the LOB is corrupted.


Please refer note 787004.1, where clear steps is provided on how to identify the corrupt blocks and work around the issue.

set serverout on
exec dbms_output.enable(100000);
declare
pag number;
len number;
c varchar2(10);
charpp number := 8132/2;

begin
for r in (select rowid rid, dbms_lob.getlength (documentcontent) len
from ecs.CS_B_DOCUMENT) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (documentcontent, 1, 1+ (page * charpp))
into c
from ecs.CS_B_DOCUMENT
where rowid = r.rid;

exception
when others then
dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
dbms_output.put_line (sqlerrm);
end;
end loop;
end if;
end loop;
end;
/


there are corrupted LOB rows withing the segment. Please perform the action plan as per note 874562.1 to confirm the same.


Reference:
Note ID 452341.1 for detecting corrupted clob's


Note 874562.1
EXP-00056,ORA-24801 During Export

Get Oracle Certifications for all Exams
Free Online Exams.com

Arabic characters are shown in reverse with HTML output

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: HTML Reports shows Arabic in reverse order


Solution:


In file /8.0.6/guicommon6/tk60/admin/Tk2Motif_UTF8.rgbEnsure the the profile option Viewer:Text is set to Browser

From the System Administrator responsibility,

navigate to Install > Viewer Options
ADD new record
File Formst: Text
Mime Type: apps/bidi
Description: Pasta Viewer for bidi

Set the profile option Viewer:Application for Text
Value: Pasta viewer for bidi

In your browser choose enocoding Unicode.

please also review the following:

notes 839520.1 and 816879.1


change the following line
Tk2Motif*fontMapCs: iso8859-1=UTF8 ( or whatever it is set to )
to be
Tk2Motif*fontMapCs: iso8859-1=AR8MSWIN1256


Make sure that the prt files in $FND_TOP\reports contains the following:

code "bold on" esc "[1m"
code "bold off" esc "[0m"
code "underline on" esc "[3m"
code "underline off" esc "[2m"

nls locale "arabic"
nls datastorageorder "logical"
nls contextuallayout "no"
nls contextualshaping "yes"


If you find the following Lines in the .prt files then remove them then add them to the Environment file :

REPORTS60_PRINTER_CODE_BEFORE=&5
export REPORTS60_PRINTER_CODE_BEFORE
REPORTS60_PRINTER_CODE_AFTER=&4
export REPORTS60_PRINTER_CODE_AFTER

In the enviroment file that you source before starting the concurrent manager :

set both the IX_Printing and
IX_Rendering to the full path of Pasta.cfg file.

Ensure you are using the Pasta Universal printer type and not the Pasta Postscript type.






Then retest for the issue.


Reference:


Note 552977.1 for reverse Arabic
notes 839520.1 and 816879.1

Get Oracle Certifications for all Exams
Free Online Exams.com

Audit trail tables does not show historical changes

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: Audit trail tables does not show historical changes


running AuditTrail Update Tables concurrent request finsihed with error
TOLERANCE_ID
Fatal error in fdasql, quitting...
Fatal error in fdacv, quitting...


Explanation:


he FNDATUPD concurrent request fails to complete successfully when auditing is enabled on "AP_SYSTEM_PARAMETERS_ALL" because this table has too many columns and by default auditing is enabled on every single column of this table. When the concurrent request runs, it tries to create a view for this table and hits a database limit on the number of columns of a table that can be audited (128 columns).

Therefore, if you really want to audit the "AP_SYSTEM_PARAMETERS_ALL" table then you will need to select fewer columns of this table to be able to successfully run the concurrent request.

If you do not want to audit this table, then do the following to remove auditing on this table and resolve the error:

1. Navigate to the audit group tables, and query the "AP_SYSTEM_PARAMETERS_ALL" table.

2. Set the Group state to "Disable - Purge Table" for this table.
Note: This option drops the auditing triggers and views and deletes all data from the shadow table.

3. Run the "AuditTrail Update Tables" concurrent program again.It should now run successfully.


Solution:


As per Note id 60828.1 point II,d) Set the profile option "AuditTrail:Activate" to yes to begin auditing data.

Now _AC tables is showing historical data per invoice id


AP_SYSTEM_PARAMETERS_ALL table is still in the audit, please review Note.274755.1 for the steps to take to clean up the audit trail.


Follow the steps in the referenced note to clean up the audit trail, recreate audit trail groups and respecify audit trail information via audit trail forms in apps and then run the FNDATUPD progr


As you would have seen when you ran the FNDATUPD program there are a number of views created, there are 2 types _AC and _AV each giving a different view of the data. To build up a change history of the data you need to join the data from a number of views and not just query the one single view.

Each view allows slightly different access to data, one allows you to reconstruct the value for a row at a given time (_AC), while the other provides simple access to when the value was changed (_AV). You will need to join views to get a change history of the data, this is explained in the documentation.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to run Oracle RDA to gather streams information

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

As suggested by note 273674.1

You can run it with
./rda.sh -vCRP STC options to collect the streams monitor and health check.

Get Oracle Certifications for all Exams
Free Online Exams.com

How to expose Oracle Sourcing home page to external vendors?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description: How to expose Oracle Sourcing home page to external vendors?

Solution:

Note 308271.1 Enable Web Access By External Supplier Users to Oracle iSupplier Portal and Oracle Sourcing
Note 395530.1 How To Diagnose An Issue Where External Supplier Users Cannot Access Sourcing Pages
Note.344749.1 How To Setup Oracle Sourcing Access Through Reverse Proxy / URL Firewall
Note 215097.1 Pricing in Negotiations - Auction, Offer, RFQ in Oracle Sourcing and Oracle Exchange
Note 731079.1 Sourcing Notifications - Please Click Here To Respond - Incorrect URL (Using Reverse Proxy)

Get Oracle Certifications for all Exams
Free Online Exams.com