Summary Of Differences Between oracle Database 9i and 10g.
The below Points are completely New in oracle database 10g (Not there in oracle 9i)
  • Automatic Storage Management(ASM).
  • Automatic Shared Memory Management(ASMM).
  • Automatic Database Diagnostic Monitor(ADDM).
  • Automatic Workload Repository(AWR).
  • Flashback Technologies.
  • Data Pump replaces Traditional EXP/IMP.
  • Automatic Checkpoint Tunning(FAST_START_MTTR_TARGET).
  • Automatic Undo Retention Tunning.
  • Introduced Default Permanent Tablespace (USERS).
  • Introduced SYSAUX tablespace.
  • Streams Technology(STREAMS POOL).
  • Introduced Big file Tablespace Option and Rename Tablespace Command.
  • Automatic SQL Tunning.
  • Temporary Tablespace Group and Default Temporary Tablespace.
  • Recovery Manager Enhancements(RMAN).
  • DBMS Scheduler Packages and DBMS File Transfer Packages.
Detailed Explanation of the above summary of differences  between oracle database 10g and 9i.
Automatic Storage Management(ASM).
ASM  means Automatic Storage Management to simplify the Storage of datafiles , controlfiles and  Redolog files.ASM  is the extension of OMF (oracle managed files).ASM functionality is controlled by ASM instance.It is not full database instance its just  a memory structures.
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files.
ASM provides the following the functionalities
  • Manages groups of disks, called disk groups.
  • Manages disk redundancy within a disk group
  • Supports large files.
Initialization Parameters and ASM instance Creation
  • The initialization parameters that are of specific interest for an ASM instance are
  • INSTANCE_TYPE – Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
  • DB_UNIQUE_NAME – Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
  • ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
  • ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
  • ASM_DISKSTRING – Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Automatic Shared Memory Management(ASMM).
Oracle database 10g’s New features.By default sga_target  is enabled.But  always sga_max_size is greater than or equal to sga_target.So once sga_target is enabled means, no need to set the auto tuned parameters like
  1. db_cache_size
  2. shared_pool_size
  3. large_pool_size
  4. java_pool_size
So ASMM automatically readjusts the sizes of the main pools.
Automatic Database Diagnostic Monitor(ADDM).
ADDM means automatic database diagnostic monitor and is a oracle database 10g’s new features. ADDM does analysis of the database, identifies problems and their potential causes, and comes up with recommendations for fixing the problems. It can call all other advisors also.
ADDM stores the snapshot in SYSAUX tablespace.
The main features of the ADDM are as follows
  • ADDM runs automatically in the background process MMON whenever a snapshot of in-memory statistics is taken. ADDM does analysis of the statistics collected between two snapshots.
  • ADDM analysis results are written back to the workload repository for further use.
  • ADDM uses the new wait and time statistics model, where activities with high time consumption are analyzed on a priority basis. This is where the big impact lies.
  • ADDM can also be invoked manually
The Automatic Database Diagnostic Monitor (ADDM) is a new diagnosis tool that runs automatically every hour, after the AWR takes a new snapshot. The ADDM uses the AWR performance snapshots to locate the root causes for poor performance and saves recommendations for improving performance in SYSAUX.
ADDM also gives the recommendations for
  • Sql tuning advisor
  • Sql access advisor
  • Segment advisor
  • Undo advisor
  • Redo log file size advisor
Automatic Workload Repository (AWR)
AWR is the oracle database 10g’s new features.Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR). In your database some repository tables are created automatically when database is created.Each and every seconds AWR collects issues and activities in the database.
The repository is a source of information for several other Oracle 10g features.
  • Automatic Database Diagnostic Monitor
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor
To check the AWR status, Show parameter statistics_level. If you want to enable the AWR report set statistics_level=Typical.If you want to disable the AWR report statistics_level=normal.
When you enable the AWR report the MMON (Manageability Monitor) background process will active.It will write issues in the repository tables. By default Every one hour AWR will generate a snapshot in SYSAUX tablespace and the default retention period is 7 days.If you want to take snapshot every 15 minutes and retention period 10 days means
begin
dbms_workload_repository.modify_snapshot_settings(
retention=>14400,       —–Minutes (=30 days). Current Value retained if null
interval=>15);              — Minutes .Current value retained if null.
End;
/

