4th. and final Day at the Oracle Open World.

This was the last day and you kind of wish for it, the information you get here is very interesting but after receiving such amount of information day after day at the end of the week you are totally burn out. One thing that I noticed and I recommend is to bring a digital camera, sit close to the screen and take pictures of the slides. It is impossible to take notes and pay attention to what the presenter is saying, most of them talk too fast and the slides are shown just for few seconds, you don’t even have time to read them much less take notes out of them. It is like a race they want to cover as much as they can in 90 min.

My first session was “Fault diagnostic best practices, what every DBA must know about Oracle11g by Prabhaker Gongloor”. Oracle is changing the architecture where your log and trace files are stored, there is an new incident, trace and metadata directory. Oracle introduced a new parameter diagnostic_dest that when set it places your log file traces as well as other debugging files on that directory. If you don't set this parameter it would be set to your ORACLE_BASE, if ORACLE_BASE is not set then it would be set to your ORACLE_HOME. The new view v$diag_info contain all the locations for the different directories.

There is a new enterprise manager "support workbench" to support all this new architecture. This includes Incident Packaging Services (IPS). IPS uses the new directory structure to automate packaging of diagnostic data. It solves the problem of what to send to support. The new EM also offers 11g Health monitoring which helps you find problems before they impact the service.

Background_dump_dest is deprecated as well as user_dump_dest; however core_dump_dest still exist. Oracle new alert.log is in XML format however Oracle is still providing the alert.log in text format in case you have a program that uses the text format.

Also in 11g the RDBMS automatically purges your trace files, reducing the possibility for traces to fill up your disk. The Automatic diagnostic repository stores diagnostic data in a hierarchy directory.

My second session was “What is new in Oracle Transparent Data Encryption by Daniel Wong”. Oracle did a strong effort to improve this tool but the main characteristic is the Tablespace encryption, now you don't have to guess what data to encrypt as you can encrypt a complete tablespace. There is not issues in having encrypted partition tables or an encrypted parent table with references on a unencrypted child table, etc.

My third session was “Best Practices for Upgrading to Oracle Database 11g by Ravi Pattabhi” Ravi basically went through the different ways to execute the upgrade.

- Manual using Database upgrade assistant.

- Automatic using the Database upgrade assistant.

- Data pump export and import.

Oracle introduced since 10g (I believe it was 10g) a set of scripts that you run before the upgrade (pre-upgrade info tool and pre-upgrade analysis tool) and it tells you all the possible problems you may face if you upgrade. There is also a post-upgrade script that you can run to verify that everything was correct. If you are using RAC or ASM, you need to address the clusterware and ASM upgrade before you upgrade the database.

There is a new dba_registry view in 11g that helps you with the upgrade process , only real errors are spooled during the upgrade. There is even in Metalink a white paper that helps you to minimize downtime during the upgrade.

At then end of the presentation Ravi went through the print screen slides of the upgrade process using DBUA (DB Upgrade Assistant). If you have upgraded from 10gR1 to 10gR2 it is basically the same screens you are familiar with. Ravi suggested to test your upgrade (Any intelligent DBA does this anyway) use RMAN to clone you production database and test the upgrade in the clone database. Database control and grid control offers a variety of tools that help you measure the effect of upgrading to 11g for your application. If you are going to do the manual upgrade automate as much as you can so you minimize human errors.

My last session was “Oracle Database 10g tuning arsenal by John Kanagaraj" John basically went through the efficient use of AWR (Advance workdload repository) and ADDM (Advance Diagnostic Manager) which are features we currently use therefore I will not elaborate on it.

In conclusion it is my personal opinion that in 11g Oracle took an step back to improve and fix all the nice features they launched in 10g. There were comments from presenters saying that they believe 11g is the most stable beta Oracle has ever produced and I admire and congratulate Oracle for just taking the time to mature their product.

I hope you enjoyed this overview at the Oracle Openworld 2007

Rafael Orta

3rd. day at the Oracle Open World

Last night was the customer appreciation event with Billy Joel, Lenny Kravitz and Stevie (Fleetwood mac). I got to my hotel pretty late and was exhausted, so I am trying to catch up today.

My first session of the day was "Oracle 11g simplifying management of Oracle database files by Aiman Al-Khammash". This session was mostly about ASM and how you can leverage it to help you administer your database files. ASM (Automatic Storage Manager) provides better performance than OCFS (Oracle Cluster File system).

