PRVF-4037 : CRS is not installed on any of the nodes

During the health check, we observed that has service status is showing as down in EM12C

Error as shown in the Incident Manager :

PRVF-4037 : CRS is not installed on any of the nodes,Verification cannot proceed,,,CRS integrity check failed,,Verification of CRS integrity was unsuccessful on all the specified nodes. ,NODE_STATUS::<Host>:EFAIL,OVERALL_STATUS::EFAIL

Logged in to the host as Grid owner :

[oracle@<host> ~]$  cluvfy comp ocr

Verifying OCR integrity

Checking OCR integrity...

ERROR:
PRVF-4037 : CRS is not installed on any of the nodes
Verification cannot proceed

OCR integrity check failed

Verification of OCR integrity was unsuccessful on all the specified nodes.
[oracle@<host> ~]$

Cause :

/etc/oraInst.loc was pointing to incorrect Inventory..

Solution :

Corrected the /etc/oraInst.loc and pointed it to expected ( Grid ) Inventory

[oracle@<host> ~]$  cluvfy comp ocr

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


ASM Running check passed. ASM is running on all specified nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+DA_XX" available on all the nodes


NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Verification of OCR integrity was successful.
[oracle@<host> ~]$

Create an Alias for [ sqlplus "/as sysdba" ]

We can use this OS utility to create an alias of the commands we use very frequently:

Here, for example :

-bash-3.00$ cat .bash_profile

alias sqllogin='sqlplus "/as sysdba"'

-bash-3.00$

-bash-3.00$ sqllogin

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 5 07:24:46 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Hope this helps !!

Upgrading Application [ 11i CU1 to 11i CU2 ] and Database [ 9.2.0.6 to 11.2.0.1 ]

Hello All,


Recently I was involved in the upgrade [ application and database ]
Here is the walkthrough of the project.


::::::::::::::::::::: Overview :::::::::::::::::::::


: Existing System :
Application version : 11.5.10.1 [ 11i CU1 ]
Database version    : 9.2.0.6   [ 9iR2    ]




: Target System :
Application version : 11.5.10.2 [ 11i CU2 ]
Database version    : 11.2.0.1  [ 11gR2   ]




:Steps Involved :
1] Review of the System to be upgraded.
2] Clone for the iterations.
3] Phase - 1 : Upgrading Application from 11iCU1 to 11iCU2.
4] Phase - 2 : Upgrading Database from 9iR2 to 10gR2
5] Phase - 3 : Upgrading the required Family Packs 
6] Phase - 4 : Upgrading Database from 10gR2 to 11gR2.
7] Phase - 5 : Patches for the issues faced after upgrade.






Now , Lets get into the details of each step.


1] Review of the System to be upgraded >>
As we all aware , that 11i premium support ended in Nov 2010 , so we referred Metalink Note-ID 
"883202.1 for Minimum Baseline Patch Requirements for Extended Support"  to make our review easier.


To check the existing patchset level of the system :


Execute below sql :
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME ;


OR


Download the latest patchset.sh from the Metalink Note-ID :
Oracle Applications Current Patchset Comparison Utility - patchsets.sh [ID 139684.1]


This shell script will provide you the current patchset level and also the latest patchset available on the metalink.


This will ease our review at module level as well.


Very Important Note : Prepare a detailed sheet of the steps to be executed.




2] Clone for the iterations >>
For Cloning we used Rapid clone method .


Importantly , Always check UAT system configuration to avoid surprise performance issues after Production Upgrade.
Keep the configuration at optimum for benchmarking testing.




3] Phase - 1 : Upgrading Application from 11iCU1 to 11iCU2 >>


Phase - 1 is three segments step.


A - Applying the CU2 patch.
B - Applying the latest TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH.
C - Applying 11.5.10 INTEROP PATCH FOR 10GR2.


A] Applying the CU2 patch >>


The Read me suggests to apply latest Oracle Applications DBA Minipack 11i.AD.I :: i.e AD.I.7 plus few pre-requisites :


Next step would be to check whether our Application-Database Tier is ready to be upgraded. So, we can run TXKScript.pl on both the tiers.


