Oracle Apps SYSADMIN Concurrent Requests

Purpose: Purpose of this blog entry is to list SYSADMIN Related concurrent requests. In this document, generic instructions and parameters are provided, which needs to be reviewed and decided based on particular customer needs.

Oracle SYSADMIN related Concurrent Requests:

1) Gather Schema Statistics

 Schedule: Every Weekend (Preferably on every Sat 6:00 pm server time). If this is not sufficient (if performance is not good), it can be done 2/3 times a week.

OR, Weekend for ALL schemas and 2/3 times during the week for only few important schemas where in there is heavy insert / update / delete happening.

Parameters:

Schema name: ALL

Parallel Worker: Number of CPUs + 2

Estimate Percent: 30

Other parameters: Default

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

2) Purge Concurrent Request and/or Manager Data

Schedule: Every day, nighttime, say 11:00 PM Server time

Parameters:

Age=30 (Purge concurrent request data older than 30 days)

Other parameters: Default

Note: Age parameter needs to be agreed with Business / Customer.

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

3) Purge Concurrent Request and/or Manager Data

 Schedule: Every day night time, say 11:30 Server time

Parameters:

Count: 5

Program Application: Application Object Library

Program: Workflow Background Process

Other parameters: Default

Note: Count Parameter needs to be agreed with Business / Customer.

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

4) Workflow Background Process

 Schedule: Every 10 min

Parameters:

Process Deferred: Yes

Process Timeout: No

How to submit: Login to Applications using SYSADMIN account, then navigate to Oracle Applications Manager responsibility, then navigate to Workflow Manager, then use “Submit Request for “ facility (top – right hand side) (Select “Background Engines” from drop down)

5) Workflow Background Process

 Schedule: Every 60 min

Parameters:

Process Deferred Yes

Process Timeout: Yes

How to submit: Login to Applications using SYSADMIN account, then navigate to Oracle Applications Manager responsibility, then navigate to Workflow Manager, then use “Submit Request for “ facility (top – right hand side) (Select “Background Engines” from drop down)

6) Workflow Control Queue Cleanup

 Schedule: Every day, night time / early morning hours

Parameters: Default

How to submit: Login to Applications using SYSADMIN account, then navigate to Oracle Applications Manager responsibility, then navigate to Workflow Manager, then use “Submit Request for “ facility (top – right hand side) (Select “Control Queue Cleanup” from drop down)

7) Purge Obsolete Workflow Runtime Data

 Schedule: Every weekend

Parameters:

Age: 30

Other parameters: Default

Note: Age parameter needs to be agreed with Business / Customer.

How to submit: Login to Applications using SYSADMIN account, then navigate to Oracle Applications Manager responsibility, then navigate to Workflow Manager, then use “Submit Request for “ facility (top – right hand side) (Select “Purge” from drop down)

8) Synchronize WF LOCAL tables

 Schedule: Every day, night time / early morning hours

Parameters: Default

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run


Note: As per metalink id 1158212.1, after E-business version 11.5.10 this request generally does not need to be run.

9) OAM Applications Dashboard Collection

 Schedule: Every 30 min

Parameters: Default

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

10) Purge Signon Audit data

 Schedule: Can no schedule needs to be run manually, say every week Monday / Friday morning.

Parameters: Date (By default system date)

Note: Most of customer wants this date to be, 2 to 3 months old

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

Note for DBA’s: There will be check box “Increment data parameter for each run” which must be checked.

11) Purge Debug Log

 Schedule: Every day / weekend.

Parameters: Date (By default system date)

Note: Most of customer wants this date to be, 2 weeks to 3 month old

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

Note for DBA’s: There will be check box “Increment data parameter for each run” which must be checked.

12) Resend Failed/Error Workflow Notifications

 Schedule: Run every 6 hours.

Parameters: ERROR / FAILED (One request for each parameter)

How to submit: Login to Applications using SYSADMIN account, navigate to System Administrator responsibility, then navigate to Requests > Run

Note for DBA’s: As this request takes date as parameter, it cannot be scheduled easily. You may have to run this physically on one particular day of week, say Monday morning.

DB Jobs

Purge old snapshots from PREFSTAT schema

Other relevant document’s.

732713.1 Purging Strategy for eBusiness Suite 11i

298550.1 Troubleshooting Workflow Data Growth Issues

Apps and Database review areas / points

Oracle Database Server Review Points / Areas

Initialization & listener parameter
AWR, Alert.log, listener log, OS watcher, RDA
Invalid Objects, Indexes and fragmentation
Tablespaces, Data files, log files and control files
Custom objects in SYSTEM tablespace & SYSTEM tablespace as default tablespace
Stats job schedule
Chained rows
Workload balancing/distribution in clustered environments
Database Patch level, de-support, and patching strategy (CPU, one off)
Server disk space for DB growth, Archive log, backup destination
Server level pre-req’s, errors, warnings & background jobs
Database Backup and Recovery
Database Monitoring and alerting system
Database Disaster Recovery solution
Debugging latch contention, hangs, crashes & locking issues

Oracle Applications Infrastructure Review (eBS) Points / Areas

Database review as per earlier slide
Application Technical Architecture
Application Backup and Recovery
Application Security, Audit, and security profile options
Standard Manager programs and it’s parameters
Application Monitoring and alerting system
Application Disaster Recovery solution
Application Patch level, de-support, and patching strategy
Network (Latency and Bandwidth)
JVM’s
JDBC connection parameters
Forms & Reports server
Standard Concurrent Manager
Recommendation on best practices for routine administrative tasks etc.

Quick DBA update on Oralce Applications R12 install, upgrade and admin scripts..

Hi to all,
In this blog I will discuss some of the main points which an Apps dba should know about Install, Upgrade and admin scripts. So lets begin with Installer

Main points about Installer:
1) config.txt is now configSID.txt , for adding node, you can use configSID.txt or get details from database directly using host.domain:port:sid format
2) Install types: Standard and Express
3) Shared APPL_TOP, COMN_TOP and tech stack as well, but not for Windows
4) Easy load balancing of CP and Web communications
5) Technology Stack Components : Oracle 10g R2 Database home, Oracle Developer 10i (forms, reports) and Oracle 10g Application Server 10.1.2 (http server)
6) Java Development Kit (JDK) 5.0 is automatically installed by Rapid Install
7) Disk Space : Applications node 28 GB , Fresh DB 45 GB, Vision DB 133 GB, Stage for fresh install 33 GB, TEMP 500 Mb
8) Create Stage : CD's are in DVD Format, and run adautostg.pl to create dir structure, which requires perl 5.0053 in PATH, and creates subdirectories startCD, oraApps, oraDB, oraAS, and oraAppDB under stage12
9) Want to install on virtual hostname, use -servername as command line parameter with rapidwiz. There are 2 more command line parameters, -restart to restart any failed install, and -techstack to install only technology stack.
10) Incase of multiuser installation, start installer using root account
11) For additional language, you must use OAM (oracle applications manager)
12) There is new concept of INST_TOP which mainly stores instance specific files including runtime files, log files and configuration files
13) In R12 there are Services concept instead of nodes (forms/web/concurrent). Following is the list of services in R12 :
* Root Service Group which supports • Oracle Process Manager (OPMN)
* Web Entry Point Services which supports • HTTP Server
* Web Application Services which supports • OACORE OC4J • Forms OC4J • OAFM OC4J
Batch Processing Services which supports • Applications TNS Listener • Concurrent Managers • Fulfillment Server
Other Service Group which supports • Oracle Forms Services • Oracle MWA Service
* : Thses services must be installed on same / one machine (which is nothing but Web node, according to 11i )
14) Regardless of type of services confugured on perticular server, all files (forms, reports, jsp) are stored in APPL_TOP (Unified), basically to have pure 3 tier arch
15) Installer gives option to configure OCM (Oracle configuration manager) where in OCM keeps track of key Oracle and OS stats. This collected data is sent to oracle support via https for better understanding of issues and quick resulations to any issues reported

