Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







Oracle New Features

Robert Freeman



Robert Freeman is available for custom consulting and is a recognized expert in all areas of Oracle technology.  Freeman is the author of "Oracle Replication" and "Easy Oracle Jumpstart", a great new book for beginner Remote DBA's.

Oracle 9i New Features

Oracle9i is the latest version of the Oracle RDBMS. It comes in the form of releases one and two. In this release, Oracle has introduced new features and functionality throughout the database engine, and has also enhanced the functionality of many supporting products such as RMAN and OEM.  This presentation will provide an initial introduction to these new features so you will be aware of them so you can take advantage of them when opportunity permits. While this is a quick introduction to these features a more detailed treatment of Oracle9iís new features is available in Oracle Pressís Oracle9i New Features

SVRMGRL and Connect Internal Desupport

Oracle has told us it was coming, and now itís here. The Server Manager utility (svrmgrl) is no longer available in 9i. Now, database administration is done via SQL*Plus or OEM. Also, connect internal is no longer available in Oracle9i. To perform administrative activities on the database (like shutdown or startup) you need to login using an account that is assigned SYSRemote DBA privileges, using the as SYSRemote DBA login syntax. Here is an example of logging into the SYS account as SYSRemote DBA using SQL*Plus:


sqlplus ďsys/password as sysRemote DBAĒ


Other variations on the login syntax exist depending on if you are connecting locally or to a remote server/database. Oracle9i now comes with a new privilege called the grant any object privilege, that facilitates this ability. Of course, be careful granting this privilege to other accounts, as it is very powerful.

Security Enhancements Associated with the Database Creation Assistant and the SYS account

After the creation of a database with the Database Creation Assistant (DBCA) you will be prompted to enter new sys and system passwords of the database being created. Also, most user/schemas that are created by the DBCA, with the exception of SYS and SYSTEM, will be locked. Also note that SYS can now grant privileges on any object in the database. No longer do you have to grant privileges to sys to allow it to manage the grants on your objects.

Server Parameter Files (SPFILE)

Oracle9i offers to manage your database initialization file for you automatically in the form of a Server Parameter File (SPFILE). Rather than have to edit the database initialization file when you want to make a change, you simply issue an alter system command, to change the value in the SPFILE. When changing dynamic parameters you can opt to either dynamically change value for the current instance, but leave the SPFILE alone, or you can change it in the SPFILE and not impact the instance, or you can change both the instance and the SPFILE. If the parameter were a static parameter, then you would only change the SPFILE. Here is an example of changing a parameter in the SPFILE.


Alter system set sort_area_size=100m scope=both;


You can reset a parameter to itís default value using the alter system reset command. Additionally, you can open and read a SPFILE, as most of the settings are visible in it. The SPFILE should not be manually altered via any kind of text editor however, because Oracle includes information in the header and footer of the SPFILE to insure the integrity of the SPFILE.

You can convert an SPFILE to a text parameter file with the create pfile from spfile command. Conversely you can convert a text parameter file to a SPFILE with the create spfile from pfile command. If you are running RAC, Oracle9i allows your different instances to share the same initialization parameter file (SPFILE or text).  You can define global parameters for all databases, or specific parameters for a specific database within the same parameter file.

(2) Automated Undo Management

Rollback segment administration has been simplified in Oracle9i with the introduction of Automated Undo Management.  With this feature, the allocation and management of rollback segments becomes Oracleís responsibility. This feature is fairly easy to use. You simply:

         Create an UNDO tablespace with the create undo tablespace command or even at database creation time. Note that you can create multiple UNDO tablespaces but only one can be in use at a time.

         Modify the database initialization parameters particular to automated UNDO. These parameters include undo_management, undo_tablespace and optionally undo_suppress_errors.

         Restart the database.

When using automated undo management, Oracle will size and create the rollback segments automatically. Generally it will create 10 undo segments, and add additional undo segments, as the system load requires.

Resumable Space Management

