“There is always next year”

Database administration has been heavily influenced by 2 main trends during the last years:

1. Enterprise Manager Grid/Cloud Control
2. The automation features introduced with Oracle 10g and especially with Oracle 11g

The job of the modern DBA is now more like the job of an analytical manager than of a database operator. Several companies hire now DBAs who are not just technicians but data and database professionals with high sense for analytical thinking and logical intuition.

Chris Foot wrote last year an excellent article on The Future of Database Tuning and Database Administration. As Chris says, “The new breed of top tuners will be the administrators who focus on how to use the toolsets and interpret their output.” The times of drilling down into X$ tables and V_$ views are over. DBAs who use most efficiently Grid Control/Cloud Control and turn on properly most (if not all) automated database features are the ones achieving best results in the field.

Let me quote Chris again: “.. reading SQL traces and statistics dumps will be a thing of the past.” The Advisors, the AWR and the ADDM reports reveal much more at a glance than we ever were able to comprehend after reading outputs from complex dictionary queries, statspack or trace files.

I am not underestimating the skills and knowledge of understanding mutexes, private redo strands and Bloom filters but with such a vast product like the Oracle database one should very carefully decide how and on what to concentrate his skills and time.

The DBA profession has several subfields and different trends prevail from time to time but one is the skill that is most wanted and badly needed: performance tuning. It is the most difficult one to learn and master.

Wonder why Grid Control and database automation are changing the DBA profession?

– using Grid/Cloud Control, the packs it comes with, and all the integrated advisors and recommendation reports can reduce the time drastically. Understand it in a way that the time a DBA can spend on an issue might vary from minutes to hours (if not days) depending on if Grid Control is used or not. I think EM is still an underestimated tool mostly because of the fact that companies cannot make/decide on the proper investments. Most managers cannot get the fact that those Grid Control packs can produce significant ROI in their database department. Often, they just can’t get it. Even if you give it to them in a box names “It”.

– automation within the database is where Oracle is going to. Best results with databases can be achieved by benefiting from all automated features offered by Oracle. Automating the Oracle Database is something overlooked and underestimated. Mostly by the old generation of DBAs who are often suspicious about everything. Let me quote Shunryu Suzuki/Steve Jobs: “In the beginner’s mind there are many possibilities, in the expert’s mind there are few.”

However, with all those new features, there comes a flock of bugs and looks like their fixes cannot catch up with the new versions and patches. Keeping all these new features in sync with the database kernel is not at all so easy. As Chris says, “our role will be how to utilize the ever-increasing number of features provided by the database to solve business problems” and I would add how to solve the business problems caused by the bugs these new features introduce. Catch 22, right?

According to Oracle 2020: A Glimpse Into the Future of Database Management, during this year 2011, “Oracle’s Larry Elision finances the Worldwide Internet Identification Database, requiring non-anonymous access and reducing cybercrime. Ellison receives the Nobel Peace Prize for his humanitarian efforts.” I am almost certain that no one takes the stuff written there seriously but there is always next year 🙂

On 4 undocumented DataPump parameters

DataPump is probably the best free Oracle utility/tool ever developed. With the exception of SQL*Plus of course which is somehow naturally excluded.

It is like SELECT being the most common command written/typed by DBAs and Developers. Guess which is the next one after SELECT? You might not want to believe it but it seems to be EXIT 🙂

Now, 11g came with several new parameters: COMPRESSION, ENCRYPTION, TRANSPORTABLE, NETWORK_LINK, PARTITION_OPTIONS, DATA_OPTIONS, REUSE_DUMPFILES, REMAP_TABLE, etc. which are probably well-known but here are few undocumented ones you can have as an ace up your sleeve.

1. METRICS

You can use the parameter METRICS=Y to include additional logging information about the number of objects and the time it took to process them in the log file. With METRICS, additional information can be obtained about the number of objects that were processed and the time it took for processing them.

Note that the METRICS parameter does not create any additional trace files. Instead, the extra details are logged in the logfile of the Export Data Pump or Import Data Pump job.

Here is an example:

expdp \'/ as sysdba\' DUMPFILE=data_pump_dir:sysman_%U.dat SCHEMAS=sysman_apm JOB_NAME=sysman_pump METRICS=Y
...
[oracle@zurich Desktop]$ expdp \'/ as sysdba\' DUMPFILE=data_pump_dir:sysman_%U.dat SCHEMAS=sysman_apm JOB_NAME=sysman_pump METRICS=Y

