OC/RDC Upgrade from 4.5.3 to 4.6.x:

Lessons Learned & Best Practices

Prepared by Sushil Motwani, David Bradley

and Subbarao Ponnuru

Oracle Apps & Health Sciences SSC Team

3

Agenda

• Oracle Clinical and RDC 4.5.3 Architecture Overview

• Oracle Clinical and RDC 4.6.x Architecture Overview –

What’s New?

• Certified Upgrade Techniques – Database and

Middleware

• Technologies in the Database, certified to be used in

OC/RDC

• Issues encountered during Pre-Production and Post-

Production

• Parameters for Optimum Performance of Database and

Middle-Tier for OC/RDC

• Best Practices to keep the environment up and running

4

OC/RDC 4.5.3 Architecture

5

OC/RDC 4.5.3 Certified Tech Stack

For more details please refer DOC ID: 180430.1

Basic Information

Database Server:

Solaris/HP/MS Windows 2000 or 2003

9.2.0.6 Enterprise Edition (64-bit) or greater (9i ONLY)

Middle Tier:

MS Windows 2003

9iAS 1.0.2.2.2a with Developer Patchset #17 or 18

Oracle 10gR1 Patch Set 1 or 3 (9.0.4.1 or 9.0.4.3)

RDC Onsite: 10gR2 (10.1.2.0.2 or 10.1.2.3)

Oracle JInitiator

6

OC/RDC 4.6.x Architecture

7

OC/RDC 4.6.x Certified Tech Stack

For more details please refer DOC ID: 180430.1

Basic Information

Database Server:

Solaris/HP-UX/Oracle Linux/Windows 2008 (4.6.4 onward)

11.2.0.2 (11.2.0.3 for 4.6.5 and 4.6.6)

Middle Tier:

MS Windows 2008 R2 Service Pack1

Developer Patchset 10.1.2.0.2 with upgrade to 10.1.2.3

Oracle 10gAS Release 3 (10.1.3) with upgrade to 10.1.3.5

Oracle Sun JRE 1.6_x

8

Pre-Upgrade/During Upgrade

Considerations • OS Version: Always install on the exact version

specified on the Doc ID: 180430.1, including the

additional software requirements

• Character Set: For OC/RDC, Oracle strongly

recommends that you use the UTF8 character set

• Critical Patch Update: ONLY apply the latest Critical

Patch Update (CPU) approved by Oracle Health

Sciences from My Oracle Support

• JDK: OC/RDC Classic support Java SE 6 Update 24

(Standard Edition, Version 1.6.0.24) or later

9

Pre-Upgrade/During Upgrade

Considerations • Database Instance: Oracle recommends that you set up

a new database instance so that neither Oracle Clinical

nor its installation process interferes with other

applications.

• Oracle SID: Define the Oracle Clinical SID name using

lowercase letters only. There are no conflicts in the

Oracle Clinical Data Extract module. Oracle

recommends that you use all lowercase letters for the

SID name.

• Linux OS: Huge Pages versus Not using Huge Pages

10

DB Upgrade Workarounds from 4.5.x to

4.6.x • Since the Target database for 4.6.x is 11.2.0.x, the

minimum version to upgrade is 9.2.0.8 for 4.5.x

• If your current Oracle Clinical installation is on an OS

that is supported for OC 4.6.2 and you choose to use

the same hardware for OC 4.6.2, you can upgrade in

place using the Oracle DBUA

• Different Hardware/Not Supported OS for 4.6.2:

Certified Method is to do an export of the 9.2.0.x

Database, and import into the newer 11.2.0.x database,

BUT a workaround is acceptable, though not certified,

and has been used by customers i.e. Upgrade to

11.1.0.7, then used Data Pump Export and Import into

11.2.0.x on the New Hardware

11

Important DB Tier Steps for

11.2.0.2 / 11.2.0.3 For 4.6.2, 4.6.3 and 4.6.4 – 11.2.0.2 Database

• Install Oracle Database 11g Release 2 (11.2.0.2) Patch

Set 1

• Install Oracle Database 11g Release 2 (11.2.0.2) Patch

Set 1 Examples

• Install Patch 10213073 to Oracle Database 11g Release

2 (11.2.0.2) Patch Set 1 – VERY IMPORTANT

• Install CPU/PSU 10248523 – VERY IMPORTANT

For 4.6.5 and 4.6.6 – 11.2.0.3 Database

• Follow Section 6 under: OC 4.6.5 (Bug 13497166)

