Oracle Database 11.2.0.3 new features

Oracle Database 11g Release 3 (11.2.0.3) was released on the 23rd of September 2011.

For now, 11.2.0.3 is available only for Linux x86 and Linux x86-64 and we have to download about 5G of data (7 files altogether).

11.2.0.3 is a full installation of the Oracle Database software meaning that you do not need to install 11.2.0.1 before installing 11.2.0.3.

According to the ReadMe for 10404530, the list of bugs fixed for Oracle Database 11g Release 2 (11.2.0.3) is in document 1348303.1: List of Bug Fixes by Problem Type available on the My Oracle Support Web site. Check it out please. It is not a short one. Not at all.

The complete list of new features of 11.2.0.3 can be found here. They are by far much less than the new features of 11.2.0.2, here is a summery of these new features:

Oracle ACFS Snapshot Enhancements
Oracle ACFS Security and Encryption Features
Support for ACFS Replication and Tagging on Windows
Oracle LogMiner Support for Binary XML
SQL Apply Support for Binary XML
Oracle LogMiner Support for Object Relational Model
SQL Apply Support for Object Relational Model
Deprecation of Obsolete Oracle XML DB Functions and Packages
Oracle Warehouse Builder Support for Partition DML
Enhanced Partitioning Support in Oracle Warehouse Builder
Oracle Warehouse Builder External Table Data Pump Support
Oracle Warehouse Builder External Table Preprocessor Support
Compressed Table and Partition Support in Oracle Warehouse Builder
Support for PL/SQL Native Compilation

Starting with Oracle Database 11g Release 2 (11.2.0.3) you can enter the Proxy Realm information when providing the details for downloading software updates. The proxy realm identifies the security database used for authentication. If you do not have a proxy realm, then you do not have to provide an entry for the Proxy Username, Proxy Password, and Proxy Realm fields. It is case-sensitive.

The following initialization parameter is new to Oracle Database 11g Release 2 (11.2.0.3): AWR_SNAPSHOT_TIME_OFFSET

Starting with Oracle Database 11g Release 3 (11.2.0.3), Oracle Universal Installer displays a new screen, Grid Installation Options, which helps in the installation of the examples software on an Oracle RAC database. The examples software is installed in the selected Oracle RAC home on all the nodes where it exists.

Nothing impressive but with a new patchset we do not actually expect new features, rather we hope that all (did I just say all) old bugs have been somehow fixed.

Unfortunately, I am waiting for the accessibility of document 1348303.1: List of Bug Fixes by Problem Type.

Exadata does not currently support Database 11.2.0.3.

When downgrading from release 11.2.0.3 to 11.2.0.2, the following error is raised when you run @catdwgrd.sql (reference Bug 11811073): ORA-20000: Upgrade from version 11.2.0.2.0 cannot be downgraded to version.

Data Pump Export operations do not work if the DMSYS schema is not removed as part of the upgrade to release 11.2.0.3 (reference Bug 10007411).

Oracle Database release 11.2.0.1 or 11.2.0.2 upgrade to Oracle Clusterware release 11.2.0.3 is not supported if the 11.2.0.1 or 11.2.0.2 release of Oracle Grid Infrastructure for a cluster is installed in a non-shared Oracle home and the 11.2.0.3 release of Oracle Grid Infrastructure for a cluster is installed in a shared Oracle home (reference Bug 10074804).

Starting with release 11.2.0.3 of Oracle Database, the Data Mining Java API is deprecated.

Starting in release 11.2.0.3, configuring HTTPS with Oracle XML DB requires that you first set up SSL_CIPHER_SUITES to include SSL_DH_anon (reference Bug 8403366).

After upgrading from 11.2.0.1 or 11.2.0.2 to 11.2.0.3, deinstallation of the Oracle home in the previous version may result in the deletion of the old Oracle base that was associated with it. This may also result in the deletion of data files, audit files, etc., that are stored under the old Oracle base.

Database links imported from an 11.2.0.3 database into a version prior to 11.2.0.3 (including 11.2.0.2) will not be usable in the import database. Any attempt to use a database link will cause the following ORA-600 error: ORA-00600 [kzdlk_zt2 err], [18446744073709551601]

For features not available or restricted in 11.2.0.3, click here.

