Feed aggregator

Jdev tip of the day

Wijaya Kusumo - Fri, 2006-07-07 02:42
This screen pop up when I started my Jdev Interesting.... Has anyone ever seen "other" useful tips? :)

Another SYSDBA password entry

Stephen Booth - Thu, 2006-07-06 09:33
Simon Kelsey commeted on one of my earlier entries on this subject that x$kzsrt may be involved:SQL> select * from v$fixed_view_definition where view_name='GV$PWFILE_USERS';VIEW_NAME------------------------------VIEW_DEFINITION--------------------------------------------------------------------------------GV$PWFILE_USERSselect inst_id,username,decode(sysdba,1,'TRUE','FALSE'), decode(sysoper,1,'Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com1

Oracle expert not login into the repository with TNS-12705

Neil Jarvis - Thu, 2006-07-06 08:47
As part of tuning 8 and 9i database you can use ‘Oracle Expert’. This is part of the tuning pack of Enterprise Manager Oracle Expert seems to be a pre-cursor to Oracle Advisors.

Basically, expert is given some basic parameters of the environment of the database and then monitors the performance of the database for about 15 minutes. At the end of the process, expert gives advice on some init.ora parameter changes. These changes can either be amended to your existing parameter file or if you are using an spfile you can change the values either on the fly or after an instance restart.

And now the story.

After installing a new database using DBCA and populating the database, it was time to see how it was performing with users on. I started Oracle expert and tried to attach to the repository. You need a repository, as this is the only way you can submit the job to monitor the database i.e. through the agents. To my surprise I received an error

XP-21161: Database connection attempt has failed

Error while trying to retrieve text for error ORA-12705

ORA-12705 is ‘invalid or unknown NLS parameter value specified’

I then tried to logon through SQL*PLUS. This was successful.

I decided to check out what NLS_LANG parameter I was using. I opened the windows repository with REGEDIT and found I had lost my HOMEx under the Oracle branch of software. This, I have discovered, as come about due to the installation of a timesheet system called AcTirecs overwriting the Oracle branch.

I had to recreate the HOME. The name for the home can be found by viewing a file in the BIN directory of the executables called oracle.key. So, under HKEY_LOCAL_MACHINE\SOFTWARE\Oracle I created a key HOME2. Within HOME2 I created some basic String settings of ID, NLS_LANG, ORACLE_HOME and ORACLE_HOME_KEY. The ID I gave was 2, which was because I already had 2 other oracle homes 0 and 1. Once I had added this ID I had also to add a key, ID2 under ALL_HOMES and also added Strings NLS_LANG=N/A and PATH being the Oracle home of the Enterprise Manager

Once I had done this Oracle Expert started. I guess the 'Error while trying to retrieve text' is also fixed but not receiving an error I'm not sure.

I suggest if you get NLS errors on windows check what is set in REGEDIT. You may find part of the repository has been overwritten.

Oracle answers on the SYSDBA password question

Stephen Booth - Tue, 2006-07-04 10:41
QUESTION=========1) Does Oracle cache the SYSDBA password?2) Is there a way to force it to re-read the password file to pick up a changed password without having to shutdown thedatabase?RESEARCH=========Note.1029539.6 - UNIX How to Set up the Oracle Password FileNote 185703.1 - How to Avoid Common Flaws and Errors Using PasswordfileNote 114384.1 - WIN: Checklist for Resolving CONNECT AS SYSDBA (Stephen Boothhttps://plus.google.com/107526053475064059763noreply@blogger.com3

Oracle Services and the Windows Registry

Edward Whalen - Mon, 2006-07-03 20:38

For those of you who have used Oracle on Windows you either think of the Windows registry as a blessing or a curse. The Windows registry is critical for the setup and maintenance of services as well as to maintain parameters relating to Oracle Home, etc. Without the registry, or with an incorrectly configured registry, Oracle on Windows will not function. Thus it is unwise to modify the registry own your own. Let the Oracle installer and DBCA modify the registry.

One of the main differences between Oracle on Windows and other platforms (besides the registry) is the use of services. With the Windows operating system any process started by the login user will exit when the user exits. This would not be good for something like the Oracle database, thus the Oracle instance is started as a Windows Service.

A Microsoft Windows service allows you to create long-running executable applications that run in their own Windows sessions. These services can be automatically started when the computer boots, can be manually started, stopped and paused. Because they run in their own session, they do not contain a user interface, but they can take parameters or use registry parameters. Services are used for long-running functionality that does not interact with other users, thus is good for server applications such as the Oracle Database Server and Oracle Listener. In addition, services can be invoked under the user context of a domain user, local user or the system account. Thus the Oracle service can be started by a user other than the Oracle database owner, but will run under the context of the Oracle user.

By default, the Oracle database server must create a minimum of two services:

  • OracleServiceSID is the Oracle bootstrap service that is used as the service that the Oracle instance runs under. The Oracle bootstrap service must be started in order for the Oracle instance to start, however, the Oracle instance can be started up or shutdown manually once the bootstrap instance is running.
  • OracleHOME_NAMETNSListener is the service that controls the Oracle listener.

These are the only two services that are critical to the Oracle instance (unless you are using ASM). Other services that you might encounter include:

  • OracleDBConsoleSID is the Oracle Enterprise manager DB Console service.
  • OracleJobSchedulerSID is the Oracle job scheduler process.
  • OracleHOME_NAMEiSQL*Plus is obviously the iSQL service.
  • OracleServiceASM_SID is the ASM instance service.

As I mentioned earlier, the OracleServiceSID is really just a service to bootstrap the Oracle instance. This service is required before the Oracle instance can be started, but if this service is running, you can still use SQL*Plus to start and stop the Oracle instance. The registry parameters HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_HOME_NAME->ORA_SID_AUTOSTART and ORA_SID_SHUTDOWN controls whether the Oracle instance automatically starts and stops when the OracleServiceSID service does. In addition to these parameters, the registry location HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->KEY_HOME_NAME holds some other very useful entries:

  • ORACLE_BASE is the location of the Oracle base directory for this Oracle home.
  • ORACLE_HOME is the location of the Oracle home directory for this Oracle home.
  • ORACLE_SID is the Oracle System Identifier or SID.
  • SQLPATH specifies the location of SQL scripts.
  • RDBMS_CONTROL specifies the location of control files. This is where Oracle will look for the spfile or the pfile for this instance.

There are other registry parameters, but I have touched on some of the most important. Beware, registry parameters must be exact. Spelling and capitalization are extremely important. If you do not have the parameter exactly right, it will be ignored and you will be debugging an illusive problem.

The registry is very useful in that it centralizes the location of parameters and settings for the Oracle installation. However, if something goes wrong, it can sometimes be very difficult to fix, so be careful.

Services are an important part of Windows 2003 and the entire Windows family. Without services it would be very difficult to implement Oracle without requiring the Oracle user to stay logged in at all times. Oracle on Windows is different from other platforms, and this is what distinguishes it.

OAF - Tables and PPR

Andries Hanekom - Thu, 2006-06-29 02:00
It has just taken me two days to implement PPR on a table, yes 2. I might be a little slow but the developers guide was of very little help and I was unable to find any worthwhile examples.

When planning to implement PPR in tables it is very important to decide early on what type of PPR you want to implement. Let’s take a step back, when implementing PPR on normal fields outside a table, there is only one instance of each field to modify. But when working in a table there might exist an infinite amount of rows each with an instance of the specific field.

If functionality requires that all instances of the PPR field behaves in the same fashion, PPR using SPEL bindings will work perfectly, and is very quick and easy to implement. On the other hand if you require each instance of the PPR field to act independently based on another value in its table row, you will have to look at Bound Values or Content Switcher.

In this post I will focus on Bound Values as this is the solution I implemented, the Dev guide specifies the following consideration when having to decide which solution to use:

“You should limit your use of Switchers to within tables, particularly when you want to switch between different kinds of web beans, such as a poplist or a checkbox. When you have only one type of web bean, but the value of an attribute on that web bean varies at runtime, then you should implement that attribute as a bound value rather than as a Switcher.”

PPR using Bound Values:
  • Add a transient attribute to your table view object; you will bind this attribute to the value of the PPR column’s RENDERED attribute.
  • Modify the transient attribute getter method to change the value depending on your PPR requirement. In my case I needed to render the Supplier Name field based on the value of the SupplierYn attribute in the table view object:

public Boolean getSupplierNameRendered()
String yn = getSupplierYn();
Boolean tf = Boolean.TRUE;

if ("Y".equals(yn))
tf = Boolean.TRUE;
else if ("N".equals(yn))
tf = Boolean.FALSE;

return (Boolean) tf;
  • Now bind the Supplier Name attribute to the transient attribute in the page controller’s processRequest method:
OAMessageLovInputBean supplierNameBean = (OAMessageLovInputBean) webBean.findIndexedChildRecursive(

new OADataBoundValueViewObject(supplierNameBean,
"SupplierNameRendered", "SupplierVO1",
  • The rendering of my attribute was based on the value of the attribute “SupplierYn” in the view object; to enable the updating change “Action Type” attribute to “fireAction”:

There it is four easy steps to enable PPR on a table.

Cool Illusion

Eric S. Emrick - Wed, 2006-06-28 17:56
Nothing Oracle related this time. Just some fun. I received this nice little illusion today via email. Enjoy.

EJB 3 and JPA, a done deal

Mike Keith - Wed, 2006-06-28 07:12

It's been awhile since I have posted anything here, although to be fair I never intended this blog to be a daily or weekly update. The thought of shackling myself to a self-imposed blogging schedule feels onerous and unwelcome. It was supposed to be a place to record and express views that I got tired of expressing verbally, or that could be conveniently summed up in one place. While it was that, it is true that I simply did not have the time to write about or explore some of the issues that I would have liked to write about.

We have finally released the EJB 3.0 specification, along with the Java Persistence API that is currently a part of the specification. Each of the three different documents can be downloaded separately here. These documents represent a huge amount of time and effort on the parts of the entire JSR 220 expert group.

The Core Contracts and Requirements document is the complete component specification document that describes the traditional EJB 2.1-style contracts for session, message-driven and entity beans with the local/remote home and component requirements. It also describes the new style of session and message-driven beans that use POJO business interfaces but no home interfaces. The simplified component view is further highlighted in the Simplified API document. It is a tremendous step forward for session bean providers and developers and offers unparalleled ease of development and client use. Supported by some of the JVM features in Java SE 5 the amount of effort required to create a simple EJB is minimal and finally approaches the original goal that hoped to make EJB development suitable for the masses.

The third document is the Java Persistence API that describes the lightweight POJO persistence programming model, including such things as a standardized object-relational mapping metadata model and sophisticated Java Persistence Query Language. Now we freely admit that it is not complete and utter perfection in API form, but we do believe that it is a very good start to what we in the persistence business have been seeking for a long time -- a standard that represents what we all believe in and have been implementing for a number of years. It may be one of the most comprehensive 1.0 specifications to have been produced in a long while.

The intent was to solve 80-90 % of the problems that people regularly face, and we have done that. The plan is to add still more of the additional features that the persistence products on the market have added over the years so that the more specialized requirements of the minority of applications can enjoy the same amount of portability that the others will.

So this is the part where all of you people in the blogosphere (that's you) come in. If there are features that you think are missing from the JPA 1.0, and that in your opinion should make the list for the next version I would be interested in hearing about them.

Viva Opera

Wijaya Kusumo - Wed, 2006-06-28 06:16
Opera browser is now version 9. I think this is the best ever release they ever have. One of my friends introduced me to Opera some 4 years ago, and it’s been my default browser ever since. Here are the features I like most about Opera: 1. Session Saver. The first thing that really made me use Opera is the Session Saver. They are the first and the best. When the OS hang, crash, or just

Oracle Performance Monitoring on Windows

Edward Whalen - Mon, 2006-06-26 11:14

Performance monitoring is a regular task for most Oracle DBAs. For Oracle on Windows this is no exception. The only difference are the tools that are available to you. With Oracle on Windows you still have all of the tools available to you that are available on other platforms, such as the Automatic Workload Repository, but in addition, you can view Oracle statistics via the Windows Performance Monitor (perfmon).

There are several advantages to monitoring Oracle via perfmon. By using perfmon, you can collect Oracle data as well as OS data that might be important to your overall performance monitoring plan. By consolidating your data collection, you will find it easier to analyze data. In addition, perfmon data can be collected and analyzed via the Microsoft Operations Manager (MOM) product. MOM allows you to collect and analyze long term data as well as to configure alerting functions that can let you know that there is a problem.

By default, Oracle statistics are not collected for use by perfmon. By following the steps outlined here you can quickly and easily configure Oracle for perfmon data collection. By default, you still have many ways of monitoring Oracle. Among these are the following:

  • Oracle Automatic Workload Repository. The Automatic Workload Repository (AWR) is performance data that is automatically collected by Oracle for performance and trouble analysis.
  • Event Viewer Alerts. The event viewer alerts are events that are automatically logged directly into the Windows alert log.
  • Oracle Alert Log. The Oracle alert log is the log where Oracle logs status and error messages.
  • Trace Analyzer. Oracle sessions can be traced and that trace data analyzed via the Oracle trace analyzer.

The Oracle Counters for Windows Performance Monitor package is not installed by default. In order to install them when you install Oracle, select the custom install option. You can also install this option later via the Oracle installer. Select custom installation and select the Oracle for Windows Performance option. This will install this package.

Once Oracle Counters for Windows Performance Monitor has been installed, you must perform one more piece of setup. The Oracle performance counters are set up to monitor one Oracle instance. Information about this instance must be configured in the registry. In order to do this, from a command prompt run orafcfg.exe with a username, password and Oracle net service name as follows:

operfcfg –U system –P password –D orcl

This will update the registry. You should now be able to monitor Oracle via perfmon. Some of the things that you can monitor are:

  • The Oracle Buffer Cache. Here you can see the cache miss ratio.
  • Shared Pool Stats. This collection includes the data dictionary cache, and the library cache.
    Log Buffer. Provides information on log space requests.
  • Database Data Files. This object provides physical read and write per second counters.
  • DBWR stats. Provides information on the DB Writer processes.
  • Miscellaneous. Other statistics include dynamic space management, free lists and dynamic sorts.

By taking advantage of Oracle Counters for Windows Performance Monitor you can easily and efficiently monitor Oracle along with monitoring the OS. As I mentioned earlier, perfmon provides valuable performance data that is easily collected and analyzed. Some of the most important and first counters that I look at when performance monitoring a system are:

  • Processor: %Processor Time. This gives me a quick look at how busy the system is.
  • Physical Disk: Avg. Disk sec/Read, Avg. Disk sec/Write. This provides me with an overview of how well the I/O subsystem is doing.

When first looking at a system I am actually more interested in disk latencies than throughput. The Avg. Disk sec/Read and Avg. Disk sec/Write should be in the range of 5-15 ms (0.005 – 0.015). Anything higher than this indicates a problem. Once I have detected a problem, I can try to determine its cause.

Of all of the platforms I work on; Linux, AIX, HP/UX, and Solaris, I find that Windows provides the best performance monitoring tool. Perfmon is easy to use, quite intuitive and easy to view and save the results in a number of formats. Oracle Counters for Windows Performance Monitor is not an end-all, but in conjunction with AWR stats and tracing will help to provide a complete picture.

10,000 Visits and Counting

Eric S. Emrick - Thu, 2006-06-22 21:33
Well, today my blog registered its 10,000th visit. While that is probably an average couple of days for Tom Kyte and a few others, I am kind of proud of my meager slice of the Oracle blogging pie. Of course, I get oodles of traffic from Google. But, that's not too bad is it?

Seriously, it has been a fun 4+ months of blogging. I haven't been able to blog as much as I would have liked over the past 2 months. The fact that I still desire to blog is a good sign though. Time and other constraints in life always crop up. Anyway, it has been really fun and I hope some of you have enjoyed your visits. I have received some very interesting emails and questions from Oracle enthusiasts all over the world.

I want to send one special thanks out to Tom Kyte for recommending I start a blog (and placing me on his metablog even though I stole the naming style of his blog) and another to Doug Burns who featured my blog on his site several months back, giving my traffic a jump start.

Actually, Tom, my first choice for a blog name was The Emusing Blog :)

Grapes of Math Puzzle

Eric S. Emrick - Thu, 2006-06-22 21:29
First, let me say that I was really excited about the title I gave to this post. It hit me while mowing my lawn and made me stop and laugh - thankfully the neighbors were not watching (as far as I know). Anyway, I did a quick Google search on the title Grapes of Math to see how original it was. While I had not heard of the title, it has many hits on the Web. Oh well, so much for absolute originality.

Tom Kyte has a really good puzzle on his blog. I enjoy a good puzzle and submitted my response, which I believe is the solution to the puzzle. But, for my personal satisfaction and to address Mr. Ed's concerns, I wanted to prove that my response was the only possible correct answer, given some conditions I have derived from Tom's post, the problem (picture) itself and intuition. If you are interested in the puzzle, please visit Tom's blog and try to solve it for yourself and skip the remainder of this post.

You mathematicians will please forgive any seemingly barbaric notations or proof layout :) Cary, Jonathan, Tom or any other mathematician lurking about, please feel free to critique the proof if it is incorrect.

Grapes of Math equation generated from picture:
(10(banana)+apple)/pear = 10(grapes)+peach+(strawberry/pear)

Prove the only solution set (banana, apple, pear, grapes, peach, lemon, strawberry) for the Grapes of Math is (9,3,2,4,6,8,1) given the following conditions:


0) each fruit represents a distinct integer that must be in [0-9]. Negative integers don't really make much sense in this case - how do you ascribe a negative integer to any fruit but the pear?
1) the numerator concat(banana,apple) is in [00-99].
2) pear cannot be 1 because grapes * pear would equal grapes and it does not. grapes * pear = lemon.
3) pear cannot be 0 because x/0 is undefined for all integers x.
4) from 2) and 3) 9 >= pear > 1.
5) grapes cannot be 1 because grapes * pear would equal pear and it does not. grapes * pear = lemon.
6) grapes cannot be greater than 4 because that would yield a concat(banana,apple) that is > two digits, which cannot be (condition 1). For example, the integer portion of the quotient concat(grapes,peach) must be less than 50, based on 4).
7) from 5) and 6), 4 >= grapes > 1.
8) from 7) banana is in [4-9]. If the integer portion of the quotient (grapes) is 2, 3 or 4, then given 4) the numerator, concat(banana,apple), must be in [40-99] . The least the numerator could be is 40 given 4) and 7). The highest would be 99 by definition.
9) all fruits taste really yummy (This is for Mr. Ed)

