Oracle 11g Database DBA New Features
Capture/replay database workloads
the total database workload to be captured, transferred to a test
database created from a backup or standby database, then replayed to
test the affects of an upgrade or system change. Currently, they
are working to a capture performance overhead of 5%, so you could
conceivably capture real production workloads.
Click Here for more detail [ HTML] [PPT]
- Automatic Memory
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. You literally tell Oracle how
much memory it has and it determines how much to use for PGA, SGA and OS
Processes. Maximum and minimum thresholds can be set.
More details => [HTML]
- Interval partitioning
for tables : As per
"Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does. Here is an example:
create table selling_stuff_daily
( prod_id number not null, cust_id number not null
, sale_dt date not null, qty_sold number(3) not null
, unit_sale_pr number(10,2) not null
, total_sale_pr number(10,2) not null
, total_disc number(10,2) not null)
partition by range (sale_dt)
( partition p_before_1_jan_2007 values
less than (to_date('01-01-2007','dd-mm-yyyy')))
Note the interval keyword. This defines the interval that you want each partition to represent. In this case, Oracle will create the next partition for dates less than 02-01-2007 when the first record that belongs in that partition is created."
- Case sensitive password
-- Init.ora parameter
sec_case_sensitive_logon = (TRUE | FALSE)
-- On system level you can switch off
alter system set sec_case_sensitive_logon = false
- -Find users who have case sensitive or case insensitive
SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS
-- Password file
orapwd file=orclpwd password=manager ignorecase=y
More Details => [HTML]
- Faster DML triggers
: Create a disabled trigger; specify trigger firing order
- Improved NFS data file
- RMAN UNDO bypass : Rman backup can bypass undo. Undo
tablespaces are getting huge, but contain lots of useless
information. Now rman can bypass those types of tablespace.
Great for exporting a tablespace from backup.:- Source:
User can create Virtual
index on table. This Virtual index is not visible to optimizer, so it
will not affect performance, Developer can user HINT and see is Index is
useful or not.Invisible Indexesprevent premature use of newly created
Check following link for more details
- Fine grained access
control for Utl_TCP: in 10g all port are available, now it is
Check following link for more details
- Data Guard Enhancements
1) Data Protection
- Advanced Compression
- Lost-write protection
- Fast-Start Failover
2) Increase ROI
- Active Data Guard
- Snapshot Standby
3) High Availability
- Faster Redo Apply
- Faster failover & switchover
- Automatic Failover using ASYNC
For detail check following link [HTML]
- New Trigger features
- Partitioning by
logical object and automated partition creation.
- LOB's - New
high-performance LOB features.
- Incident Packaging
To make oracle support better , oracle 11g introduce new feature /
Service known IPS ( Incident Packaging Service ) . Basically it is a
part/extension of Oracle 11g new feature ADR ( Automatic Diagnostic. ADR is new concept in oracle 11g and it is just a file
based repository of diagnostic data. ADR gives lot flexibility to
maintain / handle diagnostic data.
When critical errors(i.e Problem) are detected, they automatically
create an “incident” ( one occurrence of problem is Incident i.e
relation between problem and incident is one to many).
Information(trace ,dump ) related to the incident is automatically captured in file based repository known as ADR ( Automatic Diagnostic repository),
all the incident related files are taged with Incident Number and
certain health checks are run automatically. This information can be
packaged to be sent to Oracle support
Incident Packaging Service (IPS) wraps up all information about an incidentand allows you to send the whole package to Oracle Support.
Here is steps to collect and send data to support using IPS
adrci> help ips
adrci> show incident
( For example above command show incident No 9817 for ORA-600 [XYZ] )
adrci> ips create package incident <> <= ( it will give
adrci> ips create package incident 9817
Created package 4 based on incident id 9817, correlation level typical
adrci> ips add incident 9817 package 4
Added incident 9817 to package 4
adrci>>ips add file -
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log package 4
Added file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log to
adrci>>ips generate package 4 in
Generated package 4 in file /tmp/ORA600kci_20070514184516_COM_1.zip,
Send above zip file to Oracle Support :- Source
- Feature Based Patching: All one-off patches will be classified as to which feature they affect. This allows you to easily identify which patches are necessary for the features you are using. EM will allow you to subscribe to a feature based patching service, so EM automatically scans for available patches for the features you are using. Source Tim
- New Oracle11g Advisors
- Enhanced Read only
- Table trigger firing
- Enhanced Index
rebuild online : - Online index build with NO pause to DML.
- No recompilation of
dependent objects:- When
A) Columns added to tables
B) Procedures added to packages
- Improved optimizer
statistics collection speed
Improvement of AUTO sampling statistics gathering feature in Oracle 11g
Adaptive cursor sharing to improve bind variable issue
- New default audit
settings :- Oracle database where general database auditing was
"off" by default, logging is intended to be enabled by default
with the Oracle Database 11g beta secure configuration.
Notable performance improvements are planned to be introduced to reduce
the performance degradation typically associated with auditing.
- passwords are
expected to also become case sensitive This and other changes should
result in better protection against brute force attacks and password
guessing scenarios. For example, in addition to limiting the
number of failed login attempts to 10 (default configuration in 10gR2),
Oracle 11g beta’s planned default settings should expire
passwords every 180 days, and limit to seven the number of times a user
can login with an expired password before disabling access.Source
- Online index
build with NO pause to DML
- Read only table :-
alter table t read only
alter table t read write
Oracle 11g Database SQL/PL-SQL New
- Fine Grained
Dependency Tracking:- In 11g we track dependencies at the level of
•so we know that these changes have no consequence
•I classified this as a transparent performance improvement
•It’s certainly transparent!
•Unnecessary recompilation certainly consumes CPU
create table t(a number)
create view v as select a from t
alter table t add(Unheard_Of number)
select status from User_Objectswhere Object_Name = 'V'
No recompilation of dependent
objects when Columns added to tables OR
Procedures added to packages Source Tom Kyte
- Named and Mixed Notation
select fun(P4=>10) from DUAL
In 10g you can't call function in select statment by passing 4th
parameter, but in 11g it is possible
- PL/SQL "continue" keyword
- It is same as we read in c/c++ loop
- Support for “super”: It is same
"super" in Java.
- Powerfull Regular
Expression:- Now we can access data between TAGS like data
between tags <BOOK> .........</BOOK>
The new built-in REGEXP_COUNT
returns the number of times the pattern is matched in the input string.
- New table Data Type
- SQL Performance Analyzer(SPA) :- It is same as Database replay except it not capture all transaction.The SQL Performance Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system, and then replay the SQL workload against the modified database or configuration. The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor. Source.
For more detail click here [HTML] [PDF]
http://virag.sharma.googlepages.com/SQL-Performance-Analyzer.pdf [Click Here for Details]
- Caching The
Results with /*+ result_cache */ :- select /*+ result_cache */ *
from my_table, New for Oracle 11g, the result_cache hint caches the
result set of a select statement. This is similar to alter table
table_name cache,but as you can adding predicates makes /*+ result_cache
*/ considerably more powerful by caching a subset of larger tables and
select /*+ result_cache */ col1, col2, col3
from my_table where colA = :B1 Source Tom Kyte PPT
- The compound
trigger :- A compound trigger lets you implement actions for each of
the table DML timing points in a single trigger
- PL/SQL unit
source can exceeds 32k
- Easier to execute
table DDL operations online:- Option to wait for active DML operations
instead of aborting
- Fast add column with
default value:- Does not need to update
all rows to default value.
Oracle 11g Database RAC and DataGuard New
Click here to get more detail about DBMS_ADDM [HTML]
- Data Guard Load
- Oracle 11g RAC
parallel upgrades :- Big WOW, In oracle 11g you need not to shutdown all CRS , in order to upgrade CRS.
- ADDM for RAC
Oracle 11g introduce new package DBMS_ADDM , which can be use generate report for all instance of RAC. DBMS_ADDM can be use is 3 diffrent mode
- Database Mode
- Instance Mode
- Partial Mode
Oracle 11g Database Performance Tuning New
- ADR (Automatic
In Oracle 11g database New dimension fault diagnosability
infrastructure added to Oracle self managing concept to reduce time for
resolving problem/bug/SR and round trip between end-user and Oracle Support.
Trace file, dump file , core file ADR, ADRCI etc are components of fault
diagnosability infrastructure.In early version of oracle, When ever critical error
such as code bug , data corruption occur end user need to collect diagnostic
data such as process dump , data structure dump etc. In oracle 11g database end
user need to worry about what kind of dump need to collect , need not to search
trace file which need to send to Oracle Support Oracle 11g Databaseproactively collect ,tagged and store
diagnostic data in file based repository known as ADR. 11g used file based
repository because even your database is down, you have diagnostic data to send
to oracle support.
Oracle 11g introduce new tool/utility called ADRCIknown as ADR command line tool.
This tool allow user to interact with ADR ,check alert log, check health monitor(HM) status ,
Package incident and problem information into a zip file for send to Oracle Support. Etc.
username/password need to log in to ADRCI, ADRCI interact with file
system and ADR data is secured only by operating system permissions on
the ADR directories.
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
Too list all the "ORA-" error run following command
adrci>show alert -P "MESSAGE_TEXT LIKE
In 11galert file is saved in 2 location, one is in alertdirectory ( in XML format) and
old style alert file in trace directory.
Check following site for more info [HTML] [PDF]
Oracle 11g Database Backup & Recovery
configuration of archive deletion policies
Archive can be deleted , if it is not need DG , Streams Flashback etc
When you CONFIGURE an archived log deletion policy applies to all
archiving destinations, including the flash recovery area. BACKUP ...
DELETE INPUT and DELETE... ARCHIVELOG use this configuration, as does
the flash recovery area. When we back up the recovery area, RMAN can
fail over to other archived redo log destinations if the flash recovery
area is inaccessible.
In 11g can use CONFIGURE command to choose between the BZIP2 and
ZLIB compression algorithms for RMAN backups.
- Active Database
Now DUPLICATE command is network aware i.e.we can create a duplicate or
standby database over the network without taking backup or using old
- Parallel backup
and restore for very large files
RMAN Backups of large data files now use multiple parallel server
processes to efficiently distribute theworkload for each file. This
features improves the performance of backups.
- Improved block
media recovery performance
RECOVER command can recover individual data blocks.
RMAN take older, uncorrupted blocks from flashback and the RMAN
can use these blocks, thereby speeding up block media recovery.
- Fast incremental
backups on physical standby database
in 11g we can enable block change tracking on a physical standby
database (ALTER DATABASE ENABLE/DISABLE BLOCK CHANGE TRACKING SQL
statement).This new 11g feature enables faster
incremental backups on a physical standby database than in previous
releases.because RMAN identifywe the changed blocks sincethe last
More feature Coming