Difference Between Oracle 10g and 11g

Hi Friends,

In recents days many of my friends were asking the difference between Oracle 10g and Oracle 11g. Even though people aware that Oracle 12c has been release, they were eager to know more about 10g and 11g. SO though of posting this blog. 

Please note: This is upto my knowledge, please feel free to let me know difference which can been added here

So here it goes:

 Automation features include a SQL tuning advisor that automatically tunes SQL statements:

• Automatic Memory Tuning 

        Automatic PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was introduced in Oracle 10g. In 11g, all memory can be tuned automatically by setting one parameter

Automatic Shared Memory Management simplifies the configuration of the SGA. To use Automatic Shared Memory Management, set the SGA_TARGET initialization parameter to a nonzero value and setthe STATISTICS_LEVEL initialization parameter to TYPICAL or ALL. The value of the SGA_TARGET parameter should be set to the amount of memory that you want to dedicate for the SGA. In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools:

Database buffer cache (default pool)
Shared pool
Large pool
Java pool
Streams pool

If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

SGA_TARGET is a dynamic parameter that can be changed by accessing the SGA Size Advisor from the Memory Parameters SGA page in Oracle Enterprise Manager, or by querying the V$SGA_TARGET_ADVICE view and using the ALTER SYSTEM command. SGA_TARGET can be set less than or equal to the value of SGA_MAX_SIZE initialization parameter. Changes in the value of SGA_TARGET automatically resize the automatically tuned memory pools.

• SQL Performance Analyzer (Fully Automatic SQL Tuning):

Using SPA, you can tell 11g to automatically apply SQL profiles for statements where the suggested profile give 3-times better performance that the existing statement. The performance comparisons are done by a new administrative task during a user-specified maintenance window

• Automated Storage Load balancing

Oracle’s Automatic Storage Management (ASM) now enables a single storage pool to be shared by multiple databases for optimal load balancing. Shared disk storage resources can alternatively be assigned to individual databases and easily moved from one database to another as processing requirements change.

•  Automatic Diagnostic Repository

When critical errors are detected, Oracle automatically creates an “incident” ticket, notifying the DBA instantly.

Oracle11g High Availability & RAC new features

• Oracle 11g RAC parallel upgrades 

Oracle 11g promises to have a rolling upgrade features whereby RAC database can be upgraded without any downtime. Ellison first promised this feature in 2002, and it is a very challenging and complex 11g new feature.

 

• Oracle RAC load balancing advisor 

Starting in 10gr2 we see a RAC load balancing advisor utility. Oracle says that the 11g RAC load balancing advisor is only available with clients which use .NET, ODBC, or the Oracle Call Interface (OCI).
 

• ADDM for RAC 

Oracle will incorporate RAC into the automatic database diagnostic monitor, for cross-node advisories.

·         ADR command-line tool : Oracle Automatic Diagnostic repository (ADR) has a new command-line interface dubbed ADRCI, the ADR Command Interface

$adrci

adrci> set editor vi

adrci> show alert ( it will open alert in vi editor )

adrci> show alert -tail ( Similar to Unix tail command )

adrci> show alert -tail 200 ( Similar to Unix Command tail -200 )

adrci> show alert -tail -f ( Similar to Unix command tail -f )

To list all the "ORA-" error run following command

adrci> show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

Hot patching - Zero downtime patch application. 

Data Guard - Standby snapshot

The new standby snapshot feature allows you to encapsulate a snapshot for regression testing. You can collect a standby snapshot and move it into your QA database, ensuring that your regression test uses real production data.

Active Data Guard
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database.

With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.

Active Data guard Features:

1. Physical Standby with Real-time Query

2. Fast Incremental Backup on Physical Standby.

3. Automatic Block Repair.
Oracle 11g Installation – New Feature Support

• Choosing Oracle Base location

Oracle Base (set by the environment variable ORACLE_BASE) is the top-level directory for installing Oracle software. Oracle Universal Installer now allows you to specify and edit Oracle base location. Oracle recommends you to specify the same Oracle base for multiple Oracle homes. If you install Oracle database 11g software with the option of creating a database and you do not specify a value to ORACLE_BASE, the installation proceeds with the default value but a message will be logged in the alert log file. 

• Datafile and Flash Recovery Area Locations

By default, Datafiles and flash recovery area are located one level below the Oracle base location. In Oracle 10g, it is used to be saved under Oracle home directory. 

• Automatic Diagnostic Repository (ADR)

ADR is a single directory location for all error and trace data in the database. It replaces the traditional diagnostic directories such as bdump, cdump, and udump. ADR location is controlled by the new initialization parameter DIAGNOSTIC_DEST. Oracle recommends you choose the same ADR base for all Oracle products
New Initialization parameter:

• Memory Parameters

In Oracle 11g, the automatic memory management feature is developed. Both the system global area (SGA) and the program global area (PGA) will expand and shrink based on the instance demands. To enable this feature, you set the following new parameters: 

MEMORY_TARGET -- this parameter sets the system-wide usable memory that will be used by the instance for SGA and PGA.

MEMORY_MAX_TARGET -- this parameter sets the maximum value you can set for MEMORY_TARGET parameter.

• Automatic Diagnostic Repository (ADR)

ADR is a directory that points to all error data raised in the database. You set it by the new parameter DIAGNOSTIC_DEST. It replaces USER_DUMP_DEST, BACKGROUND_DUMP_DEST and CORE_DUMP_DEST parameters. 
DIAGNOSTIC_DEST defaults to the following value: $ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID 

If you haven’t set the ORACLE_BASE variable, the value of the DIAGNOSTIC_DEST parameter defaults to

$ORACLE_HOME/log.

• Security Parameters

Oracle 11g introduces two important security parameters. Following table illustrated those parameters and their usages:

SEC_CASE_SENSITIVE_LOGON -- to enable or disable password case-sensitivity

SEC_MAX_FAILED_LOGIN_ATTEMPTS -- Oracle drops the connection after the specified

Number of login attempts fails for any user.