BugDB Reporting

Sorry to do this on a public blog, but this is a posting which is only relevant to Oracle employees.


It seems a lot of teams are looking to create an analysis application on top of the BugDB, to perform their own analysis of the BugDB data for their own product. There is a right way and a wrong way to get at this data. The wrong slow way to get at this data is to build a report on top of a query across the database link to the BugDB and query this data each and every page view in your application. The more efficient way to get at this data is to create a local table in your schema and then refresh from the BugDB into this local table, on demand. This results in much less load on the BugDB database itself, and is also a much faster application for your end users.

I actually cooked up something for someone else in Server Technologies. It will create all the necessary database objects to refresh from the BugDB and it also includes an Interactive Report on top of the primary BugDB table RPTHEAD. Anyone is welcome to use this application as a starting point. It’s also hooked up with SSO authentication, so if you import this into your workspace on the internal instance of Application Express at http://apex.oraclecorp.com, you’ll be ready to go with internal single sign-on. All you need to do is:

1. Download and unzip the application export BugReports_installer.sql from: http://general.us.oracle.com/~jkallman/bugreports/BugReports.zip
2. Create a database link to the BugDB named ‘BUGDB.WORLD’ in your schema. https://bug.oraclecorp.com/help/ConnectToBugHelp.html#dblink
3. Import and install the application export BugReports_installer.sql into your workspace (presumably on apex.oraclecorp.com).
4. After installation, you’ll be prompted to Install Supporting Objects. You should simply click Next > through the wizard. Prior to this step, the database link BUGDB.WORLD must exist.

That’s all there is to it! Run the application, navigate to the Administration tab, choose your product and timeframe, click Refresh, and then click the Bugs tab to view the Interactive Report on top of your local data.

It may seem like a simple application, but with the Interactive Report on top of the BugDB RPTHEAD, each end user can create a variety of customized reports and save them. You can read about Interactive Reports here: http://www.oracle.com/technology/obe/apex/apexusr31/apexusr31.htm

Who says Application Express can't scale?

Over a year ago, I wrote about who is using Oracle Application Express on http://apex.oracle.com. At the time, I included some weekly statistics.

During our team meetings every week, I report on the overall usage of http://apex.oracle.com. I just wanted to report on some astounding numbers:

Total Page Views Distinct Applications Distinct Users
---------------- --------------------- --------------
       6225566                  3268           2778


Total Workspaces
----------------
         11643


Total Applications
------------------
           39849


Workspaces Approved
-------------------
              316

That's 6,225,566 page views in the past week, and this is still running on the same Dell PowerEdge 1950.

Now granted - the ability to sustain this type of load throughout the week deserves some credit to Kris Rice, who spent a fair amount of time analyzing Application Express on apex.oracle.com and identifying some major problem areas in performance. As well, he brought in some experts to perform OS tuning. The recent purge on apex.oracle.com removed close to 10,000 workspaces, schemas, tablespaces and datafiles. Lastly, a couple months ago, I implemented a Resource Manager plan which prevents someone from monopolizing the entire server. I'll be presenting a detailed discussion of Resource Manager and apex.oracle.com at ODTUG Kaleidoscope 2009.

A large number of these page views are from the bots of Search Engines, especially since ProMED Mail is run on apex.oracle.com. But a page view and execution of the engine is a page view and execution of the engine. All in all, that's a large load on a relatively cheap piece of hardware.

OraTweet - Micro-blogging with Oracle Application Express

About a year ago, Carl Backstrom was telling me how he was helping another gentleman in Oracle, Noel Portugal from the CRM On Demand team, with a micro-blogging site developed with Oracle Application Express. Carl game me a demonstration of OraTweet, tried to explain Twitter, tweets, and micro-blogging. I just didn't get it, at the time.

Today, micro-blogging appears to be all the rage. Dwight Howard from the Orlando Magic basketball team is on Twitter. CNBC's Fast Money television show is on Twitter. I am even on Twitter, but I really don't use it, as you can tell.

Noel's application, OraTweet, has been in use within Oracle for the past year. As Noel states on the oratweet.com site: "Teams around Oracle are finding ways to communicate with their teams while reducing email overload. The results have been amazing. Global team members are connecting and sharing information. Users have started to develop stand alone clients to consume OraTweet’s timeline. We have also integrated the OraTweet timeline to Oracle Connect, our own internal social network. Overall information is flowing faster in a lightweight format."

Noel Portugal has developed an excellent Web site which explains OraTweet, lets you submit feedback, and provides a link to download the entire source code for OraTweet: http://oratweet.com/

See you in Monterey?

Time is running out for the Oracle Development Tools User Group Kaleidoscope Conference in Monterey, California on 21-June-2009 through 25-June-2009. The lower cost advanced registration ends next Tuesday, 02-June-2009. The folks from ODTUG were kind enough to provide a template to use for a justification letter for your employer.

It all begins with the Application Express Symposium on Sunday, of which over 160 people are registered. Other than the opening remarks by some joker from Oracle, all other presentations at the Symposium are from customers and the community. And then there is a full agenda for the remainder of the week if you're interested in Oracle Application Express.

This is an outstanding, well-organized and well-run conference, giving customers the opportunity to interact with some of the greatest Application Express experts from around the world. This is a conference where you can listen, learn and discuss the solutions created by successful Oracle customers. Oracle is invited to participate, but this conference is run by ODTUG.

Disclaimer: I am not associated with this user group nor is there any financial incentive to me for the number of people in attendance.

Oracle Single Sign-On and apex.oracle.com

Application Express on apex.oracle.com is now registered as a Partner Application for Oracle Single Sign-On. This means that you can now create demonstration and sample applications, hosted on apex.oracle.com, and have the users authenticate via the Oracle Single Sign-On Login Server.

