Tuesday, August 25, 2009

Database Features: 8i vs 9i vs 10G vs 10GR2

Database Features: 8i vs 9i vs 10G vs 10GR2:
Here is an article which will give you an idea about the major differences in features for various versions of Oracle databases:
Oracle 8i Features:
Auditing - Basic setup instructions to allow the auditing of user actions.

Cost Based Optimizer (CBO) And Database Statistics - Lists the available mechanisms for gathering database statistics that are used by the cost based optimizer.

Dropping Columns - Hide unused columns or completely remove them.

Index Organized Tables - Save space and improve the performance of tables which are predominantly access by their primary keys.

Locally Managed Tablespaces - Reduce contention on the system tables by delegating extent management to individual tablespaces.

LogMiner - Analyze archived redo logs to identify specific specific statements and undo them.

Partitioned Tables And Indexes - Reduce tables and indexes to a more managable size and simultaneously improve performance.

Recovery Manager (RMAN) - Explanation of RMANs basic backup, recovery and reporting functionality.

Refreshing Stale Statistics - Keep your optimizer statistics up to date without increasing the load on the server.

Replication (Simple) - Using materialized views (snapshots) to replicate data on a remote database.

Replication (Advanced) - Using Oracle Advanced Replication for two-way data replication.

Statspack - Analyze the performance of your system using the replacement for the UTLBSTAT/UTLESTAT scripts.

Standby Database - Keep an up-to-date copy of the production database ready for failover in the event of a disaster.

Temporary Tables - Most applications need temporary tables to store information during complex data processing. See how Oracle can manage your temporary tables and keep your data secure.
Oracle9i New Features:
Automatic Segment Free Space Management - Reduce segment header contention and wasted space within blocks by switching from using FreeLists to Automatic Segment Free Space Management.

Automatic Undo Management - Replace you existing rollback segments with self-tuning undo segments.

Bitmap Join Indexes - Increase the performance and reduce the size of your data warehouse.

Data Guard - Oracle9i Data Guard is the new name for Oracle8i Standby Database incorporating a large number of new features.

Flashback Query - Get a consistent view of your data at a previous point in time.

Memory Management In Oracle9i - See how easy memory management has become in Oracle9i.

Multiple Block Sizes - Improve efficiency by having multiple block sizes that relate more closely to the operations that are being performed.

Oracle Managed Files - Let Oracle manage operating system level file placement, naming and cleanup.

Real Application Clusters - A brief introduction to the Oracle9i replacement for Oracle Parallel Server.

Recovery Enhancements In Oracle9i - Reduce unplanned downtime by using the new crash, instance and media recovery features of Oracle9i.

Recovery Manager (RMAN) Enhancements In Oracle9i - Use the latest RMAN features which make backup and recovery quicker and more reliable.

DBNEWID Utility - Change the internal DBID and the database name using this new utility without rebuilding your controlfile.

DBMS_XPLAN - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.

Export BLOB Contents Using UTL_FILE - Use the new UTL_FILE functionality to write binary data to files.

STATISTICS_LEVEL - Let Oracle9i Release 2 control the collection of statistics and advisories with a single parameter.
Oracle 10G New Features:
Flashback Versions Query-

Tablespace Management-


Oracle Data Pump-
create directory dpdata1 as '/u02/dpdata1';
grant read, write on directory dpdata1 to ananda;

* Data Pump Export
expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT
* With Parallel Operation
dumpfile=expCASES_%U.dmp parallel=4
* Data Pump Import
impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import

Flashback Table-
drop table recycletest;
show recyclebin

Managing the Recycle Bin-

Other Uses of Flashback Tables-

Automatic Workload Repository-

Automatic Storage Management-

Incremental Backups Revisited by block change tracking enabled:
alter database enable block change tracking using file '/rman_bkups/change.log';

Flash Recovery Area-
alter system set db_recovery_file_dest = '/ora_flash_area';
alter system set db_recovery_file_dest_size = 2g;
alter system set db_flashback_retention_target = 1440;
alter database flashback on;

Incremental Merge-
backup incremental level_1 for recover of copy with tag level_0 database;

* Compressed Files
backup as compressed backupset incremental level 1 database;
* Recovery Preview
restore database preview;
* Resetlogs and Recovery

RMAN can now readily use all backups, before and after a resetlogs operation, to recover the Oracle database. There is no need to shut down the database to make a backup. This new capability means that the database can be re-opened immediately for the user community after a resetlogs operation.

Enterprise Manager 10g-
Ultra Search HTTP port number = 5620
iSQL*Plus HTTP port number = 5560
Enterprise Manager Console HTTP Port (starz10) = 5500
Enterprise Manager Agent Port (starz10) = 1830

Automatic Segment Management-
High Water Mark to Low Water Mark:
alter table bookings enable row movement;
alter table bookings shrink space compact;
alter table bookings shrink space cascade;

Transportable Tablespaces-

Automatic Shared Memory Management-

ADDM and SQL Tuning Advisor-

10G R2 new features
Manageability Features:
• ASM Command Line Tool
• Drop Empty Datafiles
• Direct SGA Access for Hung/Slow Systems
• Faster Startup
• Manage Multiple Objects in Oracle Enterprise Manager
• Automatic Segment Advisor

Backup and Availability Features
• Oracle Secure Backup
• Dynamic RMAN Views for Past and Current Jobs
• Dynamic Channel Allocation for Oracle RAC Clusters
• Tempfiles Recovery via RMAN
• Flashback Database/Query Through RESETLOGS
• Flashback Database Restore Points
• Flash Recovery Area View

No comments:

Post a Comment