Proof by Exhaustion (brute force method): Grapes of Math Puzzle

Case 1: grapes = 4

If grapes = 4 then banana can only be 8 or 9 because of 4).

Case 1.1: banana = 8

If grapes = 4 and banana = 8 then pear = 2 and lemon = 8. Lemon cannot equal banana by condition 0) and, thusly, banana != 8. Therefore, concat(banana,apple) is not in [80-89].

Case 1.2: banana = 9

If grapes = 4 and banana = 9 then pear = 2 and lemon = 8. Then by subtraction (banana - lemon ) = (9-8) = 1 = strawberry.

Case 1.2.1: apple = 0

If apple = 0 then peach = 5 and concat(grapes,peach) = 45 with no remainder. We know that there must be a remainder because strawberry is 1 in this case. Therefore, apple != 0 and concat(banana,apple) is not 90.

Case 1.2.2: apple = 1

If apple = 1 then apple = strawberry = 1. Therefore, apple != 1 and concat(banana,apple) is not 91.

Case 1.2.3: apple = 2

If apple = 2 then apple = pear = 2. Therefore apple != 2 and concat(banana,apple) is not 92.

Case 1.2.4: apple = 3

If apple = 3 then peach = 6 and concat(strawberry,apple) - concat(strawberry,pear) = strawberry = 1. Therefore, grapes = 4, banana = 9, pear = 2, lemon = 8, strawberry = 1 and apple = 3.