Release Notes (Doc ID 1436682.1) for both customers

upgrading from 4.6 and earlier, and 4.6.2 or later

12

Important DB Considerations for

Optimum Performance of OC/RDC Follow the note: Configuring Oracle Clinical Remote Data

Capture Onsite 4.6.2 for Performance and Scalability

(Doc ID 1300850.1)

• During upgrade, if a Flash Recovery Area is configured,

create multiple restore points, without deleting the

older restore points.

• Generate Statistics using DBMS_STATS rather than

ANALYZE Command

• Gather Fixed Object as well as Dictionary Stats: Doc ID

798257.1, Doc ID 457926.1, Doc ID 316889.1- Step 7

• For all tablespaces created during the upgrade, use

AUTO Allocate, rather than UNIFORM Allocate.

13

Database Tier Issues /Precautions

Following the Upgrade, following issues can be typically

observed:

• VALID Objects becoming INVALID as users start

Logging into the Application

• High Library Cache Waits and Database Hanging Issues

• Unable to connect to RDC, with errors similar to “Error

in login. BC4J Exception: JBO-30003”

• Alert Log Errors: ORA-07445: exception encountered:

core dump [kkxcrc()+931]

• This was all due to the following BUG: “Bug 10213073

CREATE SYNONYM and CREATE PACKAGE may

incorrectly invalidate objects”

14

Database Initialization Parameters

• For an optimum database performance, it is important

that the Initialization Parameters for the Database are

appropriately set

• Remove any “_” parameters that were set in 9i, since

they will not or may not be needed on 11g

• Use the features of 11g in your file, to take complete

advantage of the 11g new features

• DO NOT set parameter values different from what has

been mentioned on the White Paper (Doc ID: 1300850.1)

For e.g. OPTIMIZER_FEATURES_ENABLE should be

set to 9.2.0 rather than 11.2.0

• Listed in the next few slides are the values for the

Initialization Parameters

15

Database Initialization Parameters

Parameter

Recommended

Value Comments Comments/Document IDs

compatible

'11.2.0.2.0'

or the version

of the DB

db_block_size 16384

Recommended

Value Set

dml_locks

Remove

http://docs.oracle.com/cd/E11882_01/se

rver.112/e25513/initparams081.htm#REFR

N10051

event

'31151 trace

name context

forever, level

0x100'

Required for HTML generation. NOTE: Do

not include the EVENT parameter when

you create the database. Once the

database is created, you can add the

EVENT parameter to the init.ora file.

fast_start_mttr_target 1800

Doc ID 472821.1

job_queue_processes < = # of CPUs

log_buffer 157286400

At least

3MB

log_checkpoint_interval

Remove Doc ID 472821.1

log_checkpoint_timeout

Remove Doc ID 472821.1

memory_target

Set

Appropriately

Recommended

Value Set

max_dump_file_size UNLIMITED

nls_date_format DD-MON-RRRR

Recommended

Value Set

nls_length_semantics BYTE (Default)

Recommended

Value Set

open_cursors

Make it 4 x

Processes

optimizer_dynamic_sampling 2

Recommended

Value Set

optimizer_features_enable '9.2.0'

Recommended

Value Set

16

Database Initialization Parameters

Parameter Recommended

Value Comments Comments/Document IDs

optimizer_index_caching 40

Can be

changed

optimizer_index_cost_adj 60

Can be

changed

optimizer_mode 'CHOOSE'

Recommended

Value Set

os_authent_prefix OPS$ (Default)

Recommended

Value Set

processes Check Document

remote_login_passwordfile 'EXCLUSIVE'

Recommended

Value Set

remote_os_authent TRUE

Recommended

Value Set

result_cache_max_result 5 (Default)

Recommended

Value Set

result_cache_max_size 250M (Default)

Recommended

Value Set

result_cache_mode

MANUAL

(Default)

Recommended

Value Set

sec_case_sensitive_logon FALSE

Recommended

Value Set

session_cached_cursors

At least 50%

of

OPEN_CURSORS

undo_retention Remove

Set it only if you set UNDOTBS to UNDO

Guarantee, other UNDO_RETENTION is

automatic (Doc ID: 461480.1)

max_parallel_processes 1/2* Processes

workarea_size_policy AUTO (Default)

Recommended

Value Set

17

Middle-Tier Issues / Precautions • Errors in “OPMN.LOG” file under the 10.1.3 Oracle

