Oracle 10g Standard Edition Installation & Features

Introduction:

Document highlights oracle standard edition installation steps, database hardening as per SOX compliances and issues faced during a project setup along with best practices

Table of Content(TOC):

1.     Platform OS/DB Version  

2.     How to download oracle software

3.     Difference between Enterprise & Standard Edition

4.     Server Pre-requisites

5.     Software & database Installation

6.     Issues faced

7.     Practices

1.     Platform OS/DB Version

This guide describes how to install and configure Oracle Database 10g release 2 (10.2) on AIX 5L based system (64-bit)

OS Platform:                                      IBM AIX5L 5.3

DB version :                                        10.2.0.5

DB Patch Level:                                 PSU APR 2011 (11724962)

DB Software Edition:                      Standard Edition

CPU Type:                                           64-bit

HARDWARE_BITMODE:                64

Oracle OS User :                               ora10g

Oracle groups:                                  oinstall (primary), dba (secondary)

2.     How to download oracle software

Download Link for oracle 10g

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Click on radio button & Accept the License:

Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition for AIX5L :

Download file:

10gr2_aix5l64_database.cpio.gz

Extract the file:

gunzip 10gr2_aix5l64_database.cpio.gz

cpio –idcmv 10gr2_aix5l64_database.cpio.gz

3.  Difference between Enterprise & Standard Edition

Oracle Note:  Differences between Enterprise, Standard and Personal Editions on Oracle 10.2 [ID 465465.1]

Features not available in Standard Edition:

I.                    High Availability

Oracle Data Guard :      

·         Standby with/without Data Guard broker configuration, logical standby

Online operations:         

·         online index maintenance/ table organization/ table redefinition

Backup and recovery:   

·         Block-level media recovery

·         Parallel backup and recovery

·         Change-aware incremental backups

·         Duplexed backup sets (backup sets can be written to multiple devices in parallel)

·         Point-in-time tablespace recovery (a tablespace can be recovered up to a specified point in time after a failure or inadvertent transaction execution)

·         Trial recovery (redo is applied in memory only but is not written to disk and is easily rolled back)

·         RMAN Backup Encryption

·         Unused Block Compression

Oracle Flashback features:

·         Oracle Flashback Table

·         Oracle Flashback Database

·         Oracle Flashback Transaction Query

·         Restore Points

II.                  Security

Oracle Advanced Security

Oracle Label Security

Virtual Private Database

Fine-grained auditing

Enterprise User Security

N-tier authentication authorization

III.                Manageability

Oracle Change Management Pack

Oracle Configuration Management Pack

Oracle Diagnostic Pack

Oracle Tuning Pack

Database Resource Manager:

·         Database Resource Manager gives Oracle Database more control over resource management decisions, circumventing problems arising from inefficient operating system management.

IV.                VLDB, Data Warehousing, Business Intelligence

Oracle Partitioning

Oracle OLAP

Oracle Data Mining

Data compression

Bitmapped index and bitmapped join index:

·         This feature provides an index type commonly used in data warehouses for columns with low cardinality, such as 'Y' or 'N', to dramatically improve performance gains in a data warehouse application.

Export transportable tablespace

Asynchronous Change Data Capture:

·         This feature provides a framework for capturing change data, publishing it, and enabling applications to subscribe to the change data in a controlled fashion. Change capture occurs asynchronously based on the information in the Oracle redo logs.

Summary management :

·         Summary management consists of mechanisms to define materialized views and dimensions, refresh and query rewrite mechanisms, and a collection of materialized view analysis and advisory functions and procedures in the DBMS_OLAP package

V.                  Parallel Operations

Parallel query

Parallel DML

Parallel index build:

·         Indexes can be created using parallel processes, significantly improving performance of the operation.

Parallel statistics gathering

Parallel Data Pump export and import

Parallel text index creation

Parallel backup and recovery

VI.                Information Integration

Oracle Streams

Advanced Replication (multimaster)

Oracle Messaging Gateway

VII.              Networking

Connection Manager

Multiprotocol connectivity

 VIII.            Content Management

Oracle Spatial

4.     Server Pre-requisites

I.                    Operating system filesets for AIX 5L

bos.adt.base

bos.adt.lib

bos.adt.libm

bos.perf.libperfstat

bos.perf.perfstat

bos.perf.proctools

xlC.aix50.rte:7.0.0.4 or later

xlC.rte:7.0.0.1 or later

II.              PL/SQL native compilation

    *   IBM XL C/C++ Enterprise Edition V7.0 for AIX PTF (7.0.0.2)

    *   gcc 3.3.2

III.             Pro*C/C++, Oracle Call Interface, Oracle C++ Call Interface, Oracle XML Developer's Kit (XDK)

    *   May 2005 XL C/C++ Enterprise Edition V7.0 for AIX PTF (7.0.0.2)

IV.             Oracle JDBC/OCI Drivers:

    *   JDK 1.4.2 (64-bit)

    *   JDK 1.3.1.11 (32-bit)

    *   JDK 1.2.2.18

V.               Oracle Messaging Gateway:

mqm.Client.Bnd

mqm.Server.Bnd