Export: Release 11.2.0.3.0 - Production on Tue Dec 20 10:46:08 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYSMAN_PUMP":  "/******** AS SYSDBA" DUMPFILE=data_pump_dir:sysman_%U.dat SCHEMAS=sysman_apm JOB_NAME=sysman_pump METRICS=Y 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
     Completed 1 USER objects in 0 seconds
...
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 1 DEFAULT_ROLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
     Completed 1 TABLESPACE_QUOTA objects in 8 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 27 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
...
. . exported "SYSMAN_APM"."PATCH_RUN"                        0 KB       0 rows
Master table "SYS"."SYSMAN_PUMP" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYSMAN_PUMP is:
  /u01/app/oracle/admin/CLOUD/dpdump/sysman_01.dat
Job "SYS"."SYSMAN_PUMP" successfully completed at 10:49:19

2. ACCESS_METHOD

In some situations, the undocumented parameter ACCESS_METHOD can be used to enforce a specific method to unload or load the data.

If the parameter is not specified, then Data Pump will automatically choose the best method to load or unload the data.

The parameter can only be specified when the Data Pump job is initially started i.e., the parameter cannot be specified when the job is restarted.

If the parameter is specified, the method of loading or unloading the data is enforced on all tables that need to be loaded or unloaded with the job.

Enforcing a specific method may result in a slower performance of the overall Data Pump job.

Here is an example:

expdp \'/ as sysdba\'  ... ACCESS_METHOD=DIRECT_PATH  
expdp \'/ as sysdba\'  ... ACCESS_METHOD=EXTERNAL_TABLE 

impdp \'/ as sysdba\'  ... ACCESS_METHOD=DIRECT_PATH  
impdp \'/ as sysdba\'  ... ACCESS_METHOD=EXTERNAL_TABLE 

3. TRACE

Tracing can be enabled by specifying an 7 digit hexadecimal mask in the TRACE parameter of expdp or impdp. The first three digits enable tracing for a specific DataPump component, while the last four digits are usually: 0300. Any leading zero’s can be omitted, and the value specified for the TRACE parameter is not case sensitive. TRACE does not add anything to the output of DataPump, it creates additional trace files.

Here is a summary of the Data Pump trace levels:

  10300 SHDW: To trace the Shadow process 
  20300 KUPV: To trace Fixed table
  40300 'div' To trace Process services
  80300 KUPM: To trace Master Control Process 
 100300 KUPF: To trace File Manager
 200300 KUPC: To trace Queue services
 400300 KUPW: To trace Worker process(es)         
 800300 KUPD: To trace Data Package
1000300 META: To trace Metadata Package
1FF0300 'all' To trace all components, full tracing

As you see, in order to trace all Data Pump components, level 1FF0300 can be specified, here is an example:

impdp \'/ as sysdba\' NETWORK_LINK=edw_p SCHEMAS=CLOUD PARALLEL=8 JOB_NAME=net_pump EXCLUDE=statistics TRACE=1FF0300 
...
SHDW: *** GET_STATUS_VERSION call ***
SHDW:     Version = 9999
KUPP:10:58:22.050: Input trace/debug flags: 01FF0300 = 33489664
KUPP:10:58:22.050: Current trace/debug flags: 01FF0300 = 33489664
SHDW:10:58:22.050: Current user = SYS
SHDW:10:58:22.050: Current schema = SYS
SHDW:10:58:22.050: Current language = AMERICAN_AMERICA.AL32UTF8
SHDW:10:58:22.052: Current session address = 0000000077F7AFA0
SHDW:10:58:22.052: *** OPEN call ***
SHDW:10:58:22.052:     operation       = IMPORT
SHDW:10:58:22.052:     job_mode        = schema
SHDW:10:58:22.052:     remote_link     = edw_p
SHDW:10:58:22.052:     version         = 
SHDW:10:58:22.052:     compression     = 2
KUPV:10:58:22.052: Open request for job: SYS.NET_PUMP
KUPV:10:58:22.058: Master Table create statement: CREATE TABLE "SYS"."SYSMAN_PUMP" (process_order NUMBER, duplicate NUMBER, dump_fileid NUMBER, dump_position NUMBER, dump_length NUMBER, dump_orig_length NUMBER 
...

The 3 types of trace files can be found in $ADR_HOME/trace:
– Data Pump Master Control Processes files have format: SID_dm_process_id.trc
– Data Pump Worker Process trace files have format: SID_dw_process_id.trc
– Data Pump Shadow Process trace files have format: SID_ora_process_id.trc

4. KEEP_MASTER

If the undocumented DataPump parameter KEEP_MASTER=Y is used, then the Master Table is not deleted after the job completes. The drop of the master table does not lead to any data dictionary corruption and if you keep the master table after the job completes, then a drop of the master table afterwards will not cause any corruption either.

You might use this parameter if you create transportable tablespaces where the source and the destination are both ASM based.

Here is an example:

expdp \'/ as sysdba\' directory=DUMPDIR DUMPFILE=skat_full.dmp LOGFILE=skat_full.log FULL=Y KEEP_MASTER=Y

One last note: More than 50 bugs related to DataPump have been fixed in Oracle Database 11g Release 2 (11.2.0.3). Check document 1348303.1: List of Bug Fixes by Problem Type available on the My Oracle Support Web site.

DBA: Don’t Bother Asking

To all of you from all of me what DBA can mean and be!

DBA: FlyDBA.com

DBA: Delaware Bankers Association

DBA: Double Barrel Ale

DBA: Design Business Association

DBA: Disc Brakes Australia

DBA: Doing Business as Brooklyn

DBA: Differently-Abled Business Association

DBA: Dual Band Amplifier

DBA: Detroit Batting Academy

DBA: Dallas Business Association

DBA: Double Barrel Air Pump

DBA: Deter’s Beer Asylum

DBA: Doctor of Business Administration

DBA: Defense Base Act

DBA: Digital Broadcasting Australia

DBA: Dollar Bill Acceptors

DBA: Deaf Basketball Association

DBA: Dumb But Adorable

Beautiful mind: DBA Work Motivation

Theodore Roosevelt: Far and away the best prize that life has to offer is the chance to work hard at work worth doing.

Margaret Thatcher: I do not know anyone who has got to the top without hard work. That is the recipe. It will not always get you to the top, but should get you pretty near.

Woody Allen: I don’t want to achieve immortality through my work. I want to achieve it through not dying.

During Oracle OpenWorld 2011, Paul Vallee (Founder and Executive Chairman at The Pythian Group) and Alex Gorbachev (Chief Technology Officer at The Pythian Group) organized a DBA Manager meeting where (probably) the most interesting topic discussed was what motivates DBAs.

The internet is full of articles, debates and research done on work motivation but this one is worth watching:

Interesting enough, same holds for DBAs: independence, mastery and purpose are the key motivators. All combined, they lead to self satisfaction from the DBA profession and although money might always come in between, it is by far a very short term motivation factor. Although, note that for about half of the population money is the primary motivator to perform the job properly.

1. Independence

Autonomy for the DBA is crucial in IT. Those DBA managers who micromanage their DBAs are doomed to failure. A DBA Manager should trust his/her DBAs as they should likewise trust him/her. And this would be the ideal situation.

2. Mastery

Wonder why so many people have blogs, website and answer questions in database forums? And they do it for free. Even writing a book is never for financial benefits. It is the hidden will inside us to develop skills and share them with others.

3. Purpose

Every DBA has a purpose in his career: one purpose or more than one purpose, a small one or a big one. And the purpose might change the direction of his/her DBA’s career. Call it a career goal, an internal motto. And of course, I also have one.

Another interesting concept in the DBA profession is that of the over-motivated Cowboy DBA: a person who with very good intentions screws up the production system quite quickly:

– use a new init.ora parameter (or an underscore one) in production without checking all bugs related to that parameter or test it in advance
– restart a production database in order to fix something and what that thing was remains usually a mistery
– kill all or some user sessions in production to “release” locks:

select 'alter system kill session '''||sid||','||serial#||''';' 
from v$session where username = 'PROD_USER';

– run “rm -rf *” without additionally typing “pwd”

Let me quote Don Burleson: “As every DBA knows, all developers are secretly studying database administration”. His addition is “and actively plotting to take their jobs” while mine would be “and are potential cowboy DBAs”. That is why DBAs do not usually give developers access to Production?

The Newest init.ora Parameter in the Oracle Database

Altogether there are 347 init.ora parameters in Oracle Database 11.2.0.3. Looking into all documented and undocumented parameters in 11.2.0.3, we witness a high number: 2752.

11.2.0.3 does not sparkle with any fancy features and no one is expecting it to but there is one new init.ora parameter added by Oracle: awr_snapshot_time_offset.

Why is it needed? The answer is very clear in case you have a server with several databases running on it. Consolidation has been fashionable for the past decade and many scattered corporative databases have perched on the same computer.

You might have observed those spikes on the top of every hour when all the AWR snapshots are taken. How do you avoid this, i.e., say you have 12 databases on the server and you want snapshots taken on hour bases but within 5 minutes difference for every database?

Quite simply with awr_snapshot_time_offset by setting it for n*300 (seconds) for every n:th database (n = 1..12).

The documentation of awr_snapshot_time_offset gives additional details for the parameter.

And finally, here is how one can search for the values of certain parameters, for example Bloom filter related:

select a.ksppinm name, b.ksppstvl value 
from x$ksppi a, x$ksppcv b 
where a.indx = b.indx 
and a.ksppinm like '%bloom%';

New DBA Features in Oracle Enterprise Manager Cloud Control 12c

During Oracle OpenWorld 2011, Oracle introduced Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1). I have already done the first installation on OEL Linux 6 and I must say I am impressed with the product.

Oracle Enterprise Manager Cloud Control 12c came with new features covered in 12 areas:

– Framework Enhancements
– Database Management Features
– Fusion Middleware Management Features
– Oracle Fusion Applications Management
– Application Performance Management Features
– Cloud Management Features
– Incident Management Features
– My Oracle Support Integration
– Security Features
– Lifecycle Management and Provisioning Features
– Extensibility Support
– Coherence Management Features

In this post, I will present the new 20 DBA features of OEM CC 12c as documented by Oracle (the screenshots in between are from my own installation):

1. Database Creation Via Enterprise Manager Cloud Control

A wizard allows you to create an Oracle Database from within Enterprise Manager Cloud Control. You can create different configurations, including Single Instance and Real Application Clusters (RAC) databases, as well as file system and Automatic Storage Management (ASM) storage options.

2. Database Upgrade Via Enterprise Manager Cloud Control

You can now upgrade Single Instance and Real Application Clusters (RAC) Oracle databases through Cloud Control. This feature makes it possible to manage upgrades from a single console. You no longer have to access each individual database machine to perform upgrades.

3. Database Cloning Enhancements

Cloning procedures have been enhanced to capture configuration properties as well as the software payload. This is particularly useful when migrating databases from development to test to production or the reverse. A new EMCLI verb (clone_database) allows for database cloning using the same job type as the existing Clone Database feature of Cloud Control.

4. Oracle Exadata Server Management Enhancements

Oracle Exadata management capabilities now provide support for full target lifecycle management:

– Automatically discover Exadata targets
– Automatically create an Enterprise Manager System for end-to-end monitoring
– Provide extensive administration capabilities for databases, Exadata cells and Infiniband switches
– Simplify performance diagnostics with the help of in-depth performance charts covering all Exadata components

5. Manage Automatic Storage Management (ASM) Clusters as a Target

You can now manage clustered ASM resources as a single target, instead of each individual ASM instance having to be managed separately. Incident management and metric monitoring can be managed for the entire cluster.

6. Database Configuration Compliance Standards Support

Oracle database configuration data can now be managed within the new configuration and compliance standards frameworks.

7. Emergency Performance

This feature allows a DBA to diagnose and troubleshoot a hung or extremely slow database using the memory access mode. This mode is designed to bypass the SQL information retrieval layer and read performance statistics directly from the SGA of the target.

8. Database Backup and Restore Enhancements

You can now use Enterprise Manager Cloud Control to centrally maintain the settings for Oracle database and file system backups. This enhancement enables you to apply uniform settings to multiple databases and file systems when backing up multiple objects—all in one operation. Users can perform a backup on multiple databases concurrently or sequentially in one deployment procedure. An Oracle Home OSB tape backup can be restored either to the original or to a different location, and the restored Oracle Home can be reconfigured to function in the newly restored location.

9. Database System Discovery Enhancements

As the database system is now built upon the new target and association model, you can use it to monitor and manage a database’s storage, connectivity, and high availability. This also enables you to easily monitor and manage the applications that are dependent on the database. The database discovery functionality is enhanced to work with the new discovery framework and to provide a reliable workflow to create a database system.

10. Change Plans Added to Change Management Pack

As part of the Oracle Change Management Pack, the new Change Plans function allows application developers and database administrators to encapsulate schema changes needed to be made to a database into a “change plan,” which can be used to document, capture, and apply schema changes. Change Plans are also integrated with developer and DBA tasks into SQL*Developer and Oracle Enterprise Manager task automation. This integration reduces the manual processes between the various stakeholders involved in the process of promoting planned changes across enterprise databases while ensuring the integrity of the process.

11. Compare Period Advisor

This feature compares the performance of a database over two different time ranges. It analyzes changes in performance, workload, configuration, and hardware to highlight changes between the two time periods. The Compare Period Advisor gives the DBA the ability to compare two arbitrary periods of time.

12. Compare Functionality

The Compare functionality has been enhanced with new capabilities such as template support, system level comparison, and change notification. Users can now selectively include or ignore types of differences. Output of a comparison can easily be saved and exported, both in printable (for example, plain text) and data-centric (for example, CSV) formats. Users can select comparison start and end dates and view a history of changes for composite targets.

13. Active Reports

A new Active Reports function allows users to save performance data into an HTML file. Once saved, the report can be used for offline analysis or sent to other users, including Oracle Support. Active Reports enhances the visual representation of performance data and facilitates the convenient exchange of complex data.

14. Real Application Testing and Data Masking Integration

Real Application Testing and Data Masking integration provides users with the ability to perform secure testing in situations where data in production needs to be shared by nonproduction users due to organization or business requirements. Typically testing is done in a nonproduction environment or by a different group or organization. This integration addresses a common requirement that the data used for testing be shared in a manner that adheres to data privacy and compliance regulations.

15. Application Templates for Data Masking and Data Subsetting

This feature provides predefined data masking and data subsetting templates for applications. It allows users to automatically create test systems based on best practices recommendations.

16. Data Subsetting

Data subsetting provides the ability to create a smaller sized copy of the original production data that can be given to developers for testing. While it is a data subset, the referential relationships are preserved so that the data set is complete. This allows enterprises to lower storage costs while making production data available to developers for testing, without having to incur the storage footprint of the entire production database.

17. Application Data Model Support for Data Masking

The application data model (ADM) now stores the sensitive data elements used to generate mask definitions dynamically. Instead of having to manually discover sensitive data, the application data model identifies and stores the sensitive data elements.

18. Reversible Data Masking

Using encryption and decryption algorithms, reversible masking allows encryption of a user’s data deterministically into a format chosen by the user as a regular expression. Unmasking reverses the process to revert back to the original data. This feature is useful in environments where sensitive data needs to be masked and sent to a third party for processing. Coupling integrated masking with the application data model (ADM), an application’s data model is now available for certain packaged applications and can serve as a knowledge base containing sensitive column and data relationships.

19. Performance Diagnostics Enhancements

With the interactive user interface in the Active Session History (ASH) Viewer, users now can visualize the many performance dimensions that were not available to them in earlier releases. The Enhanced Enterprise Manager Performance and Top Activity pages allow users to visualize the multidimensional data in ASH. The ASH viewer enhances the performance troubleshooting capabilities of a DBA by providing the facility to detect skews in workload. Emergency ADDM adds performance diagnostics for databases suffering from severe performance problems.

20. Streams and XStreams Support

Streams and XStreams configurations can now be managed and monitored using Cloud Control. In addition to improvements in configuration and performance monitoring screens, logical change record (LCR) tracking is available for high-level diagnosis of replication issues. Cloud Control also simplifies the management and monitoring of replicated environments.

Oracle Enterprise Manager Cloud Control 12c

It is now official: Oracle announced on October 3rd the new version of Enterprise Manager Cloud Control 12c Release 1 (12.1.0.1).

This is the first solution in IT to combine management of the full Oracle stack with complete enterprise cloud lifecycle management.

You may download it only for Linux x86-64 (64-bit).

The Enterprise Manager Cloud Control Documentation gives you the following:

Basic Installation Guide
Advanced Installation and Configuration Guide
Upgrade Guide
Administrator’s Guide
Cloud Administration Guide
Licensing Information

There are 4 mandatory plug-ins that are installed by default with Enterprise Manager Cloud Control:

Oracle Database plug-in
Oracle Fusion Middleware plug-in
Oracle My Oracle Support plug-in
Oracle Exadata plug-in

Enterprise Manager offers the following management packs for Oracle Database:

Diagnostics Pack for Oracle Database
Tuning Pack for Oracle Database
Database Lifecycle Management Pack for Oracle Database
Configuration Management Pack for Oracle Database
Provisioning and Patch Automation Pack for Oracle Database
Change Management Pack for Oracle Database
Oracle Data Masking Pack for Oracle and non-Oracle Databases
Oracle Test Data Management Pack for Oracle and non-Oracle Databases

Top 3 new features for DBAs:

1. Real-time ADDM. You can connect to the database and investigate what is going on when you cannot connect to the database because it is hanging on it is extremely slow. This is for real, connection to the database is in diagnostic mode, there is direct connections to the SGA.

2. Active Session History (ASH) Analytics. It is the adavnced version of Top Activity.

This is a snapshot from load map view mode:

3. Compare period ADDM. You can identify why during a certain period activity was slow when no changes to the SQL statements were made. Here is a snapshot from ADDM compare:

Great product from Oracle and this is not any longer just a DBA tool.

From yesterday’s Total Cloud Control session: Richard Sarwal and Sushil Kumar:

More to follow on DBA new features in “New DBA Features in Oracle Enterprise Manager Cloud Control 12c”.

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“.