New features in Oracle 11g Database - Virtual Columns

5. Virtual Columns
Virtual Columns are either created at the time of creating or modifying a table. Virtual Columns are defined by expressions or functions, which will be included as part of table structure and hence its metadata. Virtual Columns are like Views, they don't use additional disk space for its data.

U1> CREATE TABLE student_mark(
  2     stud_id NUMBER(5),
  3     stud_name VARCHAR2(15),
  4     subj1_mark NUMBER(3),
  5     subj2_mark NUMBER(3),
  6     subj3_mark NUMBER(3),
  7     tot_mark AS (subj1_mark + subj2_mark + subj3_mark));

Table created.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER

U1> DROP TABLE student_mark;

Table dropped.

U1> CREATE TABLE student_mark(
  2     stud_id NUMBER(5),
  3     stud_name VARCHAR2(15),
  4     subj1_mark NUMBER(3),
  5     subj2_mark NUMBER(3),
  6     subj3_mark NUMBER(3),
  7     tot_mark NUMBER(4) AS (subj1_mark + subj2_mark + subj3_mark));

Table created.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)

U1> INSERT
  2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
  3     VALUES(10, 'Rose', 75, 45, 66);

1 row created.

U1> INSERT
  2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
  3     VALUES(20, 'Bose', 45, 79, 88);

1 row created.

U1> SELECT *
  2     FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK
---------- --------------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186
        20 Bose                    45         79         88        212

U1> ALTER TABLE student_mark
  2     ADD avg_mark NUMBER(3) AS (((subj1_mark + subj2_mark + subj3_mark) / 3));

Table altered.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)
 AVG_MARK                         NUMBER(3)

U1> INSERT
  2     INTO student_mark(stud_id, stud_name, subj1_mark, subj2_mark, subj3_mark)
  3     VALUES(30, 'Jose', 63, 68, 72);

1 row created.

U1> SELECT *
  2 FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK   AVG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

U1> ALTER TABLE student_mark DROP COLUMN avg_mark;

Table altered.

Attaching a user defined function with a virtual column:-

U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
  2     RETURN NUMBER deterministic IS subj_avg NUMBER(3);
  3     BEGIN
  4         SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
  5         INTO subj_avg
  6         FROM student_mark
  7         WHERE stud_id = stud_no;
  8         RETURN(subj_avg);
  9     END;
 10 /

Function created.

U1> ALTER TABLE student_mark
 2     ADD avrg_mark AS (get_avg(stud_id));

Table altered.

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)
 AVRG_MARK                        NUMBER


U1> SELECT * FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK  AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

Lets see what happens when we drop the function:-

U1> DROP FUNCTION get_avg;

Function dropped.

U1> SELECT *
  2 FROM student_mark;
SELECT *
*
ERROR at line 1:
ORA-00904: "U1"."GET_AVG": invalid identifier

U1> DESCRIBE student_mark
 Name                    Null?    Type
 ----------------------- -------- ----------------
 STUD_ID                          NUMBER(5)
 STUD_NAME                        VARCHAR2(15)
 SUBJ1_MARK                       NUMBER(3)
 SUBJ2_MARK                       NUMBER(3)
 SUBJ3_MARK                       NUMBER(3)
 TOT_MARK                         NUMBER(4)
 AVRG_MARK                        NUMBER

U1> CREATE OR REPLACE FUNCTION get_avg(stud_no IN NUMBER)
  2     RETURN NUMBER deterministic IS subj_avg NUMBER(3);
  3     BEGIN
  4         SELECT((subj1_mark + subj2_mark + subj3_mark) / 3)
  5         INTO subj_avg
  6         FROM student_mark
  7         WHERE stud_id = stud_no;
  8         RETURN(subj_avg);
  9     END;
 10 /

Function created.

U1> SELECT * FROM student_mark;

STUD_ID    STUD_NAME       SUBJ1_MARK SUBJ2_MARK SUBJ3_MARK   TOT_MARK  AVRG_MARK
---------- --------------- ---------- ---------- ---------- ---------- ----------
        10 Rose                    75         45         66        186         62
        20 Bose                    45         79         88        212         71
        30 Jose                    63         68         72        203         68

Partition on Virtual Columns

This enables to partition the table based on the expression or function that were used to create a virtual column. Lets take the below example:-


U1> CREATE TABLE phone_cust (
  2     cust_id NUMBER(5),
  3     name VARCHAR2(15),
  4     street VARCHAR2(15),
  5     city VARCHAR2(10),
  6     zip NUMBER(5),
  7     phone NUMBER(10),
  8     area_code AS (SUBSTR(phone,1,3)||'-'||SUBSTR(phone,4,3)));

Table created.

U1> DESCRIBE phone_cust
 Name                    Null? Type
 ----------------------- -------- ----------------
 CUST_ID                          NUMBER(5)
 NAME                             VARCHAR2(15)
 STREET                           VARCHAR2(15)
 CITY                             VARCHAR2(10)
 ZIP                              NUMBER(5)
 PHONE                            NUMBER(10)
 AREA_CODE                        VARCHAR2(7)

U1> ALTER TABLE phone_cust modify area_code AS (SUBSTR(phone, 1, 3));

Table altered.

U1> DESCRIBE phone_cust
 Name                    Null? Type
 ----------------------- -------- ----------------
 CUST_ID                          NUMBER(5)
 NAME                             VARCHAR2(15)
 STREET                           VARCHAR2(15)
 CITY                             VARCHAR2(10)
 ZIP                              NUMBER(5)
 PHONE                            NUMBER(10)
 AREA_CODE                        VARCHAR2(3)

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 John            660 Boas Street Harrisburg      17102 7171234567 717
        20 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717

U1> DROP TABLE phone_cust;

Table dropped.

U1> CREATE TABLE phone_cust(
  2     cust_id NUMBER(5),
  3     name VARCHAR2(15),
  4     street VARCHAR2(15),
  5     city VARCHAR2(10),
  6     zip NUMBER(5),
  7     phone NUMBER(10),
  8     area_code AS (SUBSTR(phone, 1, 3)))
  9   PARTITION BY range(area_code)(
 10   PARTITION p1 VALUES less than(500) TABLESPACE encr_tbs_1,
 11   PARTITION p2 VALUES less than(999) TABLESPACE encr_tbs_2)
 12   enable ROW movement;

Table created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(10, 'Chel', '456 Walnut St', 'Philly', '19139', 2154567891);

1 row created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(20, 'John', '660 Boas Street', 'Harrisburg', '17102', 7171234567);

1 row created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(30, 'Raju', '123 Mkt Street', 'Lemoyne', '17011', 7174567891);

1 row created.

U1> INSERT
  2  INTO phone_cust(cust_id, name, street, city, zip, phone)
  3  VALUES(40, 'Appu', '3 Bridge Trc', 'Woodbridge', '07085', 7324567891);

1 row created.

U1> INSERT
  2 INTO phone_cust(cust_id, name, street, city, zip, phone)
  3 VALUES(50, 'Babu', '5 1st Ave', 'Manhattan', '10037', 2124567891);

1 row created.

U1> INSERT
  2 INTO phone_cust(cust_id, name, street, city, zip, phone)
  3 VALUES(60, 'Rangasamy', '12 Circle Dr', 'Redlands', '92415', 9094567891);

1 row created.

U1> commit;

Commit complete.

U1> DESCRIBE phone_cust
 Name                    Null? Type
 ----------------------- -------- ----------------
 CUST_ID                          NUMBER(5)
 NAME                             VARCHAR2(15)
 STREET                           VARCHAR2(15)
 CITY                             VARCHAR2(10)
 ZIP                              NUMBER(5)
 PHONE                            NUMBER(10)
 AREA_CODE                        VARCHAR2(3)

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 7171234567 717
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

U1> SELECT * FROM phone_cust PARTITION(p1);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212

U1> SELECT * FROM phone_cust PARTITION(p2);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        20 John            660 Boas Street Harrisburg      17102 7171234567 717
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

U1> UPDATE phone_cust SET phone = 2121234567 WHERE cust_id = 20;

1 row updated.

U1> commit;

Commit complete.

U1> SELECT * FROM phone_cust;

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 2121234567 212
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909

6 rows selected.

U1> SELECT * FROM phone_cust PARTITION(p1);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        10 Chel            456 Walnut St   Philly          19139 2154567891 215
        50 Babu            5 1st Ave       Manhattan       10037 2124567891 212
        20 John            660 Boas Street Harrisburg      17102 2121234567 212

U1> SELECT * FROM phone_cust PARTITION(p2);

   CUST_ID NAME            STREET          CITY              ZIP      PHONE ARE
---------- --------------- --------------- ---------- ---------- ---------- ---
        30 Raju            123 Mkt Street  Lemoyne         17011 7174567891 717
        40 Appu            3 Bridge Trc    Woodbridge       7085 7324567891 732
        60 Rangasamy       12 Circle Dr    Redlands        92415 9094567891 909
 

New features in Oracle 11g Database

What's new in an Oracle 11g Database?

1. Lets check some INIT parameters first:-

MEMORY_TARGET: Specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, increasing or decreasing SGA and PGA as needed.

MEMORY_MAX_TARGET: Maximum value that MEMORY_TARGET initialization parameter can use.

DIAGNOSTIC_DEST: Diagnostics for each database instance are located in this dedicated directory

2. Password is now Case Sensitive.

SQL> create user u1 identified by U1 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u1;

Grant succeeded.

SQL> create user u2 identified by u2 default tablespace users quota 50m on users;

User created.

SQL> grant create session, create table, create procedure, create view to u2;

Grant succeeded.

SQL> connect u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> connect u1/U1
Connected.

SQL> connect u2/U2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect u2/u2
Connected.

3. READ-ONLY Table

SQL> connect u1/U1
Connected.

SQL> create table t1 (a number) ;

Table created.

SQL> begin
   2 for x in 1..20 loop
   3 insert into t1 values (x);
   4 end loop;
   5 commit;
   6 end;
   7 /

PL/SQL procedure successfully completed.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
NO

1 row selected.

SQL> alter table t1 READ ONLY;

Table altered.

SQL> select READ_ONLY from user_tables where table_name='T1';

REA
---
YES

1 row selected.

SQL> insert into t1 values (99);
insert into t1 values (99)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "U1"."T1"

SQL> alter table t1 READ WRITE;

Table altered.

SQL> insert into t1 values (99);

1 row created.

SQL> commit;

Commit complete.

4. Tablespace Encryption

As we all know, in an Oracle database all the data are finally stored in the datafiles, what if the datafiles containing sensitive data were stolen? there are always chances that these datafiles can either be disseminated or attached to a different database and the sensitive data can be exposed or revealed.

In order to overcome this, Oracle introduced Transparent Data Encryption (TDE), in Oracle 10g version, a column of a table can be encrypted based on a (master) key, and that key is not stored within the same database, but externally in an Oracle wallet.

Now in Oracle 11g, you can encrypt an entire tablespace, i.e., a whole table or all the tables in an encrypted tablespace using tablespace encryption.

Tablespace encryption is supported on all data stored in an encrypted tablespace including internal large objects (LOBs) such as BLOBs and CLOBs. But data stored externally, like BFILE data is not encrypted.

If a table is created with BFILE column in an encrypted tablespace, then this BFILE column will not be encrypted, but the rest of the table will be encrypted.

Configure wallet location by setting the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora:-

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\ora11\admin\ORA11\wallet)))

Tablespace Level
ENCRYPTION algorithms supported in Oracle11g are 3DES168, AES128, AES192 and AES256

SYS> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SYS> CREATE TABLESPACE ENCR_TBS_1
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_01.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SYS> CREATE TABLESPACE ENCR_TBS_2
   2     DATAFILE 'C:\ORACLE\ORADATA\ORA11\ENCR_TBS_02.DBF' SIZE 20M
   3     AUTOEXTEND ON NEXT 1M MAXSIZE 128M
   4     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
   5     SEGMENT SPACE MANAGEMENT AUTO
   6     ENCRYPTION USING 'AES256'
   7     DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> select * from V$ENCRYPTED_TABLESPACES;

       TS# ENCRYPT ENC
---------- ------- ---
         5 AES128  YES
         6 AES256  YES

SYS> select tablespace_name,encrypted from dba_tablespaces;

TABLESPACE_NAME     ENC
----------------    ---
SYSTEM              NO
SYSAUX              NO
UNDOTBS1            NO
TEMP                NO
USERS               NO
TEST_TS1            NO
ENCR_TBS_1          YES
ENCR_TBS_2          YES

8 rows selected.

SQL> desc DBA_ENCRYPTED_COLUMNS
Name                Null?    Type
------------------- -------- -------------
OWNER NOT           NULL     VARCHAR2(30)
TABLE_NAME          NOT NULL VARCHAR2(30)
COLUMN_NAME         NOT NULL VARCHAR2(30)
ENCRYPTION_ALG               VARCHAR2(29)
SALT                         VARCHAR2(3)

SQL> col owner for a5
SQL> col table_name for a10
SQL> col column_name for a14
SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME    ENCRYPTION_ALG                SAL
----- ---------- -------------- ----------------------------- ---
U1    CUST       CRED_CARD_NUM  AES 192 bits key              NO
U1    CUST_2     CRED_CARD_NUM  3 Key Triple DES 168 bits key NO

SYS> CREATE TABLE CUST_1(
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16))
   5     TABLESPACE ENCR_TBS_1;

Table created.

Above table is created in an encrypted tablespace, hence all data in this table is stored encrypted on the disk.

SYS> insert into CUST_1 values (30,'Kate',1234567812345678);

1 row created.

SYS> insert into CUST_1 values (40,'Deep',5678123456781234);

1 row created.

SYS> commit;

Commit complete.

SYS> col CRED_CARD_NUM for 9999999999999999
SYS> select * from CUST_1;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
30   Kate       1234567812345678
40   Deep       5678123456781234

SYS> alter system set encryption wallet close;

System altered.

C:\>cd C:\oracle\ora11\admin\ORA11\wallet

C:\oracle\ora11\admin\ORA11\wallet>C:\oracle\ora11\BIN\orapki wallet export -wallet . -dn "CN=oracle" -request oracle.req -pwd "b1b1"

Table Level
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "b1b1";

System altered.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1";
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1"
*
ERROR at line 1:
ORA-28354: wallet already open

A wallet is automatically opened when you set or reset the master encryption key. Once you set the encryption, you will be see a file created in the above DIRECTORY location.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE ;

System altered.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "b1b1";

System altered.

Creating a table with an encrypted column using the default AES192 algorithm

SQL> conn u1/u1
Connected.
SQL> create table CUST (
   2     cust_id VARCHAR2(4),
   3     cust_name VARCHAR2(10),
   4     cred_card_num NUMBER(16) ENCRYPT
   5     );

Table created.

U1> desc cust
Name                Null?    Type
------------------- -------- --------------------
CUST_ID                      VARCHAR2(4)
CUST_NAME                    VARCHAR2(10)
CRED_CARD_NUM                NUMBER(16) ENCRYPT

U1> insert into CUST values (10,'Tom',1234567812345678);

1 row created.

U1> insert into CUST values (20,'Sam',5678123456781234);

1 row created.

U1> commit;

Commit complete.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);
CREATE INDEX cust_card_idx ON CUST (cred_card_num)
*
ERROR at line 1:
ORA-28338: cannot encrypt indexed column(s) with salt

U1> ALTER TABLE CUST MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> CREATE INDEX cust_card_idx ON CUST (cred_card_num);

Index created.

U1> col CRED_CARD_NUM for 9999999999999999
U1> select * from CUST;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Creating a table with an encrypted column using NO SALT parameter

U1> create table CUST_2 (
  2     cust_id VARCHAR2(4),
  3     cust_name VARCHAR2(10),
  4     cred_card_num NUMBER(16) ENCRYPT NO SALT
  5     );

Table created.

U1> insert into CUST_2 select * from CUST;

2 rows created.

U1> commit;

Commit complete.

U1> select * from CUST_2;

CUST CUST_NAME  CRED_CARD_NUM
---- ---------- -----------------
10   Tom        1234567812345678
20   Sam        5678123456781234

Adding Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT SALT);

Table altered.

Removing Salt to an Encrypted Column

U1> ALTER TABLE CUST_2 MODIFY (cred_card_num ENCRYPT NO SALT);

Table altered.

U1> ALTER TABLE CUST_2 REKEY;

Table altered.

U1> ALTER TABLE CUST_2 REKEY USING '3DES168';

Table altered.

Creating a table with an encrypted column using 3DES168 algorithm

create table CUST_3 (
    cust_id VARCHAR2(4),
    cust_name VARCHAR2(10),
    cred_card_num NUMBER(16) ENCRYPT USING '3DES168'
    );

Adding and modifying encrypted columns

ALTER TABLE CUST_3 ADD (cust_addr VARCHAR2(15) ENCRYPT);

ALTER TABLE CUST_3 modify (cust_name ENCRYPT);

Disable Column Encryption

ALTER TABLE CUST_3 MODIFY (cust_name DECRYPT);

Manually Creating an Oracle 11g Database

Creating an Oracle 11g Database in Windows using command line.

Creating a 11g database is just the same as 10g.

INIT.ORA.Below are the contents of my init.ora:-

db_name='ORA11'
memory_target=1G
processes = 150
audit_file_dest='C:\oracle\ora11\admin\adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest=C:\oracle\ora11\admin\ORA11\flash_recovery_area
db_recovery_file_dest_size=2G
diagnostic_dest=C:\oracle\ora11\admin\ORA11\diagnostic_dest
dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ("C:\oracle\oradata\ORA11\ORA11_CONTROL1.ora", "C:\oracle\oradata\ORA11\ORA11_CONTROL2.ora")
compatible ='11.1.0'

Set Environment Variables:-

set ORACLE_SID=ORA11
set ORACLE_HOME=C:\oracle\ora11

Create Oracle service

C:\oracle\ora11\bin\oradim.exe -new -sid ORA11 -startmode m -INTPWD oracle -PFILE "C:\oracle\ora11\database\initORA11.ora"

Instance created.
Error while deleting value, OS Error = 2

Create Database

C:\>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Dec 10 15:25:47 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> connect sys/oracle as sysdba
Connected to an idle instance.

SQL> startup nomount pfile="C:\oracle\ora11\database\initORA11.ora"

ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes

CREATE DATABASE ORA11
USER SYS IDENTIFIED BY ORACLE
USER SYSTEM IDENTIFIED BY ORACLE
DATAFILE 'C:\oracle\oradata\ORA11\SYSTEM01.DBF' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE 'C:\oracle\oradata\ORA11\SYSAUX01.DAT' SIZE 120M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 2048M
DEFAULT TABLESPACE USERS DATAFILE 'C:\oracle\oradata\ORA11\USERS01.DBF' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\oracle\oradata\ORA11\TEMP01.DBF' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\oracle\oradata\ORA11\UNDOTBS01.DBF'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 'C:\oracle\oradata\ORA11\REDO01.LOG' SIZE 100M REUSE,
'C:\oracle\oradata\ORA11\REDO02.LOG' SIZE 100M REUSE,
'C:\oracle\oradata\ORA11\REDO03.LOG' SIZE 100MREUSE
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 1;

@C:\oracle\ora11\rdbms\admin\catalog.sql
@C:\oracle\ora11\rdbms\admin\catproc.sql

SQL> connect system/ORACLE as sysdba
Connected.

@C:\oracle\ora11\sqlplus\admin\pupbld.sql

SQL> select program from v$session;

PROGRAM
-----------------------
sqlplus.exe
ORACLE.EXE (q001)
ORACLE.EXE (CJQ0)
ORACLE.EXE (q000)
ORACLE.EXE (W000)
ORACLE.EXE (QMNC)
ORACLE.EXE (FBDA)
ORACLE.EXE (SMCO)
ORACLE.EXE (MMNL)
ORACLE.EXE (MMON)
ORACLE.EXE (RECO)
ORACLE.EXE (SMON)
ORACLE.EXE (CKPT)
ORACLE.EXE (LGWR)
ORACLE.EXE (DBW0)
ORACLE.EXE (MMAN)
ORACLE.EXE (DIA0)
ORACLE.EXE (PSP0)
ORACLE.EXE (DBRM)
ORACLE.EXE (DIAG)
ORACLE.EXE (VKTM)
ORACLE.EXE (PMON)

