Monday, November 28, 2011

How to fix archive stuck error with Oracle 11gR2

Recently, I encountered an issue with Oracle R12 EBS environment unable to login to database tier with APPS schema with our Oracle 11gR2 database tier.

ORA-00257: archiver error. Connect internal only, until freed.

Sure enough, upon examination of the ADRCI alert.log entries, I found this:

* 2011-10-27 16:24:51.990 4265 krsh.c
ARC1: Error 19809 Creating archive log file to '+FRA'
*** 2011-10-27 16:24:51.990 2864 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
DDE: Problem Key 'ORA 312' was flood controlled (0x5) (no incident)
ORA-00312: online log 2 thread 3: '+DATA/vis/onlinelog/group_2.259.743840813'
ORA-00312: online log 2 thread 3: '+FRA/vis/onlinelog/group_2.259.743840815'

Solution:
The Fast Recovery Area (FRA) is full with old archive log files so we need to clean this up by removing old archive logs.
If you have production environment, back these up to tape or storage for recovery and archival purposes.

Login to RMAN and run the following commands:

RMAN> CONNECT target /

connected TO target DATABASE: INSTANT (DBID=1234567890)

RMAN> crosscheck archivelog ALL;

Note: You can schedule this command once a week.
The crosscheck command does NOT DELETE the information about the logs that it
could NOT find ON disk, it just updates their STATUS IN the repository AS 'EXPIRED'.
TO obtain a list OF logs marked AS 'EXPIRED' USE the following command:

RMAN> list expired archivelog ALL;

IF it IS NOT necessary TO keep the information about these
logs IN the repository, DELETE them WITH command:

RMAN> DELETE expired archivelog ALL;


RMAN> delete archivelog all completed before 'SYSDATE-1';

The above RMAN command will delete old archive logs past 24 hours.

Now you should be able to connect to the database.


Below are some useful queries to check on FRA available space:

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 "%" FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB %
------------------------------ ---------- ---------- ----------
DATA 823641 1044450 78.8588252
FRA 207743 511992 40.5754387
LOG 306958 307191 99.9241514


SQL>

set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/


NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
+FRA 102400 302965


If you want to change the default size for the FRA you can issue the following command from SQL*PLUS:

SQL> alter system set db_recovery_file_dest_size=600G;

System altered.

Then you should archive logs:
SQL> archive log all;
3 logs archived.

Sunday, October 16, 2011

My review Oracle OpenWorld 2011

Dear readers,

Oracle OpenWorld 2011 was a fantastic event with many new product announcements including Oracle Public Cloud as well as Oracle Big Data Appliances along with Exalytics for Business Intelligence. Before the conference, I had the pleasure of attending the Oracle ACE Director briefing at Oracle HQ in Redwood Shores under NDA to learn of new developments at Oracle.

After the ACED briefing, I headed to San Francisco with my fellow Oracle ACE Director, Arup Nanda and we checked into the Hilton Hotel. The computers failed during checkin so we had the keep the patience of a saint which paid off with a free complimentary full breakfast. My hotel room had the most amazing view of the entire San Francisco Bay area including views of the Golden Gate bridge and Alcatraz island!



My favorite part of the conference was meeting new people and the special interest groups (SIG) for virtualization with Oracle. I had a great time meeting a lot of Oracle gurus including Jonathan Lewis, Tim Gorman, Doug Burns, James Morle, Michael Abbey, and my co-author Syed Jaffar Hussein of my Oracle 11g RAC book as well as James and Kerry from PACKT Press. Some more cool things at OOW this year was the America's cup winning yacht:



This year we had a great time at OTN night and the appreciation event was wonderful with Sting in concert. Funny because it seemed like Sting was singing in slow motion on songs like Roxanne!



The lightshow with Sting was incredible!




On Tuesday, I delivered my session on database security and would like to thank the 400+ attendees who came to my session. I do hope it was informative and useful to your journey to implementing security for your Oracle environments. The weather was a bit challenging with lots of rain and cold this year.



Fortunately I had an umbrella to keep me dry.
I would like to thank Justin, Lillian, and Vikki for all their hard work to make the OOW conference and ACE briefing a total victory and success this year.

Wednesday, September 28, 2011

OpenWorld 2011

Dear readers,

It is rapidly approaching time again for one of the best technical conferences, Oracle OpenWorld!

Come learn how to secure your Oracle database environment with new features at my session next week.

Session ID: 02180
Session Title: Lockdown: Secure Your Database in a Day
Room: Moscone South- 303
Date/Time: 10/4/11, 11:45-12:45

I will also be hosting a book signing after the event in the Oracle bookstore at OpenWorld. Hope to see everyone there.

Cheers,
Ben

Tuesday, September 20, 2011

Howto- Install Oracle 11g Grid Control Enterprise Manager (OEM) Server

With the new features for Oracle 11g Grid Control Enterprise Manager (OEM), it is valuable tool now for managing large Oracle environments. I recall from Oracle 9i OEM days that it was a buggy tool and back then years ago, I used SQL*Plus and command line tools. However, in 11gR1, OEM has emerged as an excellent monitoring tool. I still use command line tools most of the time but for monitoring, its a great product. With that said, let's get started in building a new Oracle11gR1 Grid Control Enterprise Manager (OEM) environment on Linux!

Preparation

OEM 11g Grid Control server is a resource hog and requires A LOT more memory than 10g OEM Grid Control so make sure you allocate a new server with at least 16GB of RAM or you will have performance issues.

Summary of Steps

1. Download the OEM 11g Grid Control software from Oracle Edelivery or OTN site (otn.oracle.com)
2. Download Oracle Weblogic 10.3.2 Server

Note; DO NOT use later version of Weblogic as this will NOT work with setup for 11g Grid Control Server!

3. Download Java JDK version 1.6 or later
4. Download required packages for Linux


Setup Java and Weblogic:


1. install and configure java

export PATH=$PATH:/usr/local/jdk/jdk1.5.0_10/bin

