(Technology) Road Maps

When I was a kid we took a lot of road trips. Usually they were to see family and occasionally for vacation, but the part I liked was holding the atlas. I loved keeping track of where we were along the way, estimating how long until we reached the next big city (which often meant a food stop), and seeing all the other places along the way we could possibly go.

Yes, I said "atlas". Before GPS and online maps, even before mapping software existed, we carried an atlas in the car. We knew our starting point and our ending point, and we used the atlas to decide how to navigate from home to our destination and back again.  I remember my parents poring over the atlas with relatives: "If you take 568 back this way I think it will be a shorter route than the way you came." "Look, we'll be close to Chicago - we can stop at the science museum." "The last time Uncle Joe sent us that way we all got carsick - I'm not taking that road again!"

The atlas was wonderful. While we drove along I wasn't constrained to just our trip - I could flip the pages to other states and imagine all the other trips we could take and places we could go. As we passed big cities I would study the inset map of the local streets and find the landmarks we could visit someday.  When we took the inevitable detour or got caught in traffic, the atlas was our guide to a route around the blockage.

The atlas had its limits. If we didn't have the current year's atlas, we might find ourselves on a new highway that wasn't on the map.  Construction barrels could appear unexpected, any time, adding an hour to the trip. If we were off the highway in a small town there was no inset street map to guide us - and heaven forbid we stop and ask directions.

At some point there was a leap of technological advancement. The auto club provided a service where you met with an agent before your trip, told them your starting point and destination, and they would generate a customized map of your trip, on the spot.  It was a small flip book assembled from individual segments of the journey, each on their own page,  with easy-to-follow directions.  The proper highways and exits were noted.  Major construction projects were indicated, with possible detours.  Sometimes alternative routes were included on the map so you could decide whether to take the shortest mileage and save gas, the fastest speed limit and save time, or the rural route and enjoy the scenery.

This was a wonderful achievement.  No more wrestling with 6 square feet of atlas in the passenger seat.  No more fighting a state map to unfold and refold (I could solve a Rubik's Cube faster than I could properly refold a state map).  When you reach the end of one segment of the trip map, you just turn the page and focus on the next leg of your journey.  Most importantly, it was simple.  You no longer needed an experienced navigator to figure out the next exit. Any child in the car could direct the driver accurately.  Attention was focused on just the highways of interest and major exits that affect our route.  Possible delays and road blocks were anticipated with a strategy for how to deal with them.  Time estimates for each leg were printed on the page for that leg which settled all the questions of "are we there yet" and "how much longer, Daddy?"

-----

Fast forward to the days of turn-by-turn voice navigation.  I don't carry the atlas or the auto club trip plan any more, but I find myself using the same concepts in technology projects.  We often define a "road map" for large-scale technology projects, such as major system replacements or database upgrades.  We use terms like "current state" and "future state" rather than "home" and "Grandma's", but the ideas are the same.

What made a good map for a trip in my youth still makes a good road map for technology projects today.

Is your technology road map like the atlas?  Sure, it includes pictures of your old platform and what you are going to build, but how clear is the path to get from current state to the goal? Is any of the approach explained? Alternatively, is so much explained that the path lost in all the approaches and spider-web-like possibilities?  Are your road map review sessions like those conversations at my relative's dinner table about whose route is best?  Does the team get distracted by all the possible side outcomes of the project?  (This is all necessary to get to a concise project road map, but it doesn't need to become part of the everlasting documentation.)

