Oracle database 11g new feature

http://www.oracleworlds.com/

Oracle database 11g new feature

ASM :

Support for rolling upgrades.

We can maintain version compatibilities at diskgroup level.

SQL> alter diskgroup dg-name set attribute 'compatible.rdbms'='11.1';
SQL> alter diskgroup dg-name set attribute 'compatible.asm'='11.1';

v  ASM drops disks and if they remain offline for more than 3.6 hours. The

diskgroups default time limit is altered by changing the DISK_REPAIR_TIME

parameter with a unit of minutes(M/m) or hours(H/h).

SQL> alter diskgroup dg-name set attribute 'disk_repair_time'='4.5h';

Automatic bad block detection and repair.Supports variable extent

(allocation unit) sizes. The total number of extents in shared pool will be

significantly reduced and improved performance.

SQL> create diskgroup ... attribute 'au_size' = 'number-of-bytes';

New SYSASM role (like SYSDBA, SYSOPER) & OSASM OS group (like OSDBA, OSOPER) to manage ASM instance only. This will separate storage administration from database administration.
$ sqlplus "/as sysasm" or $ asmcmd -a sysasm

v  ASM Preferred Mirror Read or Preferred Read Failure Groups -

ASM_PREFERRED_READ_FAILURE_GROUPS parameter is set to the preferred

failure groups for each node.

v  Faster Mirror Resync - Fast mirror resync after temporary connectivity lost.

v  We can drop a diskgroup forcefully.

SQL> drop diskgroup dg-name force including contents;

v    Can mount the disk in restricted mode, to rebalance faster.
SQL> alter diskgroup dg-name mount restricted;

New commands in ASMCMD

v  cp - to copy between ASM and local or remote destination.

v  md_backup - to backup metadata.

v  md_restore - to restore metadata.

v  lsdsk - to list(check) disks.

v  remap - to repair a range of physical blocks on disk.

Data Pump

v  New options in Data Pump export.
DATA_OPTIONS, ENCRYPTION, ENCRYPTION_ALGORITHM, ENCRYPTION_MODE, REMAP_DATA, REUSE_DUMPFILES, TRANSPORTABLE

v  New options in Data Pump import.
DATA_OPTIONS, PARTITION_OPTIONS, REMAP_DATA, REMAP_TABLE, TRANSPORTABLE

v  New option in Data Pump export interactive mode - REUSE_DUMPFILES.

v  In Data Pump import, we can specify how the partitions should transform by using PARTITION_OPTIONS.

Dumpfile can be compressed. In Oracle 10g, only metadata can be compressed. From 11g, both data & metadata can be compressed. Dumpfile will be uncompressed automatically before importing.

v  Encryption: The dumpfile can be encrypted while creating. This encryption occurs on the entire dumpfile, not just on the encrypted columns as it was in the Oracle Database 10g.

Masking: when we import data from production to test or development instances, we have to make sure sensitive data such as credit card details, etc. are obfuscated/remapped (altered in such a way that they are not identifiable). From 11g, Data Pump enables us do that by creating a masking function and then using that during import.

RMAN

v  Multisection backups of same file - RMAN can backup or restore a single file in parallel by dividing the work among multiple channels. Each channel backs up one file section, which is a contiguous range of blocks. This speeds up overall backup and restore performance, and particularly for bigfile tablespaces, in which a datafile can be sized upwards of several hundred GB to TB's.

v  Recovery will make use of flashback logs in FRA (Flash/Fast Recovery Area).

v  Fast Backup Compression - in addition to the Oracle Database 10g backup compression algorithm (BZIP2), RMAN now supports the ZLIB algorithm, which offers 40% better performance, with a trade-off of no more than 20% lower compression ratio, versus BZIP2.
RMAN> configure compression algorithm 'ZLIB' ;

v  Will backup uncommitted undo only, not committed undo.

v  Data Recovery Advisor (DRA) - quickly identify the root cause of failures; auto fix or present recovery options to the DBA.

