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

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

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

Sometimes understanding the Oracle Database Editions is a little confuse, and on my opinion know all the options available in the market is something very important and fundamental for any DBA. The main idea of this post is to help you to understand a little bit more about all available options in the Oracle market, learn some key differences and functionalities of each Edition.

Understanding the Different Editions

All the time I receive questions regarding Oracle Database Editions like:

• What’s the difference between Standard Edition and Enterprise Edition?  
• How complicate is to upgrade a Standard Edition Database to Enterprise Edition and vice versa?   
• Can you explain me the most important Options and Packages available with Oracle?  
• Is Oracle Express Edition really free? What are the limitations and what it can do?

So many people don’t understand the diversity of Editions available with Oracle Databases, most of the time everyone associate it with the Enterprise Edition only, saying “Oracle is so expensive! Let’s look to other brands due that we can’t afford it!”, That’s a huge mistake, if you want the best available in the market you need to pay for it, but you need to understand all the options available for you by Oracle Corporation before take a precipitate and wrong decision, you always can find an edition that is affordable for your budget, and I can guarantee that you will not find the same performance and functionality at any other product in the market.

This is like buy a car, you can always dream to have a top brand vehicle, but you don’t need to buy the most expensive model if you only need a compact one, it will be more affordable and you know that you can trust in the quality of the product. The same happens with Oracle Databases. Oracle have available several editions of their Database product, all are the same product (internal code) but depending of the edition it can have some limitations or additional options to improve the performance, security, availability, and management of your 11g Database.

Oracle Database 11g is available in a choice of editions tailored to meet your business, personal, or IT needs, and also offers several extra options to enhance your Database capabilities depending of your applications requirements. Oracle have basically 5 different editions available for you, all are build using the same reliable database engine architecture and are compatible with each other, making this way an upgrade to be a very easy task to the DBA and this will also help to make your database grow at the same speed of your business.
The Editions available are: Express Edition, Personal Edition, Standard One Edition, Standard Edition and Enterprise Edition, now let’s take a quick look on each one:

• Oracle Express Edition (XE): This edition is an entry-level, small-footprint database based at the Oracle 10g release 2 Enterprise code that is free to develop, deploy, and distribute; due to it, this is an excellent option for developers, students, and very small organizations trying to initiate their journey in the Oracle world. Oracle XE is very easy to download and very simple to administrate, it can be installed on any host machine size, with any number of CPU’s and memory available, but it will have several limitations like: will storage only 4GB of data, will use up to 1GB of memory, and will use only 1 CPU in the host machine. Currently this edition is not supported by My Oracle Support (old Metalink), this means no patches available and support, but you can always refer to the OTN forums to ask your peers for any kind of help relate to this edition.
• Oracle Personal edition (PE): This edition supports single-user development and deployment environments that require full compatibility with Oracle Database Standard Edition One, Oracle Database Standard Edition, and Oracle Database Enterprise Edition. Oracle PE includes all of the components, options and functionalities that are included with Enterprise Edition with the exception of the Oracle Real Application Clusters (RAC) option. The negative side of this edition is that this is only available for Windows environments (Windows 2000, Windows NT, Windows XP, Windows Vista, and Windows Server 2003/32-bit and 64-bit versions), and the Management Packs are not included.
• Oracle Standard Edition One (SEO): Delivers an unprecedented ease to use, power, price/performance for workgroups, department-level, and Web applications. This edition is highly recommended to be used from single-server environments for small business to any highly distributed branch environments. Oracle Database SEO includes all the facilities necessary to build any business-critical applications.
• Oracle Standard Edition (SE): Offers a low cost alternative for small/medium business or departmental applications that want to have the power of Oracle, this Edition has all the benefits of Oracle SEO plus support for larger machines and clustering of services with Real Application Clusters (Free in this Edition); RAC was not included in previous Standard Edition releases prior to Oracle Database version 10g.
• Oracle Enterprise Edition (EE): It’s the top of the Oracle Editions line, provides the best in performance, availability, scalability, and security required for mission-critical applications such as high-volume online transaction processing (OLTP) applications, query-intensive data warehouses, and demanding Internet applications. Oracle Database EE contains all of the components of Oracle Database, and can be further enhanced with the purchase of several available options and packs.

Now let’s take a closer look to all availabilities and restrictions of each Oracle Edition:

Limitations/ Availability	Express Edition	Personal Edition	Standard Edition One	Standard Edition	Enterprise Edition
Number of CPU/Sockets	1 CPU	No Limit	2 Sockets	4 Sockets	No Limit
RAM	1GB	OS Maximum	OS Maximum	OS Maximum	OS Maximum
Max. Number of Users	No Limit	1	No Limit	No Limit	No Limit
Databases per Host	1	No Limit	No Limit	No Limit	No Limit
Minimum User License Requirements	Not Apply	Not Apply	5 Named Users Plus	5 Named Users Plus	25 Named Users Plus per CPU
Database Size	4GB	No Limit	No Limit	No Limit	No Limit
Windows	Available	Available	Available	Available	Available
Linux	Available	No Available	Available	Available	Available
Unix	No Available	No Available	Available	Available	Available
64-Bit	No Available	Available	Available	Available	Available

Now you have a clear idea of all available Editions of Oracle in the market, in the next post I’ll go thru the most important options and packs available to enhance your database to achieve all your personal or company goals.

Cheers,

Francisco Munoz Alvarez

By Francisco Munoz Alvarez

Before you start with an installation of Grid Control, I’ll recommend you to go thru the documentation to understand all the minimum requirements and the installation process. For reference purpose, I’ll resume some important points of it here.

Let’s start taking  a close look in the components of the Grid Control  and some important information:

Component	Definition
Grid Control Console	From the Grid Control console, you can monitor and administer your entire computing environment from one location on the network. All the services within your enterprise, including hosts, databases, listeners, application servers, Oracle Collaboration Suite applications, and Web applications are easily managed from one central location (console).
Management Agent	The Management Agent is a process that is deployed on each monitored host. It is responsible for monitoring all targets on the host, for communicating that information to the middle-tier Management Service, and for managing and maintaining the host and its targets.
Management Service	The Management Service is a J2EE Web application that renders the user interface for the Grid Control console. It works with all Management Agents to process monitoring and jobs information, and uses the Management Repository to store data.
Management Repository	The Management Repository consists of objects such as database jobs, packages, procedures, views, and two tablespaces in Oracle Database that contain all available information about administrators, targets, and applications managed within Enterprise Manager.

The Management Service uploads the monitoring data received from the Management Agents to the Management Repository. The Management Repository then organizes all data collected, so that it can be retrieved by the Management Service and displayed in the Grid Control console, making our life easy. Because all data is stored in the Management Repository, it can be shared between any number of administrators accessing the Grid Control console.

Licensing Information

Although the installation media in your media pack contain many Oracle components, you are permitted to use only those components for which you have purchased licenses. Oracle Support Services does not provide support for components for which licenses have not been purchased. For more information please refer to Oracle Enterprise Manager Licensing Information.

Oracle Directory

If you choose to install Enterprise Manager Grid Control using a new database on a computer with no other Oracle software installed, Oracle Universal Installer creates an Oracle base directory for you. If Oracle software is already installed, then one or more Oracle base directories already exist. In the latter case, you must specify the Oracle base directory into which you want to install Oracle Database.

You are not required to create an Oracle base directory before installation, but you can do so if desired. You can set the ORACLE_BASE environment directory to point to this directory, which the Oracle Universal Installer will recognize.

Multiple Oracle Home Support

Enterprise Manager is installed on multiple Oracle homes within the Oracle base directory. This means that a typical Enterprise Manager Grid Control installation creates three Oracle homes in different Oracle home directories. For example, oms10g, db10g, and agent10g.

