Oracle Corner

What I like about Oracle 10g is that it has finally made advances in self-diagnosis and self-monitoring. Oracle has been promissing a self-tuning database for ages, and this is the first version that steps considerably closer to this goal.

Interesting Oracle 10g features:

***) Cloning Oracle software installations
You can use Oracle Universal Installer to clone the whole Oracle software installation to another servers.

• ./runInstaller -clone ORACLE_HOME="" ORACLE_HOME_NAME="" [-responseFile ]

***) Data Pump
With Data Pump, jobs can be paused, monitored, resumed, restarted or left to run in the background. R/W external tables (they used to be r/o only) can be created with the ORACLE_DATAPUMP access driver; this is mostly convenient for very large tables.
Unfortunately, data pump files are not compatible with imp/exp dump files, which would be nice to have.

***) Advisors
There are plenty of new advisors in 10g, some of them (like SQL Tuning Advisor) are still not giving much useful information, but this is the beginning in fulfilling a dream of self-tuned database

• SQL Tuning Advisor
• Input: ADDM's SQLs, top SQL (AWR and SGA) and SQL sets
• Output: stale/missing statistics, Access Patchs, Restructured SQLs
• SQL Access Advisor
• Input: the same as for tuning advisor + filtered SQLs, hypothetical workload and tables full of your own SQL
• Output: recommendations for new indexes, new MV/MVL
• PGA Advisor
• Defined with SGA_TARGET parameter
• Segment Advisor
• Input: ADDM
• Output: Growth trends and shrink recommendations
• Undo Advisor
• Oracle auto-tunes UNDO_RETENTION
• Output: longest running query, average undo Kb/minute, maximum undo Kb/min, how big UNDI tablespace must be to support x hours of undo retention based on historical data
  

***) Easy installation
More information in one of my other notes on this BLOG.

***) New scheduling system
Finally a more sophisticated scheduling system: Job Scheduler. In short: a job specifies what is going to be executed (program) and when (schedule). Both programs and schedules can be reused by jobs. Jobs can be exported/imported from one system to another and they can be managed in classes (groups). Even better, classes can be prioritised, so that high priority jobs will get run first. The DBA can create a Window (a time period) and assign a job class to a resource group.

***) Lots of small features
Recycle bin, SYSAUX being used instead of SYSTEM tablespace, new global default tablespace, shrinking objects and resetting HWM at the same time, bigfile data files (up to 8 exabytes), ...
Flashback Transaction Query feature. This can be used instead of Log Miner and uses undo_retention parameter.

***) Self-Monitoring
MMON checks tablespaces' free space every 10 minutes and logs data into AWR. The DBA can define critical and warning thresholds and when breached, a dbsm_server_alert can alert the DBA (by e-mail or shown on the console's home).

***) Backup/recovery
The Block Tracking feature tracks all changed data file blocks, and "indexes" those changed blocks into the binary file. RMAN does not need to read the whole data file to perform an incremental backup, only those changed blocks.
RMAN can compress backups. I couldn't find more details about what type of compression is done, so I can only assume that UTL_COMPRESS is used for the compression.
Flashback database is great for speedy recovery in time.

***) Security
10g didn't introduce many new security features, probably because 9i was hailed as "unbreakable" and had quite a good security.
Auditing can be extended to add SQL statements and bind variables for audited operations
VPD policies can fire now on specific columns