22 rows selected.

Drop Database

SQL> connect sys/oracle as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> drop DATABASE ;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

or

SQL> STARTUP mount RESTRICT EXCLUSIVE;
ORACLE instance started.

Total System Global Area 644468736 bytes
Fixed Size 1335108 bytes
Variable Size 171966652 bytes
Database Buffers 465567744 bytes
Redo Buffers 5599232 bytes
Database mounted.

SQL> drop DATABASE ;

Operation 203 succeeded.

Version

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

Following messages were noted in Alert.ora

Tue Dec 11 08:36:23 2007
Warning: chk_tab_prop - table doesn't exist :SYS.KUPC$DATAPUMP_QUETAB
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_S
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_I
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_H
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_T
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_G
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_P
Warning: chk_tab_prop - table doesn't exist :SYS.AQ$_KUPC$DATAPUMP_QUETAB_D
Tue Dec 11 08:39:11 2007
SERVER COMPONENT id=CATPROC: timestamp=2007-12-11 08:39:11

Failure to Extend Rollback Segment

Environment: Oracle 9.2.0.7 EE, 32bit, Windows 2003 Enterprise
Server.

Problem: Transaction (bulk load) hangs and getting below error in the alert.log

Thread 1 advanced to log sequence 1234
Current log# 1 seq# 1234 mem# 0: D:\ORACLE\ORADATA\LOG1.ORA
Wed Sep 25 21:06:06 2007
Failure to extend rollback segment 19 because of 1000 conditionFULL status of
rollback segment 19 set.
Wed Sep 25 21:06:20 2007
SMON: FULL status of rollback segment 19 cleared.

I have seen various reasons and scenarios (like insufficient disk space for RBS/UNDO tablespace/datafiles to grow, or hitting maxsize limitation for datafiles, max extents reached, ORA-1555 the famous "snapshot too old" error, etc) causing failure of a rollback (undo) segment. But this is the first time I came across a RBS failure which is slightly different the usual.

The database is in AUTOMATIC undo management mode

NAME                    TYPE        VALUE
----------------------- ----------- ---------
undo_management         string      AUTO
undo_retention          integer     10800
undo_suppress_errors    boolean     FALSE
undo_tablespace         string      UNDOTBS1


Solution: After trying various options, attempts and looking the alert log message under the microscope, I came to know that the reason for the error is ORA-01000 (as the error in the alert.log says "because of 1000", so DBAs are expected to assume it is ORA-01000 error, go figure!). Here is what Oracle documentation say about this error:

ORA-01000 maximum open cursors exceeded

Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of
cursors per user.

Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle.

After changing the OPEN_CURSORS parameter (from 300) to 2000 and restarting the database, the bulk load went fine.

Did anyone had this problem? welcome to share your thoughts..

Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines

Recommended reading: "Impact of DST 2007 (Day Light Saving) on Oracle Databases"

Environment: GIS database environment, ESRI's ArcSDE 9.0 running on Oracle 9.2.0.7 database on a 32 bit Windows 2003 Server operating system.

Table of Contents

Introduction
Rollback and De-Installation of Patches
Patch Install Verification
Part 1 - Install DST Patches in Oracle 9.2.0.7 Database Server
1. Software and Utility Requirement
2. DST Patch Download from Oracle Metalink
3. Shutdown and Reboot
4. Patch Install
4.1. Patch # 5548107 [Interim Patch for Bug: 5548107]
4.2. Patch # 5654905 [Critical patch update 9.2.0.7 Patch 15]
4.2.2.1. Patch Database(s)
4.3. Patch # 5047902 [Oracle JVM TimeZone - Interim Patch for bug 5047902]
5. Final Testing
Part 2 - Install DST Patches in Oracle 9.2.0.7 Client Machines

Introduction: This document provides steps involved in installing Oracle DST patches in Oracle Database and Client machines. Identify and list out the Oracle database server(s) and client machines.

Server	Oracle Product Installed
ORADBTEST	Database Server
ORADBPROD	Database Server
WEBTEST	Client
WEBPROD	Client
APPTEST	Client
APPPROD	Client

Rollback and De-Installation of Patches: It’s safe to backup entire database server (including ORACLE_HOME and Database files) before patches are installed for the purpose of restoring the database.

However, there is a second option available, rollback scripts are created for each patch install when applied using Oracle OPatch Utility, hence a patch (or a patch-set) can be rolled back using them. During the patch install, rollback scripts are automatically created at "%ORACLE_HOME%\.patch_storage\<patch_id>\" folder in the name "rollback_<patch_id>.cmd". Patch De-Installation instructions are available at the bottom of each respective README.txt (or README.html if available) files.

There was no requirement for patch rollback when installed, hence steps for rolling back a patch is not included in this document.

Patch Install Verification:Verify the log file "Apply_<patch_id>_<timestamp>.log" created at "%ORACLE_HOME%\.patch_storage\<patch_id>\ for any errors after every OPatch install. Also check %ORACLE_HOME%\cpu\CPUJan2007Apply_<ORACLE_SID>_<timestamp>.log after running "catcpu.sql" script for patch 5654905.

Part 1 - Install DST Patches in Oracle 9.2.0.7 Database Server

1. Software and Utility Requirement: Oracle DST Patches are applied using an Oracle Patch utility called OPatch. It requires Perl 5.005_03 or higher and JDK versions of 1.3.1 or higher.

Note: Java, OPatch and Perl that were already present in Database Server may not include all the required files for DST patches. Hence download and reinstall all the three once again to successfully install the DST patches.

1.1. JAVA: Download and install JAVA from below Sun website and set PATH accordingly:-

J2SDK - http://java.sun.com/j2se/1.4.2/download.html