Permissions Required for Executing UTL_FILE

The management audit log package of the scheme owner uses the UTIL package. For this package to function properly, the Enterprise Manager schema user (for example, sysman) must have permissions to execute this package.

To grant permissions, run this command (where sysman is the schema user):

grant execute on utl_file to sysman;

Enterprise Manager 10g Grid Control Certification Matrix

Before you download the software, Oracle recommends you to read the Oracle Enterprise Manager 10g Grid Control Certification matrix. The certification matrix shows the operating systems and browser versions on which Enterprise Manager Grid Control and Management Agent are certified.

The Enterprise Manager 10g Grid Control Certification matrix is available on Oracle Metalink at:

https://metalink.oracle.com/

Login and select the Certify tab. On the Certify page, click View Certifications by Product and select Enterprise Manager 10g Grid Control, and then click Submit.

Preinstallation Requirements for Enterprise Manager

For small environments (100 monitored targets):

• 1 Host with 1 CPU (3GHz) 2 GB RAM and 2 GB Space to install the Oracle Management Service.
• 1 Host with 1 COU (3GHz) 2 GB RAM and 10 GB space to the Oracle Management Repository

Note: On small environments it’s ok to share the same host for the Oracle Management Service and Repository.

• 400 MB hard disk space to install the Oracle Management Agent for all Unix platforms and 500 MB for Windows,

For information regarding Preinstallation requirements, please refer to the OEM Documentation here.

Certified Enterprise Manager Targets

This is a resume of the list, for full list, please refer to the documentation.

Supported Targets	Release
Oracle Application Server	9.0.4.2 and later patchsets 10.1.2.0.0 (Phase 1) 10.1.0.2.0.1 (SEONE) 10.1.2.0.2 (Phase 2) 10.2.0.2.1 Patchset 10.1.3 (Standalone OC4J)
Oracle Database, Listener	8.1.7.4 9.0.1.5 9.2.0.7 and later patchsets 10.1.0.4 and later patchsets 10.2
Oracle Real Application Clusters Database	9.2.0.6 10.1.0.4 10.1.0.5 10.2
Oracle Collaboration Suite	9.0.4.2 and later 10.1.1

Note: Oracle recommends that the target host on which you are installing the Management Agent have a static IP address and not DHCP.

Reference material: Enterprise Manager Grid Control Installation and Basic Configuration Guide.

 By Francisco Munoz Alvarez

TIPS USING NOLOGGING MODE 

DIRECT PATH INSERT 

To use Direct Path Insert use the /*+ APPEND */ hint as follow:

• INSERT /*+ APPEND */ into … SELECT …

When direct path insert is used oracle does the following:

• Format the data to be inserted as oracle blocks.

• Insert the blocks above the High Water Mark (HWM)

• When commit takes place the HWM is moved to the new place (The process is done bypassing the buffer cache).

It is clear that direct load is useful for bulk inserts. Using it to insert few hundred records at a time can have bad effect on space and performance.

It is very important to understand how Direct Path Inserts affects redo generation. As mentioned above it does not affect indexes but it is affected by the following factors:

• The database Archivelog mode.
• Using the /*+ APPEND */ hint.
• The LOGGING mode of the table.
• The FORCE LOGGING mode of the database (from 9i R2).

If the database is in FORCE LOGGING mode then Oracle will treat the table as if it was in LOGGING mode regardless of its mode. To find out if the database is in FORCED LOGGING or not run:

• select FORCE_LOGGING from v$database ;

If the /*+ APPEND */ Hint is not used then the insertion will generate the normal amount of redo regardless of the other factors.

This table will show the relation between ARCHIVELOG mode and having the table in LOGGING mode when the /*+ APPEND */ hint is used. This does not include index and  data dictionary changes redo generation.

LOGGING MODE	ARCHIVELOG	NOARCHIVELOG
LOGGING	Redo	No Redo
NOLOGGING	No Redo	No Redo

For Bulk DML  

Bulk Inserts 

To load bulk data using Direct Path.

• set table in nologging mode. Alter table table_name nologging;
• alter index index_name unusable ;
• alter session set skip_unusable_indexes=true ;(*)
•  Insert /*+ APPEND */ into table_name select …
•  Alter index index_name rebuild nologging;
• Alter table table_name logging ;
• Alter index index_name logging ;
• Backup the data.

(*)skip_unusable_indexes is an instance initialization parameter in 10g and defaulted to true. Before 10g, skip_unusable_indexes needs to be set in a session or the user will get an error. It is a good practice to set it in a session, regardless of the database version, when the above is done.

There is no direct way (at the time of writing this document) of reducing redo generation for bulk update and delete. The user needs to reduce the workload on the database.

Bulk Delete 

1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); The NOLOGGING comes after the TABLE_NAME not at the end of the statement.
2. Insert /*+ Append */ into new_table select the records you want to keep from current_table.
3. Create the indexes on the new table with NOLOGGING (*)
4. Create constraints, grants etc.
5. Drop current_table.
6. Rename new_table to current.
7. Alter new_table and indexes logging.
8. Backup the data.

(*) If the data left is so small or there are a lot of dependencies on the table (views, procedures, functions) the following steps can be used instead of 3-6 above:

1. Disable constrains on current_table;
2. Truncate current_table;
3. make indexes unusable;
4. alter current table NOLOGGING ;
5. Insert /*+ APPEND */ into current_table select * from new_table ;
6. commit;
7. rebuild indexes with NOLOGGING;
8. enable constraints
9. Put current table and indexes in LOGGING mode
10. backup the data
11. drop table new_table;

Bulk Update 

Follow the steps for bulk Delete but integrate the update within the select statement. Lets say that you want to update the value column in the goods table by increasing it by 10% the statement will be like:

1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); (The nologging comes after the table_name, not at the end of the statement.).
2.  Insert /*+ Append */ into new_table select (update statement eg: col1, col2* 1.1,…)
3. Create the indexes on the new table with NOLOGGING (*)
4. Create constraints, grants etc.
5. Drop current_table.
6. Rename new_table to current.
7. Alter new_table and indexes logging.
8. Backup the data.

Backup and Nologging 

If required, it is possible that the data loaded using NOLOGGING can be loaded again. If the database crashed before backing up the new data then this data can not be recovered.

Here are the two scenarios of backing up:

Export (exp or expdp) 

This method will allow you to recover the loaded data up to the point the export was taken but not later.

For customers using 10g Oracle Streams, there is also the option of using Data Pump Export and Import Direct Path API. For more details please refer to the Utilities Guide

Hot Backup 

In order to recover any additional data or modification to the table you bulk inserted into using NOLOGGING the least you need to do is a hot backup of that tablespace. Remember you still generate redo for DML on the table when it is in NOLOGGING mode but you are strongly advised to put it in LOGGING mode in case you run one of the operations mentioned in the Disabling Logging section.

Wait for next part, I will talk about Redo Log I/O related wait events..  

 

This is the first of many workshops I’ll start to post on my blog, at the end of each one I’ll post the full paper and some videos teaching step by step each workshop.

Let’s start with the first one

Part I - Creating the Virtual Environment

Project Name	Oracle Rac5/2008
Author	Francisco Munoz Alvarez
Software Used	Oracle Enterprise Linux 5 WMware Workstation 6.0.4 build 93057 Oracle 11.1.0.6 Database and Clusterware Software
Date	30/10/2008

 

The idea of this workshop, it’s to guide you on how to create a RAC Installation using VMware Workstation 6, Oracle Enterprise Linux 5, and Oracle 11g.

This installation should never be used for Production or Development purposes. This installation was created for educational purpose only, and is extremely helpful to learn and understand how Oracle RAC works and if you do not have access to traditional hardware resources.

