Wednesday, December 24, 2008

Big Tables in Campus Solutions

Major challenge that we faced while moving from SQL Server to Oracle was how to get big tables
from SQL Server into Oracle in the fastest time, my time slot was just 14 hours. 


There were like 11 big tables(large volume of data) - and peoplesoft has provided a purge process for only one table.
The below 11 tables were taking like 16 hrs to export and import. 

1. PS_CLASS_ATTENDNCE
2. PS_FEE_CLASS_PRICE - Purged those rows with CLASS_PRICE =0 and drop_dt = null
3. PS_SA_REPORT_RSLT - PeopleSoft (now Oracle) has provided a purge process.
4. PS_CREDIT_HISTORY
5. PS_CALC_MESSAGES
6. PS_STDNT_BUDGET_IT
7. PS_FA_FATERM_AUDIT
8. PS_COMMUNICATION
9. PS_STDNT_FA_tERM
10. PS_ITEM_LINE_SF
11. PS_ITEM_SF_GL_HIST



We used the standby database  which was in sync with the production and started the export and import simultaneously to fit the time window.

Wednesday, December 3, 2008

Configuring SSL

SSL certificate configuration

The following steps describe how to generate private keys, import certificates into a keystore and how to configure SSL via the WebLogic admin server console.

1.  Access the WebLogic domain files.

Open a DOS window and CD to the Webserve directory under your ps_home.  (e.g. CD C:\pshome\webserv\ ) and then CD to bin folder which contains pskeymanager.cmd  

2.  Create a new private key and certificate request for your server.

1.  To create a new private key and certificate signing request, run pskeymanager.cmd -create

2.  Follow the prompts and specify the information that you normally would when creating a certificate. 

Keystore password: The default keystore password will be "password". It is adviced to be changed and made a note of.

DV - DevendraVaidya

Alias: MyPS (This is what is export over the internet) https://myps.dv.com:/psp/myps/

Common name:  myps.dv.com  (domain)

OU: DVIT

Organization:  Devendra Vaidya Ltd

City: Mumbai

State: Maharashtra

Country: India

 

Take the defaults on the rest of the fields

 

3.  Submit your certificate request to a Certificate Authority (RapidSSL / Verisign/Thwate) to obtain your certificate (a.k.a public key).  

 Once the certificate it received, paste it into a .txt file. (dv.txt)


4.  Obtain the root certificate of the CA which processed your request.

Download the root CA certificate from for eg GeoTrust/ Thwate which ever you go with.

The root that is used to issue Versign/Thwate/ certificates(one you choose) is the root labeled "Equifax Secure Certificate Authority (Base-64 encoded X.509)" (Root 1).  Once the certificate it received, paste it into a .txt file. (cert1.txt)

 

5.  Create a combined certificate.

The certificate received from Versign/Thwate/ (one you choose)  must also contain a copy of the root certificate. Paste the text of cert1.txt into dv.txt after the existing text with no blank lines between the two certs.  Save the file (dv_combined.txt).

 

6.  Place the certificates on the webserver.

Place the files (cert1.txt and dv_combined.txt) into the Weblogic domain directory. (C:\pshome\webserv\dv)

 

 

7.  Import your Certificate Authority's certificate into your keystore.

To import your Certificate Authority's public certificate, which you received from your CA, into your keystore, execute "pskeymanager.cmd -import".    When prompted for an alias, specify "equifax".   This name is simply an alias for this certificate.    When prompted for the certificate file to import, specify the cert1.txt certificate file.

 

8.  Import your certificate into your keystore.

To import your public certificate, which you received from your CA, into your keystore, execute "pskeymanager.cmd -import".      When prompted for an alias specify the same alias you did when you created your private key and cert request in step 2 (e.g. your machine name MyPs).   When prompted for the certificate file to import, specify your combined certificate (dv_combined.txt).

 

9. Restart WebLogic Server

Restart the PIA server either via C:\pshome\webserv\dv\bin\stopPIA.cmd and startPIA.cmd or, if installed as an NT service, " NET START dv

 

10. Logon to the WebLogic Server Administrative Console.

Access the WebLogic Server console at http:///console.    When prompted for a User Name and password, specify the WebLogic system ID and password which you indicated during the PIA install.    If accepted the default ID and password during the PIA install they are 'system' and 'password'.

11.  Access Keystores & SSL configuration from the WebLogic Admin console.

