Oracle SQL Developer Data Modeler 3.3 now available

Attention all data modellers - we are pleased to announce the release of SQL Developer Data Modeler 3.3. This release includes a new search, reports can be generated from search results, extended Excel import and export capabilities and more control and flexibility in generating your (more...)

Finally here: Oracle Instant Client 11gR2 for OS X

See the blog post over at Oracle for details, and be sure to send Christopher Jones an email (via the address in his post) stating you're an active user of the client. As Christopher mentions in his post,

You can really help us justify resources for the OS X bundle if you email me letting me know you are a fan. Since Instant Client is a free download, it is tricky for us to know how widely it gets used.

Jump to OTN to download, but please remember to email Christopher as well, if you're interested in seeing the client (more...)

11.2 AQ – Messages stuck in PROCESESED,WAIT State

Original Post can be viewed at 11.2 AQ – Messages stuck in PROCESESED,WAIT State

I had been working today on AQ issue where messages were not moving from WAIT to READY State.This was on database running on RHEL5u6(64 bit). I thought of quickly documenting it for easy reference When you enqueue record, you can specify delay after which record should be ready for dequeue. During this time, MSG_STATE [...] Weblog

Using SQL commands on ADRCI

Original Post can be viewed at Using SQL commands on ADRCI

While working with ADRCI , I came to know that we can run normal SQL commands for some of the operation.e.g To display current purge policy , you can run select * from adr_control instead of show control. adrci> desc ADR_CONTROL Name Type NULL? ----------------------------- --------------- ----------- ADRID number SHORTP_POLICY number LONGP_POLICY number LAST_MOD_TIME timestamp [...] Weblog

Build an 11gR2 RAC cluster in VirtualBox in 1 Hour using OVM templates

[I originally posted this over at the Pythian blog. If you're not following it, you should! Way more content, by far smarter people than lil ol' me.]

After reviewing my blog post about running EBS OVM templates in VirtualBox, two of my teammates suggested that I work on something with potentially broader appeal. Their basic message was, "This is really cool for us EBS nerds, but what about the Core DBAs?"

So how does "11gR2 RAC in an hour" sound? :-) In this post, I'll demonstrate how to deploy the pre-built Oracle VM templates to create a two-node (more...)

Limiting I/O and CPU resources using 11g Oracle Resource Manager

Original Post can be viewed at Limiting I/O and CPU resources using 11g Oracle Resource Manager

Recently I was working on using Oracle DBMS_RESOURCE_MANAGER to limit resources usage by read-only queries. This was required to prevent long running ad-hoc and poorly written queries.Instead of writing custom sql /script to kill long running session, we decided to utilize Oracle Database Resource Manager. We decided using elapsed_time as criteria for cancelling sql.But during [...] Weblog

ORA-12514 due to DNS issue

Original Post can be viewed at ORA-12514 due to DNS issue

A User reported ORA-12514 error from JDBC application connecting to RAC database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor To troubleshoot it, a)I checked following things to ensure specified service is running srvctl status service -d dbname -s serv_name b) Ensured service is registered with listener lsnrctl status Note that [...] Weblog

Sqlplus HTML reports

Original Post can be viewed at Sqlplus HTML reports

I was working on setting up monitoring for application team to monitor AQ and wanted html formatting of table data. On searching I found this link from Ittichai Chammavanijakul  which makes use of css formatting to generate good visual reports.   EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 [...] Weblog

No more cleartext-passwords in Scripts – Oracle Secure External Password Store (SEPS)

Gone are the day when cleartext passwords had to be stored in scripts for Oracle database access. The solution to this requirement is “Oracle Secure External Password Store (SEPS)”. This article will give a short introduction and a practical example of the solution.

Key facts:

  • no Advanced Security Option (License) necessary
  • every unix-account, who has access to the wallet can use it to log on to the contained databases without a password! Therefore prevent other unix-accounts from accessing your wallet! (chmod, chown)


  • Oracle Client: Unix-Account, who wishes to connect to the database without providing a password needs to (more...)

Oracle SCN Problem