For a complete list of all possible bugs and issues, check on the Open Bugs.

11.2.0.3 was called Oracle Database 11g Release 3 for a short period of time but Oracle fixed the documentation.

Oracle Database Appliance and Automatic Bug Fixing in the Cloud

Mathematician Alfred North Whitehead said: “Civilization advances by extending the number of important operations which we can perform without thinking about them.”

Same holds for the Database!

Oracle have just announced the new “Oracle Database Appliance” with self managing automatic features preconfigured (simple, highly reliable, affordable database system for small/midsize enterprises and departments):

I have just gathered a list of the automated processes offered by Oracle within the Oracle database (in no order whatsoever):

– Automatic Statistics Collection
– Automatic Tuning Optimizer (ATO)
– Automatic Repair in a Data Guard Configuration
– Automatic Undo Management
– Automatic Undo Retention Tuning
– Automatic Shared Memory Management
– Automatic Space Segment Management (ASSM)
– Automatic PGA Memory Management
– Automatic Memory Management (AMM)
– Automatic Degree of Parallelism
– Automatic Storage Management (ASM)
– Automatic RAC Database Startup/Restart
– Automatic Maintenance Tasks
– Automatic Tablespace Point In Time Recovery
– Automatic Workload Repository (AWR)
– Automatic Service Registration
– Automatic SQL Tuning (my favourite!)
– Automatic Database Diagnostic Monitor
– Automatic Segment Advisor
– Automatic Diagnostic Repository (ADR)
– Automatic Checkpoint Tuning
– Automatic Maintenance Jobs
– Automatic Global Index Maintenance During DDL
– Automatic Client Failover
– Automatic OCR Backup Rotation
– Automatic Plan Capture with SPM
– Automatic Refresh of Materialized Views
– Automatic VIP failback
– Automatic Block Recover
– Automatic Disaster Recovery Fails with RMAN
– Automatic Channel Failover
– Automatic Synchronization of Oracle Text Index
– Automatic Registration of the Database with the Default Listener
– Automatic Offlining of the Datafile in Noarchivelog
– Automatic Datatype Conversion
– Automatic Tape Drive Cleaning and a Cleaning Tape in a Tape Library
– Automatic Controlfile Backup
– Automatic Eject Of Tape After Backup Using Oracle Secure Backup
– Automatic BackupSet Failover On Missing or Corrupt BackupPieces
– Automatic BMR (Block Media Recovery)
– Automatic System Tasks
– Automatic Database Performance Monitoring
– Automatic Archiving
– Automatic Propagation in Replication
– Automatic Job Scheduling
– Automatic Resume of DataPump

Quite a list I would say, right?

An excellent paper from Oracle called Oracle Database 11g vs. IBM DB2 UDB V9.7 points out the most important trend of database manageability: the self automation of the database product. Let me quote (part of) the conclusion of the paper:

“The Automatic Database Diagnostic Monitor (ADDM), SQL Advisors and Real Application Testing are just some of the unique Oracle Database 11g features that are yet unmatched by DB2 version 9.7. Oracle Database 11g is the only database product available today that automatically collects and manages historical performance data for self-management purposes, periodically and automatically analyses this data and makes tuning recommendations. Oracle Database 11g is also the only RDBMS with rich software quality management features for real workload testing. These distinct technologies are at the core of the next generation of Oracle databases that represent simplicity, ease of management and software quality management while still providing the most robust, reliable and secure of relational databases.”

How true indeed! But what we would like to see in the future is even more: how about automatic bug fixing in the Cloud? Just like this:

1. First, we set the credentials with Oracle Support: How to set the ‘My Oracle Support’ Preferred Credentials in the Grid Console? [ID 1168603.1]
2. The Oracle database creates an incident and it is transferred to Oracle Support via the Enterprise Manager Support Workbench.
3. Then it is internally verified if the problems is bug related.
4. If it is a bug then Oracle’s own BugDB checks for a patch or workaround which fixes the bug.
5. If there is a patch available, then the patch is automatically uploaded to the clients cloud environment and then applied (online of course)!
6. If there is a workaround with an init.ora parameter, then the “alter system” command is automatically applied, as what Oracle Support can remotely run in the client’s database is controlled by the client with a new init.ora parameter called mos_cloud_permission_level.