Therefore, concat(banana,apple) = 93 is a numerator solution.

Case 1.2.5: apple = 4

If apple = 4 then apple = grape = 4. Therefore, apple != 4 and concat(banana,apple) != 94.

Case 1.2.6: apple = 5

If apple = 5 then peach = 7 and pear = 4. But, pear is assumed to be 2 and cannot 2 != 4. Therefore, apple != 5 and concat(banana,apple) != 95.

Case 1.2.7: apple = 6

If apple = 6 then peach = lemon = 8. Therefore, apple != 6 and concat(banana,apple) != 96.

Case 1.2.8: apple = 7

If apple = 7 then peach = lemon = 8. Therefore, apple != 7 and concat(banana,apple) != 97.

Case 1.2.9: apple = 8

If apple = 8 then apple = lemon = 8. Therefore, apple != 8 and concat(banana,apple) != 98.

Case 1.2.10: apple = 9

If apple = 9 then apple = banana = 9. Therefore, apple != 9 and concat(banana,apple) != 99.

Therefore, for grapes = 4, the only solution for numerator concat(banana,apple) in [80-99] is 93.

Case 2: grapes = 3

If grapes = 3 then banana can only be 6 or 7 because pear > 1 from condition 4).

Case 2.1: banana = 6

If grapes = 3 and banana = 6 then pear = 2 and lemon = 6, and lemon = banana = 6. Therefore, banana != 6 and concat(banana,apple) is not in [60-69].

