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.
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Wednesday, January 12, 2011
Saturday, June 6, 2009
[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
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
@> 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
Subscribe to:
Posts (Atom)