Oracle Database Upgrade 10g to 11g

Upgrading Oracle 10.2.0.1 to 11.1.0.6

Before Starting the steps for Oracle Database Upgrade, would like to give some clear picture on What is Oracle Database Upgrade?

                    Oracle Upgrade is a process of changing the environment of the database without modifying the existing data and configurations to avail a robust features in the latest version and releases of the Oracle product, that internally will delivers more automation, new features, and flexibility so that the data can be secure
                    Technically, Oracle Upgrade means changing the versions or the releases of the Oracle database within the same or in different servers or with different OS platforms (ex: version 9i to 10g, 10g to 11g, and also the release 10.1 to 10.2, 11.1 to 11.2) to the higher version/release that intern will upgrade the existing physical files state to the newer release/version

                   For upgrading, we will be using startup upgrade command. The startup migrate is deprecated from Oracle 10g for migration (Migration means moving the database from one OS platform to another, or from lower version/release to higher version/release as like upgrade, but the main difference here is we are logically extracting the physical files contents of data and we are loading it back to the higher version/release within the same or across the different OS servers, here the physical files state does not change as like upgrade)

                 For More understanding on Oracle Version number, Let say the oracle version # 11.2.0.1.0, Then 11 is major database release number, 2 is database maintenance  release number, 0 is Application release number, 1 is component specific  release number, 0 is platform specific release number.

If we upgrade the database, the first number and second number will change. The Fourth digit indicates the Patch number, This number will change whenever we apply patch to database.

Types of Upgrade:

1. Manual Upgrade by running the script manually in Proper order.
2. Using Database Upgrade Assistant (DBUA) utility.

Manual Upgrade:

Prerequisite:

We should have the Oracle Database 10g and here we are upgrading to Oracle 11g (11.1.0.6)

Step 1: Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle home

For Eg: If 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0 the the 11g oracle home should be /u01/app/oracle/oracle/product/11.1.0

Start the installation using below command

./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst

The GUI screen will open for Installation and follow the steps. DO NOT give Yes for "Upgrade as existing database" Option. At the end of the installation. installer will ask to run root.sh script. DO NOT press OK button. Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle database 11g.

Step 2: Pre-Upgrade Utility

The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 11g and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool:

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location

[oracle]$cd $ORACLE_HOME
[oracle]$cd rdbms/admin
[oracle]$pwd
/u01/app/oracle/oracle/product/11.1.0/db_1/rdbms/admin
[oracle]$cp utlu111i.sql /tmp

The utility will give the output in the form of recommendation to be implemented before starting the upgrade. Unless these requirement are met, the upgrade will fail.

Login to the 10g Oracle database and run the above sql which is copied.

SQL> @/tmp/utlu111i.sql

Oracle Database 11.1 Pre-Upgrade Information Tool 02-11-2011 16:16:52

**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 693 MB
…. AUTOEXTEND additional space required: 243 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 262 MB
…. AUTOEXTEND additional space required: 62 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 438 MB
…. AUTOEXTEND additional space required: 178 MB
–> DEF_TEMP tablespace is adequate for the upgrade.

…. minimum required size: 61 MB

**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
– No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
– No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters:[Update Oracle Database11.1 init.ora or spfile]
**********************************************************************
–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID

–> OLAP Analytic Workspace [upgrade] VALID

