Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :-

Thursday, May 28, 2009

Statspack Snapshot DBMS Job

Expressing Time with Fractions - (Date Arithmetic)


1. Overview
2. Job Intervals and Date Arithmetic
3. Job Intervals Examples
4. Oracle Date Math Examples
5. Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval
6. Run Statspack Snapshot Every 15 Minutes Starting at the Next 15 Minute Interval
7. Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval
8. Run Statspack Snapshot Every 1 Hour
9. DBMS_JOB / Every 15 Minutes from Monday to Friday, Between 6 a.m. and 6 p.m.
10. Execute a Job Every xy Hours Starting at an Arbitrary Start Time


The following article provides a cheat sheet for handling date/time math in Oracle. One of the core issues to keep in mind is that Oracle date math is based on a day.

Job Intervals and Date Arithmetic

Job execution intervals are determined by the date expression set by the interval parameter. One key to setting the interval correctly is determine which of the following applies to the job:

  1. Each execution of the job should follow the last by a specific time interval.
  2. The job should execute on specific dates and times.

Type 1 Jobs

Jobs of type 1 usually have relatively simple date arithmetic expressions of the type SYSDATE+N, where N represents the time interval expressed in days. The following table provides examples of these types of intervals.

Action Interval Time
Execute daily 'SYSDATE + 1'
Execute every 4 hours 'SYSDATE + 4/24'
Execute every 10 minutes 'SYSDATE + 10/1440'
Execute every 30 seconds 'SYSDATE + 30/86400'
Execute every 7 days 'SYSDATE + 7'
Do no re-execute and remove job NULL

NOTE: Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with in interval of 'SYSTEM + 1', it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. the next day using DBMS_JOB.RUN, then it will be rescheduled for execution at 4:00 p.m. the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case, the job will run as soon as it can, but the execution time will have migrated away from the original submission time due to the later execution. This "drift" in next execution times is characteristic of jobs with simple interval expressions.

Type 2 Jobs

Jobs with type 2 execution requirements involve more complex interval date expressions, as see in the following table.

Action Interval Time
Every day at 12:00 midnight TRUNC(SYSDATE + 1)
Every day at 8:00 p.m. TRUNC(SYSDATE + 1) + 20/24
Every Tuesday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24
First day of the month at midnight TRUNC(LAST_DAY(SYSDATE) + 1)
Last day of the quarter at 11:00 p.m. TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24
Every Monday, Wednesday and Friday at 9:00 p.m. TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 21/24

Job Intervals Examples

Remember that the dbms_job.submit() procedure accepts three parameters:

job => :jobno
, what => 'statspack.snap;' -- What to run
, next_date => TRUNC(sysdate+1/24,'HH') -- Start next hour
, interval => 'TRUNC(SYSDATE+1/24,''HH'')' -- Run every hour

--  =========================================================
-- Schedule a SNAPSHOT to be run on this instance every hour
-- =========================================================



select instance_number into :instno from v$instance;

-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour.
-- ------------------------------------------------------------
, 'statspack.snap;'
, TRUNC(sysdate+1)+6/24
, 'TRUNC(SYSDATE+1/24,''HH'')'
, :instno);

-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
, 'statspack.snap;'
, TRUNC(sysdate+1)+9/24
, 'TRUNC(SYSDATE+12/24,''HH'')'
, :instno);

-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
, 'statspack.snap;'
, TRUNC(sysdate+1)+6/24
, 'TRUNC(sysdate+10/1440,''MI'')'
, :instno);

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
, 'statspack.snap;'
, TRUNC(sysdate+1)+6/24
, NEXT_DAY(sysdate,''TUESDAY'')
, NEXT_DAY(sysdate,''WEDNESDAY'')
, NEXT_DAY(sysdate,''THURSDAY'')
, NEXT_DAY(sysdate,''FRIDAY'')
) + 1/24
, ''HH'')'
, :instno);



Oracle Date Math Examples

Date / Time Fraction Description
WHERE (date) > sysdate - 6/24; Past 6 hours. (or 1/4 of a day ago)
WHERE (date) > sysdate - 6; Past six days
WHERE (date) > sysdate - 6/1440; Past six minutes
6 hours
One second
Five seconds
One minute
Five minutes
One hour
Five hours
TRUNC(SYSDATE+1/24,'HH') Every one hour starting with the next hour

Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval

variable jobno number;
variable instno number;
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/5)+1)*5)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/5)+1)*5)/(24*60)', TRUE, :instno);

Run Statspack Snapshot Every 15 Minutes Starting at the Next 15 Minute Interval

variable jobno number;
variable instno number;
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/15)+1)*15)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/15)+1)*15)/(24*60)', TRUE, :instno);

Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval

variable jobno number;
variable instno number;
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, :instno);

Run Statspack Snapshot Every 1 Hour

variable jobno number;
variable instno number;
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);

DBMS_JOB / Every 15 Minutes from Monday to Friday, Between 6 a.m. and 6 p.m.

If the database is Oracle8i (Release 2) or higher, you can simply use the CASE statement. The following CASE statement returns the correct interval for the above specification:
SQL> ALTER SESSION SET nls_date_format = '(DY) MON DD, YYYY HH24:MI';
Session altered.

THEN TRUNC(sysdate) +
WHEN (TO_CHAR(sysdate, 'DY') NOT IN ('FRI','SAT','SUN'))
THEN TRUNC(sysdate)+1+6/24
ELSE next_day(trunc(sysdate), 'Mon') + 6/24
END interval_date
FROM dual;


