11g - viragsharma

Oracle Openworld 2006, Oracle announced some exciting new features of Oracle 11g database and has 500 ( approx) new features. Just wait-n-watch to see all features, i.e once oracle release this product we will able to know all new feature. currently Beta test in underway for 11g How ever you can see Amozon.com and Burleson Consulting already booking order for New Feature Book/s and some of them have full TOC. So Check these sites you might get more new features.

Oracle Lunch party is over( on 11-JUL-07 New York), but product Oracle database 11g is not released. Most of the DBA where very much excited and ready to download 11g on 11-JUL-07 but....

I like DBA stuff, like RAC and DG( My favorite topics) , Performance, Backup & Recovery and SQL/PL-SQL new features. So writing/learing those Oracle database 11g new features only. I am also very much interested to write "Top 10 Oracle database 11g new features for DBA"[Click Here] ( Page Update on weekend only , When get little time to read :-) something new from below given source sites and It is intended for information purposes only)

Oracle Database 11g New Features Top 11g New Feature for DBAs

1) Automatic Diagnostic Repository [Click Here] New

2) Database Replay [Click Here]

3) Automatic Memory Tuning {Click Here]

4) Case sensitive password

5) Virtual columns and indexes [Click Here]

6) Interval Partition and System Partition

7) SQL Performance Analyzer (SPA)[Click Here PDF] New

8) ADDM RAC Enhancements [New]

9) SQL Plan Management and SQL Plan Baselines [Click Here pdf file]

10) SQL Access Advisor & Partition Advisor

11) Active Data Guard

12) DBMS_STATS Enhancements

13) The Result Cache

14) Total Recall (Flashback Data Archive) New

Virag Sharma

Table Of Contents

Oracle 11g Database DBA New Features

Database Capture/replay database workloads :- Allows 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 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. 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]

http://viragsharma.blogspot.com/2007/04/oracle-11g-automatic-memory-management.html

Interval partitioning for tables : As per Robert

"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)
interval (numtoyminterval(1,'MONTH'))
( 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
--passwords
```
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 management
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: Laurent
Virtual columns/indexes :- 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
indexes
Check following link for more details
http://viragsharma.blogspot.com/2007/04/oracle-11g-invisible-indexes.html
Fine grained access control for Utl_TCP: in 10g all port are available, now it is controlled.
Check following link for more details
http://viragsharma.blogspot.com/2007/07/oracle-11g-and-acl-access-control-list.html
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]

http://viragsharma.blogspot.com/2007/09/oracle-11g-data-guard-enhancements-new.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
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 package No.)
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>

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
package 4

adrci>>ips generate package 4 in /tmp
Generated package 4 in file /tmp/ORA600kci_20070514184516_COM_1.zip, mode
complete
adrci>>

Send above zip file to Oracle Support :- Source
http://viragsharma.blogspot.com/2007/07/new-way-in-11g-to-send-file-to-oracle.html

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 tables
Table trigger firing order
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 Features

Fine Grained Dependency Tracking:- In 11g we track dependencies at the level of element within unit. •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'
- -----
VALID

No recompilation of dependent objects when Columns added to tables OR Procedures added to packages Source Tom Kyte

Named and Mixed Notation from SQL:-

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 "simple_integer"
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 common queries.

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 characters
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 Features

Data Guard Load Balancing
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

Click here to get more detail about DBMS_ADDM [HTML]

Oracle 11g Database Performance Tuning New Features

ADR (Automatic Diagnostic Repository)

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.

No 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
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 )

Too list all the "ORA-" error run following command

adrci>show alert -P "MESSAGE_TEXT LIKE '%ORA-%'"

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]

http://viragsharma.blogspot.com/2007/07/how-to-check-alertlog-in-oracle-11g.html

Oracle 11g Database Backup & Recovery New Features

Enhanced 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.
Configuring backup compression
In 11g can use CONFIGURE command to choose between the BZIP2 and ZLIB compression algorithms for RMAN backups.
Active Database Duplication
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 backup.
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 incremental backup.

More feature Coming soon ......................

http://virag.sharma.googlepages.com/oracle11gfeaturedatabasereplay

http://virag.sharma.googlepages.com/oracle11gadrautomaticdiagnosticrepositor

http://viragsharma.blogspot.com/2007/08/oracle-sql-replay-sql-performance.html

http://virag.sharma.googlepages.com/sqlreplay%26sqlperformanceanalyzer

Disclaimer

The views expressed are my own and not necessarily those of my current or past employer. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.

The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.