C:\j2sdk1.4.2_13\bin>java -version java version "1.4.2_13" Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_13-b06) Java HotSpot(TM) Client VM (build 1.4.2_13-b06, mixed mode

In Database Server, it is installed in "C:\j2sdk1.4.2_13", include its "bin" folder in "Path" environment variable [refer section 1.4].

1.2. Perl - Download Perl using Oracle Interim Patch # 2417994 and install it as per instructions provided in the README.txt file.

Log into "metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "2417994" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". Download the 9.2.0.1 release, downloaded file name would be "p2417872_9201_WINNT.zip". [Or use below link to download]

http://updates.oracle.com/ARULink/Download/process_form/p2417872_9201_WINNT.zip?file_id=536569&aru=2430176

C:\perl\perl\5.00503\bin\MSWin32-x86>perl -version This is perl, version 5.005_03 built for MSWin32-x86 Copyright 1987-1999, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5.0 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using `man perl' or `perldoc perl'. If you have access to the Internet, point your browser at http://www.perl.com/, the Perl Home Page.

In Database Server, Perl is installed in "C:\perl", include its "..\bin\MSWin32-x86" folder in "Path" environment variable [refer section 1.4].

1.3. OPatch - In Database Server, OPatch is installed in "D:\oracle\ora92\OPatch" folder. [If already present, move/backup the files present in the OPatch folder and reinstall new OPatch in the same folder]. Include its "D:\oracle\ora92\OPatch" folder in "Path" environment variable [refer section 1.4].

set ORACLE_HOME=d:\oracle\ora92 D:\>opatch version Oracle Interim Patch Installer version 1.0.0.0.52 Copyright (c) 2005 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com OPatch Version: 1.0.0.0.52 OPatch returns with error code = 0

Download Oracle OPatch using Bug # 2417872, and install it as per instructions provided in the README.txt file.

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "2617419" as patch number, Select "Microsoft windows Server 2003" as platform and click "Go". Download the 10.1.0.2 release, downloaded file name would be "p2617419_10102_GENERIC.zip". [Or use below link to download].

http://updates.oracle.com/ARULink/Download/process_form/p2617419_10102_GENERIC.zip?aru=5939446&file_id=9596770&patch_file=p2617419_10102_GENERIC.zip

Reference: Oracle9i Data Server Interim Patch Installation (OPatch) Note: 189489.1

1.4. Path environment variable: Once Path is set for all the above 3, it would look as follows:-

echo %Path% D:\oracle\ora92\jre\1.4.2\bin\client;D:\oracle\ora92\jre\1.4.2\bin; D:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin; C:\Program Files\Oracle\jre\1.1.8\bin;C:\WINDOWS\system32; C:\WINDOWS;C:\WINDOWS\System32\Wbem; C:\Program Files\Microsoft SQL Server\80\Tools\BINN; C:\esri_sde\ArcSDE\ora9iexe\bin;c:\program files\netiq\Common\Bin; C:\j2sdk1.4.2_13\bin;D:\oracle\ora92\OPatch; C:\perl\perl\5.00503\bin\MSWin32-x86;

2. DST Patch Download from Oracle Metalink:-
2.1. Interim Patch for Bug: 5548107

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "5548107" as patch number, Select "Microsoft windows Server 2003" as platform and click "Go". Download the 9.2.0.7 release, downloaded file name would be "p5548107_92070_GENERIC.zip". [Or use below link to download].

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5548107&release=8092070&plat_lang=2000P&patch_num_id=759969

2.2. Oracle 9.2.0.7 Patch 15 on Windows 32 bit # 5654905

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" and provide "5654905" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". The downloaded file name would be "p5654905_92070_WINNT.zip". [Or use below link to download].

http://updates.oracle.com/ARULink/Download/process_form/p5654905_92070_WINNT.zip?file_id=18784604&aru=8927462

2.3. Oracle JVM Patch # 5047902

"metalink.oracle.com" -> "Patch & Updates" -> "Simple Search" provide "5047902" as patch number, Select "Microsoft Windows (32 bit)" as platform and click "Go". Download the 9.2.0.7 release, downloaded file name would be "p5047902_92070_GENERIC.zip". [Or use below link to download].

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5047902&release=8092070&plat_lang=2000P&patch_num_id=682328

Unzip all the above three zip files (patches) in the appropriate folders and following is the folder structure created in Database Server:

Example:
Folder called "D:\5548107" for patch 5548107
Folder called "D:\5654905" for patch 5654905
Folder called "D:\5047902" for patch 5047902
Read the README.txt present in the respective unzipped folder for further information about the patches.

3. Shutdown and Reboot

3.1. Manual Services - Make all the ArcSDE and Oracle Services "Startup Type" as "Manual" if they are "Automatic" [Note down the "Startup Type" of these services prior to the change, will need to reverse it back to the original startup type ("Automatic") after the patch install].

3.2. Shutdown ArcSDE Service(s):

C:\>set ORACLE_SID=TESTORCL C:\>set SDEHOME=C:\esri_sde\ArcSDE\ora9iexe C:\>sdemon -o shutdown -i esri_sde -p <password> -N ArcSDE Instance esri_sde on TESTORCL is Shutdown!

3.3. Shutdown Oracle Databases: [From command line sqlplus.exe, not the windows sqlplusw.exe]

SQL> conn sys/@TESTORCL as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

3.4. Stop all Oracle* services:

C:\>net stop OracleServiceTESTORCL The OracleServiceTESTORCL service is stopping. The OracleServiceTESTORCL service was stopped successfully. C:\>net stop OracleOra92TNSListener The OracleOra92TNSListener service is stopping .The OracleOra92TNSListener service was stopped successfully.

3.5. Rename bin and reboot server

- Rename the "bin" folder in "D:\oracle\ora92" to some other name (Example: "bin_save") and reboot the server.
- Once the server comes back up, rename the "bin_save" folder back to "bin" again

4. Patch Install
4.1. Patch # 5548107[Interim Patch for Bug: 5548107]. Patch the Oracle Home (Run only once for an Oracle Home):

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\>set ORACLE_HOME=D:\ORACLE\ORA92 D:\>cd 5548107 D:\5548107>opatch lsinventory Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_16-02-35.log" Result: Installed Patch List: ===================== 1) Patch 5548107 applied on Wed Feb 28 15:28:38 EST 2007 [ Base Bug(s): 5548107 ] OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\5548107>opatch apply Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\5548107\Apply_5548107_02-28-2007_16-03-22.log" Subset patches: 5548107, The fixes for Patch 5548107, are included in the patch currently being installed (5548107). OPatch will roll back the subset patch(es) and install the new patch (5548107). Backing up comps.xml ... OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = d:\oracle\ora92) Is this system ready for updating? Please respond Y|N > Y Rolling back patch 5548107... Creating log file quot;D:\oracle\ora92\.patch_storage\5548107\RollBack_5548107_02-28 -2007_16-03-39.log" Rolling back with all-node mode. OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Oracle instances have been shut down, proceeding with auto-rollback. Removing patch 5548107... Restoring copied files... Warning: Cannot restore the file "d:\oracle\ora92\rdbms\admin\utltzuv2.sql" as OPatch can't find backup file, so it is backed up as quot;d:\oracle\ora92\rdbms\admin\utltzuv2.sql_removed_5548107" Updating inventory... Back to applying patch 5548107... Backing up 2nd copy of comps.xml ... Applying patch 5548107... Patching jar files... Patching copy files... Inventory is good and does not have any dangling patches. Updating inventory... Verifying patch... Backing up comps.xml ... OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

Optional: Patch 5548107 will create a file called "utltzuv2.sql" in the "ORACLE_HOME\rdbms\admin" folder, this script is to check if TIMESTAMP WITH TIME ZONE data is stored in the database. Refer to Metalink document "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note: 396670.1" for further details.

4.2. Patch # 5654905 [Critical patch update 9.2.0.7 Patch 15]