The script TXKScript.pl is placed under FND_TOP on Application Tier and ORACLE_HOME on Database Tier.


Pre-requisites >>


# Run the admkappsutil.pl under AD_TOP to create the appsutil.zip file
# Copy the successfully generated zip file to DB Tier and unzip it [ unzip -o appsutil.zip ]




Next step would be to run the scripts on both the Tiers :


Sample Script :


>> Run the Technology Validation Utility using the below script on Application Tier:
$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl
        -script=$FND_TOP/patch/115/bin/txkVal11510MP_AP.pl
        -txktop=$APPLTMP
        -appspass=<apps_password>
        -outfile=$APPLTMP/txkVal11510MP.html




>>  Run the Technology Validation Utility using the below script on Database Tier:
$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl
        -script=$ORACLE_HOME/appsutil/bin/txkVal11510MP.pl
        -txktop=$ORACLE_HOME/appsutil
        -appspass=<apps_password>
        -outfile=$ORACLE_HOME/appsutil/temp/txkVal11510MP_DB.html






IMPORTANT :: Make sure the output of the script is "ALL PASS " on both the Tiers.


Next step would be to apply the CU2 patch on the application :
# Bounce the database and listener to ensure that there are no locks.
# Use the option "nocompilejsp" to save the time of upgrade.


Note : Check for the successful completion.




B] Applying the latest TXK AUTOCONFIG AND TEMPLATES ROLLUP PATCH >>


Pre-Steps :
We have to check whether the application is ready to techstack upgrade :


Run the Technology Stack Validation script as follows:
           NOTE: Ensure that your current working directory is
           [patch unzipped location]/fnd/patch/115/bin


          ./txkprepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=<apps database password>




Once we pass the validation , we can apply the TXK patch on the application Tier.




C] Applying 11.5.10 INTEROP PATCH FOR 10GR2 >>
This patch will prepare the application tier for the database upgrade to 10gR2.


Once the Phase - 1 is completed , we are sure that our Application is upgraded from 11iCU1 to 11iCU2 and also is ready to database upgrade from 9iR2 to 10gR2.




4] Phase - 2 : Upgrading Database from 9iR2 to 10gR2 >>


A] Installation of Oracle 10gR2 Base Release [ 10.2.0.1 ]


# Create a directory on the server to install the Oracle Binaries onto the server.


# Download the stage from OTN and upload & unzip the database 10gR2 base release stage to the server.


# Install the Base Release to directory. [ with companion ]


# Now download the 10gR2 patchset 3 patch and apply it on 10g Home.


# Apply the pre-req optaches on the new home. [ Refer upgrade metalink note-id ]


# Check whether the Existing database is ready for the upgrade :


Goto 10.2.0/rdbms/admin
ls –ltr  utlu112i.sql
Sqlplus “/as sysdba”


SQL> spool <filename>.log
SQL>@utlu112i.sql


B] Upgrade the database from 9iR2 to 10gR2:


# Unset the existing 9i environment:


# Set new ORACLE_HOME and ORACLE_SID 


cd $ORACLE_HIME/bin


./dbua




Follow the screens and start the upgrade.


Make sure the upgrade finishes successfully.


Check the dba_registry for successful upgrade of the components.




Post Steps :


Re-create 9idata directory
Run changeperm.sh from $ORACLE_HOME/install
Fix Korean lexers
Run adgrants.sql
Grant create Procedure on CTXSYS
Implement Autoconfig
Gather Stats for SYS schema


Note : Set new environment and check the application.




5] Phase - 3 : Upgrading the required Family Packs >>


Review required family packs ( In our case )


A] 11i.ATG_PF.H.delta.7 (RUP 7)
B] 11I.HR_PF.K delta 5
C] 11i.FIN.PF.G
D] MINI-PACK 11I.AP.O
E] MINI-PACK 11I:PN.M


Post Steps :
Generate message files. 
Compile APPS schema(s). 
Compile flexfield data in AOL tables. 
Compile menu information. 




6] Phase - 4 : Upgrading Database from 10gR2 to 11gR2


Pre-Steps :


exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSMAN'); [ if the user exist ]


A] Installation of Oracle 11gR2 Base Release [ 11.2.0.1 ]


