Thursday, June 18, 2009

Reducing Patching Downtimes via Shared Apps File Systems

I had a chance conversation with an Apps sysadmin late in the evening at the Collaborate conference. He wearily noted that he had to somehow cover increasing maintenance costs with reduced levels of staffing. The conversation suggested that our support for shared E-Business Suite application tier file systems may be one of our better-kept secrets. If you haven't come across this yet, here's a way of reducing the amount of time that you spend patching.


Scaling Up with Load-Balancers

When your E-Business Suite user base grows beyond a certain size, it's likely that you'll look into deploying multiple application servers in a load-balanced pool of nodes. Your deployment topology might look like this:

Generic Apps Load-balancing:

Regardless of whether you're running Release 11i or 12, each of these
nodes would need its own disk space and would to be maintained and
patched separately. What a pain.

Different File System Structures in Release 11i and 12

In Release 11i, the Applications tier file system includes the APPL_TOP, the COMMON_TOP, and the Applications technology stack (8.0.6 and iAS ORACLE_HOMEs). In a traditional deployment, each one of these application servers would have its own Applications file system, like this:

Distributed Application Tier Filesystem:

In Release 12, the Applications tier file system includes the APPL_TOP, the COMMON_TOP, and the Applications technology stack (10.1.2 and 10.1.3 ORACLE_HOMEs), plus a new INST_TOP. Each node would have the following:

Release 12 application tier structure 2:

Enter the Shared Application Tier File System

For Release 11i, starting with 11.5.10, it's possible to put the Applications tier file system on a shared disk resource mounted to each Application tier server node in the system, like this:

Shared Application tier file system:

Similarly, in Release 12, you could put the Applications tier file system on a shared disk resource mounted to each Application tier server node, like this:

Release 12 shared filesystem:

Migrating to Shared File Systems