Is your road map like the auto club map?  Is it focused on the effort at hand, rather than all possible projects?  Is it clear and concise (can a new person on your team explain it)? Does it list the main alternatives, determined after pre-filtering many of the possible options?  Does it include expectations about where the project may slow down and hit challenges?  Is it broken into manageable segments?  Are those segments time-bound with a rough estimate based on known constraints (think simple estimates like the trip's "distance" and "speed limits", not yet calculating specific tasks or detailed effort)?

In large organizations, road map efforts can be a large project in themselves, and may span months.  If this is the case, are you updating the road maps (like we replaced our atlas) to verify the original plan is still viable in the current environment? Can it be modified to use a more efficient "highway"?

I should mention the GPS.  The GPS with turn-by-turn navigation is great, but it's not really a map any more.  You only see a small segment compared to the big picture of the atlas or even the focused picture of the trip plan.  The GPS excels at providing just-in-time directions rather than just listing possibilities. If we carry the analogy through, the GPS is not a technology road map, it is a project-level plan. It is easily viewed as a modern, agile-style, just-in-time stream of activity, directing what you need to do next, just in time to do it.  If you get off course, the GPS recalculates, similar to how an agile team can be responsive to road blocks or detours that come up mid-project.  This is quickly becoming an analogy for another topic of its own, so I'll say no more.

-----

Clear, concise road maps are great for technology projects. Apart from the office this summer, I think the family needs a road trip. I might just turn off the GPS, hand my kids an atlas, and see where we end up.

Of Sequences, Insert All, and Prepared Statements

This post was almost titled "When is nextval Not the Next Value"

I was recently asked to help troubleshoot a constraint violation.  The developer was getting a unique constraint violation on a constraint they did not recognize (nor could I... thank you Hibernate, and your gosh-awful constraint naming).

Finding the constraint was simple enough. 

select * from dba_cons_columns where constraint_name='TC234467890'; 

I quickly saw this was a constraint on the ID column of the history table, which I knew was set from a sequence.  I asked to see the SQL that was failing.  They sent me part of their java code.

sql="insert all "

     + "into history (id, col1, col2,col3) "

     + " values (hibernate_seq.nextval,1,0, " + floatVar[i] + ") "

     + "into history (id, col1, col2, col3) "

     + " values (hibernate_seq.nextval, 1,0, " + floatVar[i+1] +") "

     + "into history (id, col1, col2, col3) "

     + " values (hibernate_seq.nextval, 2,0, " + floatVar[i+2] +") "

     + " select 1 from dual;";

executeUpdate(SQL);

After cringing at the lack of bind variables I saw that the Insert All was the culprit.  A quick test of the query in sqlplus confirmed it.  The code was attempting to increase performance by executing multiple inserts in a single call to the database.  This is admirable, but misguided.  Because all 3 inserts are part of the same SQL statement, the nextval of hibernate_seq is fetched at the first occurrence and re-used for each additional occurrence  in the same statement.  This caused the same ID value to be used for each of the 3 rows, which of course violates the primary key's unique constraint.

I advised them to break up the insert all into multiple calls to the database.  They did and that fixed the immediate issue. I then introduced them to prepared statements.  This developer was new to java, so there was no expectation that they were aware of it.

sql="insert into history(id, col1, col2, col3) values (hibernate_seq.nextval, ?,?,?)"

stmt=prepareStatement(SQL);

for (I=1;I<=4;I++)

{

  setFloat(1,floatVar[i]);

  stmt.execute();

};

The prepared statement approach to this has several advantages.

First, it fixes the sequence nextval issue.

Second, it allows more readable code by separating the structure of the query from the variables passed into it.

Third, it allows clearer type casting of data types.  This fixed the next issue the developer ran into due to implicit type conversions when the SQL was sent as a single string.

Fourth, and most important for performance, it allows the database to work more efficiently.  In the executeQuery approach, each SQL call is a unique string sent to the database.  Each is hard parsed and never re-used.  With prepared statements, the SQL is generic and common across all calls.  Hard parsing is not needed.  The value of variables are passed and bound to their location in the query at runtime.

Fifth, using bind variables prevents SQL injection... a topic for another day.

Why Does the Broadcast Van in Iron Man 3 Need Exadata?

One of the things I enjoy when watching Disney/Marvel's Iron Man and Avengers franchises is looking for the Oracle product placement.  I need help, I know.

Some of the stand-outs are Larry Ellison's cameo in Iron Man 2, as well as Oracle all over the advertising at the Stark Expo. In the Avengers, several racks of Oracle appliances were in the Shield lab where the tesseract was being studied. In Iron Man 3 Jarvis used the Oracle Cloud to reconstruct a crime scene, but the one that had me scratching my head was "Why does a broadcast van need an Exadata machine?"

My first thought was "that was ridiculous", but after I saw someone besides me also post this to Twitter, I started to ponder why you might do that.

So why would you consider Exadata anywhere in the first place?  Speed. Processing power. A turn-key Oracle RAC installation.  Tom Kyte has referred to Exadata as the closest thing he has seen to FAST=TRUE.

The other compelling reasons... Storage nodes and Hybrid Columnar Compression. The first application for this is data warehousing, but I can think of other massively parallel applications as well, some very relevant in a news van.

First, consider Gary the cameraman. He modeled his life after Tony Stark. He probably read the Oracle magazine articles about everything Stark Industries was doing with Oracle products, and talked his station into outfitting his van with some of that tech so he could work like Tony.

Video is a data intensive media.  Modern video, HD recording, and high frame rates can benefit from a powerful processing and storage engine.  A database solution with the Oracle Multimedia package could allow Gary to capture footage, and instantly categorize and organize it.  The parallel processing of Exadata storage nodes would allow tagging, processing, and searching large amounts of video in ways not possible with traditional tape or digital video equipment.

Aliens came through a wormhole and devastated New York. The news network may have revised their disaster recovery plans to make mobile copies of much of their infrastructure. Exadata in the field could be part of that strategy. If the central studio went down, advertising, billing, and other business functions could be brought up in the remote vans if needed. Feasible in our world? Maybe not, but we're in the Marvel universe for this discussion.  You never know when a super-villain is going to wreck a data center.

Is it feasible?  A Mercedes-Benz Sprinter with the extended roof would need to be modified slightly.  The stock high-roof model is half an inch short for a full rack installation.  At over 14 kW peak, the power requirements for Exadata are high for a mobile unit, but broadcast vans often are power hungry, so heavy power generation is often part of the package.  We'll assume the news channel bought a mobile-class Arc Generator from Stark Industries.  At 42 inches deep, there would not be a lot of room in the van for work space. Again, not unusual in a broadcast installation.  Cooling may be the biggest challenge, but if we are modifying the body to make things fit anyway, and powering the van with an Arc Reactor, this too is easily solved.

One of the things I love about science fiction is its ability to make us think about where we can push technology or where it might be applied that we don't normally think about.  Sure, this Iron Man 3 scene was rather contrived product placement, but it wasn't that long go that my phone was dumb and my 486dx was powerful.  If those devices have switched places, how long before we really are installing Exa-class machines in broadcast vans?

EXP_FULL_DATABASE Workarounds

As an application DBA, I was asked if I would be impacted if the system DBA revoked EXP_FULL_DATABASE from the application-DBA role.  I am frequently asked to perform export-import operations on a number of different schemas, and I prefer to do that work as myself rather than the schema owners, so I responded "Yes, there will be significant impact. What workaround do you propose?" Cue soundbite of crickets chirping.

Part 1 of ?: THE MOST BASIC WORKAROUNDS

The System DBA's concern is that EXP_FULL_DATABASE is a highly privileged role with implicit rights that must be protected; SELECT ANY TABLE and EXECUTE ANY PROCEDURE, in particular.  Their concern is well-founded.  With EXECUTE ANY PROCEDURE you can run absolutely anything in the database, and there are procedures owned by SYS that can be misused by a malicious user to take control of the database.

This presents a dilemma.  I need to be able to export any schema and import into another database on demand to support development and testing efforts.  Without EXP_FULL_DATABASE, what are my options?

* Use legacy exp/imp as the schema owner
* Use Data Pump expdp/impdp as the schema owner
* Use Data Pump over a NETWORK LINK, but only as the schema owner.

Are you sensing the same theme I am?  Let me add another twist.  I do not necessarily, as an application DBA, have access to the database server itself.  Unless I negotiate access to a dump directory on the server I cannot get to any exports created by data pump export. 

I personally like to keep data-owning schema passwords local to the database, and different in each environment.  This protects the production data (access to development does not imply access to production), protects the schema structure (I must have the connection and password right for a given environment as an extra precaution in running update scripts), and empowers developers (they can have access to change structure as they need in development without affecting test or production).


And finally, another security wrinkle... Development and Test practices currently include making copies of production data available in test databases to test solutions to data-related issues outside of production.  This is a good testing practice, but a common and poor data security practice.  That issue is a topic for another post.  For the sake of this discussion we will continue under the premise that copying production to test is a firm requirement.

So the complete challenge is: how can I effectively support my development and test community without going through the System DBA to perform exports and transfer export files, and without further compromising security?

Let's explore each option. The solutions in this post will assume that I know the source and target database passwords for each schema I need to manage, including production.  Each option will not the implications if the source password is not known.

LEGACY EXP/IMP

Using the legacy export and import utilities as the schema owner is the simplest option.  It has the inherent risk that I must know the schema password for the source database.  If the source is production, that may present an information security risk. If I cannot know the production password then a system DBA must perform the export for me.

Export and Import served us for many versions, but today they show their age.  Parallelism is limited (do it yourself) and restarting the process means modifying parameter files to force it to start and stop where you need.  It does not have the same monitoring or exclusion features as data pump, so it is not my preference if I can avoid it.

If I know the schema passwords for the source and target databases, this can work.  It is certainly not ideal, but it does allow me to maintain different passwords in each environment, since exp and imp are separate steps.

DATA PUMP EXPDP/IMPDP

If I have access to the data pump directory on the database server, this may be an option. Its benefits over legacy exp/imp are in the enhanced restart, monitoring, parallelism, and exclusion features of data pump, which are well documented elsewhere.  It suffers from the same limitation as EXP/IMP in that I must know the source database password for the schema I want to export.  If I do not have access to the data pump dump directory on the database server, I am again in need of the system DBA to be involved in routine data copy activities which may not be the best use of their time.

If I have access to the data pump exports and the schema passwords in the source and target databases, this can also work.  It does not require the schema password to be the same in the source and target databases, since expdp and impdp are separate steps.

DATA PUMP VIA NETWORK_LINK

Data pump has the option to skip the intermediate export file altogether.  While this may be slower and more network round-trips than using a disk, you do not need access to the data pump dump directory on the database server.  In my case, this is an advantage.  With EXP_FULL_DATABASE on the source I can have a generic database link defined from target to source, and connect as myself (my ID is LDAP-authenticated and the same password for any database). Without EXP_FULL_DATABASE on the source, the NETWORK_LINK option is not available to copy different user's schema.

Generic database links require the user to have the same password in both databases.  I can define a user-owned database link, but that leaves the password in plain text in the USER_DB_LINKS view in the target.  Even if I only create the link while I am moving data, if this is a development database anyone with the schema password can see the database link definition while I am performing the copy.

One solution is to change the target database password while I am performing a copy.  This locks out anyone else who does know the password, preventing access while the copy is occurring.  This also means I can create the database link to the source, perform the copy, then drop the database link before resetting the schema password to its usual value.  This prevents others from learning the source database password by querying USER_DB_LINKS in the target database. 

Part 2: SOURCE PASSWORD UNKNOWN ?

 If the source schema password is unknown then we must come up with some other way to run an export for a different schema.  This requires some analysis of the PL/SQL behind expdp and impdp to determine the equivalent privileges needed without SELECT ANY TABLE and EXECUTE ANY PROCEDURE.

The next post in this series will include the results of that investigation and any additional strategies that come from it.

 

Considering the Cloud? You Might Already Be There.

I'm at IOUG Collaborate 12 this week, and one of the sessions I attended gave me a new perspective on "The Cloud", or at least what it means to have and use a "Private Cloud". I've always had a bit of an eye-roll response to the concept of a Private Cloud - why put a trendy name on something as commonplace and mundane as putting more than one thing on a server/SAN/mainframe, which was my view of a private cloud.

The session I attended was by Michael Timpanaro-Perrotta, of Oracle Corporation. The prentation was one of the more enlightening overviews of what cloud architecture is that I have come across. I won't re-present Michael's work here, but will share a few of my "Aha!" moments.

Aha Moment #1

There is nothing magical about "the cloud". A cloud is presenting technology as a standardized service. It is less about the technology itself or where it is hosted and more about how the service is deployed and delivered. If we are deploying servers in a cowhere fashion where the upkeep becomes transparent to our customers, that is one type of cloud.

Aha Moment #2

In the database sense, a cloud database can be a centrally-managed, consolidated instance that serves a variety of customers and applications. It turns out much of my career has been "cloudy", in this sense. Whenever I have the opportunity, I am a proponent of consolidating instances for ease of manageability and reduced maintenance cost. These are key cloud concepts - common, standardized deployments that serve multiple purposes.

Aha Moment #3

I'm building a private cloud database today. Who knew? Our project is labelled a "server refresh" - nothing about "cloud" in the name, design, or requirements - but we are consolidating similar instances with compatible workload into a single instance. We are migrating from a dedicated host to a shared O/S infrastructure. Eventually this database will move from the shared O/S cluster into an even-more-shared RAC cluster. This is the very process Michael layed out in discussing how organizations move from silos into a private cloud model.

"Cloud" may be the buzzword of the year, but it is not as mysterious as you may think. If you look at your current technology projects, those that simplify things, standardize, virtualize, or share architecture at some level may very well be cloud projects.

APEX 4

I've been working on a small hobby application recently with Oracle XE and finally put an APEX front end on it. I started with the default APEX that installed with 10g XE (Apex 2.2). Knowing APEX 4 has nicer features I considered upgrading sooner, but didn't see the need for my project.

The tipping point came when I tried to use make some changes using Firefox 4. Most things worked fine, except when I tried to modify a PL/SQL package. In FF4, the text was in a red, uneditable text box instead of the usual text box.

I exported my APEX schemas (FLOWS_020000 and FLOWS_FILES) as a precaution, then followed the APEX install/upgrade instructions found here: http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/otn_install.htm#BABHHIJB .

I was a little concerned with the documentation's description of the various components to think about when installing APEX (APEX listener, Oracle HTTP server, etc) but I expected the APEX install to figure out what I was running and take care of things since I was upgrading a functional, stock install on 10gXE. That expectation proved mostly true.

(Sidebar: I'm normally much more thorough about reading documentation before jumping into upgrades, and I like to understand what is changing before I change it. In this case, APEX is promoted as something that is easy to use and doesn't take a lot of deep technical knowledge to manage, so I purposely read as little as possible to see how well that assertion held up.)

After downloading and unzipping files, the actual upgrade process took about 45 minutes on a desktop-class machine, running Windows 7 Enterprise and Oracle 10g XE. The new APEX objects installed as expected into the schema APEX_040000, and my application was also upgraded without incident.

Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the APEX_040000 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)