Who is registered on Oracle.com? Well, if you have ever asked or answered a question on the wildly popular Oracle Application Express discussion forum on OTN, then you already have an account.

To enable SSO authentication to Oracle.com in your application on apex.oracle.com, simply follow these steps:

1. Shared Components -> Authentication Schemes
2. Click Create button
3. Choose “Based on a pre-configured scheme from the gallery” and click Next
4. Choose “Oracle Application Server Single Sign-On (Application Express Engine as Partner App)” and click Next
5. Give it a name like SSO and click Create Scheme
6. In the subsequent report of authentication schemes, click the “make current” link for your newly created SSO one
7. Click the Make Current button on the confirmation page
8. Go get a beer (or coffee or tea) to celebrate

It's that simple.

To see this in action, here's a very brief sample application with a public and non-public page, using SSO authentication.

Cleaning Up

The architecture of Application Express is such that the database objects associated with a specific release all exist in a single schema. For the original Oracle HTML DB 1.5, all of the database objects existed in schema FLOWS_010500. As of the most recent version, Oracle Application Express 3.2, all of the database objects reside in schema APEX_030200.

When a new version of Application Express is installed, the following three actions take place:

1. The new version is installed into a new schema (this happens the same whether it's a new installation or an upgrade installation).
2. The meta data from the previous version is copied and sometimes transformed into the new schema.
3. All of the public synonyms which were pointing at the "old" version are now redirected to the new version of Application Express.

The beauty of this architecture is if an error occurs during installation or during upgrade, it's easy to revert back to the previous installation, as documented here. Both an APEX 3.1 installation (in FLOWS_030100) and an APEX 3.2 installation (in APEX_030200) can exist in the Oracle database at the same time, and with only one version active. Understand, though, if you choose to rollback and remove the newer version of Application Express, all of the changes made in the newer version (instance settings, application changes, new workspaces, etc.) will not be propagated back to the old version of Application Express if you switch schemas.

Once you have successfully upgraded your instance of Application Express, tested all of the upgraded applications, and deem the upgrade successful, there is no reason to maintain the old version of Application Express. Not only does it take up space in your database, it leaves a fairly privileged database account in the database, and this could pose a security risk if compromised.

For apex.oracle.com, which currently has over 20,000 workspaces, I always install the new version of Application Express into it's own tablespace. I do not install Application Express into the default SYSAUX tablespace. Application Express 3.1 was installed into tablespace APEX_REL31. When I recently upgraded to Application Express 3.2, and after we ran successfully for a couple days, it was easy to clean up the old Application Express 3.1 by issuing:

DROP USER FLOWS_030100 CASCADE;<br />DROP TABLESPACE APEX_REL31 INCLUDING CONTENTS AND DATAFILES;<br />
That's it! The privileged database user from the previous version is gone. All of the space on disk is reclaimed. And the newer version of Application Express 3.2 in APEX_030200 hums along just fine.


Note: There is one schema installed with Application Express, FLOWS_FILES, which persists across version upgrades. The sole purpose of this schema is to maintain uploaded files. You should never manually remove this schema unless you want to remove every facet of Oracle Application Express from your database. And if you really wanted to remove every trace of Application Express, I'd recommend using the apxremov.sql script anyway.



Considerations when dropping on Database 11g

If you're using Application Express with Oracle Database 11g (11.1.0.6 or greater), undoubtedly you had to enable Network Services so Application Express could send e-mail, use Web Services, etc. If you didn't do this, then you'll encounter errors like "ORA-24247: network access denied by access control list (ACL)" when APEX attempts to send e-mail or access any other network resource.

One important caveat to the instructions above - if you created an ACL for FLOWS_030100 (Application Express 3.1) and granted connect privileges to FLOWS_030100, after you upgrade to Application Express 3.2 , grant these same connect privileges on the existing ACL to APEX_030200, and you drop database user FLOWS_030100, the Network ACL will contain a dangling reference to FLOWS_030100 and will be invalid. What this means is that if you're running along just fine with an upgraded Application Express 3.2 and you drop FLOWS_030100, your delivery of e-mail may no longer work. Users will see error messages like "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

This situation happened to me over the weekend. After Application Express 3.2 was running for a month on our internal instance, the old FLOWS_030100 database user was finally dropped. Numerous issues were raised on Monday morning by workspace owners who said they could no longer send e-mail and they were seeing the error message "ORA-24247: network access denied by access control list (ACL)" in APEX_MAIL_LOG.MAIL_SEND_ERROR.

The only way I've discovered to rectify this situation is to manually drop the ACL reference to FLOWS_030100. I connected as SYS in SQL*Plus and ran:

exec dbms_network_acl_admin.delete_privilege('power_users.xml','FLOWS_030100',TRUE,'connect');<br />

This underlying database issue, with the ACL privileges not being dropped when a database user is dropped, was filed as Bug 7493477 and is targeted to be fixed in Database 11gR2.

Oracle Application Express in the cloud

You may have heard a thing or two about cloud computing. Jason Straub, from the Oracle Application Express development team, recently blogged about using Oracle Application Express in the cloud - and how you can run APEX in the cloud for as cheap as 20 cents USD / hour.

http://jastraub.blogspot.com/2009/03/test-drive-oracle-application-express.html

There's a little bit of setup to use the Amazon.com Elastic Compute Cloud, but once that's behind you, you can easily stand up your own Oracle database in the cloud for a tiny amount of money. And, in my opinion, this just further demonstrates how the browser-based Oracle Application Express hosted development and deployment environment is so well suited to cloud computing.