Tuesday, December 28, 2010

How to list all default profiles for Oracle R12 EBS Account

While checking profile options set for the SYSADMIN account, I ran across this useful SQL script from

My Oracle Support Note 201945.1
How to list E-Business Suite Profile Option values for all levels using SQLPlus

Let's run the script logged into Oracle R12 (12.1.1) in our test instance with APPS schema and run the SQL script:

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'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(p.profile_option_name) in (
select profile_option_name from fnd_profile_options_tl
where upper(profile_option_name) like upper('%&profile_name%')
and upper(profile_option_name) in (select profile_option_name
from fnd_profile_options_tl
where upper(user_profile_option_name) like upper('%&user_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, user_profile_option_name, level_id, level_set;

The script will prompt you for the profile name which in our case is SYSADMIN to check for default profile settings.

Enter value for profile_name: SYSADMIN

------------------------------ ---------------------------------------- --------------- ------------------------------ ----------------------------------------
ADI_WHEN_SYSADMIN_OUTPUT_VIEW ADI: Allow Sysadmin to View all Output Site Y

The above script is very useful when reviewing security values and profile settings for Oracle R12 E-Business Suite accounts.

Some additional useful MOS notes and scripts:
Note: 201945.1 - How to list E-Business Suite Profile Option values for all levels using SQLPlus
Note: 282382.1 - How to Search all of the Profile Options for a Specific Value
Note: 367926.1 - How To Find All Users With A Particular Profile Option Set?

Tuesday, December 21, 2010

Changing Weblogic Admin Password

Recently, I completed a new installation for Oracle 11g Weblogic server as part of the Oracle 11g SOA Suite configuration. Unfortunately I forgot the password. Here is how to reset the password for the weblogic admin account with Oracle 11g Weblogic (10.3.3).

Step 1: Set the environment for your Weblogic domains:

In our case, we are running Linux so we change to the Middleware directory set by the MIDDLEWARE_HOME variable

$ cd $MIDDLEWARE_HOME/user_projects/domains/{your_domain}/bin
where your_domain is the domain_name for your Weblogic domain as shown in below example:

$ cd user_projects/domains/fmw_domain/bin

Execute the setDomainEnv.sh script

. ./setDomainEnv.sh

Step 2:Run the copy command before we change the admin password for weblogic

$ java -cp /weblogic1033/oracle/wlserver_10.3/server/lib/weblogic.jar:$CLASSPATH weblogic.security.utils.AdminAccount weblogic mypass1 .

where weblogic is the admin username and mypass1 is the new password

Step 3: Reset the weblogic admin password with the following command:

$ java weblogic.security.utils.AdminAccount weblogic mypass1 .

where weblogic is the admin user account and mypass1 is the new password. Don't forget the period at the end of the command or this will not work.

In the future, if you do not want to set a password for the weblogic admin user, you can modify the boot.properties file as discussed in the following My Oracle Support note:

Starting AdminServer Without Username/Password Prompt in WebLogic Server 10.3


SQL Developer

In the past as a DBA and developer, I used Quest TOAD on a regular basis. Now with the freely available SQL Developer tool from Oracle, there is the perfect solution for a robust and easy to use development and DBA tool.

Oracle SQL Developer now has the following utilities:

1. Data Modeler- instead of ErWIN you can do it from SQL Developer
2. SQL and PL/SQL Development
3. Third Party Database Migration tool- migrate from MySQL and DB2 or Microsoft SQL

4. DBA tools for monitoring user sessions, killing processes, etc.
5. Search feature for online Oracle Documentation

In my book on database migrations, Migrating to Oracle Expert Secrets for Migrating from SQL Server and MySQL from Rampant Tech Press, I discuss how to use the migration tools available in SQL Developer. Sue Harper of Oracle also has excellent discussions from the Oracle SQL Developer product management team. Let's take a quick peak into some of the new and useful goodies available in the latest version of SQL Developer - the swiss army knife of Oracle development tools!

The main window and menu has list of tools available:

I like the database copy utility when I need to clone a database fast:

Tools->Database Copy

Next up is the cool feature for data modeling. I will write more about this in a future blog post as the new data modeling tool merits a blog post of its own. You can generate new data models for physical or logical database designs or re-engineer a current database environment as well as gather DDL for the entire database or schemas.

The Unload Wizard is interesting tool which I plan to explore and test in the future. This allows you to dump data in various formats such as XML, PDF and flat file formats.

I also like the Diff Wizard tool to check for synchronization issues between database environments. I find this particularly useful after I perform a database refresh to a DEV environment from Production to ensure that the development database is in sync with production. SQL Developer provides a simple option as shown below to perform this task.

We can choose options for taking the diff in the databases

Once the report completes it shows the diff between the two databases:

Finally, with the DBA mindset, I like the monitoring capabilities to view sessions and trace expensive SQL statements. No longer must I use SQL scripts to view SQL statements and explain plans. Its so much easier as shown below.

And last but not least is the way cool search functionality for checking online Oracle documentation and even the Tom Kyte AskTom site:

This makes the latest version of the Oracle SQL Developer tool a one stop center for many development and DBA tasks and best of all, it is FREE!


Wednesday, December 1, 2010

Oracle R12 E-Business Suite video from OTN LAD Conference

My Oracle R12 E-Business Suite Performance Tuning video from the OTN LAD Conference is now available in Spanish.


Special thanks to fellow Oracle ACE in Peru- Miguel Palacios for the video.

Hope all Oracle users enjoy the video.


Sunday, November 7, 2010

OTN LAD Tour: Peru, Chile and Uruguay

Dear readers,

After an exciting trip to Peru, Chile, and Uruguay, I wanted to relay the wonderful experience that was the first ever OTN LAD Tour conference which spanned seven countries: Peru, Chile, Uruguay, Brazil, Colombia, Ecuador and Costa Rica. I want to extend my thanks to our most gracious hosts in these countries who not only worked hard behind the scenes to make this conference a success but welcome all of us Oracle ACEs to their countries with their hospitality!

Side trip before the conference.

The weekend before I arrived at the first conference, I went on a side trip to Cusco and Machu Picchu. Here is the map of the trip.

Needless to say, the adventure to Cusco and Machu Picchu was incredible! From stellar food at the Chicha restaurant in Cusco by famed Peruvian chef, Gaston Acurio to the magic of the Inka trail, there is no words to describe it.

Ceviche is a national dish of marinated seafood in lime juice and quite tasty!
Next up, I ordered the amazing anticuchos (beef hearts grilled to perfection) with various types of corn (maize) and potatos.

To reach the famed city of the Inca, you have to fly into Cusco and take a nice scenic four hour train ride to Aguas Calientes and then a bus ride to the base of Machu Picchu.

Once you arrive at Machu Picchu, its quite a hike up to the top! It was raining a lot and slippery which required a rain coat and extra care to avoid falling to one's death over the steep ledges. The views are simply fantastic and breathtaking!

To see this magic place requires several days to explore every incredible spot in the site!

I also went on a side tour around Cusco with fellow Oracle ACE Directors Hans Forbrich and Daniel Morgan who were gracious to arrange for the tour guide to show us around the fantastic wonders of Cusco. Below is a church that was built on an Incan site.


First, I met with fellow Oracle ACE Enrique Orbegozo who showed me a wonderful tour and genuine Peruvian hospitality by giving me a tour of Lima and excellent Peruvian cuisine.

Enrique was very gracious and took me to an incredible Peruvian folklore show that night.

PEOUG OTN Conference in Peru

After the amazing trip to Cusco and Machu Picchu, I flew back to Lima for the conference in Peru. Everyone was friendly, helpful and gracious.

Chile and Uruguay were wonderful experiences for me as well and from the bottom of my heart, thank you for your hospitality and it was a pleasure and honor to participate in the OTN LAD Tour.


Saturday, September 25, 2010

Open World Conference Part Two

Dear readers,

After my session for OOW, I had to run to a local clinic to receive my yellow fever vaccinations in preparation for my trip next month to South America for the Oracle OTN LAD conference Tour. It was painful to get these shots and my arm still hurts after it a few days later. I can only imagine how dreadful it would be to catch Yellow Fever and die from it. Anyways I went to some sessions, did work and networked with peers and new friends. On Wednesday night, there was a fantastic concert on Treasure Island with Berlin, Black Eyed Peas (BEP), Don Henley and Dave Miller band. I almost missed it because the queues for the bus were like 6 blocks long at the Hilton where I was staying! So after waiting for half an hour in futile desperation, I took a taxi to the concert on Treasure Island. Here is a good video of the Steve Miller Band:

Now Steve Miller is way beyond my time but he still rocks! I only wish that I had 1/10 of his guitar ability. It was crowded and food was near impossible to get without waiting for hours. So, since I ate already, I decided to grab some drinks and it was ironic how the food lines had thousands of people waiting but the lines for wine and beer were nonexistant! All in all a great time.

I did catch up with old friends like Charles Kim of Oracle MAA fame and Chet Justice.

I spent time camped out in the Zone for OTN lounge which this year was in a tent outside the Hilton. A couple years ago it was inside the Moscone Center. I liked the tent but wish it was inside the Moscone since making the trek from the tent on Mason to the Moscone was quite a walk and hard to make it to every session. Since I was multi-tasking in terms of doing work for a client, working on my presentations, and networking, the Zone Tent for OTN on Mason was my stomping grounds for most of OOW.

On Tuesday night, I went to the OTN night party at Yerba Buena gardens. It was ok but I almost was not able to get into the party as security guards were not educated on who to let in and keep out. Fortunately many of us bloggers had same problem and tweeted en masse and Justin saved the day and we were able to get into the OTN night party. Fortunately I wore a jacket as it was quite chilly that night.

I ran into Tim Hall quite a bit at the conference which was good since we both were not feeling that great and crashed a lot at the Zone OTN tent. Daniel Morgan and Hans Forbrich were around and we had some good chats in preparation for the OTN LAD conference trip. Ran into Richard Foote and Fritz Hoogland many times and these guys are great fun to chat with. Richard has the coolest Aussie accent and I always joke with him to bounce around like a kangaroo since he is from down under. Maybe I can index your foot sometime, Richard?

Of course no discussion would be complete without mentioning our new friend the Java Bean Bunny Guy! This cool dude was walking around the whole place waving and showing us the real power of J2EE.

My old iPhone died the weekend before OOW. So I bought a new iPhone4. Its great and does fantastic HD video and images. I look forward to using the Face time video chat once more folks have it.

Open World Conference 2010 Part One

Dear readers,

Oracle OpenWorld was fantastic this year. With the addition of JavaOne and Oracle Develop conference more than 40,000 people attended this year! It seems like OOW keeps getting bigger and better each year. Some good photos of the first day of the conference.

Larry Ellison gave a very technical keynote to open the conference which was much more focused on technical details than what he usually presents. Below is good video that I took for Larry Ellison's keynote on Cloud Computing.

As a new Oracle ACE Director, I enjoyed the briefing that I participated in at Oracle HQ before the conference. My thanks go out to Lillian, Vikki, and Justin for a fantastic job in taking great care of us Oracle ACEs and Oracle ACE Directors for the conference!

We also had a fantastic Oracle ACE cruise on Sunday evening with lots of good food, drinks and discussion.

I presented my session on Monday afternoon and we had a good turnout over 150 folks came to learn about Oracle R12 E-Business Performance tuning. For me, the best part of the OOW conference was sharing my technical knowledge with fellow Oracle professionals as well as networking with peers, seeing old friends again and meeting new ones. When folks came to me after my session to gave me excellent feedback it makes me happy that I am helping out fellow Oracle professionals with their technical challenges.

Monday, August 30, 2010

Announcements- OpenWorld and Oracle ACE Director Status

Dear Readers,

I have become an Oracle ACE Director!

Even more exciting news is that I will be presenting at Oracle OpenWorld this year. Below are the details:

Session ID: S319054

Title: Oracle E-Business Suite 12 Performance Tuning 101

In this session, learn how to approach performance tuning issues in both proactive and reactive modes for all components of the complex Oracle E-Business Suite 12 environment. Discover the latest performance tuning methods and tools for the application and database tiers with Oracle E-Business Suite. Learn how to tune the concurrent manager, application-tier components, the Java Virtual Machine (JVM), and database tier servers.

Speaker(s): Ben Prusinski, Ben Prusinski and Associates, President and Chief Architect

Track(s): Applications Tools and Technology, Oracle E-Business Suite
Schedule: Monday, September 20, 12:30 | Marriott Marquis, Golden Gate C2

Hope to see everyone there! I will also be answering your technical Oracle questions at the Oracle OTN ACE Lounge as well.

Thursday, August 19, 2010

Plagiarism and Oracle RAC book update

It has been found that Guenadi Jilevski has plagiarized dozens of others work on Oracle blogs and websites as shown below:

From the September 2009 archive, virtually all of the content has been pasted-in from other sources and represented as his own work.


Stolen from Martin Casterbach:




Stolen from Dr. Hall:




Stolen from Martin Casterbach:




Stolen from Martin Casterbach:




Stolen from Arup Nanda:




Stolen from Hall:




Stolen from Martin Casterbach:


Needless to say, I have ended all professional dealings with Jilevski. As for my new Oracle 11g RAC book with PACKT Press, my chapters are listed below:

Chapter 1: Introduction
Chapter 2: Architecture
Chapter 5: Managing and Troubleshooting Oracle 11g Clusterware
Chapter 14: Maximum Availability

Tuesday, August 17, 2010

Recent issue of blogs and copyright

Dear readers,

I recently had a major issue come to light when one of my co-authors for the newly released Oracle 11g Real Application Cluster Handbook with PACKT Press, Mr. Guenadi Jilevski, was taken to task for copyright infringement and plagiarism for Oracle content posted on his blog. This WRONG and against the law. Not only is plagiarism stealing, it is a violation of professional ethics. You cannot steal others work and call it your own original content! Furthermore, one must ALWAYS acknowledge and credit sources for published materials referenced as given by copyright laws and fair use laws unless it is one's original material.

Some background information on this matter came up when Jonathan Lewis approached me and Timur's blog discusses the matter here below:


Don Burleson also brought Jilevski's web content to my prompt attention:


Don Burleson then asked me if I was involved in the content posted on Jilevski's blog. My answer is this:

"No, I have no involvement whatsoever in the content posted by Guenadi Jilevski on his oracle blog. "

I just want to clear the air on this matter once and for all. US and international copyright law applies to both web and printed media as well as recorded media. You CANNOT take others content and cut and paste it to make it your own! This is stealing, plain and simple!

I have contacted Guenadi Jilevski to address this issue with all parties to make it correct if he has not credited sources of technical materials published. In addition, I have asked Jilveski to apologize for stealing others content. He needs to be taken to task for this behavior.

In summary, here is the fact about things with respect to writing and media usage of one's work and publications:

1) Please note that Copyright is NOT about copying without attribution, it is about NOT copying! That means, you cannot cut and paste others' diagrams, works and writings and claim it as your own work! You MUST use your own original work!


Tuesday, July 20, 2010

Webinar for Oracle R12 EBS Patching

Recently I gave a webinar for the LAOUC (Latin America and New Zealand/Australia Oracle User Community)on best practices for patching the Oracle R12 E-Business Suite environment.

Below is a link to the webinar for those of you who would like to better understand how patching works for Oracle R12 E-Business Suite environment.

Enjoy! Ben

Bug in Oracle 12.1.1 EBS Installer (RapidWiz)

While performing a fresh installation of the Oracle 12.1.1 E-Business Suite for the Linux platform, I discovered an interesting bug! The Rapid Wizard (rapidwiz) installer confuses the DBA by showing the incorrect version of the Oracle database software to be installed as part of the Oracle 12.1.1 EBS environment. Instead of showing the correct version for the Oracle 11gR1 database tier, it shows a 9i database!

I found this curious as I continued the installation for 12.1.1 EBS as it later on shows the correct database server version ( that will be installed for the database tier:

I wonder if Oracle EBS team has caught this bug or not? I will open a bug fix request to product development for the Oracle EBS team.


Tuesday, June 29, 2010

Part II: Setup Printers for Oracle E-Business Suite Release 12

In this post, we finish our printer configuration for the Oracle Applications R12 E-Business Suite environment. In the previous blog post, we discussed how to use the LINUX tools to configure printers at the operating system level. In this post, we finish the printer configuration by showing you how to setup the printer in the Oracle R12 E-Business Suite environment within the Oracle Applications Manager (OAM) graphical interface.

The first step is to login to the OAM environment as the SYSADMIN user and to navigate to the System Administrator responsibility.

Now we need to setup the printer by register the device.

We enter the settings for the printer in the flexfields above.
As such you need to enter the values for the printer driver and print type.

We enter the values for the HP Laserprinter below

Next we need to configure the HP printer with the correct driver as shown below.

As part of the print driver configuration for the Oracle R12 EBS environment,
we have additional print considerations. For example, the Driver Method comes into play
depending on the command, program or subroutine used with the printer.

We also have Driver Method Parameters that allow us to spool file or generate
print output to standard input as shown below.

OK so now that we have configured our printer, we need to run some post-configuration tests to verify that it works. Fortunately, Oracle EBS provides a suite of utilities and diagnostic tests for us with Release 12.

To run these tests, you need to switch to the System Administration responsibility for OAM and navigate to Diagnostics-> Diagnostic Tests as shown below.

Under the basic tab for the Application Object Library, select RunALL
to generate diagnostics for the environment.

We have covered the basics of printer setup and for future reference, Oracle provides many excellent tips on advanced configuration such as PASTA setup with Oracle R12 EBS.

Below are some excellent references from My Oracle Support (MOS) formerly known as Metalink (http://support.oracle.com)

Pasta Overview Note: 420019.1

Note 356501.1 "How to Setup Pasta Quickly and Effectively".

Note 269129.1 "How to Implement Printing for Oracle Applications: Getting Started" for general driver information.

Note 365111.1 "How to Setup Pasta for PCL Based Printers "

Note 356501.1 "How to Setup Pasta Quickly and Effectively",

Once you run the test for the pasta printer, Oracle Diagnostics will generate an HTML file report to display the configuration details and status report for the printers.

My Oracle Support (MOS) Technical References

NOTE:356501.1 - How to Setup Pasta Quickly and Effectively
NOTE:728077.1 - How to Setup IX Library Quickly and Effectively
NOTE:365111.1 - How to Setup Pasta for PCL Based Printers
NOTE:189708.1 - Oracle Reports 6i Setup Guide for Oracle Applications 11i
NOTE:240864.1 - Activating and Configuring IX Library
Note 60936.1 Step By Step Guide to Set Up a Printer in Oracle Applications
Note 99495.1 Oracle Applications Postscript Printing
Note 112172.1 Oracle Applications Character Printing
Note 152285.1 Building a Printer Initialization String for Oracle Applications
Note 106186.1 How to Test an Initialization String Outside of Oracle Applications
Note 1014599.102 How to Test Printer Initialization Strings in Unix

Printer Configuration for Oracle R12 E-Business Part I: Preparation

In this blog series, we will cover how to setup and configure printers with the Oracle R12 E-Business Suite. As a systems applications DBA, we are tasked with the challenge of printer configuration. Often this is an overlooked item before Go-Live which severely impacts the project success because if the financial analyst or CFO cannot print invoices or financial reports, they will be unable to perform essential tasks even in this Green Day of paperwork reduction.

Part 1: Printer Setup Preparation
Part 2: Configure Printers with R12 E-Business Suite

Ok lets get started. We need to first prepare the operating system to accept our printers for usage before R12 EBS will be able to use the new printer. We will cover how to configure the Linux and UNIX operating system by using the lp facility with CUPS for printing. For reference with HP Printers for Linux, we download the software from


Another good reference site for Linux drivers is the open source site


We go to site below


Download printer drivers for Linux

Our first step is to download the correct printer driver which in our case happens to be an HP Laserjet printer. We download this from the HP site listed below.

We follow the prompts and download the driver.

Once we have downloaded the print driver, we are ready to install it.
HP uses a download manager for Linux so we choose the Linux version and download it.

Good now we just need to install the software for Linux to configure our HP Laserjet printer. Once we have installed the driver we can run the CUPS utility for Linux to setup our new printer.

Next we need to check the status for the Linux/UNIX based CUPS print server

[root@tusebs etc]# /etc/init.d/cups status
cupsd (pid 5398) is running...

To add the new printer to the LINUX environment, you can use the web gui tool to setup new printer device for LINUX. Open a browser to:


The CUPS printer utility will show up as shown below.

We use this excellent browser based tool to add, remove, adminster and monitor printer configurations for LINUX.

So now we need to add the printer to our environment with the tool.

Click the button in the tool for Add New Printer

Then click on the continue button.

After entering the printer name, you need to enter the device mapping for it.
For example, we used LPD/LPH host or Printer Jet Direct driver but you can also choose from other device mappings such as IP address for the device configuration and
then click continue.

Now we need to enter the device URI for our printer which
in our case, since our HP printer has a Jet Direct card installed so we use this option and enter the socket URI mapping.

Next we click on the continue button. Now you will need to enter the make which is HP in our case and then continue. You can also use a PPD file if you one.

Enter the model/driver for your printer and continue.
Then click on the button to Add the printer.
Once the printer has been added to your LINUX configuration, you can verify status in the printers section of the tool. Make sure to use the concurrent processing server as the focal point for the printer setup and configuration.

Now we are ready to add our printer to R12 EBS environment!
Stay tuned for part II where we use the Oracle Application Manager (OAM) to install and configure our new printer for Oracle R12 EBS.