–> OLAP Catalog [upgrade] VALID
–> EM Repository [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Spatial [upgrade] VALID
–> Data Mining [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
–> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. OLAPSYS

…. SYSMAN

WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.
WARNING: –> EM Database Control Repository exists in the database.
…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.

This scripts will generates to check whether all the properties will match during upgradings,

Tablespaces: Verifies that the tablespaces are suitable to upgrade, if any problems of space, increase the size of tablespaces

Update parameters: Check the update parameters from 10.2.0.1 to 11.1.0.6

Renamed Parameters: Check the renamed parameters in 11.1.0.6

Obsolete/Deprecated parameters: Remove the parameters which are deprecated in the 11.1.0.6
version. More about the deprecated parameters, refer the Oracle 11g documentation

Components: Verify all the components are VALID, if they show INVALID, run ‘utlrp.sql‘ from
Oracle10g’s ORACLE_HOME to make them VALID.

Miscellaneous Warnings: The timezone file version should be upgraded from 2 to 4.

The remaining miscellaneous warnings can be ignored.


Step 3: Executing the recommended steps

Following are the critical steps to be executed based on the above warnings. These command are to be executed while connecting to database from 10g Oracle home.

a) TimeZone Issue

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the version of existing timezone files

SQL> select * from v$timezone_file;

For 10.2.0.1, check the metalink note ID 413671.1. The table which defines the patch to be applied to upgrade the timezone.

If there is no official patchset for the version you are currently having then you can Identify patch for a different patchset, but with same release.

For example if you run 10.2.0.1 and there is no particular patch release. In this case we can make use of patch 5632264 for 10.2.0.2 and 10.2.0.3, and this will be applicable to 10.2.0.1 as well.
Please follow the metalink note ID 396387.1

Once you identify the correct patchset (5632264 for 10.2.X), download the same and unzip it.

1. Unzip the patch file p5632264_10203_Linux-x86-64.zip
          $ unzip p5632264_10203_Linux-x86-64.zip
2. Change directory to the 5632264 and list, which will contain the timezone files
3. Backup the existing timezone files in the Oracle10g’s ORACLE_HOME, these will be present under
         $ORACLE_HOME/oracore/zoneinfo directory
4. Copy the newly extracted files to the ORACLE_HOME path
         $ cp -r 5632264/files/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo
5. If the new timezone changes has to come to an effect, restart the database and select the timezone
         SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4

b) Optimizer Statistics Warning

WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS

…. SYSMAN

Gather the dictionary statistics by executing the PL/SQL blocks

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘OLAPSYS’);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);
PL/SQL procedure successfully completed.

Step 4: Rerun the Pre-Upgrade Utility

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.

Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home. If every thing looks fine, Shut down the database from 10g Oracle Home


This time make sure you don't have the critical warnings like the one with TIMEZONE version.

Step 5: Backup the Oracle 10g Database

Perform the Oracle 10g database backup in any of the methods like cold backup, hot backup or RMAN backups. Backup the ORACLE_HOME software path too.

Cold backup : http://engistan.com/oracle-11g-cold-backup-with-restore/
Hot backup : http://www.oracledistilled.com/oracle-database/backup-and-recovery/user-managed-online-backups-hot-backup/
RMAN Backup : http://psoug.org/reference/rman_demos.html

Step 6: Create Parameter file for Oracle 11g

Backup the Oracle10g’s parameter file and paste under Oracle11g’s default path

$ cp /u01/app/o10g/product/10.2.0/db_1/dbs/initorcl.ora
/u01/app/o11g/product/11.1.0/db_1/dbs/initorcl.ora


Edit the Oracle 11g’s parameter file to remove the Obsolete/Deprecated parameters, as listed in the output of Pre-Upgrade Utility.

Step 7: Upgrade the database

1. If the Oracle10g database is still running, shutdown the database
2. Export the variables for Oracle 11g

$ export ORACLE_SID=orcl
$ export ORACLE_BASE=/u01/app/o11g

$ export ORACLE_HOME=/u01/app/o11g/product/11.1.0/db_1
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export PATH=$ORACLE_HOME/bin:$PATH

3. Start the SQL*Plus with sysdba privilege
4. Start the Oracle11g database using the ‘startup upgrade‘

SQL> startup upgrade

5. Verify the version of Oracle

SQL> select * from v$version;
BANNER
———————————————————————-
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

6. Upgrade the data dictionary tables via the catupgrd.sql script.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

From hereafter the database downtime will starts and the upgrading a database will take its own time to completes depending upon the size of the database and environment.
Once the upgrades completes, the database will shutdown automatically



Step 8: Post Upgrading Steps

1. Once the upgrade completed, reinitialize the system parameters for normal startup and bounce back the database by login again as sys with sysdba privilege, and start the database to normal mode

SQL> startup

2. Check the dba_registry for the components and its status

SQL> select comp_name, status, version from dba_registry;

3. Run ‘utlu111s.sql‘ script to display the results of the upgrade

SQL> @$ORACLE_HOME/rdbms/admin/utlu111s.sql

4. Run ‘catuppst.sql‘ script from $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that change the database from UPGRADE mode to the NORMAL mode

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql

5. Find for the invalid objects after the upgrade

SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———
1572

6. Run ‘$ORACLE_HOME/rdbms/admin/utlrp.sql‘ script to recompile the INVALID objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

7. Again find the status for invalid objects after recompiling them

SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0

This will completes the manually upgrading the Oracle database from 10.2.0.1 to 11.1.0.6

Using the Database Upgrade Assistant (DBUA):

You can start the Database Upgrade Assistant (DBUA) via the shipped utility in Oracle 11g, the dbua command. The scripts we had executing in the Manually in the previous method will be executed and taken care by the DBUA utility

Follow the steps to upgrade the Oracle 10.2.0.1 database to 11.1.0.6:
1. Login in as Oracle 11g user, and initiate all the variables need for the environment
$ export ORACLE_BASE=/u01/app/o11g
$ export ORACLE_HOME=/u01/app/o11g/product/11.1.0/db_1
$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
$ export PATH=$ORACLE_HOME/bin:$PATH
2. Confirm the Oracle 10g database name and ORACLE_HOME path is entered in /etc/oratab file
$ cat /etc/oratab
orcl:/u01/app/o10g/product/10.2.0/db_1:N
3. Execute the dbca utility from Oracle 11g’s path
$ $ORACLE_HOME/bin/dbua
4. A Welcome screen will appear, click Next

 5. Select the database you want to upgrade from the list of available databases. You can upgrade only one database at a time. For all the list of databases, the database names should be listed in /etc/oratab file (Step 2). Click Next

6. The dbua utility will check the warnings should be taken care during the upgrading

Here again we need to solve the warning like update the timezone file version as a critical warning, and remaining warning you can skip. If you continue with this screen, at the point of in time the dbua will through an error and the upgrading the database will fail. Its a better practice to solve the warnings.

For Timezone issue and Optimizer Statistics warning , please follow Step 3 in Manual Upgrade process

Gather the dictionary statistics by executing DBMS_STATS.GATHER_SCHEMA_STATS for all the users as per the warning. This will solve the main warnings that got from the above step, the remaining warning can be ignored. Rerun the dbua utility again and clear the previous steps, now the warnings will be ease to upgrading the database, you can ignore these warnings

Click ‘Yes’, this will continue with the further steps of upgrading.

7. The next screen will allow to select the ORACLE_BASE and the Diagnostic Destination, Click Next

8. You can then select to have your database files moved during the upgrade process. Click Next

9. DBUA will then prompt you for a flash recovery area destination for the storage of backup and recovery-related files. Click Next

10. DBUA will then prompt you to recompile invalid PL/SQL objects following the upgrade. If you do not recompile these objects after the upgrade, the first user of these objects will be forced to wait while Oracle performs a run-time recompilation. Click Next

11. DBUA will prompt for back up the database as part of the upgrade process. If already backed up the database prior to starting DBUA, elect to skip this step. If you choose to have DBUA back up the database, it will shut down the database and perform an offline backup. DBUA will also create a batch file in that directory to automate the restoration of those files to their earlier locations. Click Next

 12. If Oracle detects multiple Oracle Net listeners on the server, you will then be prompted to select a listener for your database, and the network configuration details will be displayed for your review and editing. Click Continue

13. A final summary screen displays your choices for the upgrade, and the upgrade starts when you
accept them. Click Finish

14. The DBUA will start upgrading the database, the upgrading logs can be read in the ORACLE_BASE path

15. The DBUA will complete the upgrading the database, Click ‘OK’

16. After the upgrade has completed, DBUA will display the Upgrade Results screen, showing the steps performed, the related log files, and the status.

17. The section of the screen titled Password Management allows you to manage the passwords and the locked/unlocked status of accounts in the upgraded database.

If you are not satisfied with the upgrade results, you can choose the Restore option. If you used DBUA to perform the backup, the restoration will be performed automatically; otherwise, you will need to perform the restoration manually.

When you exit DBUA after successfully upgrading the database, DBUA removes the old database’s
entry in the network listener configuration file, inserts an entry for the upgraded database, and reloads the file.

Following the upgrade, you should double-check the configuration and parameter files related to the
database, particularly if the instance name changed in the migration process. These files include

The tnsnames.ora file
The listener.ora file

How to Resize redo log file

Re-sizing the Redo Log file in Oracle:

When people ask (especially in interview) "how to resize the redo log file in oracle", every one may get confused and people will start thinking about the command to resize the log file. before starting this blog, let me make it clear that WE CANNOT RESIZE THE REDO LOG FILE USING SINGLE COMMAND, WE NEED TO DROP THE EXISTING REDO LOG FILE AND RECREATE THE REDO LOG FILE. This is the only method to resize the redo log file in oracle.

