Question about partitioning and ORA-01460 in parallel server in 11gr2
Date: Wed, 24 Apr 2013 19:45:36 +0000
Message-ID: <494C8EA00972B745A690E110F510E9BF05049E37_at_de-cm-mbx01>
I greet everyone - I'm a new French subscriber - One of your members suggested me to post you the following issue : After a migration from 10g to 11g, we use a new hash/range partitioning for a big table for load-balancing (with another table with BLOBs in separate tablespaces with reference partitioning to the first one) and I chose a number of 6 for the hash part of the partitioning key and 4 for the range part to limit the number of partitions to 6x4, for maintenance consideration, and regarding to the CPU number. It caused obviously different partitions sizes, but I did not see any error in copying my existent records, all copied in the new partitioned table, neither using the application. We use default parallelism for both tables.
We now see unpredictable errors ORA-12801 and ORA-01460 ("parallel query" and "unimplemented data conversion") after several weeks in production, not having seen it before with the "black box" Software package we use, because it did not detect the error (Oracle no more maintain Lodestar).
I am currently looking to Bug 13099577 : ORA-1460 WHEN PARALLEL QUERY SERVERS ARE USED - which looks like being the same problem but does not completely corresponds because we use 11.2.0.2 and documentation describes upgrade in 11.2.0.3, and describes a complete loss of service, which is not our case. I did not find corresponding trc files with this error.
The error "disappear" when we turn off parallelism on the main table, or restart the DB, but does not "reappear" when we turn it on again. We did not identify what causes the error appearance. I hesitate to simply turn of parallelism for performance risks. But it may be the simpliest way to avoid the error.
The other tail (lead ?) should be NLS_NCHAR_CHARACTER set to UTF8, which was not the case before migration, but our application schemas do not use NCHAR or NCLOB columns.
I see now in Oracle documentation ("Partitioning in Data Warehouse") that the number of partitions "should be" a multiple of 2 (and not must be) what I had not seen in other documentations. No oracle error occurred while creating the tables and copying the data.
Do you think the error and the "non multiple of 2" number of 6 partitions and these messages could be related ? Does the number of partitions "should be" or "must be" a multiple of 2 ?
Does anyone have an experience of this ORA-12801 / ORA-01460 error and 13099577 patch ?
Thank you for your help, if you can. It is a real urgent production bug and I am very worried about it because we did not see it earlier, and have to fix it quickly.
I am in France and it is late at this time, but will have a look at my mail this evening and tomorrow morning.
Best regards
Dominique Labbens
oerr ora 12801
12801, 00000, "error signaled in parallel query server %s"
// *Cause: A parallel query server reached an exception condition.
// *Action: Check the following error message for the cause, and consult
// your error manual for the appropriate action.
and
ORA-01460 unimplemented or unreasonable conversion requested Cause: The requested format conversion is not supported. Action: Remove the requested conversion from the SQL statement. Check the syntax for the TO_CHAR, TO_DATE, and TO_NUMBER functions to see which conversions are supported.
Dominique LABBENS - Architecte
Capgemini Industrie & Distribution / EUC / IDS Tel.: +33 1 78 66 60 24 – Mob.: + 33 6 22 66 85 79 www.capgemini.com<http://www.capgemini.com/>
People matter, results count.
This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 24 2013 - 21:45:36 CEST