After upgrading to 10.2.0.5 on Linux, our Oracle Enterprise Manager would report intermittently that the OC4J was down- Target Name=EnterpriseManager0.serve3r Target type=Oracle Application Server Host=mtlincoln Occurred At=March 12, 2010 3:09:52 PM MDT Message=The application server instance is down Severity=Critical Acknowledged=No Notification Rule Name=Application Server Availability and Critical States Notification Rule Owner=SYSMAN If you immediately checked the status of the OEM, all responses reported acceptable- ./opmnctl status Processes in Instance: EnterpriseManager0.serv3r ——————-+——————–+———+——— ias-component                 | process-type              |pid | status ——————-+——————–+———+——— HTTP_Server                   | HTTP_Server           | 25823 | Alive LogLoader                        | logloaderd                |   N/A | Down dcm-daemon                     | dcm-daemon            |   N/A | Down OC4J                                | Home                      |   25822 | Alive…

I know I owe a few folks a blog on temp tablespace groups-  THANK YOU for your patience, I know these last couple months I’ve been way too busy and haven’t been able to allocate the time I would have liked here… Temp tablespace groups ROCK–  plain and simple..  This is a feature I have been working with extensively and have seen great performance improvement with in my datawarehouse environments when implemented correctly.  My main focus has been striping temp tablespace groups across fusion I/O cards, but the improvement moving from a standard temp tablespace to a temp tablespace group on standard hardware shows significant improvement…

Let’s say you have a database that everything can be recreated just by re-running a process and no nightly backups are required-  the database would be quicker to recreate from the backup running on it’s source database than restoring from a nightly backup or taking the performance hit of doing so.  Let’s say you have three tablespaces that are on I/O fusion cards that aren’t protected by hardware mirroring and losing these three could be more of a challenge than the business would like to have.  What options could you come up with to protect those tablespaces? Oracle Streams? CTAS…

Interesting Issue… I was working on an interrupted distributed, (remote) transaction that I’d come across in one of our smaller reporting systems and attempting to rerun since 2008 against a decommissioned database, (yes, I said 2008…) This is a very “sublime” issue, as Oracle thinks it’s just taking care of a problem behind the scenes, but, like I said, this database no longer existed and was taken down while this remote process was running. The problem consisted of three databases, the reporting database, we’ll call REPRT_1, the current production warehouse, PROD_1 and the older production base, PROD_X Oracle keeps submitting…

Tanel Poder had a great post about bind variable peeking, http://blog.tanelpoder.com/2010/02/02/bind-variable-peeking-execution-plan-inefficiency/  I run into this exact issue more often than I would like to and have found a few tricks to refrain it from happening in code that is “vulnerable” to the situation. Truthfully, there isn’t much you can do when Oracle has gone in and performed a bind peek and chosen a poor example to peek on.  As a DBA, you simply know there is an issue when the explain plan states that the CBO has determined the cost is exceptionally low to perform a nested loop on a…

The Lead Developer and I have been working very, very hard on tuning the main process that produces much of the revenue for our company. The tuning exercise has been very successful but we’ve run into some challenging on the pre-processing that occurs for each week to build the mart that the newly tuned process runs on afterwards. Due to this, I’ve been granted some time to monitor the build process a little closer than previously and although I have a lot of tuning to do, some of the largest challenges comes in the form of queries that the analysts…

I’ve come to realize that most folks don’t really understand the limitations in the allocation of PGA memory to hashing and sorting. They truly believe that if they set their PGA high enough, the database will just allocate as much memory as they have in the setting towards any hash or sort process, when in truth, (and I am referencing 10g here…) there is a “threshold” to protect the database from allocating too much PGA memory to any one process. This ceiling is for sorting or hashing and this is why I am such a strong advocate of viewing temp…

I’m preparing to build our new 11g environment and as I “study up” on all that is new in 11g, there are a few features that stand out over the rest, (at least in my book!) I/O Calibration I’m am challenging the upper most limits of my hardware with how much I/O I can produce. I have spent much of my time since I started with my new company correcting the I/O issues manually, (yes, ASM failed me, but I’m sure it will live up to my expectations someday!:)) The CALIBRATE_IO procedure that comes as part of the DBMS_RESOURCE_MANAGER packge…

After a lovely week off, I came back into work and brought up the OEM console to take a quick look at our databases. I noticed all our databases from one server were set to timing out on their uploads of the agent to grid control in the “All Targets” View. I logged onto the server and attemped to upload them manually: From the $AGENT_HOME- emctl upload agent; I received the following message: ERROR-400Data will be rejected for upload from agent ‘https://:/emd/main/’, max size limit for direct load exceeded [6827354/5242880] This error is due to a file limit on the…

Two weeks ago, after a new mart had performed excellent for 24hrs, a sudden and detrimental decline in performance was seen. I’d been trying to track this issue for almost two months, but had only seen it in two or three processes total and never to this level. I had some history from previous DBA’s, but they didn’t know what was causing it and when a mart has a limited lifespan before you are dropping and recreating it from a 6TB warehouse- any DBA knows, this is not going to be easy. The main goal of the mart is to…

…also known as follow up post on “dropping unused columns…” In my last post I discussed how to drop unused columns on a compressed, partitioned table. The reason I had come across this task, as I had listed in the post, was that a developer had a huge update that would have taken over 24 hrs that we could have done in 70 seconds if we utilized an exchange partition option. This update on the two partitions needed to be performed BEFORE I could get the maintenance window I needed to drop the unused columns. Being the “too smart for…

A developer needed to do some serious data changes to a partition. I thought I’d be a helpful DBA and suggested he do a CTAS of the one partition, make the change and we’d exchange the partition. He was thrilled when the update statement that I’d killed after 24 hrs of no successful runs was cut down to 70 seconds with the CTAS and then all we needed to do was have me exchange it back in- ALTER TABLE TAB1 EXCHANGE PARTITION P4706 WITH TABLE TAB_CPY_4706; ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE…