|
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.
|
