Happy New Year

So, that's the last entry for this year.

Happy new year, guten Rutsch (german) and bonne et joyeuse année 2010 (french) to all my readers.

Next year I will continue with more stuff about Oracle Fusion Middleware 11g and Enterprise Content Management. So stay tune ... :-)

Fun with DUAL Table

Try this "little hack":

connect to Database as sysdba and execute following commands:

select * from dual;
D
-
X
select count(*) from dual;
COUNT(*)
--------
1
insert into dual values ('Y');
commit;

Connect in another session with a normal user and try to drop one table:

conn scott/tiger
drop table t1;
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
select * from dual;
D
-
X

Cool the dual just shows one row as expected

Go back to the session with the connect as sysdba

select * from dual;
D
-
X

Upps, here is the same, one row in the dual.

create table copy_dual as select * from dual;
select * from copy_dual;
D
--
X
Y

And here we can see the two rows :-)
To clean up, just delete the second row in dual table

delete from dual where dummy = 'Y';
commit;

Addendum: Under Oracle 11.2.0.1.0 the error with the drop table command doesn't come up again

Explaining Enterprise 2.0

Today I discover a really cool presentation from Oracle concerning Enterprise 2.0 http://getsocial.oracle.com/?id=d2d

I can strongly recommend this interactive presentation to you, it's one of the best presentations I ever see from Oracle.

DOAG Conference 2009 - Training Day

From the 17th until 19th November is in Nürnberg/Germany the DOAG Conference and in access on the 20th November 2009 I will give on the Training Day a workshop for "Identity Management for DBA's", details can be found under http://www.doag.org/konferenz/doag/2009/konferenz/schulung.

Oracle Fusion Middleware 11.1.1.2.0 available

The latest release of the Oracle Fusion Middleware 11.1.1.2.0 is now available on http://www.oracle.com/technology/software/products/middleware/htdocs/fmw_11_download.html

To find the new features/changes of this new release is a little tricky :-) so here is the link to the new features http://download.oracle.com/docs/cd/E15523_01/core.1111/e10105/whatsnew.htm#ASADM10740

The major new feature in the FMW 11.1.1.2.0 is the implementation of the Automatic Diagnostic Repository (ADR). Oracle DBA's are used to this component since Oracle 11g, so now even a classical DBA got a chance to manage a little bit the Fusion Middleware :-)

The corresponding chapter in the documentation for the ADR can be found here: http://download.oracle.com/docs/cd/E15523_01/core.1111/e10105/diagnostics.htm

Method-R for Non-Technicals

Today I discover some nice article from Cary Millsap explaining Method-R for non-technical people: http://method-r.com/faq/31-method-r/44-can-you-explain-method-r

But I wonder if this model will work, if you try to adapt this model to a shopping tour with your girlfriend :-)

Oracle Reports desname Bug fixed with Fusion Middleware 11g

This time I come back with an old stuff, which I publish nearly 4 years ago (ups, I'm getting old): The famous Oracle Reports desname Bug, my White Paper concerning this can be found under following Link A Security Hole in Oracle Application Server (Reports) and how to ... (Website of my previous employer). Due to this bug (which was never fixed from Oracle in the Oracle Application Server 10g) it was/is possible to override any file to which the oracle user got access (details see in my mentioned White Paper).

And now start claping Oracle introduce in the Oracle Reports 11g (Part of the Oracle Fusion Middleware 11g) a new Configuration Element <folderAccess> with which we can limit the read and write access for the Reports Server :-) Cool ...

The <folderAccess> Element can be defined in the $DOMAIN_NAME/servers/WLS_REPORTS/stage/reports/reports/configuration/rwserver.conf Configuration File for In-Process Servers or in the $ORACLE_INSTANCE/config/ReportsServerComponent/<reports_name>/rwserver.conf Configuration File for Standalone Reports Servers:

<folderAccess>
   <read>/u01/applications/demoapp/reports</read>
   <write>/u01/applications/reports_output</write>
</folderAccess>

