What's New in Oracle 11g Release 2

Oracle 11g Release 2 (11.2.0) - Sept 2009

• A separate tool, named deinstall, introduced for deinstallation and deconfiguration of Oracle products. Oracle Universal Installer no longer removes Oracle software.


• chopt tool, a command-line utility, to configure the database options. Oracle Universal Installer no longer provides the custom installation option of individual components.


• Unusable indexes and index partitions no longer consume space in the database because they become segmentless.


• Complete IPv6 Support for JDBC Thin Clients.


• From this release, Oracle/ASM will Support 4KB Sector Disk Drives.


• Edition-based redefinition allows an application's database objects to be changed without interrupting the application's availability by making the changes in the privacy of a new edition.


• CREATE or REPLACE TYPE will allow FORCE option. The FORCE option can now be used in conjunction with the CREATE or REPLACE TYPE command.


• New SQL*Plus command SET EXITCOMMIT specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
  SET EXITC[OMMIT] {ON|OFF}


• LISTAGG Analytic Function
  This function making very easy to aggregate strings. It also allows us to order the elements in the concatenated list.
  COLUMN employees FORMAT A50
  SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;
  DEPTNO EMPLOYEES
  ---------- --------------------------------------------------
  10 CLARK,KING,MILLER
  20 ADAMS,FORD,JONES,SCOTT,SMITH
  30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


• Oracle Database 11g Release 2, provides the new PRECEDES keyword in trigger definition which allows trigger-upon-trigger dependencies.


• Audit filename will be prefixed with the instance name and ends with a sequence number. For example:
  SID_ora_pid_seqNumber.aud or SID_ora_pid_seqNumber.xml
  An existing audit file is never appended.


• From Oracle 11g R2, we can change audit table's (SYS.AUD$ and SYS.FGA_LOG$) tablespace and we can periodically delete the audit trail records using DBMS_AUDIT_MGMT.

• The initial segment creation for non partitioned tables and indexes can be delayed until data is first inserted into an object. Depending on the module usage, only a subset of the objects is really being used. With delayed segment creation, empty database objects do not consume any space, reducing the installation footprint and speeding up the installation.


• Flashback Data Archive support for DDL.


• In Oracle Database 11g Release 2 (11.2), support for the LZO compression algorithm on SecureFiles has been added. The new compression option is designated as COMPRESS LOW.
Fast decompression - LZO compression is 2 times faster than ZLIB. Fast compression - LZO compression is 3 times faster than ZLIB.

• IGNORE_ROW_ON_DUPKEY_INDEX hint for INSERT Statement With INSERT INTO TARGET ... SELECT ... FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted.
• Oracle Database Smart Flash Cache is a new feature, for Oracle Linux & Oracle Solaris, which increases the size of the database buffer cache without having to add RAM to host.
• ENABLE_GOLDENGATE_REPLICATION initialization parameter controls services provided for Oracle GoldenGate (both capture and apply services).
• Oracle Database 11g Release 2 introduces two new SQL*Net parameters that can be used on for connection strings of individual clients. The first parameter is CONNECT_TIMEOUT, it specifies the timeout duration (in seconds) for a client to establish an Oracle Net connection to an Oracle database, this parameter overrides SQLNET.OUTBOUT_CONNECT_TIMEOUT in the SQLNET.ORA. The second parameter is RETRY_COUNT and it specifies the number of times an ADDRESS_LIST is traversed before the connection attempt is terminated.
• Concurrent Statistics gathering feature is introduced in Oracle 11g release 2, which enables user to gather statistics on multiple tables in a schema, and multiple (sub)partitions within a table concurrently.
  
  ASM

• ASM Configuration Assistant (ASMCA) is a new tool to install and configure ASM.


• ASM Cluster File System (ACFS) provides support for files such as Oracle binaries, Clusterware binaries, report files, trace files, alert logs, external files, and other application datafiles. ACFS can be managed by ACFSUTIL, ASMCMD, OEM, ASMCA, SQL command interface.


• ASM Dynamic Volume Manager (ADVM) provides volume management services and a standard device driver interface to its clients (ACFS, ext3, OCFS2 and third party files systems).