Home during startup: • 12/04/16 14:54:54 [pm-process] Starting Process: default_group~rdc~default_group~1 (872552613:0)

12/04/16 14:54:57 [libopmnoc4j] Host and Port information for port type jms not sent by the OC4J process

12/04/16 14:54:57 [libopmnoc4j] WARNING: OC4J did not send the protocol value for port id: jms

12/04/16 14:54:57 [libopmnoc4j] Port information in the ONS notification is incorrect for proc: 872552610.

Some of the reasons for the failure are:

1. ajp, rmi and jms ports could not be bound by the process.

2. If a port value for a certain port ID is handed by OPMN to the OC4J process and this value is not

returned back in the ONS notifications. For example, this can happen for certain bad JGroups related

configurations.

12/04/16 14:54:57 [libopmnoc4j] Process Start Error: default_group~home~default_group~1 (872552610:2112)

• Oracle 10.1.3 starts the Oracle HTTP Server and all

OPMN components using Windows Services.

• Oracle is not ready to bring OPMN up, before Windows

starts bringing up services 1-by-1

• Oracle Services need to be brought up last i.e.

Dependencies are added on “Spooler” or “Messenger”

Services as per http://support.microsoft.com/kb/193888

18

Middle-Tier Issues / Precautions

19

Middle-Tier Issues / Precautions • Sometimes during startup of components: Log files indicate

unable to start the Oracle HTTP Server(OHS) and OC4J

components. Symptoms are encounter on Windows platform

running "net start ServiceName" command from the command line

or starting the OPMN service from Windows Service Panel. • 08/12/03 13:11:10 [pm-process] Starting Process: HTTP_Server~HTTP_Server~HTTP_Server~1 (252382046:0)

08/12/03 13:11:10 [pm-process] Starting Process: default_group~home~default_group~1 (252382047:0)

08/12/03 13:14:12 [pm-process] Stopping Process: HTTP_Server~HTTP_Server~HTTP_Server~1 (252382046:7824)

08/12/03 13:14:12 [pm-process] Stopping Process: HTTP_Server~HTTP_Server~HTTP_Server~1 (252382046:7824)

08/12/03 13:14:12 [libopmnohs] Forcefully Terminating Process: HTTP_Server~HTTP_Server~HTTP_Server~1 (2523

82046:7824)

08/12/03 13:14:21 [pm-process] Process Stopped: HTTP_Server~HTTP_Server~HTTP_Server~1 (252382046:7824)

08/12/03 13:21:23 [pm-process] Stopping Process: default_group~home~default_group~1 (252382047:7096)

08/12/03 13:21:23 [libopmnoc4j] Process Stop Error: default_group~home~default_group~1 (252382047:7096)

08/12/03 13:21:23 [libopmnoc4j] Failed to construct stop command for proc: 252382047

08/12/03 13:21:23 [libopmnoc4j] Forcefully Terminating Process: default_group~home~default_group~1 (252382

047:7096)

• If the components are started separately, all the components start

successfully. %AS10gR3_home%\opmn\bin\opmnctl start

%AS10gR3_home%\opmn\bin\opmnctl startproc process-type=HTTP_Server

%AS10gR3_home%\opmn\bin\opmnctl startproc process-type=home

%AS10gR3_home%\opmn\bin\opmnctl startproc process-type=opa

%AS10gR3_home%\opmn\bin\opmnctl startproc process-type=OC4J_BI_FORMS

%AS10gR3_home%\opmn\bin\opmnctl startproc process-type=rdc

%AS10gR3_home%\opmn\bin\opmnctl startall

20

Middle-Tier Issues / Precautions • By default OPMN starts all managed components at the same

time.

• The OC4J component startups are very resource-intensive and

causes the system to slow down.

• As a result, the components are not able to complete startup

within the configured 'start timeout' values.

• After exceeding the 'start timeout' value, OPMN stops the

managed components.

SOLUTION

• Set up logical dependencies among involved components so

that OPMN will start them individually in sequential order,

similar to running opmnctl startproc commands from command

line.

• OPMN will not start subsequent component until dependent component is started successfully.

21

Middle-Tier Issues / Precautions • The “OPMN.XML” file can be modified to add the dependencies

between the various OPMN Components: <ias-component id="default_group">

<dependencies>

<managed-process ias-component="HTTP_Server" process-type="HTTP_Server" process-set="HTTP_Server" autostart="true"/>

</dependencies>

<process-type id="home" module-id="OC4J" status="enabled">

<ias-component id="ASG" status="enabled" id-matching="true">