That is what I call automation!

Note that something similar is even now offered by Oracle via the SQL Repair Advisor. It is a semi-automatic patching of SQL statements throwing ORA-600 or ORA-7445. But here by patch Oracle mean more of an SQL transformation than a standard patch downloadable from MOS.

Oracle OpenWorld 2011

I am Speaking at Oracle OpenWorld 2011 and I hope you will join me there!

Welcome to my session: Tuning Toolkit for Advanced DBAs: Learn from the Past, and Prepare for the Future. The presentation will be on Tuesday at 03:30 PM, Moscone South – 104.

There will be over 80 Oracle ACEs and ACE Directors who will speak at OOW 2011!

The content catalog shows 75 sessions on Database performance and scalability out of the 306 database conference session. Just a reminder that 4-5 years ago the database conference sessions were about 100.

The database stream has been now divided into the following 11 substreams:

– Cloud Consolidation: 39 sessions
– Data Warehousing: 32 sessions
– Database Manageability: 55 sessions
– Database Platforms: 31 sessions
– Database Security: 23 sessions
– High Availability: 47 sessions
– MySQL: 29 sessions
– Oracle Exadata: 57 sessions
– Performance and Scalability: 75 sessions
– Specialty Data and Search: 15 sessions
– Storage Management: 24 sessions

One of my favorite topics, Oracle Enterprise Manager (part of the Cross Stream track), will be covered in 161 conference sessions. I have said it before, I have had it in several final conference slides, I will say it now: Oracle Enterprise Manager Grid Control is probably the best feature/tool/utility in Oracle after 9i.

And for the DBAs, as this is a DBA blog, here is a complete list of all sessions having the work “DBA” in the title:

14642: AWR and ASH in 3-D: Performance Analysis Tools No DBA Has Seen Before
20880: Becoming a Rock Star MySQL DBA
9218: DBA Fusion: An Introduction to Oracle Enterprise Manager 11g Architecture and Installation
13781: Day in the Life of a DBA: Cloud-Ready Management Solutions for Any IT Shop
14641: Extreme Database Administration: New Features for Expert DBAs
13081: Mastering Oracle Data Pump: Technical Deep Dive into Performance/Internals for Hands-on DBAs
15143: MySQL for Oracle DBAs, or How to Speak MySQL for Beginners
13446: Oracle Database Vault: DBA Best Practices
8046: Oracle Exadata Management for Oracle DBAs
14365: Oracle WebLogic Server Management for DBAs: Cross-Tier Visibility from JVM to Database
6681: Trends in Database Administration and the Changing Role of the DBA
14644: Tuning Toolkit for Advanced DBAs: Learn from the Past, and Prepare for the Future
8044: What If Kramer Were Your DBA and Seinfeld Tuned Your Database?
28900: Launching the IOUG Virtualization SIG: 360 Degrees of Virtualization for Oracle DBAs

If you wonder why so many people attend OOW, here are the high-level benefits of attending Oracle OpenWorld according to Oracle:

– Participate in sessions and hands-on workshops led by the world’s foremost IT experts
– Find out firsthand how to streamline upgrades and deployments
– Attend sessions in the all-new Server and Storage Systems stream
– Dig deep into application deployment, scaling, upgrading, and best practices
– Meet with your industry and technology peers
– Share notes in special interest groups, and product and industry sessions
– See hundreds of partners and customers as they present product demos and solutions in three Exhibition Halls

And here is a picture from LJE’s keynote on Cloud Computing I took last year from my seat:

P.S. I cannot find a single session on Oracle bugs and we all know that they play a major role in the database world. For sure, many DBAs would be very interested in a presentation called “11gR2 top 10 bugs“.

The role of the DBA in the Private Database Cloud

Nowadays, you can buy almost any outsourcing services. However, you can not outsource the responsibility.

If your databases are in a cloud (Amazon, Microsoft, etc.) whose responsibility is it when all is down? Shared responsibility? But not only in IT, almost everywhere shared responsibility is often no one’s responsibility.

See, Google Docs was unavailable for a period on Wednesday. Yesterday (9 September 2011), Microsoft’s Office 365, the alternative to Google’s suite of online apps was also down.