4.2.1. Patch the Oracle Home (Run once for each Oracle Home):

-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\>cd D:\5654905 set ORACLE_HOME=D:\ORACLE\ORA92 D:\5654905>opatch lsinventory Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_16-19- 55.log" Result: Installed Patch List: ===================== 1) Patch 5548107 applied on Wed Feb 28 16:04:19 EST 2007 [ Base Bug(s): 5548107 ] OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\5654905>opatch apply Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\5654905\Apply_5654905_02-28-20 07_16-21-12.log" Backing up comps.xml ... OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = d:\oracle\ora92) Is this system ready for updating? Please respond Y|N > Y Executing the Apply pre-patch script (D:\5654905\custom\scripts\pre.bat)... Applying patch 5654905... Patching jar files... Patching copy files... Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\30" Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\90" Creating new directory "d:\oracle\ora92\cpu\cpujan2007\owa_all\101" Creating file to hold list of directories that were mkdir'ed: "D:\oracle\ora92\.patch_storage\5654905\opatch_dirs_created.lst" Inventory is good and does not have any dangling patches. Updating inventory... Verifying patch... Backing up comps.xml ... ***************************************************************** ** ATTENTION ** ** ** ** Please note that the Security Patch Installation (Patch Deinstallation) is ** ** not complete until all the Post Installation (Post Deinstallation) ** ** instructions noted in the Readme accompanying this patch, have been ** ** successfully completed. ** ** ** ***************************************************************** ***************************************************************** Executing the Apply post-patch script (D:\5654905\custom\scripts\post.bat)... OPatch succeeded. OPatch returns with error code = 0 -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- D:\5654905>cscript //nologo remove_demo.js ORACLE_HOME is set to D:\ORACLE\ORA92 This script will remove the Vulnerable OHS Demos %ORACLE_HOME%/Apache/Apache/fcgi-bin/echo* Please note that you will NOT be able to restore these demos after removing, if you may want to restore these demos, please manually backup all the files under %ORACLE_HOME%/Apache/Apache/fcgi-bin/ directory, and then rerun remove_demo.js Continue to remove the Vulnerable OHS Demos? Please respond Y|[N] => Y Removing the Vulnerable OHS Demos... Patch Installation Script Completed -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

4.2.2. Patch the Databases (run for each database if multiple instances running)

4.2.2.1. Database: TESTORCL

C:\>net start OracleOra92TNSListener The OracleOra92TNSListener service is starting. The OracleOra92TNSListener service was started successfully. C:\>net start OracleServiceTESTORCL The OracleServiceTESTORCL service is starting. The OracleServiceTESTORCL service was started successfully. C:\>D: D:\>cd D:\oracle\ora92\cpu\cpujan2007 D:\oracle\ora92\cpu\cpujan2007>sqlplus "sys/<password>@TESTORCL as sysdba" SQL*Plus: Release 9.2.0.7.0 - Production on Wed Feb 28 16:35:05 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 630268612 bytes Fixed Size 455364 bytes Variable Size 285212672 bytes Database Buffers 343932928 bytes Redo Buffers 667648 bytes Database mounted. Database opened. -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- @catcpu.sql ... ... No. of Invalid Objects is :50 Please refer to README.html to for instructions on validating these objects Logfile for the current catcpu.sql session is : APPLY_SDE_28Feb2007_16_36_40.log not spooling currently SQL> select status,count(*) from dba_objects group by status order by 1; STATUS COUNT(*) ------- ---------- INVALID 60 VALID 33031 SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production D:\oracle\ora92\cpu\cpujan2007>cd ../.. D:\oracle\ora92>cd rdbms/admin D:\oracle\ora92\rdbms\admin> sqlplus "sys/<password>@TESTORCL as sysdba" SQL*Plus: Release 9.2.0.7.0 - Production on Wed Feb 28 16:43:07 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- SQL> @utlrp.sql PL/SQL procedure successfully completed. Table created. ... ... PL/SQL procedure successfully completed. SQL> select status,count(*) from dba_objects group by status order by 1; STATUS COUNT(*) ------- ---------- INVALID 2 VALID 33079 SQL> column owner format a15 SQL> column object_name format a25 SQL> column object_type format a25 SQL> select owner,object_name, object_type from dba_objects where status <> 'VALID' order by 1,2,3; OWNER OBJECT_NAME OBJECT_TYPE --------------- ------------------------- ------------------------- TEST_ATT P_UPD_SALE_PRICE PROCEDURE TEST_BTT P_UPD_SALE_PRICE PROCEDURE

Shutdown and Restart TESTORCL database, and start ArcSDE Service esri_sde

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 630268612 bytes Fixed Size 455364 bytes Variable Size 285212672 bytes Database Buffers 343932928 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production D:\oracle\ora92\rdbms\admin>set ORACLE_SID=TESTORCL D:\oracle\ora92\rdbms\admin>set SDEHOME=C:\esri_sde\ArcSDE\ora9iexe D:\oracle\ora92\rdbms\admin>sdemon -o start -i esri_sde -p <password> ArcSDE Instance esri_sde started Wed Feb 28 16:53:16 2007

Testing: Using ArcCatalog, connect to Vector (esri_sde) and see if the connection succeeds and able to view the layers. Tested and it worked.

4.3. Patch # 5047902 [Oracle JVM TimeZone - Interim Patch for bug 5047902]

4.3.1. Shutdown all ArcSDE Services and Oracle Databases [as described in section 3.2 through 3.4].

4.3.2. Move file "orajox9java_util.dll" from "D:\oracle\ora92\javavm\admin" to some other (backup) folder and ensure that the file is backed up and not present in "ORACLE_HOME\javavm\admin" folder.

4.3.3. Patch the Oracle Home (Run only once for an Oracle Home):

