Oracle NZ - Francisco Munoz Alvarez

Hi All,

NZOUG (New Zealand Oracle Users Group) and LAOUC (Latin American Oracle Users Communty) is organizing a serie of webinars to Oracle Community (over 42 Webinars) with the participation of several Oracle Celebrities, and is my desire to share the first 8 webinars recorded with all Oracle Users around the world!

Please find bellow all the webinars recorded in the past 2 months for LAOUC and NZOUG, I want to express a special thank you to: Arup Nanda, Daniel Morgan, Debra Lilley, Alex Gorbachev and Ben Prusinski for their invaluable participation (Alex Webinar recording is coming soon) and  for the incredible desire to share their knowledge with the community!

Arup Nanda Presenting Under the Hoods of Cache Fusion, GES, GCS and GRD	Daniel Morgan Presenting 11gR2 Edition Based Redefinition
Ben Prusinski Presenting  Tip & Tricks Patching EBS R12	Arup Nanda Presenting  RAC Performance Tuning
Daniel Morgan Presenting How to Read and Interpret an Explain Plan	Arup Nanda Presenting How to Secure Your DB in one Day
Debra Lilley Presenting Fusion Applications, What’s behind it!

A lot of people don’t know several powerful functionalities that we have available when using Data Pump (expdp/impdp), most of the people only use these tools to export and import data (in other words, only to move data), and never notice that it can be used for example to help us to do:

• Data Masking
• Build a Metadata Repository
• Create a version control
• Clone Users (Create a new user using and existent user as a template)
• Create smaller copies of production
• Create your database in a different file structure
• Move all objects from one tablespace to another
• Move a object to a different schema (A simple example, change a table owner)

Now let’s see how each functionality I mentioned above can be used at real life.

1) Data Masking

In many organizations (I hope so) the DBA’s had the obligation for a security and compliance purpose to mask all sensible information that leaves the production environment to as an example to refresh or create a QA/Test or Dev environment. To help us to address this kind of requirements we could easily use the Enterprise Manager Data Masking Pack (remember it is an extra pack, and consequently you need to pay extra to use it), or as a different option, use the “remap_data” parameter available in Data Pump to help you with this requirement(**this is a new functionality at 11g)!

Let’s use the classic SSN (Social Security Number) example to illustrate how it works:

a) First let’s create the table for the test and load some data on it.

SQL> CREATE TABLE HR.EMPLOYEE

  2  ( EMP_ID   NUMBER(10) NOT NULL,

  3    EMP_NAME VARCHAR2(30),

  4    EMP_SSN  VARCHAR2(9),

  5    EMP_DOB  DATE

  6* )

SQL> /

insert into hr.employee values (101,‘Francisco Munoz’,123456789,’30-DEC-73′);

insert into hr.employee values (102,‘Horacio Miranda’,234567890,’17-JUL-76′);

insert into hr.employee values (103,‘Evelyn Aghemio’,659812831,’02-OCT-79′);

b) The second step will be to create the remap function:

SQL> create or replace package pkg_masking

   2 as

   3 function mask_ssn (p_in varchar2) return varchar2;

   4 end;

   5 /

SQL> create or replace package body pkg_masking

   2 as

   3 function mask_ssn (p_in varchar2)

   4 return varchar2

   5 is

   6 begin

   7 return lpad (

   8 round(dbms_random.value (001000000,999999999)),9,0);

   9 end;

  10 end;

  11 /

This function will take a varchar argument and returns a 9 char. We will use this function to mask all SSN information inside our employee table.

SQL> desc employee

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

EMP_ID                                    NOT NULL NUMBER(10)

EMP_NAME                                           VARCHAR2(30)

EMP_SSN                                            VARCHAR2(9)

EMP_DOB                                            DATE

SQL> select * from employee;

EMP_ID     EMP_NAME                       EMP_SSN   EMP_DOB

---------- ------------------------------ --------- ---------

101        Francisco Munoz                123456789 30-DEC-73

102        Horacio Miranda                234567890 17-JUL-76

103        Evelyn Aghemio                 345678901 02-OCT-79

For this example, all you want to mask is the column EMP_SSN, which contains the SSN of each employee.

b) Now we are going to export the table employees using the expdp tool, and while exporting, we will use the parameter “remap_data” to mask the data for us in the dump file using the function we previously created.

$ expdp hr/hr tables=hr.employee dumpfile=mask_ssn.dmp directory=datapump remap_data=hr.employee.emp_ssn:pkg_masking.mask_ssn

Note: By defect the “remap_data” parameter will use the user doing the export as the owner of the remap function, if the schema owner of the function is different you will need to use the following commad:

$ expdp hr/hr tables=hr.employee dumpfile=mask_ssn.dmp directory=datapump remap_data=hr.employee.emp_ssn:owner.pkg_masking.mask_ssn

c) Now all we need to do is to import the mask_ssn.dmp in our QA/Test or Dev Database and it will magically have the new values there.

SQL> select * from employee;

EMP_ID     EMP_NAME                       EMP_SSN   EMP_DOB

---------- ------------------------------ --------- ---------

101        Francisco Munoz                108035616 30-DEC-73

102        Horacio Miranda                324184688 17-JUL-76

103        Evelyn Aghemio                 638127075 02-OCT-79

Note: you can use the “remap_data” option in the impdp tool if you have a normal export done before , also remember that you can use it to mask almost everything, but please take in consideration your application requirements and data integrity requirements when using it!

For more information regarding this option and to see another examples, please refer to this paper: http://www.oracle.com/technology/products/database/utilities/pdf/datapump11g2009_transform.pdf

2) Metadata Repository and Version Control

As a DBA, I’m always looking for proactive ways to allow me to be prepared in case of a disaster strike or if an emergency release rollback is required (I love to use always the “What if” methodology), and due to these reasons, have a metadata repository and version control of it is always useful .

But how can I easily create it? Easy, first do a full backup of your database using Datapump.

$ expdp user/password content=metadata_only full=y directory=datapump dumpfile=metadata_24112010.dmp

Note: If you want to create a repository only for objects like procedures, packages, triggers, … , all you need to do is add the parameter “include=<procedures,packages,triggers,…> to your expdp command, I usually include in the dump file name the date of the dump for reference purpose and best practice.

Then use the impdp tool to create the SQL file that will allow you to create all objects in your Database. It will be something like this:

$ impdp user/password directory=datapump dumpfile= metadata_24112010.dmp sqlfile=metadata_24112010.sql

This simple technique will allow you to create your metadata repository easily and also keep a versioning of your database objects as an extra, also if you create your repository (DB) and you want to refresh an object definition (as example let use the table emp from schema “scott”), all you will need to do is an export of the new table definition from your source database and then import it on your target database (your repository) as show bellow:

$ expdp user/password content=metadata_only tables=scott.emp directory=datapump dumpfile= refresh_of_table_emp_24112010.dmp

$ impdp user/password table_exists_action=replace directory=datapump dumpfile= refresh_of_table_name_24112010.dmp

3) Clone a User

In the past when a DBA had the need to create a new user with the same structure (All objects, tablespaces quota, synonyms, grants, system privileges, etc) was a very painful experience, now all can be done very easily using Data Pump, let use as an example that you want to create the user ”Z” exactly like the user “A”, to achieve this goal all you will need to do is first export the schema “A” definition and then import it again saying to the Data Pump to change the schema “A” for the new schema named “Z” using the “remap_schema” parameter available with impdp.