# Create a directory on the server to install the Oracle Binaries onto the server.


# Download the stage from OTN and upload & unzip the database 11gR2 base release stage to the server.


# Install the Base Release to directory. [ with companion ]


# Apply the pre-req optaches on the new home. [ Refer upgrade metalink note-id ]


# Check whether the Existing database is ready for the upgrade :


Login as Database user 
Goto 11.2.0/rdbms/admin
ls –ltr  utlu112i.sql
Sqlplus “/as sysdba”


SQL> spool <filename>.log
SQL>@utlu112i.sql
....
SQL> spool off;


# Unset the existing 10g environment:


# Set new ORACLE_HOME and ORACLE_SID 


cd $ORACLE_HIME/bin


./dbua




Follow the screens and start the upgrade.


Make sure the upgrade finishes successfully.


Check the dba_registry for successful upgrade of the components.




Post Steps :


Re-create 9idata directory
Run adgrants.sql  
Grant create procedure privilege on CTXSYS  
Set CTXSYS parameter  
Implement Autoconfig
Gather statistics for SYS schema 


Note : Set 11g environment and check the application health.



7] Phase - 5 : Patches for the issues faced after upgrade >>


This phase is very important phase of the upgrade.


Once we release the instance to the users , they will report list of issues.


Some issues wil resolved using patches, so log a ticket and apply all the required patches.


Important Note : To avoid surprise issues after upgrade , we have to make sure that most of the issues are resolved in the iteration phase.


This will reduce the downtime of the instance after upgrade.


I have tried my best to summarize the upgrade ; I hope this will help !!




Best Of luck for the upgrade.

Metalink Note-IDs related to Concurrent Managers

1) Concurrent Processing Tables and Purge Concurrent Request and/or Manager Data Program (FNDCPPUR) [ID 104282.1]

2) Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance [ID 822368.1]

3) Best Practices for Performance for Concurrent Managers in E-Business Suite [ID 1057802.1]

4) Using the Output Post Processor (OPP) in Oracle Applications Concurrent Processing [ID 291792.1]

5) Cannot View Concurrent Manager Log And Out Files in R12 (APP-FND-01634 ) [ID 555394.1]

6) Deadlock Error During Concurrent Request Termination [ID 1060736.1]

7) Concurrent Processing (CP) / APPS Reporting Scripts [ID 213021.1]

8) How to Activate Parallel Concurrent Processing - More Facts [ID 602899.1]

9) How to Configure Concurrent Managers Applications for Parallel Concurrent Processing [ID 157352.1]10) Performance: Purge Obsolete Workflow Runtime Data ( FNDWFPR ) Concurrent Request is Slow [ID 985332.1]

11) Concurrent Requests Errors Out With ORA-20100: Error: FND_FILE Failure [ID 460643.1]

12) In R12 Get APP-FND-01564 ORA-01116 ORA-01110 ORA-27041 Errors When Running Concurrent Requests [ID 577291.1]

13) Performance Degradation When Submitting a Concurrent Request [ID 563434.1]

14) Concurrent Mangagers Down Cause Of Output Post Process Failing To Start [ID
798362.1]

15) How To Create a Java Concurrent Program? [ID 827563.1]

16) How to Troubleshoot Concurrent Manager Start Up Issue Related to Service Manager [ID 735148.1]

17) How To Submit A Concurrent Request Set Using Fnd_Request.Submit_Request [ID 382791.1]

18) How to Solve Concurrent Request Database Performance Issues [ID 863780.1]

19) How To Get Level 12 Trace And FND Debug File For Concurrent Programs [ID 726039.1]

20) How To Print Concurrent Requests in PDF Format [ID 333504.1]

21) When Submitting Create Accounting Program It Completes With Warning "Setting the Profile Option Concurrent: OPP Response Timeout to a Higher Value May Be Necessary" [ID 553320.1]

22) Troubleshooting AutoInvoice: Concurrent Request Failures (Request Status = Error) [ID 1082238.1]

23) How To Create A Custom Concurrent Program With Host Method and Pass Parameters To The Shell Script [ID 266268.1]

24) How to Submit a Concurrent Request With CONCSUB Using a User Other Than APPS [ID 1050415.1]