sun sep 15, 2002 16:35
mon sep 16, 2002 06:00
The CASE statement gives you great flexibility in generating a complex value such as you need. Unfortunately, DBMS_JOB will allow you to use only an interval that is 200 characters or less, and even if you "scrunch up" the CASE statement above, you'll find it is about 300 characters minimally. So, you cannot use it directly in the call to DBMS_JOB. My solution to that is one of two things: either I would create a view NEXT_DATE as that select, so that select * from next_date would return the next time the job runs, or I would wrap the above query in a PL/SQL function that returns a date. If I used a view, my call to DBMS_JOB might look like:
( :n, 'proc;', sysdate,
'(select * from next_date)'
Or, if I used the PL/SQL function approach and created a function NEXT_DATE, it could be:
( :n, 'proc;', sysdate,

Execute a Job Every xy Hours Starting at an Arbitrary Start Time

The following solution comes thanks to Martin Trappel <>.

This solution combines an arbitrary start date with an arbitrary time-interval. The next execution dates will be exactly n times the interval from the starting date.


job => X
, what => '
-- do stuff ...
, next_date => :some_date
, interval => 'GET_NEXT_JOB_DATE(''my_job'', 2.25)'
, no_parse => FALSE


, p_interval_hours NUMBER) RETURN DATE IS
x_old_next_date DATE;
x_new_next_date DATE;
SELECT next_date
INTO x_old_next_date
FROM user_jobs
WHERE LOWER(INTERVAL) LIKE '%'||p_jobid||'%';

IF (x_old_next_date IS NULL) THEN
x_old_next_date := SYSDATE;

x_new_next_date := x_old_next_date;
WHILE(x_new_next_date <= sysdate) LOOP x_new_next_date := x_new_next_date + p_interval_hours / 24; END LOOP; RETURN x_new_next_date; END GET_NEXT_JOB_DATE;


Happy Learning!

Wednesday, May 20, 2009

Oracle Application Server 10g- FAQ's

Oracle Process Manager and Notification Server

Frequently Asked Questions

This FAQ addresses frequently asked questions relating to the Oracle Application Server 10g Release 3 ( version of Oracle Process Manager and Notification Server (OPMN) and is divided into the following sections:

Topology and DRM

1.0 General

1.1 What is OPMN?
Oracle Process Manager and Notification Server (OPMN) is installed and configured with every Oracle Application Server installation type and is essential for running Oracle Application Server. OPMN provides an integrated way to manage all Oracle Application Server components. OPMN consists of two main pieces the process manager and the notification server.
• Process manager is the centralized process management mechanism in Oracle Application Server and is used to manage all Oracle Application Server processes. The PM starts, restarts, stops, and monitors every process it manages. It also performs death-detection and automatic restart of the processes.
• Oracle Notification Server (ONS) is the transport mechanism for failure, recovery, startup, and other related notifications between components in Oracle Application Server.

1.2 What are some of the new features in OPMN?
OPMN has following new features:
• Infrastructure
o Single Configuration File: The information that was stored in ons.conf and dcm.conf is now configured within opmn.xml
o Logging Mechanism: Standard and debug messages are located in opmn.log, opmn.dbg and opmn.out files.
• Notification Server
o IPv6 Support: ONS now supports the IPv6 network stack.
o Dynamic Discovery: Connection topology is dynamically managed.
• Process Management
o Applications Support: OPMN can now manage J2EE applications.
o Dynamic Resource Management: Capability to describe a set of desired behaviors and take actions to achieve the desired results
o Service Fail-over: Mechanism to specify a critical single (or limited number) process that must run somewhere in the cluster
o Sequential Requests: Option to perform the request upon one process at a time
o Progressive Request Report: Way to report the result of each part of an OPMN request as it completes
For details refer to the Oracle Process Manager and Notification Server Administrator’s Guide 10g Release 3 (

1.3 What algorithm does OPMN use to determine when to restart the process?
The OPMN logic for restarting a managed process (assuming no user requests to stop or restart the process) is a three pronged approach.
1. OS Process Check: Every 2 seconds OPMN queries the OS with the managed process id to see if it has terminated.
2. Forward Ping: Periodically, 20 seconds by default, OPMN sends a ping message to the managed process and expects a result within 20 seconds.
3. Reverse Ping: Every 20 seconds managed process sends OPMN a ping notification.
If check #1 fails OPMN always attempts to restart the managed process.
For check #2 and #3, if OPMN does not get a forward ping response, it flags the managed process as “DEAD” (i.e. unresponsive/unreachable). OPMN will continue to try and ping this "DEAD" process for max retry times. Max retry is the value of either of following two data elements:
• reverseping-failed-ping-limit : when reverse pings are being received, OR
• no-reverseping-failed-ping-limit : when reverse pings are also not being received (within the timeout period specified by reverseping-timeout data element).
If any forward ping succeeds, the process state is set back to "ALIVE" (and the internal count towards max retry is set back to 0). If the forward ping fails consecutively for max retry times, then OPMN will attempt to stop and restart the process.

1.4 Can OPMN manage J2EE applications?
Yes. J2EE applications are supported using the OPMN process management mechanism. There are automatic application state and metric updates. You can start, stop, restart, and check the status of your J2EE application using opmnctl commands. This allows much finer grained control for performing operations such as application upgrades or resetting of unresponsive applications.

2.0 Logging

2.1 I don’t see ipm.log and ons.log files anymore. Where do OPMN standard and debug messages go?
OPMN and OPMN-managed processes generate log files during processing to enable you to troubleshoot difficulties you might have in execution of the process. In prior releases, standard and debug messages were located in either the ipm.log or ons.log files.
Standard and debug messages are located in the following files:
• opmn.log: contains standard international log messages, all standard OPMN log messages and messages for ONS and Oracle Process Manager (PM).
• opmn.dbg: contains OPMN debug log messages (English only) for ONS and PM.
• opmn.out: OPMN console log messages (stdout and stderr).

2.2 There used to be a level attribute in the tag that I don’t find anymore. How do I define log levels?
In, logging is configured by component codes rather than level codes. The logging messages contain the literal value based on logging levels rather than an integer value; for example: none, fatal, error, warn, notify, debug1, debug2, debug3, and debug4.
All OPMN log messages of non-debug type (none, fatal, error, warn and notify) for the ONS and PM components go in opmn.log file. If debugging is enabled, then all OPMN debug log messages (of type debug1, debug2, debug3 and debug4) are written into opmn.dbg file.
You can configure the following component codes for logged events for both log and debug:
• internal: a log for the common internal information for OPMN
• ons: a log for the ONS component information for OPMN
• pm: a log for the PM component information for OPMN
Both the ons and pm components consist of subcomponents that can also be configured. Refer to the Oracle Process Manager and Notification Server Administrator’s Guide 10g Release 3 ( for the list of ons and pm subcomponents.

2.3 Can OPMN rotate its log files?
Yes. OPMN can rotate its log (opmn.log) and debug (opmn.dbg) files based on size, time or both. You can enable rotation by configuring rotation-size and rotation-hour attributes of and tags. When the log file reaches the configured size or at the given hour of the day, the OPMN logging mechanism will close the file, rename it with a time stamp suffix, and then create a new log/debug file.
OPMN console log file (opmn.out) is not rotated as this is expected to be a very small file in size. Once OPMN gets past a certain point in initialization, it never writes any output to opmn.out anymore. So there is only a small set of messages that could ever appear in this file.

2.4 Can OPMN rotate managed process’s console log file?
The console log for OPMN managed process (e.g. $OH/opmn/logs/HTTP_Server~1 file for OHS) is simply the raw stdout and stderr output from the process. OPMN creates a file for each managed process and writes a simple time and event stamp for start, stop and restart before creating the processes and handing the file descriptor to the operating system to use for the managed process's stdout and stderr.
At process startup, before handing off an existing console log file to a managed process, OPMN checks the size against a configured limit (rotation-size attribute of tag), and if the file size exceeds the limit, it will rename the existing file to include a time stamp, and then creates a new file for the managed process. If the rotation-size attribute is not configured, OPMN will not be able rotate the process’s console log file.
OC4J also provides a mechanism to manage its stdout/stderr log files, refer to chapter 4 ‘OC4J Runtime Configuration’ of Oracle Container for J2EE Configuration and Administration Guide 10g Release 3 ( for more details.

3.0 Topology and DRM

3.1 What is dynamic discovery of ONS topology?
In prior releases, each OPMN instance had to be configured with the host and port values of the other ONS servers that it communicated with. This list was maintained in the ons.conf file that was maintained by DCM. Whenever this file changed, restarting OPMN was necessary to reflect the change.
OPMN can now optionally discover other ONS servers dynamically. Instead of configuring a list of all other servers to connect to, a discovery mechanism consisting of a multicast address or list of discovery servers is used by OPMN. ONS uses the discovery mechanism to announce new servers and join them into the ONS topology dynamically. This reduces the amount of configuration necessary for each Oracle Application Server instance, eliminates the need to restart OPMN when the topology changes, and removes configuration changes when the topology changes. Explicitly configuration of all nodes is still supported if desired.

3.2 In prior releases there used to be a file called ons.conf that I don’t see anymore. Where is topological information defined?
The information that was stored in ons.conf is now configured within the topology section under the element in opmn.xml. The “nodes” list value is the same as what was specified in ons.conf, and there are two elements - the discover and gateway. Also, OPMN used to extract the instance id, instance name, cluster id and cluster name values from dcm.conf (in the dcm/config directory), but these values are now specified directly as attributes to the element.

3.3 What is Dynamic Resource Management (DRM)?
Dynamic Resource Management (DRM) is a new OPMN capability designed to describe a set of desired behaviors and take actions to achieve the desired results. DRM functionality provides a way for you to customize the management of your processes through configuration changes only. The DRM enables you to have process management commands issued based on system conditions according to a set of user-configured directives. Some DRM examples are:
• Start an additional OC4J process every day at 5 p.m. to accommodate peak usage hours.
• Restart an OC4J process whenever it’s heap usage grows beyond 500 MBs.
• Spawn an additional OC4J process when average response time exceeds 500 milliseconds as long as there are less than 4 processes running.
DRM functionality is currently available within a single application server instance. Local information is used to make local decisions leading to local actions. The scope of DRM will be expanded to the entire cluster in the next major release of Oracle Application Server.

3.4 What is a Resource Management Directive (RMD)?
Resource Management Directives are configured in opmn.xml. RMD tells DRM when and what to do. Each RMD has a single conditional followed by a list of actions, which are executed in the order in which they are configured. If any of the actions fail, their execution is stopped, and the exceptions (if any) are executed in the order in which they are configured – if an exception fails then exception processing is stopped.

3.5 What kind of conditional can be defined in a RMD?
The conditional that can be used include:
• Keywords to reference any or specific ias-components, process-types, process-sets, processes or applications.
• Any DMS metric maintained by OPMN.
• Time of the day
• Internal events (such as instance joins topology)
• Any logical (AND/OR) combination of the above checks

4.0 Configuration

4.1 In opmn.xml, enclosed within the notification-server tags, there is a port tag that has local, remote and request attributes. What are these for?
The local attribute specifies a local port for notification traffic within the Oracle Application Server Instance. This port can also handle administrative requests like starting and stopping the Oracle Application Server Instance. It cannot handle SSL and is instead restricted to the localhost TCP interface for security reasons. The remote port is for inter Oracle Application Server Instance notification traffic. It is also capable of handling administrative requests and supports SSL. The request port is to send requests for certain OPMN data dumps. It cannot handle any administrative commands and does not support SSL.

4.2 How can I send environment variables to managed processes like OHS and OC4J from OPMN?
Within the OHS and OC4J tags, you need to add directives such as the following:

4.3 What is routing ID?
The routing ID specifies a routing relationship between OC4Js and OHSs. In other words, an OHS routes to every OC4J that it shares a routing ID with. Every OC4J is assigned a routing ID, similarly each OHS is assigned one or more routing IDs to route to.
OPMN passes the routing ID to OC4J as a system property and to OHS as an environment variable when these are started. OC4J adds this routing ID to the ONS notifications it publishes. OHS listens for notifications from OC4J. When an OHS sees the first notification from an OC4J containing a routing ID on its list, it begins routing to it.
The addition of routing IDs and mount point discovery in Oracle Application Server 10g Release 3 ( version of OHS allows mod_OC4J to dynamically discover all aspects of OC4J routing.

4.4 Should I configure routing ID for OHS in both mod_oc4j.conf and opmn.xml files?
No. Out of the box, OHS is configured to pick up its routing ID from opmn.xml file. Though it is possible to configure routing IDs for OHS in both opmn.xml and directly in mod_oc4j.conf file, but if OHS is configured with routing-id in both places, it considers it an error and fails to start. So routing IDs for OHS should either be configured in opmn.xml (specified as module data under element or under element of OHS) or mod_oc4j.conf but not at both places.

4.5 I am seeing the following error message in opmn.log file. How can I correct this?
"oc4j register failed. register_proc - required port missing for OC4J proc"
From the error message, it seems that OC4J failed to bind to one of its ports (ajp etc). Please make sure that valid available ports have been specified. Also, refer the troubleshooting chapter in Oracle Process Manager and Notification Server Administrator’s Guide 10g Release 3 ( for details on how to troubleshoot OPMN related errors.

4.6 Where can I find details of various OPMN commands?
Refer to chapter 4 ‘opmnctl Commands’ of Oracle Process Manager and Notification Server Administrator’s Guide 10g Release 3 ( for details of various opmnctl commands.
You can also use ‘opmnctl usage’ command to get the detail usage message of all commands supported by OPMN.

Oracle Application Server 10g R3 (
Oracle Process Manager and Notification Server FAQ

Happy Learning !

Sunday, May 17, 2009

In-Depth: Load-Balancing E-Business Suite Environments

As I watched blood drain from my arm, my thoughts turned to load-balancing and system redundancy. My phlebotomist had just cheerfully informed me the lab's primary testing PC had failed that morning, so there would be an unusual delay in getting blood test results until a replacement arrived.

Increasing Fault Tolerance at Lower Cost

You can use load-balancing routers (LBRs) to protect your E-Business Suite from similar types of system failures. Load-balancers increase your environment's fault-tolerance and scalability by distributing load across a pool of application servers like this:

Generic Apps Load-balancing:

Besides fault-tolerance and scalability, another appealing benefit is that you can use load-balancing to substitute expensive SMP boxes with clusters of inexpensive Linux-based commodity servers.

Linux Load-Balancing on Oracle's Global Single Instance

In fact, this is what we've done at Oracle: our own E-Business Suite environment is a Global Single Instance running on about 58 Linux-based application servers. This has reportedly saved us millions in operating costs.

If you're joining us at this year's OpenWorld conference, make sure you attend Bret Fuller's ever-popular session on how we run the E-Business Suite internally; some of the statistics he shows on our transactional volumes are mind-blowing.

Supported Load-Balancing Methods

The E-Business Suite supports the following types of load-balancing:
I'll cover only the first two methods in this article.

HTTP Layer Load-Balancing

HTTP Layer load-balancing is the most common method used in E-Business Suite environments.

HTTP Layer Load-Balancing:

In this configuration, end-users navigate to a specific Web Entry Point that represents your E-Business Suite's domain name. An HTTP Layer load-balancer routes all subsequent traffic for a specific user to a specific Web Node.

HTTP Layer load-balancers may use heartbeat checks for node death detection and restart, and sophisticated algorithms for load-balancing.

DNS-Based Load-Balancing

When an end-user's browser attempts to access your E-Business Suite environment, your local Domain Name Server (DNS) can direct that user to a specific application server in a pool based on available capacity:

DNS-Based Load-balancing:

Traffic for that user's session will be handled by the application server, while other users' traffic may be directed to other application servers in the pool. Like HTTP layer load-balancers, many DNS-based load-balancers use heartbeat checks against nodes and sophisticated algorithms for load-balancing.

Business Continuity ("Disaster Recovery")

Our larger enterprise-class customers combine DNS-based and HTTP layer load-balancers to support their business continuity plans. In the event of a disaster, end-users are directed via a DNS-based load-balancer from the primary E-Business Suite environment to an offsite standby site.

Disaster Recovery using DNS + HTTP LBRs:

Minimum Requirement: Session Persistence

Remember that although Oracle doesn't certify specific load-balancers or networking hardware with the E-Business Suite, we do support their use generically.
In other words, we've designed the E-Business Suite to be able to use load-balancers in general.

The minimum requirement is that a load-balancer support session
, where a client's initial HTTP connection is directed to a
particular application server, then subsequent HTTP requests from that
client are directed to the same server. As long as a
load-balancer is able to handle session persistence (also referred to
as "stickiness"), it's likely to work with the E-Business Suite.

Happy Learning !

Optimizing R12 Performance via OC4J Load-Balancing

Oracle Application Server provides features that allow customers to load balance their middle tier deployments. OC4J Clustering in OracleAS 10g is one such deployment that is widely used in load balanced configurations.

Starting with Oracle E-Business Suite Release 12.0.2 (Release Update Pack 2), we support OC4J Clustering as part of AutoConfig's load balancing deployment options. This configuration option is supported for the OC4J instance running out of R12's 10.1.3 ORACLE_HOME.

Clustering Model

Oracle Application Server 10g 10.1.3 supports various models for OC4J clustering:
  • Dynamic node discovery
  • Static discovery servers
  • Cross topology gateways
  • Manual node configuration.
E-Business Suite Release 12 uses the manual node configuration model, which is also referred to as static node-to-node communication. Node and port information is manually specified in this mode. Configuration details are managed by Autoconfig, which handles all necessary settings in the R12 configuration files.

Figure-1 static node-to-node communication model


Deployment Options

Release 12 supports the following deployment options with OC4J clustering:
  1. Single Web Entry Point
  2. Multiple Web Entry Points


  1. Oracle Applications Technology Stack Patch Set 2 5917601 or higher.
    • This Patch Set is included in the R12.ATG_PF.A.DELTA.2 5917344 and the Release 12.0.2 (Release Update Pack 2)
  2. A correctly-configured hardware load balancer

Configuring OC4J Load Balancing

Configuring both deployment options requires:
  1. Changing the E-Business Suite Context file using the Context File Editor
  2. Running AutoConfig
  3. Restarting the application tier server processes.
Single Web Entry Point

In this deployment option, there is single web entry point for the OC4J
applications - oacore, forms and oafm services are configured to run on all or some
of the application tier nodes.

Figure-2: Deployment model with Single Web Entry point and OC4J application running on all the nodes

SingleWebEntry: Architecture diagram showing an E-Business Suite environment with two application tier servers and a single webentry point

Multiple Web Entry Points

In this deployment option, there are multiple web entry points with OC4J applications running on selected application tier nodes.

Figure-3: Deployment model with Multiple Web Entry points and OC4J applications running on selected nodes

multipleweb: Architecture diagram showing a multinode Apps environment

Happy Learning !

Understanding JDBC Connections From the eBusiness Middle Tier

In this article I will describe the basics of configuring and monitoring JDBC connections between the eBusiness Web Tier and the Database, then cover some common issues to help identify root causes for both Release 11i and Release 12 of eBusiness Suite.

11i Architecture:

Brief overview

In general, whenever a functional page requires data from the database, it makes a call to a lower code level (the Java layer in the Application Object Library, also known as AOL/J) which handles the database connectivity. The AOL/J code provides a JDBC connections to the database through the Database Connection Pool.

You should note that the Java Connection Pool mechanism for eBiz is completely provided by eBiz code and does not use the mechanisms provided to pool connections through Java language directly, nor through the Application Server configuration.

Configuring JDBC connection pooling

The JDBC connection pool is created using the settings in the DBC file. The name, location and contents of this DBC file is controlled through AutoConfig. To modify any of the JDBC connection pool parameters, you should therefore use the techniques described in the following document to ensure changes are maintained in a supportable and consistent way:

The "s_dbc_file_name" variable in the CONTEXT.xml file provides the dbc filename and is located in the $FND_TOP/admin/<>_ directory.

JDBC connection pool parameters and their use are covered in the following documents:

When considering any changes, you should also take into account that every JVM has its own JDBC connection pool. For example, if you have one Web Node with three OACoreGroup JVMs plus one XmlSvcsGrp JVM configured, then you will have a total of four JDBC connection pools with connections to your eBiz database

Monitoring the JDBC connection pool

It is always a good idea to understand how your environment looks when things are going well, to give you a baseline to compare against if you need to investigate any issues.

You will most certainly need to review the JDBC connection data if you are experiencing issues.

Monitoring JDBC Connections through Oracle Applications Manager (OAM)

Login to OAM directly or via the "System Administration" responsibility.
  1. Select the "JServ Usage" under the Monitoring section in OAM
  2. Click the "Expand all" link to list the Servers and all the JServ processes for OACoregroup. This shows memory usage, connections (including "Potentially Leaked") and Application Module information. You can click the "Add to support cart" to capture this page if Oracle Support are assisting your investigation.
  3. If there are any non zero values for "Potentially Leaked" then click on this number to drill down into the details
  4. Select "Yes" for the "Filter by Potentially Leaked" option and click "Go" button
  5. Click "Show all details" to display the Java Thread dump for all the potentially leaks Java connections


The "old" way of gathering this data was to use the URL http://host.domain:port/OA_HTML/jsp/fnd/AoljDbcPoolStatus.jsp but this will only give data for the one JVM you happen to connect to, so may not be so useful in multi-JVM environments.

Run SQL scripts to monitor database connections

Using SQL scripts will not give so much information as OAM, but can be used to provide useful summary information on a periodic basis. For example you may wish to include the information from the SQL below as part of your baseline data:

REM Connections by machine and instance
select s.machine, s.username, s.module, s.inst_id, count(*) how_many
from (select distinct PROGRAM, PADDR, machine, username, module, inst_id from gV$SESSION) s,
gv$process p
where s.paddr = p.addr
and p.inst_id = s.inst_id
group by s.machine,s.username, s.module, s.inst_id

NOTE - when looking at V$SESSION, the STATUS of JDBC connections tend to show as INACTIVE, this is normal and does not indicate a specific problem

Where Could It Go Wrong?

Issues with the JDBC connection pool tend to be of a nature whereby the number of database connections increase over time and do not seem to be released again. If this continues unchecked, then you may find the database will run out of sessions/processes and/or the Middle Tier JVM will run out of connections or memory.

A "quick fix" would normally be to restart Apache, but the underlying cause would need to be investigated further.

Issues can potentially occur at five different levels:

  1. Core Java Code
  2. AOL/J JDBC Code
  3. OA Framework
  4. Functional Code
  5. External Influences

I'll discuss each of these areas next.

Core Java code

Although eBiz does not use native Java connection pooling methods, we do rely on the underlying Java APIs generally. Any issues at this level will generally require the latest Java version to be installed

If you need to upgrade your Java version, see:


As this is the code that handles JDBC connection, it is often the first area to be blamed, but the JDBC connection pool can only drop database connections where the calling application has released the JDBC connection it holds in the pool, so it often turns out to be an issue higher up the code stack.

Number of JDBC connections increase after ATG Rup 5 because jdbc parameters are lower case (Metalink Note 459072.1) describes a known issue with Apps 11i.

It is prudent to be on the latest JDBC driver patch, but should have at least applied one of the following patches:-

The latest JDBC patch can be found in:

You should note that the JDBC driver version has no relation to the Database version, as it is installed on the eBiz Middle Tier. For example, the latest version of JDBC drivers provided by patch 4899697 ( is the same patch for all RDBMS versions.

OA Framework (OAF)

OA Framework calls AOL/J when it needs a database connection, and it is up to OAF to release any such connection when it has finished with it. There is an added complexity, in that OAF also has its own pooling mechanism for the OAF Pages, which is the "Application Module pool" (AM pool). This means that although a user may have finished with a page, the page and its associated database connection are retained for a period of time.

The AM pool is controlled by profile options, which are described in :-

Issues at this code level would tend to be either:

1. Issue with AM Pooling

You can test the effect of disabling AM pooling by setting the profile option "FND: Application Module Pool Enabled" to "No". Use this technique with caution if considering this for a production environment.

2. Specific bug where database connection is not released.

This would generally require patching.

Functional Code

Issues at this code level would tend to be a specific bug where a connection is not released.

External influences

Firewall timeouts are known to potentially cause an issue for JDBC connections. For details, see:

Although this note is for Apps 11i, the technical issue can also apply to Release 12 as well

Configuring eBiz to minimize JDBC connections

If investigating issues with JDBC connections increasing, it may be useful to minimise the database connections as much as possible by de-tuning the JDBC pool. This may reduce end user performance, so should be used with caution if considering this for a production environment.

To do so, you need to do both these steps:

1. Disable Application Module (AM) Pooling

This is necessary as the AM objects hold a JDBC connection whilst they are alive, even if they are not currently used.

Set the profile option "FND: Application Module Pool Enabled" (AMPOOL_ENABLED) at SITE level to a value of "No"

2. Set the JDBC connection pool parameters to release connections:



Identifying issues with JDBC connections can sometimes be a frustrating process, as the investigations may need to consider multiple failure points and complex architectures. I hope this article has given you a better understanding of JDBC Pooling and where to start looking for issues.

Happy Learning !

Wednesday, May 6, 2009

Oracle Upgrade DB to 10g in oracle apps

10g ( Database Upgrade

Note: upgrade from to 10.2.0
OS: Sun 5. 10
Node: Multi node

10g Upgrade Information
Software Requirements
Before the Database Installation
Database Installation:
Database Upgrade
Post Upgrade Step (Conditional)
HR EEO Patch
Initialization parameters set for 10G database (TAG) on 11i
Before 10g upgrade
After 10g Upgrade

10g Upgrade Information

Server: oadevdb03


Port: (1547)

Location: /u26/oracle/oraTAG/TAGdb/10.2.0

Oracle DB version:

Time taken: 5 hours (approx)

Software Requirements


� Oracle Database 10g ( DVD.


� 4653225 (11.5.10 INTEROP PATCH FOR 10GR2)
� 6880880 (OPatch 10.2)
� 5753621 (ONE-OFF REQUEST ON TOP OF 11.5.9 CU2 FOR BUG 5525015 Product Receivables)


� Metalink Note# 362203.1 (Oracle Applications Release 11i with Oracle 10g Release 2 (10.2.0))
� Oracle Database Upgrade Guide 10g Release 2
� Metalink Note# 353150.1 (OPatch Failing Validation Phase When Archiving Really Did Not Fail)
� Metalink Note# 216205.1 (Database Initialization Parameters for Oracle Applications Release 11i)
� Metalink Note# 438049.1 (How To Find RDBMS patchsets on Metalink)
� Metalink Note# 165195.1 (Using AutoConfig to Manage System Configurations with Oracle Applications 11i)


� Cold Backup of entire instance.
Unix system parameter
Create magic ticket for following
Add set noexec_user_stack=1 in the /etc/system file (will be done by sysadmin)
Then reboot the system.
Perform this step a week before in Production.


We are following section 1 of Note # 362203.1.
Before the Database Installation

1. Verify software version (15 minutes)
� Apply patch 4653225 interoperability patch for 10gR2.
� Download patch 4653225 for both Us, Japanese Language.
� Make sure only database and listener are running.
� Count number of invalids objects
� Enable maintenance mode using adadmin utility.
� Perform following under dbnode (oadevdb03)
cd 4653225
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
cd 4653225
use adpatch utility to apply patch
Perform following under dbnode (oadevdb03)
cd 4653225_JA
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
cd 4653225_JA
use adpatch utility to apply patch
Disable maintenance mode using adadmin utility.
Shutdown Database and listener.

2. Migrate to Oracle Portal 10g
� Skip as we are not using the Oracle Portal.

3. Deregister the current database server
� Skip as we are not planning to change database port, host, SID

4. Update application tier context file with new database listener port number
� Skip

5. Export OLAP analytical workspaces
� Skip

Database Installation:

6. Prepare to create the 10.2.0 Oracle home (10 minutes)
� Please follow Chapter 2 of 10g Upgrade guide also.
� Create following directory
cd /u26/oracle/oraTAG/TAGdb
mkdir 10.2.0
Change orainventory location in oadevdb03
cd /var/opt/oracle
vi oraInst.loc
and comment all other locations
Change .profile of oracle user (oraTAG)
export ORACLE_HOME=/u26/oracle/oraTAG/TAGdb/10.2.0
export PERL5LIB
export ORA_NLS10
export DBDIR=/u26/oracle/oraTAG/TAGdb/9.2.0
export PATH
. /u26/oracle/oraTAG/TAGappl/APPSORA.env

7. Install the base 10.2.0 software
� Install the base 10.2.0 software. (20 minutes)
� export DISPLAY=10.x.x.x:0.0
� verify DISPLAY setting by invoking the xclock command
� cd /u26/oracle/oraTAG/soft
� ./runInstaller
� Need root access to run (especially in Production)

8. Install Oracle Database 10g Products from the 10g Companion CD
� Skip. Not Required.

9. Perform patch set pre-installation tasks
� Download Patchset 3 (5337014)
� Unzip patch under /u26/oracle/oraTAG/soft/patchset

10. Perform patch set installation tasks (15 minutes)
� Install the patchset
� export DISPLAY=10.x.x.x:0.0
� verify DISPLAY setting by invoking the xclock command
� cd /u26/oracle/oraTAG/soft/patchset/Disk1
� ./runInstaller
� Need root access to run (especially in Production)

11. Create nls/data/9idata directory (5 minutes)
� cd $ORACLE_HOME (10g Oracle Home)
� check perl location ( it should be /usr/bin/perl)
� cd nls/data/old
� perl cr9idata.sql(pl)
� It will create $ORACLE_HOME/nls/data/9idata directory
� After creating the directory, make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 10g Oracle home.
20th � 27th November 2008Take cold bacup of entry system.

./opatch apply /u26/oracle/oraTAG/soft/patchset/5871314

opatch lsinventory
% cd 5892355
% opatch rollback -id 5892355

1) echo $ORACLE_HOME(
2) check the oracle_home in .profile
3)/var/opt/oracle below the files
oratab and oraInst.loc
4) $ORACLE_HOME/jdk/bin/java -fullversion
java full version "1.4.2_08-b03"
5) check perl version and location
6) setenv OBJECT_MODE 32_64 before applying opatch
Ensure environment variable OBJECT_MODE is set to 32_64
# % setenv OBJECT_MODE 32_64 or $ export OBJECT_MODE=32_64

7) Please get the confom the Opatch under $ORACLE_HOME
8) read care full README.txt
9) incase of failures.

12. Apply additional RDBMS patches (20 minutes->
� Download all patches (5892355,5871314,5755471,5752399 and 6880880)
� Unzip the patch 6880880 under $ORACLE_HOME for latest OPATCH version.
� Unzip patch 5892355
� cd 5892355
� opatch apply
� Verify the patch application. (./opatch lsinventory)
� Download in the /u26/oracle/oraTAG/TAGdb/10.2.0
� Copy the old version Opatch folder.
� Please ignore following error message for all the Patches
The following warnings have occurred during OPatch execution:
1) OUI-67124:Verification of 'ar' actions failed. This is a known issue for this platform. Please check the note 353150.1 on Metalink for the process to manually verify the files.
OPatch Session completed with warnings.
� Unzip patch 5871314
� cd 5871314
� opatch apply
� Unzip patch 5755471
� cd 5755471
� opatch apply
� Apply patch 5752399 to avoid the problem in step 15
� Unzip patch 5752399
� cd 5752399
� opatch apply OPATCH.SKIP_VERIFY=true
Step1 cd /u26/oracle/oraTAG/TAGdb/10.2.0/Opatch

(Folder copy)
cp-Rp/u26/oracle/oraTAG/TAGdb/10.2.0/OPatch /u26/oracle/oraTAG/TAGdb/10.2.0/OPatch_old

(File copy) /u26/oracle/oraTAG/TAGdb/10.2.0/
Database Upgrade

13. Shutdown Applications server processes and database listener
� On each application tier server node, shut down all server processes or services. Also shutdown database and listener.
� Already done.

14. Prepare to upgrade
� Done.
28 Nov 2008 18:30 Hours (Take cold bacup of entry system.)

15. Upgrade the database instance. (2 hours)
� Please follow Chapter 3 and 4 of 10g Upgrade guide.
� Run the Pre-Upgrade Information Tool
� Start separate session with old 9i oracle home setting and start database
� Copy the utlu102i.sql file from the ORACLE_HOME/rdbms/admin directory of the new Oracle Database 10g release to old 9i oracle home.
� Login to sqlplus with sys user (10 minutes)
� spool 10gupg.log
� @ utlu102i.sql
� spool off;
� copy the 2 file utlu102i.sql and utltzuv2.sql (/tmp/utlu102i.sql , utltzuv2.sql)
� In Production, copy orapwPROD from old_oracle_home to new_oracle_home.
� Modify the initTAG.ora in new Oracle home with following changes

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
WARNING: --> "streams_pool_size" is not currently defined and needs a value of at least 50331648
WARNING: --> "large_pool_size" needs to be increased to at least 8388608
WARNING: --> "session_max_open_files" needs to be increased to at least 20

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
--> "optimizer_max_permutations"
--> "row_locking"
--> "undo_suppress_errors"
--> "max_enabled_roles"
--> "enqueue_resources"
--> "sql_trace"

Issues Requiring Further Analysis Prior to Upgrading

� Deprecated CONNECT Role
Database Links With Passwords
No effect
Release 8.1.7 National Character Set
Optimizer Statistics
Shutdown database.
Copy the configuration file from old_home to new_home
cp-Rp/u26/oracle/oraTAG/TAGdb/9.2.0/admin /u26/oracle/oraTAG/TAGdb/10.2.0/admin
Please verify following variables are set(New oracle1og Home)
cd $ORACLE_HOME/rdbms/admin(New Oracle Home)
connect to sqlplus as sysdba (10 minutes)
startup upgrade
Default $ORACLE_HOME/dbs/sysaux01.dbf Move to above the location.
Gather the invalid objects( Count and object name)
spool upgrade.log 29th nov 2008 (15.30 � 16.50)Hours
(1 hour)
spool off;
Next shutdown immediate, restart for normal operation, and then
Run utlrp.sql to recompile any invalid application objects.
@utlu102s.sql TEXT
At time of process you can the status of invalid objects.
SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
(to compile the invalids) (30 minutes) 29th nov 2008 16.52 hours
It does not compile all invalids. Recompile later thru adadmin.(43 Invalid)
Gather the invalid objects( Count and object name)

Select substr(owner,1,12) owner,substr(object_name,1,30) object,
substr(object_type,1,30) type, status from dba_objects where status <> 'VALID';

Note: before upgrade (225) after upgrade 42000 invalid objects

16. Modify Initialization parameters
� Follow Note # 216205.1 to change Database initialization parameter in Oracle Application 11i.
� Please refer the Appendix
16A shutdown
Strtaup pfile= /u26/oracle/oraTAG/TAGdb/10.2.0/dbs/initTAG.ora
Spfile create spfile from pfile;

17. Additional database configuration
� Done

18. Perform patch set post installation tasks
� Skip.

19. Install Data Mining and OLAP
� Skip.

20. Natively compile PL/SQL code
� Skip.

After the Database Upgrade

21. Fix Korean Lexers
� Skip as we didn�t find the script drkorean.sql under @$ORACLE_HOME/ctx/sample/script

22. Apply RDBMS patches for Daily Business Intelligence
� Skip.

23. Import OLAP analytical workspaces
� Skip.

24. Start the new database listener (5 minutes)
� cp tnsnames.ora and listener.ora from OLD_ORACLE_HOME/network/admin/TAG to ORACLE_HOME/network/admin
cp -Rp /u26/oracle/oraTAG/TAGdb/9.2.0/network/admin /u26/oracle/oraTAG/TAGdb/10.2.0/network/admin
� edit listener.ora and make following changes
change the location of Oracle home and Log file
� lsnrctl start TAG
Export TNS_ADMIN=/u26/oracle/oraTAG/TAGdb/10.2.0/network/admin/TAG_oadevdb03
25. Run adgrants.sql (2 minutes)
� Copy $APPL_TOP/admin/adgrants.sql to ORACLE_HOME
� sqlplus "/ as sysdba" @adgrants.sql APPLSYS

26. Grant create procedure privilege on CTXSYS (2 minutes)
� Copy $AD_TOP/patch/115/sql/adctxprv.sql to ORACLE_HOME
� sqlplus apps/ @adctxprv.sql CTXSYS

27. Implement and run AutoConfig (30 minutes) -- > 19 Nov 2008 20:20 Hours
� Follow Section 8 from Note# 165195.1
� Copy AutoConfig to the RDBMS ORACLE_HOME
� cp-Rp/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil /u26/oracle/oraTAG/TAGdb/10.2.0/appsutil
On the Application Tier (OADEVAPP03):
Log in to the APPL_TOP environment
Create file
perl /bin/
This will create in $APPL_TOP/admin/out

On the Database Tier:
FTP the file to the ORACLE_HOME
unzip �o

ls *.env
vi TAG_oadevdb03.env ( change the oracle_home path and unset APPL_TOP)
Generate your Database Context File (1st Dec 2008 IST)
cd ORACLE_HOME/appsutil/bin
perl tier=db appsuser=APPS
Enter Hostname of Database server: oadevdb03
Enter Port of Database server: 1551(1547)
Enter SID of Database server: TAG
Enter the value for Display Variable: oadevdb03:1.0
The context file has been created at:
Generate and Apply AutoConfig Configuration files
cd $ORACLE_HOME/appsutil/bin contextfile=/u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/TAG_oadevdb03.xml
run in appstier (both dbnode and appsnode)
DB node :
� /u26/oracle/oraTAG/TAGdb/10.2.0/appsutil/scripts/TAG_oadevdb03 >./
� /u26/oracle/oraTAG/TAGcomn/admin/scripts/TAG_oadevdb03 >./
� Apps Node :
� /u26/oracle/oraTAG/TAGcomn/admin/scripts/TAG_oadevdb03 >./

28. Gather statistics for SYS schema (10 minutes) 1st Dec 2008 5.30pm
� Copy $APPL_TOP/admin/adstats.sql to ORACLE_HOME
� sqlplus "/ as sysdba"
� shutdown immediate;
� startup restrict;
� @adstats.sql;
� shutdown normal;
� startup;
� exit;

29. Re-create custom database links
� Not required.

PLATFORM=SunOS; export PLATFORM (NOTE:293441.1)

30. Re-create grants and synonyms(10 minutes) 3rd Dec 2008 8.30pm
Recreate grants and synonyms option
Choose option 4 from Main menu then choose option 2.
Compile APPS schema.
Choose option 3 from Main menu then choose option 1.
Make sure the number of invalids objects are 14.
New invalid objects
PER_STC_BUS (Package Body)

31. Apply Oracle Receivables patch(5 minutes)
� Enable maintenance mode using adadmin utility.
Perform following under dbnode (oadevdb03)
cd 5753621
use adpatch utility to apply patch
Perform following under appsnode (oadevapp03)
cd 5753621
use adpatch utility to apply patch
Disable maintenance mode using adadmin utility.
Post Upgrade Step (Conditional)

� Please test the in oadevdb03. (10 minutes)
� Check if the entire concurrent managers are shutting down.
� If not please run the cmclean.sql script (134007.1)
� Make sure all the FNDLIBR are stopped.
� Login to sqlplus as apps user
o @cmclean.sql
o Commit;
� Test the again
� Check the number of invalids after the 10g upgrade.

HR EEO Patch
� Apply HR EEO patch(30 minutes)
� Enable maintenance mode using adadmin utility.
� Gather invalid object again
� Perform following under dbnode (oadevdb03)
o Unzip
o cd 5511810
o use adpatch utility to apply patch
� Perform following under appsnode (oadevapp03)
o Unzip
o cd 5511810
o use adpatch utility to apply patch
� Perform following under dbnode (oadevdb03)
o Unzip
o cd 5511810_JA
o use adpatch utility to apply patch
� Perform following under appsnode (oadevapp03)
o Unzip
o cd 5511810_JA
o use adpatch utility to apply patch
� Disable maintenance mode using adadmin utility.
� Gather invalid object again
� Take Cold Backup of system.


Initialization parameters set for 10G database (TAG) on 11i

Before 10g upgrade

db_name = TAG
control_files = /u26/oracle/oraTAG/TAGdata/cntrl01.dbf,/u26/

db_block_size = 8192
compatible = 9.2.0
_system_trig_enabled = true


#row_locking = always

nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary # Required 11i setting
nls_comp = binary # Required 11i setting
nls_length_semantics = BYTE # Required 11i setting

#audit_trail = true # if you want auditing

#max_enabled_roles = 100 # Some modules depend on this feature.
user_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/udump
background_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/bdump
core_dump_dest = /u26/oracle/oraTAG/TAGdb/9.2.0/admin/SIT1_oadevdb03/cdump
max_dump_file_size = 20480 # trace file size
timed_statistics = true

processes = 1200 # Max. no. of users x 2
sessions = 400 # 2 X processes
db_files = 500 # Max. no. of database files
dml_locks = 10000
cursor_sharing = EXACT # Required 11i settting
open_cursors = 600 # Consumes process memory, unless using MTS.
session_cached_cursors = 200
#enqueue_resources = 32000 # Max. no of concurrent database locks
db_cache_size = 163577856
db_block_checking = FALSE
db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE







olap_page_pool_size = 4194304
shared_pool_size = 300000000
shared_pool_reserved_size = 30000000
_shared_pool_reserved_min_alloc = 4100

java_pool_size = 52428800

#utl_file_dir = /usr/tmp,/u26/oracle/oraTAG/TAGcomn/temp,/u26/oracle/oraTAG/s
utl_file_dir= /home/bicepsftp/files,/u26/oracle/oraTAG/sit1comn/temp,/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil/outbound/SIT1_oadevdb03,/usr/tmp
aq_tm_processes = 1
job_queue_processes = 2

# log_archive_start = true # if you want automatic archiving

parallel_max_servers = 8
parallel_min_servers = 0

optimizer_features_enable = 9.2.0

db_file_multiblock_read_count = 8

#optimizer_max_permutations = 2000 # Required for 11i setting

_like_with_bind_as_equality = TRUE


_fast_full_scan_enabled = FALSE

query_rewrite_enabled = true


_index_join_enabled = FALSE

_b_tree_bitmap_plans = FALSE


After 10g Upgrade

db_name = TAG
control_files= /u26/oracle/oraTAG/TAGdata/cntrl01.dbf,/u26/oracle/oraTAG/TAGdata/cntrl02.dbf,/u26/oracle/oraTAG/TAGdata/cntrl03.dbf

db_block_size = 8192

compatible = 10.2.0

_system_trig_enabled = true


nls_language = american
nls_territory = america
nls_date_format = DD-MON-RR
nls_numeric_characters = ".,"
nls_sort = binary # Required 11i setting
nls_comp = binary # Required 11i setting
nls_length_semantics = BYTE # Required 11i setting

user_dump_dest = /u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/udump
core_dump_dest = /u26/oracle/oraTAG/TAGdb/10.2.0/admin/TAG_oadevdb03/cdump

max_dump_file_size = 20480 # trace file size

timed_statistics = true

_trace_files_public = TRUE

processes = 1200 # Max. no. of users x 2
sessions = 400 # 2 X processes
db_files = 500 # Max. no. of database files
dml_locks = 10000
cursor_sharing = EXACT # Required 11i settting
open_cursors = 600 # Consumes process memory, unless using MTS.
session_cached_cursors = 200

sga_target= 1G
db_block_checking = FALSE
db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval = 100000
log_buffer = 10485760
log_checkpoints_to_alert = TRUE





olap_page_pool_size = 4194304

shared_pool_size = 300000000
shared_pool_reserved_size = 30000000

utl_file_dir = /home/bicepsftp/files,/u26/oracle/oraTAG/TAGcomn/temp,/u26/oracle/oraTAG/TAGdb/9.2.0/appsutil/outbound/TAG_oadevdb03,/usr/tmp

aq_tm_processes = 1
job_queue_processes = 2

parallel_max_servers = 8
parallel_min_servers = 0

db_file_multiblock_read_count = 8

_like_with_bind_as_equality = TRUE


_fast_full_scan_enabled = FALSE


_b_tree_bitmap_plans = FALSE


PLATFORM=SunOS; export PLATFORM (add in the .profile in DBTier of DB node)
export TNS_ADMIN=/u26/oracle/oraTAG/TAGdb/10.2.0/network/admin/TAG_oadevdb03
unset NLS_LANG (in DB env file)

Happy Learning !