Below are the steps :

Before dropping the redo log file we need to keep the below this in mind:

1. A database requires atleast two redo log file, regardless number of members
2. We cannot drop the active or current redo log file
3. We have to change the active and current redo log file to inactive and then we can drop the redo log file

In  below example i have 4 redo log files which is of 50MB and i will resizing it to 100MB

Step 1: Check the status of Redo log file

SQL>  select group#,sequence#,bytes,archived,status from v$log;

              GROUP#  SEQUENCE#      BYTES    ARC    STATUS
              ----------    ----------    ----------      -----       -------------
                    1          5   52428800      YES          INACTIVE
                    2          6   52428800      YES          ACTIVE
                    3          7   52428800      NO          CURRENT
                    4          4   52428800     YES          INACTIVE

So as i mentioned earlier that we cannot drop the active and current redo log file, we cannot drop the group # 2 and 3 


Step 2: Forcing the checkpoint

There are two SQL command to force the checkpoint :

1. alter system checkpoint;
2. alter system checkpoint global;

The first command will explicitly forces oracle to perform a checkpoint for either the current instance and all instances. Forcing the checkpoint ensured that all changes to the database buffers are written  to the data files. The 2nd sql command i.e., global checkpoint is not finished until all instance that requires recovery have been recovered.

SQL> alter system checkpoint global ;

system altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

    GROUP#    SEQUENCE#        BYTES    ARC       STATUS
----------    ----------    ----------    -----     ----------------
         1          5       52428800     YES      INACTIVE
         2          6      52428800     YES       INACTIVE
         3          7      52428800     NO       CURRENT
         4          4      52428800    YES       INACTIVE

Since the status of group 1,2,4 are inactive .so we will drop the group 1 and group 2 redo log file.

Step 3: Drop the redo log file

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES    ARC    STATUS
----------    ----------    ----------    ---     ----------------
         3          7               52428800      NO       CURRENT
         4          4             52428800       YES      INACTIVE

Make sure that you delete the redo log file  by OS command before creating the log file. else we will face the below error:

SQL> alter database add logfile group 1 '\u01\phil\oradata\orcl\redo01.log' size 100m;
alter database add logfile group 1 '\u01\phil\oradata\orcl\redo01.log' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file '\u01\phil\oradata\orcl\redo01.log' - file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Step 4: Create a new log file

SQL> alter database add logfile group 1 '\u01\phil\oradata\orcl\redo01.log' size 100m;
Database altered.

SQL> alter database add logfile group 2 '\u01\phil\oradata\orcl\redo02.log' size 100m;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#      SEQUENCE#      BYTES     ARC       STATUS
----------    ----------     ----------       ---      ----------------
         1          0      104857600       YES     UNUSED
         2          0      104857600       YES     UNUSED
         3          7       52428800        NO      CURRENT
         4          4       52428800       YES      INACTIVE

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8  104857600     YES     ACTIVE
         2          9  104857600     NO      CURRENT
         3          7   52428800     YES     ACTIVE
         4          4   52428800     YES     INACTIVE

SQL> alter system checkpoint global;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8    104857600     YES     INACTIVE
         2          9    104857600     NO     CURRENT
         3          7     52428800     YES     INACTIVE
         4          4     52428800    YES      INACTIVE

Step 5 : Now drop the remaining two redo log file

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8  104857600      YES      INACTIVE
         2          9  104857600      NO       CURRENT

Step 6: Create the redo log file now

SQL> alter database add logfile group 3 '\u01\phil\oradata\orcl\redo03.log' size 100m;
Database altered.

SQL> alter database add logfile group 4 '\u01\phil\oradata\orcl\redo03.log' size 100m;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8        104857600      YES       INACTIVE
         2          9        104857600      NO        CURRENT
         3          0        104857600     YES        UNUSED
         4          0        104857600     YES        UNUSED

SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

I think the below details also will be relevant for this post. i.e. status of redo log file. The log file has below status :

USED:  Indicates that the log file has been just added

CURRENT: Indicates the valid log which is currently in use.

ACTIVE: Indicates the valid log which is currently not in use.

INACTIVE: Indicates that the log is no longer required for recovery.

Hope above post helps. And always, any mistake or if i miss any point please let me know :)

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.