v  Archived Redo log failover - this feature enables RMAN to complete backups even when some archiving destinations having missing logs or contain logs with corrupted blocks where local archive log destination is configured along with FRA.

 Virtual Private Catalog - a recovery catalog administrator can grant visibility of a subset of registered databases in the catalog to specific RMAN users.

RMAN> grant catalog for database db-name to user-name;

·                     Catalogs can be merged/moved/imported from one database to another.

·                     New commands in RMAN

v  RMAN> list failure;

v  RMAN> list failure errnumber detail;

v  RMAN> advise failure;

v  RMAN> repair failure;

v  RMAN> repair failure preview;

v  RMAN> validate database; -- checks for corrupted blocks

v  RMAN> create virtual catalog;

Partitioning:

v  Partition advisor - figure out what partitions to create.

v  Automated partitioning by interval (new partitions are added automatically).

v  Automated reference partitioning by Parent/Child reference (as partitions are created, partitions are created in tables that reference them).

v  Partitioning by virtual columns.

v  New composite partitioning types: Range-Range, List-Range, List-Hash, List-List, Interval-Range, Interval-List and Interval-Interval.

v  System partitioning is introduced.

v  Support for transportable partitions (tablespace transport of single partition) -

for moving partitions between different databases/operating systems.

v  Staleness checking in partitions - only outdated partitions will be refreshed

when we run dbms_mview.refresh().

Compression

Support compression on INSERT, UPDATE and DELETE operations. 10g only supported compression for bulk data-loading operations.

v  Advanced compression allows for a 2-3 X compression rate of structured and unstructured data.

v  From Oracle 11g, we can compress individual partitions also.

Performance improvements 

v  RAC - 70% faster (ADDM has a better global view of the RAC cluster).

v  Streams - 30-50% faster.

v  Optimizer stats collection - 10x faster.

v  OLAP (Online Analytic Processing) based materialized views for fast OLAP cube building. Cube-organized MView supports automatic query rewrite and automatic refresh of the cube.

v  SQL Result Cache - new memory area in SGA for storing SQL query results, PL/SQL function results and OCI call results. When we execute a query with the hint result_cache, the results are stored in the SQL Result Cache. Query results caching is 25% faster. The size of the cache is determined by result_cache_max_size, result_cache_max_result, result_cache_mode, result_cache_remote_expiration.

v  Invisible indexes - indexes will be ignored by the optimizer. Handy for testing without dropping. To make it visible, recreate it.
SQL> alter index index-name invisible;

v  Oracle secure files - 5x faster than normal file systems.

Availability improvements 

v  Ability to apply many patches on-line without downtime (RAC and single instance databases).

v  XA transactions spanning multiple servers.

v  Improved runtime connection load balancing.

v  Flashback Transaction/Oracle Total Recall.

Security improvements 

v  Support for case sensitive and multi-byte passwords (disabled by setting

SEC_CASE_SENSITIVE_LOGON parameter to FALSE).

v  Transparent Data Encryption - support for tablespace level encryption.

v  Hardware based master key protection.

v  Encrypt backups.

v  Kerberos authentication - strong passwords.

v  Add Multi-factor DBA controls with Data Vault.

v  New parameters have been added to enhance the default security of the database.
* SEC_RETURN_SERVER_RELEASE_BANNER
* SEC_PROTOCOL_ERROR_FURTHER_ACTION
* SEC_PROTOCOL_ERROR_TRACE_ACTION
* SEC_MAX_FAILED_FAILED_LOGIN_ATTEMPTS
* SEC_DISABLE_OLDER_ORACLE_RPCS

Manageability improvements 

v  New MEMORY_TARGET, MEMORY_MAX_TARGET parameters. When we set MEMORY_TARGET, Oracle will dynamically assign memory to SGA & PGA as and when needed i.e.MEMORY_TARGET=SGA_TARGET+PGA_AGGREGATE_TARGET. New views related this are v$memory_dynamic_components, v$memory_resize_ops.

v  From Oracle 11g, SID clause in "alter system reset" command is optional.
SQL> alter system [SID=instance-name] reset parameter-name;

