RE: how to improve impdp

  • From: "Hsieh, Joan" <Joan.Hsieh@xxxxxxxxx>
  • To: "info@xxxxxxxxxxxxxxx" <info@xxxxxxxxxxxxxxx>, 'oracle_l' <ORACLE-L@xxxxxxxxxxxxx>
  • Date: Tue, 9 Jul 2013 18:31:50 +0000

Hi David,

The database is dataguard environment, so it has to be in archive mode.  In 
cutover time windows, I don't want to break the standby database and have to 
recreate it later.  Our cutover time is combined the AIX database move to LINUX 
and one peopletools upgrade.  So there are two projects going on in the same 
open window.  Peoplesoft tech team it is ok just import data only without the 
statistics and indexes since both can be taken care in the peoplesoft upgrade. 
I'm trying to do another test without statistics and indexes.

"Drop user sysadm cascase"  on the new LINUX server take almost the whole day 
to finish. That's another issue.

Joan



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of David Kurtz
Sent: Tuesday, July 09, 2013 11:47 AM
To: 'oracle_l'
Subject: RE: how to improve impdp 

When you run the import in parallel what is your bottleneck?  What does OEM or 
the AWR Report show you?

- if you are waiting on redo or logfile sync increase parallelism probably 
isn't going to help.  I suppose you could run the database in noarchive log 
mode during the import because you have very little to lose.  However, if this 
is the case during an import you are probably going to have performance 
problems with the Financials batch processing when you go live!

- Quite a lot of this thread has discussed parallelising the export/import.
If you are not simply waiting on disk that might work.
Aside from unique indexes, there is no database enforced referential integrity 
in PeopleSoft.  So, you could easily separate the tables into different exports 
and import them concurrently too.
Everything in PeopleSoft is in on schema (by default SYSADM) and one other 
object is in the PS schema.  Afterwards you can use the PeopleSoft DDDAUDIT 
report to make sure all the tables and views are present.
You could also use PeopleTools App Designer to double check that there are no 
discrepancies between the PeopleTools data dictionary and the reality of the 
database.

Rebuilding your indexes could also be time consuming.  PeopleSoft usually 
creates them parallel and then alters them noparallel.  So the create commands 
during the Import may not run in parallel (to be honest I haven't checked).
You could just use PeopleTools App Designer to build an index build script that 
will build all the indexes in parallel and alter them noparallel again.


regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com DBA Blogs: 
PeopleSoft: http://blog.psftdba.com, Oracle:
http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Iotzov, Iordan
Sent: 09 July 2013 16:01
To: Joan.Hsieh@xxxxxxxxx; oracle_l
Subject: RE: how to improve impdp
Importance: High

If impdp over DB link approach is not feasible and IO throughput to/from the 
export/import files is not an issue, you might try "overlapping" the export and 
the import. You can split the 4 hour export into a number of smaller exports.
Run the first small export and upon completion concurrently start the 
corresponding small import and the next small export. After the next small 
export is done, start the next small export and run (or queue) the 
corresponding small import, and so on.

To be done safely, this approach requires knowledge of schemas, tables and 
constraints in the underlying database. Thorough testing is a must - make sure 
that ALL objects are accounted for.

Iordan Iotzov
http://iiotzov.wordpress.com/



Check out SmartSource Xpress, our new iPad app!
Follow us on Twitter | Like us on Facebook

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Hsieh, Joan
Sent: Tuesday, July 09, 2013 10:15 AM
To: oracle_l
Subject: how to improve impdp

Hi List,
I'm trying to use data pump to export and import from the AIX database to the 
LINUX database. The application is peoplesoft Finacial.  The expdp take around 
4 hours, the impdp take 7 hours which is a big concern on the weekend 
production cutover time.

The parameter I used on the impdp as below, do you have any suggestion how to 
improve the impdp time?

Thanks,

Joan

userid= /
directory=dmpdir
dumpfile=FMPROD_expdp.1300_070213.dmp
PARALLEL=5
logfile=impdp_FINPROD1_i07032013114001.log

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




This message and its attachments may contain legally privileged or confidential 
information. It is intended solely for the named addressee. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to the addressee), you may not copy or deliver this message or its attachments 
to anyone. Rather, you should permanently delete this message and its 
attachments and kindly notify the sender by reply e-mail. Any content of this 
message and its attachments that does not relate to the official business of 
News America Incorporated or its subsidiaries must be taken not to have been 
sent or endorsed by any of them. No warranty is made that the e-mail or 
attachment(s) are free from computer virus or other defect.

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


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


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2013