Case 2.2: banana = 7

If grapes = 3 and banana = 7 then pear = 2 and lemon = 6. This means apple can only be in [4-5] (cannot be 6 because lemon = apple = 6 violates condition 0).

Case 2.2.1: apple = 4

If apple = 4 then peach = 3, and peach = grapes = 3. Therefore, apple !=4.

Case 2.2.2: apple = 5

If apple = 5 then peach = 5. Therefore, apple != 5.

Therefore, concat(banana,apple) is not in [70-79].

Case 3: grapes = 2

If grapes = 2 then banana must be in [4-5] because of condition 4).

Case 3.1: banana = 4

If banana = 4 then lemon = banana. Therefore, banana != 4 and concat(banana,apple) is not in [40-49].

Case 3.2: banana = 5

If grapes = 2 and banana = 5 then pear = grapes = 2. Therefore, banana != 5 and concat(banana,apple) is not in [50-59].

Therefore, concat(apple,banana) is not in [40 - 59].

From grapes in [2-4], we have proved that only one solution (93) exists for the numerator concat(banana,apple) between 40 and 99. By condition 7), concat(banana,apple) is not in [00-39].


Therefore, 93 is the only solution for concat(banana,apple) in [00-99]. After exhausting all possible two digit values for concat(banana,apple) only one solution set (banana, apple, pear, grapes, peach, lemon, strawberry) was found:


and for Mr. Ed...

(9,3,-2,4,6,8,1) iff concat(grapes,peach) = -46

Solution set applied to equation of Grapes of Math:

(10(9)+3)/2=93/2=46 ½=10(4)+6+(1/2) quod erat demonstrandum

Note: lemon is absorbed in the equation, given the correctness of strawberry = 1.

Viva le Resistance, Viva Mozilla!

Andries Hanekom - Thu, 2006-06-22 09:00
Many of us are content to use IE for all our browser needs, but few know that there is an alternative. Mozilla Firefox is a open-source browser that some ten percent of the web uses, and which has been certified for use with Oracle EBS 11i.

I have been using Firefox for just more then a year, it is a very stable and well tested product that can be installed and immediately used as your main browser. There is also a number of plugin’s and extensions, Eddie Awad created a very useful plugin that allows you to search Metalink and Oracle Docs direct from your browser.

So, help stem the tide, go ahead, check it out.

Oracle Blooper

Eric S. Emrick - Wed, 2006-06-21 20:21
Well, instead of assimilating the suitable, albeit plentiful, Oracle manuals to cover the material required for the Oracle 7.3 -> Oracle 9i upgrade certification exam I opted to search for a single resource. I came across a third-party study tool that was recommended by Oracle and seemed to foot the bill. Very much to my surprise the material was disjointed, loaded with typographical errors and often flat out wrong.

