Difference Between Oracle 10g and 11g

1-  Simplified and improved automatic memory management

Oracle 9i automated PGA management by introducing PGA_AGGREGATE_TARGET parameter. Oracle 10g continued this trend by automating SGA management using the SGA_TARGET parameter.

Oracle 11g takes this one step further by allowing you to allocate one chunk of memory, which Oracle uses to dynamically manage both the SGA and PGA.

·       MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".

·       MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

2- New fault diagnosability infrastructure to prevent, detect, diagnose, and help resolve critical database errors.

When a critical error occurs, it is assigned an incident number, and diagnostic data for the error (such as trace files) are immediately captured and tagged with this number. The data is then stored in the Automatic Diagnostic Repository (ADR)—a file-based repository outside the database—where it can later be retrieved by incident number and analyzed.

The goals of the fault diagnosability infrastructure are the following:

·       First-failure diagnosis

·       Problem prevention

·       Limiting damage and interruptions after a problem is detected

·       Reducing problem diagnostic time

·       Reducing problem resolution time

·       Simplifying customer interaction with Oracle Support

SELECT * FROM V$DIAG_INFO;
INST_ID	NAME	VALUE
1	Diag Enabled	TRUE
1	ADR Base	/u01/oracle
1	ADR Home	/u01/oracle/diag/rdbms/orcl/orci
1	Diag Trace	/u01/oracle/diag/rdbms/orcl/orci/trace
1	Diag Alert	/u01/oracle/diag/rdbms/orcl/orci/alert
1	Diag Incident	/u01/oracle/diag/rdbms/orcl/orc/incident
1	Diag Cdump	/u01/oracle/diag/rdbms/orcl/orcl/cdump
1	Health Monitor	/u01/oracle/diag/rdbms/orcl/orcl/hm
1	Default Trace File	/u01/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22769.trc
1	Active Problem Count	8
1	Active Incident Count	20

To obtain a list of health check names, run the following query:

SELECT name FROM v$hm_check WHERE internal_check='N';

NAME

------------------------------------

DB Structure Integrity Check Data Block Integrity Check Redo Integrity Check Transaction Integrity Check Undo Segment Integrity Check Dictionary Integrity Check

To run a Health Monitor Checker using Enterprise Manager

^ü       On the Database Home page, in the Related Links section, click Advisor Central.

^ü        Click Checkers to view the Checkers subpage

^ü        In the Checkers section, click the checker you want to run.

^ü      Enter values for input parameters or, for optional parameters, leave them blank to accept the defaults.

^ü       Click Run, confirm your parameters, and click Run again.

3-  Invisible Indexes

If the usage of the index is at all, or it results a bad performance to other queries, one can easily drop the index. However in future, if some adhoc queries require the index badly (where the index dropped would have increased the performance dramatically), we need to think reinstating the index again. The recreation of an index is a very expensive one and requires a lot of database resources and consumes a lot of time as well.

Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

CREATE INDEX index_name ON table_name(column_name) INVISIBLE;

ALTER INDEX index_name INVISIBLE;

ALTER INDEX index_name VISIBLE;

SELECT * FROM EMPLOYEES WHERE EMPID = 1001; (Full table scanning)

Adhoc queries requiring the index using index hint:

SELECT /*+ index(index_name) */ * FROM EMPLOYEES WHERE EMPID = 1001;

4-  Virtual columns

When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc.

The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the data type is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.

CREATE TABLE EMPLOYEES

(

id          NUMBER,

first_name VARCHAR2(10),

last_name  VARCHAR2(10),

salary        NUMBER(9,2),

comm1    NUMBER(3),

comm2    NUMBER(3),

salary1 AS (ROUND(salary*(1+comm1/100),2)),

salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,

CONSTRAINT employees_pk PRIMARY KEY (id)

);

Moreover, virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.

In my opinion it’s the optimizer statistics that are likely to be the most common benefit of virtual columns.

5- Enhanced security for password-based authentication by enabling use of mixed case in passwords.

Passwords are case sensitive in Oracle 11g, while user names are still case insensitive as before. To support mixed case password names, the orapwd program has been modified to allow or disallow case-sensitive passwords and the ignorecase parameter has been added to the command line. Ignorecase should be set to Y to enable password case sensitivity for SYS and SYSDBA connections.

ALTER SYSTEM set sec_case_sensitive_logon = false;

ALTER SYSTEM set sec_case_sensitive_logon = true;

6-  Tablespace-level encryption

The Transparent Data Encryption (TDE) feature was introduced in Oracle 10g Database Release 2 to simplify the encryption of data within datafiles, preventing access to it from the operating system. Tablespace encryption extends this technology, allowing encryption of the entire contents of a tablespace, rather than having to configure encryption on a column-by-column basis.