2. install and setup oem grid database
In our case, we used Oracle 11.2.0.2 database for the repository with Grid Control

3. post-db config tasks

$ emca -deconfig dbcontrol db -repos drop -SYS_PWD -SYSMAN_PWD

Login to the OEM repository Oracle 11gR2 database
Make the following initialization parameter changes.

sqlplus / AS SYSDBA

ALTER SYSTEM SET log_buffer=10485760 SCOPE=SPFILE;
ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;

In another shell window, un-install the database control:

$ emca -deconfig dbcontrol db -repos drop -SYS_PWD oracle -SYSMAN_PWD oracle

STARTED EMCA at Sep 3, 2011 11:43:41 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: oem
Listener port number: 1521

----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Sep 3, 2011 11:43:54 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/oem/emca_2011_09_03_11_43_41.log.
Sep 3, 2011 11:43:54 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Sep 3, 2011 11:44:15 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Sep 3, 2011 11:45:23 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Sep 3, 2011 11:45:25 AM


4. Install weblogic server 10.3.2

$ java -d64 -jar wls1032_generic.jar


5. Apply patch for weblogic

Next we must apply the "WDJ7" patch to the installation by running the Smart Update utility.

Notes:

The installation will complete without this patch, so if you have any trouble you can ignore it.
Newer versions of the Smart Update utility no longer allow you to download patches using the utility itself. For the revised patch download instructions see MOS Notes 876004.1 and 1302053.1.

download patch from MOS site

unzip p9438213_10320_Generic.zip -d /u01/weblogic/utils/bsu/cache_dir

Execute the following command to apply the Weblogic patch with the BSU utility:

Syntax:
./bsu.sh -prod_dir= -patch_download_dir= -status=downloaded -view -verbose

For example:
./bsu.sh -prod_dir=/opt/bea/weblogic92 -patch_download_dir=/opt/bea/utils/bsu/cache_dir -status=downloaded -view -verbose

This is the syntax for the command to install a patch:

./bsu.sh -prod_dir=/u01/weblogic -patchlist=WDJ7 -verbose -install

This is the syntax for the command to check if the patch is installed:
./bsu.sh -prod_dir= -patch_download_dir= -status=applied -verbose -view

For example:
./bsu.sh -prod_dir=/opt/bea/weblogic92 -status=applied -verbose -view

$ cd /u01/app/oracle/Middleware/utils/bsu
$ sh bsu.sh
Update the patch client by clicking the "OK" button on the message dialog.


./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu -patchlist=4D53,NIXN,XLXA -verbose -install

./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu -patchlist=WDJ7 -verbose -install


Unable to apply patch for weblogic 10.3.2 server

Oh no Mr. Bill! We hit a Weblogic bug! Seems like the BSU utility does not play well with some online systems. So, we tried the following approach:

$ ./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu -patchlist=4D53,NIXN,XLXA -verbose -install


Encountered unrecognized patch ID: 4D53
Encountered unrecognized patch ID: NIXN
Encountered unrecognized patch ID: XLXA


$ ./bsu.sh -view -patch_download_dir=/u01/weblogic/utils/bsu/cache_dir -status=applied -verbose -prod_dir=/u01/weblogic/wlserver_10.3
ProductName: WebLogic Server
ProductVersion: 10.3 MP2
Components: WebLogic Server/Core Application Server,WebLogic Server/Admi
nistration Console,WebLogic Server/Configuration Wizard and
Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Serve
r,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC
Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic S
erver/WebLogic Server Clients,WebLogic Server/WebLogic Web S
erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog
ic Server/Workshop Code Completion Support
BEAHome: /u01/weblogic
ProductHome: /u01/weblogic/wlserver_10.3
PatchSystemDir: /u01/weblogic/utils/bsu
PatchDir: /u01/weblogic/patch_wls1032
Profile: Default
DownloadDir: /u01/weblogic/utils/bsu/cache_dir
JavaVersion: 1.6.0_14
JavaVendor: Sun


Let's Fix this issue with apply Weblogic Patch:

Solution: rename patch-catalog-

I found the answer on the OTN Oracle forums:

https://forums.oracle.com/forums/thread.jspa?threadID=1064396

Now, this is not supported by Oracle so you should not do this in a production environment without guidance of Oracle support! Since we are doing a test setup, we can experiment!


$ mv patch-catalog_15563.xml patch-catalog.xml
$ cd ..
$ ./bsu.sh -prod_dir=/u01/weblogic/wlserver_10.3 -patch_download_dir=/u01/weblogic/utils/bsu/cache_dir -patchlist=4D53,NIXN,XLXA -verbose -install
Checking for conflicts..
No conflict(s) detected

Starting installation of Patch ID: 4D53
Installing /u01/weblogic/utils/bsu/cache_dir/4D53.jar
Extracting /u01/weblogic/patch_wls1032/patch_jars/Bug9100465_1032.jar
Updating /u01/weblogic/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar
Old manifest value: Class-Path=
New manifest value: Class-Path=../../../patch_jars/Bug9100465_1032.jar
Result: Success

Starting installation of Patch ID: NIXN
Installing /u01/weblogic/utils/bsu/cache_dir/NIXN.jar
Extracting /u01/weblogic/patch_wls1032/patch_jars/bug8990616_1032.jar
Updating /u01/weblogic/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar
Old manifest value: Class-Path=../../../patch_jars/Bug9100465_1032.jar
New manifest value: Class-Path=../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar
Result: Success

Starting installation of Patch ID: XLXA
Installing /u01/weblogic/utils/bsu/cache_dir/XLXA.jar
Extracting /u01/weblogic/patch_wls1032/patch_jars/Bug9221722_1032.jar
Updating /u01/weblogic/patch_wls1032/profiles/default/sys_manifest_classpath/weblogic_patch.jar
Old manifest value: Class-Path=../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar
New manifest value: Class-Path=../../../patch_jars/Bug9221722_1032.jar ../../../patch_jars/bug8990616_1032.jar ../../../patch_jars/Bug9100465_1032.jar
Result: Success


