dbms_pclxutil.build_part_index

While working on our datapump workaround, I stumbled upon dbms_pclxutil.build_part_index. This is a handy little bugger that has been around since at least 8.1.5, and has some interesting limitations. For starters, the said index has to be partitioned and unusable. Not a biggie, but why? I also found out (the hard way) that not only do you have to run the procedure as the table owner, but also the index owner as well. Or to put it in other words, you have to create the index in the same schema as the table, and run the procedure from that schema only. That rather confounds me; when I asked Oracle Support about this, I was given some schtick about how they can't have users running around building partitioned indexes on other people's tables. If you ask me, I am thinking some developer back in the early '90s wrote this handy piece of code, but made some assumptions, and those assumptions were never corrected going forward. Strange how after all these releases, it is still not very well documented.

ah well....

11g and DBControl, Part 2

After finding two bugs with 11g DBControl already (and that was just the installation), I am finding another basic problem. I am experimenting with RAT and attempting to replay a workload. The ability to export a workload from a 10.2.0.4 database and replay it in 11g is a huge thing. If it works. If it works, we may have a huge demand for it; shipping off our workload to 3rd-party vendors (software and hardware) for benchmarking, internal testing of various upgrade options, etc.

So I set out to generate and capture a workload in a freshly upgraded 10.2.0.4 database. First I have to set pre_11g_enable_capture to true, which is a little odd. Why is it default to off? From there, you can loosely follow the online documentation:
backup source database
capture workload
restore database elsewhere
preprocess workload
replay workload

I was good up until the last step. Since the topic is DBControl, I decided to use DBControl to preprocess the workload after I datapumped my source into a target 11g database. DBControl then directs you to hit a link to replay the workload by configuration and initiating worker threads (aka, Client Connections). My workload was a mere 20 minutes (just for an initial test). My "Client Connections" have been attempting to start for 14 hours so far. They would probably run indefinitely if I let them.

I have Oracle Support working on this, but I have to admit, all these little buggy problems with my first experience in 11g is not very encouraging. No, let me restate that - they are downright discouraging. The "base" realease is 11.1.0.6, which seems to indicate the 5th minor patch. How did these issues make it through 5 rounds of testing? I am rather confused. Perplexed. Mystified. Discombulated. Nothing like kicking the tires and finding that they are flat.

Just as a side note, I also ran a workload capture for 14 hours (different box). Oracle generated over 37 thousand files! Wow.

Updated 19:35 pm
Here are all the bugs that matched from WebIV (kinda scary):
Bug.5932196 (80) DEADLOCK BETWEEN MV_REFRESH (DEL) AND AUTO GATHER_STATS_JOB Gen V111:
3 Bug.6668534 (80) REPLAY CLIENT HANG UPON REPLAYING KILLED SESSION (ORA-28) Gen V111:
3 Bug.6668157 (80) WRC CLIENT HANG WHEN START REPLAY FAILS Gen V1110:
3 Bug.6661772 (80) TRANSACTION ID AT CAPTURE FILE HAS AN INVALID VALUE Gen V1110:
3 Bug.5637692 (80) CANNOT REPLAY TKTUIM1.TSC WORKLOAD. WRC CLIENT ENCOUNTERS SIGNAL 3. Gen V111:
3 Bug.5636024 (80) TEST WITH UPIALL BUNDLED FUNCTION HANGS DURING REPLAY Gen V11:
3 Bug.6608197 (80) WRC CLIENT HANG WHEN 2 MMONS WAIT ON EACH OTHERS MSG Gen V111:
3 Bug.6596423 (80) WRC REPLAY PROCESS HUNG WHEN TESTING GLOBAL PORTAL EXCHANGE DUE TO I
NCIDENT Gen V111:
3 Bug.5587101 (80) INTERMITTENT HANG DURING REPLAY WITH OCI CONNECTION POOLING WKLD Gen V11:
3 Bug.6521269 (80) WRC REPLAY PROCESS HUNG WHEN TESTING GLOBAL PORTAL EXCHANGE DUE TO LOB Gen V111:
3 Bug.6141226 (80) REPLAY HANG AFTER 10 HOURS CAPTURE Gen V111:
3 Bug.6068506 (80) WCR REPLAY HANGS WAITING FOR COMMIT THAT WAS NOT SUBMITTED Gen V1110:
3 Bug.6046429 (80) TST&PERF - WRC HANG WHEN WORKLOAD REPLAY Gen V1110:
3 Bug.6025549 (80) WCR REPLAY HANGS WAITING FOR ROW LOCK