How many times have you started a bulk SQL*Loader process or were importing data into a table in your database and had the load fail because you ran out of space? Failure of loads because of lack of available space can be a huge problem. Typically you have a limited window in which to perform the load. Often space related failures occur in the middle or towards the end of the load, which is a big problem because you donít have enough time to restart the load. Oracle9i comes to the rescue with resumable space management.

With resumable space management certain space related errors cause the session encountering the error to suspend for a specified amount of time, allowing the Remote DBA to correct the problem. Resumable space management can be used to suspend the session in the following circumstances:

         Running out of tablespace space.

         You have reached a max extent condition in the table or an associated index.

         You have exceeded a tablespace quota.

Most Oracle DML statements can use resumable space management features, though there are some restrictions with regards to objects in dictionary-managed tablespaces. Even parallel processing can take advantage of this feature. Also the Oracle Import utility and SQL*Loader utilities have new parameters that allow you to take advantage of this new feature.

To use resumable space management must you enable it on a session-by-session basis using the alter session enable resumable command. By default if the space condition is not corrected after 2 hours, then the transaction will fail. You can configure a larger or smaller value if your needs require. Once enabled, Oracle will automatically detect the space condition and suspend the session. Oracle will write an entry to the alert log that the session has been suspended. Additionally the Remote DBA_RESUMABLE view maintains a record of all currently suspended sessions. Once the Remote DBA has corrected the space problem the suspended session will automatically resume its operation at the point of suspension.

Oracle also provides an after suspend system trigger event, that allows you to automate your response to a session suspend condition. Further the dbms_resumable package is provided to allow for management of resumable space management from within SQL or PL/SQL.

Default Temporary Tablespaces

In previous versions of Oracle users were assigned SYSTEM as the default temporary tablespace. This could lead to problems if the Remote DBA was not careful to assign the user to the correct temporary tablespace when creating the account. Oracle9i solves this problem with the introduction of the default temporary tablespace. When a default temporary tablespace is defined, Oracle will assign that tablespace to each new user account as itís created. Of course, if you define a temporary tablespace for that user account, then that setting will override the default setting.

You define an Oracle default tablespace by using the alter database default temporary tablespace command. You can also define the default temporary tablespace for the database within the confines of the create database command. If you want to change the currently defined default temporary tablespace, simply use the alter database default temporary tablespace command. All users assigned to the old default temporary tablespace will now be assigned to the newly defined default temporary tablespace. Users assigned to other temporary tablespaces will remain unchanged.

Oracle Managed Files

Oracle Managed Files (OMF) is a new feature from Oracle that allows the database to manage just about all facets of Oracle database file administration. With OMF configured properly, you donít need to define the names or locations of Oracle files, the size of the file and you donít need to worry about removing the datafile after you have created it. All the Remote DBA need do is configure a few parameters and Oracle will do the rest.  OMF will manage creation of your database redo logs, control files and database datafiles for you automatically. If you drop a tablespace made up of OMF datafiles, Oracle will remove those datafiles from the file system.

Dynamic Memory Management

There are times when you want or need to modify the memory allocation of your Oracle database SGA. You might want to add memory to the database default buffer cache, or maybe the shared pool, you might even want to remove memory from one of these structures. Until Oracle9i the database had to be shutdown before you could make any modifications to memory allocations. Oracle9i offers the ability to dynamically alter many memory configurations via the alter system command.

Note that there are still some memory areas that are not dynamic. This includes the java_pool. Also, if you continue to use the db_block_buffers, buffer_pool_keep and buffer_pool_recycle parameters you will not be able to dynamically alter these memory areas either.

An additional parameter called sga_max_size is used to define the maximum amount of memory that can be allocated to the SGA. This parameter defaults to the total amount of memory allocated to the SGA when the database is first started. You can override this default by setting the sga_max_size parameter, but this will require you to shutdown and restart the database instance. Finally, be aware that if you set the sga_max_size parameter on most platforms the result will be that Oracle will acquire memory equivalent to sga_max_size from the operating system at database startup. Thus, even though your total SGA may only have 300MB allocated, if sga_max_size is set to 500MB, then 500MB will be acquired by Oracle from free memory. Thus, be cautious setting sga_max_size so that you donít take memory away from user sessions, applications and that you donít cause swapping to occur.

