RE: ORA-02294 only on some parallel PK validates

I used a similar process during a migration to 11g. The parallel importing of the tables went fine since impdp will spawn several processes (parallel=##) and each process will import one table each until all tables are imported. If I remember correctly, my problem came when the index creation occurred. In this case the indexes were created with a parallel index creation (one index at a time with multiple processes). This caused me problems because the index wound up being larger than if I had allowed them to be created non-parallel. I can't intelligently explain why that is, but I read something about this from Jonathan Lewis and all I remember is it explained my situation well (better than I can remember at this time). What I wound up doing was creating the indexes separately non-parallel after the data was completely imported.

As Mark said, I think it is better if you can do the whole import non-parallel.

Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham Sent: Wednesday, May 21, 2014 3:06 PM
To: rjoralist3_at_society.servebeer.com; oracle-l_at_freelists.org Subject: RE: ORA-02294 only on some parallel PK validates

It is unclear to me why you do not proceed with the 15 indentified tables non-parallel, if that works repeatably.

With so many objects available to run in separate threads, it is unclear to me that parallel is an advantage for any of this process.

In fact, if you decide to go that way, I would expect a significant reduction in the overall elapsed time. For tables that will fit in the buffer cache, you might want to run a scanner to get nice clean blocks in there just before you unleash the index creates for that table, with a thread for each index create on a cached table.

If overall elapsed time is not an issue, then making the 15 problematic tables non parallel is also not an issue.

Then you can save your SR processing for a post mortem.

Good luck,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Wednesday, May 21, 2014 4:59 PM
To: oracle-l_at_freelists.org
Subject: ORA-02294 only on some parallel PK validates

Hey all,

In 11.2.0.3.8 EE on AIX, we're moving data around for a migration. Here's the high-level of the script:

1. Drop all PKs and indexes in target DB.
2. Datapump import 4600+ tables from a previous dpexp, data only.
3. Run 750+ really ugly UPDATEs, TRUNCATE a few tables, etc.
4. Recreate all indexes/PKs 4a) ALTER SESSION FORCE PARALLEL DDL PARALLEL 8; 4b) ALTER TABLE ttt PARALLEL (DEGREE 8) for "large" tables. 4c) Recreate all 9300+ indexes 4d) Recreate all 4600+ PKs w/NOVALIDATE (FKs not used) 4e) ALTER TABLE...VALIDATE for all 4600+ PKs. 4f) Reset all 9300+ indexes to NOPARALLEL

The parallelism and separate PK validation on step 4 is necessary for performance, and it generally works very well.

However, 15 of the PKs failed on the ALTER...VALIDATE with "ORA-02294: cannot enable (MYUSER.MYTBL_PK) - constraint changed during validation". Recreating them after the script completed w/o parallel worked fine.

MOS has an incorrect message for the ORA-2294 error (feedback on the article didn't seem to work, either). And searching the nets just comes up with "someone else is altering the PK, too". Not in this case. There were no other connections to the DB at the time, verified by listener logs and v$session reports.

I suspect parallel issues, but was hoping someone else ran into this, as I can't afford the time to deal with an SR now.

Anyone?

Rich

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

Confidentiality Notice: This message and any attachments hereto may contain confidential and privileged communications or information and/or attorney client communications or work-product protected by law. The information contained herein is transmitted for the sole use of the intended recipient(s). If you are not the intended recipient or designated agent of the recipient of such information, you are hereby notified that any use, dissemination, copying or retention of this e-mail or the information contained herein is strictly prohibited and may subject you to penalties under federal and/or state law. If you received this e-mail in error, please notify the sender immediately and permanently delete this e-mail.

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 22 2014 - 00:48:44 CEST