v  New DIAGNOSTIC_DEST parameter as replacement for BACKGROUND_DUMP_DEST, CORE_DUMP_DEST and USER_DUMP_DEST. It defaults to $ORACLE_BASE/diag/.

v  From 11g, we have two alert log files. One is the traditional alert_SID.log (in DIAGNOSTIC_DEST/trace) and the other one is a log.xml file (in DIAGNOSTIC_DEST/alert). The xml file gives a lot more information than the traditional alert log file. We can have logging information for DDL operations in the alert log files. If log.xml reaches 10MB size, it will be renamed and will create new alert log file. log.xml can be accessed from ADR command line.
ADRCI> show alert

v  Logging information for DDL operations will be written into alert log files, is not enabled by default and we must change the new parameter to TRUE.
SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

v  Parameter(p) file & server parameter(sp) file can be created from memory.
SQL> create pfile[=location] from memory;
SQL> create spfile[=location] from memory;

v  From 11g, server parameter file (spfile) is in new format that is compliant with Oracle Hardware Assisted Resilient Data(HARD).

v  DDL wait option - Oracle will automatically wait for the specified time period during DDL operations and will try to run the DDL again.
SQL> ALTER SYSTEM/SESSION SET DDL_LOCK_TIMEOUT = n;

v  We can define the statistics to be pending, which means newly gather statistics will not be published or used by the optimizer — giving us an opportunity to test the new statistics before we publish them.

v  From Oracle Database 11g, we can create extended statistics on
(i) expressions of values, not only on columns
(ii) on multiple columns (column group), not only on single column.

v  Table level control of CBO statistics refresh threshold.

SQL> exec dbms_stats.set_table_prefs(’HR’, EMP’, ‘STALE_PERCENT’, ‘20');

Flashback Data Archive - flashback will make use of flashback logs, explicitly created for that table, in FRA (Flash/Fast Recovery Area), will not use undo. Flashback data archives can be defined on any table/tablespace. Flashback data archives are written by a dedicated background process called FBDA so there is less impact on performance. Can be purged at regular intervals automatically.

v  Analytic Workspace Manager (AWM) - a tool to manage OLAP objects in the database.

v  Users with default passwords can be found in DBA_USERS_WITH_DEFPWD.

v  Hash value of the passwords in DBA_USERS (in ALL_USERS and USER_USERS) will be blank. If you want to see the value, query USER$.

v  Default value for audit_trail is DB, not NULL. By default some system privileges will be audited.

v  LogMiner can be accessed from Oracle Enterprise Manager.

Data Guard improvements

v  Oracle Active Data Guard - Standby databases can now simultaneously be in read and recovery mode - so use it for running reports 24x7.

v  Online upgrades: Test on standby and roll to primary.

v  Snapshot standby database - physical standby database can be temporarily converted into an updateable one called snapshot standby database.

v  Creation of physical standby is become easier.

v  From Oracle 11g, we can control archive log deletion by setting the log_auto_delete initialization parameter to TRUE.  The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. Default is 24 hours (1440 minutes).

v  Incremental backup on physical readable physical standby.

v  Offload: Complete database and fast incremental backups.

v  Logical standby databases now support XML and CLOB datatypes as well as transparent data encryption.

v  We can compress the redo data that goes to the standby server, by setting compression=enable.

v  From Oracle 11g, logical standby provides support for DBMS_SCHEDULER.

v  When transferring redo data to standby, if the standby does not respond in time, the log transferring service will wait for specified timeout value (set by net_timeout=n) and then give up.

v  In Oracle 11g, block change tracking is now supported in the standby database.

v  New package and procedure, DBMS_DG.INITIATE_FS_FAILOVER, introduced to programmatically initiate a failover.

SecureFiles 
SecureFiles provide faster access to unstructured data than normal file systems, provides the benefits of LOBs and external files. For example, write access to SecureFiles is faster than a standard Linux file system, while read access is about the same. SecureFiles can be encrypted for security, de-duplicated and compressed for more efficient storage, cached (or not) for faster access (or save the buffer cache space), and logged at several levels to reduce the mean time to recover (MTTR) after a crash.

create table table-name ( ... lob-column lob-type [deduplicate] [compress high/low] [encrypt using 'encryption-algorithm'] [cache/nocache] [logging/nologging] ...) lob (lob-column) store as securefile ...;

To create SecureFiles:
(i) The initialization parameter db_securefile should be set to PERMITTED (the default value).
(ii) The tablespace where we are creating the securefile should be Automatic Segment Space Management (ASSM) enabled (default mode in Oracle Database 11g).

Real Application Testing(RAT)
Real Application Testing (RAT) will make decision making easier in migration, upgradation, patching, initialization parameter changes, object changes, hardware replacements, and operating system changes and moving to RAC environment. RAT consists of two components:

Database Replay 

 capture production workload and replay on different (standby/test/development) environment. Capture the activities from source database in the form of capture files in capture directory. Transfer these files to target box. Replay the process on target database.

v    SQL Performance Analyzer (SPA) - identifies SQL execution plan changes and performance regressions. SPA allows us to get results of some specific SQL or entire SQL workload against various types of changes such as initialization parameter changes, optimizer statistics refresh, and database upgrades, and then produces a comparison report to help us assess their impact. Accessible through Oracle Enterprise Manager or dbms_sqlpa package.

Other features

v  Temporary tablespace or it's tempfile can be shrinked, up to specified size.
SQL> alter tablespace temp-tbs shrink space;
SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};
SQL> alter tablespace temp-tbs shrink tempfile '.../temp03.dbf'