Multiple Database Block Size Support

In previous versions of Oracle, the database was one single consistent block size. Once that block size was fixed, that was it, the database block size was set in stone unless you recreated the database. This was problematic in many cases. For example if you wanted to transport tablespaces between databases of different block sizes, you could not. Further, in certain conditions, hybrid databases might benefit from database data residing in tablespaces of differing block sizes.

To solve this problem, Oracle9i allows you to assign different blocks sizes to each individual tablespace (except SYSTEM, Temporary and UNDO or Rollback segment tablespaces) as the tablespace is created. Additionally, tablespaces that are transported into the database can be a different block size than the default database block size.

We have already discussed the different shared memory sub-cache definitions. You will need to create shared memory sub-caches before you can transport in or create and tablespace with a block size other than the default block size.

The ability to create and/or transport tablespaces with multiple block sizes has numerous applications. For Hybrid databases, it is possible that tablespaces with smaller block sizes will be more efficient for OLTP access, while those with larger block sizes will be more efficient for reporting purposes. Also, in the past it would sometimes be difficult to transport tablespaces between OLTP systems and data warehouse or reporting systems because of differing block sizes of the databases. This is no longer the case.

Cursor Sharing in Oracle9i

Oracle8i introduced a feature called cursor sharing which provided the ability of the optimizer to convert literals within SQL statements into bind variables in certain situations. As a result, SQL statements that are alike with the exception of literal values, can share a given cursor. This has the impact of reducing the overall time to parse the SQL statement and, perhaps most importantly, reducing fragmentation of the shared SQL area of the shared pool. Unfortunately, one of the end results of cursor sharing and the use of bind variables is that the optimizer has a difficult time determining the selectivity of the data in the columns associated with the bind variable. This can lead to sub-optimal execution plans. You use the parameter cursor_sharing=force to enable cursor sharing.

