Using R12 adctrl utility for managing jobs

The Oracle E-Business Suite provides a comprehensive suite of DBA utilities that every Apps DBA needs to master to be an effective EBS Applications DBA. In this blog, I will cover the useful and often misunderstood utility called ADCTRL. The adctrl utility is a key utility in the toolkit of every veteran Oracle Applications DBA professional. We will use 12.0.6 release for R12 Oracle E-Business Suite.

ADCTRL is used to manage worker processes that perform a pivotal role in the function
of batch processsing for patching and many other Applications activities.

ADCTRL is located under $AD_TOP/bin directory with Oracle E-Business Suite.

$ cd $AD_TOP/bin

To begin a new adctrl session, you enter the adctrl at a command window prompt:

$ adctrl

Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA

AD Controller

Version 12.0.0

NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.

Your default directory is '/d1/bin/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?

AD Controller records your AD Controller session in a text file
you specify. Enter your AD Controller log file name or press [Return]
to accept the default file name shown in brackets.

Filename [adctrl.log] :

************* Start of AD Controller session *************
AD Controller version: 12.0.0
AD Controller started at: Wed Sep 23 2009 22:25:12

APPL_TOP is set to /EBS121/bin/apps/apps_st/appl

Reading product information from file...

Reading language and territory information from file...

Reading language information from applUS.txt ...


Enter the ORACLE username of Application Object Library [APPLSYS] : apps

Enter the ORACLE password of Application Object Library [APPS] :

AD Controller is verifying your username/password.
The status of various features in this run of AD Controller is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE No 1 -1 Y N N Y N N
PREREQ No 6 -1 Y N N Y N N
CONCURRENT_SESSIONS No 2 -1 Y Y N Y Y N
PATCH_TIMING No 2 -1 Y N N Y N N
PATCH_HIST_IN_DB No 6 -1 Y N N Y N N
SCHEMA_SWAP No 1 -1 Y N N Y Y N
JAVA_WORKER No 1 -1 Y N N Y N N
CODELEVEL No 1 -1 Y N N Y N N



AD Controller Menu
---------------------------------------------------

1. Show worker status

2. Tell worker to restart a failed job

3. Tell worker to quit

4. Tell manager that a worker failed its job

5. Tell manager that a worker acknowledges quit

6. Restart a worker on the current machine

7. Exit

After you have entered the APPLMGR username and password the above menu will open for ADCTRL.

Here we have 7 options.

You can start, stop and monitor the workers. Lets monitor status of workers with option 1:


Enter your choice [1] :


Control
Worker Code Context Filename Status
------ -------- ----------------- -------------------------- --------------
1 Run Generic R120 BOMFMCPY.pll Running
2 Run Generic R120 BOMFMMDE.pll Running
3 Run Generic R120 BOMFMOPR.pll Running
4 Run Generic R120 BOMFMREV.pll Running
5 Run Generic R120 BOMFMCON.pll Running
6 Run Generic R120 BOMFMBM2.pll Running
7 Run Generic R120 BOMFMBM3.pll Running
8 Run Generic R120 BOMFMBM1.pll Running

Undocumented Option 8:


This will allow you to continue processing a job and kill the adworker that failed.


Back at the main adctrl menu choose option 8:

Enter your choice [1] : 8

Enter the worker number(s)/range(s) or 'all' for all workers,
or press [Return] to go back to the menu :

To select the "Skip worker" option, type the number 8 and
press Return.

This can be useful in testing a patch and you need to let the patch or job run to completion.



ADWORKER LOGFILES for R12 EBS

How to find the log files for these workers? It is confusing to the newbie.

cd $APPL_TOP/admin/{ORACLE_SID}

Example:

apps@sandiego:/d1/bin/apps/apps_st/appl/admin
$ cd $APPL_TOP/admin/VIS/log

to find the adworker log files we do a ls command:

apps@sandiego:/d1/bin/apps/apps_st/appl/admin/VIS/log
$ ls -ls adw*.log
45871 -rw-r--r-- 1 apps dba 46934789 Sep 23 22:19 adwork001.log
25974 -rw-r--r-- 1 apps dba 26560214 Sep 23 22:19 adwork002.log
26037 -rw-r--r-- 1 apps dba 26637070 Sep 23 22:19 adwork003.log
25251 -rw-r--r-- 1 apps dba 25824196 Sep 23 22:18 adwork004.log
16379 -rw-r--r-- 1 apps dba 16750699 Sep 23 22:18 adwork005.log
17120 -rw-r--r-- 1 apps dba 17510037 Sep 23 22:18 adwork006.log
15612 -rw-r--r-- 1 apps dba 15965759 Sep 23 22:18 adwork007.log
16177 -rw-r--r-- 1 apps dba 16543829 Sep 23 22:18 adwork008.log