• ACFS Snapshots are read-only on-line, space efficient, point in time copy of an ACFS file system. ACFS snapshots can be used to recover from inadvertent modification or deletion of files from a file system.


• ASM can hold and manage OCR (Oracle Cluster Registry) file and voting file.
• Trace File Analyzer (TFA) and Collector, also known as TFA Collector, is a diagnostic collection utility to simplify diagnostic data collection on Oracle Clusterware, Oracle Grid Infrastructure and Oracle RAC systems.


• ASM diskgroups can be renamed, by using renamedg command.


• From Oracle 11g R2, ASMCMD utility can do
  • ASMCMD Instance Management Commands - dsget, dsset, lsop, lspwusr, orapwusr, shutdown, spbackup,spcopy, spget, spmove, spset, startup.
  • Managing diskgroups (create, mount, alter, drop) through ASMCMD Disk Group Management Commands - chdg, chkdg, dropdg, iostat, lsattr, lsdg, lsdsk, lsod, md_backup,md_restore, mkdg, mount, offline, online, rebal, remap, setattr, umount.
  • User management and File access control through ASMCMD File Access Control Commands - chgrp, chmod, chown, groups, grpmod, lsgrp, lsusr, mkgrp, mkusr, passwd, rmgrp, rmusr.
  • Template management through ASMCMD Template Management Commands - chtmpl, lstmpl, mktmpl, rmtmpl.
  • Volume management through ASMCMD Volume Management Commands - volcreate, voldelete, voldisable, volenable, volinfo, volresize, volset, volstat.
  • We can execute OS commands at asmcmd by using !, in the same we do at SQL prompt.

Data Guard

• Automatic Block Repair - Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.
• The number of standby databases that a primary database can support is increased from 9 to 30 in this release.
• RMAN duplicate standby from active database
  RMAN > duplicate target database for standby from active database;
• Compressed table support in logical standby databases and Oracle LogMiner.
• Archived log deletion policy enhancements - we can CONFIGURE an archived redo log deletion policy so that logs are eligible for deletion only after being applied on or transferred to (all) standby database destinations.
• Increase in redo apply performance.
• In Oracle 11.2.0.4, new Data Guard broker configuration property, PrimaryLostWriteAction, determines what action is taken if a standby database detects that a lost write has occurred at the primary database.
• Heterogeneous Data Guard Configuration.

Tablespace Point-In-Time Recovery (TSPITR)

• We have the ability to recover a dropped tablespace.
• TSPITR can be repeated multiple times for the same tablespace. Previously, once a tablespace had been recovered to an earlier point-in-time, it could not be recovered to another earlier point-in-time.
• DBMS_TTS.TRANSPORT_SET_CHECK is automatically run to ensure that TSPITR is successful.
• AUXNAME is no longer used for recovery set datafiles.

Oracle Scheduler

• E-mail Notification - Oracle Database 11g Release 2 (11.2) users can now get e-mail notifications on any job activity.
• File Watcher - File watcher enables jobs to be triggered when a file arrives on a given machine.

RMAN
The following are new clauses and format options for the SET NEWNAME command:
• A single SET NEWNAME command can be applied to all files in a database or tablespace.
SET NEWNAME FOR DATABASE TO format;
SET NEWNAME FOR TABLESPACE tsname TO format;
• New format identifiers are as follows:
  %U - Unique identifier. data_D-%d_I-%I_TS-%N_FNO-%f
  %b - UNIX base name of the original datafile name. For example, if the original datafile name was $ORACLE_HOME/data/tbs_01.f, then %b is tbs_01.f.
• DUPLICATE can be performed without connecting to a target database, which requires connecting to a catalog and auxiliary database.
• Oracle 11.2.0.1 offers backup to Amazon S3 with the Oracle Secure Backup (OSB) Cloud Module.
• Archived log deletion policy enhancements - we can CONFIGURE an archived redo log deletion policy so that logs are eligible for deletion only after being applied on or transferred to (all) standby database destinations.

What's New in 11g Release 1 Oracle 12C What's New in 12c