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,
decode(v.level_id,
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',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'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

SHORT_NAME NAME LEVEL_SET CONTEXT VALUE
------------------------------ ---------------------------------------- --------------- ------------------------------ ----------------------------------------
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?

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:

980292.1
Starting AdminServer Without Username/Password Prompt in WebLogic Server 10.3

Cheers,
Ben

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
Server

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!

Cheers,
Ben

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.

http://www.vimeo.com/16914910

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

Hope all Oracle users enjoy the video.

Cheers,
Ben