Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at email@example.com. Please include your name and telephone number in your email.
This month’s question comes to us from Jeannette in Raleigh, NC: We run Oracle 10.2.0.3 on RedHat Enterprise Linux 4 and will get 0RA-07445 errors from the database. Oracle documentation states that a core dump file should be created but I never get one—and I don’t even get a stack trace. I don’t know where to turn if I can’t even look at traces or core dumps. How do I get the information needed to diagnose the problem or at least start talking intelligently? Please help.
Terry Sutton of the Database Specialists team responds: There are a couple factors in Linux that can lead to this problem. By default RedHat Enterprise Linux puts the following line into /etc/profile (as documented on the RedHat website):
ulimit -S -c 0 > /dev/null
This command tells the shell to limit the size of core dump files to zero bytes. You can see your current limits with the “ulimit -a” command. Because the command appears in /etc/profile, it takes effect for every interactive shell. Presumably RedHat thinks that you won’t want to see core dump from interactive sessions. However, the way the /etc/init.d/dbora script (which in most cases is used to start the database when the server boots) is usually written, the Oracle instance gets started from an interactive shell, and thus Oracle is not permitted by the shell to write core dump files.
Fortunately the solution to this problem is fairly simple. Just add the line:
ulimit -c unlimited
to the oracle user’s .bash_profile file, which will negate the line in /etc/profile, and enable the writing of core dump files. You will want to make this same change for any user who is likely to start the Oracle instance.
There is also an interim Oracle patch for bug 6412186, which may be related to this issue. If you’re having this problem you should contact Oracle Support to see if this bug is relevant for your situation.
Finally, there is one more configuration item needed to resolve the issue. According to Metalink Note 367645.1, additional steps need to be taken enable setuid processes to create corefiles. In the directory /proc/sys/kernel is a file named either core_setuid_ok (RedHat 3), or suid_dumpable (RedHat 4). The file will contain one character, which is usually “0”. We need to change this value to “1”. So we enter:
echo 1 > /proc/sys/kernel/suid_dumpable
This will allow setuid processes to create corefiles.
With these changes, you should be able to receive the diagnostic information you need to further pursue the root causes of your ORA-7445 errors.
Most DBAs will agree that the automatic undo tuning features brought by Oracle9i and enhanced by Oracle10g greatly eased the difficulty of managing undo segments. Setting the UNDO_RETENTION instance parameter to the number of expected seconds that the longest running query would take seems pretty straightforward. But with Oracle10g, there is more to it than meets the eye. For starters, Oracle treats auto-extensible undo tablespaces differently than fixed, as follows:
- Fixed-size Undo Tablespace. Oracle ignores the UNDO_RETENTION instance parameter and sets an effective undo retention period based on a history of system performance. To see this value, query the V$UNDOSTAT view and look at the TUNED_UNDORETENTION column.
- Auto-extensible Undo Tablespace. Oracle will honor the UNDO_RETENTION parameter setting as a minimum number of seconds to retain undo blocks, but may keep them around longer. If the undo tablespace is pressured to reuse unexpired blocks, the tablespace will extend.
The auto-extensible undo tablespace seems like the more desirable arrangement, except when you consider than once an undo datafile is extended, it cannot be shrunk. Most businesses experience periods of intense activity from time to time, so an undo tablespace can grow to an incredibly large size, contributing to that bloated feeling your database has been complaining about.
So what is the lesson to be learned here? Our advice is to resist the temptation to go with an auto-extensible undo tablespace, instead keeping a close eye out for space pressure. Keep an eye on the UNXPBLKREUCNT column of the V$UNDOSTAT view to see if unexpired blocks are being reused. If they are, then you can make the decision whether or not to extend your undo tablespace.
—Contributed by Gary Sadler, Database Specialists, Inc.
Now would be a good time to consider attending the Oracle OpenWorld Conference being held November 11-15 at the Moscone Center in San Francisco. Registration is still open and can be found at http://www.oracle.com/openworld/2007/registration.html. You can register for the full event or get a free visitors pass if you register prior to November 10, 2007.
It’s funny how Oracle versioning has gone over the years. When I started out as a DBA many (don’t ask) years ago it almost seemed I received disks and tapes on a quarterly basis. Most were minor patches, and Oracle’s delivery system was much different than now-a-days. It also seemed that as soon as I got comfortable with one version another came out. I, and many DBAs, were constantly fighting feature-creep. Always wondering if we should learn something new or stay with the old friend—the version we were on. After all, who wouldn’t want the latest and greatest?
I have quickly learned, through many different experiences, startups, mergers, and exciting development projects that there are really two different and opposing sides of the version war.
- Many start-ups or new development projects will pick the latest and greatest version. They will often promote internally or hire fresh DBAs that typically only have experience in the Oracle version that the current project is using.
- Legacy systems tend to just exist and are not often upgraded. They just work and there is no reason to change that. The DBA staff is often very skilled in a version that in most circumstances will experience de-support.
Feature creep, the changing technology landscape, and a DBA staff that is under-trained or experiencing a fading skill-set can leave many companies in the lurch.
The good thing about working at Database Specialists, Inc. is the ability to continually keep database skills in check for all versions of Oracle—whether supported or not. The only version we don’t support is v.6—but we do have the skills for it (somewhere). Our clients concentrate on doing business while we pay attention to the fine Oracle details. If something in an Oracle version does warrant migration to a new version we are right there able to tell them why.
—Contributed by James Koopmann, Database Specialists, Inc.