Main points about Upgrade:
1) You can only upgrade to R12 from 11i, if you are at older version (like 10.7 or 11.0.3 etc), you must upgrade first to 11i, and then upgrade to R12
2) High level R12 Upgrade process :
• Run rapid installer first time to layout new file structure and tech stack
• Migrate or Upgrade database to 10g R2
• Run Autopatch to run database driver to bring DB to R12 level
• Run rapid installer second time to configure and start services

Admin Scripts:

adautocfg.sh - run autoconfig
adstpall.sh - stop all services
adstrtal.sh - start all services
adapcctl.sh - start/stop/status Apache only
adformsctl.sh - start/stop/status OC4J Forms
adformsrvctl.sh - start/stop/status Forms server in socket mode
adoacorectl.sh - start/stop/status OC4J oacore
adoafmctl.sh - start/stpp/status OC4J oafm
adopmnctl.sh - start/stop/status opmn
adalnctl.sh - start/stop RPC listeners (FNDFS/FNDSM)
adcmctl.sh - start/stop Concurrent Manager
gsmstart.sh - start/stop FNDSM
jtffmctl.sh - start/stop Fulfillment Server
adpreclone.pl - Cloning preparation script
adexecsql.pl - Execute sql scripts that update the profiles in an AutoConfig run
java.sh - Call java executable with additional args, (used by opmn, Conc. Mgr)

Note: To understand this page, you should have prior knowledge or background of APPS 11i

Oracle APPS R12 File system structure

In this blog I will show you the new R-12 file system tree structure.

As you can see in above tree structure, we have db, apps and inst as top level dir's

Under DB we have tech_st which is DB home and apps_st which holds DB datafiles.

Under apps, again we have tech_st which holds 1012 and 1013 homes, and apps_st holds APPL_TOP and OAD_TOP.

And finally inst which mainly stores instance specific files including runtime files, log files and configuration files

Note: If image is not been displayed fully, you can save the image to your desktop and open locally to see the full size image.

Using OEM, want to know database growth pattern of last one month/year ?

Here are the steps to know Database growth pattern for last one month/year using OEM

1) Login to OEM and Click on the Reports Tab
2) Navigate to Reports-->Storage-->Oracle Database Space Usage path and Click on Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for last one Month.
4) Also we can get one year Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports-->Storage-->Oracle Database Space Usage path and click on Oracle Database Tablespace Monthly Space Usage link.

I hope this helps DBA's to plan for future disk space requirements.

Using OEM, want to see the list of tables to reclaim some space in a database and chained rows?

Steps to find Object Fragmentation and Chained Rows in a Database is:

1) Login to OEM and Click on Targets tab and Choose Database sub tab.
2) Click on required database and follow the path under Space summary >Segment findings.
3) Click Configure tab on right side. Provide Reclaimable space(%) ,Reclaimable Space(MB), Row Chaining (%) and Click on add button and select Tablespaces to be checked for row chaining.

For Row Chaining, the navigation path is
Reports->Storage->Oracle Database Space Issues->Segments with Row Chaining Issues

For Objects Fragmentation, the navigation path is
Reports->Storage->Oracle Database Space Issues->Tablespaces with Reclaimable Space

Oracle Apps R12 Self Study quick guide

Are you an Apps 11i DBA and want to learn R12?
Here are some of the things you can try out your self and get confidence of working on R12

* Install R12.5 (HRMS + FIN)
* Languages: US and F
* Setup Workflow mailer
* Upgrade Discoverer version
* Apply latest CPU patches
* Apply latest High Priority patches
* Upgrade to latest certified DB version
* Install XX_TOP
* Custom Standard manager for SYSADMIN requests
* Configure BPEL
* Configure Database Data guard (Optional)
* Configure R12 Monitoring through OEM
* Configure printer
* Configure iRecruitment (concept only, using virtual servers, host entries etc)
* Configure DBVault
* Configure UPK

These steps are not in sequence, so you have to apply some logic before you do it.
Also you have to search Metalink / OTN / other sites to get the details steps for one major step.
And, lastly, dont forget to take backup before any major change you making.. :-)

After doing all this, I am sure you can support R12 with confidence..