Verify weblogic patch was installed successfully


Ok, lets verify again that the Weblogic Patch was applied:

$ ./bsu.sh -view -patch_download_dir=/u01/weblogic/utils/bsu/cache_dir -status=applied -verbose -prod_dir=/u01/weblogic/wlserver_10.3

ProductName: WebLogic Server
ProductVersion: 10.3 MP2
Components: WebLogic Server/Core Application Server,WebLogic Server/Admi
nistration Console,WebLogic Server/Configuration Wizard and
Upgrade Framework,WebLogic Server/Web 2.0 HTTP Pub-Sub Serve
r,WebLogic Server/WebLogic SCA,WebLogic Server/WebLogic JDBC
Drivers,WebLogic Server/Third Party JDBC Drivers,WebLogic S
erver/WebLogic Server Clients,WebLogic Server/WebLogic Web S
erver Plugins,WebLogic Server/UDDI and Xquery Support,WebLog
ic Server/Workshop Code Completion Support
BEAHome: /u01/weblogic
ProductHome: /u01/weblogic/wlserver_10.3
PatchSystemDir: /u01/weblogic/utils/bsu
PatchDir: /u01/weblogic/patch_wls1032
Profile: Default
DownloadDir: /u01/weblogic/utils/bsu/cache_dir
JavaVersion: 1.6.0_14
JavaVendor: Sun


Patch ID: 4D53
PatchContainer: 4D53.jar
Checksum: -653702491
Severity: optional
Category: Web App
CR/BUG:
Restart: true
Description: weblogic http client implementation, does not handle keepali
ve conf (http1.0)

Patch ID: NIXN
PatchContainer: NIXN.jar
Checksum: -1413651003
Severity: optional
Category: Core
CR/BUG:
Restart: true
Description: Connecting to wls with non-ssl IIOP port fails if the port i
s enabled on a server running without a non-ssl IIOP channel
.

Patch ID: XLXA
PatchContainer: XLXA.jar
Checksum: 1670664663
Severity: optional
Category: Web App
CR/BUG:
Restart: true
Description: Jsp compiler shouldn't recompile jsp page when page-check-se
conds is set to -1


Whew! Now, that we have Weblogic 10.3.2 installed and patched, we need to change settings and resize the OEM 11g Grid Control repository database.

Resize undo tablespace to 500G


To avoid an error during installation for Grid Control, you need to resize the undo tablespace.

select file_name from dba_data_files where tablespace_name ='UNDOTBS1';


FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oem/undotbs01.dbf



Then we alter the size of the undo tablespace file, set autoextend on and maxsize to unlimited:

SQL> select file_name from dba_data_files where tablespace_name ='UNDOTBS1';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/oem/undotbs01.dbf

SQL> alter database datafile '/u01/app/oracle/oradata/oem/undotbs01.dbf' resize 1500M;

Database altered.

SQL> alter database datafile '/u01/app/oracle/oradata/oem/undotbs01.dbf' autoextend on maxsize unlimited;

Database altered.


Start the OEM 11g Grid Control installation program with ./runInstaller


I will spare you the screenshots as these are available online in the Oracle 11g Grid Control installation documentation and also at Dr. Tim Hall's blog at http://www.oracle-base.com/articles/11g/GridControl11gR1InstallationOnOEL5.php

Once the Grid Control setup completes, take down the following information for reference:

1. Enterprise Manager Grid Control URL: https://oem:7799/em
2. Admin Server URL: https://oem:7101/console

The following details need to be provided during the additional OMS install:

1. Admin Server Hostname: oem
2. Admin Server Port: 7101


Special thanks to Dr. Tim Hall for his excellent blog spot and walk through on 11g Grid Control. As you can see, Oracle 11gR1 Grid Control is far more resource intensive to setup and run than previous versions.

Oracle 11gR1 Grid Control Enterprise Manager Collection fails

Recently we had issue with Oracle 11g Enterprise Manager (OEM) Grid control failing to collect up to data host data with following error from Grid Control:



This was a cryptic error and after research, we found the solution in the following My Oracle Support:

Note: 271598.1
Grid Agent Configuration: Certain Operations from Grid Console Fail With 'ERROR: NMO Not Setuid-root (Unix-only)'

So, I performed the following steps to resolve the issue with Oracle 11g Grid Control:

./emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.


Open a new shell window logged in as the root user:

# cd /home/oracle/agent11g/
# ./root.sh

As the oracle user, start the agent on the monitored host:

$ ./emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Starting agent ....... started.


Now comes the secret sauce or step that solves issue:


./emctl control agent runcollection rac1:host host_storage
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

In the above command emctl control agent runcollection hostname:host host_storage
you will update the details sent to the OEM 11g Grid Control server.


Perform upload to OEM 11g Grid Control Server:


$ ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Verify it has resolved issue. You will see the current host status as shown in the below example:






Stay tuned for more Oracle 11g Grid control tips. I will share my lessons learned next for the installation process with Oracle 11gR1 Grid Control Server.

Oracle 11g Agent silent installation with OEM 11g Grid Control

Dear readers,

After setup of a new Oracle 11gR1 Grid control Enteprise manager (OEM) server, I had to deploy agents to monitor the Oracle environments. Unfortunately, the mass push automation process via OEM 11g Grid Control kept failing and numerous support calls to Oracle Support alas yielded no success. I was able to quickly automate the process via silent installation and deployment of Oracle 11g agents with Grid Control 11g. Below is the process of steps to setup the Oracle 11g agents for Oracle 11g Grid Control:

1) Download and unzip the agent 11g software to hosts monitored. In my case, I needed to configure agents for Oracle 11g RAC on Red Hat Enterprise Linux 5 platform.

cd /home/oracle

unzip Linux_x86_64_Grid_Control_agent_download_11_1_0_1_0.zip

Configure response file for silent agent installation


Before we can kickoff the silent install for the 11g agent, you need to edit the response file called additional_agent.rsp located under the $AGENT_HOME/response directory as shown in my example below:

cd /home/oracle/linux_x64/response