·                     keep n{K|M|G|T|P|E};
We can check free temp space in new view DBA_TEMP_FREE_SPACE.

·                     From 11g, while creating global temporary tables, we can specify TEMPORARY tablespaces.

·                     Online application upgrades and hot patching. Features based patching is also available.

·       Real-time SQL Monitoring, allows us to see the different metrics of the SQL being executed in real time. The stats are exposed through V$SQL_MONITOR, which is refreshed every second.

·        "duality" between SQL and XML - users can embed XML within PL/SQL and vice versa.

·      New binary XML datatype, a new XML index & better XQuery support.

·      Query rewriting will occur more frequently and for remote tables also.

·      Automatic Diagnostic Repository (ADR)- automated capture of fault diagnostics for faster fault resolution. The location of the files depends on DIAGNOSTIC_DEST parameter. This can be managed from Database control or command line. For command line, execute $ ./adrci

·                     Repair advisors to guide DBAs through the fault diagnosis and resolution process.

·                     SQL Developer is installed with the database server software (all editions). The Windows SQL*Plus GUI is deprecated.

 APEX (Oracle Application Express), formerly known as HTML DB, shipped with the DB.

·                     Checkers - DB Structure Integrity Checker, Data Block Integrity Checker, Redo Integrity Checker, Undo Segment Integrity Checker, Transaction Integrity Checker, Dictionary Integrity Checker.

·                     11g SQL Access Advisor provides recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structure.

·                     hangman Utility – hangman(Hang Manager) utility to detect database bottlenecks.

·                     Health Monitor (HM) utility - Health Monitor utility is an automation of the dbms_repair corruption detection utility.

·                     The dbms_stats package has several new procedures to aid in supplementing histogram data, and the state of these extended histograms can be seen in the user_tab_col_statistics view:
dbms_stats.create_extended_stats
dbms_stats.show_extended_stats_name
dbms_stats.drop_extended_stats

·                     New package DBMS_ADDM introduced in 11g.

·                     Oracle 11g introduced server side connection pool called Database Resident Connection Pool (DRCP).

Desupported features 
The following features are desupported/deprecated in Oracle Database 11g Release 1 (11.1.0):

·                     Oracle export utility (exp). Imp is still supported for backwards compatibility.

·                     Windows SQL*Plus GUI & iSQLPlus will not be shipped anymore. Use SQL Developer instead.

·                     Oracle Enterprise Manager Java console.

·                     copy command is deprecated.