We'll be using the "Purge" servlet directive to make it happen.
To set up your system for servlet directives you need to specify a password in your web profile. So open your web profile (PeopleTools > Web Profile > Web Profile Configuration), navigate to the Custom Properties tab, and look for a property called auditPWD. It should be set to a string, and the property value should be whatever password you want to set. The default is "dayoff". If you make any changes, you'll have to bounce your web server for it to take effect.
Once the auditPWD property is in place, you can simply call the psp servlet with the purge command, and your web server cache will be purged. The syntax of the URL is: http://[server]/psp/[site]/?cmd=purge&pwd=[password]
For example: http://psoft.rajpandian.com:6500/psp/HDEV/?cmd=purge&pwd=dayoff.
Anyway, update the URL with your specific info, paste it into your browser, and the web server cache will be cleared without a web server bounce.
As always, make sure you test these techniques in a non-production database, and use at your own risk! I hope you find them helpful.
PeopleSoft troubleshooting and tracing
Labels:
PeopleSoft
Application Engine
Tracing
Use the -TRACE option to trace the SQL executed by an application engine program. You can do this either through the command line debugger or in the override parameters of the process definition.
To trace the PeopleCode executed by an application engine program use the -TOOLSTRACEPC option.
These are the various tracing options and what they do. Like all other PeopleSoft trace flags these are bit fields so if you want trace values 1, 2 and 4, you would set the trace to 1 + 2 + 4 = 7 (-TRACE 7).
Value Trace Setting
0 Disables tracing.
1 Initiates the Application Engine step trace.
2 Initiates the Application Engine SQL trace.
4 Initiates the trace for dedicated temporary table allocation to an Application Engine trace (AET) file. You can trace how the system allocates, locks, and releases temporary tables during program runs.
128 Initiates the statement timings trace to a file, which is similar to the COBOL timings trace to a file.
256 Initiates the PeopleCode detail to the file for the timings trace.
1024 Initiates the statement timings trace, but stores the results in the PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL tables.
2048 Requests a database optimizer trace file.
4096 Requests a database optimizer to be inserted in the Explain Plan table of the current database.
8192 Sets a trace for PeopleSoft Integration Broker transform programs.
Note that output from the -TRACE flag goes to the application engine trace (.AET) file. Output from the -TOOLSTRACEPC output goes to the PeopleTools trace file (.TRC).
Command Line Debugging
To run application engine in debug mode, run the following from the command line of %PS_HOME%/bin/client/winx86. You may need to install peopletools locally first.
psae.exe -CT DatabaseType -CD DatabaseInstance -CO OperatorID -CP OperatorPassword
-R RunControl -I ProcessInstance -AI ApplicationEngineProgramName -DEBUG Y
The app engine will log into the database and present you with the following prompt:
PeopleTools 8.48.12 - Application Engine
Copyright (c) 1988-2007 PeopleSoft, Inc.
All Rights Reserved
Application Engine Debugger - enter command or type ? for help.
{APPENGINENAME}.{SECTION}.{FIRST_STEP}>
Note if you get an error about the environment variable PS_SERVER_CFG not being set, you will need to set up the following environment variables:
PS_SERVDIR pointing to your process scheduler location (e.g. %PS_HOME%\appserv\prcs\CS90)
PS_SERVER_CFG pointing to your process scheduler configuration file (e.g. %PS_SERVDIR%\psprcs.cfg)
You can view the current values in the state record, step into code - which will take you into app designer if it is a PeopleCode step. But the best feature is that it allows you to modify the contents of a state record.
Note that the following commands are available in the debugger:
Debug Commands:
(Q)uit Rollback work and end program
E(X)it Commit work and end program (valid between steps)
(C)ommit Commit work (valid between steps)
(B)reak Set or remove a break point
(L)ook Examine state record fields
(M)odify Change a state record field
(W)atch Set or remove a watch field
(S)tep over Execute current step or action and stop
Step (I)nto Go inside current step or called section and stop
Step (O)ut of Execute rest of step or called section and stop
(G)o Resume execution
(R)un to commit Resume execution and stop after next commit
COBOL
This article on COBOL SQL Tracing explains how to enable tracing on a COBOL process definition.
PeopleCode
Tracing PeopleCode
In the set trace flags on the signon page use Each Statement or through PeopleTools -> Utilities -> Debug -> Trace PeopleCode use Show Each to log all the PeopleCode that fires during your trace. This will tell you what PeopleCode and events are firing (and what is not firing) and is usually enough to give you an idea of what is going on. The best thing is that this trace produces a much smaller log file than selecting most of the other PeopleCode trace settings, making the PeopleCode trace manageable!
Writing to a log file
Here's a simple example of how to log to a file. Obivously its not a good idea to use hardcoded absolute paths so adjust this code accordingly.
&fileLog = GetFile("C:\temp\LOGFILE.log", "w", "a", %FilePath_Absolute);
&fileLog.WriteLine("Begin");
&fileLog.WriteLine("End");
&fileLog.Close();
Tracing
Use the -TRACE option to trace the SQL executed by an application engine program. You can do this either through the command line debugger or in the override parameters of the process definition.
To trace the PeopleCode executed by an application engine program use the -TOOLSTRACEPC option.
These are the various tracing options and what they do. Like all other PeopleSoft trace flags these are bit fields so if you want trace values 1, 2 and 4, you would set the trace to 1 + 2 + 4 = 7 (-TRACE 7).
Value Trace Setting
0 Disables tracing.
1 Initiates the Application Engine step trace.
2 Initiates the Application Engine SQL trace.
4 Initiates the trace for dedicated temporary table allocation to an Application Engine trace (AET) file. You can trace how the system allocates, locks, and releases temporary tables during program runs.
128 Initiates the statement timings trace to a file, which is similar to the COBOL timings trace to a file.
256 Initiates the PeopleCode detail to the file for the timings trace.
1024 Initiates the statement timings trace, but stores the results in the PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL tables.
2048 Requests a database optimizer trace file.
4096 Requests a database optimizer to be inserted in the Explain Plan table of the current database.
8192 Sets a trace for PeopleSoft Integration Broker transform programs.
Note that output from the -TRACE flag goes to the application engine trace (.AET) file. Output from the -TOOLSTRACEPC output goes to the PeopleTools trace file (.TRC).
Command Line Debugging
To run application engine in debug mode, run the following from the command line of %PS_HOME%/bin/client/winx86. You may need to install peopletools locally first.
psae.exe -CT DatabaseType -CD DatabaseInstance -CO OperatorID -CP OperatorPassword
-R RunControl -I ProcessInstance -AI ApplicationEngineProgramName -DEBUG Y
The app engine will log into the database and present you with the following prompt:
PeopleTools 8.48.12 - Application Engine
Copyright (c) 1988-2007 PeopleSoft, Inc.
All Rights Reserved
Application Engine Debugger - enter command or type ? for help.
{APPENGINENAME}.{SECTION}.{FIRST_STEP}>
Note if you get an error about the environment variable PS_SERVER_CFG not being set, you will need to set up the following environment variables:
PS_SERVDIR pointing to your process scheduler location (e.g. %PS_HOME%\appserv\prcs\CS90)
PS_SERVER_CFG pointing to your process scheduler configuration file (e.g. %PS_SERVDIR%\psprcs.cfg)
You can view the current values in the state record, step into code - which will take you into app designer if it is a PeopleCode step. But the best feature is that it allows you to modify the contents of a state record.
Note that the following commands are available in the debugger:
Debug Commands:
(Q)uit Rollback work and end program
E(X)it Commit work and end program (valid between steps)
(C)ommit Commit work (valid between steps)
(B)reak Set or remove a break point
(L)ook Examine state record fields
(M)odify Change a state record field
(W)atch Set or remove a watch field
(S)tep over Execute current step or action and stop
Step (I)nto Go inside current step or called section and stop
Step (O)ut of Execute rest of step or called section and stop
(G)o Resume execution
(R)un to commit Resume execution and stop after next commit
COBOL
This article on COBOL SQL Tracing explains how to enable tracing on a COBOL process definition.
PeopleCode
Tracing PeopleCode
In the set trace flags on the signon page use Each Statement or through PeopleTools -> Utilities -> Debug -> Trace PeopleCode use Show Each to log all the PeopleCode that fires during your trace. This will tell you what PeopleCode and events are firing (and what is not firing) and is usually enough to give you an idea of what is going on. The best thing is that this trace produces a much smaller log file than selecting most of the other PeopleCode trace settings, making the PeopleCode trace manageable!
Writing to a log file
Here's a simple example of how to log to a file. Obivously its not a good idea to use hardcoded absolute paths so adjust this code accordingly.
&fileLog = GetFile("C:\temp\LOGFILE.log", "w", "a", %FilePath_Absolute);
&fileLog.WriteLine("Begin");
&fileLog.WriteLine("End");
&fileLog.Close();
PeopleTools security records
Labels:
PeopleSoft
A summary of the main PeopleTools security records.
Operators
Record Description
PSOPRDEFN
Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list.
PSROLEUSER
The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to.
PSOPRCLS Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention.
PSOPRALIAS
Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity.
PSOPRALIASTYPE
This is the setup table for operator aliases
PSOPRALIASFIELD
This is the setup table that maps operator aliases to records & fields
PSUSERATTR
User attributes store the a hint password question & response for a user (if this is enabled)
PSUSEREMAIL Email addresses for users.
Roles
======
Record Description
PSROLEDEFN
Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing.
PSROLECLASS
Roles are made of up of one or permission lists, and this table links the two together. Very handy.
Permission Lists
=================
Record Description
PSCLASSDEFN
Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change.
PSAUTHITEM
The link between permission lists and menus
PSAUTHBUSCOMP The link between permission lists and component interfaces and their methods
PSAUTHOPTN The link between permission lists and personalisations
PSAUTHPRCS The link between permission lists and process groups
PSAUTHSIGNON The link between permission lists and signon times
PSAUTHWEBLIBVW A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS The link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP The link between permission lists, trees and query access groups
PS_MC_OPR_SECURITY The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists
Check out this forum post for details about how to check which users have access to PeopleTools.
Portal
The PeopleSoft Enterprise Portal basically refers to the navigation system that links menus and components to the PeopleSoft online interface, navigation and search functionality. Access to anything in the online application is handled through portal security. The records associated with this include:
Record Description
PSPRSMDEFN Stores the structure of the portal registry. This data is stored in a hierarchical (tree) structure within the table. The field PORTAL_URI_SEG1 is the menu, PORTAL_URI_SEG2 is the component, and PORTAL_URI_SEG3 is the market.
PSPRSMPERM Stores permission lists associated with access to everything within the portal registry
Operators
Record Description
PSOPRDEFN
Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list.
PSROLEUSER
The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to.
PSOPRCLS Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention.
PSOPRALIAS
Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity.
PSOPRALIASTYPE
This is the setup table for operator aliases
PSOPRALIASFIELD
This is the setup table that maps operator aliases to records & fields
PSUSERATTR
User attributes store the a hint password question & response for a user (if this is enabled)
PSUSEREMAIL Email addresses for users.
Roles
======
Record Description
PSROLEDEFN
Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing.
PSROLECLASS
Roles are made of up of one or permission lists, and this table links the two together. Very handy.
Permission Lists
=================
Record Description
PSCLASSDEFN
Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change.
PSAUTHITEM
The link between permission lists and menus
PSAUTHBUSCOMP The link between permission lists and component interfaces and their methods
PSAUTHOPTN The link between permission lists and personalisations
PSAUTHPRCS The link between permission lists and process groups
PSAUTHSIGNON The link between permission lists and signon times
PSAUTHWEBLIBVW A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS The link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP The link between permission lists, trees and query access groups
PS_MC_OPR_SECURITY The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists
Check out this forum post for details about how to check which users have access to PeopleTools.
Portal
The PeopleSoft Enterprise Portal basically refers to the navigation system that links menus and components to the PeopleSoft online interface, navigation and search functionality. Access to anything in the online application is handled through portal security. The records associated with this include:
Record Description
PSPRSMDEFN Stores the structure of the portal registry. This data is stored in a hierarchical (tree) structure within the table. The field PORTAL_URI_SEG1 is the menu, PORTAL_URI_SEG2 is the component, and PORTAL_URI_SEG3 is the market.
PSPRSMPERM Stores permission lists associated with access to everything within the portal registry
which users have access to a particular page
Labels:
PeopleSoft
Find out which users (and from what permission lists) have access to a particular page:
select distinct OPRID, OPRCLASS
from PSOPRCLS
where OPRCLASS in (
select distinct CLASSID
from sysadm.PSAUTHITEM
where PNLITEMNAME = ''
);
select distinct OPRID, OPRCLASS
from PSOPRCLS
where OPRCLASS in (
select distinct CLASSID
from sysadm.PSAUTHITEM
where PNLITEMNAME = '
);
Query to find processes by process status
Labels:
PeopleSoft
The following query will give you a summary of the requested processes by process status.
select
RQST.RUNSTATUS,
RQST.PRCSTYPE,
(
select XLAT.XLATLONGNAME
from PSXLATITEM XLAT
where XLAT.EFFDT = (
select max(XLAT_ED.EFFDT)
from PSXLATITEM XLAT_ED
where XLAT_ED.FIELDNAME = XLAT.FIELDNAME
and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE
) and XLAT.FIELDNAME = 'RUNSTATUS'
and XLAT.FIELDVALUE = RQST.RUNSTATUS
) as RUNSTATUS_XLAT,
count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,
min(RUNDTTM) as FIRST_OCCURRED,
max(RUNDTTM) as LAST_OCCURRED
from PSPRCSRQST RQST
group by RQST.RUNSTATUS, RQST.PRCSTYPE
order by RUNSTATUS_XLAT, RQST.PRCSTYPE
select
RQST.RUNSTATUS,
RQST.PRCSTYPE,
(
select XLAT.XLATLONGNAME
from PSXLATITEM XLAT
where XLAT.EFFDT = (
select max(XLAT_ED.EFFDT)
from PSXLATITEM XLAT_ED
where XLAT_ED.FIELDNAME = XLAT.FIELDNAME
and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE
) and XLAT.FIELDNAME = 'RUNSTATUS'
and XLAT.FIELDVALUE = RQST.RUNSTATUS
) as RUNSTATUS_XLAT,
count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,
min(RUNDTTM) as FIRST_OCCURRED,
max(RUNDTTM) as LAST_OCCURRED
from PSPRCSRQST RQST
group by RQST.RUNSTATUS, RQST.PRCSTYPE
order by RUNSTATUS_XLAT, RQST.PRCSTYPE
Application Engine programs only stuck at queued
Labels:
PeopleSoft
You may find that only application engine programs are stuck at queued while other processes (SQRs, crystals etc) run to success. This typically happens due to processes blocking the process scheduler queue. Check the process scheduler/master process scheduler logs. You might see something like this:
Checking Process cancels...
(NET.113): Client ChkAeStatus3 service request succeeded
Process 2319373 is still running as Session ID 19955
(NET.113): Client ChkAeStatus1 service request succeeded
Process 2319395 is still running as Session ID 19946
(NET.113): Client ChkAeStatus2 service request succeeded
Process 2319404 is still running as Session ID 19950
Application Engine : 3:3
Requests found in Process Request table 3
This indicates that the three process instances, 2319373, 2319395 and 2319404 are all running. As there is a maximum of 3 application engine programs that can run at any one time and there are currently 3 running, all other application engine programs requested will remained at queued. However, the three process instances may not actually be running. If this is the case, these will need to be manually stopped, for example with a process scheduler restart and perhaps by manually killing the processes on the process scheduler server if required.
Checking Process cancels...
(NET.113): Client ChkAeStatus3 service request succeeded
Process 2319373 is still running as Session ID 19955
(NET.113): Client ChkAeStatus1 service request succeeded
Process 2319395 is still running as Session ID 19946
(NET.113): Client ChkAeStatus2 service request succeeded
Process 2319404 is still running as Session ID 19950
Application Engine : 3:3
Requests found in Process Request table 3
This indicates that the three process instances, 2319373, 2319395 and 2319404 are all running. As there is a maximum of 3 application engine programs that can run at any one time and there are currently 3 running, all other application engine programs requested will remained at queued. However, the three process instances may not actually be running. If this is the case, these will need to be manually stopped, for example with a process scheduler restart and perhaps by manually killing the processes on the process scheduler server if required.
Process run status
Labels:
PeopleSoft
In the PeopleTools 8.4x look at the translates on the field RUNSTATUS or use the query.
select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
Here's a summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.
Value Status
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart
The following query will give you a summary of the process run statuses in your process request table:
select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;
select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
Here's a summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.
Value Status
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart
The following query will give you a summary of the process run statuses in your process request table:
select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;
Processes stuck at Queued
Labels:
PeopleSoft
There are a number of reasons why a process might be stuck at queued. The most obvious is that the process scheduler is down (check the Servers tab in the process monitor). Beyond this, there are some useful troubleshooting that apply:
Check the following tables:
PSPRCSRQST
PSPRCSQUE
PSPRCSPARMS
The row count should be the same in both tables. If one is out of sync with the other, then it can help to remove orphaned instances in of the tables. Restarting the process scheduler and clearing the process scheduler cache will also fix a number of issues.
Check the following tables:
PSPRCSRQST
PSPRCSQUE
PSPRCSPARMS
The row count should be the same in both tables. If one is out of sync with the other, then it can help to remove orphaned instances in of the tables. Restarting the process scheduler and clearing the process scheduler cache will also fix a number of issues.
EMI calculation using Excel
I mentioned the formula used to calculate an EMI, but putting values in the formula and calculating it is a cumbersome task. So I did some research in Microsoft Excel and found out that it can done quite easily in excel.
EMI calculation in Excel
Step 1: Open the excel sheet and locate the fx button
Step 2: In the pop-up menu, click on Financial Catergory
Step 3: In the Function Name click 'PMT'
Step 4: A box will appear as shown, fill in the values mentioned and you get the EMI.
EMI calculation in Excel
Step 1: Open the excel sheet and locate the fx button
Step 2: In the pop-up menu, click on Financial Catergory
Step 3: In the Function Name click 'PMT'
Step 4: A box will appear as shown, fill in the values mentioned and you get the EMI.
Good Amazon deals site
Labels:
General
I found this site has good one to buy from Amazon
www.deal4low.com
www.deal4low.com
வாழ்க்கை கணக்கு
வாழ்க்கையில்,
அன்பை கூட்டிக்கொள்!
அறிவை பெருக்கிக்கொள்!
இனிமையை
தனிமையால் வகுத்துக்கொள்!
பாவத்தைக் கழித்துக் கொள்!
பிறருடன் சமமாக
வாழ கற்றுக் கொள்...!
அன்பை கூட்டிக்கொள்!
அறிவை பெருக்கிக்கொள்!
இனிமையை
தனிமையால் வகுத்துக்கொள்!
பாவத்தைக் கழித்துக் கொள்!
பிறருடன் சமமாக
வாழ கற்றுக் கொள்...!
Cloning PeopleSoft Database
Labels:
Oracle DBA
Overview
This document describes the steps required to create a clone of the PeopleSoft production database. This is the most common task of a PeopleSoft Administrator / in any PeopleSoft upgrade project.
Here are some of the steps that need to be consider when creating a Development or Test environment from a production database on PeopleTools 8.4X.
Steps
1. Create a backup of the production database. (This is your source database)
2. Stop the Target Application Environment. (If application environment is running)
Application Servers
Process Schedulers
Web Servers
3. Overlay the target database with a recent backup of the production database.
4. Start the database
5. Set DBNAME in PSDBOWNER back to the target database name.
UPDATE PSDBOWNER SET DBOWNER = 'Database Name';
6. Set GUID to ' ' in PSOPTIONS table.
UPDATE PSOPTIONS SET GUID = ' ';
This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.
7. Delete the data from
Reporting tables
Process scheduler tables
Application messaging tables
This data isn't relevant in the target database.
Below DMS scripts are provided by PeopleSoft. Use these scripts to clean the above tables.
prcsclr.dms (Process Scheduler Tables)
rptclr.dms (Reporting Tables)
appmsgpurgeall.dms (Application Messaging Tables)
8. Reset email address to blank or predefined value.
Since you are creating a clone of the production database you will have actual email address of all the employees. Email address is a very critical data. Reset everyone's e-mail address to a pre-defined value so that workflow messages from the Test environment don't get sent to real users.
Some of the common tables of PeopleSoft ERP are as follows
PSOPRDEFN
PS_ROLEXLATOPR
PSUSEREMAIL
You need to take care of Application specific tables. You can find out these tables by using below script.
SELECT RECNAME
FROM PSRECFIELD B
WHERE EXISTS (SELECT 'X' FROM PSRECDEFN A WHERE RECTYPE = 0 AND A.RECNAME = B.RECNAME)
AND B.FIELDNAME = 'EMAILID'
8. Change application password (VP1)
Logon to PeopleSoft data mover script and execute the below scripts if you want to chage the application password.
UPDATE PSOPRDEFN SET OPERPSWD = 'PASSWORD', ENCRYPTED = 0 WHERE OPRID = VP1;
ENCRYPT_PASSWORD VP1;
This document describes the steps required to create a clone of the PeopleSoft production database. This is the most common task of a PeopleSoft Administrator / in any PeopleSoft upgrade project.
Here are some of the steps that need to be consider when creating a Development or Test environment from a production database on PeopleTools 8.4X.
Steps
1. Create a backup of the production database. (This is your source database)
2. Stop the Target Application Environment. (If application environment is running)
Application Servers
Process Schedulers
Web Servers
3. Overlay the target database with a recent backup of the production database.
4. Start the database
5. Set DBNAME in PSDBOWNER back to the target database name.
UPDATE PSDBOWNER SET DBOWNER = 'Database Name';
6. Set GUID to ' ' in PSOPTIONS table.
UPDATE PSOPTIONS SET GUID = ' ';
This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.
7. Delete the data from
Reporting tables
Process scheduler tables
Application messaging tables
This data isn't relevant in the target database.
Below DMS scripts are provided by PeopleSoft. Use these scripts to clean the above tables.
prcsclr.dms (Process Scheduler Tables)
rptclr.dms (Reporting Tables)
appmsgpurgeall.dms (Application Messaging Tables)
8. Reset email address to blank or predefined value.
Since you are creating a clone of the production database you will have actual email address of all the employees. Email address is a very critical data. Reset everyone's e-mail address to a pre-defined value so that workflow messages from the Test environment don't get sent to real users.
Some of the common tables of PeopleSoft ERP are as follows
PSOPRDEFN
PS_ROLEXLATOPR
PSUSEREMAIL
You need to take care of Application specific tables. You can find out these tables by using below script.
SELECT RECNAME
FROM PSRECFIELD B
WHERE EXISTS (SELECT 'X' FROM PSRECDEFN A WHERE RECTYPE = 0 AND A.RECNAME = B.RECNAME)
AND B.FIELDNAME = 'EMAILID'
8. Change application password (VP1)
Logon to PeopleSoft data mover script and execute the below scripts if you want to chage the application password.
UPDATE PSOPRDEFN SET OPERPSWD = 'PASSWORD', ENCRYPTED = 0 WHERE OPRID = VP1;
ENCRYPT_PASSWORD VP1;
Oracle Enqueues
Labels:
Oracle DBA
A Latch as used in Oracle is a low-level mechanism for serializing concurrent access to memory structures such as cache buffer headers. A latch is a simple memory structure that averages 100 to 200 bytes in size (actual size is dependent upong the version of Oracle, Operating System and hardware platform). Latches are contained in a state object structure and reside in either fixed SGA (parent latches) or the shared pool. They are set via the hardware-atomic compare-and-swap (CAS) instructions – LOCK CMPXCHG for Intel. Latches can be shared and are used for some AQ operations.
Latch Contention occurs when a process attempts to get a latch but fails because another process already has control of it. If the attempt (get) was issue with in no-wait mode, then return to the calling process with a failure result. If the get was issued in a willing-to-wait mode then continue attempting to obtain the latch until x number of tries is reached.
The term to describe a get that is executed in a willing-to-wait mode that tries repeatedly without acquiring the latch is spinning – the process issues the get, if the latch is already held then the process will sleep, wake up after x amount of time expires and attempts to acquire the latch again.
The process attempting to acquire a latch may be waken by the process that releases the latch but this method isn’t used for all latches.
The following views are used to troubleshoot contention issues:
v$session_event
v$session_wait
v$latchholder
Troubleshooting latch contention consists of first identifying the session(s) that are currently experiencing problems and then identify the latch the session is waiting for and determine if the wait time is significant enough to warrant attention. Identifying the child latch that is involved will help determine if the contention is concentrated on a particular child latch or are many child latches involved.
As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and consume less memory space. They can also be embedded inside other structures, they have flexible spin/yield/wait strategies defined by the client process and they do not factor into the accounting for GETS,SPINS or YIELDS, only WAITS.
Keep in mind that KGX mutexes are not OS mutexes.
In 11g+ mutexes are used instead of library cache latches. The default for pinning library cache cursors and parent examination for 10.2.0.2+ is mutexes – _kks_use_mutex_pin=true.
Some known mutex types in 11g:
Cursor Parent
Cursor Pin
Cursor Stat
Library Cache
hash table
Mutex Troubleshooting – involves querying the views: v$session_wait, v$mutex_sleep and v$mutex_sleep_history among others. The mutex sleeps are well instrumented in the wait interface and the P1, P2, P3 values contain the hash value of the library cache objects experiencing contention, the session holding the mutex, etc. The views v$event_name and v$session_wait “text” columns document the meaning of P1, P2 and P3. The v$mutex_sleep view shows the wait time and the number of sleeps for each combination of mutex type and location while the v$mutex_sleep_history view shows the last individual occurences of mutex sleeps based on a circular buffer and this view contains the highest level of detail.
Enqueues – Terminology and Views
Locks are called enqueues because the locking mechanism implements an ordered queuing structure for lock waiters. Latches and mutexes do not have sophisticated queuing mechanisms.
Enqueue Resources – v$resource(X$KSQRS)
Enqueue Locks – v$lock(X$KSQEQ)
Enqueue resource types – v$lock_type has two meanings – ID1 and ID2
Slots in a hash table identify a resource. Resources are not locks, they are placeholders that reference the object that can be locked. The unique identifier (primarykey) for a resource will consists of the following:
Resource type (TM, TX, MR, …)
Resource identifier 1 (ID1)
Resource identified 2 (ID2)
Example:
TM-XXXXXXXX-XXXXXXXX
The resource identifiers are used to break down a resource class into individual objects. An example of this would be two tables that both have a TM resource allocated to them in memory. The ID1 specifies the object_id of the table (v$lock_type which contains the meaning of ID1/2 for each resource type).
Locks – provide the link between the resource locked and the holder of that resource. If there are many lock holders on a single resource (assuming compatible mode) then it follows that there will be multiple lock structures pointing to that one resource.
Latch Contention occurs when a process attempts to get a latch but fails because another process already has control of it. If the attempt (get) was issue with in no-wait mode, then return to the calling process with a failure result. If the get was issued in a willing-to-wait mode then continue attempting to obtain the latch until x number of tries is reached.
The term to describe a get that is executed in a willing-to-wait mode that tries repeatedly without acquiring the latch is spinning – the process issues the get, if the latch is already held then the process will sleep, wake up after x amount of time expires and attempts to acquire the latch again.
The process attempting to acquire a latch may be waken by the process that releases the latch but this method isn’t used for all latches.
The following views are used to troubleshoot contention issues:
v$session_event
v$session_wait
v$latchholder
Troubleshooting latch contention consists of first identifying the session(s) that are currently experiencing problems and then identify the latch the session is waiting for and determine if the wait time is significant enough to warrant attention. Identifying the child latch that is involved will help determine if the contention is concentrated on a particular child latch or are many child latches involved.
As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and consume less memory space. They can also be embedded inside other structures, they have flexible spin/yield/wait strategies defined by the client process and they do not factor into the accounting for GETS,SPINS or YIELDS, only WAITS.
Keep in mind that KGX mutexes are not OS mutexes.
In 11g+ mutexes are used instead of library cache latches. The default for pinning library cache cursors and parent examination for 10.2.0.2+ is mutexes – _kks_use_mutex_pin=true.
Some known mutex types in 11g:
Cursor Parent
Cursor Pin
Cursor Stat
Library Cache
hash table
Mutex Troubleshooting – involves querying the views: v$session_wait, v$mutex_sleep and v$mutex_sleep_history among others. The mutex sleeps are well instrumented in the wait interface and the P1, P2, P3 values contain the hash value of the library cache objects experiencing contention, the session holding the mutex, etc. The views v$event_name and v$session_wait “text” columns document the meaning of P1, P2 and P3. The v$mutex_sleep view shows the wait time and the number of sleeps for each combination of mutex type and location while the v$mutex_sleep_history view shows the last individual occurences of mutex sleeps based on a circular buffer and this view contains the highest level of detail.
Enqueues – Terminology and Views
Locks are called enqueues because the locking mechanism implements an ordered queuing structure for lock waiters. Latches and mutexes do not have sophisticated queuing mechanisms.
Enqueue Resources – v$resource(X$KSQRS)
Enqueue Locks – v$lock(X$KSQEQ)
Enqueue resource types – v$lock_type has two meanings – ID1 and ID2
Slots in a hash table identify a resource. Resources are not locks, they are placeholders that reference the object that can be locked. The unique identifier (primarykey) for a resource will consists of the following:
Resource type (TM, TX, MR, …)
Resource identifier 1 (ID1)
Resource identified 2 (ID2)
Example:
TM-XXXXXXXX-XXXXXXXX
The resource identifiers are used to break down a resource class into individual objects. An example of this would be two tables that both have a TM resource allocated to them in memory. The ID1 specifies the object_id of the table (v$lock_type which contains the meaning of ID1/2 for each resource type).
Locks – provide the link between the resource locked and the holder of that resource. If there are many lock holders on a single resource (assuming compatible mode) then it follows that there will be multiple lock structures pointing to that one resource.
What Character Set Supports Which Language
Labels:
Oracle DBA
Overview
This list identifies which Oracle ISO or MSWIN character sets contains which supported language(s). All of these character sets contain the 7-bit ASCII English characters and numerals. Oracle lists both the ISO and MSWIN characteristics and their recommendation is to use the MSWIN database character sets instead of the ISO versions, even if all of your clients are not Windows based. This is simply because the MSWIN database character sets are more comprehensive and support more characters than the ISO database character sets.
There are no problems using a MSWIN database character set on a non-Windows/Unix platform.
You can determine what the current value for your database character set is by executing the following statement:
select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;
To determine the client setting use ‘NLS_LANG’ instead of ‘NLS_CHARACTERSET’ and see note below.
Character Sets
WE8ISO8859P15 (ISO 8859-15), WE8MSWIN1252 : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Finnish, French, Frisian, Galician, German, Greenlandic, Irish Gaelic, Italian, Luxembourgish, Norwegian, Portuguese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish and the Euro symbol
EE8ISO8859P2 (ISO 8859-2), EE8MSWIN1250 : Albanian, Croatian, Czech, German, Hungarian, Polish, Slovak, Slovenian, Sorbian
NEE8ISO8859P4 (ISO 8859-4), BLT8MSWIN1257 : Danish, Estonian, Finnish, German, Greenlandic, Latvian, Lithuanian, Norwegian, Sami, Slovenian, Swedish
CL8ISO8859P5 (ISO 8859-5), CL8MSWIN1251 : Bulgarian, Byelorussian, Slavic Macedonian, Russian, Sebian, Ukrainian
AR8ISO8859P6 (ISO 8859-6), AR8MSWIN1256 : Arabic
EL8ISO8859P7 (ISO 8859-7), EL8MSWIN1253 : Greek (monitoniko orthography)
IW8ISO8859P8 (ISO 8859-8), IW8MSWIN1255 : Hebrew
VN8MSWIN1258 : Vietnamese
TH8TISASCII : Thai
JA16SJIS : Japanese
ZHS16GBK : Simplified Chinese
KO16MSWIN949 : Korean
ZHT16HKSCS and ZHT16HKSCS31 : Traditional Chinese (Taiwan)
ZHT16HKSCS and ZHT16HKSCS31 : Traditional Chinese (Hong Kong)
WE8ISO8859P2 (ISO 8859-1) : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Faroese, French, Finnish, Frisian, Galician, German, Greenlandic, Icelandic, Irish Gaelic (new orthography), Italian, Luxemburgish, Norwegian, Portugese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish
SE8ISO8859P3 (ISO 8859-3) : Esperanto, French, German, Italian, Maltese, Portugese, Turkish
WE8ISO8859P9 (ISO 8859-9) : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Finnish, French, Galician, German, Greenlandic, Irish Gaelic (new orthography), Italian, Luxemburgish, Norwegian, Portugese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish, Turkish
NE8ISO8859P10 (ISO 8859-10) : Dannish, Estonian, Faroese, Finnish, German, Greenlandic, Icelandic, Irish Gaelic (new orthography), Lithuanian, Norwegian, Sami, Slovenian, Swedish
Notes:
An external resource http://www.eki.ee/letter allows you to choose a language and then it displays an overview of all the charactersets that contain all the letters.
This list identifies which Oracle ISO or MSWIN character sets contains which supported language(s). All of these character sets contain the 7-bit ASCII English characters and numerals. Oracle lists both the ISO and MSWIN characteristics and their recommendation is to use the MSWIN database character sets instead of the ISO versions, even if all of your clients are not Windows based. This is simply because the MSWIN database character sets are more comprehensive and support more characters than the ISO database character sets.
There are no problems using a MSWIN database character set on a non-Windows/Unix platform.
You can determine what the current value for your database character set is by executing the following statement:
select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;
To determine the client setting use ‘NLS_LANG’ instead of ‘NLS_CHARACTERSET’ and see note below.
Character Sets
WE8ISO8859P15 (ISO 8859-15), WE8MSWIN1252 : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Finnish, French, Frisian, Galician, German, Greenlandic, Irish Gaelic, Italian, Luxembourgish, Norwegian, Portuguese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish and the Euro symbol
EE8ISO8859P2 (ISO 8859-2), EE8MSWIN1250 : Albanian, Croatian, Czech, German, Hungarian, Polish, Slovak, Slovenian, Sorbian
NEE8ISO8859P4 (ISO 8859-4), BLT8MSWIN1257 : Danish, Estonian, Finnish, German, Greenlandic, Latvian, Lithuanian, Norwegian, Sami, Slovenian, Swedish
CL8ISO8859P5 (ISO 8859-5), CL8MSWIN1251 : Bulgarian, Byelorussian, Slavic Macedonian, Russian, Sebian, Ukrainian
AR8ISO8859P6 (ISO 8859-6), AR8MSWIN1256 : Arabic
EL8ISO8859P7 (ISO 8859-7), EL8MSWIN1253 : Greek (monitoniko orthography)
IW8ISO8859P8 (ISO 8859-8), IW8MSWIN1255 : Hebrew
VN8MSWIN1258 : Vietnamese
TH8TISASCII : Thai
JA16SJIS : Japanese
ZHS16GBK : Simplified Chinese
KO16MSWIN949 : Korean
ZHT16HKSCS and ZHT16HKSCS31 : Traditional Chinese (Taiwan)
ZHT16HKSCS and ZHT16HKSCS31 : Traditional Chinese (Hong Kong)
WE8ISO8859P2 (ISO 8859-1) : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Faroese, French, Finnish, Frisian, Galician, German, Greenlandic, Icelandic, Irish Gaelic (new orthography), Italian, Luxemburgish, Norwegian, Portugese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish
SE8ISO8859P3 (ISO 8859-3) : Esperanto, French, German, Italian, Maltese, Portugese, Turkish
WE8ISO8859P9 (ISO 8859-9) : Albanian, Basque, Breton, Catalan, Cornish, Danish, Dutch, Finnish, French, Galician, German, Greenlandic, Irish Gaelic (new orthography), Italian, Luxemburgish, Norwegian, Portugese, Rhaeto-Romanic, Scottish Gaelic, Spanish, Swedish, Turkish
NE8ISO8859P10 (ISO 8859-10) : Dannish, Estonian, Faroese, Finnish, German, Greenlandic, Icelandic, Irish Gaelic (new orthography), Lithuanian, Norwegian, Sami, Slovenian, Swedish
Notes:
An external resource http://www.eki.ee/letter allows you to choose a language and then it displays an overview of all the charactersets that contain all the letters.
RMAN script
Labels:
Oracle DBA
Scripting Oracle RMAN Commands
Two ways to script RMAN, use a command file which is a text file residing in the file system and use a stored script which is stored in the RMAN catalog database and executed from the RMAN command prompt.
Command file to backup the USERS tablespace:
connect target /
connect catalog rman/secretpass@rmancat
run { allocate channel c1 type disk format ‘/orabak/%U’;
backup tablespace users;
}
RMAN> @backup_ts_users.rman
rman @backup_ts_users.rman
rman cmdfile=backup_ts_users.rman
rman target=/ catalog=
rman/secretpass@rmancat
Note that the CONNECT clauses are inside the backup_ts_users.rman command file, so there is no reason to provide the password in the command line – meaning you can eliminate the risk of accidental exposure of the password. Had we not included the password of the catalog user rman inside the command file, we would have had to call the Oracle RMAN executable like above.
To use a command file parameter-driven approach like the following where instead of actual values, the new command file includes the parameters (also known as placeholders or substitution variables) &1 and &2 which with a parameter-driven command file you define any number of parameters in this manner and pass the values at runtime.
connect target /
connect catalog rman/secretpass@rmancat
run { allocate channel c1 type disk format ‘&1/%U’;
backup tablespace &2;
}
A shell script, named backup_ts_generic.sh calls the backup_ts_generic.rman command file with the values /tmp as the backup location for parameter &1 and USERS as the tablespace name for parameter &2:
$ORACLE_HOME/bin/rman <
@backup_ts_generic.rman “/tmp” USERS
EOF
You can make this shell script even more generic so that the parameters are passed from the command line of the file system itself. For example, if you modify the second line in the backup_ts_generic.sh shell script to read:
@backup_ts_generic.rman “/tmp” $1
you will be able to call the backup_ts_generic.rman command file, provide /tmp as the backup location, and pass the tablespace name in the command line. For instance, if you want to backup the MYTS1 tablespace, you can issue the command:
backup_ts_generic.rman MYTS1
To log RMAN scripts via an automated mechanism such as cron in UNIX or Scheduler in Windows, you are not physically watching the command window, so how do you know the output of the Oracle RMAN commands? To capture the output, you can use the log parameter in the Oracle RMAN command line:
rman cmdfile=backup_ts_users.rman log=backup_ts_users.log
Stored Scripts – although command files work pretty well in most cases, they have one huge drawback – a command file should be available on the server where the Oracle RMAN backup is to be run. Otherwise, from within the command file, you have to connect from the Oracle RMAN client to the server by using a connect string:
connect target sys/oracle123@remotedb
There are several problems with this setup. First, the modified command file needs to store the password of SYS or some other user with SYSDBA privilege. In a security-conscious environment that may not be acceptable. Second, the Oracle RMAN client may not be compatible with the Oracle Database release. Finally, for performance reasons, you may very well want to run the Oracle RMAN client on the same server as the database itself. But what if you have databases on different servers? You will have to replicate a command file script to all servers. And when you modify the script you will have to make sure it is copied to all those servers again.
The solution use Oracle RMAN stored scripts, you can create scripts that are stored inside the Oracle RMAN catalog and not on the server itself. Because it is stored inside the Oracle RMAN catalog, you will need to connect to the catalog first, as shown below. To execute this script, al you have to do is call it with the execute command from the Oracle RMAN prompt:
RMAN> run { execute script backup_ts_users; }
Stored script for backing up USERS tablespace:
rman
RMAN> connect target /
RMAN> connect catalog rman/secretpass@rmancat
RMAN> create script backup_ts_users
command ‘Tablespace USERS Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace users;
}
The backup_ts_users stored script created above is available only to the target database to which it is currently connected. It is a local stored script, and you can’t execute a local script for one database in another. To execute a script in multiple databases, create a global stored script by using the keyword GLOBAL between CREATE and SCRIPT. For instance, to create a global script for the one above, replace:
create script backup_ts_users
with
create global script backup_ts_users
Once created, this global stored script can be executed in any database connected to this catalog. If you need to modify the script, there is no need to copy it to all servers or databases; it’s automatically available for execution to all databases connecting to the catalog.
If the global stored script already exists and you want to update it, replace CREATE with REPLACE:
replace global script backup_ts_users
- and include the updated script text.
Parameterization: This backup_ts_users stored script has a very specific purpose – backing up the USERS tablespace. What if you want to backup a different tablespace? Rather than creating multiple scripts, you can create a generic stored script to backup any tablespace.
A stored script merely asks for a value; it does not store the value you used in the script itself:
RMAN> create script backup_ts_any
comment ‘Any Tablespace Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace &1;
}
With the parameterized stored script created, pass the value of the parameter via a USING clause. For example, to backup the SYSTEM tablespace by using this backup_ts_any stored script, use the following Oracle RMAN command:
run { execute script
backup_ts_any using ‘SYSTEM’; }
To display the list of stored scripts:
RMAN> list script names;
RMAN> list global script names;
RMAN> print global script backup_ts_level1_any;
RMAN> delete global script backup_ts_level1_any;
RMAN> create script backup_ts_users
from file ‘backup_ts_users.rman’;
RMAN> print script backup_ts_users to file ‘backup_ts_users.rman’;
Two ways to script RMAN, use a command file which is a text file residing in the file system and use a stored script which is stored in the RMAN catalog database and executed from the RMAN command prompt.
Command file to backup the USERS tablespace:
connect target /
connect catalog rman/secretpass@rmancat
run { allocate channel c1 type disk format ‘/orabak/%U’;
backup tablespace users;
}
RMAN> @backup_ts_users.rman
rman @backup_ts_users.rman
rman cmdfile=backup_ts_users.rman
rman target=/ catalog=
rman/secretpass@rmancat
Note that the CONNECT clauses are inside the backup_ts_users.rman command file, so there is no reason to provide the password in the command line – meaning you can eliminate the risk of accidental exposure of the password. Had we not included the password of the catalog user rman inside the command file, we would have had to call the Oracle RMAN executable like above.
To use a command file parameter-driven approach like the following where instead of actual values, the new command file includes the parameters (also known as placeholders or substitution variables) &1 and &2 which with a parameter-driven command file you define any number of parameters in this manner and pass the values at runtime.
connect target /
connect catalog rman/secretpass@rmancat
run { allocate channel c1 type disk format ‘&1/%U’;
backup tablespace &2;
}
A shell script, named backup_ts_generic.sh calls the backup_ts_generic.rman command file with the values /tmp as the backup location for parameter &1 and USERS as the tablespace name for parameter &2:
$ORACLE_HOME/bin/rman <
@backup_ts_generic.rman “/tmp” USERS
EOF
You can make this shell script even more generic so that the parameters are passed from the command line of the file system itself. For example, if you modify the second line in the backup_ts_generic.sh shell script to read:
@backup_ts_generic.rman “/tmp” $1
you will be able to call the backup_ts_generic.rman command file, provide /tmp as the backup location, and pass the tablespace name in the command line. For instance, if you want to backup the MYTS1 tablespace, you can issue the command:
backup_ts_generic.rman MYTS1
To log RMAN scripts via an automated mechanism such as cron in UNIX or Scheduler in Windows, you are not physically watching the command window, so how do you know the output of the Oracle RMAN commands? To capture the output, you can use the log parameter in the Oracle RMAN command line:
rman cmdfile=backup_ts_users.rman log=backup_ts_users.log
Stored Scripts – although command files work pretty well in most cases, they have one huge drawback – a command file should be available on the server where the Oracle RMAN backup is to be run. Otherwise, from within the command file, you have to connect from the Oracle RMAN client to the server by using a connect string:
connect target sys/oracle123@remotedb
There are several problems with this setup. First, the modified command file needs to store the password of SYS or some other user with SYSDBA privilege. In a security-conscious environment that may not be acceptable. Second, the Oracle RMAN client may not be compatible with the Oracle Database release. Finally, for performance reasons, you may very well want to run the Oracle RMAN client on the same server as the database itself. But what if you have databases on different servers? You will have to replicate a command file script to all servers. And when you modify the script you will have to make sure it is copied to all those servers again.
The solution use Oracle RMAN stored scripts, you can create scripts that are stored inside the Oracle RMAN catalog and not on the server itself. Because it is stored inside the Oracle RMAN catalog, you will need to connect to the catalog first, as shown below. To execute this script, al you have to do is call it with the execute command from the Oracle RMAN prompt:
RMAN> run { execute script backup_ts_users; }
Stored script for backing up USERS tablespace:
rman
RMAN> connect target /
RMAN> connect catalog rman/secretpass@rmancat
RMAN> create script backup_ts_users
command ‘Tablespace USERS Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace users;
}
The backup_ts_users stored script created above is available only to the target database to which it is currently connected. It is a local stored script, and you can’t execute a local script for one database in another. To execute a script in multiple databases, create a global stored script by using the keyword GLOBAL between CREATE and SCRIPT. For instance, to create a global script for the one above, replace:
create script backup_ts_users
with
create global script backup_ts_users
Once created, this global stored script can be executed in any database connected to this catalog. If you need to modify the script, there is no need to copy it to all servers or databases; it’s automatically available for execution to all databases connecting to the catalog.
If the global stored script already exists and you want to update it, replace CREATE with REPLACE:
replace global script backup_ts_users
- and include the updated script text.
Parameterization: This backup_ts_users stored script has a very specific purpose – backing up the USERS tablespace. What if you want to backup a different tablespace? Rather than creating multiple scripts, you can create a generic stored script to backup any tablespace.
A stored script merely asks for a value; it does not store the value you used in the script itself:
RMAN> create script backup_ts_any
comment ‘Any Tablespace Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace &1;
}
With the parameterized stored script created, pass the value of the parameter via a USING clause. For example, to backup the SYSTEM tablespace by using this backup_ts_any stored script, use the following Oracle RMAN command:
run { execute script
backup_ts_any using ‘SYSTEM’; }
To display the list of stored scripts:
RMAN> list script names;
RMAN> list global script names;
RMAN> print global script backup_ts_level1_any;
RMAN> delete global script backup_ts_level1_any;
RMAN> create script backup_ts_users
from file ‘backup_ts_users.rman’;
RMAN> print script backup_ts_users to file ‘backup_ts_users.rman’;
Creation of Demo and System PeopleSoft Databases
Labels:
PeopleSoft
A PeopleSoft Demo database contains all the data of the System database (tools and application) and demo data needed for testing or demonstration.
If you create the database manually, without using the Database Configuration Wizard, one of the main steps is to create the Data Mover script to import the system/demo data from the *.db files.
After you log in Data Mover (Bootstrap mode) and open the menu File > Database Setup, here are some things to consider:
1. In the second panel of the wizard, in the Database Type frame, you have to select what kind of application to create:
- choose Demo to have your core application along with demo data
- choose System if you don’t need the demo application data
2. Add the PeopleSoft application you want to create along with all the languages you need, no matter if you create a Demo or a System environment.
3. DO NOT ADD the PeopleTools System Database. This is used only if you want to create from scratch a custom application outside the delivered PeopleSoft applications.
4. When you finish this wizard, Data Mover will create a DMS file that will import data from the PS_HOME\data\*.db files according to your previous selections.
According to the PeopleSoft application, the .db files are named as follows:
hcengs.db, hcengl.db, etc. for HRMS
crengs.db, crengl.db, etc. for CRM
epengs.db, epengl.db, etc. for FSCM
lmengs.db, lmengl.db, etc. for ELM
etc.
Replace further xx with hc, cr, ep, lm, etc. according to your application.
The xxengs.db file will create all the Application and PeopleTools tables, and will import the system data. This file needs to be present in the dms script for both the Demo and System environments.
The xxengl.db file will import Application sample data in existing tables. This file needs to be present only in the script for the Demo environment.
The xxfraa.db, xxitaa.db, xxduta.db, etc. files will import language related data in existing tables. These files are needed for both the Demo and System environments.
The ptengs.db is used ONLY if you want to create from scratch a custom application outside the delivered PeopleSoft applications.
5. The logs of the Data Mover import will be found in the path set in Configuration Manager > Profile > Default > Edit > Common > Log Directory. They are in the format xxengs.log, xxengl.log, xxfraa.log, etc. and can be open to see what data has been imported from every *.db file.
If you create the database manually, without using the Database Configuration Wizard, one of the main steps is to create the Data Mover script to import the system/demo data from the *.db files.
After you log in Data Mover (Bootstrap mode) and open the menu File > Database Setup, here are some things to consider:
1. In the second panel of the wizard, in the Database Type frame, you have to select what kind of application to create:
- choose Demo to have your core application along with demo data
- choose System if you don’t need the demo application data
2. Add the PeopleSoft application you want to create along with all the languages you need, no matter if you create a Demo or a System environment.
3. DO NOT ADD the PeopleTools System Database. This is used only if you want to create from scratch a custom application outside the delivered PeopleSoft applications.
4. When you finish this wizard, Data Mover will create a DMS file that will import data from the PS_HOME\data\*.db files according to your previous selections.
According to the PeopleSoft application, the .db files are named as follows:
hcengs.db, hcengl.db, etc. for HRMS
crengs.db, crengl.db, etc. for CRM
epengs.db, epengl.db, etc. for FSCM
lmengs.db, lmengl.db, etc. for ELM
etc.
Replace further xx with hc, cr, ep, lm, etc. according to your application.
The xxengs.db file will create all the Application and PeopleTools tables, and will import the system data. This file needs to be present in the dms script for both the Demo and System environments.
The xxengl.db file will import Application sample data in existing tables. This file needs to be present only in the script for the Demo environment.
The xxfraa.db, xxitaa.db, xxduta.db, etc. files will import language related data in existing tables. These files are needed for both the Demo and System environments.
The ptengs.db is used ONLY if you want to create from scratch a custom application outside the delivered PeopleSoft applications.
5. The logs of the Data Mover import will be found in the path set in Configuration Manager > Profile > Default > Edit > Common > Log Directory. They are in the format xxengs.log, xxengl.log, xxfraa.log, etc. and can be open to see what data has been imported from every *.db file.
Subscribe to:
Posts (Atom)