25) How To Gather Statistics On Oracle Applications 11.5.10(and above) - Concurrent Process,Temp Tables, Manually [ID 419728.1]

26) Where Is 'Delete Data From Temporary Table' Concurrent Program - ICXDLTMP.SQL [ID 397118.1]

27) Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests [ID 392635.1]

28) Concurrent Requests Fail with FDPSTP failed due to ORA-20100: File o0002712.tmp creation for FND_FILE [ID 148262.1]

29) Concurrent Requests Complete with ORACLE Error 20100 in FDPSTP [ID 157756.1]

30) Ora-01555 Caused By SELECT Statements With Low Query Duration and no Concurrent DML [ID 361992.1]

31) ORA-20100 & ORA-6512 When Running a Concurrent Process [ID 123977.1]

32) How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running. [ID 735119.1]

33) How to Solve Concurrent Request Database Performance Issues [ID 863780.1]

34) How to move Concurrent Processing Server from one node to another node [ID 373611.1]

35) A closer examination of the Concurrent Program Purge Obsolete Workflow Runtime Data [ID 337923.1]

36) Concurrent Managers Will Only Start on One Node in PCP/RAC Environment [ID 264296.1]

37) How to Solve Concurrent Request Database Performance Issues [ID 863780.1]

38) How to Submit a Concurrent Request With CONCSUB Using a User Other Than APPS [ID 1050415.1]

39) How To Create A Custom Concurrent Program With Host Method and Pass Parameters To The Shell Script [ID 266268.1]

40) Troubleshooting AutoInvoice: Concurrent Request Failures (Request Status = Error) [ID 1082238.1]

41) When Submitting Create Accounting Program It Completes With Warning "Setting the Profile Option Concurrent: OPP Response Timeout to a Higher Value May Be Necessary" [ID 553320.1]

Hope this helps !!!!

Notification Mailer Issue - Unable to approve/reject leave/PO using Outlook

Hello All,

Few days back , HRMS Team enabled the functionality to Approve/Reject the Employee's Leave through Mail ; But the User started complaining that the functionality is not working for few employees.

So after analysing the issue, we found out that the employees/managers who approve/reject the leave through "Webmail" were able to approve/reject the leave ; The employees/manager who use Outlook were able to view the Approve / Reject buttons but functionality was not working.

So after drilling down the issue , we have found that the one of the "Template" was not supporting the Outlook Header.
To fix the issue , we changed the Template "Open Notification (Direct Response Parsing)" to "Workflow Open Mail (Direct)" and bounced the Notification Mailer.

Navigation :
Login as Sysadmin > System Administration > OAM > Notification Mailer > Edit > Advanced > Step 4 of 8 > Templates section.

Thanks to metalink... :)

[ Issue ] FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file

Error :
**Starts**14-SEP-2009 16:45:36
ORACLE error 20100 in FDPSTP
FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, oklslagl.tmp in the directory, [/usr/tmp].
You will find more information in the request log.
ORA-06512: at "APPS.FND_FILE", line 417

Cause :
Unable to create the [filename].tmp in [/usr/tmp] directory.

Solution :
>> Login into the Database Node.
>> Check for the init[SID].ora file
>> Check whether we have  [/usr/tmp] listed in the utl_file_dir parameter
>> OR check for the permissions on [/usr/tmp]
>> Check whether the file named "oklslagl.tmp" already exist in [ /usr/tmp ]
If yes , Then either remove them or move them to some other temporary directory.

[Issue] Too Many Open Files Error

Hi All,
We faced an issue during Request Set run of DBI Initial load .

Current setup :
Database :10.2.0.3
Application : 12.0.4
OS : Redhat Advanced server 5 (Tikanga)


Error:
APP-FND-01564: ORACLE error - 1116 in SUBMIT: others
Cause: SUBMIT: others failed due to ORA-01116: error in opening database file 13
ORA-01110: data file 13: '.dbf' '
ORA-27041: unable to open file
SVR4 Error: 24: Too many open files
Additional information: 3
The SQL statement being executed at the time of the error was: &SQLSTMT and was executed from the file &ERRFILE.


Cause :
Refer metalink Note-id :397119.1 for more details


Solution :
(before proceeding with the solution , we should always check for the following parameter at OS level)
cat /etc/security/limits.conf
* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536


If you do not have these parameters required values ; then make the changes accordingly ; followed by server bounce.

1) Apply the opatch 5257698 on to ur Application database. [Make sure all your Application/Database services are down]
2) cd $ORACLE_HOME/nls/data/old
3) perl cr9idata.pl [This will recreate the *.nlb file in $ORACLE_HOME/nls/data] Set the environment as mentioned.
4) Start your database and applications services.
5) Retest your issue.

Happy Reading !! :)

[Read Me]Oracle Database Family...

I came across an interesting article on the web ..so thought to share with you all..
This article has various versions of Oracle Database ; with release date and features added..

** Oracle 10g Release 2 (10.2.0) – September 2005 **
Transparent Data Encryption
Async commits
CONNECT ROLE can not only connect
Passwords for DB Links are encrypted
New asmcmd utility for managing ASM storage

** Oracle 10g Release 1 (10.1.0) **
Grid computing – an extension of the clustering feature (Real Application Clusters)
Manageability improvements (self-tuning features)
Performance and scalability improvements
Automated Storage Management (ASM)
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Flashback operations available on row, transaction, table or database level
Ability to UNDROP a table from a recycle binAbility to rename tablespaces
Ability to transport tablespaces across machine types (E.g Windows to Unix)
New ‘drop database’ statement
New database scheduler – DBMS_SCHEDULER
DBMS_FILE_TRANSFER Package
Support for bigfile tablespaces that is up to 8 Exabytes in size
Data Pump – faster data movement with expdp and impdp.