Microsoft online services hit by major failure

These are all high profile failures. As DBAs, we might wonder what is best from database perspective: public or private cloud? I vote for database consolidation which is almost a synonym for the new modern concept of a database private cloud.

The best Cloud presentation I have listened to so far was this week’s “Database Consolidation onto Private Clouds” by William Hardie and Mark Townsend.

A consolidation poll shows that the database is the top non-mixed consolidation candidate.

Based on my experience, the most important benefits from consolidation are as follows:

Harmonized environment:
– Less time needed for database management
– Easier monitoring
– Less unexpected problems

Stronger security:
– Less cost:
– Less database licenses (paid per CPUs)
– Less storage needed
– Less memory and CPU needed
– Less computers needed

Better performance and availability:
– Standard parametrization and settings
– Regular common reorganization
– Proactive monitoring

And here are the poll results due to Oracle:

Once the databases are in the cloud, what is the role of the DBA? There are so many articles on the web about that. Read what Robin Schumacher has to say: Will the Cloud obsolete the DBA? Let me quote him: “But, in the end, will an organization be able to send their DBA packing just because they moved their database to the cloud? Not a chance.”

I agree. And on the contrary: DBAs will be even more valued and needed for three reasons:

1. The cloud will bring more challenges and need for DBA work to the enterprise.
2. After every new database release the complexity of the database engines grows quite significantly.
3. The importance and the size of the data grows faster than technology can handle it.

Just to make myself clear that DBAs will not become endangered species, I will mention that the affords needed to apply Grid Infrastructure Patch Set Update 12419353 (= 11.2.0.2.3 GI PSU for Linux x86-64) are far away from simple.

The 236MB file which requires the 29MB OPatch utility version 11.2.0.1.5 or later has a ReadMe file with cases and subcases which is far more complex than the ReadMe files of Oracle 10g. Have you ever seen bugs filed against the ReadMe? Here you go: Bug INCORRECT GI PSU 11.2.0.2.3 PATCH 12419353 README ‘PATCH PREREQ’ INSTRUCTION.

I wonder when the Cloud DBA will have to apply in the future CI PSU (Cloud Infrastructure Patch Set Update) will the ReadMe come as a separate .zip file or in a small booklet 🙂

Finally, a very good slide from William Hardie and Mark Townsend on database cloud architecture!

The efficiency of the DBA in the infinite corporate loop of cost savings

One of the founders of modern macroeconomics John Maynard Keynes said: “Whenever you save five shillings, you put a man out of work for a day”.

Richard Cookson, chief investment officer of Citigroup’s said last week for CNBC: “The corporate sector continues to simply slash costs rather than focus on top-line growth. If you carry on just cutting costs and cutting people, at some stage the growth will stop.”

Business insider says it directly: Corporate Efficiency Is Getting Absurd.

Efficiency is a great concept but how often do you see a DBA working efficiently in your job who gets to leave the office on time? … Indeed.

It does not take much to fall into the trap of database cost savings. Often a nice .ppt slide with the following challenges can do the trick:

• Lack of true shared service database infrastructure
• Minimal adoption of virtualization and clustering
• Multiple configurations with minimal automation in change management, provisioning and patching
• Highly unbalanced cost on database licenses and database storage
• Minimal infrastructure to improve availability and performance

Add a cost saving graph or diagram, something like this, and be sure someone will bite the bait.

In order to dig deeper into the issue, I will come with two concrete examples:

1. Cost savings due to downgrading the Oracle edition from EE to SE: the features not available with Oracle Database Standard Edition or Standard Edition One is not that long. Here are few:

• Oracle Data Guard and Transparent Application Failover (TAF)
• Online index maintenance, Online table organization and Online table redefinition
• RAC customers must use Oracle Cluster Ready Services as the clusterware; third party clusterware is not supported
• Block-level media recovery, Parallel backup and recovery, Change-aware incremental backups and Trial recovery
• Oracle Flashback, Oracle Advanced Security, Oracle Label Security, Virtual Private Database and Fine-grained auditing
• Grid Control packs, Database Resource Manager, Oracle Partitioning, Data compression
• Bitmapped index and bitmapped join index, parallelism
• Oracle Streams: no asynchronous capture from log files, Advanced Replication