Flashback Technologies

Flashback technologies is the oracle database 10g’s new features.

Types of Flashback Technologies

There are six basic types of Flashback recovery, discussed below in detail:
  • Flashback Query
  • Flashback Version Query
  • Flashback Transaction Query
  • Flashback Table
  • Flashback Drop (Recycle Bin)
  • Flashback Database
How to Configure the flashback
In mount stage only we can enable the flashback technologies and  disable
SQL>alter database flashback on;         —–Enable
SQL>alter database flashback off;        ——–Disable
Flashback Query
You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.
his example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.
The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:
sql>select * from  employee as of timestamp to_timestamp(‘
2006-04-04 09:30:00′ , ‘YYYY-MM-DD HH:MI:SS ‘) where name=’JHON’;
this updates restores Jhon’s information to the employee table
sql> insert into employee (select * from employee as of timestamp to_timestamp(‘ 2006-04-04 09:30:00′ , ‘YYYY-MM-DD HH:MI:SS ‘) where name=’JHON’);
Flashback Version Query
Not only can the DBA run a manual analysis, but this is a powerful tool for the application’s developer as well. You can build customized applications for auditing purposes. Now everyone really is accountable for his or her actions. Various elements for this are shown below
Versions_xid-The transaction id that created this version of the row
Versions_operations-The action that created this version of the row (such as delete, insert, and update).
Versions_startscn-The SCN in which this row version first occurred
Versions_Endscn-The SCN in which this row version was changed
For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates <!–[if gte mso 9]–>For extended help options we can use the help extended command from within the ADR command tool and shown below
We can get detailed explanations of an option such as the BEGIN BACKUP command which will present a series of screens much like that found in the old UNIX man pages.
What you should finally realize at this point is that Oracle 11g has taken monitoring to a whole new level over 10g release. In fact, think of it as monitoring on steroids. We will next view an example of how to look at the log files for database monitoring with Oracle 11g on Red Hat Linux
Lets scroll down and see more options for the show alert option with ADRCI
As you can see, there are now options to review the old style alert.log files as well as the ability to view the log files from the Oracle listener and host. We can also parse the log file to see just a recent activity which is useful for quick monitoring tasks. Lets take a peek at one of the recent log files in the next example.
We choose option 1 and the alert log file is displayed below:
By default the log files are stored under the Linux /tmp directory structure.
To exit and return to the main ADR command screen we use the :q command within the Linux vi editor.
The ADR command tool also lets us view the new health monitor reports and functions.
For instance, if I wish to look at the most recent health monitor execution tasks then I can simply issue the show hm_run command within the ADR command interface
Also of note with Oracle 11g are several new V$ tables that correspond to the ADR and new health monitoring features:
V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_FINDING
V$HM_INFO
V$HM_RECOMMENDATION
V$HM_RUN
Online Patching
Online patching introduced in oracle database 11g. This simplifies administration, because no downtime is needed, and also results in a much quicker turnaround time for installing or de-installing Online Patches.
A regular RDBMS patch can require many minutes to install, since it requires instance shutdown, a relink, and instance startup. On the other hand, you can install an online patch in just a few seconds
Online patches are only applicable for Oracle RDBMS and not any other products. Online patches are currently not supported in Windows, and only supported on the following UNIX platforms for version 11.1.0.7.0 and later:
  • Linux x86
  • Linux x86_64
  • HP-UX Itanium (HP-UX 11.31 and later)
Solaris SPARC 64-bit (Solaris 10 and later)
Temporary Tablespace shrink
Oracle database 11g introduced the temporary tablesapce shrink feature.
SQL>alter tablespace temp1 shrink space;
This deallocates all the unused segments from the tablespace and shrinks it. After the above operation, you can check the view DBA_TEMP_FREE_SPACE to check how much the allocated space and free space currently is.
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ ---------------
TEMP            179306496       179306496      178257920