<dependencies>

<managed-process ias-component="default_group" process-type="home" process-set="default_group" autostart="true"/>

<managed-process ias-component="default_group" process-type="opa" process-set="default_group" autostart="true"/>

<managed-process ias-component="default_group" process-type="OC4J_BI_FORMS" process-set="default_group" autostart="true"/>

<managed-process ias-component="default_group" process-type="rdc" process-set="default_group" autostart="true"/>

</dependencies>

• After implementing the changes mentioned, the

following command should give the appropriate output: D:\>d:\apps\oracle\AS10gR3\opmn\bin\opmnctl status

Processes in Instance: AS10gR3.<Server_Name>

---------------------------------+--------------------+---------+---------

ias-component | process-type | pid | status

---------------------------------+--------------------+---------+---------

ASG | ASG | N/A | Down

OC4JGroup:default_group | OC4J:rdc | 6012 | Alive

OC4JGroup:default_group | OC4J:OC4J_BI_FORMS | 6032 | Alive

OC4JGroup:default_group | OC4J:opa | 5584 | Alive

OC4JGroup:default_group | OC4J:home | 5676 | Alive

HTTP_Server | HTTP_Server | 5848 | Alive

22

Middle-Tier Issues / Precautions • Even with the changes made, and following all the recommendations of

Doc ID: Configuring Oracle Clinical Remote Data Capture Onsite 4.6.2 for

Performance and Scalability (Doc ID 1300850.1), the HTTP Server / Apache

URL stops responding after a few hours.

• At the time when the HTTP_Server stops responding, the

SSL_Engine_Log shows the following error: [18/Apr/2012 08:44:29 06832] [error] SSL call to NZ function nzos_Handshake failed with error 28854

(server *******.com:443, client 10.2.16.153)

[18/Apr/2012 08:44:29 06832] [error] Unknown error

• There a couple of reasons, the above error occurs:

• Apache.EXE is running out of Virtual Memory – Increase the Virtual

Memory on the servers to be at least 1.5 times the physical RAM.

http://support.microsoft.com/kb/308417

• Since, 10.1.3 is a 32-bit Installation, the Apache.EXE starts reaching the

2GB limit, and hangs the Middle Tier, once it reaches it. The

SYSINTERNALS tool identifies this. • Starting up the server the virtual size of the Apache.exe of the AS10gR3 is about 1.4 GB

• When a server is unavailable the virtual size of the apache.exe of the AS10gR3 is at 2

GB

23

Middle-Tier Issues / Precautions

24

Middle-Tier Issues / Precautions • Configuring Oracle Clinical Remote Data Capture Onsite 4.6.2 for

Performance and Scalability (Doc ID 1300850.1), states the following in

Section 3.1 – Configuring the Web Server: • MaxKeepAliveRequests: 0

• KeepAliveTimeout: 900

• MaxRequestsPerChild: 0

• ThreadsPerChild: 1000

• Internally, Oracle ACS set the following values for ThreadsPerChild, the

memory consumed by Apache.EXE: • TPC=50, 180MB, no growth

• TPC=200, 369MB, minor growth

• TPC=500, 732MB at start, 783MB after 30 minutes, 810MB after 45 minutes, 840MB after 60

minutes

• TPC=1000, 1390MB at start, 1440MB after 30 minutes, and keeps increasing to 2GB if

Apache.EXE is being used i.e. users are connecting to the system

• Once "Virtual Size" reaches 2.0GB, the HTTPserver stops responding.

• With further research, the Best Value for ThreadsPerChild was “250”. The

default of “50” is definitely not acceptable, but a value of “1000” is an

overkill for a 32-bit Installation of 10.1.3 Oracle Application Server

25

Report Server Queue Authentication • The Reports Server Queue is accessible using the following URL:

https://Server-Name/reports/rwservlet/showjobs?server=ocrepsrv

• Any user who knows the Server Name, and the Reports Server Name, can

access the Queue by typing the above URL, and can view the Queue

without Authentication

• We can prevent this from happening: • Edit your $ORACLE_HOME/reports/conf/rwservlet.properties file and uncomment the Diagnostics entry as

follows:

Change: #DIAGNOSTIC=NO TO DIAGNOSTIC=NO

• In the same directory, under: "server_name.conf" file under the identifier element:

<identifier confidential="yes" encrypted="no">username/password</identifier>

• The username and password can be different on each Reports Server Machine, and can be known only to

the registered users.

• Recycle the 10.1.2 Middle Tier Components.