After installation, application upgrades, and validation completed, I had to update the image directory using @apxldimg.sql C:\apex_4.0.2_en (because I was upgrading, not installing fresh) . I ran this twice: the first time I specified a deeper path to APEX files (second-guessing the documentation) and it failed. I re-ran with only the path to the directory I extracted files to (like the documentation said) and it worked. Trust the documentation. The image update took about 4 minutes.

The documentation contains a lot of steps specific to Oracle 11g. Since this is on 10g XE I skipped those altogether. Maybe if I want to explore a REST API I will install the APEX listener, but that is a project for another time.

I attempted to connect to the APEX administration site. I was asked to change the admin password, and I found the new APEX has much stricter password rules than before (at least for the administrator - default settings for APEX users are less strict, but easily configurable). After changing the password I could access the administration page. I saw several new panels and features to explore. I reset the password for my main development workspace, and re-connected to APEX as that developer.

Now that I was fully upgraded and ready to try things out, here's what I found...

So the first test - did this solve the problem I was having?

Yes. The path to the database object browser changed a little, but once I found it I was able to edit the package spec using Firefox 4 . In APEX 2.2 the Object Browser was on the home screen of the workspace. In APEX 4 It is under Home>SQL Workshop>Object Browser


Now the more important test - what changes did I observe in my application?
When I built my application in APEX 2.2 I used APEX internal authentication, so the password for the application user (call it "XYZ") was not the same as the schema owner/APEX workspace user "XYZ". After upgrading the IDs were merged and use the same password. When I used the new password I entered the application fine.