With the Sub-Element <read> we can define to which directories the Reports Server got read access (multiple directories can be added separated with a semicolon) and the most important Sub-Element <write> defines to which directories the Reports Server got write access (multiple directories can be added separated with a semicolon). So an Oracle Reports Call with the option destype=file and desname=<target_output_dir> can only write output files to the defined write-Directory: no chance to damage other files outside this directory :-)

So, that's really a good reason to move to Oracle Reports 11g

Security Hole in Fusion Middleware 11g WebLogic Admin Server

After a little bit playing with the new Fusion Middleware 11g I found a small security hole in the WebLogic Admin Server.

In order to start the WebLogic Admin Server Oracle provides you a shellscript $MW_HOME/user_projects/domains/$DOMAIN_NAME/startWebLogic.sh

The first way you can use this script is in an interactive way, just execute the script and it will prompt you for the WebLogic Admin-User and his Password. But this methode is not usefull for e.g. RunLevel Scripts :-)
So the second way is, you can buildup a wrapperscript in which you define two Variables (WLS_USER and WLS_PW for the WebLogic Admin-User and his Password) and execute out of this wrapperscript the startWebLogic.sh script to start the WebLogic Admin Server without prompting of the User and Password.

But be aware, if you use the second option, the WebLogic Admin-User and his Password will be displayed in cleartext in the ps-List :-(

In the last line of the ps-List you will see username=weblogic and password=oracle11g

To get out of this problem, just create under your directory $MW_HOME/user_projects/domains/$DOMAIN_NAME/servers/AdminServer/security a file named boot.properties with the content

username=your_admin_user

password=your_admin_user_password

and with this boot.properties file you will not be prompted for the WebLogic Admin-User and his Password and moreover it will not be displayed at the ps-List :-)

Oracle Fusion Middleware 11.1.1 Forms & Reports Next Errors Part II

And here I go with the update on my previous post "Oracle Fusion Middleware 11.1.1 Forms & Reports Next Errors

The first response from Oracle Metalink was:

• Confirm it is physically a Linux OS and not any form of virtualisation (which is not supported)

Here my 2 Cents for this response:
In the meantime, I found the solution for this problem :-)
If you are running the Installer for the Oracle Fusion Middleware, you will be asked for either creating or extending a WebLogic Domain. Normally you choose to create a new Domain, which got the default name "ClassicDomain". If you choose this default name, you will receive the error "An internal operation has failed: The oracle instance is not empty. Select a different location or remove the instance at this location." if you change the Names for the Oracle AS Instance, the Instance Location aso.

So define your own WebLogic Domain Name and then you can also choose your own names for the Oracle AS Instance aso.

Oracle Fusion Middleware 11.1.1 Forms & Reports Next Errors

After I solved yesterday the first error during the linking process of the Oracle Fusion Middleware 11g Forms & Reports the next error comes up :-) Now at the configuration steps, the first configurations like creating Managed Servers for Forms & Reports, Deploying of the Enterprise Manager aso. was successfull, but at the configuration step "Creating ASInstance" a failure comes up :-(

The Installation Logfiles just say:

An internal operation has failed: The oracle instance is not empty. Select a different location or remove the instance at this location.
. . .
Caused by: oracle.as.config.exception.LocationAlreadyUsedException: The oracle instance is not empty. Select a different location or remove the instance at this location.

???? That's a fresh new Installation, how the Instance can not be empty?????

So, now I'm trying with Oracle Metalink to solve this problem, let's hope ....
This blog entry will be continued, depending on the performance of Metalink :-)

Oracle Fusion Middleware 11.1.1 Forms & Reports Linking Error

As I am playing now with the new Release of the Oracle Fusion Middleware 11g Forms & Reports I run in a linking error during the installation.

My environment is as follows:

• Oracle Enterprise Linux 5 x86-64
• All required packages according to the installation guide in 32bit and 64bit installed
• ulimts correct set (4096) and not as wrongly stated in the documentation with 2048 :-)

I start the installation within a linux32 bash as the Fusion Middleware 11g Forms & Reports is only available for 32bit and 64bit Operating Systems are certified according to the documentation.

After some minutes clicking through some screen and waiting for copy the linking process start and throws an error:

• Error in invoking target 'client_sharedlib' of makefile '/u00/app/oracle/product/wls-10.3.1/asfr11g/rdbms/lib/ins_rdbms.mk'.

Nice :-)

Here my solution, log on as root user and execute following commands:

mv /usr/bin/gcc /usr/bin/gcc.save
mv /usr/bin/g++ /usr/bin/g++.save

Create with vi a new file with the name gcc under the directory /usr/bin with following content:

/usr/bin/gcc.save -m32 $*


Make the new create file gcc executable:

chmod 755 /usr/bin/gcc

Create a sym-link:

ln -s /usr/bin/gcc /usr/bin/g++

After this restart the whole installation process and be happy :-)

Reverse Engineering with DBMS_METADATA

Today I'm coming with PL/SQL stuff, nothing with Oracle Application Server :-)

At the moment I'm developing for a customer a small reverse engineering routine to capture an Oracle Database (CreateDB.sql, Tablespaces, Users, Grants aso). For this I'm using the package dbms_metadata. If you are using this package to capture e.g. all objects grants to all users, you may receive errors like this "SQL Error: ORA-31608: specified object of type OBJECT_GRANT not found". This comes up, if the user don't hold any object grants.
To avoid this simply run a query like this, which will generate you a list of DBMS_METADATA.GET_GRANTED_DDL statements for every user which holds objects grants:

select 'select DBMS_METADATA.GET_GRANTED_DDL(''OBJECT_GRANT'', '||a.username||') from dual;'
from dba_users a, (select distinct(grantee) from dba_tab_privs) b
where a.username = b.grantee;

You can adapt this construct easily on SYSTEM_GRANT and ROLE_GRANT.

Oracle Fusion Middleware 11g available

Since yesterday (Thursday, 02.07.2009) the new Oracle Fusion Middleware 11g is available on Oracle Technet:
http://www.oracle.com/technology/software/products/middleware/htdocs/111110_fmw.html

The following components are available:

• SOA Suite
• WebCenter Suite
• Identity Management
• Access Manager
• Portal, Forms, Reports and Discoverer
• Repository Creation Utility

The whole stack is available for Windows and Linux platforms, the remaining Operating Systems will follow.

With the Fusion Middleware 11g a complete new concept of the architecture is established, all based on the Oracle WebLogic Server.

JDBC URL with Oracle SID or ServiceName

Today I face a small problem with a definition of a JDBC URL which is referencing an Oracle Database.

I just define:

jdbc:oracle:thin:scott/tiger@localhost:1521/PROD.OCZH.CH

where PROD.OCZH.CH is the ServiceName of my Database, but it fails :-(

After a some investigations, the solution is simple, if you want to use the ServiceName of your Database in the JDBC URL you have to place // in front of the servername:

jdbc:oracle:thin:scott/tiger@//localhost:1521/PROD.OCZH.CH

Cool Video about APEX

I discover today a cool video from Oracle concerning Oracle APEX



Enjoy it :-)

Oracle 10.2.0.4.0 on OS X Intel (Part 2)

Today I got some time to test the new Oracle 10.2.0.4.0 on my MacBook Pro. Really cool stuff.

Installation is quite simple, just follow the Installation Guide. Only thing what is missing, is that you have to set the Environment Variable DYLD_LIBRARY_PATH=$ORACLE_HOME/lib in order to use all necessary utilities (e.g. lsnrctl, sqlplus aso.)

You should also set all mentioned settings according to the Installation Guide, like Kernel Settings and specially ulimits, I forget the ulimits and immediately as I wanted to create a Database I receive TNS-12547 lost contact :-( , just set as the oracle user "ulimit -Hn 65536 and ulimit -Sn 65536" and start the creation of the Database and everything will run :-)

Here you will see, that it's really Oracle 10.2.0.4.0 on OS X :-) (see last line)

Oracle Database 10g Release 2 (10.2.0.4.0) for Mac OS X (Intel) available

My first post about a long time is about the availability of Oracle Database 10g Release 2 (10.2.04.0) for Mac OS X on Intel Platform :-) Cool, now I can use my Mac ProBook with almost every Oracle Tools.

The Download can be found under Oracle TechNet.