For each adworker process, a log file is generated that shows activity and errors if any. Lets examine one of these log files:


************* Start of AD Worker session *************
AD Worker version: 12.0.0
AD Worker started at: Fri Sep 18 2009 16:33:10

APPL_TOP is set to /d1/bin/apps/apps_st/appl

Worker id = 1
Started by: adpatch

NLS_LANG value from the environment is : AMERICAN_AMERICA.UTF8
NLS_LANG value for this AD utility run is : AMERICAN_AMERICA.UTF8

Connecting to SYSTEM......Connected successfully.

Already asked for the ORACLE username and password
of Application Object Library.
(The given username/password was: APPLSYS/*****)

Connecting to APPLSYS......Connected successfully.

The status of various features in this run of AD Worker is:

<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y


By understanding where to find the adworker log files you can troubleshoot E-Business issues quickly and become a better DBA.

Stay tuned as we cover more Apps DBA tips in future posts!

Cheers,
Ben

Tips and Tricks for Installing Oracle R12 E-Business

Dear readers,

I will present a brief overview of tips for monitoring the installation of an R12 E-Business environment for Oracle Applications. For our example, we installed a fresh new Vision instance of Oracle Applications (12.0.1) running under Oracle Enterprise Linux 5.2 (32 bit). During an installation, Oracle shows a basic splash screen:



Ok thats nice but still hides much of the actual work being performed under the covers. How do we troubleshoot the installation process? Oracle writes multiple log files during each step of the process for R12 to several directories:

Database Logfile ($ORACLE_HOME):
/d01/oracle/VIS/db/tech_st/10.2.0/appsutil/log

Apps Tier Logfile ($APPL_TOP):

/d01/oracle/VIS/inst/apps/VIS_bensr12/logs



R12 has several new changes from 11i Applications, one is the new instance directory
or INST_TOP directory.







We had a file access permissions problem during the install as reviewed by the output of the logfile:



Once we fixed it and granted correct permissions to it, we were able to complete the installation. Once the installation completes, R12 completes a series of post-install checks to verify all is correct. If anything fails, the Rapid Wizard installer throws an error with a red checkmark on items that have errors.



So, how do we fix this error? We have to bounce the apps tier and it solves the login page error. To do so, we use the scripts adstpall.sh and adstrtal.sh



Once the apps middle tier is back online, we can now access the login page for R12:



and once we sign in as sysadmin we are ready for training and testing with our new R12 Vision instance:



Stay tuned for upcoming tips with patching and concurrent manager tips with R12.

Fun and Games with OEL 5 Linux missing packages

Dear readers,

Recently as of yesterday I decided to do a new R12 EBS install this time with a Vision Demo instance to have a new EBS environment with fresh seed data to test our new features and for GRC (Govern Risk Control). I ran up2date oracle-validated to install the missing rpms and most of the R12 install went fine and took 9 hours!

However, after the install, I noticed that the listener and install did not succeed and found root cause was that some of the OEL Linux rpms failed to all download and install earlier. As shown below this is caused by kernel dependency problems:




Unfortunately in my enthusiasm and also due to the wee hours to get R12 setup, I did not realize my oversight. Then when R12 failed to start with listener errors and library missing errors and after search on Metalink, it dawned on me the issue was indeed these missing Redhat 5 packages. Oracle Enterprise Linux really is just Red Hat Linux kernel.

To resolve problems with the RPMs that failed to download and install correctly from the Oracle Unbreakable Linux Network (ULN), I had to run the up2date-nox --configure command and choose option 19 to bypass the kernel dependency warning.




We need to clear these warning messages out so that we can re-run up2date and download and install the missing Linux rpms.




And voila! Just like magic, we were able to download these key packages for Linux
so we can complete the R12 EBS installation.

Oracle 11g Release 2 Now Available

Oracle 11g Release 2 has been announced and available for download:

http://www.oracle.com/technology/software/products/database/index.html

As a beta tester for the Oracle 11gR2 team, I am excited and look forward to implementing many of the new features for 11gR2 with customers. Many new whitepapers are available at following sites:

http://www.oracledatabase11g.com/Authentication/Login_w.html?url=/?src=6811170&Act=28

I will be blogging on the many new features for 11gR2 in addition to Fusion Middleware and R12 EBS topics this year.

Cheers,
Ben

DBA Quick Tip of the week: Measuring Redo Generated

One topic that DBAs frequently need to assess when planning for new projects such as 11g Data Guard and 11g Streams is how to calculate redo generated by an Oracle database. This is key to understand because redo generation affects throughput and performance with both standby databases and replication based activities. I am going to show you a quick and dirty way to calculate the amount of redo generated by an Oracle 11g database. To do so, you will need to run a new 11g Automatic Workload Repository (AWR) report either from Oracle Enterprise Manager or via the awrrpt.sql script.
Second, your database must currently be licensed to use the AWR


Now lets examine our 11g AWR report:

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
BEN11G 3769640271 ben11g 1 18-Jun-09 15:17 11.1.0.6.0 NO

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
sandiego.localdo Linux IA (32-bit) 1 .99

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 24 04-Aug-09 12:55:42 37 1.2
End Snap: 30 07-Aug-09 18:11:18 36 1.0
Elapsed: 4,635.59 (mins)
DB Time: 1.47 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 76M 76M Std Block Size: 8K
Shared Pool Size: 156M 156M Log Buffer: 5,988K


Scroll down to the following section of the AWR report:

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.0 0.0 0.00 0.03
DB CPU(s): 0.0 0.0 0.00 0.03
Redo size: 62.9 5,874.9
Logical reads: 1.2 113.1
Block changes: 0.3 31.5
Physical reads: 0.0 2.0
Physical writes: 0.0 2.0
User calls: 0.0 1.0
Parses: 0.1 9.9
Hard parses: 0.0 0.1
W/A MB processed: 8,664.4 808,959.0
Logons: 0.0 0.3
Executes: 0.2 18.8
Rollbacks: 0.0 0.0
Transactions: 0.0


Under the heading for Load Profile generated by the AWR, you can examine redo size generated by second, per transaction, per execution or per system call. So in our example above, we had a grand total of 62.9K of redo per second. We can correlate this to the V$ data dictionary performance views with the following SQL*PLUS script that queries against the V$LOG and V$DATABASE performance views:


SELECT Start_Date,
Start_Time,
Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
2) AS Mbytes,
Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time,
'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time,
'HH24') || ':00' AS Start_Time,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,
'YYYY-MM-DD'),
To_Char(Vlh.First_Time,
'HH24') || ':00') Log_Hist,
V$log Vl,
V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date,
Log_Hist.Start_Time;



START_DATE START NUM_LOGS MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2009-03-21 15:00 8 400 BEN11G
2009-03-21 16:00 1 50 BEN11G
2009-03-22 10:00 1 50 BEN11G
2009-03-23 17:00 4 200 BEN11G
2009-03-25 21:00 2 100 BEN11G
2009-03-26 13:00 2 100 BEN11G
2009-04-06 22:00 1 50 BEN11G


We can then take the number of redo logs from the NUM_LOGS column of the above query and multiply by size of each online redo log (ORL) file.


SQL> select group#, members, bytes, archived, status
2 from v$log
3 ;

GROUP# MEMBERS BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 52428800 YES INACTIVE
2 1 52428800 NO CURRENT
3 1 52428800 YES INACTIVE


I will leave the math calculations up to you.

For those of you who cannot afford the AWR licensed option for 10g/11g, in this down economy, I will provide you with a low cost low tech way to figure out how much redo is generated by Oracle.

Another method to calculate redo generated for Oracle is to examine the pattern of log switches that occur within your Oracle database. Upon examination of your alert.log file, you can take a differential of log switches to assess exactly how much redo is generated at peak and idle times.

First lets perform some log switches in our test 11g database!

SQL> alter system switch logfile;

System altered.


Note: we are using 11gR1 on Oracle Enterprise Linux 5.2 for the examples here. So we start a new ADRCI session

[oracle@sandiego ~]$ adrci

ADRCI: Release 11.1.0.6.0 - Beta on Fri Aug 7 18:41:22 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show alert

Choose the alert log from the following homes to view:

1: diag/rdbms/ben11g/ben11g
2: diag/clients/user_oracle/host_3399978961_11
3: diag/clients/user_unknown/host_411310321_11
4: diag/tnslsnr/sandiego/listener
Q: to quit


2009-08-07 18:45:58.653000 -07:00
Thread 1 advanced to log sequence 21
Current log# 3 seq# 21 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:03.171000 -07:00
Thread 1 advanced to log sequence 22
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete
Current log# 1 seq# 22 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log
2009-08-07 18:46:05.556000 -07:00
Thread 1 advanced to log sequence 23
Current log# 2 seq# 23 mem# 0: /u01/app/oracle/oradata/ben11g/redo02.log
2009-08-07 18:46:07.060000 -07:00
Thread 1 advanced to log sequence 24
Current log# 3 seq# 24 mem# 0: /u01/app/oracle/oradata/ben11g/redo03.log
2009-08-07 18:46:09.209000 -07:00
Thread 1 advanced to log sequence 25
Current log# 1 seq# 25 mem# 0: /u01/app/oracle/oradata/ben11g/redo01.log



So you can add up the total number of logs between log switches and divide by total time to obtain a rough estimate.

By understanding how to calculate redo generation, you will be prepared to understand one key component of planning for your Data Guard, Streams or replication environments as this will give you some idea on how much network bandwidth you will require to efficiently move the redo logs between source and target databases.



UPDATE:


Fellow Oracle professional, Coskan Gundogar provided a SQL script to examine redo log generation. Here is the sample output:

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 9 17:06:48 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select trunc(first_time),sum(blocks*block_size)
from (select distinct first_change#,first_time,blocks,block_size,completion_time
from v$archived_log)
group by trunc(first_time)
order by trunc(first_time);

TRUNC(FIR SUM(BLOCKS*BLOCK_SIZE)
--------- ----------------------
26-MAR-09 51228160
06-APR-09 44119040
18-JUN-09 35488768
07-AUG-09 51233280

The primary difference in this script is that it queries redo generation from the V$ARCHIVED_LOG dynamic performance view rather than the earlier script that queries the V$LOG_HISTORY and V$LOG dynamic performance views.

So there you have it, multiple ways to calculate exactly how much redo is generated by your Oracle database! Stay tuned, next time we will provide some quick tips on tuning network and disk I/O for your Oracle database.

Cheers,
Ben

New Books on SOA and Oracle technology from PACKT Press

As I am writing a new book for Oracle 11g RAC, my new publisher asked me to review new Oracle books on SOA technology. Below are exciting new titles from PACKT Press.


SOA Developer Suite Guide




The SOA Developer Suite Guide provides a comprehensive introduction to deploying robust SOA architectures with the Oracle SOA Suite and BPEL language. Since many clients use or plan to deploy SOA, I find this useful as a DBA and Oracle consultant to quickly learn how to architect elegant solutions with SOA and Oracle technology.




SOA Cookbook





I like this book as I am new to many of the SOA developer tools from Oracle including the BPEL language. This guide is excellent primer for learning how to implement and deploy SOA with Oracle! The hands on approach is excellent for Oracle developers who need to ramp up quickly with creating new SOA architectures.


BPEL Cookbook






Next we have a cookbook for SOA technology with Oracle. The book BPEL Cookbook is great for learning techniques to develop robust BPEL programs for SOA implementations with Oracle.



Open LDAP Developer Guide




LDAP is a network methodology for deploying elegant network solutions with many Oracle Fusion Middleware products
This guide provides a nice solid foundation for Oracle identity management and security professionals who need to understand LDAP concepts for deployments.


Finally, I am reviewing a book on Oracle Hyperion Essbase which is complex OLAP business intelligence technology in use by many large companies these days that deploy E-Business and data warehouse environments.

Oracle Hyperion Essbase 9 Implementation Guide

Voyage to the Mothership- Oracle HQ

It is intermission time so grab your popcorn, in our break from a pure tech topic, I want to blog on my amazing trip to the mothership, that is to Oracle headquarters in Redwood Shores! In my 12+ years of working on Oracle databases, this was my first pilgrammage to visit the campus and HQ for Oracle!






I have always wanted to take a trip to where it all began.




As an Oracle Professional, I have interviewed many times with Oracle but unfortunately it either was not the right fit or recently a hiring freeze nixed my efforts to join Oracle. It really is luck and timing. It really is about good fortune, timing and knowing the right people at the right time in life.

I finally finished my third book on Oracle database technology. What led me to the visit to Oracle HQ you might ask? I went to a session on Oracle R12: it was fun and helped to refresh my EBS DBA skills on patching, cloning, and managing Oracle 12i or R12 EBS environments. I met some cool people most of the class works for Oracle support! It was fun! And I even managed to fix my errors in my VMWare configuration for R12 EBS on Oracle Enterprise Linux! Had to fix routing and network configuration issues then had to stop and restart the apps tier services. And voila! Now my VMWare works for R12! Woohoo!

I can say this: if you have the golden opportunity to visit Oracle HQ: take it! Oracle employees at HQ in Redwood Shores are blessed to work on one of the nicest corporate environments. Period. From the amazing fitness center with classes, gym, and Olympic swimming pool to amazing gourmet meals served at the many cafes on campus at budget prices (I had a grilled NY strip steak for less than $8!), people seem happy to work for and at Oracle! That explains why Oracle DEMANDS Ivy League pedigrees from many potential employees such as Stanford. I ran into Julian Dyke while learning new features for R12. Such a great RAC expert and very nice guy. Funny the people we run into by chance. Now, I did a session at another Oracle center in Los Angeles and in stark contrast, the center was NOT as nice as Oracle HQ! Views of Barbie and Mattel do not compare to the serenity of Oracle HQ!




Besides the 20 foot parking structure with Ken and Barbie, I really was not impressed by El Segundo, California and the one floor that Oracle training occupies there. But, a short 2 hour drive was more convenient from San Diego than the flight required to SFO for my visit to Oracle HQ. Was it worth it? Heck yeah, and if I am lucky to meet Larry someday and can pick his brain for a few minutes, perhaps I can make the planet a better and happier place with less suffering,