• To access the Reports Server Queue now, you will have to use the following URL:

https://Server_Name/reports/rwservlet/showjobs?server=ocrepsrv&authid=username/password

• The clear text of username and password will be encrypted by Reports Server once it starts up, and the

encrypted attribute is changed to yes to indicate the content is encrypted.

• Now, any access to the Reports Server for using the Web commands will have to pass the same authid. If

the authid is not passed, then you will get the following error: REP-52262: Diagnostic output is disabled.

• Now, Clicking on link in Onsite to view Report Queue manager will not work and you will not be able to

view report queue manager from option within Oracle Clinical.

26

Reference Material

• Oracle Clinical / Remote Data Capture Product Information Center – Doc ID: 1331795.1

• Oracle Clinical Versions 4.6.2, 4.6, 4.5 and 4.0 Summary of Patches Available – Doc ID: 121863.1

• Configuring Oracle Clinical Remote Data Capture Onsite 4.6.2 for Performance and Scalability – Doc ID:

1300850.1

• OC/RDC 4.6 Performance Behavior and System Recommendations White Paper - Doc ID: 985112.1

• HugePages on Linux: What It Is... and What It Is Not… - Doc ID: 361323.1

• Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration – Doc ID: 401749.1

• HugePages on Oracle Linux 64-bit - Doc-ID: 361468.1

• Oracle Clinical / Remote Data Capture (OC and RDC) Documentation – Doc ID: 316117.1

• Oracle Life Sciences Applications Supported Technology Stacks – Doc ID: 180430.1

• Oracle Thesaurus Management System (TMS) Product Documentation Page – Doc ID: 1262269.1

• OLSA 4.6.x and 4.7.x Known Install and Configuration Issues – Doc ID: 386941.1

• Oracle Health Sciences Documentation – Doc ID: 1264932.1

• Oracle Clinical, Oracle Clinical Remote Data Capture Onsite, and Oracle Thesaurus Management System

Security Configuration Guide – Doc ID: 1300836.1

• Using Oracle Active Data Guard for Disaster Recovery - Doc ID: 1137450.1

• Behavior of OPMN and OracleAS Services on MS Windows Explained – Doc ID: 418977.1

• OPMN Start All Hangs / Timeouts When Starting Up Services Deployed in OracleAS 10gR3 (10.1.3) - Doc ID:

759201.1)

• OPMN Start Order Dependencies in OracleAS 10g- Doc ID: 261018.1

• Oracle® Clinical and Oracle® Thesaurus Management System Guide to Using Oracle Active Data Guard for

Disaster Recovery - Doc ID: 1137450.1

• Complete Checklist to Upgrade the Database to 11gR2 using DBUA - Doc ID: 870814.1

• Cloning Oracle Clinical and TMS 4.6.x Databases - Doc ID: 883213.1

27

Presenter Bio – Subbarao Ponnuru Ph. D

Doctorate in Organic Chemistry from IIT Kharagarpur,

India, and recipient of Alexander von Humboldt

fellowship from Germany.

Over 17 years of experience in multiple areas of IT and

worked in various roles covering all aspects of the

enterprise software life cycle in the domains of ERP

applications, Fusion Middleware technology and health

science applications . Working with Oracle ACS-SSC

for the last 5 years, and currently as a Technical

Program Manager. Areas of expertise include solution

architecture, product implementation & Management,

business development, technical delivery management

and governance.

28

Presenter Bio – David Bradley David Bradley has over 20 years of experience in

multiple areas of Information Technology.

For the last 12 years, he has been working with Oracle

ACS-SSC, currently as a principal support delivery

engineer. His expertise is in architecture, performance

tuning, troubleshooting and capacity planning.

He has worked with the Oracle database, middle-tier,

Clinical and Siebel products.

He holds a BA in Finance from Cal State Fullerton.

29

Presenter Bio – Sushil Motwani Sushil Motwani is the Senior Principle Advanced

Support Engineer with the Oracle ACS/SSC Practice at

Oracle USA. He provides on-site and offshore (remote)

services to his customers. Sushil has over 19 years

experience as an Oracle DBA/Oracle Applications DBA

with over 3-4 years supporting the Technology Stack of

Clinical Applications.

Before his current position, Sushil was a Director of

Database and Applications Administration at a smaller

organization located in the Boston, MA Area

Sushil has presented papers at several Oracle User

Groups over the course of his career.

30

Q U E S T I O N S

A N S W E R S

31