VI.              APAR:

    *  IY58143: REQUIRED UPDATE FOR AIX 5.3

    *  IY59386: libdepend.mk files are all empty

    *  IY60930: Unable to delete network routes

    *  IY66513: LDR_CNTRL turns on undesirable option when initialized with incorrect value

    *  IY70159: krtl relocation problem

    *  IY68989: eFix for write to mmapped space hangs

    *  IY64361: Exception in putdiag_no_handler() when -O is specified

    *  IY65361: May 2005 XL C Enterprise Edition V7.0 for AIX PTF

    *  IY65362: MAY 2005 XL C/C++ Enterprise Edition V7 for AIX

VII. APAR required for JDK 1.4.2 (64-bit):

* IY63533: DK 1.4.2 64-bit SR1 caix64142-20040917

VIII. APARs required for JDK 1.3.1.11 (32-bit):

* IY58350: SDK 1.3.1 32-BIT SR7P : CA131IFX-20040721A

* IY65305: JAVA142 32-BIT PTF : CA142IFX-20041203

IX. APAR required for JDK 1.2.2.18:

* IY40034: SDK 1.2.2 PTF: CA122-20030115

X. ULIMIT Settings

time(seconds) unlimited

file(blocks) unlimited

data(kbytes) unlimited

stack(kbytes) unlimied

coredump(blocks) unlimited

memory(kbytes) unlimited

5.  Software & database Installation

Step 1. Verify server pre-requisites are installed as per TOC_4

Step 2. Verify mount points for oracle binaries & for database for permissions

Step 3. Verify oracle user & primary/secondary group

Step 4. Download Software as per TOC_2 and ftp to server in bin mode

Step 5. Execute rootpre.sh present in $DOWNLOAD_PATH/rootpre/rootpre.sh as root, which will grant necessary privileges on /usr/local/bin

Step 6. Setup GUI

·         Open xming on PC

·         Enable X11 forwarding before logging into server

·         Verify xclock

Step 7. Execute  $DOWNLOAD_PATH/runInstaller.sh

Step 8. Provide below inputs

i.                    Select advance installation & software only

ii.                  Installation type as Standard Edition

iii.                Specify oracle home/base location

iv.                 Verify logs during/after installation present in $ORACLE_HOME/cfgtoollogs

Step 9. Create .profile for newly installed oracle software as below in $HOME/.profile file

ORACLE_HOME=/oracle/app/product/10.2.0/db_1

export ORACLE_HOME

ORACLE_SID=

export ORACLE_SID

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/local/lib:/lib:/usr/lib

export LD_LIBRARY_PATH

PATH=$PATH:/usr/ccs/bin:$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/usr/local/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/lib

export PATH

Step 10. Database/listener creation

i.                    Execute dbca

ii.                  Select database template as general purpose

iii.                Provide database name

iv.                 Supply sys/system password

v.                   Choose memory parameter values

vi.                 Change character set to WE8ISO8859P15(as per project)

vii.               Verify database file/controlfile/redo log locations

viii.             Verify initialization parameter values

ix.                 Click on prepare db creation script can be used for future use

x.                   Monitor database creation logs under $ORACLE_HOME/cfgtoollogs/dbca

xi.                 Verify db parameter values/character set after DB creation

xii.               Create listener using netca

xiii.             Verify tnsping $ORACLE_SID                                                                                    

6.      Issues faced

Issues faced during a project setup related to DB configuration/Standard Edition are as below.

Issue  1.  Unable to create  Bitmap indexes

Problem: Geneva Application is having a table structure which requires index to create as a Bitmap Index for low cardinality values

Error:

IMP-00003: ORACLE error 439 encountered

ORA-00439: feature not enabled: Bit-mapped indexes

Action/Workaround: B-tree Index has been create

Issue  2.  Unable to create Table Partition

Problem: Geneva Application requires tables to partitioned under XXXX_CORP schema , but receiving below error while importing schema from prod.(Enterprise Edition)

Error:

IMP-00003: ORACLE error 439 encountered

ORA-00439: feature not enabled: Partitioning

IMP-00017: following statement failed with ORACLE error 439:

Action/Workaround: Converted Partitioned tables into heap tables

7.   Best Practices

Best practices are designed based on the database configuration issues faced during application setup

1.       Keep USERS/UNDOTBS tablespace datafile autoextend on during application data loading

2.       Verify dba_sys_privs / synonyms of geneva_admin & XXXX_CORP privileges from production

3.       Select_catalog_role can be granted to geneva_admin/or any temp user to identify missing privileges & revoked later

4.       Modify Partitioned tables to heap tables

5.       Modify Bitmap indexes to b-tree indexes

6.       Sqlnet.ora INBOUND_CONNECTION_TIMEOUT parameter need to be disabled or set to higher value followed by listener bounce

Error:

java.sql.SQLException: Io exception: There is no process to read data written to a pipe.

Refer oracle Note:

JDBC Connections from E-Business Suite Application Tier Fail with "java.sql.SQLException: Io exception: There is no process to read data written to a pipe." [ID 734293.1]

7.        Java 64 bit version should be installed in server

8.       /etc/resolve.conf should not be present incase host is not present in DNS, which imposes additional check while connecting through JDBC thin client & takes additional time to connect

9.       AQ$ table & privileges need to be crosschecked

10.   Invalid Geneva_admin owned wrapped packages/procedures need to be reviewed by Convergys only

11.   Apply latest PSU patch to oracle home

12.    Take cold backup after successful configuration.