There are a few prerequisites:
  • Different nodes must be running the same operating system and the same O/S patches
  • You must be on a UNIX platform (Windows doesn't support shared file systems)
Certification of Shared File System Solutions

If you've gotten to this point, you're probably wondering, "Is my _____ SAN/NAS shared file system software certified with the E-Business Suite?" File system solutions that customers have recently asked about include:
Supported but not Certified

The short answer is that your shared file system solution is supported but not certified with the E-Business Suite, for either Release 11i or 12. Remember that there's a key distinction between support and certification, which I've covered in detail in this article:
The complexities of whatever shared disk resource management solution that you're using must be transparent to the E-Business Suite. Aside from that, there aren't any special requirements for shared disk resources. They can be local to the server or on a standalone disk array.

Almost Irresistible

Regardless of which E-Business Suite release you're running, the main advantage of using an Application shared file system is simplicity and ease of patching: when
you apply patches or changes to the shared disk resource,
they're immediately visible on all application tier server nodes. You
patch in a single place and deploy those changes across multiple
servers. You save disk space for each additional application node you
deploy, and it's easier to deploy additional nodes, too.

If you've been wondering how to squeeze more productivity out a packed roster of administration activities, I'd recommend setting up a testbed to try this out. Feel free to post a comment with your experiences with this; I'll make sure your feedback gets back our team.

Related
HAPPY LEARNING!

Which is Better: Forms Servlet or Socket Mode?


Many products within the Oracle E-Business Suite have screens that are built with Oracle Forms. Oracle Forms can be run in either servlet mode or socket mode. Apps 11i is based on Forms 6i and is configured to run in socket mode by default. Apps 12 is based on Forms 10g and is configured to run in servlet mode by default. What are these modes, and which is better?

What is Forms Servlet Mode?

The Forms Listener Servlet is a Java servlet that delivers the ability to run Oracle Forms applications over HTTP and HTTPS connections. It manages the creation of a Forms Server Runtime process for each client, as well as network communications between the client and its associated Forms Server Runtime process.

The desktop client sends HTTP requests and receives HTTP responses from the web server. The HTTP Listener on the web server acts as the network endpoint for the client, keeping other servers and ports from being exposed at the firewall.

Forms listener servlet diagram showing firewalls desktop client and oc4j container on application tier

Forms listener servlet diagram showing firewalls desktop client and oc4j container on application tier




What is Forms Socket Mode?

Initial releases of the Oracle Forms Server product used a simple method for connecting the client to the server. The connection from the desktop client to the Forms Listener process was accomplished using a direct socket connection. The direct socket connection mode was suitable for companies providing thin client access to Forms applications within their corporate local area networks. For the direct socket connection mode, the client had to be able to see the server and had to have permission to establish a direct network connection.

Although the direct socket connection mode is perfectly suited for deployments within a company’s internal network, it's not the best choice for application deployment via unsecured network paths via the Internet. A company connected to the Internet typically employs a strict policy defining the types of network connections that can be made by Internet clients to secure corporate networks. Permitting a direct socket connection from an external client exposes the company to potential risk because the true identity of the client can be hard to determine.

Servlet Mode Advantages

1. HTTP and HTTPS traffic is easily recognizable by routers, while socket mode communications is generally considered suspect and treated on an exception basis.
2. Existing networking hardware can be used to support basic functions such as load-balancing and packet encryption for network transit.
3. More resilient to network and firewall reconfigurations.
4. More robust: servlet connections can be reestablished if network connections drop unexpectedly for Forms, Framework, and JSP-based pages.
5. Is the only supported method for generic Oracle Forms customers, and therefore is more thoroughly tested by the Forms and E-Business Suite product groups.
6. Performance traffic can be monitored via tools like Oracle Real User Experience Insight (RUEI).
7. Socket mode is not supported on Windows-based server platforms.

Socket Mode Advantages

1. Uses up to 40% less bandwidth than Forms servlet mode. This may be perceived by Wide Area Network (WAN) users as causing slower responsiveness, depending upon network latency.
2. Uses fewer application-tier JVM resources than servlet mode, due to fewer TCP turns and lack of overhead associated with HTTP POST handling.

Switching Apps Deployments Between Modes

Due to its numerous advantages, Forms servlet mode is the preferred and recommended deployment model for Forms on the web.

There may be circumstances where you need to switch between the default Forms modes. You might wish to switch your Oracle E-Business Suite Release 12 environment to socket mode to improve performance or reduce network load. You might wish to switch your Apps 11i environment to servlet mode as part of your rollout to external web-based end-users outside of your organization.

If you're running Apps 11i and would like to switch to servlet mode, see:

* Using Forms Listener Servlet with Oracle Applications 11i (Note 201340.1)

If you're running Apps 12 and would like to switch to socket mode, see:

* Using Forms Socket Mode in Oracle Applications Release 12 (Note 384241.1)


Happy Learning !

Thursday, May 28, 2009

Statspack Snapshot DBMS Job

Expressing Time with Fractions - (Date Arithmetic)


Contents

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


Overview

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:

DBMS_JOB.SUBMIT (
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
-- =========================================================

VARIABLE jobno NUMBER;
VARIABLE instno NUMBER;

BEGIN

select instance_number into :instno from v$instance;

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

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

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

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

COMMIT;

END;
/



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/24
1/4
6 hours
1/24/60/60
5/24/60/60
One second
Five seconds
1/24/60
5/24/60
One minute
Five minutes
1/24
5/24
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;
BEGIN
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);
COMMIT;
END;
/



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

variable jobno number;
variable instno number;
BEGIN
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);
COMMIT;
END;
/



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

variable jobno number;
variable instno number;
BEGIN
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);
COMMIT;
END;
/



Run Statspack Snapshot Every 1 Hour

variable jobno number;
variable instno number;
BEGIN
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);
COMMIT;
END;
/



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.

SQL> SELECT
sysdate
, CASE
WHEN ( TO_CHAR(SYSDATE, 'HH24') BETWEEN 6 AND 17
AND
TO_CHAR(SYSDATE, 'DY') NOT IN ('SAT','SUN')
)
THEN TRUNC(sysdate) +
(TRUNC(TO_CHAR(sysdate,'sssss')/900)+1)*15/24/60
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;


SYSDATE
------------------------------

INTERVAL_DATE
------------------------------
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:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'(select * from next_date)'
);
end;
/
Or, if I used the PL/SQL function approach and created a function NEXT_DATE, it could be:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'next_date()'
);
end;
/



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

The following solution comes thanks to Martin Trappel <tm5@gmx.at>.

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.

**********

SYS.DBMS_JOB.SUBMIT (
job => X
, what => '
begin
-- do stuff ...
end;
'
, next_date => :some_date
, interval => 'GET_NEXT_JOB_DATE(''my_job'', 2.25)'
, no_parse => FALSE
);


**********

FUNCTION GET_NEXT_JOB_DATE( p_jobid VARCHAR2
, p_interval_hours NUMBER) RETURN DATE IS
x_old_next_date DATE;
x_new_next_date DATE;
BEGIN
-- FETCH THE CURRENT NEXT DATE OF THE JOB:
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;
END IF;

-- START AT THE LAST SET NEXT DATE AND INCREASE BY THE SET
-- INTERVAL UNTIL THE NEW NEXT DATE IS IN THE FUTURE:
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!