1. Project Hardware and Software Overview

 

Host Machine
Machine Name	Francisco-PC
Operational System	Windows Vista Business SP1
VMware Version	WMware Workstation 6.0.4 build 93057
Host Machine	HP Pavilion 6730b
Memory	3 GB
External Hard Drive	600 GB
Processor	Intel Core Duo P8400

 

Virtual Machine #1 - Rac1 Operational System Oracle Enterprise Linux 5 Machine Name rac1 Database Version 11.1.0.6 Instance Name Test1 Public Name/IP - (eth0) rac1.dbisonline.com - 192.168.2.8 Interconnect Name/IP - (eth1) rac1-vip.dbisonline.com -10.10.10.11 Memory 860 MB CPU 2 CPUs Hard Drive 18 GB Location C:\Users\francisco\Documents\labs-virtual-machines\RAC\rac1

Virtual Machine #2 - Rac2 Operational System Oracle Enterprise Linux 5 Machine Name rac2 Database Version 11.1.0.6 Instance Name Test2 Public Name/IP - (eth0) rac2.dbisonline.com - 192.168.2.9 Interconnect Name/IP - (eth1) rac2-vip.dbisonline.com -10.10.10.12 Memory 860 MB CPU 2 CPUs Hard Drive 18 GB Location C:\Users\francisco\Documents\labs-virtual-machines\RAC\rac2

VMware File	Virtual Disks	Virtual Device Node	Size (GB)	Description
RAC\rac1\localdisk-flat.vmdk	/dev/sda1 /dev/sda2	SCSI 0:0	8	/ Mount Point Swap space Oracle Binaries
RAC\shared_storage\ocfs2-flat.vmdk	/dev/sdb1	SCSI 1:0	1	OCFS2 Disk
RAC\shared_storage\asm1-flat.vmdk	/dev/sdc1	SCSI 1:1	3	ASM disk group 1
RAC\shared_storage\asm2-flat.vmdk	/dev/sdd1	SCSI 1:2	3	ASM disk group 2
RAC\shared_storage\asm3-flat.vmdk	/dev/sde1	SCSI 1:3	3	ASM Flash Recovery Area

 

2. The Software

You can download all software you will need to this project at the following urls:

• https://www.vmware.com/tryvmware/?p=workstation-w
• http://www.oracle.com/technology/software/products/database/index.html
• http://edelivery.oracle.com/linux
• http://oss.oracle.com/projects/ocfs2

 

 

3. Virtual Machine Rac1 Setup

 

We are going to create 2 virtual machines (Rac1 and Rac2) that will share 5 disks (asm1, asm2, asm3, ocfs2 and local disk).

First we will create the windows folders to have the virtual machines and the shared storage.

• C:\Users\francisco\Documents\labs-virtual-machines\RAC\rac1
• C:\Users\francisco\Documents\labs-virtual-machines\RAC\rac2
• C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage

1. Then Double-click on the VMware Icon your desktop to bring up the application:

2.   Click on the New Virtual Machine Icon.

3. New Virtual Machine Wizard: Click on Next.

4. In the Select the Appropriate Configuration Window Select Custom and then press next.

5. Press Next in the Compatibility Window.

6. Select a Guest Operating System:

• a. Guest operating system: Select Linux.
• b. Version: Select Red Hat Enterprise Linux 5.

c. Press Next

7. Name the Virtual Machine:

• a. Virtual machine name: Enter rac1
• b. Location: Enter C:\Users\francisco\Documents\labs-virtual-machines\RAC\rac1\
• c. Press Next

8. Processor Configuration

• a. Number of processors: Select Two
• b. Press Next

9. Memory for the Virtual Machine

• a. Memory: Enter 860 MB
• b. Press Next

10. Network Type

• a. Network Connection: Select Use bridged networking
• b. Press Next

11. Select I/O Adapter Types