However, if you plan on downgrading dozens or scores of databases, then for sure there will be features not available on the low cost edition. Migration takes time. DBAs spent extra efforts on such migrations plus note the time they will spend on looking for workarounds for the not supported features. And I am talking big time.

And how about the practical issues: backups cannot be taken any more in parallel, Grid Control packs are not supported?

Bottom line: will the efforts spent be really worth the savings? Will there be savings? How about the work and improvements DBA could have done instead?

2. Cost savings due to taking into use Oracle Advanced Compression:

The Oracle Advanced Compression option contains the following features:

• Data Guard Network Compression
• Data Pump Compression
• Fast RMAN Compression
• OLTP Table Compression
• SecureFile Compression and Deduplication

Compression has clear benefits: reduced storage costs and improved performance. It drives storage utilization up, saves data center space, power, HVAC.

But in order to take advanced compression into use for say already existing tables in every database, one must dedicate resources and time. Lots of both. And buy the licenses of course.

Bottom line: will the efforts spent be really worth the savings? Will there be savings? How about the work and improvements DBA could have done instead?

Are you a DBA looped into the efficiency schema of corporate cost savings? May be it is for good and all is well planned and organized? See, experts are predicting that the average data growth in an organization over the next five years will reach 570%. Five Seventy? Sounds solid, right?

But how can you have triumph over temptation?

I’m in a very perplexing situation: IBM DB2 9.7 or Oracle 11.2?

Unlike Owen Wilson’s perplexing situation between the past and the present in Midnight in Paris, mine is in the present only: two current releases of two database brands: IBM DB2 9.7 and Oracle 11.2.

Which one is easier to administer? DB2 9.7 or Oracle 11.2? Which one saves more time and has higher total cost of ownership? Perplexing situation! But why?

In a 29 page article from October 2010, called Comparing DBA Productivity: An Oracle/DB2 Task Complexity Analysis, Triton Consulting (an IBM Premier Business Partner) “proved” that the advantages of DB2 9.7 over Oracle 11.2 are more than 50%:

In a 51 page article from June 2011, called Database Manageability and Productivity Cost Comparison Study: Oracle Database 11g Release 2 vs IBM DB2 Enterprise 9.7, ORC International “proved” that the advantage of Oracle 11.2 over DB2 9.7 are the following:

– Oracle Database 11g reduces required DBA time by 43 percent compared to IBM DB2
– Oracle is 45% less complex and easier to manage
– By using Oracle instead of DB2, businesses can save US$51,600 per DBA per year

What is going on? One tells us that IBM DB2 is easier to manage than Oracle, another one tells us exactly the opposite.

Obviously, comparing Oracle to DB2 is not as straightforward as comparing ArchiCAD to NotePad?

Let us consider the backup and recovery scenario from both studies. The pro-IBM one claims that the DB2 advantage is 83%. The pro-Oracle one says that the IBM process requires 65% more time and 51% more steps. Those who have spent years of really backing up and restoring databases know very well that there are dozens of factors that influence on the process. Giving figures for average time is simple going out of proportions.

Getting realistic and keeping low profile on the marketing aspect, most of us know that all is up to the people who do the work: the DBAs. There are DBAs that can install/patch the Oracle software and create/upgrade a database in less than 2 hours; there are DBAs who will struggle may be 2 days before asking for help.

What is complex for a certain DBA might be trivial for another. Bottom line is that education, work experience and knowledge are what matter when DBA work is done.

Look at the following screen captured from the video New Approaches for Database Cost Savings by Forrester’s Noel Yuhanna:

The efforts required according to the last column are low. And this might eventually bring cost savings due to increased DBA productivity. Questions is whose efforts are we talking about? It is all up to the people, their skills and knowledge.

In my opinion, when database features are compared, what matters is what is automated and what the DBA is not required to do instead of the time required for the work. And here features like the Automatic Database Diagnostic Monitor, Tuning Advisors, Real Application Testing make the difference! Namely, the self management capabilities of the RDBMS.

Comparing things on a general level (even with detailed figure estimations) might give us somewhat picture about what’s going on but crunching numbers in the database industry might not always bring us the same results. The two contradictory articles mentioned about seem to show us this perplexing situation.