Oracle9i now adds modifications to cursor sharing. If you set cursor_sharing=similar, the optimizer will be able to analyze the distribution of the data in the columns (using the analyzed statistics of the table, columns, associated indexes and any histograms that you may have generated, and determine if the parsed execution plan will be optimal. If the plan does appear to be optimal then the parsed SQL statement will be used.

Self Tuning PGA

Oracle9i can now self-tune the PGA for a given session. Previously the Remote DBA had a number of different parameters to choose from when tuning the memory allocated to an Oracle server session.  Now, just one parameter, pga_aggregate_target, can be used to define the total amount of physical memory that should be made available for use by all dedicated server processes. Using this value, Oracle will then derive values for parameters such as sort_area_size, hash_area_size, bitmap_merge_area_size, and create_bitmap_area_size. Note that you can still tune individual parameters if you should wish to do so.

Online Table Redefinition

Using the new dbms_redefinition package, you can redefine a table online, while the data in that table is still available for users to query or execute DML against. You can move the entire table, specific partitions or any number of combinations of operations during the redefinition. Other operations might include renaming columns, moving the table to a new tablespace, converting the table to an IOT, or into a partitioned table and so on. There are a number of rules and limitation with regards to online table redefinition, as you might expect.

Miscellaneous Administrative Features

There are a number of other administrative features that are new to Oracle9i. You can now instruct Oracle to remove database datafiles when dropping a tablespace by using the new including contents and datafiles clause of the drop tablespace command.

Oracle9i now also gives the Remote DBA the option to prohibit any nologging operation on the database with the new force logging clause of the alter database or create database command. This is a great option to have if you are administering a stand-by database environment.

If you have ever had your database crash on you in the middle of a hot backup, you know what a pain it can be to get all of the database datafiles out of hot backup mode. Oracle9i comes to the rescue with the alter database end backup command. Now just one command will take all the database datafiles out of hot backup mode.

Oracle9i also makes it easier to convert from LONG data types to LOB data types. This is facilitated through the alter table command. Be careful of the additional disk space requirements that accompany this conversion. You will need about twice the space of the original long to perform the conversion.

Another nice new feature is that sys truly becomes a privileged administrative account. Prior to 9i, the sys account could not grant direct access rights to objects it did not own, unless the owner of the object gave sys the rights to do so. This is no longer the case in Oracle9i. Now sys can grant and revoke access to any object in the database at will. This new feature is closely is associated with the new Oracle9i privilege, grant any object which can be granted to any user to allow them to administer grants throughout the database.

One final thing you might be interested in is that the system tablespace can now be locally managed in Oracle9i Release 2. In fact, if you use the database creation assistant in 9iR2, it will create the system tablespace as a locally managed tablespace by default. Note that if you make the system tablespace locally managed that no other tablespaces in the database will be able to be dictionary managed.

New Partitioning Options and Features

Oracle9i comes with some new partitioning options that you will want to know about. First, Oracle9i introduces a new type of partitioning called list partitioning. With list partitioning you can define a list of values associated with a partition key column, and assign those values to a specific partition.

For example, if you had a retail operation that operated in all fifty states and you often did lookups on your customers by state, you might want to use list partitioning to partition your customer information by state. 

Oracle9i Release two builds on the list partitioning feature by allowing you to build range partitioned tables that are sub-partitioned using the list partition method. Also 9iR2 offers an option to create a MAXVALUE partition for a list partitioned table which was not available in 9iR1. Also in 9iR2, Oracle has made some changes to split partition operations that make partition split operations more efficient.

Extraction of Object Metadata

Many Remote DBAís have either crafted their own scripts to extract DDL from the database data dictionary; of they have purchased a tool to do it for them. Oracle9i makes the job of extracting database object DDL much easier with the introduction of the dbms_metadata package. The dbms_metadata package allows you to extract the DDL for objects within the database in straight text format, or you can opt to extract it in XML format.


Automatic Segment Space Management

In previous versions Oracle tracked block availability with freelists that kept track of all blocks that were available to write to. The method of tracking free space in a segment could be contentious at times, and cause performance problems. While freelist space management is still available (and is the default setting), Automatic Segment Space Management (ASSM) is a new feature in Oracle9i that is designed to simplify free space management of segments and reduce the contention that can accompany the use of freelists. ASSM can only be enabled within a locally managed tablespace, and all segments within a tablespace designated to use ASSM must use ASSM.  To create a tablespace that will use ASSM, you use the segment space management auto parameter of the create tablespace command.

When you create a segment in a tablespace using ASSM, Oracle will create a series of bitmap blocks known as bitmapped blocks (BMBS) that will be stored in the segment being created (typically at the beginning, but other BMBS can be added anywhere in the segment as needed). The BMBS are kept current by Oracle as data in the segment is being modified and the BMBS are used to keep track of data block space allocation.

ASSM eliminates the need for freelist groups, commonly associated with a Real Application Cluster (RAC) environment. ASSM leads to better performance in many cases, and particularly if your segments contain rows that vary in size.  In a Real Application Clustered environment, segments built using ASSM can perform much better than those using FREELIST space management.

Should you use ASSM? ASSM isnít the silver bullet for all of your problems, but if your problems revolve around freelist contention, ASSM may well be your answer.

Skip Scanning of Indexes

Oracle9i has changed the rules when it comes to indexes. Oracle9i now can perform an index skip scan operation. The skip scan operation allows the Oracle optimizer to consider any column within an index for an index scan operation, even if that column is not on the leading edge of the index. This new feature has several impacts that you will want to consider. First, it means that when you are migrating that you might find your SQL queries getting different execution plans (and hopefully running faster because of this). This also means that the execution plan of some of your hand crafted and hinted SQL queries might change as well. So, test carefully and make sure that this feature will not have a negative impact on your database.

Bitmap Join Indexes

Oracle9i offers some new indexing options that can help you to improve performance of your database. A bitmap join index creates an index that is, for all practical purposes, a pre-join of columns from two or more different tables represented in the form of a bitmap. If you have table joins that involve columns with a relatively small number of distinct values, then a bitmap join index might work for you.

Mapping tables and Bitmap Indexes on Index-Organized Tables

Oracle9i now allows you to create bitmap indexes on index-organized tables (IOT). To create a bitmap index on an IOT you must first create a mapping table on the table. The mapping table translates the bit in the index to a logical ROWID in the IOT. You create a mapping table when you create the IOT by using the mapping table clause of the create table command.

Note that you cannot add a mapping table to an existing IOT. To add a mapping table to an existing IOT, you must rebuild the IOT and create the mapping table at that time. 

One of the main purposes of mapping tables is to support another Oracle9i new feature. This feature is the ability to create secondary bitmap indexes on IOTís. You can create multiple bitmap indexes on a single IOT in 9i, all of which will be supported by the single mapping table.

Other 9i Index New Features

Oracle9i has added additional index functionality features. These new features include:

         You can now create, rebuild or coalesce IOT secondary indexes online.

         Parallel DML on IOTís is now supported.

         You can now move IOTís with overflow segments online in Oracle9i.

         You can now monitor index usage with the monitoring usage clause of the alter index command. The monitoring of an index is binary in nature. That is to say, you only know if the index have been used, but not how many times it has been used or how recently.

External Tables

Oracle9i now allows you to access external files from within the database directly through the use of the new external table feature. An external table is defined within the database, and points to a physical datafile that is present on the server where the database operates. You create an external table with the create table command, using the new organization external clause. Once the table is defined, it can be accessed with normal SQL SELECT statements. Note that you cannot currently create any indexes on external tables. If you wish to remove an existing external table, just drop it with the drop table command.

View Constraints

Query rewrite depends on the definition of constraints between related tables to work properly. This has been a problem in the past because you could not create constraints on views, thus if a view were built on a dimension or fact table, and that view was used in a SQL statement, Oracle could not take advantage of query rewrite (and thus, perhaps, take advantage of a materialized view).

To solve this problem, Oracle9i introduces view constraints. You can now define primary key, unique key and foreign keys on a view when you issue the create view command. Alternatively you can add constraints to the view through the alter view command. Note that any constraint that is defined will not be validated, and that NOT NULL constraints are inherited from the base table.

Multi-Table Insert Statements

Often source data is destined to more than one table. In these cases before Oracle9i, multiple INSERT statements would be required. This results in additional unnecessary IO to the source table in order to populate the different tables. Oracle9i introduces multi-table insert statements. Multi-table inserts come in 3 different forms:

         Unconditional Ė Inserts the given data into multiple tables without restriction.

         Pivoting Ė Used to insert data from a denormalized structure, into one or more tables.

         Conditional Ė Provides for conditional control of each insert based on established specific criteria.

Associative Arrays

Until Oracle9i we could only associate a numeric data type as the index to an array of a PL/SQL table via the index by binary_integer option when defining that PL/SQL table. Oracle9i now allows you to index on a varachr data type using the index by varchar2(n) option.


Oracle CASE Statements and Expressions

Oracle8i offered a case statement within SQL, but there was no such statement available in PL/SQL. Within PL/SQL, Oracle9i offers two variations of the case command, simple and searched. Case statements do not return a value whereas case expressions do return a value. Both types of the case commands are available in two flavors: simple or searched. A simple case command evaluates only a single value whereas a searched case value can evaluate multiple values.


Oracle Merge Statements

During different load processes, you have a case where you want to insert a record if one doesnít already exist, or update a record if it already exists. Previously you would need to write PL/SQL to perform such an operation. The new merge statement is designed for just such a situation.  The merge statement will allow you to insert a record into a table if it doesnít already exist, and will allow you to update an existing record in a table, during the execution of the statement.

Support for ANSI/ISO SQL 1999 Compliance

Oracle has added new SQL operators to come into compliance with the ANSI/ISO SQL 1999 standard. These new operators are:

         Cross join Ė Which produces a cross product of two tables, resulting in a Cartesian join.

         Natural join Ė Performs a join based on like columns in two tables.

         Using Ė allows specification of columns to be used as the equijoin when performing the join.

         On Ė This clause can be used to restrict the result set returned by a statement.

         Left outer join Ė performs a left outer join.

         Right outer join Ė performs a right outer join.

         Full outer join Ė Performs an outer join on both tables.

Note that even though the outer join SQL 1999 syntax is included in Oracle9i, the old outer join operator (+) is still available.

Looking at Cached Execution Plans

Sometimes explain plan output can be wrong. Not often, but it can happen. If you want to see the real execution plan that Oracle is using for a given query, you can use the new v$sql_plan view. This view, looking much like the Oracle plan_table view, contains the execution plans for all SQL statements currently in the shared SQL area. It also contains address information so you can join to v$sqlarea, if you need to get at the text of the SQL statement or itís execution statistics of the given SQL statements.

Generate an Explain Plan Using DBMS_XPLAN

Every Remote DBA has his own script to format the results of the plan table. Problem is that you have to maintain those scripts, and you have to tote them around with you to different work sites if you are consulting. Oracle9i solves this problem with dbms_xplan. You can use dbms_xplan to display and format the execution plan for you!


Column Defaults

Now, you can define default values for a given column, which will only be used if the keyword default is used in a SML statement operating against that table. You can define a column default when creating a table with the create table command, or you can add a default value to an existing column with the alter table command.


New Date and Time Datatypes, Functions and functionality

Oracle9i has introduced the concept of time zone offsets to the database. Thus, you can establish what time zone your server is, and record times based on an offset from the server time zone. The time zone can also be set at the session level, allowing applications to establish a time zone setting when they execute.

Also in 9i, we find the introduction of several new date and time data types. The primary purpose of these new date/time data types is to provide an additional level of precision with regards to elapsed time, with a possible precision of up to 9 digits. This precision is limited by the precision of the underlying operating system.

In addition to the new date/time datatypes, Oracle has introduced a host of new functions related to date and time. These include functions to manipulate the new date/time datatypes listed above and to deal with the new time zone features in Oracle9i.

Better Support for LOBís

Oracle9i now provides native support for LOBís of up to 32k. This means that character based functions such as substr, will now work with LOBís of up to 32k. Also, you can use the alter table command to effortlessly can convert LONGís within a table to LOBís. Be aware that there are some significant temporary space requirements associated with the conversion of a LONG column to a LOB, so be prepared.

PL/SQL Native Compilation

Oracle9i supports native compilation of PL/SQL stored procedures. This means that you can compile PL/SQL with your C compiler, and they will work much faster. You will need to set parameters to indicate to Oracle that you wish to compile the procedure natively, and Oracle even supports compilation of the PL/SQL packages they supply.

Character vs. Byte Semantics

Oracle9i now allows you to define storage of a character type using either the length of the character, or in bytes. This has application in multi-byte character code sets (such as Unicode) to ensure that a character column can store the number of characters required.

Fine Grained Auditing

Oracle9i now allows you to audit all SELECT access against a specific table. Auditing occurs after you create specific audit policies that define the degree of auditing that should occur. The criteria defined in an audit policy can be granular down to the selection of a specific column or columns and based on a range of values. When SQL statements are executed by Oracle, the audit policies are checked to see if the SQL statement merits auditing. If so, a record will be written to the Remote DBA_FGA_AUDIT_TRAIL table for the Remote DBA or security staff to review later.

Fast Start Time-Based Recovery

Fast start time-based recovery (FSTBR) is a new Oracle9i feature that is designed to reduce the overall time it takes to perform crash or instance recovery. You configure FSTBR by setting the fast_start_mttr_target parameter, defined in seconds (0 to 3600), in the database parameter file or SPFILE. Based on this setting, Oracle will dynamically derive other database parameter settings so that the requested mean time to recover (mttr) will be as close to the requested time as possible. Fast_start_mttr_target replaces several parameters used in Oracle8i and earlier such as db_block_max_dirty_target (which is now an obsolete parameter), fast_start_io_target, and log_checkpoint_interval. Any of these parameters can be set manually to override the derived values that Oracle will assign to them based on the setting of fast_start_mttr_target.

Flashback Query

Oracle9i offers the ability to look back into the past, and see how the data looked at a specific point in time. This functionality is known as flashback query. With flashback query you first define a point in time (or SCN) to flashback to at the session level using the dbms_flashback package. Once you have defined the point in time that you wish to flashback to, all subsequent queries for that session will produce results that reflect the committed state of the object being queried at approximately that flashback point in time. Once you have completed your flashback queries, you then disable flashback query and all rows subsequently returned will represent the current temporal state of the database. Oracle9i Release 2 adds additional functionality to flashback query by allowing you to specify a flashback time for a specific individual SQL statement via the new as of clause. 

Note that we indicated the rows returned were from approximately the flashback time requested. Oracle does round the flashback time or SCN number to in 5-minute increments. Also, you can only flashback approximately 5 days because of internal restrictions that Oracle has placed on referencing the time and SCN. Finally, if you want to use flashback query, all of the undo generated from the point in time you wish to flashback to, must be available. Oracle uses this undo to generate the read-consistent images that it will need to construct the flashback data. If this undo is not available, then the flashback query will return an error.

RMAN New Features

RMAN has come a long way since itís first introduction in Oracle8. Oracle9i offers a RMAN that is very functional and feature rich. RMAN in Oracle9i now offers configurable default parameters. By configuring default values for channels, level of parallelism, there is often no longer a need for a run block when performing a backup of the database. So, now database backups are often as simple as using the commands backup database. Oracle9i also allows you to backup your database and the archive logs together in one operation with the backup database plus archivelog command.

Some sites like to backup their database to disk, and then later backup those backup set pieces to tape, Orace9i RMAN makes this easy as it now has the ability to backup backup sets. Using optional arguments, you can define which backup sets you want backed up based on time or date of the backup, or other criteria.

Recovery of the control file and the database SPFILE is now much easier with the ability to automatically backup of these critical components with every backup. Simply issue the command configure controlfile autobackup on and Oracle will include the control file and database SPFILE (if one is being used) at the end of every backup. Also, if you have enabled automated backups of your control file, Oracle will backup the control file, to disk only, every time you make a change to the database that impacts the control file.

Oracle also makes recovery of the control file much easier, even when you are not using a recovery catalog. All you need do is set the database DBID and in some cases allocate a channel to the backup device, and Oracle will search for the most current backup of the database control file.

A final new Oracle9i RMAN feature we want to mention is Block Media Recovery. This functionality allows you to restore corrupted blocks from your backup sets, online. Thus, if you receive an error indicating a given block is corrupted, RMAN can recover that block for you, while the rest of the tablespace remains online.

RMAN in Oracle9i has a number of new features, and we have covered a number of them here. Still, we donít have enough pages here to even mention them all, let alone do them justice. For a complete treatment on RMAN in Oracle9i, see the Oracle Press Title Oracle9i RMAN Backup and Recovery by Robert Freeman for more details (Howíd that guy get in this chapter twice?).

Log Miner New Features

Log Miner has several new features that you can take advantage of in Oracle9i. First, you can instruct Log Miner to only mine committed transactions. Further, Log Miner can now translate DML statements associated with database clusters. Log Miner also supports translation of database DDL statements in Oracle9i. Log Miner also supports new functionality to determine if the catalog you are using is stale, and allows you several different options with regards to the dictionary you use to translate object information during the mining process. Finally, Log Miner can now skip redo log corruption, providing an avenue for recovery of more transactions in the event of a major database failure.

Oracle9i Data Guard

Data guard in Oracle9i replaces Oracle8iís stand-by database architecture. It offers several new features in Oracle9i including the ability to configure the standby database architecture in a synchronous, no data loss mode. In synchronous no data loss mode, the primary server will not complete a commit until the changes have been recorded on at least one of the remote stand-by servers. This ensures that there will be zero data divergence between the primary database and at least one of the stand-by servers at al times, at some performance cost. Oracle9i also allows you to manage many data guard configurations from OEM, making the administration of a data guard environment easier.

Finally, Oracle9i Release two introduced the concept of a logical stand-by database. Using another new feature, Oracle streams, Oracle will apply the SQL executed on the primary database directly to the logical stand-by database site rather than applying redo from the archived redo logs. As a result the logical standby database can be open read-only while changes from the primary database continue to be applied. Additionally you can add additional indexes on the stand-by database that are not present on the primary database.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.