SYSAUX
This new tablespace sits alongside the SYSTEM tablespace and serves the purpose of keeping system-related schemas out of the SYSTEJM tablespace. This includes AWR data, Log Miner data, Streams, Oracle Text and Oracle Spatial. You can also put any of your own schemas in there as you see fit.
Automatic Shared Memory Management (ASMM)
Oracle have taken the memory management features introduced in Oracle 9i a step further by introducing the SGA_TARGET parameter. This parameter governs the SHARED_POOL_SIZE, DB_CACHE_SIZE &
JAVA_POOL_SIZE parameters, with the database automatically resizing them as it sees fit. With the use of an spfile, SGA_TARGET can be decreased or increased within the limits of SGA_MAX_SIZE without the need for a database restart.
Automatic Workload Repository (AWR)
The AWR replaces the Statspack utility, a vital DBA tool from Oracle 8i & 9i. Unlike Statspack, the AWR runs automatically in the background, by default gathering hourly snapshots of system data and keeping 7 days worth on-line. More statistical data is collected than before and easy to read HTML reports can be produced. In addition, you can create performance baselines against which you can compare database activity.
Data Dump
Although import and export still exist in 10g, Oracle has produced a significantly enhanced version of the tools (impdp & expdp), collectively known as Data Pump. Using similar interfaces to the old export and import you can now load and unload data at dramatically faster speeds. In addition you can detach from a long-running Data Pump job session and re-attach to it from elsewhere and continue to interact with it. Many extra features have also been added, including dynamic transformation of data storage parameters and tablespace locations. Another nice feature is that the contents of single table dump files exported with expdp can be viewed as external tables using a new external table driver.
DBMS_FILE_TRANSFER Package
This new package can be used to copy binary files around, both on the local server and to remote servers. As is usual these days, directory objects within the database facilitate this process.
Direct 8 & 8i Upgrade Procedure
You can directly upgrade your 8.1.7 or 8.0.6 database to 10g Release 1 using a single script.
Extended Database Auditing
When database-based auditing is switched on, extra information such as SQL statements can be written to the AUD$ table.
More Archiving Destinations
You can now have 5-10 archive log destinations specified in the parameter file.
Multiple Block Size
Tablespaces can be created with non-standard block sizes (i.e. block sizes other than that specified by the DB_BLOCK_SIZE initialisation parameter). To use this feature you must specify a sub-cache for that block size by using the appropriate DBA_(n)K_CACHE_SIZE initialisation parameter.
Optimizer Changes & Statistics Gathering
Although it remains for backwards compatibility, the Rule Based Optimizer has been shelved and is no longer supported. This makes it more important than ever to have current and accurate statistics on all your data and 10g provides an automatic statistics gathering job to gather statistics nightly. Tables now all have monitoring on by default and so only stale or incomplete statistics are gathered. Furthermore limited dynamic statistics gathering can occur at query parse time to produce at least some statistics on objects that do not have any.
Resumable Space Allocation
This feature was introduced in Oracle 9i and could be set at session level or as part of an import command. 10g introduces the resumable_timeout init.ora parameter meaning the feature is turned on at the database level. It can also be altered via the alter system command.
Security Enhancements
The new DBMS_CRYPTO package allows data to be encrypted right down at the table data level using most kinds of encryption such as RSA and MD5 and so on. Further improvements to the Fine Grained Auditing feature and Virtual Private Databases (DBMS_RLS) mean data can be monitored and secured at an even more sophisticated level than before.
Shared Server
This is now even easier to use with dispatchers that start automatically and easily changeable settings via alter system commands.
Temporary Tablespace Groups
As the name suggest you can now assign any number of temporary tablespaces to a temporary tablespace group and assign the group as a user’s temporary tablespace. This enables better distribution of temporary segment usage and can be of particular benefit in data warehouses where very large sort or hash joins are written to disk.
Web-based Enterprise Manager and Grid Control
The old enterprise manager tool has been completely revamped and turned into a web-based management tool. As well as all the usual features you can now access all of the aforementioned workload repository and advisory features through the same interface and completely manage all aspects of database administration. Grid Control lets you manage databases and application services across RAC environments.
Automatic Database Diagnostics Monitor (ADDM) & the Advisor Framework
10g provides the DBMS_ADVISOR package to help tune many aspects of database performance including query tuning, undo tuning, hot segment identification & materialized view tuning. With regards to SQL tuning, the advisor can work out the best optimizer plan and you can save this as a profile for the statement so it is always used. DBMS_ADVISOR underpins the ADDM which, like the AWR is constantly running in the background. You can run an ADDM report between two snapshots to see what performance bottlenecks have been identified and any auto-generated advice about how to resolve them.
Automatic Storage Management (ASM)
With this new feature, Oracle can now take over the task traditionally performed by volume managers (e.g. Veritas) and manage the filesystems on your disks directly. A special Oracle instance resides on the server which transparently manages the disks. By presenting a series of raw disks to the Oracle ASM instance, Oracle can manage disk redundancy and data striping across these disks to provide best performance. In addition Oracle can dynamically rebalance disks as you new disks to the server. However, if you prefer to have something like disk RAID pre-configured, you can just present the RAID volumes to ASM and it will only manage the filesystem and the database files. ASM uses an extension of Oracle Managed Files syntax to locate and name database files. As well as core database files, archivelogs and other backup files can also reside on ASM.
Automatic Undo Retention
The init.ora parameter undo_retention is now a minimum value rather than an absolute value for undo retention. In 10g, Oracle will take this minimum and tune undo retention up and down above it in an effort to ensure that the longest running queries running on the database will not be susceptible to ORA-01555 errors.
Bigfile Tablespaces
This special kind of tablespace has a single datafile that can be anywhere in size up to 128TB (depending on the block size specified for the tablespace). This simplifies management and you can use alter tablespace commands where you would normally use “alter database datafile” commands to modify the file, e.g. change its size or set autoextend. This kind of tablespace is most useful in data warehouses as it allows for the creation of very large databases.
Database Scheduler
The new DBMS_SCHEDULER package significantly enhances the functionality of DBMS_JOB. The scheduler can run scripts external to the database and jobs can have dependencies so that they will be run in sequence and the sequence halted if one fails. Job windows can be created so that a job will start when the window opens and will terminate if still running when the window closes – ideal for limiting overnight schedules.
Default User Tablespace
Oracle 9i introduced the default temporary tablespace and 10g takes this step further by introducing the ability to set a default user tablespace as part of the alter database command.
Drop Database / Rename Tablespace
These two new SQL commands provide handy solutions to old problems. A tablespace can now easily be renamed with a single rename command. An entire database instance and all its files can be dropped with the drop database command. This is especially useful if you are using Automatic Storage Management as in 10g release 1 you cannot directly interact with ASM files. Furthermore, from RMAN you can issue the “drop database including backups” command to delete everything related to the database.
EZ Connect Facility
You can now connect to database without any of the traditional configuration files (such as sqlnet.ora and tnsnames.ora) by specifying a JDBC-style connect string that includes server and listener port information.
Multi-tier Materalised Views
You can now build updateable materialized views on top of other updateable materialized views. This provides the ability to create complex branched replication environments, e.g. for regional offices.
Online Segment Shrink
The days of having to rebuild an object to reset its high watermark are now over with the online segment shrink feature. ALTER TABLE commands can be run to compact a table and reset its high watermark, thus freeing up any unused space within each segment. This feature does not work with LOBS, XML and other user-defined data types.
Recycle Bin
As part of enhancements to Oracle’s Flashback features, each tablespace now has a recycle bin and objects are moved there after they are dropped unless you specifically use the PURGE keyword as part of the drop statement. If you don’t need it the recycle bin can be turned off with a hidden parameter.
Resource Manager Enhancements
Enhancements here include a Session Timeout feature and the ability to switch back to your original consumer group after a switch for other reasons. In addition you can configure the database to automatically assign a consumer group to a session based on certain characteristics.
RMAN - Datafile Format Conversion
It used to be the case that if you wanted to move a database from Windows to Solaris, you had to do an export and import due to the difference in the way the two operating systems construct files. With 10g, RMAN can now convert files between platforms as part of a transportable tablespace operation. Instead of just copying the read-only datafile as in a normal transportable tablespace operation, you issue the RMAN “convert tablespace” command to the convert the file to a new format and location.
Server Alerts
The DBMS_SERVER_ALERT package allows you to monitor and automatically generate alerts within the database. Things you can monitor include tablespace usage, open cursors, database wait time and blocked sessions.
Skip Unusable Indexes
The new init.ora parameter skip_unusable_indexes prevents errors being returned when a query is issued against a table that tries to use an unusable index. Instead the index is ignored and the query goes ahead.
UTL_MAIL Package
This new package allows you to send mails from the database using very simple commands, instead of the many commands that used to be necessary with UTL_SMTP. All you need to do is set the smtp_out_server init.ora parameter to point to your mail host and port number.
Application Programming Binary Float/Double Data Types
These new floating point data types are more efficient than the number data type and are more widely accepted in the field of computing.
DBMS_LOB Enhancements
Two new procedures LOADBLOBFROMFILE and LOADCLOBFROMFILE allow you to import external file content directly into LOB fields.
Oracle Data Provider.net (ODP.net)
ODP.net is a new programmatic environment that implements a data provider for Oracle Database. It uses APIs native to Oracle Database to offer fast and reliable access from any .NET application to database features and data. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library.
Terabyte LOB Support
JDBC, OCI & PL/SQL can now all handle terabyte LOBs.
Case & Accent Insensitive Queries
The new NLS_SORT parameter can be used in queries to ignore case and accents when sorting.
Expression Filter
Oracle Expression Filter lets you store conditional expressions in a column that you can use in the WHERE clause of a database query.
Regular Expressions
These can now be used in SQL though the new REGEXP_LIKE, REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUSBTR functions, leveraging this powerful Unix-style functionality.
Backup & Recovery Flash Recovery Area
This is an area of disk space designed to allow you to keep all your recovery-related database files in a single location. You set a size for the flash recovery area although this is a soft limit rather than hard disk limit. Files to go in here are RMAN backup sets and controlfile backups; redo log files; control files; archivelog files and flashback log files. You can use the RMAN “backup recovery area” command to back up the entire flash recovery area to tape or another disk.
Recovery Through Resetlogs
This is now possible using an additional tag in the archive_log_format init.ora parameter. The new %r format command shows which incarnation of the database the log refers to as well as the thread and sequence number.
RMAN - Backup Duration and Throttling
It is now possible to configure RMAN to terminate a backup after a specified amount of time or to throttle a backup process in order to minimise the load on the server.
RMAN - Channel Failover
If you are using multiple channels in an RMAN operation, the process can automatically failover between channels if a particular channel encounters an error.
RMAN - Incrementally Updated File Copy Backups
Perhaps one of the best new RMAN enhancements is the ability to just update your backup sets instead of backing up the whole database every night. Provided you have enough disk space to keep your backups online, RMAN can initially create a file copy backup of the database and then update these copies using available incremental backups every time the “recover copy of database” command is issued.
SYSAUX Tablespace
This new tablespace sits alongside the SYSTEM tablespace and serves the purpose of keeping system-related schemas out of the SYSTEJM tablespace. This includes AWR data, Log Miner data, Streams, Oracle Text and Oracle Spatial. You can also put any of your own schemas in there as you see fit.
Flashback Enhancements
The flashback feature introduced in Oracle 9i has been considerably improved in 10g. Having set things up appropriately, flashback data is written to flashback logs on the local filesystem and using these, you can flashback an entire database to a previous moment in time – much quicker than restoring from a backup and rolling forward with archivelogs. With the aid of the new Recycle Bin feature you can flashback tables to before they were dropped and even flashback queries and transactions.
RMAN - Backup Command Changes
The backup and copy commands have been merged together into just the backup command. To do a file copy backup you can use the “backup as copy” syntax to over-ride the default creation of backup sets.
RMAN - Binary Compression for Backup Sets
RMAN already compresses backups by not backing up unused data blocks but with 10g you can take this even further and apply binary compression to backup sets to further reduce disk usage.
RMAN - Fast Incremental Backup
With the addition of the Block Change Tracking feature, incremental RMAN backups can be done in a fraction of the time of level zero backups. An external file is configured on the server which keeps track of any changed blocks since the last backup, this information is then used to only backup those blocks.
RMAN - Restore Failover
During the restore process, RMAN can now automatically failover to a copy of a backup piece if the original is for some reason not available. If no copy can be found then it can fail over to an earlier backup piece and just roll forward with more archivelogs.
RMAN - User-catalogued Backup Pieces
Of great relief to many DBAs will be the fact that you can now manually add a backup piece into the RMAN catalogue using the CATALOG command. This means that if you have created an image copy of your own, or if you have simply had to move a backup piece to another location, you can now attach it manually to the controlfile or recovery catalogue so it can be used in recoveries. This means that if you cannot replicate the exact location of your backup directory when restoring on a new server, you can still make use of files at a different location. It also means that you can use backups even if you had to recreate your controlfile.
Asynchronous Change Data Capture
Change Data Capture was introduced in Oracle 9i and is a powerful tool for updating your data warehouse from the source databases. 10g introduces Asynchronous capture, meaning changes captured in the redo logs can be propagated to the warehouse via archive logging, and then applied to the warehouse using Oracle Streams technology.
MERGE Statement Improvements
It is no longer necessary to include both the INSERT and UPDATE clauses, only one is needed and Oracle can work out the rest. There is now an optional DELETE clause that can delete rows according to certain criteria. You can now perform conditional inserts using a new WHERE clause.
Partitioning Enhancements
You can now partition Index Organised Tables (IOTs). Hash partitioned global indexes are also available.
Recovery Through Resetlogs
This is now possible using an additional tag in the archive_log_format init.ora parameter. The new %r format command shows which incarnation of the database the log refers to as well as the thread and sequence number.
Materialized View Enhancements
Partition Change Tracking, introduced in Oracle 9i for range and range-hash partitioned tables, can now be used for all partitioning types. This means that each partition tracks its own changes via materialized view logs and uses the details for a fast refresh. Hierarchical materialized views can now be automatically refreshed in the proper order using DBMS_MVIEW. You can also build materialized views that depend on other views using the “build deferred” clause and let them later be populated via the refresh procedure. As part of the Advisor Framework, the new materialized view advisor (DBMS_ADVISOR.TUNE_MVIEW) has been created to help tune materialized views.
MODEL Clause
This powerful new clause allows you to perform complex computations and predictions based on current data without the need for excessive joining and UNION clauses. It’s also OLAP compliant and can be used with Oracle OLAP features.
QUERY Re-write Enhancements
The new optimizer hint “rewrite_or_error” can be used to cause statements to fail if they are not re-written.
High Availability
Automatic Failover with Data Guard Manager
Not only has the syntax for this tool been greatly simplified, it can now be used to auto-failover a database to the standby. This is done by having a separate server with an Oracle installation on it (and another license fee of course!) which monitors both the live and standby nodes. In the event it loses contact with the live database, the monitor node performs a failover to the standby. When it becomes available again, the old live database is automatically reconfigured to act as a new standby.
Real Application Clusters (RAC)
RAC has been radically altered as part of the transition to grid computing. Although the basic concepts remain the same, the components of the clustering software have been brought together and renamed Cluster Ready Services, or CRS. CRS manages cluster database functions including node membership, group services, global resource management, and high availability. As previously mentioned, application services are defined within CRS to help enable grid computing. 10g also saw the introduction of Virtual IP Addresses (VIP) to enhance the failover abilities of RAC when nodes go down. Each server has its own VIP address which is registered in DNS. When a node goes down, Oracle can migrate the VIP to another node in the cluster so that connections can still come though. The Database Configuration Assistant (DBCA) has been considerably enhanced and Oracle recommends using it for creating, modifying and deleting RAC databases.
Support for Recovery Through Resetlogs
The new %r tag in the archive log format means that a standby database can be rolled forward even if a resetlogs has occurred on the primary.
Flashback Support
As already mentioned you can use the Flashback feature to flashback a standby database to a point before it was activated and continue to use it as a standby.
log_archive_dest_n new “valid_for” Parameter
In Oracle 9i, configuring the log_archive_dest_n init.ora parameter to cope with switchovers and switchbacks meant many alert log errors being produced as, for example, the standby destination configured on the standby database tried to connect to the live database before the roles were switched over. In Oracle 10g, the new “valid_for” parameter allows you to configure an archive destination to only be active when the database is playing a certain role, meaning no more confusing and irrelevant alert log errors.
Real-time Apply Enhancements
It’s no longer necessary for a log switch to occur to apply data to a standby using the SQL apply method.
The Grid
The centrepiece of the publicity surrounding Oracle 10g is the concept of Grid Computing. Real Application Clusters (RAC), the high availability functionality introduced in Oracle 9i, has been completely revamped to provide the backbone for Oracle’s vision of grid computing (inevitably involving trying to make you purchase as many Oracle licenses as possible). By registering database applications as services within the RAC cluster configuration, you can assign a service to one or more nodes in RAC cluster meaning that behind the scenes Oracle will load balance the application across the nodes, taking advantage of idle CPU on some nodes when others start to become overloaded. The concept extends to the Oracle Application Server suite of products meaning you can have a complete grid-based and fault tolerant multi-tier application. Central to the management of all this is Grid Control, an enhanced version of the new web-based Oracle Enterprise Manager software that allows you to manage all aspects of your grid.