I was willing to forgive the copious typographical mistakes per/page (which approached the Golden Ratio mind you) as I could deduce the intentions. I could stomach the disjointed word salads and sparse information. But I refused to read another page after encountering a heinously blatant, careless and nonsensical bit of misinformation. How could I possibly continue to use this material as a study reference if I could not trust the content? To the misinformation at hand, the material states the following verbatim:

PGA_USED_MEM - The process is using PGA memory.
PGA_ALLOC_MEM- The process has been allocated PGA memory.
PGA_MAX_MEM - The process has been allocated maximum memory.
PGA_GIBBERISH - The process has found gibberish in the PGA and wishes to purge. (OK, this was my invention)

I scratched my head. Re-read, scratched head some more. Finished beer and reached for another. Nothing seemed to alleviate my consternation. I was well aware of these attributes of v$process and was not so much concerned with the incorrectness, as I knew their meaning. It was the gross negligence that left my jaw drooping for a minute.

The values for these attributes are NOT Boolean as you well know. You don't query v$process and find a Y or N associated with the values for these attributes. The Oracle documentation defines these attributes in a very straightforward manner. Is there any other way?

PGA_USED_MEM number PGA memory currently used by the process
PGA_ALLOC_MEM number PGA memory currently allocated by the process (including free PGA memory not yet released to the operating system by the server process)
PGA_MAX_MEM number Maximum PGA memory ever allocated by the process

Simply stated, I was shocked that the author(s) and editor(s) put such little thought into the material and subsequent proof reading. Actually, I think the author's brain was tied behind his back while writing this material. If one aspires to put together training material and includes attribute definitions that are pre-defined for you in the Oracle documentation set, might I recommend taking a cursory glance at said documentation? You can't just feed me a heaping helping of documentation rubbish without expecting me to pitch the kindling into the nearest can - I know, I've seen me do it! Did I mention the material is several fold more expensive than any of Tom Kyte's or Jonathan Lewis' books? Lesson learned.

