Thursday, October 4, 2012

Peoplesoft Delivered Security scripts

In this post, I am explaining some delivered scripts (Datamover, located in PSHOME directory)

1.Userexport.dms
2.userimport.dms
3. mvprdexp.dms


1.userexport.dms
SET OUTPUT USEREXPORT.DAT;
SET LOG USEREXPORT.LOG;

EXPORT PSOPRDEFN;
EXPORT PSOPRALIAS;
EXPORT PSROLEUSER;
EXPORT PSUSERATTR;
EXPORT PSUSEREMAIL;
EXPORT PSUSERPRSNLOPTN;
EXPORT PS_ROLEXLATOPR;
EXPORT PS_RTE_CNTL_RUSER

Note:This script only exports user and user role data not  roles and permission lists 

3. mvprdexp.dms
This is script contains almost all Peopletools core tables information and can be used to upgrade or move core tables.. Click here to download

Message Node tables (Content Provider node and Database node)
SELECT * FROM PSPRDMCNTPRV
SELECT * FROM PSMSGNODEDEFN
SELECT * FROM PSNODEURITEXT
SELECT * FROM PSTRUSTNODES

Portal definition
SELECT * FROM PSPRDMDEFN /*list of all portals*/
SELECT * FROM PSPRDMCNTPRV /*node templates for content provider nodes*/
SELECT * FROM PSPRDMDEFN

Portal Registry tables
SELECT * FROM PSPRSMDEFN ORDER BY PORTAL_OBJNAME, PORTAL_PRNTOBJNAME /* CRefs and Folders */
SELECT * FROM PSPRSMPERM ORDER BY PORTAL_OBJNAME /* CRef & Folder permissions */
SELECT * FROM PSPRSMATTR ORDER BY PORTAL_OBJNAME
SELECT * FROM PSPRSMATTRVAL ORDER BY PORTAL_OBJNAME
SELECT * FROM PSPRSMSYSATTR ORDER BY PORTAL_OBJNAME
SELECT * FROM PSPRSMSYSATTRVL ORDER BY PORTAL_OBJNAME

Portal User Data for 8.4
SELECT * FROM PSPRUHDEFN /*User Homepage */
SELECT * FROM PSPRUHTAB /*User Home Page tab */
SELECT * FROM PSPRUHTABPGLT ORDER BY 1,2,4 /*User Home Page tab pagelets */
SELECT * FROM PSPRUFDEFN /*Favorites */
SELECT * FROM PSPRSMHPASGPGLT /*assigned Home Page pagelets */

PORTAL_URLTEXT field in the PSPRSMDEFN table which contains the URL definitions of the Content References. There are also other PSPRSM prefix tables which are all related to the Portal and Mobile setup, but which are much less frequently needed (and easily found if they are). This case will be updated as required by version changes or customer request, but it should serve as the main repository.

Peoplesoft Tax update Generic and Specific instructions

I worked for Tax update in 2008, and through this post I will try to explain my experience on Peoplesoft Tax update  for my blog visitors and students

FAQ's on Tax Update:
-----------------------

1.How to now Tax update releases?
Ans. Oracle releases the Tax update calender every year, you can mark dates relevant to your Application . you can access calender by logon to  support.oracle.com

2.My current Oracle support covers Tax Updates?
Ans. Oracle offers 3 level of customer support  a.Premium Support b.Extended Support c.Sustaining support.  Tax, Legal, and Regulatory Updates not covered in Sustaining support. Click here to Download Oracle Lifetime support policy doc


Peoplesoft tax update general steps:
Oracle publishes the tax update calendar for a year. You can get the release
dates from that calendar. The generic steps to be followed:

1. Keep the tax update calendar and mark the dates when the tax updates will
be available in support.oracle.com
2. Download the tax update file from the Oracle
3. Pass on the release notes to the payroll analyst/concern authority to analyze any
functional and technical impacts and plan for testing.

After Approval
On Demo Environment
1,Backup Demo

2.Run Compare and report against Test DB
3. Apply tax updates in DEMO environment.
4.Analyze the changes

On Test Environment (Replica of Prod)
1.Backup Test Environment
2. Pre-Update Comp report
3. Apply tax updates in testing environment.
4.Post-Update Comp report
5. Carry out functional and technical testing in testing environment.

Raising change request for Prod from change board