D:\5654905>cd ../5047902 D:\5047902>opatch apply Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\5047902\Apply_5047902_02-28-20 07_17-53-12.log" Backing up comps.xml ... OPatch detected non-cluster Oracle Home from the inventory and will patch the lo cal system only. Please shut down Oracle instances running out of this ORACLE_HOME (Oracle Home = d:\oracle\ora92) Is this system ready for updating? Please respond Y|N > Y Applying patch 5047902... Patching jar files... Patching copy files... Inventory is good and does not have any dangling patches. Updating inventory... Verifying patch... Backing up comps.xml ... OPatch succeeded. OPatch returns with error code = 0 D:\5047902>opatch lsinventory Oracle Interim Patch Installer version 1.0.0.0.56 Copyright (c) 2006 Oracle Corporation. All Rights Reserved.. We recommend you refer to the OPatch documentation under OPatch/docs for usage reference. We also recommend using the latest OPatch version. For the latest OPatch version and other support related issues, please refer to document 293369.1 which is viewable from metalink.oracle.com Oracle Home : D:\ORACLE\ORA92 Oracle Home Inventory : D:\ORACLE\ORA92\inventory Central Inventory : C:\Program Files\oracle\inventory from : N/A OUI location : D:\ORACLE\ORA92\oui OUI shared library : D:\ORACLE\ORA92\oui\lib\win32\oraInstaller.dll Java location : "D:\ORACLE\ORA92\jre\1.4.2\bin\java.exe" Log file location : D:\ORACLE\ORA92/.patch_storage/<patch ID>/*.log Creating log file "D:\oracle\ora92\.patch_storage\LsInventory__02-28-2007_17-55- 09.log" Result: Installed Patch List: ===================== 1) Patch 5047902 applied on Wed Feb 28 17:54:56 EST 2007 [ Base Bug(s): 5047902 ] 2) Patch 5654905 applied on Wed Feb 28 16:24:22 EST 2007 [ Base Bug(s): 4605400 4315431 3801082 3721136 4741071 4572340 5211863 444 8018 4192148 4049345 4047167 4544805 4908162 5411847 2838218 4928723 5250979 404 7969 5059488 3975758 4589659 4872999 3344331 4523125 3350337 4162272 4925103 412 4059 5242644 5064363 5500873 4708037 5250980 5064365 4379241 4632780 4547641 475 1923 4751528 5225794 4499035 5654905 4547566 5188596 4748597 5689875 3125250 462 5102 2701372 5021708 5049060 3904125 4523371 4083461 5049062 4656033 5212539 324 3584 3667025 4721492 3807408 3396162 4947132 4950942 4964703 4754842 4684373 456 7733 2595474 4519477 4567971 3967276 4715022 2816302 4312390 4107544 4329304 501 5557 4427475 4607458 4007599 4134994 4536817 4248629 4573980 4923667 4434689 457 0598 4917666 4287891 4409977 3799129 4294948 4627859 4718976 4969880 4869694 400 8013 4689959 5025840 3101559 4900129 3098032 5249142 4285404 4308824 4369235 463 2494 4618715 3429960 4592382 4335559 3984145 3684772 3857781 4687386 4288876 461 0323 5116414 3281270 4036921 4253914 4121749 5290940 5490841 4257473 4446528 454 8339 3328894 3937981 4449595 4398475 4727589 3879892 4402255 3206617 4369410 518 8321 4486132 4207529 3351594 5490931 4727517 4523577 5694714 4593537 3816595 493 9157 5382965 4567854 4579590 4659781 4939797 4529007 4275234 2709343 5045474 432 2477 4627335 3383661 4695511 2467239 4483951 4188472 4658188 3889156 4599763 469 7938 ] 3) Patch 5548107 applied on Wed Feb 28 16:04:19 EST 2007 [ Base Bug(s): 5548107 ] OPatch succeeded. OPatch returns with error code = 0

4.3.4. Startup Oracle Databases and respective ArcSDE Services [use section 4.2.2.1. for startup command usage] and below Create JAVA in each databases.

D:\oracle\ora92\rdbms\admin>sqlplus "sys/<password>@TESTORCL as sysdba" SQL*Plus: Release 9.2.0.7.0 - Production on Wed Feb 28 18:01:33 2007 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> CREATE OR REPLACE JAVA SYSTEM 2 / Java created.

4.3.5. Make ArcSDE and Oracle Services "Startup Type" to "Automatic" if they were originally "Automatic" (refer section 3.1).

5. Final Testing: Shutdown ArcSDE Services and Oracle Databases, reboot the machine, once the machine comes up, startup Oracle Database and ArcSDE Services and connect it from ArcCatalog and other front end applications.

Part 2 - Install DST Patches in Oracle 9.2.0.7 Client Machines

1. Backup/move (create a folder, example "old_28Feb2007") the existing "timezlrg.dat" and "timezone.dat" files present in "ORACLE_HOME\oracore\zoneinfo" to the new folder ("old_28Feb2007").

2. Locate the new "timezlrg.dat", "timezone.dat" and "readme.txt" in the "...5654905\files\oracore\zoneinfo" folder where Patch 5654905 is unzipped (or copy it from the DB server where the patch has already been installed) and copy it to "ORACLE_HOME\oracore\zoneinfo".

Author:	Babu Rangasamy
Title:	GIS and Database Consultant
Created Date:	March 1, 2007
Contact:	Babu.Rangasamy[at]gmail.com

Impact of DST 2007 (Day Light Saving) on Oracle Databases

Daylight Saving Time (DST) - USA - 2007

Introduction: As per the Energy Policy Act of 2005, DST will start from 2 a.m. on the 2nd Sunday in March and end at 2 a.m. on First Sunday in November [Earlier it used to start at 2 a.m. 1st Sunday in April and end at 2 a.m. Last Sunday in October].

Spring Forward: On 2nd Sunday in March, clock will leap ahead from 1:59 a.m. to 3:00 a.m.

Fall Back: On 1st Sunday in November, clock will move back from 1:59 a.m. to 1:00 a.m.