$ expdp user/password schemas=A content=metadata_only directory=datapump dumpfile= A_24112010.dmp

$ impdp user/password remap_schema=A:Z directory=datapump dumpfile= A_24112010.dmp

And your new user Z is now created like your existing user A , that easy!

4) Create smaller copies of production

That is a very common task for a DBA, you are always having a task to create a copy of your Database (for development or test purpose) but your destination server don’t have enough space to create a full copy of it! This can be easily solved with Data Pump, for this example, let say that you only have space for 70% of your production database, now to know how to proceed, we need to decide if the copy will contain metadata only (no data/rows) or if it will include the data also. Let’s see how to do each way:

a) Metadata Only

First do a full export of your source database.

$ expdp user/password content=metadata_only full=y directory=datapump dumpfile=metadata_24112010.dmp

Then, let’s import the metadata and tell the Data Pump to reduce the size of extents to 70%, you can do it using the parameter “transform” available with “impdp”, it represent the percentage multiplier that will be used to alter extent allocations and datafiles size.

$ impdp user/password transform=pctspace:70 directory=datapump dumpfile=metadata_24112010.dmp

Let’s do a test and see if this is really true, first let export any table of my test database (metadata only) and generate the “sql” script to see the normal size of it.

$expdp user/password content=metadata_only tables=user.x_integration_log_det directory=datapump dumpfile=example_24112010.dmp

$impdp user/password content=metadata_only directory=datapump dumpfile=example_24112010.dmp sqlfile=x_24112010.sql

CREATE TABLE "USER“.”X_INTEGRATION_LOG_DET“

      ( "BATCH_NO” NUMBER(9,0),

        "SEQUENCE#” NUMBER(9,0),

        "FILENAME” VARCHAR2(200 BYTE),

        "ERROR_MESSAGE” VARCHAR2(2000 BYTE),

        "NO_OF_RECORDS” NUMBER,

        "STATUS” VARCHAR2(2000 BYTE)

      ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "CUSTSERV_LOG_DET_DATA” ;

Above is the SQL code generated by Data Pump, you can see that the table is going to be created using 65536 for the initial extent and 1048576 for the next extent, now let’s generate it again but using the transform parameter to reduce the size of it to 70% of original size.

$impdp user/password transform=pctspace:70 content=metadata_only directory=datapump dumpfile=example_24112010.dmp sqlfile=x_24112010.sql

CREATE TABLE "USER“.”X_INTEGRATION_LOG_DET“

    ( "BATCH_NO” NUMBER(9,0),

      "SEQUENCE#” NUMBER(9,0),

      "FILENAME” VARCHAR2(200 BYTE),

      "ERROR_MESSAGE” VARCHAR2(2000 BYTE),

      "NO_OF_RECORDS” NUMBER,

      "STATUS” VARCHAR2(2000 BYTE)

    ) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 45875 NEXT 734003 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "CUSTSERV_LOG_DET_DATA” ;

Above is the SQL code generated by Data Pump, and you can see that the table is now going to be created using 45875 for the initial extent and 734003 for the next extent, clearly reduced 30% of the original size, in other words, it works.

Please refer to Oracle documentation for more ways to use the transform parameter, you will not regret

b) Metadata and data

First does a full export of your source database using the export parameter “sample”, this parameter specify a percentage of the data rows to be sampled and unload from your source database, in this case let’s use 70%.

$ expdp user/password sample=70 full=y directory=datapump dumpfile=expdp_70_24112010.dmp

Then, all you need to do as the example before is to import it telling the Data Pump to reduce the size of extents to 70%, and that’s it!

$ impdp user/password transform=pctspace:70 directory=datapump dumpfile=expdp_70_24112010.dmp

5) Create your database in a different file structure

This is very easy to archive, all you need to do is use the parameter “remap_datafile” on your import command as the example bellow:

$ impdp user/password directory=datapump dumpfile=example_24112010.dmp remap_datafile=’/u01/app/oracle/oradata/datafile_01.dbf’:’/u01/datafile_01.dbf’

6) Move all objects from one tablespace to another

This is very easy to do it, as the previous example, all you will need to do is use the parameter “remap_tablespace” on your import command as the example bellow:

$ impdp user/password directory=datapump dumpfile=example_24112010.dmp remap_tablespace=OLD_TBS:NEW_TBS

7) Move a object to a different schema

All you will need to do is use the parameter remap_schema as the example bellow when importing it.

$ expdp user/password tables=user.table_name directory=datapump dumpfile=table_name_24112010.dmp

$ impdp user/password directory=datapump dumpfile=table_name_24112010.dmp remap_schema=old_schema:new_schema

Always remember, Data Pump is your good friend, but RMAN still your best friend ;). You can easily guess my next month topic will be…  :p