• a. SCSI Adapters: Select LSI Logic
• b. Press Next

12. Select a Disk:

• a. Disk: Select create a new virtual disk.
• b. Press Next

13. Select a Disk Type:

• a. Virtual Disk Type: Select SCSI (Recommended).
• b. Press Next

14. Specify Disk Capacity:

• a. Disk capacity: Enter 8GB and Select Allocate all disk space now.
• b. Press Next

15. Specify Disk File:

• a. Disk file: Enter localdisk
• b. Click on Finish.

16. Now we will create the rest of shared disks:

• a. VMware Server Console: Click on Edit virtual machine settings.

17. Add Hardware Wizard: Click on Next.

18. On Hardware Type:

• a. Hardware types: Select Hard Disk
• b. Then click on Next

19. Select a Disk:

• a. Disk: Select Create a new virtual disk.
• b. Then Click Next

20. Select a Disk Type:

• a. Virtual Disk Type: Select SCSI (Recommended).
• b. Mode: Select Independent
• c. Mode: Select Persistent
• d. Then Click Next

21. Specify Disk File:

• a. Disk file: Enter “C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\ocfs2.vmdk”
• b. Then Click Next

22. Specify Disk Capacity:

• a. Disk capacity: Enter “1.0GB”
• b. Select Allocate all disk space now.
• c. Then Click Finish

Repeat steps 16 to 22 to create all the other virtual SCSI hard disks:

• asm1.vmdk, C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm1.vmdk (3GB),
• asm2.vmdk, C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm2.vmdk (3GB),
• asm3.vmdk, C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm3.vmdk (3GB).

Then when finish you will have something like this:

Add an additional virtual network card for the private interconnects:

• a. VMware Server Console: Click on Edit virtual machine settings.
• b. Hardware types: Ethernet Adapter.
• c. Then Click Next

On Network Type:

• a. On Network Connection Select Bridget: Connect directly to the physical network
• b. Click on Finish.

Then Select Floppy then:

• a. click on Remove
• b. Then OK.

Like you see the hard drives are showing:

• Hard Disk (SCSI 0:0) 8 GB
• Hard Disk (SCSI 0:1) 1 GB
• Hard Disk (SCSI 0:2) 3 GB
• Hard Disk (SCSI 0:3) 3 GB
• Hard Disk (SCSI 0:4) 3 GB

Now we need to change them to look like this:

• Hard Disk (SCSI 0:0) 8 GB
• Hard Disk (SCSI 1:0) 1 GB
• Hard Disk (SCSI 1:1) 3 GB
• Hard Disk (SCSI 1:2) 3 GB
• Hard Disk (SCSI 1:3) 3 GB

To make this possible we will give double-click over the hard disk showing SCSI 0:1

• a. Then Click on Advanced

Then Change Virtual Device node

• a. From SCSI 0:1 Hard Disk (SCSI 0:1)
• b To SCSI 1:0
• c. Then Click OK
• d. Then Click OK one more time

Repeat these steps to change:

• Hard Disk (SCSI 0:2) 3 GB to SCSI 1:1
• Hard Disk (SCSI 0:3) 3 GB to SCSI 1:2
• Hard Disk (SCSI 0:4) 3 GB to SCSI 1:3

Now to finish this virtual machine, edit the file C:\Users\francisco\Documents\labs-virtual-machines\RAC\Red Hat Enterprise Linux 4.vmx.

               a. Then add the following lines to it like the example bellow:

disk.locking = "FALSE"
diskLib.dataCacheMaxSize = "0"
scsi1.sharedBus = "virtual"

b.

config.version = "8"

virtualHW.version = "6"

numvcpus = "2"

scsi0.present = "TRUE"

scsi0.virtualDev = "lsilogic"

memsize = "860"

scsi0:0.present = "TRUE"

scsi0:0.fileName = "localdisk.vmdk"

ide1:0.present = "TRUE"