Countries Affected: Canada and Bermuda are making similar changes to be consistent with USA time changes. Computers and applications running in other countries (who don't follow DST) having associated with computers and applications running in the DST implemented counties could also be affected.

Operating System: Once the required patch is applied, the system clock will automatically adjust its system time at the beginning and end of DST. Any dependent applications running on the OS will obtain the system time from the system clock.

Microsoft Daylight Saving Time: Help and Support Center

How it affects the Oracle Database?: Time zone and DST are used in Oracle database at the below two areas. Hence 2 patches are available, 1 for Time zone files and other for OJVM.

[1] Time Zone: Applicable only for Oracle 9.x and 10.x. There are two datatypes TIMESTAMP WITH LOCAL TIME ZONE [TSLTZ] and TIMESTAMP WITH TIME ZONE [TSTZ] and a function TZ_OFFSET which obtains their time-zone information from Oracle's time-zone file which comes as a part of Oracle database installation.

[1a] Database Server Side:

If your database uses any of the above (datatypes or/and functions) and uses the affected time zones, then patch is required, otherwise patch is not necessary. A script is available to assess the use of time zone in the database.

[1b] Client Side: If all the below conditions are met, then time zone file install is required:-
- connects to the affected database
- time zone data type used by client
- client uses the affected time zone

Refer Below Metalink documents:-
- Effects on client and middle-tier of applying time zone patches on the Oracle Database Note:396426.1
- Workarounds when Database time zone patches are not available for your patchset Note:396387.1

[2] Oracle Java Virtual Machine (OJVM) uses time-zone information which is stored in its database. Need to install the patch only on the database side if OJVM is installed, applicable for Oracle database versions from 8.1.7.4 to 10.2.0.3.

Here is a typical (9.2) query to check whether OJVM is installed in your database:-

column owner format a10 SELECT object_type,       owner,       status,       COUNT(*)  FROM dba_objects WHERE object_type LIKE '%JAVA%' GROUP BY object_type,owner,status; OBJECT_TYPE        OWNER     STATUS  COUNT(*) ------------------ ---------- ------- ---------- JAVA DATA          SYS        VALID   288 JAVA CLASS         SYS        VALID   9080 JAVA CLASS         SYS        INVALID 1 JAVA CLASS         ORDSYS     VALID   870 JAVA CLASS         ORDSYS     INVALID 12 JAVA SOURCE        SYS        VALID   8 JAVA SOURCE        ORDSYS     VALID   8 JAVA RESOURCE      SYS        VALID   180 JAVA RESOURCE      ORDSYS     VALID   16 9 rows selected

Refer to Metalink Note:397770.1 [How to Correctly Check if the Oracle JVM is Installed in the Database].

[3] Here are the things that you got to do:-

[3a] Backup your client and server.

[3b] Run re_tz_views.sql [Refer to Metalink Note:412971.1 - SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a
Database]

@cre_tz_views.sql spool cre_tz_views_output.txt select * from TZ$TSLTZ_TAB_COLS; select * from TZ$TSLTZ_VW_COLS; select * from TZ$NAMED_TSTZ_TAB_COLS; select * from TZ$NAMED_TSTZ_VW_COLS; select * from TZ$ARGUMENTS; select * from TZ$DBTIMEZONE; select * from TZ$OVERVIEW; select * from TZ$SOURCE; spool off drop function TZ$NAMED_TSTZ_VW_COLS_FN ; drop view TZ$TSLTZ_TAB_COLS ; drop view TZ$TSLTZ_VW_COLS ; drop view TZ$ARGUMENTS ; drop view TZ$SOURCE ; drop view TZ$DBTIMEZONE; drop function TZ$NAMED_TSTZ_TAB_COLS_FN; drop type TSTZ_NAMED_TABLE; drop view TZ$NAMED_TSTZ_TAB_COLS; drop view TZ$NAMED_TSTZ_VW_COLS ; drop view TZ$OVERVIEW ;

DBTIMEZONE ---------- -04:00 USAGE_TYPE                  VALUE      DESCRIPTION --------------------------- ---------- ------------------------------------------------------------------------ DBTIMEZONE                  -04:00     Database Time Zone, if this is an 'offset' then not affected by DST NAMED_TSTZ_TABLE_USE_NONSYS 0          Number of TSTZ columns using named time zones in tables not owned by SYS NAMED_TSTZ_TABLE_USE_SYS    0          Number of TSTZ columns using named time zones in tables owned by SYS NAMED_TSTZ_VIEW_USE_NONSYS  0          Number of TSTZ columns using named time zones in views not owned by SYS NAMED_TSTZ_VIEW_USE_SYS     0          Number of TSTZ columns using named time zones in views owned by SYS TSLTZ_TABLE_USE             0          Number of TSLTZ columns used in tables TSLTZ_VIEW_USE              0          Number of TSLTZ columns used in views TZ_ARGUMENTS                0          Number of PL/SQL objects with Time Zone arguments OWNER           NAME                           TYPE         LINE       TEXT --------------- ------------------------------ ------------ ---------- ------------------------------------------------------------------------------------------- BABU            TESTXSP_STORE_DDL              PROCEDURE    25         updatedate=systimestamp,RESOURCE_TYPE=IN_RESOURCE_TYPE BABU            TESTXSP_STORE_DML              PROCEDURE    39         IN_BABU_SERVICE,IN_SERVICE_TYPE,IN_ONLINK,IN_CONTENT_TYPE,systimestamp, IN_RESOURCE_TYPE ); BABU            CREATE_ONLINE_DOCUMENT         PROCEDURE    13         insert into TEST_TAB (ACTTIME) values (systimestamp); BABU_RW         B_UPDATE_APPL_ALERT_UPDATE     TRIGGER      5          SELECT SYSTIMESTAMP INTO :NEW.LAST_UPDATE_STARTED FROM dual; BABU_ADMIN      B_INSERT_SERVICE_TXT           TRIGGER      13         SELECT SYSTIMESTAMP INTO :NEW.TIME_STAMP FROM dual; BABU_ADMIN      B_UPDT_WEB_SERVICES_TXT        TRIGGER      2          select sysdate into :new.timestamp from dual; BABU_ADMIN      B_INSRT_WEB_SERVICES_TXT       TRIGGER      2          select sysdate into :new.timestamp from dual; BABU_ADMIN      PKG_SERVICE                    PACKAGE BODY 18         || 'TO_TIMESTAMP(''' || start_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')' || ' AND ' BABU_ADMIN      PKG_SERVICE                    PACKAGE BODY 19         || 'TO_TIMESTAMP(''' || end_date_in || ''',''MM/DD/YYYY HH24:MI:SS'')'

[3c] Identify the correct "utltzuv2.sql" [this script only checks stored TIMESTAMP WITH TIME ZONE data in the database, but NOT the TIMESTAMP WITH LOCAL TIME ZONE data], download it [Refer Table 1 in Metalink Note:359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database] and run the "utltzuv2.sql" to check the if TSTZ datatype is used in any tables in the database.

For 9.2.0.7, download Patch and install patch 5548107 (also to get the utltzuv2.sql script).

Refer to "Usage of utltzuv2.sql before updating time zone files in Oracle 9 Note:396670.1"

SQL> @utltzuv2.sql You current timezone version is 1! Query sys.sys_tzuv2_temptab table to see if any TIMEZONE data is affected by version 3 transition rules PL/SQL procedure successfully completed.

[3d] Save TSTZ data as varchar2.

[3e] Apply patch [Refer Table 2 and 3 in Metalink Note:359145.1
- Impact of 2007 USA daylight saving changes on the Oracle database]
[For 9.2.0.7 DB TimeZone, use Patch bundle 15 (bug 5654905) or later]
[For 9.2.0.7 OJVM, use JVM Timezone Patch 5047902]

[3f] Restore back to TSTZ datatype

[3g] Apply fixes to the clients and middle-tiers.

[3h] Work on already existing TSLTZ data.

[3i] Apply patch for OJVM.

References:
1. Impact of 2007 USA daylight saving changes on the Oracle database - Note:359145.1
2. How to Determine Whether Time Zone Changes Will Affect Your Database - Note:406410.1
3. SCRIPT: cre_tz_views.sql - Assess Time Zone usage in a Database - Note:412971.1
4. USA 2007 Daylight Saving Time (DST) Compliance for Oracle Server Technologies Products Note:397281.1
5. Timestamps & time zones - Frequently Asked Questions Note:340512.1

Click here for a step-by-step documentation on "Installation of Oracle DST 2007 Patches in Oracle 9.2.0.7 Database and Client Machines"

Author:	Babu Rangasamy
Title:	GIS and Database Consultant
Created Date:	February 8, 2007
Contact:	Babu.Rangasamy[at]gmail.com