Edit the file with editor of your choice, in my case, I used vi editor on Linux.

Since we don't want updates and don't care about My Oracle support details, we will comment out the email address specifics.

$ vi additional_agent.rsp

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
#DECLINE_SECURITY_UPDATES=
#MYORACLESUPPORT_USERNAME=
#MYORACLESUPPORT_PASSWORD=
#COLLECTOR_SUPPORTHUB_URL=

INSTALL_UPDATES_SELECTION="skip"
#STAGE_LOCATION=

#PROXY_USER=
#PROXY_PWD=
#PROXY_HOST=
#PROXY_PORT=


ORACLE_AGENT_HOME_LOCATION=/home/oracle

b_silentInstall=true
CLUSTER_NODES=rac1,rac2,rac3,rac4

#-------------------------------------------------------------------------------
OMS_HOST=oem
OMS_PORT=4900
AGENT_REGISTRATION_PASSWORD=yourpassword

Since we have an Oracle 11g RAC environment, we set the parameter in the response file for CLUSTER_NODES to the cluster RAC node names. If you do not have RAC, you can comment this parameter out. Make sure to set the correct hostname, OMS port number and agent registration password or your silent agent installation will fail.


Perform silent installation for Oracle 11g Agent with OEM Grid Control 11g


Navigate to the AGENT_HOME/agent directory

cd /home/oracle/linux_x64/agent

Execute the silent agent installer script with the correct options and parameters as shown in the below example:

./runInstaller -silent -responseFile /home/oracle/linux_x64/response/additional_agent.rsp \
-invPtrLoc /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc

Note: you must grant the correct location for the Oracle Inventory file (oraInst.loc) or the silent installation will fail for the agent.

Once you silent installation has succeeded you should see the following status:


Running Configuration assistant "Agent Configuration Assistant"

Performing free port detection on host=rac1
Performing targets discovery and agent configuration

Securing the agent

Starting the agent

Configuration assistant "Agent Configuration Assistant" Succeeded

AgentPlugIn:agent configuration finished with status = true

Running Configuration assistant "Agent Add-on Plug-in"

Configuration assistant "Agent Add-on Plug-in" Succeeded

Finally, we verify that we can show all of the agents deployed for the Oracle 11g RAC environment



$ cd /home/oracle/agent11g/bin

$ ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /home/oracle/agent11g/rac1
Agent binaries : /home/oracle/agent11g
Agent Process ID : 13181
Parent Process ID : 13157
Agent URL : https://rac1:3872/emd/main
Repository URL : https://oem:4900/em/upload
Started at : 2011-09-20 20:28:06
Started by user : oracle
Last Reload : 2011-09-20 20:28:06
Last successful upload : 2011-09-20 20:38:49
Total Megabytes of XML files uploaded so far : 15.68
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 31.34%
Last successful heartbeat to OMS : 2011-09-20 20:39:10
---------------------------------------------------------------
Agent is Running and Ready

The beauty is when you specify the RAC cluster nodes in the first response file as we did above, is that all of the cluster nodes will have the silent install agent performed at same time!

Application Tier crashes at 100% CPU load

We had an issue with our Oracle R12 EBS environment when the CPU spiked constantly at 100% when load testing with lots of concurrent active users.

After much research, it was determined to be caused by

Bug 6519700 ESC: CSE: R12SIP: 6513826 FRMWEB RUNAWAY PROCESS CONSUMING 100% CPU-MIDDLE TIER



The fix was to shutdown the apps middle tier server and configure the following forms environment variables:

FORMS_RECORD_GROUP_MAXFORMS_CATCHTERM


1) First we had to update the context file on the application middle tier as follows:

s_forms_catchterm=0

2) Since the FORMS_RECORD_GROUP_MAX is not a default variable in the app tier context file, you need to create a custom template as follows.

cd $AD_TOP/admin/template
mkdir custom
cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/template/custom/APPLSYS_ux.env

Edit the custom template file:

vi $AD_TOP/admin/template/custom/APPLSYS_ux.env

Set the environment variable FORMS_RECORD_GROUP_MAX to a high value such as 10000 or greater.

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

Save the file.

Run autoconfig utility for apps middle tier server.


Start app tier services and test.

Now we now longer have this issue.

Load Balancing Part I: Oracle R12 EBS and Oracle 11g RAC- Manual process

I had to configure an Oracle R12 (12.1.1/12.1.3) E-Business Suite and two node Oracle 11gR2 RAC environment recently to take advantage of load balancing.

However, instead of using a hardware load balancer such as Cisco ACE, we had to find a manual process to implement this goal.

First stop was to review the documentation listed below on My Oracle Support (http://support.oracle.com):

Note # 380489.1 Using Load Balancers with Oracle E-Business Suite Release 12
Note # 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite 12
Note # 226880.1 Configuration of Load Balancing and Transparent Application Failover
Note # 262298.1 Load Balancing Doesn't Balance the Number of Sessions Across All Available Nodes
Note # 887522.1 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained

Note #1080674.1 JDBC Drivers and Failover Mechanisms (FCF, TAF, SCAN )
Note #1100024.1 Master Note for Oracle JDBC High Availability Features
Note #247135.1 How to Implement Load Balancing With RAC Configured System Using JDBC

Now, based on my experience with load balancers and Oracle, you should in most cases take the default setup of running your application middle tier server nodes in the default servlet mode to work with Cisco ACE and other hardware load balancers.

There is no compatibility with socket mode for hardware load balancers after discussions with Oracle support, you MUST run in servlet mode for hardware load balancing.

With that said, you could also use a web cache 10gAS application server to use software load balancing instead of the expensive hardware load balancer.

Our challenge was to implement a pure software load balance setup with Oracle R12 EBS and 11gR2 RAC environment with the ability to run the application middle tier nodes in socket mode for performance and application compatibility reasons.

Configure application middle tier environment for load balancing

First, we had to edit the following context variables on the apps tier for load balancing using context editor in the Oracle Applications Manager (OAM):


s_tools_twotask=VIS_BALANCE
s_weboh_twotask=VIS_BALANCE
s_apps_jdbc_connect_alias=VIS_BALANCE


Make sure to run the Oracle R12 EBS autoconfig utility ($ADMIN_SCRIPTS_HOME/adautocfg.sh) after changing the above context variables in your app tier context file.

Note: take a backup of the context file before making changes in case you need to revert back to original configuration!


The above context variables for the apps tier should point to the load balance setup in tnsnames.ora on the two RAC cluster nodes as shown below:

VIS_BALANCE=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(ADDRESS=(PROTOCOL=tcp)(HOST=node1-vip)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=node2-vip)(PORT=1521)) )
(CONNECT_DATA= (SERVICE_NAME=VIS)
) )