1.Backup Test Environment
2. Pre-Update Comp report
3. Apply tax updates in Prod environment.
4.Post-Update Comp report
5. Carry out functional and technical testing

9. Move tax updates to production.

Apart from those steps, PSAdmin should create own excel sheet to keep update impacts and must follow Oracle docs specific to that Tax update



Thursday, August 30, 2012

Useful links for Peoplesoft web services

http://download.oracle.com/peopletools/html/service3_viewlet_swf.html

http://bloggingaboutoracleapplications.org/peoplesoft-91-mobile-inventory-setup-peoplesoft-integration-broker-activating-inventory-services/

http://peoplesoft.wikidot.com/component-interface-based-web-services

http://peoplesoft.wikidot.com/component-interfaces

http://peoplesoft.wikidot.com/creating-a-user-using-the-user-profile-ci

http://peoplesoft.wikidot.com/testing-web-services-with-soapui

Sunday, August 12, 2012

Some important V$ views

I cannot stop myself to share a few great views which provide excellent information

V$SGA :
We can get information about the system global area (SGA)

V$SGA_DYNAMIC_COMPONENTS:
Can get information about the dynamic SGA components. This summy provides details based on all completed SGA resize operations since instance startup. All sizes are expressed in bytes.

V$SGAINFO:

Can get information about the SGA, including the sizes of different SGA components, the granule size, and free memory.

V$SGASTAT

We can get detailed information on the system global area (SGA).

V$SHARED_SERVER

We can get information on the shared server processes.

V$TABLESPACE

Can get tablespace information from the control file

V$TRANSACTION

Can get lists the active transactions in the system.

V$TRANSACTION_ENQUEUE
displays locks owned by transaction state objects.

V$VERSION

We can get version numbers of core library components in the Oracle Database.


V$LOGFILE

This view contains information about redo log files.

V$BACKUP_DATAFILE_DETAILS:
We can get information about restorable datafiles. It will include all datafiles backed in the backup set, image copies, and proxy copies.

Please browse the Oracle link to explore more views
http://docs.oracle.com/cd/B19306_01/server.102/b14237/toc.htm

Saturday, August 11, 2012

How to switch user in sqlplus

We can switch users in sqlplus without logging out

SQL> conn system/system
Connected.
SQL> conn / as sysdba
Connected.
SQL> conn scott/tiger
Connected.
SQL>

Oracle basic terms and concepts

Instance:
Oracle database is associated with an Oracle instance. While database getting started on serve, Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle process (depends on configuration)

The combination of the SGA and the Oracle process is called an Oracle instance.

SGA= Context areas, Database Buffer Cache and Redo log and Oracle processes

States of instances
1.Mount
2.Opened
3.Unmount

Parameter file
To start any Oracle instance, Oracle must read an initilization parameter file: A file

containing configuration parameters for that intance and databse.
Initialisation parameter categorrized into different groups

Parameter related to Name
Parameter for limit setting maximum and minimum
Parameter for capacity : SGA (variable)

Parameter file tells Oracle :
The name of the database for which to start up an instance
How much memory to use for memory structures in the SGA
What to do with filled online redo log files
The names and locations of the database control files
The names of undo tablespaces or private rollback segments in the database

How to change Parameter values:
--------------------------------
Variable parameters for example related to SGA can be changed by dynamically by using ALTER SESSION or ALTER SYSTEM

Note:
If we are not using server parameter file, change made using the ALTER SYSTEM statement are only in effect for the current instance, it means if we reboot server, changes will be revert back to previous values. We must manually update the text initialization parameter file

These are daily useable commands in SQL Commands (Oracle)

1.How to check Log Mode:
SELECT LOG_MODE FROM V$DATABASE;

2.Change Log Mode:

Alter database archivelog; (Database must be in mounted mode, database Alter command will not permanently add entries in spfile, we need to do it manually)

to open database in mount only mode startup mount

3.Open database read only

ALTER DATABASE OPEN READ ONLY;

4. Open Database in read and write mode:

ALTER DATABASE OPEN READ WRITE;
However, read-write is the default mode.

5.Start commands

1.Normal start = startup
2.Startup mount



Other various commands
V$ARCHIVED_LOG
Displays historical archived log information from the control file.
V$ARCHIVE_DEST
Describes archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES
Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG
Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG
Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY
Contains log history information such as which logs have been archived and the SCN range for each archived log.