Oracle Fusion Middleware & Application Server

Friday, March 25, 2011

Where is my tnsnames.ora in an Oracle Fusion Middleware 11g environment?

Today a really short, but I hope useful blog post :-) I got regulary the question from customers and/or colleagues "Where is my tnsnames.ora?". I can really understand this question, as the directory structure and layout for an Oracle Fusion Middleware 11g environment is slightly different from the directory structure of the previous Oracle Application Server 10g environment.

The answer is really simple on where to find the tnsnames.ora in an Oracle Fusion Middleware 11g environment:
just go to $MW_HOME/<InstanceName>/config here you will find the tnsnames.ora.

Wednesday, March 23, 2011

The new sports - Plagiarism

Some time ago I saw in Tim Hall's Blog a post that his complete site has been stolen (My whole website stolen again…) and republished under another name.

Now it also hits me, not the whole site was copied, at the moment only one complete article of me (Oracle Internet Directory Light for tnsnames Resolution - Dirk ...)

Seems that Plagiarism is the new sport :-)

The article were re-published under the site BLOG.ABIGOLD.FR with a One-To-One copy, even all names which contains the domain name of my previous employeer and every screenshot, in which you can clearly see the domain name of my previous employeer and I am quite sure the publisher of my stolen content was never employed at this company :-)
My 2 cents for such a stupid copy action is ;-)

(Note: If you like this shirt, go to )

Only after I contacted the service provide of this domain, they change the author name to my name.
Seems that some people want to adorn themself with borrowed plums

Sunday, March 6, 2011

Switching Oracle HTTP Server to Port 80

If you are using the Oracle Webtier 11g ( / / in your projects and specially the Oracle HTTP Server, you face normally the problem, that the Oracle HTTP Server is configured with a Listen Port of 7777 or something like this.
But endusers dont like to remember always the Port number of the Oracle HTTP Server, so the goal is to reconfigure the Oracle HTTP Server. Under a UNIX operating system you have to consider one important point for this task, all ports <1024 belongs to root and normally the Oracle Webtier is installed under a own user (normally user oracle) which don't have root privileges. So the ownership of the .apachectl binary must be changed.

Following steps must be done:
  • Shutdown your Oracle HTTP Server
  • Reconfigure the Listen Port
  • Change the .apachectl binary
  • Restart your Oracle HTTP Server and test
The first step is to shutdown the Oracle HTTP Server, this must be done with opmnctl. The binary you can find under your WebtierHome/instances/instance1/bin.
Connect as the Oracle software user (oracle) to your server

# assuming your MW_HOME is /u00/app/oracle/product/fmw-11.1.1 
export MW_HOME=/u00/app/oracle/product/fmw-11.1.1
cd $MW_HOME/Oracle_WT1/instances/instance1/bin
./opmnctl stopall

Second step is to configure the Listen Port for the Oracle HTTP Server to port 80

# assuming you are still connected as oracle user to your sevrer
cd $MW_HOME/Oracle_WT1/instances/instance1/config/OHS/ohs1
cp httpd.conf
vi httpd.conf
# Now go to the line with Listen <portnumber> (normally 777x)
# and replace the port number to port 80
Listen 80
# save the changes

The third step is to change the ownership and permissions of the .apachectl binary, this step must be done as root user:

# assuming you are connected to the server as root user
# use the MW_HOME from step 1
cd $MW_HOME/Oracle_WT1/ohs/bin
# be aware, we have to modify the hidden file .apachectl (the dot is correct) 
ls -la .apachectl
-rwxr-x--- 1 oracle oinstall 13278 Dec 17 03:54 .apachectl
chown root .apachectl
chmod 6750 .apachectl
ls -la .apachectl
-rwsr-s--- 1 root oinstall 13278 Dec 17 03:54 .apachectl

The last step is to startup your reconfigured Oracle HTTP Server, for this you must connect to your server as Oracle Software user:

cd $MW_HOME/Oracle_WT1/instances/instance1/bin
./opmnctl startall
# Then check the status with the option -l
# to see if your Oracle HTTP Server is up and
# the option -l display the used ports (see picture below)
./opmnctl status -l

The final test is, startup your browser and point to your Oracle HTTP Server

Friday, March 4, 2011

My Oracle ACE Trophy arrived

Today I received a small package with a warning label "Please handle with care - Glass", inside was my Oracle ACE Trophy :-)

Looks cool on my desk :-)

Tuesday, March 1, 2011

Why you should check the default grants to PUBLIC

Today something Oracle Database Security related: Why you should have a look on the default grants to PUBLIC in an Oracle Database.

Just a small showcase:
  • One Application Schema APP_USER which owns some tables
  • One Application related Role APP_READ to which grant select on APP_USER.<TABLENAME> were given
  • One Enduser named EVIL :-) which got create session and hold the APP_READ role
  • One Enduser named FRIENDLY :-) which got create session and hold the APP_READ role

-- First lets create the Application Schema APP_USER
create user APP_USER identified by mysecrectpassword;
grant connect, create table to APP_USER;
create role APP_READ;

-- Secondly create the Enduser EVIL
create user EVIL identified by evilpassword;
grant connect, app_read to EVIL;

-- Thirdly create the Enduser FRIENDLY
create user FRIENDLY identified by friendlypassword;
grant connect, app_read to FRIENDLY;

-- Connect with the Application Schema APP_USER
-- Create one table
-- and asign the select right to the Application Role APP_READ
conn app_user/mysecretpassword
create table t1 as select * from all_objects;
grant select on t1 to APP_READ;

Now we have to open 2 sessions to the Database, one connected with the Application User EVIL and one with the Application User FRIENDLY.

In the session with the user EVIL we execute following SQL statement:
conn evil/evilpassword
exec sys.dbms_snapshot.BEGIN_TABLE_REORGANIZATION('APP_USER','T1');

In the second session with the user FRIENDLY we try now to run select on the table APP_USER.T1:
conn friendly/friendlypassword
select * from APP_USER.T1;
The session from the user FRIENDLY is not responding :-( The session from user FRIENDLY will only responding when we are running following SQL Statement in the session of user EVIL:
-- Session of user EVIL
exec sys.dbms_snapshot.END_TABLE_REORGANIZATION('APP_USER','T1');
-- Some errors will come up, as the used table
-- have not a materialized view log
ERROR at line 1:
ORA-23413: table "APP_USER"."T1" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_SNAP_INTERNAL", line 703
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2811
ORA-06512: at line 1

In the above example are clearly demonstrated two major problems:
  • Even with a create session privilege you have too many privileges through the default grants to PUBLIC
  • The Procedure dbms_snapshot.BEGIN_TABLE_REORGANIZATION takes every table on which I got a select right, no matter if its just a normal table

The above example shows clearly that we have to be careful with the default grants to PUBLIC in an Oracle Database.

Above shown example were executed under Oracle and Oracle