A GoldenGate database for Peoplesoft reports

I came across a very interesting MOS note, Deploying Oracle GoldenGate to Achieve Operational Reporting for PeopleSoft [ID 1114746.1]. It explains how build a GoldenGate (replication) database dedicated to Process Scheduler reports, instead of running reports on the primary database.
It takes full advantages of GoldenGate compared to a classic replication like standby database mechanism.
Indeed, GoldenGate allows us :
1) to replicate only a set of tables from source : who knows Peoplesoft database can realize the benefits in terms of space saving on secondary site.
2) to replicate in both directions, from primary to secondary site, but also from secondary to primary : this will be used to run a Process Scheduler on the secondary site, the tables containing the process submission will be replicated from primary to secondary, this will be checked by the Process Scheduler running on the secondary site and it will fire the process. Finally, as soon as the process is done on secondary site, the results are send back to the primary by the replication from secondary to the primary site.
Furthermore, some heavy reports can implies a lot of work regarding the queries and database tuning, and some specifics needs you would avoid on your primary database (bigger temporary tablespace, additional indexes…). So, if you have heavy report managed by the Process Scheduler to run, you could freed your database of this amount of reporting work.
Very well done, well explained in details (including what tables have to be replicated and/or excluded in what direction), this document is worth to read.

Nicolas.

Peoplesoft load without segments

As discussed few months ago here, Oracle 11gR2 offers the possibility to create table without segment. Particularly interesting on Peoplesoft database to drop down the size of the occupied size in the database, as I explained it is difficult to get rid off segment on existing table, but what happens on a new database load for a first Peopletools installation ?
Everything works as expected, after doing a database creation, just be sure to set the parameter deferred_segment_creation to TRUE on system level before the very first DataMover load :
SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL>
Then, after load, a quick check shows a lot of tables are empty (HCM9.1/Peopletools 8.50) :
SQL> select count(*) from user_tables where num_rows = 0;

  COUNT(*)
----------
     12101
But how many of them have a segment ?
SQL> select count(*)
  2  from   user_tables,dba_segments
  3  where  num_rows = 0
  4  and    segment_type='TABLE'
  5  and    table_name=segment_name;

  COUNT(*)
----------
         0
Yeah, actually none of the empty tables have segment !

Now to get rid of segment on existing table, what about export and import through DataMover ?
Just found a table with few rows, and truncate it (just for my test) :
SQL> truncate table PS_AA_COST_RT_JPN;

Table truncated.

SQL> select count(*) from dba_segments where segment_name='PS_AA_COST_RT_JPN' and segment_type='TABLE';

  COUNT(*)
----------
         1

Then with DMS, connect as PS and export the table :

Finally, import with REPLACE_ALL option :

Let’s check what happened on database side :
SQL> select count(*) from user_tables where table_name='PS_AA_COST_RT_JPN';

  COUNT(*)
----------
         1

SQL> select count(*) from dba_segments where segment_name='PS_AA_COST_RT_JPN' and segment_type='TABLE';

  COUNT(*)
----------
         0

Yeap, no more segment. So, finally, if you have an existing Peoplesoft database, and think about an upgrade to 11gR2, maybe a DataMover export and re-import of all the empty tables has to be considered (maybe that’s easier than “rebuild” everything through AppDesigner).

Nicolas.