Saturday, May 25, 2013
Monday, May 13, 2013
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?
Tuesday, July 10, 2012
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.
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.
Tuesday, April 24, 2012
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.
Saturday, June 11, 2011
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.
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.
Sunday, April 10, 2011
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
- Transportable Tablespaces
- 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.
Monday, January 18, 2010
I had been expecting a career move to take me deeper into Oracle-specific technology, where I hoped to gain more technical skills in core database administration and new features. As it turns out the opportunity that presented itself was not one of more depth in Oracle, but less - however it is adding breadth via exposure to Informix and DB2.
Today I find myself needing to support Oracle at the application/schema level, but also needing to support DB2 at a similar level, and Informix from the installation on up. At one point of my career I may have said "I'm an Oracle guy, no thanks". Today I'm more of a "right tool for the job" guy, so I welcome the chance to learn the competing DB systems.
In studying both DB2 and Informix, I find myself translating the concepts of each into the concepts I am familiar with from Oracle. Sometimes I must be careful, such as when seeing the word "tablespace", which means different things in each RDBMS. But overall I am finding conceptually they are more alike in operation than I exepected. I am finding specific parts about each that I like, and other parts where I prefer Oracle's approach. (Perhaps a "universal translator" cross-reference would be an interesting summary to put together and post here at a later date.)
Professionally, I like where this is going. I believe this experience is going to make me a better DBA overall, as I will get to know the differences and strengths of different vendor's database systems. Down the road this could give me the ability to pick the most appropriate system for a specific project, or it could help me understand the "gotchas" to expect when migrating a system from one platform to another (for example, how are NULLs treated in a query in each system?).