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

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

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

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.

SQL commands to know database and instance name

SQL> Select name from v$database;
SQL> select instance_name from v$instance;
SQL> select * from global_name;
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name';
SQL> select global_name from global_name

Tmadmin commands to monitor and configure dynamically

Tmadmin has almost 50 commands, and broadly can be categorized into 7

1.Universal Device List and transaction log
2.Start, shutdown and modify part or all of the configuration
3.Monitor the configuration and its performance
4.Global Transactions
5.Services
6.Servers and its Groups
7.Use other commands


TMAdmin is a command interpreter that provides monitor and modification of the bulletin board.The command requires the

TUXCONFIG environmental variable to set

We can administrator only ne tmadmin process at a time.There are two commandline options aviable

-r (readyonly)
-c(configuration mode)
-v tmadmin to display TUXEDO version number and license number



TMAdmin miscellanmous commands

defualt (d) set default values for arguments of other commands
dump(du) dump current bulletin board into a file
ech(e) echo input command lines
help (h) print command list or command syntax
pageinate(page) pipe output of commands to a pager
quite(q) terminte the session
verbose(v) show output in verbose mode
!! repeat previous shell command
Repeat last tmadmin command

Default (d)

The default command of tmadmin allows to default values to be set for serveral frequentlyu used parameters.
config

















Tmadmin display parameter commands
_______________________________________


bbparms(bbp) print a summary of bulletin board parameters

bbsread(bbls) list IPC resources on machine mid

serverparms(srp) print parameters of the specified server
serviceparms(scp) print parameters of the specified service



Tmadmin Statistics (Note format of stats is different in verbose on and off modes)

bbstats(bbs) print a summary of the bulletin board's statistics

printclient(pclt) print names and other information about active client processes

printgroup(pg) print server group table information

printnet(pnw) print count of messages in and out for specified machines; indicates if machine is partitioned
printqueue(pq) print information for a specified queue or all queues


printserver(psr) print information for a specified server or all servers

printservice(psc) print information for a specified service or all services

shmstats(sstats) Available in SHM mode only. Enable option for more exact
statistics.





Managing the Configuration


Command(abbr) Description
aborttrans(abort) notify the coordinator of a transaction, or a participant, to abort it
committrans(commit) notify a participant of a decided transaction to commit heuristically
printtrans(pt) print information from the global transaction table
advertise(adv) add a service to the service table
unadvertise(unadv) remove a service from the service table
suspend(susp) remove a service from the list of those available. Server or queue identifiers can be used to broaden

the scope.

resume(res) return a suspended service to the list of those available Server or queue identifiers can be used to

broaden the scope.

changeload(chl) change the load specified for a service

changepriority(chp) change the priority specified for a service
changetrantime(chtt) change the time limit specified for a service
config(conf) make changes in TUXCONFIG


Managing Transactions(Global Transaction Table (GTT) ):

pt (print transactions) commands

Installing Change Assistant Installing Change Assistant: To install Change Assistant , you need to go to PS_HOME\setup\PsCA\setup.exe. Click on Setup.exe file Click Next: Click Next: Click Next Again Click Next: You Can Yes if you want to install Change Impact Analyzer: Installation Completed. Click Finish ByDefault, the Change Assistant is installed in C:\Program Files\PeopleSoft\Change Assistant folder After you have installed Change Assistant, you must set the variable path and scan your workstation. Setting the Variable Path: After installing Change Assistant, you need to set the path. 1. Select Start, Settings, Control Panel. 2. Double-click the System icon.The System Properties screen appears. 3. Select the Advanced tab. 4. Click Environment Variables. 5. Select the Path variable in the System Variables section, then click the Edit button.The Edit System Variables screen appears. 6. Insert the following, in the Variable Value field, to the beginning of the path: C:\PS_HOME\bin\client\winx86;where PS_HOME is the name of the PeopleTools folder. 7. Click OK to save your settings. Scanning the Workstation The first time you use Change Assistant, it automatically scans your workstation for applications that it will use in order to automate the steps. For example, it automatically finds the SQL Query tool and uses it to run SQL commands or scripts. If you add a new application or update an existing application, Change Assistant must perform a scan of the system in order to discover the changes. To perform this scan, select Tools, Scan Configuration.