So you will need to create the entry called VIS_BALANCE for example as shown in the above tnsnames.ora file for the app tier nodes and database tier RAC Nodes.

Configure listener.ora


Sometimes even if you setup load balancing correctly, the distribution will not be sent equally across the cluster nodes. To resolve this issue, there is an undocumented parameter you can set to avoid this problem in your listener.ora file.



vi listener.ora


PREFER_LEAST_LOADED_NODE_listener_name=OFF

Register listener and services


Now that we have completed these changes, we need to register the listener services for all nodes.

Then unset and reset the remote_listener parameter for node1.


sqlplus '/as sysdba'
alter system set remote_listener='' scope=both;
alter system register;
alter system set remote_listener='VIS_REMOTE' scope=both;
alter system register;

Stop and restart the database and listeners.

lsnrctl stop
lsnrctl start
lsnrctl reload


Verify that both listeners display both instances in the status and services output.
lsnrctl services
lsnrctl status

Since load balancing is a complex topic, we just scratched the surface here in this post to highlight a quick and easy way to implement manual load balancing at the database layer with Oracle R12 EBS and Oracle 11gR2 RAC.



Future post will cover how to load balance the application middle tier.

Oracle 11g agent fails to upload to Oracle 11g Grid Control

Recently, I built a new Oracle 11gR1 Grid Control Enterprise Manager (OEM) server at work to monitor our complex environments.

All worked fine with agents, OMS and Grid Control until the other day when the agent failed to upload the status to Grid control.

After checking status for the agent and trying to perform a manual upload as listed below,
I received the following errors:

"emctl upload failed: collection status disabled by upload manager"

So I checked on disk space and plenty was available on the filesystem where the agent was located.
After finding a hit on My Oracle Support (MOS) site under Doc ID 396238.1,

I reviewed the following parameters in the emd.properties file under the
AGENT_HOME/sysman/emd directory:

UploadFileSize
UploadMaxBytes
UploadMaxDiskUsedPct


These were set to 2048, 50, and 98 for UploadFileSize, UploadMaxBytes, and UploadMaxDiskUsed by default when I checked the emd.properties file.


Sure enough the default values were too small thus causing the agent upload to fail.

First, I shutdown the agent with the emctl stop agent command.

Next, I changed the parameters to larger values for size of files:

$ cd /home/oracle/agent11g/sysman/config
vi emd.propertiesUploadFileSize=4096


## The maximum number of megabytes(MB) the upload manager will support in the
# upload directory before temporarily disabling collections, logging and tracing
##

UploadMaxBytesXML=500

# The maximum amount (%) of disk space that can be used on the EMD filesystem
# before the following is disabled:
# - Collection of data (upload manager)
# - Logging and tracing
# - Diagnosability traces
#@description=
#@valueType=Int
#@LOV=#@default=98#

UploadMaxDiskUsedPct=99

Then I started the agent and performed manual upload to OEM Grid OMS server:

./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /home/oracle/agent11g/node1
Agent binaries : /home/oracle/agent11g
Agent Process ID : 16864
Parent Process ID : 16828
Agent URL : https://node1:3872/emd/main
Repository URL : https://oem:4900/em/upload
Started at : 2011-09-20 11:33:05
Started by user : oracle
Last Reload : 2011-09-20 11:33:05
Last successful upload : 2011-09-20 13:22:32
Total Megabytes of XML files uploaded so far : 6.27
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 5.30%
Last successful heartbeat to OMS : 2011-09-20 13:36:26
---------------------------------------------------------------
Agent is Running and Ready

$ ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully



Now it works and OEM Grid Control can receive the updates from the agent on the server monitored.Cheers,Ben

Thursday, July 28, 2011

Using PL/SQL and SQL to change profile options for Oracle R12 EBS

Recently I had to fix an unusual problem with Oracle R12 (12.1.1) E-Business Suite system after changing some default timeout parameters. Since I was unable to login to the Oracle Applications Manager (OAM) graphical interface and had weird errors below, I needed a way to switch these profile values back to the lower default values. I set the ICX timeout parameters (ICX: Session Timeout, ICX: Limit Time, and ICX: Limit Connect) way too high in our test environment to experiment with user logout issues. SO I had the below errors:

Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION.

Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at "

APPS.FND_SESSION_MANAGEMENT", line 1236 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421 ORA-06512: at line 1 has been detected in FND_AOLJ_UTIL.is_Valid_ICX.

Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.


Needless to say this was extremely frustrating problem to solve as the few hits on My Oracle Support (formerly Metalink) along with an SR to support were unable to provide me with the solution and root cause. Finally after some research and discussion with a very smart support manager from Oracle (thank you Warwick!), I used a SQL and PL/SQL script to reset the values to much lower timeout settings and then I cleared the web browser cache. This solved the issue.

Sometimes you may not want to login or be able to access the OAM browser due to such issues. In this case, you want to use a SQL and/or PL/SQL wrapper script to modify profile settings. Below is the script to check for current values of profiles within Oracle R12 EBS:


set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
spool profile_options.txt
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;


The above script queries the Oracle R12 EBS database for values against the FND_PROFILE tables in the APPS schema.
Ok so now that we have our settings for profiles, let's show how we changed these for ICX profile settings to provide the solution.


DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('ICX_LIMIT_CONNECT', '2000', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
/


To use the above PL/SQL script to change profile parameters, it uses the FND_PROFILE package for Oracle R12 EBS with the SAVE function within PL/SQL which in turn takes three parameters as shown by the package definition listed below:


SQL> desc FND_PROFILE

FUNCTION BUMPCACHEVERSION_RF RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SUBSCRIPTION_GUID RAW IN
P_EVENT WF_EVENT_T IN/OUT

FUNCTION DEFINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

FUNCTION DELETE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT

PROCEDURE GET
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 OUT

FUNCTION GET_ALL_TABLE_VALUES RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DELIM VARCHAR2 IN

PROCEDURE GET_SPECIFIC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_Z VARCHAR2 IN
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
VAL_Z VARCHAR2 OUT
DEFINED_Z BOOLEAN OUT
ORG_ID_Z NUMBER IN DEFAULT
SERVER_ID_Z NUMBER IN DEFAULT

FUNCTION GET_TABLE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
SITE_ID_Z NUMBER IN DEFAULT
PROCEDURE INITIALIZE_ORG_CONTEXT

PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 IN

PROCEDURE PUTMULTIPLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMES VARCHAR2 IN
VALS VARCHAR2 IN
NUM NUMBER IN

FUNCTION SAVE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT


FUNCTION SAVE_USER RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN

FUNCTION VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

FUNCTION VALUE_SPECIFIC RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
USER_ID NUMBER IN DEFAULT
RESPONSIBILITY_ID NUMBER IN DEFAULT
APPLICATION_ID NUMBER IN DEFAULT
ORG_ID NUMBER IN DEFAULT
SERVER_ID NUMBER IN DEFAULT

FUNCTION VALUE_WNPS RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN

In our above example, we gave FND_PROFILE.SAVE the required three parameters to set the profile values for ICX:Limit Connect based on time duration of 2000 which allows for up to a maximum of 2000 connections permitted in a single session. We want a large value so that the many batch processes and concurrent jobs processed by functional users can take place without terminating abnormally while processing such things as invoices and order shipments for Oracle Financials within the Oracle R12 E-Business Suite.

Tuesday, July 12, 2011

Add node fails due to time synchronization issue with NTPD and CTSSD

Today while checking the status of a four node Oracle 11gR2 RAC environment, I noticed something was wrong with time synchronization between the cluster nodes. Even though I had our system administrator configure NTPD for the environment, the Cluster Verification Utility (CVU) failed on NTPD errors and showed the Cluster Synchronization Services Daemon (CTSSD) in Observer mode when I ran a check of the clock synchronization:


oracle@rac1 ~]$ cluvfy comp clocksync -n all

Verifying Clock Synchronization across the cluster nodes

Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed

Checking if CTSS Resource is running on all nodes...
CTSS resource check passed


Querying CTSS for time offset on all nodes...
Query of CTSS for time offset passed

Check CTSS state started...
CTSS is in Observer state. Switching over to clock synchronization checks using NTP


Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
NTP Configuration file check passed

Checking daemon liveness...
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes

NTP daemon slewing option check failed on some nodes
Check failed on nodes:
rac1,rac2,rac3,rac4
PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"
Clock synchronization check using Network Time Protocol(NTP) failed

PRVF-9652 : Cluster Time Synchronization Services check failed

Verification of Clock Synchronization across the cluster nodes was unsuccessful on all the specified nodes.


Aha! So CTSSD must have an NTPD server in slewing option mode or it will fail to synchronize the cluster nodes correctly.


The solution to this is to shutdown the Oracle RAC database environment as well as to shutdown ASM and the clusterware and then to restart ntpd on the Oracle RAC cluster nodes and app tier server host with the –x option

Previous to this, I had verified that ntpd was running however it was started by default mode and not with the -x option as shown below:

[root@rac1 ~]# service ntpd status
ntpd (pid 24396) is running...
[root@rac1 ~]# ps -ef|grep ntpd
root 15495 8369 0 09:52 pts/1 00:00:00 grep ntpd
ntp 24396 1 0 Jul11 ? 00:00:00 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g

You can check to see if the -x flag has been set by examination of the /etc/sysconfig/ntpd file.

[root@rac1 ~]# grep OPTIONS /etc/sysconfig/ntpd
OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"
NTPDATE_OPTIONS=""