The New Zealand conference this year was a great event, with a little over 150 participants and with a fantastic group of speakers and topics (For more information please refer to http://www,nzoug.org ).

From Left to Right: Guy Harrison, Chris Muir, Tim Hall, Robert Freeman, Daniel Morgan, and Francisco Munoz Alvarez.

During the event, my company  (Database Integrated Solutions http://www.dbisonline.com) was doing a pool to see the reality of the New Zealand market, 87 delegates from 31 different companies answered our questionary, and here are the results:

 

Thanks’ to all NZOUG participants and hope to see everyone again next year!

Cheers,

Francisco Munoz Alvarez

Who never had a problem with a SQL that is killing your Database Performance and you can’t fix it because it’s running from an external closed application that you or your developers can’t touch?

Since Oracle version 10 this is a problem of the past, now you can easy solve this kind of problem using the DBMS_ADVANCED_REWRITE package, which allow you to transform/customize queries on the fly, changing for example one query with bad explain plan for another one with a good explain plan.

Before you become too excited, please remember the following restrictions: 

• It does not work with bind variables.(Alternative solution at Metalink Doc ID. 392214.1)
• Only works for the SELECT statement.
• Does not work when the base table is modified through DML.

To see how it works, first we will need to grant execute privileges on the package to our user called test and allow it to create materialized views.

CONN sys/password AS SYSDBA

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test;

GRANT CREATE MATERIALIZED VIEW TO test;

Now let’s create and populate our objects for test purpose:

CONN test/test

CREATE TABLE students (

  student_id               NUMBER(10),

  student_name             VARCHAR2(45),

  student_status           VARCHAR2(1),

  student_year             number(2),

  student_address          varchar2(45),

  student_city             varchar2(16),

  student_zip              number(6),

  student_social_security  number(10))

/

ALTER TABLE students

ADD CONSTRAINT pk_student PRIMARY KEY (student_id)

USING INDEX

PCTFREE 0;

BEGIN

  INSERT INTO STUDENTS VALUES (1,'PAUL COOK','Y',9,'5 Main Road','Auckland',2031,100101000);

  INSERT INTO STUDENTS VALUES (2,'HORACIO MIRANDA','Y',8,'15 Main Road','Auckland',2031,100101001);

  INSERT INTO STUDENTS VALUES (3,'SCOTT PEDERSEN','Y',7,'13 Main Road','Auckland',2031,100101002);

  INSERT INTO STUDENTS VALUES (5,'SETH PICKERING','Y',9,'12 Main Road','Auckland',2031,100101003);

  INSERT INTO STUDENTS VALUES (6,'FRANCISCO ALVAREZ','Y',11,'11 Main Road','Auckland',2031,100101004);

  INSERT INTO STUDENTS VALUES (7,'ALTMAAR VISSER','Y',9,'16 Main Road','Auckland',2031,100101005);

  INSERT INTO STUDENTS VALUES (9,'REYNALDO OCFEMIA','Y',6,'25 Main Road','Auckland',2031,100101006);

  INSERT INTO STUDENTS VALUES (15,'CAMERON PITCHES','Y',12,'31 Main Road','Auckland',2031,100101007);

  INSERT INTO STUDENTS VALUES (18,'MONIQUE GENNIP','Y',8,'71 Main Road','Auckland',2031,100101008);

  INSERT INTO STUDENTS VALUES (99,'TERRENCE LO','Y',6,'17 Main Road','Auckland',2031,100101009);

  INSERT INTO STUDENTS VALUES (100,'KIM FONG','Y',11,'16 Main Road','Auckland',2031,100101010);

  INSERT INTO STUDENTS VALUES (103,'CHRIS OPPERMAN','Y',12,'7 Main Road','Auckland',2031,100101011);

  INSERT INTO STUDENTS VALUES (104,'SCOTT TIGER','Y',6,'62 Main Road','Auckland',2031,100101012);

  INSERT INTO STUDENTS VALUES (105,'EVELYN AGHEMIO','Y',11,'32 Main Road','Auckland',2031,100101013);

  INSERT INTO STUDENTS VALUES (106,'TOMAS MUNOZ','Y',11,'18 Main Road','Auckland',2031,100101014);

  INSERT INTO STUDENTS VALUES (107,'GONZALO TORRES','Y',10,'14 Principal Road','Auckland',2031,100101015);

  INSERT INTO STUDENTS VALUES (108,'JOHN KEY','Y',10,'12 Principal Road','Auckland',2031,100101016);

  INSERT INTO STUDENTS VALUES (109,'JOHN A','Y',7,'21 Principal Road','Auckland',2031,100101017);

  INSERT INTO STUDENTS VALUES (111,'JOHN B','Y',9,'121 Principal Road','Auckland',2031,100101018);

  INSERT INTO STUDENTS VALUES (112,'JOHN C','Y',8,'321 Principal Road','Auckland',2031,100101019);

  INSERT INTO STUDENTS VALUES (113,'JOHN D','Y',6,'35 Principal Road','Auckland',2031,100101020);

  INSERT INTO STUDENTS VALUES (114,'JOHN E','Y',12,'41 Principal Road','Auckland',2031,100101021);

  INSERT INTO STUDENTS VALUES (116,'JOHN F','Y',8,'161 Principal Road','Auckland',2031,100101022);

  INSERT INTO STUDENTS VALUES (10,'JOHN G','Y',7,'171 Principal Road','Auckland',2031,100101023);

  INSERT INTO STUDENTS VALUES (311,'JOHN H','Y',11,'353 Principal Road','Auckland',2031,100101024);

  INSERT INTO STUDENTS VALUES (312,'JOHN I','Y',7,'351 Principal Road','Auckland',2031,100101025);

  INSERT INTO STUDENTS VALUES (319,'JOHN K','Y',9,'352 Principal Road','Auckland',2031,100101026);

  INSERT INTO STUDENTS VALUES (322,'JOHN L','Y',6,'353 Principal Road','Auckland',2031,100101027);

  INSERT INTO STUDENTS VALUES (333,'JOHN M','Y',11,'354 Principal Road','Auckland',2031,100101028);

  INSERT INTO STUDENTS VALUES (343,'JOHN N','Y',6,'355 Principal Road','Auckland',2031,100101029);

  INSERT INTO STUDENTS VALUES (344,'JOHN O','Y',7,'356 Principal Road','Auckland',2031,100101030);

  INSERT INTO STUDENTS VALUES (345,'JOHN P','Y',8,'357 Principal Road','Auckland',2031,100101031);

  INSERT INTO STUDENTS VALUES (346,'JOHN Q','Y',9,'358 Principal Road','Auckland',2031,100101032);

  INSERT INTO STUDENTS VALUES (347,'JOHN R','Y',10,'359 Principal Road','Auckland',2031,100101033);

  INSERT INTO STUDENTS VALUES (350,'JOHN S','Y',11,'360 Principal Road','Auckland',2031,100101034);

  INSERT INTO STUDENTS VALUES (530,'JOHN T','Y',12,'361 Principal Road','Auckland',2031,100101035);

  INSERT INTO STUDENTS VALUES (531,'JOHN U','Y',13,'362 Principal Road','Auckland',2031,100101036);

  INSERT INTO STUDENTS VALUES (533,'JOHN V','N',6,'35 Principal Road','Auckland',2031,100101037);

  INSERT INTO STUDENTS VALUES (534,'JOHN X','N',8,'13 Principal Road','Auckland',2031,100101038);

  INSERT INTO STUDENTS VALUES (535,'JOHN Z','N',7,'135 Principal Road','Auckland',2031,100101039);

  INSERT INTO STUDENTS VALUES (536,'JOHN Y','N',11,'435 Principal Road','Auckland',2031,100101040);

  INSERT INTO STUDENTS VALUES (537,'JOHN W','Y',8,'635 Principal Road','Auckland',2031,100101041);

  INSERT INTO STUDENTS VALUES (539,'ARTUR JOHNES','Y',6,'22 Secondary Road','Auckland',2031,100101042);

  INSERT INTO STUDENTS VALUES (540,'KING PANTHER','Y',7,'22 Secondary Road','Auckland',2031,100101043);

  INSERT INTO STUDENTS VALUES (541,'PINK PANTHER','Y',8,'22 Secondary Road','Auckland',2031,100101044);

  INSERT INTO STUDENTS VALUES (542,'HAROLD ROBINS','Y',9,'221 Secondary Road','Auckland',2031,100101045);

  INSERT INTO STUDENTS VALUES (543,'CHRIS BONES','Y',8,'222 Secondary Road','Auckland',2031,100101046);

  INSERT INTO STUDENTS VALUES (545,'TIM TOM','Y',9,'223 Secondary Road','Auckland',2031,100101047);

  INSERT INTO STUDENTS VALUES (546,'TIM JONES','Y',10,'223 Secondary Road','Auckland',2031,100101048);

  INSERT INTO STUDENTS VALUES (547,'MICHAEL JONES','Y',11,'224 Secondary Road','Auckland',2031,100101049);

  INSERT INTO STUDENTS VALUES (548,'ANN SMITH','Y',12,'225 Secondary Road','Auckland',2031,100101050);

  INSERT INTO STUDENTS VALUES (549,'JOHN SMITH','Y',13,'226 Secondary Road','Auckland',2031,100101051);

  INSERT INTO STUDENTS VALUES (551,'PAUL STONE','Y',6,'227 Secondary Road','Auckland',2031,100101052);

  INSERT INTO STUDENTS VALUES (552,'CARL SMITH','Y',7,'228 Secondary Road','Auckland',2031,100101053);

  INSERT INTO STUDENTS VALUES (553,'TEST','Y',8,'229 Secondary Road','Auckland',2031,100101054);

  COMMIT;

END;

/

CREATE TABLE grades (

  student_id       NUMBER(10),

  grade   NUMBER(6,2),

  grade_subject    VARCHAR2(4),

  grade_date       DATE,

  grade_note      VARCHAR2(60))

/

ALTER TABLE grades

ADD CONSTRAINT pk_GRADES PRIMARY KEY (student_id, grade, grade_subject,grade_date)

USING INDEX

PCTFREE 0;

ALTER TABLE grades

ADD CONSTRAINT fk_students

FOREIGN KEY (student_id)

REFERENCES students(student_id);

BEGIN

  INSERT INTO GRADES VALUES (553,100.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (552,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (551,87.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (549,87.5,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (548,90.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (547,64.7,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (546,85.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (545,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (543,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (542,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (541,94.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (540,94.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (539,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (1,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (2,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (3,98.7,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (5,96.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (6,97.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (7,90.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (9,91.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (15,92.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (18,93.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (99,94.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (100,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (533,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (534,100.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (535,100.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (536,99.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (537,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (530,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (531,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (103,67.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (104,56.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (105,93.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (106,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (107,72.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (108,71.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (109,68.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (111,77.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (112,87.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (113,65.5,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (114,34.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (116,91.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (10,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (311,78.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (312,88.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (319,67.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (322,89.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (333,95.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (343,91.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (344,98.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (345,87.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (346,93.0,'ENGL',sysdate,null);

  INSERT INTO GRADES VALUES (347,99.0,'ENGL',sysdate,null);

  COMMIT;

  INSERT INTO GRADES VALUES (553,100.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (552,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (551,87.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (549,87.5,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (548,90.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (547,64.7,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (546,85.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (545,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (543,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (542,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (541,94.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (540,94.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (539,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (1,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (2,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (3,98.7,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (5,96.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (6,97.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (7,90.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (9,91.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (15,92.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (18,93.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (99,94.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (100,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (533,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (534,100.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (535,100.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (536,99.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (537,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (530,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (531,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (103,67.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (104,56.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (105,93.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (106,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (107,72.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (108,71.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (109,68.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (111,77.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (112,87.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (113,65.5,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (114,34.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (116,91.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (10,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (311,78.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (312,88.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (319,67.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (322,89.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (333,95.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (343,91.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (344,98.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (345,87.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (346,93.0,'MATH',sysdate-7,null);

  INSERT INTO GRADES VALUES (347,99.0,'MATH',sysdate-7,null);

  COMMIT;

  INSERT INTO GRADES VALUES (553,100.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (552,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (551,87.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (549,87.5,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (548,90.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (547,64.7,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (546,85.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (545,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (543,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (542,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (541,94.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (540,94.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (539,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (1,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (2,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (3,98.7,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (5,96.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (6,97.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (7,90.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (9,91.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (15,92.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (18,93.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (99,94.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (100,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (533,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (534,100.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (535,100.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (536,99.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (537,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (530,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (531,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (103,67.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (104,56.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (105,93.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (106,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (107,72.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (108,71.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (109,68.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (111,77.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (112,87.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (113,65.5,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (114,34.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (116,91.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (10,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (311,78.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (312,88.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (319,67.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (322,89.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (333,95.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (343,91.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (344,98.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (345,87.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (346,93.0,'BIOL',sysdate,null);

  INSERT INTO GRADES VALUES (347,99.0,'BIOL',sysdate,null);

  COMMIT;

  INSERT INTO GRADES VALUES (553,100.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (552,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (551,87.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (549,87.5,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (548,90.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (547,64.7,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (546,85.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (545,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (543,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (542,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (541,94.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (540,94.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (539,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (1,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (2,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (3,98.7,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (5,96.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (6,97.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (7,90.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (9,91.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (15,92.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (18,93.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (99,94.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (100,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (533,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (534,100.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (535,100.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (536,99.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (537,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (530,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (531,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (103,67.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (104,56.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (105,93.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (106,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (107,72.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (108,71.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (109,68.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (111,77.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (112,87.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (113,65.5,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (114,34.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (116,91.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (10,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (311,78.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (312,88.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (319,67.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (322,89.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (333,95.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (343,91.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (344,98.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (345,87.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (346,93.0,'ARTS',sysdate,null);

  INSERT INTO GRADES VALUES (347,99.0,'ARTS',sysdate,null);

  COMMIT;

END;

/

Now let simulate that you found the SQL bellow running in your Database:

SQL> Explain plan for

select student_name,avg(a.grade) from grades a, students b

where b.student_social_security = 100101016

and   b.student_id = a.student_id

group by student_name

/

STUDENT_NAME                                  AVG(A.GRADE)

--------------------------------------------- ------------

JOHN KEY                                                71

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3187331965

---------------------------------------------------------------------------------

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |           |     4 |   304 |     5  (20)| 00:00:01 |

|   1 |  HASH GROUP BY      |           |     4 |   304 |     5  (20)| 00:00:01 |

|   2 |   NESTED LOOPS      |           |     4 |   304 |     4   (0)| 00:00:01 |

|*  3 |    TABLE ACCESS FULL| STUDENTS  |     1 |    50 |     3   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN | PK_GRADES |     4 |   104 |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("B“.”STUDENT_SOCIAL_SECURITY“=100101016)

   4 - access("B“.”STUDENT_ID“=”A“.”STUDENT_ID“)

Note

-----

   - dynamic sampling used for this statement

You can see that the SQL above is doing a full scan to the students table, after a few modifications we have another SQL, a little more efficient, and it will be:

SQL> Explain plan for

select student_name,avg(a.grade) from grades a, students b

where b.student_id = 108

and   b.student_id = a.student_id

group by student_name

/

STUDENT_NAME                                  AVG(A.GRADE)

--------------------------------------------- ------------

JOHN KEY                                                71

Execution Plan

----------------------------------------------------------

Plan hash value: 3300694555

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |     4 |   252 |     2   (0)| 00:00:01 |

|   1 |  HASH GROUP BY                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| STUDENTS   |     1 |    37 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | PK_STUDENT |     1 |       |     1   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | PK_GRADES  |     4 |   104 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("B“.”STUDENT_ID“=108)

   5 - access("A“.”STUDENT_ID“=108)

Note

-----

   - dynamic sampling used for this statement

You can see that the second SQL is more efficient that the first one, and for that reason we will order Oracle to replace the bad SQL for the good one every time the bad SQL is executed, how we can do it? Easily, the magic will be:

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

SQL> BEGIN

       sys.dbms_advanced_rewrite.declare_rewrite_equivalence (

       name              => 'test_rw1',

       source_stmt =>

       'select student_name,avg(a.grade)

       from grades a, students b

       where b.student_social_security = 100101016

       and b.student_id = a.student_id

       group by student_name',

       destination_stmt =>

       'select student_name,avg(a.grade)

       from grades a, students b

       where b.student_id = 108

       and   b.student_id = a.student_id

       group by student_name',

       validate  => false,

       rewrite_mode      => 'text_match');

     END;

     /

Let’s now see if the magic really works:

SQL> Explain plan for

select student_name,avg(a.grade) from grades a, students b

where b.student_social_security = 100101016

and   b.student_id = a.student_id

group by student_name

/

STUDENT_NAME                                  AVG(A.GRADE)

--------------------------------------------- ------------

JOHN KEY                                                71

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Execution Plan

----------------------------------------------------------

Plan hash value: 3300694555

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |            |     4 |   252 |     2   (0)| 00:00:01 |

|   1 |  HASH GROUP BY                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS                |            |     4 |   252 |     2   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| STUDENTS   |     1 |    37 |     1   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | PK_STUDENT |     1 |       |     1   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN           | PK_GRADES  |     4 |   104 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - access("B“.”STUDENT_ID“=108)

   5 - access("A“.”STUDENT_ID“=108)

Note

-----

   - dynamic sampling used for this statement

The magic is done, now every time the source_stmt is execute it will be replaced by the destination_stmt with a better execution plan

Enjoy this trick!

Kind Regards,

Francisco Munoz Alvarez

I want to start this New Year creating a new session on my blog called “DBA Tip of the month” where I will try to post every month an important tip to help all fellows DBAs to improve their careers.

The first topic of this session will be:
 

“Being Proactive with Backup & Recovery”

Always when I arrive on a new client I ask the DBA on charge the following questions:

• Do you have your recovery strategy documented step by step?
• Are you 100% sure that your tape backups are usable?
• Do you know exactly how long a recovery on your production environment will take if necessary?

And almost 90% of the time the answers will be:

• No!
• I not sure, but I think so!
• No idea, probably…!

You will be on shock to know how many times I’m call to support a DBA to try to recover a Database because the most current tape backup is unusable!

Backup & Recovery are a very important (crucial) part of a DBA role, as a DBA I’ll never be stressed enough to repeat over and over what in my opinion is the most important rule for a DBA:

“The most important rule with respect to data is to never put yourself into an unrecoverable situation.”

You know, because bad stuff happen….

…When you less expect, and due to this, I’ll always recommend a DBA to perform a proactive approach to his/her Database Backup and Recovery strategy.

The main idea is:

1. Randomly choose a backup tape and recovery it on a test machine (It can be a virtual one).
2. Take this opportunity to document all the recover process.
3. Review the entire process ant try to improve it!
4. Repeat this exercise every month and try to involve other DBAs in the process!

This easy process will allow you to:

1. Test your Tape backups and see if they are being backup correctly.
2. Check and improve your recovery knowledge and strategy.
3. Document all your recovery process that could be used for any other DBA in the company in case you are not available in the recovery situation.
4. Detect any error on your backup & recovery strategy.
5. Know your recovery time. Next time your manager asks you” Do you know how long a recovery will take? You will know the exact answer.
6. Have an opportunity to review your process and try to make it more efficient.

Like you can see, this is an easy proactive exercise that will allow you and your company to be prepared in case of a disaster and recovery situations occurs, and you know when this always happens….
 

Cheers,

Francisco Munoz Alvarez

DB_ULTRA_SAFE is a new parameter introduced with Oracle 11gR1, and a fantastic new GEM for High Availability, that using Data Guard to configure on both the primary and standby will trigger the most comprehensive data corruption prevention and detection (and repair on 11gR2, see **) tool in the market.

** Starting in Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

Speaking simple, what this new functionality will do is use your Standby Database as a backup to correct automatically any data corruption on your primary database and vice-versa (again on 11.2).

The DB_ULTRA_SAFE initialization parameter also controls other data protection behavior in Oracle Database, such as requiring ASM to perform sequential mirror write I/Os.

You basically need to understand that when setting DB_ULTRA_SAFE it will control the behaviour of DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters for you, which mean:

When you set DB_ULTRA_SAFE to	Then the following parameters…
DATA_AND_INDEX (recommended by Oracle)	DB_BLOCK_CHECKING is set to FULL. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.
DATA_ONLY	DB_BLOCK_CHECKING is set to MEDIUM. DB_LOST_WRITE_PROTECT is set to TYPICAL. DB_BLOCK_CHECKSUM is set to FULL.

Lets’ Check  all the parameters affected by DB_ULTRA_SAFE:

• DB_BLOCK_CHECKING (Introduced with Oracle 8.1.6) prevents memory and data corruptions, but it incurs some performance overhead on every block change.
• DB_BLOCK_CHECKSUM (Introduced with Oracle 8.1.6) detects redo and data block corruptions and detect corruptions on the primary database and protect the standby database. This parameter requires minimal CPU resources.
• DB_LOST_WRITE_PROTECT (also introduced with 11gR1) enable or disable a physical standby database to detect lost write corruptions on both the primary and physical standby database.

Important: if you explicitly set the DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, or DB_LOST_WRITE_PROTECT parameters in the initialization parameter file, then the DB_ULTRA_SAFE parameter has no effect and no changes are made to the parameter values. Thus, if you specify the DB_ULTRA_SAFE parameter, do not explicitly set these underlying parameters.

To activate it, all you need to do is follow the following steps:

On the Primary Database:  

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:
2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;
3. SQL> shutdown immediate (Shutdown your Database)
4. SQL> startup (This will start your primary Database using your new parameter set in the SPFILE previously)

On the Physical Standby Database: 

1. Set the DB_ULTRA_SAFE=DATA_AND_INDEX initialization parameter using:
2. SQL> alter system set db_ultra_safe=dta_and_index scope=spfile;
3. SQL> startup nomount
4. SQL> alter database mount standby database;
5. SQL> alter database recover managed standby database disconnect from session;

If you are using your Standby Database on Read Only mode you also need to run the follow commands on your Physical Standby DB: 

1. SQL> alter database recover managed standby database cancel;
2. SQL> alter database open read only;

If you decide to change later the Read Only Standby to Standby again, you just will need to run the following command: 

1. SQL> alter database recover managed standby database disconnect from session;

Hoping this information could help you in the future,

Francisco Munoz Alvarez

Example done on primary:

Example done at the Standby:

Technorati Tags: DB_ULTRA_SAFE,HIGH AVAILABILITY,BLOCK CORRUPTION,DATA CORRUPTION

I’m writing this post due that I receive this question almost once a week regarding OEM installations.
If you see this error when installing your Oracle Enterprise Manager (Grid Control 10.2.0.3) at OEL 5 or RHEL 5:

/home/oracle/OracleHomes/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

Don’t panic, all you need to do is:

1. Connect as root
2. Run : ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
3. Problem solved, for more information please refer to: http://download.oracle.com/docs/cd/B19306_01/relnotes.102/b15659/toc.htm#CJAHFHCJ

Kind Regards,

Francisco Munoz Alvarez

The EM (Enterprise Manager) Desktop Widgets are lightweight internet applications that provide persistent desktop access to key Enterprise Manager monitoring and diagnostic information.

Oracle first released the EM Widgets at August 2009, now at December 2009; Oracle has already 3 different Widgets available, they are

• Target Search Monitoring - Provides access to Enterprise Manager Targets via quick and easy target-searching capabilities. Also allows you to mark targets as “favourites” and monitor their status in real time.

         

• High-Load Databases - Provides a summary of the top 5 databases ordered by the Average Active Sessions performance or load metric. Also provides access to recent ADDM findings (license required) and drill-down to performance pages of monitored databases.

        

• Service Level & Monitoring - Provides a snapshot of the health of the services, and presents a consolidated view of the most important service data such as availability and current alerts along with the service level. (Require apply the) Enterprise Manager patch# 8869802 before running this widget. 

         

If you are a DBA and want to try them, please refer to this link to download them: http://www.oracle.com/technology/products/oem/widgets/index.html

Kind Regards,

Francisco Munoz Alvarez

SANGAM’09 at Bangalore, India is already behind and I don’t have words to express how wonderful experience it was, I just can say I’ll never forget. India is a fantastic country and a beautiful place to visit. I want to say thank you to the AIOUG directors for the invitation to speak in their first conference, it was fantastic  and I’m 100% sure that AIOUG have a great future and I’ll be more than glad to help and participate any time requested!

Regarding my ACE program presentation in the end of my “Logging or No Logging” speech, I’ll love to see more Indian nominations for ACE Awards, due to the great capacity and knowledge of all professionals in the region, please, if you know someone that fit on this category, please submit a nomination and help Oracle to detect and award the best of the best on your country. (Nominations here)

Download SANGAM’09 presentations and script here

   

Francisco Munoz Alvarez  

After months of tests, Oracle released today the newest release of 11g Database (11.2.0.1 - for Linux x86 and 64bit only), with more than 200 new features and a lot of excellent surprises.

Oracle Database 11g Release 1 (11.1 – Released at August 2007) and Release 2 (11.2 – Released today) are the versions with the bigger number of new capabilities ever build and released by Oracle, that’s way was not a complete surprise when it received the 2009 Best Database of the year award by InfoWorld magazine, and certified by Garner as the Database #1 in the world with 48.9% of the worldwide market..

Capabilities as Real Application Testing, Active Data Guard, Advance Compression, In-Memory Database Cache, ASM Cluster File System (ACFS), the Grid Infrastructure Installation option,  Desktop and Server  class options, Intelligent Data Placement, Oracle Restart,Transparent Tablespace Encryption, Automatic Compilation for Java and PL/SQLs, ADDM for RAC,RAC One Node option, SecureFiles, online upgrade, Flashback Data Archive, Advisors for Streams, Partitioning and Repair  to name some, are new functionalities that in the past where only available at DBA dreams.

If you are looking to upgrade your current DB, direct upgrades are only allowed for versions:

• 9.2.0.8 or higher

• 10.1.0.5 or higher

• 10.2.0.2 or higher

• 11.1.0.6 or higher

 Some deprecated initialization parameters are:

• remote_os_authent

• commit_write

• cursor_space_for_time

• instance_groups

• log_archive_local_first

• plsql_debug replaced by plsql_optimize_level

• plsql_v2_compatibility

• resource_manager_cpu_allocation

• standby_archive_dest

• transaction_lag attribute

• ddl_wait_for_locks

• logmnr_max_persistent_sessions

• plsql_compiler_flags

• max_enabled_roles

• background_dump_dest replace by diagnostic_dest

• user_dump_dest replaced by diagnostic_dest

Always remember that today the most IT and Business challenges/goals are related to:

• Reduce IT Costs
• Reduce Complexity
• Reduce Risk
• Increase the rate of changes
• Be allow to manage more information
• Improve the Quality of service

Clearly Oracle 11g is the best option available in the market to allow you and your business to achieve all these goals. Some of the new functionalities included with 11g are:

New data types

The new data types available in Oracle 11g are:

• Binary XML type - up to 15X faster over XML LOBs. (11gR1)
• DICOM Medical Images. (11gR1)
• 3 D Spatial Support. (11gR1)
• RFID tag data types. (11gR1)

SQL

• Automatic SQL tuning with self-learning capabilities. (11gR1)
• Tables can have Virtual columns (calculated from other columns). (11gR1)
• Virtual Columns Indexes on VC and Partitioning on VC. (11gR1)
• Fast “alter table … add column” with default values. (11gR1)
• Online rebuilding of indexes with no pause on DML activity. (11gR1)
• Ability to mark a table as “read only”. (11gR1)
• New PIVOT and UNPIVOT operations. (11gR1)
• Attribute Extraction of Requested Attributes Only of DICOM metadata. (11gR2)

PL/SQL and XML

• Native Compilation no longer requires a C-compiler. (11gR1)
• New “SIMPLE_INTEGER” data type - always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER. (11gR1)
• SQL and PL/SQL result caching (in SGA). (11gR1)
• Can specify trigger firing order (FOLLOWS-clause). (11gR1)
• Compound triggers - a trigger can be before, after, row and statement all in one. (11gR1)
• New CONTINUE statement - starts the next iteration of the loop. (11gR1)
• DML triggers are up to 25% faster - in particular, row level triggers doing updates against other tables. (11gR1)
• Finer grained dependency tracking. (11gR1)
• Dynamic SQL enhancements. (11gR1)
• Ability to reference sequences (no need to select seq.nextval into :var from dual). (11gR1)
• Support WITH HOLD Option for CURSOR DECLARATION in Pro*C. (11gR2)
• JDBC Support for Time Zone Patching. (11gR2)
• OCI Support for 8-Byte Integer Bind/Define. (11gR2)
• Pro*C Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH. (11gR2)
• Pro*COBOL Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH (11gR2)
• Binary XML Enhancements. (11gR2)
• Oracle XML DB Repository Performance Improvements and Guidelines. (11gR2)
• XMLIndex Enhancements. (11gR2)· XMLType Partitioning. (11gR2)
• JDBC Support for SecureFile Zero-Copy LOB I/O and LOB Prefetching. (11gR2)

ASM

• Support for rolling upgrades. (11gR1)
• Automatic bad block detection and repair. (11gR1)
• Fast mirror resync after temporary connectivity lost. (11gR1)
• ASM Cluster File System (ACFS) (11gR2) - The ASM Cluster File System (ACFS) extends Automatic Storage Management (ASM) by providing a robust, modern, general purpose file system for files beyond the Oracle database files. ACFS provides support for files such as Oracle binaries, report files, trace files, alert logs, and other application data files.
• ASM Dynamic Volume Manager (DVM) (11gR2) - is a kernel-loadable device driver that provides a standard device driver interface to clients (for example, ACFS). File systems or other processes can do I/O to this device driver as they would to any other disk device driver on the system.
• ASM FS Snapshot (11gR2) - is a point-in-time copy of a file system and can provide up to 64 snapshot images.
• Enterprise Manager Integration for ASM File Access Control. (11gR2)
• Oracle Cluster Registry (OCR) and Voting Disk on ASM. (11gR2)
• ASM Optimal Disk Placement. (11gR2)
• ASMCMD Command Extensions (11gR2) - The ASMCMD tool is extended to include management of ASM disks, disk groups, and ASM instance in addition to managing ASM files.
• Automatic Storage Management (ASM) File Access Control. (11gR2)

Partitioning

• Partition advisor - figure out what partitions to create. (11gR1)
• Automated partitioning by interval (new partitions are added automatically). (11gR1)
• Automated reference partitioning by Parent/Child reference (as partitions are created, partitions are created in tables that reference them). (11gR1)
• Partitioning by virtual columns. (11gR1)
• New composite partition types: Range/Range, List/Range, List/Hash, and List/List. (11gR1)
• Support for transportable partitions - for moving partitions between different operating systems. (11gR1)

Compression

• Support compression on INSERT, UPDATE and DELETE operations. 10g only supported compression for bulk data-loading operations. (11gR1)
• Advanced compression allows up to 4 x compression rate of structured and unstructured data improving query performance and reducing storage costs. (11gR1)
• Datapump export dump files can be compressed. (11gR1)
• New Columnar Table Compression .(11gR2)

Clustering

• Agent Development Framework - Oracle Clusterware provides an agent framework for managing all kinds of applications with Oracle Clusterware. Using the agent framework provides optimized application startup, checking, and stopping based on user-defined scripts. This feature helps save money and reduces costs efficiently enabling high availability for applications using Oracle Clusterware. (11gR2)
• Out-of-Place Oracle Clusterware Upgrade - A new version of Oracle Clusterware is now installed into a separate home from the current installation. This reduces the downtime required to upgrade a node in the cluster and facilitate the provisioning of clusters within an enterprise. (11gR2)
• Cluster Time Service - The Cluster Time Service synchronizes the system time on all nodes in the cluster. A synchronized system time across the cluster is a prerequisite to install and successfully run an Oracle cluster. (11gR2)
• Configuration Assistant Support for Removing Oracle RAC Installations. (11gR2)
• Configuration Assistants Support New Oracle RAC Features. (11gR2)
• Downgrading Database Configured With DBControl. (11gR2)
• Enhanced Cluster Verification Utility. (11gR2)
• Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters - Enterprise Manager provisioning introduces procedures to easily scale. (11gR2)
• Enterprise Manager Support for Grid Plug and Play. (11gR2)
• Enterprise Manager Support for Oracle Restart. (11gR2)
• Enterprise Manager-Based Clusterware Resource Management. (11gR2)
• Grid Plug and Play (GPnP). (11gR2)
• Improved Clusterware Resource Modeling. (11gR2)
• Integration of Cluster Verification Utility (CVU) and Oracle Universal Installer (OUI). (11gR2)
• Java API for Oracle RAC FAN High Availability Events. (11gR2)
• Oracle Cluster Registry (OCR) Enhancements. (11gR2)
• Faster relocation of services on node failure.
• Oracle Clusterware now supports up to 5 copies of the OCR for improved availability of the cluster.
• OCR can now be stored in Automatic Storage Management (ASM).
• Oracle Restart Integration with Oracle Universal Installer. (11gR2)
• Oracle Universal Installer Support for Removing Oracle RAC Installations. (11gR2)
• OUI Support for Out-of-Place Oracle Clusterware Upgrade. (11gR2)
• Patch Application with DBControl. (11gR2)
• Policy-Based Cluster and Capacity Management. (11gR2)
• Role-Separated Management - Role-separated management for Oracle Clusterware allows certain administrative tasks to be delegated to different people, representing different roles in the company. It is based on the idea of a clusterware administrator. (11gR2)
• Server Control (SRVCTL) Enhancements. (11gR2)
• Server Control (SRVCTL) Enhancements to Support Grid Plug and Play. (11gR2)
• SRVCTL Support for Single-Instance Database in a Cluster. (11gR2)
• UCP Integration with Oracle Data Guard. (11gR2)
• UCP Integration with Oracle Real Application Clusters. (11gR2)
• Universal Connection Pool (UCP) for JDBC. (11gR2)
• Zero Downtime Patching for Oracle Clusterware and Oracle RAC. (11gR2
• Database QoS Management Support. (11gR2)
• Database Quality of Service (QoS) Management Server (11gR2)
• Enterprise Manager QoS Management Integration. (11gR2)

Performance improvements

• RAC - 70% faster (ADDM has a better global view of the RAC cluster). (11gR1)
• Streams - 30-50% faster. (11gR1)
• Optimizer stats collection – 10X faster. (11gR1)
• Query results caching - 25% faster. (11gR1)
• Table Scans 2.5x Faster using advance compression. (11gR1)
• OLAP-based Materialized Views for fast OLAP CUBE building. (11gR1)
• New In-Memory Database Cache, Data cached in application memory, fast and consistent response times and a standard SQL interface.
• Result caches - new memory areas in the SGA for storing SQL query results, PL/SQL function results and OCI call results. (11gR1)
• Invisible indexes - indexes that are ignored by the optimizer. Handy for testing without dropping. (11gR1)
• Oracle secure files – 5X faster than normal file systems. (11gR1)
• No need to have a C-compiler installed to use Native PL/SQL Compilation. (11gR1)
• Stored Outlines Migration for SQL Plan Management - Stored outlines can be migrated for future and enhanced usage with SQL Plan Management (SPM).
• Support for 4 KB Sector Disk Drives - Today, disk drives have 512 byte sectors. Disk drive manufacturers are moving to 4 KB sector drives because it allows them to offer higher capacity with lower overhead.
• Client Result Cache Using Table Annotations Support - enables applications to leverage client and server result caching through deployment time knobs as opposed to making application changes. In addition, this feature provides automatic client cache invalidation.
• Segment Creation on Demand (11gR2) - The initial segment creation for non partitioned tables and indexes can be delayed until data is first inserted into an object.
• System-Managed Indexes for List Partitioning Tables. (11gR2)
• Zero-Size Unusable Indexes and Index Partitions (11gR2) - Unusable indexes and index partitions do not consume any space in the database anymore; they become segment less.

Availability improvements

• Ability to apply many patches on-line without downtime (RAC and single instance databases). (11gR1)
• Data recovery advisor - quickly identify the root cause of failures; auto fix or present recovery options to the DBA. (11gR1)
• ASM Preferred Mirror Read/ Faster Mirror Resync. (11gR1)
• XA transactions spanning multiple servers. (11gR1)
• Improved runtime connection load balancing. (11gR1)
• Flashback Transaction/ Oracle Total Recall. (11gR1)
• Automatic Block Repair - Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination. (11gR2)
• Backup to Amazon Simple Storage Service (S3) Using OSB Cloud Computing. (11gR2)
• DUPLICATE Without Connection to Target Database. (11gR2)
• Enhanced Tablespace Point-In-Time Recovery (TSPITR). (11gR2)
• New DUPLICATE Options:
  • NO REDO (11gR2)
  • UNDO TABLESPACE <ts_name> [, <ts_name> …] (11gR2)
• New SET NEWNAME Clauses and Format Options (11gR2)
• Tablespace Checks in DUPLICATE. (11gR2)
• New Oracle Streams Features like (11gR2):
  • XStream In - Extended Streams Inbound (XStream In) provides a high performance, transaction-based interface to Oracle Streams for information exchange from non-Oracle databases or file systems.
  • XStream Out - Extended Streams Outbound (XStream Out) provides a high performance, transaction-based interface to Oracle Streams for information exchange to non-Oracle databases or file systems.

Security improvements

• Support for case sensitive and multi-byte passwords (disable by setting SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE. (11gR1)
• Transparent Data Encryption (support for tablespace level encryption). (11gR1)
• Hardware based master key protection. (11gR1)
• Encrypt backups. (11gR1)
• Kerberos authentication - strong passwords. (11gR1)
• Add Multi-factor DBA controls with Data Vault. (11gR1)
• Tablespace Master Key Re-Key (11gR2) - Oracle Advanced Security capability allows customers to change the master key used to protect the encryption keys used to encrypt Oracle tablespaces.

Manageability improvements

• New MEMORY_TARGET parameter for Automatic memory tuning. (11gR1)
• Enterprise Manager Support Workbench for ASM. (11gR2)
• New Gateway Support (11gR2)
• EMCA Supports New Oracle RAC Configuration for EM. (11gR2)
• SQL monitoring (11gR2) - Monitoring the progress of long-running SQL statements at the XPlan level.
• Automatic Patching of Time Stamp with Time Zone Data. (11gR2)
• Prevent Data Loss for Time Zone with Local Time Zone Data Type. (11gR2)
• Enterprise Manager Integration with ASM Optimal Disk Placement. (11gR2)
• Enterprise Manager Support for ASM Cluster File System (ACFS). (11gR2)

Business Intelligence and Data Warehousing

• Allow Virtual Columns in the Primary Key or Foreign Key for Reference Partitioning. (11gR2)
• Analytic Functions 2.0. (11gR2)
• EXECUTE Privilege for DIRECTORY Objects. (11gR2)
• In-Memory Parallel Query. (11gR2)
• Minimal Effort Parallel Execution - Auto Degree of Parallelism (DOP) and Queuing. (11gR2)
• Preprocessing Data for ORACLE_LOADER Access Driver in External Tables. (11gR2)
• Recursive WITH Clause. (11gR2)
• Significant Performance Improvement of On-Commit Fast Refresh. (11gR2)

Oracle Warehouse Builder

• Database 11g now Integrated ETL, Analytics and Data Mining. (11gR1)
• Advanced Find Support in Mapping Editor. (11gR2)
• Copy and Paste of Operators and Attributes in Mapping Editor. (11gR2)
• Current Configuration Dropdown Box in Design Center Toolbar. (11gR2)
• Editor Menu Experts. (11gR2)
• Enhanced Support for Flat File Imports. (11gR2)
• Expression Editing in Operator Edit Dialog. (11gR2)
• Foldering and Spotlighting. (11gR2)
• Fusion Client Platform Integration. (11gR2)
• Grouping and Spotlighting of Objects in Mapping Editor. (11gR2)
• Guided Assistance. (11gR2)
• Improved Management of Locations Registered in Multiple Control Centers. (11gR2)
• Improved User Interface for Managing Locations. (11gR2)
• Key Lookup Operator Enhancements. (11gR2)
  • More efficient use of screen real estate.
  • Support for non-equality lookups.
  • Dynamic lookups, where the lookup table may be modified during the mapping execution.
• Mapping Debugger Enhancements. (11gR2)
  • Improved support for watch points and enabling and disabling of individual break points.
  • Support for user-defined type columns.
  • Enhanced support for numerous existing operators, such as VARRAY, EXPAND, and CONSTRUCT.
  • Support for key lookup and table function operators.
  • Support for correlated joins.o Improved cleanup of debugger-specific objects.
• Metadata Import from COBOL Copybooks. (11gR2)
• OMB*Plus Activity Type. (11gR2)
• Operator References Included in Generated PL/SQL Code. (11gR2)
• Quick Mapper. (11gR2)
• Repository Browser Changes. (11gR2)
• Simplified Oracle Warehouse Builder Repository Upgrades. (11gR2)
• Subquery in Join Operator. (11gR2)
• Support for Extracting Data From Tables Containing LONG Data Type. (11gR2)
• Table Functions. (11gR2)

Data Guard improvements

• Standby databases can now simultaneously be in read and recovery mode - so use it for running reports 24×7. (11gR1)
• Online upgrades: Test on standby and “roll” to production. (11gR1)
• Snapshot standby (create test databases). (11gR1)
• Incremental Backup on Physical Readable Physical Standby. (11gR1)
• Offload: Complete database and fast incremental backups. (11gR1)
• Logical standby databases now supports XML and CLOB datatypes as well as transparent data encryption. (11gR1)
• Compressed Table Support in Logical Standby Databases and Oracle LogMiner. (11gR2)
• Configurable Real-Time Query Apply Lag Limit. (11gR2)
• Integrated Support for Application Failover in a Data Guard Configuration. (11gR2)
• Support Up to 30 Standby Databases. (11gR2)

Oracle SecureFiles

• SecureFiles (also known as “FastFiles” in the 11g beta release) provide faster access to unstructured data than normal file systems. For example, write access to SecureFiles is faster than a standard Linux file system, while read access is about the same. In addition, it provides compression, encryption and data deduplication. (11gR1)
• LZO Support for SecureFiles (11gR2) – This allows Fast decompression - LZO is about 2 times faster than zlib, and Fast compression - LZO is about 3 times faster than zlib.
• SecureFiles Archive Manager. (11gR2)

Real Application Testing

Real Application Testing or RAT will make it easier to do upgrades, hardware replacements and operating system changes. RAT consists of two components:

• Database Replay - capture production workload and replay on different (test) environment. (11gR1)
• SQL Performance Analyzer - identifies SQL execution plan changes and performance regressions. (11gR1)

Other features

• Online application upgrades and “hot” patching (Hot patching is described in educational and marketing materials, but no actual hot patches exist, to date.) (11gR1)· Online table and index redefinition. (11gR1)
• Improved data compression. (11gR1 and 11gR2)
• “duality” between SQL and XML - users can embed XML within PL/SQL and vice versa. (11gR1)
• New binary XML data type, a new XML index & better XQuery support. (11gR1)
• Automated capture of fault diagnostics for faster fault resolution. (11gR1)
• Repair advisers to guide DBAs through the fault diagnosis and resolution process. (11gR1)
• SQL Developer is installed with the database server software (all editions)
• The Windows SQL*Plus GUI is deprecated. (11gR1)
• APEX is now shipped with the DB. (11gR1)
• Improvements to Oracle Scheduler (11gR2) like:
  • E-mail Notification.o File Watcher.
  • Multiple Destination Jobs.
  • Remote Database Jobs.
• Data Pump Legacy Mode. (11gR2)
• Complete IPv6 Support for JDBC Thin Clients. (11gR2)
• Complete IPv6 Support for JVM and Java Debuggers. (11gR2)
• IPv6 Support in Oracle Database. (11gR2)
• Enhanced Spatial Support (11gR2) like:
  • 3D Visualization Framework.
  • Network Data Model Enhancements.
  • New GeoRaster JAVA API.
  • Raster Reprojections and Ground Control Point-Based (GCP) Georeferencing.
  • Routing and Geocoding Enhancements.

Desupported features

The following features are desupported in 11g:

• Oracle export utility (exp). Imp is still supported for backwards compatibility.
• iSQLPlus not shipped anymore, you will need to use the SQL Developer instead.
• Oracle Ultra Search
• Java Development Kit (JDK) 1.4, Oracle recommends to use JDK 5.0, but JDK 1.5 is fully supported
• CTXXPATH Index, Oracle recommends to use XMLIndex Instead

For more information and to download 11gR2 please refer to:

http://www.oracle.com/technology/index.html and

http://www.oracle.com/pls/db112/portal.all_books

If after all this information you still thinking about not move to 11g, please think again a do a favor to yourself and your company, try it

Kind Regards,

Francisco Munoz Alvarez