In the graphical domain structure hierarchy on the left navigate the following;

  • Click on Servers and select PIA
  • Click on "keystores" tab
  • Click on "Lock & Edit" button in the left panel.
  • Select 'Custom Identity And Custom Trust'  from the keystores dropdown field

12.  Update keystore properties.

Update the following fields and then press the 'save' button.

Click on "Activate changes" button to activate the changes


Field


Value


Comment


Custom Identity Key Store File Name:


keystore/pskey


If a different keystore was specified via pskeymanger -keystore file, specify that  name here.


Custom Identity Key Store Type:


JKS


Always JKS


CustomIdentityKeyStorePass Phrase:


password


See note below regarding password


Confirm CustomIdentityKeyStorePass Phrase:


password


See note below regarding password


Custom Trust Key Store File Name:


keystore/pskey


If a different keystore was specified via pskeymanger -keystore file, specify that  name here.


Custom Trust Key Store Type:


JKS


Always JKS


CustomTrustKeyStorePass Phrase:


password


See note below regarding password


Confirm CustomTrustKeyStorePass Phrase:


password


See note below regarding password

Note regarding passwords:  The default keystore and private key password is 'password'.   This should NEVER be used in a production environment.   A private key's password and a keystore's password can be changed via pskeymanager's -changepassword option.

 

 

13.   Update private key properties.

Click on "SSL" tab

Update the following fields for your private key and then press the 'Save' button.


Field


Value


Comment


Private key Alias:


(e.g machine name)


Alias you specified when you created  the private key for this server in step 4.


passphrase:


password


See note below regarding password


Confirm passphrase:


password


See note below regarding password

 

 

14.  Save your keystore changes.

You MUST click the "Activate changes" button to activate the changes that you have made.

 

15.  Restart WebLogic Server and make sure the SSL port is listening on the configured port.

 

(See step 9.)

 

This information will be displayed when running the startPIA.cmd or you can run netstat –a.

 

 

 

16. Configure the other web server(s).

 

To install this cert on other web servers, just copy the /keystore/pskey file to the second webserver and make the same configurations in the Admin Console to pick the right Private Key Alias. Then restart.

Word of Thanks to Dan Ashburn- We worked together in setting this up successfully. 

Tuesday, December 2, 2008

Stundent Admin - tables that can/cannot be purged!

Motive : Sharing something I found on Customer Connect - helped me while moving PeopleSoft Campus Solutions from Sql Server to Oracle.

PROBLEM:  Which tables can or cannot be purged in Campus Solution : Student Admin Module

Ultimately, the customer is responsible for their own data when archiving records, and unless the purge process is delivered, this is not  a supported function.  We do have information on some tables that we can share, however, as long as the customer realizes that testing and maintenance of this will not be supported.  Any table not appearing in this solution should be regarded as un-purgeable.

The following table, which is NOT all inclusive, outlines some of the tables which should never be purged without serious analysis and testing.  This table was compiled as a result of a direct request regarding these specific tables, and you should not take this to mean that these are the only tables that need to remain intact.

ITEM_SF
ITEM_LINE_SF
ITEM_XREF
ITEM_DUE_SF
ACCOUNT_SF
TUIT_CALC_TBL
FEE_CLASS_PRICE
BI_BILLING_LINE
STDNT_CAR_TERM
STDNT_ENRL  (except through the delivered process)
STDNT_SESSION

The following tables may be purged through delivered processes:

STDNT_ENRL - (Manage Student Records, Manage Academic Records, Process, Drop Purge) will purge all of the classes in a drop status from PS_STDNT_ENRL.  The process is documented in Peoplebooks.

PS_SA_REPORT_RSLT

The following tables can be purged as indicated:

PS_STDNT_CARTRM_PD after Consolidated Statistics has been run

BI_EMPLID_TEMP - After any billing run
BI_ACCT_TEMP - After any billing run
BI_ACCT_ITM_TMP - After any billing run
CREDIT_HISTORY of all but the most current effective dated rows for each student
ITEM_SF_GL_HIST - if the following table is purged
SF_ACCTG_LN - after journals are processed and data no longer needed for historical purposes

COMM_SCTN _DATA  in conjunction with the following table
COMM_SCTN_HDR. (see res 200732040)

PS_SF_ACCTG_LN_OLD after upgrade has been completed