Metalink 3 coming "next year"

While communicating with an Oracle Global Customer Care employee today, I learned that Metalink 3 is coming next year. This should be interesting.

I filed 3 feedback SRs today:

• Allow Bugs older than 14 days to be views on Headlines
• Remove "News & Notes" from headlines
• Correct problems with "Certify & Availability" section of Headlines

I also some outstanding requests to view more than 365 days worth of SRs, and allow some sort of hierarchies within a single CSI.

Update Thu Apr 3 15:44:03 CDT 2008
From the horse's mouth:

Faster problem resolution
- Speed problem resolution with quick access to the information you need with our new personalized dashboard
- Facilitate faster resolution with a robust knowledgebase and powerful self-service tools
- Optimize system availability with enhanced Service Request (SR) management
Extended preventative, automated support capabilities
- Receive a simple, fast, easy way to maintain your Oracle systems
- Mitigate risk with simplified configuration management solutions such as Software Configuration Manager
- Proactively prevent problems and accelerate problem resolution with automated HealthChecks and Product Alerts
Increased personalization
- Utilize increased personalization and knowledge integration via OracleMetaLink 3's new user interface for powerful information management
- Personalize your homepage content for easier access to self-service tools and configuration data
- Quickly access your Service Request history with MySRs

11g and DBControl

After hearing and reading about so many of the wonderful new toys with 11g, I decided to give some of them a whirl; I tend to learn better when I can break it myself.

Unfortunately, I ended up breaking something else even before I could begin. When installing the software with the basic default options (starter database, DBConsole), emca blew chunks when trying to start dbconsole. This is not the kind of headache I wanted, but I am hoping to use this as a learning opportunity. Somehow. Hopefully I will be able to provide a resolution when this is over with.

For starters, the trace and emConfig.log are a bit hard to read. I did find some key occurences of "OC4J Configuration issue", so I turned to Metalink and google. Metalink was particularly unhelpful. The closest match I could find was Note 549079.1, which is good in and of itself, but 1) it says 10g, not 11g, 2) none of the 3 cases in step 5 applied (I tried all of them). I am still slogging through Google hits, but so far the only solutions I am finding are for 10g, and they do not seem to apply to 11g.

From emConfig.log
CONFIG: OC4J Configuration issue. /u01/app/oracle/product/11.1.0/oc4j/j2ee/OC4J_DBConsole_server1_TEMP not found.

Apr 2, 2008 2:12:05 PM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing /u01/app/oracle/product/11.1.0/bin/emctl start dbconsole
Apr 2, 2008 2:12:05 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at /u01/app/oracle/cfgtoollogs/dbca/TEMP/emConfig.log for more details.
Apr 2, 2008 2:12:05 PM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error starting Database Control
at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:869)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:250)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:213)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:235)
at oracle.sysman.assistants.util.em.EMConfiguration.run(EMConfiguration.java:460)
at java.lang.Thread.run(Thread.java:595)


Now, why would a required file not be found? Keep in mind this is coming straight out of the OUI, so I have this expectation that all required files would be automatically created.

I manually created the missing directories (recursive copy of existing directories) and the emca -repos create command was able to do a lot more. Now I get this really strange message:
CONFIG: Unable to determine local host vide /u01/app/oracle/product/11.1.0/server1_TEMP/sysman/config/emd.properties : No such file or directory


What is a "host vide"? Nothing on metalink or Google for that one.

UPDATE Fri Apr 4 13:56:56 CDT 2008
Resolved. See comments.