In case you have not heard all the buzz about the Oracle SCN flaw, which was published by Infoworld after the release of January CPU, I summarize the essentials.

  • There is a risk that the SCN reaches it´s maximum value and this could lead to an outage of the database.
  • There is a bug in “ALTER DATABASE BEGIN BACKUP”, which increases the SCN dramatically. (Bug 12371955 – fixed in and others)
  • When you query a remote database via database link from a database with elevated SCN, the remote SCN gets increased to the higher value (more...)

Customize/Personalize Oracle APEX Workspace Environment

When you have multiple Oracle APEX environments, e.g., development, test, UAT and production, and for multiple groups, one of the confusing things is that sometimes developers are lost on which environment they’re working on. Most of technically proficient developers can make a distinction easily from the URLs they’re currently using. However, most of less technically proficient (e.g., business users) sometimes cannot.

A quick and simple solution here is to make a visual distinction on the APEX workspace pages especially on the logo area. Instead of using plain vanilla logo images, they are replaced with more distinct (more...)

Tailoring Fusion CRM

The journey for the Fusion CRM development team has been a long one. What a great feeling for all of us when Fusion Applications was officially released this year as Generally Available. During his keynote speech at this year's Oracle OpenWorld Steve Miranda reiterates that statement. Also during his session (more...)

when does PMON register to remote listeners

I had a complex problem today: I tried to setup a connection manager, but unlike Arup, I did not like to to use SOURCE_ROUTE. So I had to make the pmon register itself to the cman. As we have already an entry in spfile for remote_listener=REMOTE, I just enhanced this alias in tnsnames.ora by the additional line for the cmans HOST and PORT.
Unfortunately the services did not show up in the cmans show services. Not even an alter system register; did any good, still no service.
After checking with tcpdump (there where really no communication to the cman) and  (more...)

Book Review: Oracle 11g R1/R2 Real Application Clusters Essentials by Ben Prusinski and Syed Jaffer Hussain (Packt Publishing)

Oracle 11g R1/R2 Real Application Clusters EssentialsOracle Real Application Cluster (RAC)  becomes the technology of choice for highly available and highly scalable deployment of enterprise Oracle database environment. With its innovative technology it comes with no surprise its complexity. The complexity is not only with the underlying database technology itself, but also how well you design and implement it to work with other components including operating system, storage subsystem, etc. In order to setup and maintain a successful RAC environment, it not only requires technical knowledge of database administrator (DBA), but also well collaboration and extended planning between all IT and business partners.

Few years (more...)

11g Gotcha

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an "ORA-28002: the password will expire within X days" error message if you keep the default value. To change the PASSWORD_LIFE_TIME, you: ALTER PROFILE default LIMIT password_life_time UNLIMITED; One thing to keep in mind is

What’s old is new again

We had a fair number of problems related to bind variable peeking in our and dbs.  In fact, the plans became so erratic that we decided to just turn bind variable peeking off by setting _optim_peek_user_binds=false in all the init.ora files. Flash forward to Oracle 11g ( and the optimizer has been enhanced to use bind variable peeking much better.  After converting to

My first 11g show stopper: Purging the Recyclebin

I've run into my first issue with  After one of my dbs was upgraded, every time I try to purge the recyclebin, I get an ORA-00600 error: SQL> purge dba_recyclebin; purge dba_recyclebin * ERROR at line 1: ORA-00600: internal error code, arguments: [ktcdso-1], [], [], [], [], [], [], [], [], [], [], [] It doesn't matter if the recyclebin is on or off. I have done about six upgrades and

Listener Logging in 11g

By default, the listener logging is not turned on in 11r2.  You have to turn it on by specifying: LOGGING_listener_name=ON in your listener.ora file.

Something Interesting about the 11g Client

If you're just beginning to implement Oracle 11g, you need to be aware of the Automatic Diagnostic Repository (ADR).  It has nothing to do with AWR, ADDM, ASM, ASSM, or any of the other acronyms Oracle has come up with over the last two releases. In terms of the database, ADR could be a great thing.  It's integration with Enterprise Manager is probably one of the best parts about the feature. 

Some Great New Features

I've been off exploring Oracle 11g for a little bit trying to figure out a strategy to upgrade my environment. Oracle 11g has a ton of new features, most of which are now "options" (in other words they cost more money). When Oracle came out with compression at the segment level in 9i, I thought it was a great feature, at least in theory. But as I got to use compression, I found out that DDL