I found the following two My Oracle Support (http://support.oracle.com) notes useful while solving this issue with Oracle 11gR2 RAC and time synchronization issues.

MOS 1054006.1- CTSSD Runs in Observer Mode Even Though No Time Sync Software is Running
MOS 1056693.1- How to Configure NTP or Windows Time to Resolve CLUVFY Error PRVF-5436 PRV-9652

Logon failure to Oracle R12 EBS in 11gR2 RAC Environment and Solution

Yesterday while working on a four node Oracle 11gR2 RAC environment with Oracle R12 eBusiness Suite, I encountered a weird login authentication error when I tried to open a few forms to manage concurrent processing as system administrator:

"Cannot Complete Application logon. You may have entered an invalid applications password or there may have been a database connect error."

I checked the password by logging into the database tier with the apps account and that worked. Then I searched on My Oracle Support and found more clues to help me solve the issue.

Starting Forms Intermittently Raises "Cannot Complete Applications Logon" and "APP-FND-01496" Error When Using RAC [ID 753028.1]

Sure enough, when I logged into the Oracle 11gR2 RAC cluster nodes and checked for their time, they were out of sync! So, we had to configure ntpd (Network Time Protocol Daemon) and restart the cluster nodes. Here is what is puzzling- with 11gR2, there is a new process called the CTSSD or Cluster Time Synchronization Service Daemon which is supposed to replace the functionality of NTPD. However, you still need to run NTPD to have RAC function in 11gR2 without complaining. Also, NTPD is required to some extent to maintain a time synchronization with the Oracle RAC nodes. Once we re-synched the clocks for the Oracle 11gR2 RAC hosts, we were able to resolve the forms login errors with our Oracle R12 eBusiness Suite and RAC environment.

Data Recovery Advisor Feature for 11g Not Supported with 11gR2 RAC

Today while checking and validating our database backups for our Oracle 11gR2 RAC and R12 EBS Oracle environments, I wanted to test out the Data Recovery Advisor with 11gR2 RAC. Well, turns out, it is not supported! When you try to issue the DRA commands from RMAN, you get the following message:


[oracle@rac1 ~]$ rman target RAC1

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 12 14:09:39 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

target database Password:
connected to target database: VIS (DBID=296736794)

RMAN> list failure low;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/12/2011 14:09:50
RMAN-05533: LIST FAILURE is not supported on RAC database

RMAN> advise failure low;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of advise command at 07/12/2011 14:09:56
RMAN-05533: ADVISE FAILURE is not supported on RAC database

After checking this on My Oracle Support (http://support.oracle.com), I discovered that indeed, Oracle does not support their new feature for backup and recovery (Data Recovery Advisor) on RAC!

Now this is confusing because in the Oracle 11gR2 documentation, it specifies that DRA is supported by RAC when in fact, it is not supported and does not work!

Reference:
Documentation wrong with RAC and DRA - Data Recovery Advisor. (Oracle Database Administrator's Guide 11.2) [ID 1297071.1]

So folks, you cannot use the cool new 11g feature data recovery advisor with RAC! I was bummed to find this out as it is a cool feature and worked in 11gR1.

Wednesday, May 25, 2011

Oracle 11gR1/R2 RAC Essentials Book now available

My new Oracle 11g RAC book is now available from PACKT Press:

http://www.packtpub.com/oracle-11g-r1-r2-real-application-clusters-essentials/book



If you are an Oracle DBA or technical manager looking to implement and design an Oracle 11g RAC environment, then you will find this book critical to successful implementation!

Tuesday, March 29, 2011

Upcoming Events- Spring/Summer 2011

I will be presenting at a few conferences over the next several months:

Collaborate 11- April 12, 2011 for OAUG in Orlando, Florida
Lock Down! Secure your Oracle R12 E-Business Suite Environment in a week.
Event Type: White Paper / Standard Session

Session ID: 4908
Date: Tuesday, April 12
Time: 3:15 pm - 4:15 pm
Room: W304F
Speaker: Benjamin Prusinski

Description: This session will provide the audience with the technical guidance to implement security best practices for Oracle R12 E-Business Suite.

EMC World in May, 2011 in Las Vegas, NV

http://www.emcworld.com/
Oracle eBusiness Suite on RAC Sizing & Configuration on Vblocks Best Practice
This session will provide an overview of the Vblock Powered Solution for Oracle E-Business Suite and RAC. Participants will learn best practices for sizing and layout of Oracle E-Business and RAC database cluster on a Vblock Platform, how to dynamically scale out the Oracle E-Business application tier and RAC database cluster on a Vblock Platform, how to bi-directionally migrate an Oracle RAC database cluster from Physical to Virtual and how to build an inexpensive but effective high availability environment for Oracle E-Business stack on a Vblock Platform.

ODTUG Kaleidoscope in Long Beach, CA

http://www.kscope11.com/
Performance Tuning 101: Top Tips for Oracle 11gR2
Ben Prusinski
Session 17, 06/30/2011, 8:30 AM-9:30 AM
Learn how to use the new features in Oracle 11g Release 2 to optimize performance for data warehousing and transactional OLTP environments. We will show you some quick wins in terms of leveraging tools such as the new Results Cache and little known tools such as oradebug and lots more. Back to Top

I look forward to see you all at one of these events! Stay tuned for more exciting news coming soon.

Cheers,
Ben

Thursday, March 3, 2011

Annoying interruption problem solved for Oracle R12 E-Business Suite

I run most of my test environments using virtual servers with VMWare and Oracle Sun Virtualbox. As such the network connections use DHCP to access wireless internet access points. Recently a new issue came up that gave me hours of grief. After making some changes to my Oracle R12 context file, I was unable to get the middle tier application services started successfully. I received the following errors:

******************************************************
ERROR: Timed out (100000): Interrupted Exception
Your are running adoafmctl.sh version 120.6.12000000.2
Starting OPMN managed OAFM OC4J instance
*****************************************************


A quick search on My Oracle Support found similar issue in Note: 564536.1.
The note recommends changing the timeout parameters for oacore parameter
under the $ORA_CONFIG_HOME/10.1.3/opmn.xml file for the middle application tier server as well as changing the context file parameters s_oacoretimeout, s_formstimeout, and s_oafmtimeout for the middle tier application context file and running autoconfig on the middle tier. I tried this and unfortunately, it did not fix my problem.

So, upon further investigation, I found the solution given by My Oracle Support Note: 412980.1 Problem: oafm, forms, and oacore not starting after IP Address Change.

Aha! I thought since I use DHCP and virtual networks, this plays havoc on my Oracle R12 EBS virtual environments. The root cause of my problem and why the middle tier services failed to start is that the OC4J lock files store the old IP address which are incorrect after an IP address is changed by the DHCP and virtual network adapters whenever I travel to use different networks. To solve my problem, I had to perform the following tasks.

1. Take backup of current middle tier files
2. shutdown all current running middle app tier services with the adopmnctl.sh script
under the $INST_TOP/admin/scripts/home directory

3. delete the lock files under the $INST_TOP/ora/10.1.3/j2ee directories

[applmgr@tusebs scripts]$ echo $INST_TOP
/appstier12/CLONE/inst/apps/CLONE_tusebs
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oacore/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/oafm/persistence/*
[applmgr@tusebs scripts]$ rm -fr /appstier12/CLONE/inst/apps/CLONE_tusebs/ora/10.1.3/j2ee/forms/persistence/*

4. Run Autoconfig on the apps middle tier:
[applmgr@tusebs scripts]$ ./adautocfg.sh
Enter the APPS user password:

The log file for this session is located at: /appstier12/CLONE/inst/apps/CLONE_tusebs/admin/log/02101314/adconfig.log

AutoConfig is configuring the Applications environment...

AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /appstier12/CLONE/inst/apps/CLONE_tusebs
Classpath : /appstier12/CLONE/comn/java/lib/appsborg2.zip:/appstier12/CLONE/comn/java/classes

Using Context file : /appstier12/CLONE/inst/apps/CLONE_tusebs/appl/admin/CLONE_tusebs.xml

Context Value Management will now update the Context file

Updating Context file...COMPLETED

Attempting upload of Context file and templates to database...COMPLETED

Configuring templates from all of the product tops...
Configuring AD_TOP........COMPLETED
Configuring FND_TOP.......COMPLETED
Configuring ICX_TOP.......COMPLETED
Configuring IEO_TOP.......COMPLETED
Configuring BIS_TOP.......COMPLETED
Configuring AMS_TOP.......COMPLETED
Configuring CCT_TOP.......COMPLETED
Configuring WSH_TOP.......COMPLETED
Configuring CLN_TOP.......COMPLETED
Configuring OKE_TOP.......COMPLETED
Configuring OKL_TOP.......COMPLETED
Configuring OKS_TOP.......COMPLETED
Configuring CSF_TOP.......COMPLETED
Configuring IGS_TOP.......COMPLETED
Configuring IBY_TOP.......COMPLETED
Configuring JTF_TOP.......COMPLETED
Configuring MWA_TOP.......COMPLETED
Configuring CN_TOP........COMPLETED
Configuring CSI_TOP.......COMPLETED
Configuring WIP_TOP.......COMPLETED
Configuring CSE_TOP.......COMPLETED
Configuring EAM_TOP.......COMPLETED
Configuring FTE_TOP.......COMPLETED
Configuring ONT_TOP.......COMPLETED
Configuring AR_TOP........COMPLETED
Configuring AHL_TOP.......COMPLETED
Configuring OZF_TOP.......COMPLETED
Configuring IES_TOP.......COMPLETED
Configuring CSD_TOP.......COMPLETED
Configuring IGC_TOP.......COMPLETED

AutoConfig completed successfully.

5. Start application middle tier services and verify all is working now

[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps

You are running adstrtal.sh version 120.13.12000000.3

Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adopmnctl.sh start
script returned:
****************************************************

You are running adopmnctl.sh version 120.4.12000000.3

Starting Oracle Process Manager (OPMN) ...

adopmnctl.sh: exiting with status 0

adopmnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adopmnctl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adapcctl.sh start
script returned:
****************************************************

You are running adapcctl.sh version 120.6.12000000.2

Starting OPMN managed Oracle HTTP Server (OHS) instance ...

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adapcctl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoacorectl.sh start
script returned:
****************************************************

You are running adoacorectl.sh version 120.11.12000000.2

Starting OPMN managed OACORE OC4J instance ...

adoacorectl.sh: exiting with status 0

adoacorectl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoacorectl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adformsctl.sh start
script returned:
****************************************************

You are running adformsctl.sh version 120.12.12000000.3

Starting OPMN managed FORMS OC4J instance ...

adformsctl.sh: exiting with status 0

adformsctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adformsctl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adoafmctl.sh start
script returned:
****************************************************

You are running adoafmctl.sh version 120.6.12000000.2

Starting OPMN managed OAFM OC4J instance ...

adoafmctl.sh: exiting with status 0

adoafmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adoafmctl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adalnctl.sh start
script returned:
****************************************************

adalnctl.sh version 120.3

Checking for FNDFS executable.
Listener APPS_CLONE has already been started.

adalnctl.sh: exiting with status 2


adalnctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adalnctl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/adcmctl.sh start
script returned:
****************************************************

You are running adcmctl.sh version 120.14

Starting concurrent manager for CLONE ...
Starting CLONE_0210@CLONE Internal Concurrent Manager
Default printer is noprint

adcmctl.sh: exiting with status 0


adcmctl.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adcmctl.txt for more information ...


.end std out.

.end err out.

****************************************************


Executing service control script:
/appstier12/CLONE/inst/apps/CLONE_tusebs/admin/scripts/jtffmctl.sh start
script returned:
****************************************************

You are running jtffmctl.sh version 120.3

Validating Fulfillment patch level via /appstier12/CLONE/comn/java/classes
Fulfillment patch level validated.
Starting Fulfillment Server for CLONE on port 9300 ...

jtffmctl.sh: exiting with status 0


.end std out.

.end err out.

****************************************************


All enabled services for this node are started.

adstrtal.sh: Exiting with status 0

adstrtal.sh: check the logfile /appstier12/CLONE/inst/apps/CLONE_tusebs/logs/appl/admin/log/adstrtal.log for more information ...

[applmgr@tusebs scripts]$ $ADMIN_SCRIPTS_HOME/adapcctl.sh status

You are running adapcctl.sh version 120.6.12000000.2

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: CLONE_tusebs.tusebs.localdomain
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
OC4J | oafm | 25028 | Alive
OC4J | forms | 24953 | Alive
OC4J | oacore | 24866 | Alive
HTTP_Server | HTTP_Server | 24812 | Alive


adapcctl.sh: exiting with status 0

And Voila! Issue has been resolved and no more annoying interrupted exception!

Friday, February 25, 2011

Kick off event for San Diego Oracle Applications User Group (SDOAUG) in March 2011

As a new board member for the San Diego Oracle Applications User Group (SDOAUG) in San Diego, we are having a kickoff networking event in March.

On March 10th we are having our Annual Kickoff Networking Event at Karl Strauss Brewery from 4:30pm-7:30pm in Sorrento Valley. If you are not part of the SDOAUG membership list please send me your contact info or go to http://sdoaug.org/ to register yourself as a member to receive all future SDOAUG notifications.

Food, good beer and conversation will be flowing fast and steady so be sure to sign up and attend the premier kickoff event of the year for San Diego! Hope to see you all there!


Cheers,
Ben Prusinski, SDOAUG Events Chair, San Diego Oracle Applications User Group