I/O Performance and Oracle

Edward Whalen - Mon, 2006-06-19 16:32
One of the most common performance problems that I encounter is an insufficient I/O subsystem. This is especially true when working in an Oracle (or SQL Server) environment, because of the Database Server’s sensitivity to high I/O latencies. With Oracle, the time it takes for each I/O operation is much more important than the actual throughput that you can achieve (MB/sec). Because of this, the I/O subsystem should be monitored, tuned and sized sufficiently.

One of the biggest problems that performance tuning consultants have encountered in the last few years is the introduction of larger and larger disk drives. This has led to database systems being created with larger and larger disk drives, but fewer of them. This has led to an epidemic of I/O performance problems. Let me explain further.

Most I/O in Oracle is random in nature. This is due to the fact that you have a system where many people are looking for many diverse pieces of data. In addition, structures such as indexes work by retrieving one block that points to another block and finally to the data block itself. Thus almost all I/O can be considered random.

The basic structure of the disk drive has not changed significantly in the last few years. Since disk drives have gone from 2 GB in size to 300 GB in size, the average seek time has dropped from 9 ms to 4.5 ms. This represents a 150 times increase in size, with a 2 times increase in performance. This has caused a problem where large databases are being configured on fewer and fewer disk drives.

To make things worse, some vendors are now selling Serial-ATA drives that have an average seek time of 8 ms or higher. This represents a 250 times larger drive with essentially no increase in performance. SATA drives should be avoided for Oracle storage if Fibre Channel or SCSI drives could be used.

At the same time, with Oracle on Windows (32-bit) databases are getting larger and larger with memory not increasing significantly. This has caused an upturn in I/O problems occurring with Oracle systems on Windows. The good news is that the solution to this problem is simple, add more disk drives.

How do you determine if you are having I/O problems? There are several methods. The Windows performance monitor (Perfmon) provides a wealth of I/O performance data. To me, the most significant I/O counters are Avg. Disk sec/Read and Avg. Disk sec/Write. These represent the disk latencies and are your first indication that you are having a problem. Disk latencies should be in the range of 5 ms – 15 ms for reads and 0 – 10 ms for writes (if a write cache is used). The performance counters for Windows are very accurate and very useful in determining if you have a performance problem.

In addition to the Windows Perfmon counters, Oracle AWR reports can also be quite useful in determining if you are having an I/O problem. Look for the top 5 wait events. If you see one or more of the Top 5 Timed Events in the System I/O wait class, this is an indication of an I/O problem. In addition, the I/O statistics also report average read ms and average write ms per data file and per tablespace. This information is very useful.

In summary, I/O is critical to the performance of your Oracle Database system. Don’t confuse size with performance. The performance capacity of your I/O subsystem depends on the number of disk drives, not the size of the drives. Performance and sizing are critically linked. An undersized I/O subsystem will perform poorly no mater how much CPU capacity and memory you have. If you are experiencing performance problems, check the I/O subsystem first.

Find the right JDeveloper Patch for OAF development

Andries Hanekom - Mon, 2006-06-19 08:54

I have read a number of questions regarding what JDeveloper version should be used for OAF development. After a recent upgrade of our development system from 11.5.9 to 11.5.10 CU2, I did some research on Metalink and found the following info regarding JDeveloper versions:

Current JDeveloper patches:

  3. Patch 4573517 - Oracle9i JDeveloper with OA Extension for 11.5.10 CU2

To determine which patch to use, you can simply check the framework version in your instance by using http://host:port/OA_HTML/OAInfo.jsp, then choose the matched one.

11.5.10K = patch 4045639
11.5.101CU = patch 4141787
11.5.102CU = patch 4573517
11.5.103CU = patch 4725670

It would be interesting to have a survey regarding what framework versions are being used for OAF development. Please post your current development version in a comment; I will publish the results at a later stage.

Checkout Metalink note 416708.1 for the up to date list of OAF releases.


Andries Hanekom - Mon, 2006-06-19 04:05
The main aim of this Blog is to share my experiences as an Oracle e-Business Suite (EBS) developer. I will also from time to time blog about any subject that made me laugh, cry, angry or I feel would be of interest.

Just to get everybody thinking here is a short list of EBS topics I will be covering:

  • Oracle Application Framework Development, Extension and Personalization
  • Database Performance (Development Specific)
  • SQL & PL/SQL Concurrent Development
  • Open Interfaces and API's
  • Forms, Reports, CUSTOM Library development and customization
  • Workflow Development and Customization
  • Development Tools (JDev, Forms, Reports, TOAD, etc.)