** Oracle 9i Release 2 (9.2.0) **
Locally Managed SYSTEM tablespaces
Oracle Streams – new data sharing/replication feature (can potentially replace Oracle Advance Replication and Standby Databases)
XML DB (Oracle is now a standards compliant XML database)
Data segment compression (compress keys in tables – only when loading data)
Cluster file system for Windows and Linux (raw devices are no longer required).
Create logical standby databases with Data Guard
Java JDK 1.3 used inside the database (JVM)
Oracle Data Guard Enhancements (SQL Apply mode – logical copy of primary database, automatic failover
Security Improvements – Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory

** Oracle 9i Release 1 (9.0.1) – June 2001 **
Traditional rollback segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it’s own “Rollback Segments” and size them automatically without any DBA involvement.
Flashback query (dbms_flashback.enable) – one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
Use Oracle Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetch data and hand it to Oracle Text to be indexed.
Oracle Nameserver is still available, but deprecate in favour of LDAP Naming (using the Oracle Internet Directory Server). A nameserver proxy is provided for backwards compatibility as pre-8i client cannot resolve names from an LDAP server.
Oracle Parallel Server’s (OPS) scalability was improved – now called Real Application Clusters (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications doesn’t need to be cluster aware anymore.
The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read write operations. The Data Guard Broker allows single step fail-over when disaster strikes.
Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
Dynamic Memory Management – Buffer Pools and shared pool can be resized on-the-fly. This eliminates the need to restart the database each time parameter changes were made.
On-line table and index reorganization.
VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI’s, etc. XML integrated with AQ.
Cost Based Optimizer now also consider memory and CPU, not only disk access cost as before.
PL/SQL programs can be natively compiled to binaries.
Deep data protection – fine grained security and auditing. Put security on DB level. SQL access do not mean unrestricted access.
Resumable backups and statements – suspend statement instead of rolling back immediately.
List Partitioning – partitioning on a list of values.
ETL (eXtract, transformation, load) Operations – with external tables and pipelining.
OLAP – Express functionality included in the DB.
Data Mining – Oracle Darwin’s features included in the DB.

** Oracle 8i (8.1.7) **
Static HTTP server included (Apache)
JVM Accelerator to improve performance of Java code
Java Server Pages (JSP) engine
MemStat – A new utility for analyzing Java Memory footprints
OIS – Oracle Integration Server introduced.
PLSQL Gateway introduced for deploying PL/SQL based solutions on the Web
Enterprise Manager Enhancements – including new HTML based reporting and Advanced Replication functionality included.
New Database Character Set Migration utility included.

** Oracle 8i (8.1.6) **
PL/SQL Server Pages (PSP’s)
DBA Studio Introduced.
Statspack.
New SQL Functions (rank, moving average).
ALTER FREELISTS command (previously done by DROP/CREATE TABLE).
Checksums always on for SYSTEM tablespace allowing many possible corruptions to be fixed before writing to disk.
XML Parser for Java.
New PLSQL encrypt/decrypt package introduced.
User and Schemas separated.
Numerous Performance Enhancements.

** Oracle 8i (8.1.5) **
Fast Start recovery – Checkpoint rate auto-adjusted to meet roll forward criteria
Reorganize indexes/index only tables which users accessing data – Online index rebuilds
Log Miner introduced – Allows on-line or archived redo logs to be viewed via SQL
OPS Cache Fusion introduced avoiding disk I/O during cross-node communication
Advanced Queueing improvements (security, performance, OO4O support
User Security Improvements – more centralisation, single enterprise user, users/roles across multiple databases.
Virtual private database
JAVA stored procedures (Oracle Java VM)
Oracle iFS
Resource Management using priorities – resource classes
Hash and Composite partitioned table types
SQL*Loader direct load API
Copy optimizer statistics across databases to ensure same access paths across different environments.
Standby Database – Auto shipping and application of redo logs. Read Only queries on standby database allowed.
Enterprise Manager v2 delivered
NLS – Euro Symbol supported
Analyze tables in parallel
Temporary tables supported.
Net8 support for SSL, HTTP, HOP protocols
Transportable tablespaces between databases
Locally managed tablespaces – automatic sizing of extents, elimination of tablespace fragmentation, tablespace information managed in tablespace (i.e moved from data dictionary) improving tablespace reliability
Drop Column on table (Finally !!!!!)
DBMS_DEBUG PL/SQL package, DBMS_SQL replaced by new EXECUTE IMMEDIATE statement
Progress Monitor to track long running DML, DDL
Functional Indexes – NLS, case insensitive, descending.

** Oracle 8.0 – June 1997 **
Object Relational database
Object Types (not just date, character, number as in v7
SQL3 standard
Call external procedures
LOB >1 per table
Partitioned Tables and Indexes
export/import individual partitions
partitions in multiple tablespaces
Online/offline, backup/recover individual partitions
merge/balance partitions
Advanced Queuing for message handling
Many performance improvements to SQL/PLSQL/OCI making more efficient use of CPU/Memory. V7 limits extended (e.g. 1000 columns/table, 4000 bytes VARCHAR2)
Parallel DML statements
Connection Pooling ( uses the physical connection for idle users and transparently re-establishes the connection when needed) to support more concurrent users.
Improved “STAR” Query optimizer
Integrated Distributed Lock Manager in Oracle PS (as opposed to Operating system DLM in v7).
Performance improvements in OPS – global V$ views introduced across all instances, transparent failover to a new node
Data Cartridges introduced on database (e.g. image, video, context, time, spatial)
Backup/Recovery improvements – Tablespace point in time recovery, incremental backups, parallel backup/recovery. Recovery manager introduced
Security Server introduced for central user administration. User password expiry, password profiles, allow custom password scheme. Privileged database links (no need for password to be stored)
Fast Refresh for complex snapshots, parallel replication, PL/SQL replication code moved in to Oracle kernel. Replication manager introduced.
Index Organized tables
Deferred integrity constraint checking (deferred until end of transaction instead of end of statement).
SQL*Net replaced by Net8
Reverse Key indexes
Any VIEW updateable
New ROWID format

** Oracle 7.3 **
Partitioned Views
Bitmapped Indexes
Asynchronous read ahead for table scans
Standby Database
Deferred transaction recovery on instance startup
Updatable Join Views (with restrictions)
SQLDBA no longer shipped.
Index rebuilds
db_verify introduced
Context Option
Spatial Data Option
Tablespaces changes – Coalesce, Temporary Permanent,
Trigger compilation, debug
Unlimited extents on STORAGE clause.
Some init.ora parameters modifiable – TIMED_STATISTICS
HASH Joins, Antijoins
Histograms
Dependencies
Oracle Trace
Advanced Replication Object Groups
PL/SQL – UTL_FILE

** Oracle 7.2 **
Resizable, autoextend data files
Shrink Rollback Segments manually
Create table, index UNRECOVERABLE
Subquery in FROM clause
PL/SQL wrapper
PL/SQL Cursor variables
Checksums – DB_BLOCK_CHECKSUM, LOG_BLOCK_CHECKSUM
Parallel create table
Job Queues – DBMS_JOB
DBMS_SPACE
DBMS Application Info
Sorting Improvements – SORT_DIRECT_WRITES

** Oracle 7.1 **
ANSI/ISO SQL92 Entry Level
Advanced Replication – Symmetric Data replication
Snapshot Refresh Groups
Parallel Recovery
Dynamic SQL – DBMS_SQL
Parallel Query Options – query, index creation, data loading
Server Manager introduced
Read Only tablespaces

** Oracle 7.0 – June 1992 **
Database Integrity Constraints (primary, foreign keys, check constraints, default values)
Stored procedures and functions, procedure packages
Database Triggers
View compilation
User defined SQL functions
Role based security
Multiple Redo members – mirrored online redo log files
Resource Limits – Profiles
Much enhanced Auditing
Enhanced Distributed database functionality – INSERTS, UPDATES,DELETES, 2PC
Incomplete database recovery (e.g SCN)
Cost based optimiser
TRUNCATE tables
Datatype changes (i.e VARCHAR2 CHAR, VARCHAR)
SQL*Net v2, MTS
Checkpoint process
Data replication – Snapshots

** Oracle 6.2 **
Oracle Parallel Server

** Oracle 6 – July 1988 **
Row-level locking
On-line database backups
PL/SQL in the database

** Oracle 5.1 **
Distributed queries

** Oracle 5.0 – 1986 **
Supporting for the Client-Server model – PC’s can access the DB on remote host

** Oracle 4 – 1984 **
Read consistency

** Oracle 3 – 1981 **
Atomic execution of SQL statements and transactions (COMMIT and ROLLBACK of transactions)
Nonblocking queries (no more read locks)
Re-written in the C Programming Language

** Oracle 2 – 1979 **
First public releaseBasic SQL functionality, queries and joins

[Read Me]Steps in Processing an SQL Statement

Before going through this article.Plz refer to the oracle 9i architecture.. for better understanding.

@> Assume a user issues an update stmt on the table such that more than 1 user is affected by the update .This stmt is passed to the USER process .Then the server(rather Query processor )will check whether the SQL stmt is already contained in the library cache such that corresponding info(parse tree, execution plan) can b used .If the stmt can not be found , it is parsed aftr verifying the stmt (usr privileges, affected tables and columns) using data from the data dictionary cache, a query execution plan is generated by the query optimizer .Together with the parse tree, this plan is stored in the library cache.

@> For the objects affected by the stmt (table) it is cheked, whether the corresponding data blocks already exist in the database buffer .If not , the user process reads the data blocks into the database buffer .If thr is not enough space in the buffer , the least recently used blocks of other objects are written back to the disk by the “DBWR” process.

@> The modifications of the tuples affected by the update, occurs in the database buffer.Before the database blocks are modified the “before image” of the tuples is written to the rollback segments by the DBWR process.

@> While the redo-log buffer is filled during the data blocks modifications ,LGWR process writes entries from the redo-log buffer to the redo-log files.

@> Aftr all tuples (or data blocks ) have been modified in the database buffer , the modifications can be committed by the user using the commit command.

@> As long as no commit has been issued by the user , modifications can be undone using the rollback stmt. In this case , the modified data blocks in the database buffers are overwritten by the original blocks stored in the rollback segments.

@> If the user issues a commit , the space allotted for the blocks in the rollback segments is deallocated and can be used by other transactions . Furthermore the modifications in the database buffers are unlocked such that other users now can read the modified blocks.The end of the transaction (more precisely the commit) is recorded in the redo-log files.The modified blocks are only written to the disk by the DBWR process ,if the space allocated for the blocks is needed.

...Referred from Oracle Fundamentals