11g allows rolling upgrades patches for Clusterware and ASM, in 11g you can have variable size extends and the extend size grows automatically with the file. 11g also increases maximum ASM size which is platform dependent. In 11g the allocation unit (AU) is not longer 1MB. as in 10g, you can now set it up to 1, 2, 4,8,16,32,64 MB. Same as in 10g the striping size is one allocation unit (AU) an file stripe is 128KB.

There are new ASM disk group attributes such as

AU_SIZE , DISK_REPAIR_TIME (Length of time before removing a disk once offline), COMPATIBLE_ASM and COMPATIBLE_RDBMS.

There is also a new view: V$ASM_ATTRIBUTE

There are new command line parameters (asmcmd):

lsdsk = List asm disks.
cp = Copy files between asm and non asm storage
remap = Re-maps unreadable blocks in normal high redundancy
md_backup / md_restore = Backup and restore ASM disk group environment.

In addition you can use $asmcmd help to find commands

My second session was "Backup and recovery best practices for very large databases by Tim Chien". Tim explained that every day disk is becoming a more popular backup media since you get performance at a low cost. Tim spoke about VTL (Virtual Tape Libraries) and said they provide you the best of both worlds (tape / disk). He advised to allocate disk backup for the most critical databases, and he also suggested to use locally attached tape drives. Tim suggest the to use the following tools:

- RMAN (Incremental backups)
- Data Pump
- Oracle Secure Backup
- Flash back technology (This is redo base).
- Data Guard (Use it as a disk backup or take the backup out of it)

Use third party storage space solutions.

- Snapshots
- Split mirror backup
- CDP appliances (provides continue snapshot copy-on-write or
allocate-on-write)

Plan your data layer and exploit partitioning and read only tablespaces.

Develop your backup procedure using the following options

Option 1: Use level 0 (FULL) and fast incremental backups.

Option 2: Use level 0, fast incremental backups and incremental updates backup.

Option 3: Data Guard + Full incremental backups.

Option 4: Maintain ETL loads , restore full backups and run needed ETL.

