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';
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
$ 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;
SQL> alter diskgroup dg-name mount restricted;
New
commands in ASMCMD
v cp - to copy between
ASM and local or remote destination.
v md_restore - to restore
metadata.
v lsdsk - to list(check)
disks.
v remap - to repair a
range of physical blocks on disk.
v
New options in Data Pump export.
DATA_OPTIONS, ENCRYPTION, ENCRYPTION_ALGORITHM, ENCRYPTION_MODE, REMAP_DATA, REUSE_DUMPFILES, TRANSPORTABLE
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
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.
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' ;
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 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;
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.
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
* 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;
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
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;
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;
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;
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.
(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:
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'
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.
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
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):
The following features are desupported/deprecated in Oracle Database 11g Release 1 (11.1.0):
·
Windows SQL*Plus GUI & iSQLPlus will not be shipped anymore.
Use SQL Developer instead.
·
Oracle Enterprise Manager Java console.
·
copy command is deprecated.