All comments, anonymous or otherwise, fully appreciated.

Luca Canali and ASM Metadata Internals

Radoslav Rusinov - Wed, 2006-06-14 13:12
In a recent technical paper Luca Canali explains how to find the mapping between physical storage, ASM allocation units, and database files by querying the undocumented X$ tables.By using the information retrieved from X$KFFXP table he succeeded to:Retrieve a file directly from ASM disksMonitor how the extents in a datafile are allocated across ASM disksMonitor how a datafile is spread across ASMRadoslav Rusinovhttp://www.blogger.com/profile/18163031714036680150noreply@blogger.com0

Christo Kutrovsky's Blog

Radoslav Rusinov - Sun, 2006-06-11 12:23
If you’ve missed to notice – Christo Kutrovsky started his own professional blog dedicated mainly to the Oracle database.Christo Kutrovsky is an Bulgarian Oracle professional currently working in <?xml:namespace prefix = st1 />Canada as a Senior Database/System Administrator for The Pythian Group.His blog is a part of the corporate blog of The Pythian Group.The blog is only two-months old but Radoslav Rusinovhttp://www.blogger.com/profile/18163031714036680150noreply@blogger.com0

Oracle High Availability on Windows using Fail Safe

Edward Whalen - Sat, 2006-06-10 13:13

Oracle on Windows supports the same Oracle Disaster Recovery (DR) and High Availability (HA) features as other platforms, such as Oracle RAC and Data Guard. In addition to these, Oracle on Windows supports Oracle Fail Safe. These are all excellent solutions with different usages, costs and performance.

Oracle RAC is a high availability solution that allows multiple Oracle instances to access the same database. This provides for both high availability and additional performance, since all nodes are active. Oracle RAC is an excellent product, but somewhat complicated to install and manage and can be expensive.

In addition to Oracle RAC is Oracle Data Guard. Data Guard is really more of a Disaster Recovery (DR) solution than a High Availability solution, but it can be very useful in the event of a system failure. Data Guard is a fantastic product which I have installed many times. Data Guard is one of those products where the base installation can be fairly straight forward, but there are so many options and modes of operation that it can quite quickly become very complicated.

The final solution in this discussion is Oracle Fail Safe. Fail Safe is a Windows only product that makes Oracle Microsoft Cluster Services (MSCS) aware. Failsafe is available for all Windows platforms (32-bit, x64 and Itanium) and is downloadable from the Oracle web site. Fail Safe is a plug in to MSCS to allow the Oracle instance and listener to be managed under the control of MSCS. This allows the Oracle instance to fail over in the event of a failure of one of the nodes.

The MSCS architecture is very straight forward. Two systems share the same disk, of which only one system controls at a time. In the event of a failure (determined by the heartbeat mechanism), the instance currently running the Oracle instance and controlling the storage is replaced by the standby system. This allows the Oracle database to be back up and running in a matter of minutes, thus improving availability.

Oracle Fail Safe has the advantage over RAC in that it is much easier to implement and administer. Most Windows shops already have implemented MSCS and are very familiar with it. In addition, Oracle Fail Safe is a core feature of Oracle9i and Oracle10g for Windows and no additional license is needed.

With Oracle Fail Safe you can fail over the following Oracle components:

  • Oracle Database Server.
  • Oracle Listener.
  • Oracle Intelligent Agent.

The failover is seamless and works very well. In addition, the management tools allow you to configure Fail Safe to automatically fail back a node at a scheduled time, thus allowing you to configure different hardware resources for the primary and standby servers.

I have worked on multiple Oracle Fail Safe installations. In a Windows environment where you want to have assurance of High Availability at a low entry point cost, Oracle Fail Safe might be for you.

Configuring Oracle Fail Safe is easy once the Microsoft Cluster Services have been installed. The following steps are done in order to install Oracle Fail Safe.

  1. Install Windows on both servers.
  2. Install MSCS (if applicable) and create the cluster.
  3. Install Oracle Database Software on each node’s local disks, using the same Oracle Home directory.
  4. Create the Oracle database on one node using the shared disk for storage.
  5. Install Oracle Fail Safe on each node in the cluster.
  6. Configure the cluster using the Fail Safe manager.

You will find that Fail Safe is an easy to use, inexpensive way to provide high availability in a Windows environment.


Subscribe to Oracle FAQ aggregator