Consider the use of nologging (used widely for Data warehouses to speedup performance.

Divide full backup workloads across multiple days.

New in 11g he said you can use multi section backups (intra file parallel backup)

And lastly develop recovery strategies and use the data recovery advisory.


Then I assisted to "Larry Ellison key notes" Larry was introduced by Billy Joel and then he announced once again Oracle Virtualization and the software was open for download after the announcement. Larry basically spoke about what they heard the customers wants and what Oracle is doing to cover that demand. He also made reference to Unbreakable and the fact that despite it has been only one year since they launched it and they have not sales force for it several big companies have moved to it.

My fourth meeting of the day was "Oracle Active data guard and how to utilize your standby databases for production workload by Grant McAlister" . Grant started by explaining the traditional physical standby architecture

He also spoke about "Active data guard" which is a new Oracle option for which you have to pay a license fee; basically it allows you to continue ship and apply of redo on the standby while the database is in read only mode, with this you get better utilization and performance.

Grant said that you can scale out with active data guard as you can have your reports been feed out of the read only standby.

Active Data Guard allows real time query and RMAN block change tracking on a standby database. When the DB is open read only the consistency is guarantee since it is maintained through query scn.

Whole the database is open read only you can select and alter session. He also spoke about the best practices such as what are the apps that are candidate for active data guard. Grant explained how he was able to scale out just separating the queries from the writes.

He mentioned 3 methods to redirect writes:

1. - DML application level change.
2. - Use of a Database Link
3. - DML via database link.

He advises to use synonyms to hide the database links, keep in mind that from a read only database you can't invoke a remote store procedure.

My last session was "Oracle Data Guard tips and tricks by Larry Carpenter".
Larry started encouraging people to use ASM, he said it provides ease of management and advises the following:

- Use ASM
- Use Flashback recovery area
- Use RMAN to create your standby database.

Convert your standby first to ASM in that way moving to ASM does not mean downtime. He explained how having a flash recovery are simplifies management. The flash recovery area is required for flash back database. Larry explained how to turn on the flash recovery option. He explained that running jobs in the logical standby will not work until the standby gets to be the primary. In 11g you can use the DBMS_SCHEDULER on the active , the standby or both.

Well that is all for today folks.

2nd Day at the Oracle Openworld

I am still trying to adjust to the jet lag, my internal clock is still in NJ or Bahamas where I was on vacation before getting here, never mind.

My first conference today was "Oracle Database 11g Business Intelligence and Data Warehousing by Ray Roccaforte". Ray spoke about the exorbitant grow of data volume, he mentioned Yahoo has a DW database of 250TB. he said that due to the increase in the amount of data you need more intelligent tools to interpret that data and that is where the business intelligence tools come to help. The oracle BI solution differentiates from other solutions in that it performs analytics inside the database therefore is faster.

Oracle provides:

Scalable Data management

ASM - Partitioning - Parallel Operations - Aggregation management - RAC

Analytic Platform

Multi dimensions Calculations - Time Series - Forecasting - Statistics - Data mining

ETL & Data Quality

Data Integration - Data Modeling - Meta data Management - Data Profiling - SOA


Oracle is launching a new initiative partnered with some of the main hardware providers like Dell, IBM and Sun and they are shipping along with the hardware the DW solution perfectly tunned and ready to use out of the box.

In 11g there are performance enhancements for Oracle partitioning such as interval partitioning that I mentioned yesterday, there are new types of composite partitioning (range - range , list - range , list - hash) , you can also partition on virtual columns (on expression values) , you also have reference partitioning (equi-partition a FK table with a PK table). The advantage of partition interval is that it creates the partition on the fly when the first row is inserted, for example:

create table sh.sales partition by range(time_id) interval (numtomyinterval (1,'month'),

Also in 11g there is a new feature called "SQL Query result cache" , I think I mentioned this in my previous blog from yesterday but basically you cache the results of a query and it can be used by subsequent queries that can make use of it. For example you may have an expensive group by part of a query, that group by can be used by subsequent queries that may need of it.

Also in 11g a single cube provides equivalence to thousand of materialized views, The 11g SQL query optimizer treats OLAP cubes as materialized views. what is not clear to me is if I need to buy OLAP or they are allowing me to create cubes as part of EE base.

My second conference was "Oracle 11g Real application testing: Avoid performance regressions with SQL performance analyzer by Benoit Dageville" Benoit mentioned that the new real application testing (RAT) has 2 components and is a package of Grid control that you can access for an extra fee.

RAT - SQL Performance Analyzer
- Database replay.

You use the SQL performance analyzer to do unit testing it, helps you to predict problems before it happen.

You use the database replay feature for comprehensive testing and replay real workload.

The SQL performance analyzer offers automatic data capture with almost no overhead, it offers you production sql context and complete sql workload as well as automatic analysis in minutes. You can later use the automatic regression tuning to improve your sql.

The cycle of RAT is as follow:

You capture your sql in production -> transfer the sql to test -> execute the sql pre-change -> set changes -> execute replay -> compare performance -> do the production changes.

Creating sql plan base lines helps to revert to previously known stable plans. SQL Tunning adv visor helps to explore better execution plans. You can leverage SQL performance analyzer 11g (SPA) to determinate how changes will affect you when you move to 11g, even if your database is a 10g database and you don't have a 11g database yet (optimizer upgrade simulator).

My third session was "Oracle 11g Data pump: Data compression, encryption and more by Carol Palmer" Carol started by giving an overview of data pump, and then focused in the following new features on 11g.

- Data compression
- Dump file encryption
- Partition base transportable tablespace

she mentioned that the original import (imp) will be around still in future versions but the export as we know it is not longer on 11g. Oracle is pushing everybody to move to data pump. Data pump import (impdp) is 15-40 times faster than the regular import and expdp (Data pump export) is twice faster than the regular export.

The new data compression feature compress data and meta data or both depending of what you want, it is an inline operation meaning that while you import of export the data gets compressed and decompressed contrary to the traditional two step extraction and compression. The speed of compression is as fast as traditional utilities like gzip and the compression ration is similar as well. Compression adds a 10% overhead versus non compressed import and export.

Data pump encryption offers password base encryption, you can use the Oracle wallet or you can use both. it is also an inline operation and works in conjunction with Oracle transparent data encryption, the default encryption algorithm is AES125.

Carol briefly spoke about partition base transportable tablespace, no much details on it. She also spoke about data transform (remap_data) , you basically create a function to obscure sensitive data and the translation is done once you import in your test system, in that way you can protect columns such as social security numbers, salaries etc from the developers or anyone with access to development. You can remap several columns for several tables at a time, the disadvantage I see is that you need to create a function for every column you want to remap.

Carol also spoke about "secure files" they basically are the new lobs but with better performance , you can compress them, you can encrypt them and they are supported by data pump.

My last session and my favorite for the day was "The top 10 new features of Oracle 11g by Thomas Kyte" . Tom gave a preamble saying that it was very easy to see the data out of database files and that you could find in sourceforge.com plenty of data unloaders that can read Oracle database files and that is why you need to encrypt the data.

1. - Encrypted tablespace: Column encryption as been there since 10g R2, however you could not use range scan and you had primary / foreign key issues when one or the other was not encrypted. Also sometimes you do not know what data to encrypt. Tablespace encryption solves all those limitations.

2. - Oracle 11g does more cache: You have client side cache, server result cache (JIT - MV) and PL/SQL functions result cache. There is a result_cache hint that you can use to tell Oracle to cache or not the results. You can also ask for the result of a function to be cache using the "create or replace function cache" statement.

3. - Standby just got better: Logical standby has been always limited in type support but always open for business, while the physical standby is easier but considered not helpful for day to day operation. Now the logical standby has XML type support and the physical standby can be used for destructive testing, in other words you can open it do the operations you need then bring it back to the original state using flash back and place it again in standby mode. Another improvement is that even that you are querying the database the redo is continuously been applied. This is great because you can offload your reports to the standby database without impacting the synchronization with your primary.

4. - Real application testing: I mentioned this before, Tom did not mentioned much about this other than it has 2 components: capture workload and replay DB workload.

5. - Data pump compression and encryption: Tom mentioned the new partition options (none - departition - merge) and you can also reuse dump files (reuse_dumpfiles).

6. - Virtual columns: They are column expressions involving other columns in the table or constants.

7. - Partitioning just got better: Now Oracle covers all the set of composite partitions and you can have virtual column partition, you can partition by reference and you can use interval partitioning which was mentioned before.

8. - Long waited pivot columns: now Oracle implemented a new clause called pivot to display pivot columns.

9. - Flash back data archive: For years customers have wanted to go back in time more than just few hours or a day, well because of the architecture of flash back that was not possible (you can't keep years of undo inline) , now with the new flash back data archive you can do total data recall or table by table.

10. - Finer grained dependency tracking: In 11g you have less objects invalidations even if you specifications to a procedure or add / modify or drop a column, or if you modify a synonym pointer.

11. - OLTP table compression: Oracle advance compression now has a 3x ratio.

Personal Comment: it seems that in 11g Oracle rather than getting crazy adding features that people may not use, they took an step back and matured and improved what they already have which I think is great.

More tomorrow...

Day 1 Oracle Open World

Hello Everybody

this is my report of the first day at the Oracle Open World, my trip here was not easy, I spent Sunday since 2PM flying here and arrived Monday at 9:50 AM, during the trip my luggage was lost, but I am not going to bore you with the details.

I did not arrived on time for the opening keynote of Charles Philip but friends told me that it was good. There was a Keynote on Sunday which I also missed, but this time was Larry Ellison the speaker, he spoke about Oracle and its beginnings and today everybody has made comments about how good it was.

My first meeting was "The fastest and most cost effective backup for Oracle Database" by Sean McKeown. Sean is the manager for the system administrators in the Oracle Global IT organization, he was explaining how Oracle saved 1.5 million dollars using Oracle Secure Backup (OSB), Oracle Secure Backup is a media library manager from Oracle, In the future you won't need for third party media library managers to backup Oracle databases to tape. The product only works for Oracle databases and with certain hardware, they couldn't say if in the future it will work for non Oracle databases, however my guess is that it wont.

OSB supports virtual library tape, if you are not familiar with the term it is just making the backup to a disk that looks like a tape to your server, later on the information is backup to tape from that special disk and the space taken by the backup is released.

One curious thing is that is the System Administrators and not the DBAs the ones that take care of the backup and recovery of information at Oracle, the DBAs just need to be sure that RMAN is setup properly. Oracle is using OSB for their email, they serve more than 70,000 employees and the amount of information they store is pretty big. He said that Oracle secure backup was 20 to 50% faster than the Veritas solution and a lot cheaper too, it is just 3K per tape drive. OSB does not backup the undo tablespace.

My second meeting was "Oracle 11g next generation of high availability" by Sushil Kumer. In this session he talked about flash back query, flash back version which I was not familiar with (you basically can tell the database to show you the transactions that happened between 2 date time you choose), he spoke about flash back database, class="blsp-spelling-error" id="SPELLING_ERROR_12">ASM (Automatic Storage Manager), flash back table (none of this are new) , what is new is flash back transaction, he said that now Oracle is intelligent enough to know all the dependencies of a transaction and flash it back eliminating the need for you to guess what a particular process did and what tables touched. Flashback is free with

Flashback has become very popular as a test tool since you can bring back the whole database to the original state after you test any process, and it is a lot simpler and faster than restoring and recovering your database.

In a regular DR scenario the correction time = error time + f(db size), with
flash back the correction time = error time

He said a lot of Oracle customers has been asking to flash back longer periods but until now the product was not designed to support that, in 11g Oracle introduced flash back data archive which allows you to go back years using the same flash back architecture.

My third meeting was "The best Oracle 11g new features" by Rich Niemic. This was probably the best meeting I had so far, however he did not go technical and spend 15 min. making jokes, no that the jokes were not good, but when you have the opportunity to get to Rich in front of you, you want to get the most out his time.

He said that Oracle in 11g did not added a lot of new features but they focused in improving the current features and it was his opinion that 11g was the beta version of Oracle with the best quality ever. He said Oracle did improvements automating the backup to disk, now it does data corruption validation so you know that the backup is corrupt before you need to restore it. He said more people now is using backup to cheap ATA disks.

Rich spoke about Data recovery adviser, now you don't have to think about what is the best way to recover your database in the less time, Oracle will recommend it to you.

In Data guard now can synchronize disk mirroring, and in 11g you can have a snapshot standby and been recovered at the same time, let me explain that. In the past you could use your read only standby for reporting (read only) but while the database was open it was not applying the transaction log from the primary, you had to close the database and place it in recovery mode again so it apply the changes. In 11g you can have the database open read only and the transaction log is been applied at the same time.

11g has a feature called real patch application where making use of grid control and if Oracle detects an error it can apply a patch to fix the error online. Also Oracle is doing their best to deliver all their future patches making use of rolling patches in RAC, same with the Database upgrade.

In 11g you can do online table and index redefinition online, yes I know you will say you can do it in 10g too, the difference is that is 11g there is no locking.

He said that in 11g you only have to set 1 parameter and that parameter is memory_target and that includes your PGA and your SGA.

In 11g there is a new parameter called result_cache_mode, using this parameter you can ask Oracle not to cache the results, in addition you can flush the db cache. The cache in 11g is more intelligent therefore improving performance.

In 11g there are invisible indexes, which are useful if you want to know how an application with lots of indexes will behave if you drop some of them, but without the need to drop them. If you make the index invisible Oracle will ignore it, and if you really need the index all you have to do is make it visible again.

He said people usually rebuild indexes online while what they should be doing is coalescing the indexes.

In 11g Oracle introduced a simple integer data type saving space and speeding up transactions. 11g also introduced a lot of improvements in ADDM, there is also a Global ADDM which access the entire cluster performance information.

11g introduced the SQL Plan management (SPM) which basically is a tool that records what is going on and can play it back.

Oracle is deprecating Outlines and replacing it with SQL Plan base line.

There are also improvements in grid control 11g for performance. You can capture baselines, then use the sql access advisor and sql repair advisor.

The SQL Performance analyzer captures performance and allows you to run the same in other environment, at the end it provides you a graphic comparison between environments.

Real Application testing captures your database workload and allows you to replay it in another system, which this features you an test different version of software and architectures.

Partitioning just got better with the interval clause, now if you forget to specify the maximum value for the partition or a partition gets out of range you will not get an error are long as the interval parameter is specified. In other words the interval is telling Oracle what interval to use to create new partitions when data is out of range and now you can compress partitioned tables.

Oracle is introducing Oracle Secure Files which is faster than using lobs. Oracle also did improvements on the optimizer statistics , now you have an auto sampling feature. You can also gather stats and not publish the stats until you are not ready.

There is also a new feature where Oracle understand the correlation between different column tables and behave different depending of the utilization. You can have multi column, multi table stats. They also included several security enhancements which he did not detailed.

My last meeting for the day was the keynotes with Andy Meldensohn, he is the Senior product manager for the database, he went over some of the new features for the database again, and some of their customers implementing and taking advantage of those features spoke as well. The CIO of eHarmony said that they tried using SQL Server 2005 clustered environment but did not provided them the scalability they needed so they switched to Oracle RAC.

More information tomorrow.