The look and features of my application continued to work as they always had. I found no differences in my application itself.


Finally - what did I see that wasn't there before (new toys)?
Where to begin?

• The new APEX Administration tab has an Activity Monitor and a Dashboard with metrics at the database, application, web server, and user levels. There is a lot to explore here, and a lot that will be useful in monitoring a deployment (at least one larger than my hobby system).
• The Manage Service tab has features for managing the system and application Meta Data and features to better support collaborative development.
• The Team Development tab offers a host of project management features. I'm eager to dive into this and use it as a development task list, project tracking, and other features.
• The Application Builder adds a tab for Supporting Objects which generates application deployment and upgrade scripts. This supports more structured deployments from a development server to test and production servers... much easier than APEX 2.2.
• The SQL Workshop contains simpler (or at least more obvious to me) tools to import and export spreadsheet, text, and XML data.

These options only scratch the surface.

Conclusion: The upgrade was simple, but not automatic. Follow the documentation. It will save you from mistakes. In my case I didn't worry about the APEX listener yet, but I may explore that in the future.

The new tools and look are welcome improvements. I look forward to diving in with APEX 4.

Oracle XE 11g Early Adopter

I was excited to see Oracle XE 11g has finally been released on OTN for early adopters. Loading it up, I was disappointed by a couple things right off the bat. I know, "early adopter" is a big red flag. (See update below. My disappointment is a result of my ready-fire-aim approach to trying XE 11g. Read the DOCS first and you won't be surprised).

First off, I had to uninstall my 10g XE since I can only have one "OracleServiceXE" service. I can live with this one - I purposely wanted to deal with the default install. The real surprise here was that uninstalling XE also uninstalled my 10g XE DATA, not just the binaries. I made a cold backup copy before uninstalling, but still - uninstallers should KEEP the data files or at least warn me. Grr.

Second off, I saw the terms and conditions were the usual OTN terms, including "don't use this for internal data processing without a license" (paraphrased). I found that odd, since the 10g XE had its own language that states "OK for production use" (paraphrased again, I didn't look up the exact language for the sake of this post).

So, 11g XE installed. (Still no notice of what the restrictions of the XE license are for 11g? Are they the same as 10g? More storage? Don't know, nothing in the download, install, or terms of service have enlightened me yet). Install is simple enough. I would have preferred an option to install clean or upgrade an existing 10g XE database, but this is an early adopter release, so I can live with that... I guess.

Command line sqlplus works... no surprise there... try SQL Developer. Surprise! SQL Developer is not working now. Why should that have changed? Now I have some investigation and troubleshooting to do.

I'm at Collaborate 2011 this week, so maybe in some of the evening downtime I'll poke around and try to get everything working again.

---

UPDATE: A quick search of the OTN Forums led me to the fine documentation I did not bother looking up before. In it is the familiar language licensing 11g XE for "purposes of developing, prototyping and running your applications for your own internal data processing operations".

So, any relaxed (improved) restrictions?

• Single instance per server (no change)


• Only uses 1 CPU (no change)


• Supports up to 11GB of user data (up from 4GB, that is a welcome increase)


• May use up to 1GM RAM (no change)

How about features? Here are the quick standouts from perusing the list of what is NOT available (see the documentation for the full list):

• Pre-compilers and SQLJ


• Java support in the database


• Block Change tracking for incremental backup


• Flashback (in just about any form)


• Online table redefinition and index rebuilds


• Tablespace point-in-time recovery


• Rolling upgrades


• Advanced Security


• VPD / Label Security / DB Vault


• Secure External Password Store


• Client-side query, and other types of results caches


• Partitioning


• Transportable Tablespaces


• Streams


• Multimedia


• Spatial


• ASM


• Resource Manager

Besides the license and feature summary, the full 11g XE Documentation set is available at http://download.oracle.com/docs/cd/E17781_01/nav/portal_booklist.htm.

In the README document, we find why SQL Developer stopped working. SQL Developer 3.0 must be used. This is a known issue and is stated that it will be fixed when 11g XE is no longer beta. In the meantime, I was thinking of moving to SQL Developer 3 anyway, since it is no longer in pre-release status.