Wednesday, November 26, 2008

impdp with in Peoplesoft database

I was very enthusiat by the new features offered by the Oracle import dump, the new import utility in 10g.
Especially with the option NETWORK_LINK which allow us to do an import in a database through a database link directly from the database source, without having the need of the creation of any dump file.
It is very useful regarding the size of Peoplesoft database dump, several dozen of Gb.
Until today, to save disk space, it was common to use an old export utility into a dump file and compress on the fly. Same for the import, uncompress and import into database on fly.
Since it is not possible to do with expdp, the NETWORK_LINK should offer a good alternative.

Unfortunately, two mains points make it impossible to use :

1. NETWORK_LINK does not work with tables containing LONG datatype.
It is not a joke, but Peoplesoft database (before the Peoplesoft version 9.0 and Peopletools 8.49) still contains a lot of tables like that. I was very disapointed by this limitation, and I was surprised because nothing in the documentation say anything about that limitation. You could read the Metalink note 6630677.8 (IMPDP skips table exported just before table with LONG column using NETWORK_LINK - seems to be fixed in 11.1.0.6).
Ok, we could find a workaround and work through a file for these tables (with LONG datatype column in it), even if it's a pity. Or work with the very old, but much more robust SQL*Plus command COPY.

2. There is a bug in the number of imported rows reported in the log file (known bug #7146127, not solved yet). In some cases, for tables containing a lot of columns (I don't know what's the number to hit this error), it is reported 1 imported row in the log file, even if it imported several of thousands of rows. So, now how to prove how many have been imported ? Run a count against every single imported tables ? Not acceptable.

Here is the output of number of columns by tables (Peoplesoft HRMS9, Peopletools 8.49) :
Nb tables with less than 100 columns.................. : 42655
Nb tables with more than 100 and less than 200 columns : 171
Nb tables with more than 200 columns.................. : 21

What else ? Still stuck with exp/imp utility despite they are old.

Enjoy,


Addendum (04-FEB-2009) : regarding the wrong number of rows reported in the logfile, there is one off-patch for 10.2.0.4, it is fixed in the coming 11.2, please have a look into the following Metalink note :
Impdp Logfile Reports Wrong Number of Row Imported For a Table If Using NETWORK_LINK
Doc ID: 752476.1

Friday, November 14, 2008

Oracle 11.1.0.6_1013

Last month, October 2008, Oracle has released a new version of the initial patchset level of 11gR1 - 11.1.0.6 - downloadable on http://download.oracle.com
If you start a new project with in 11gR1 database, take care to have this version, the file is suffixed by _1013.
This new recut of the 11gR1 include a bug fix - see the Metalink note 738115.1 for more information.

Enjoy,

Saturday, November 08, 2008

Metalink3, the end of Peoplesoft GSC, future of database support

Yesterday, Firday 7th, November 2008, the website Metalink3 has take in place of the Peoplesoft Global Support Center (http://www.peoplesoft.com).
That was expected since Oracle bought Peoplesoft. Finally, it is happening.
It is a very big change for Peoplesoft cutomers.
For who work on Peoplesoft for several years, that means a lot of habits have to be change (started with the severity scale and basics words as "case" becoming "SR").

Awaiting the effectivness of the replacement (from the coming Monday, after the outage during this week-end), the Metalink3 was available in "reduce" mode without all the features. That is more likely the Oracle classic Metalink site we know for the database support.

Moreover, it has been already announced, the Metalink3 website will become also the support website for the Oracle Database, eBusiness... the date is not defined yet.

Well, hopefully, that'll help,

Enjoy,

Nicolas.