ide1:0.fileName = "auto detect"

ide1:0.deviceType = "cdrom-raw"

floppy0.autodetect = "TRUE"

ethernet0.present = "TRUE"

ethernet0.wakeOnPcktRcv = "FALSE"

usb.present = "TRUE"

ehci.present = "TRUE"

sound.present = "TRUE"

sound.fileName = "-1"

sound.autodetect = "TRUE"

svga.autodetect = "TRUE"

pciBridge0.present = "TRUE"

mks.keyboardFilter = "allow"

displayName = "rac1"

guestOS = "rhel5"

nvram = "Red Hat Enterprise Linux 4.nvram"

deploymentPlatform = "windows"

virtualHW.productCompatibility = "hosted"

tools.upgrade.policy = "useGlobal"

disk.locking = "FALSE"

diskLib.dataCacheMaxSize = "0"

scsi1.sharedBus = "virtual"

ide1:0.autodetect = "TRUE"

floppy0.fileName = "A:"

extendedConfigFile = "Red Hat Enterprise Linux 4.vmxf"

scsi0:1.present = "FALSE"

scsi0:1.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\ocfs2"

scsi0:1.mode = "independent-persistent"

scsi0:2.present = "FALSE"

scsi0:2.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm1.vmdk"

scsi0:2.mode = "independent-persistent"

scsi0:3.present = "FALSE"

scsi0:3.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm2.vmdk"

scsi0:3.mode = "independent-persistent"

scsi0:4.present = "FALSE"

scsi0:4.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm3.vmdk"

scsi0:4.mode = "independent-persistent"

floppy0.present = "FALSE"

ethernet1.present = "TRUE"

ethernet1.wakeOnPcktRcv = "FALSE"

scsi1.present = "TRUE"

scsi1.virtualDev = "lsilogic"

scsi1:0.present = "TRUE"

scsi1:0.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\ocfs2"

scsi1:0.mode = "independent-persistent"

scsi1:1.present = "TRUE"

scsi1:1.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm1.vmdk"

scsi1:1.mode = "independent-persistent"

scsi1:2.present = "TRUE"

scsi1:2.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm2.vmdk"

scsi1:2.mode = "independent-persistent"

scsi1:3.present = "TRUE"

scsi1:3.fileName = "C:\Users\francisco\Documents\labs-virtual-machines\RAC\shared_storage\asm3.vmdk"

scsi1:3.mode = "independent-persistent"

 

Ok, let’s finish here for today folks. Tomorrow I’ll explain how to create the second node and to install the Oracle Enterprise Linux 5

Cheers,

Francisco Munoz Alvarez 

This procedure can be used for single instance or RAC (9i to 11g):

In the 32 bit source server :

Start making a full backup of the database including all archivelogs:

RMAN> run  {  

allocate channel c1 type disk;  

allocate channel c2 type disk;  

backup database plus archivelog;  

backup current controlfile;  }

In the target 64 bit server:1) Copy backup files from source server
2) Create the pfile or copy the pfile fromsourceserver
3) Startup nomount the new instance.
4) Connect to rman and set the dbid:

$ rman target / nocatalog RMAN> set dbid=<dbid from the 32 server>;

5) Restore the controlfile:

RMAN> restore controlfile from ‘/disk1/backup_32/backup_piece_name’;

RMAN> alter database mount;

RMAN> restore database;

6) Identify the archivelog till you want to do the recovery and check if you have all the backups you need to do it and run:

RMAN> run {

set until sequence xxxx;

recover database;

}

RMAN> exit

7) Now Migrating to 64 bit:

SQL> recover database until cancel using backup controlfile;

cancel

SQL> alter database open resetlogs migrate;

SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> shutdown immediate;

SQL> startup

Note: Also please take a look in other methods in the following Metalink Note:

• Migration of Oracle Instances Across OS Platforms - Doc ID: Note:733205.1

Cheers,

Francisco Munoz Alvarez