| Oracle9i
Database README
Release 2 (9.2) |
|
Contributing Editors: Cathy Baird, Mark Bauer, Ruth Baylis,
Bridget Burke, Ted Burroughs, Phil Locke, Diana Lorentz, Kevin MacDowell,
Carol Sexton, and Randy Urbano
The Programs (which include both the software and documentation)
contain proprietary information of Oracle Corporation; they are provided
under a license agreement containing restrictions on use and disclosure
and are also protected by copyright, patent and other intellectual and
industrial property laws. Reverse engineering, disassembly or decompilation
of the Programs, except to the extent required to obtain interoperability
with other independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the US Government or anyone licensing or using the Programs on behalf of the US Government, the following notice is applicable:
Programs delivered subject to the DOD FAR Supplement are
commercial computer software and use, duplication and disclosure of the
Programs including documentation, shall be subject to the licensing restrictions
set forth in the applicable Oracle license agreement. Otherwise, Programs
delivered subject to the Federal Acquisition Regulations are 'restricted
computer software' and use, duplication, and disclosure of the Programs
shall be subject to the restrictions in FAR 52.227-19, Commercial Computer
Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle
Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be licensee's responsibility to take all appropriate fail-safe, back up, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the Programs.
Oracle is a registered trademark, and SQL*Plus, Oracle9i,
Pro*C, Pro*C/C++, Pro*COBOL, Pro*FORTRAN and PL/SQL are trademarks or registered
trademarks of Oracle Corporation. Other names may be trademarks of their
respective owners.
All trade names referenced are the service mark, trademark,
or registered trademark of the respective manufacturer.
Documentation
Accessibility:
Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at
http://www.oracle.com/accessibility/Accessibility of Code Examples in Documentation:
JAWS, a Windows screen reader, may not always correctly read
the code examples in this document. The conventions for writing code require
that closing braces should appear on an otherwise empty line; however,
JAWS may not always read a line of text that consists solely of a bracket
or brace.
Accessibility
of Links to External Web Sites in Documentation:
This documentation may contain links to Web sites of other
companies or organizations that Oracle Corporation does not own or control.
Oracle Corporation neither evaluates nor makes any representations regarding
the accessibility of these Web sites.
-----------------------------------------------
CONTENTS
This README file is relevant only to the delivered Oracle9i
release 2 (9.2) product and its integral parts, such as SQL, PL/SQL, the
Oracle Call Interface (OCI), SQL*Loader, Import/Export utilities, and so
on. There is also a separate README file for Oracle Net Services (known
in previous releases as Net8) and Oracle Ultra Search, Oracle Internet
Directory, and Oracle Advanced Security. There may also be separate README
files for precompilers and other Oracle products.
For information about upgrading, downgrading, and migration,
see Oracle9i Database Migration.
This README documents any differences between the server
(and its integral parts) and its documented functionality, as well as known
problems and workarounds. For additions and corrections to the server documentation
set, please refer to the Oracle9i Documentation Addendum, which
is available on the product CD. A list of open known bugs is also available
on the product CD.
Operating system releases, such as UNIX, Windows NT, OpenVMS,
and so on, often provide an operating system specific README document.
Additional README files may also exist for specific Oracle products such
as SQL*Forms. This README file is provided in lieu of system bulletins
or similar publications.
Please read the cover letter included with your Oracle9i
release 2 (9.2) package.
Files on Line, which shipped with the previous release of
the database, will be included with Oracle9iAS release 9.0.2.
Oracle9i Database (9.2) product documentation is available
on the documentation CD and on the Oracle Technology Network (OTN). Updates
to the documentation on the CD may be available only on OTN.
The following list of books describes the Oracle9i
Database documentation set available on the Documentation CD.
A96531-01 Oracle9i Database New Features
A96521-01 Oracle9i Database Administrator's Guide
A96524-01 Oracle9i Database Concepts
A96525-01 Oracle9i Database Error Messages
A96530-01 Oracle9i Database Migration
A96533-01 Oracle9i Database Performance Tuning Guide
and Reference
A96532-01 Oracle9i Database Performance Planning
A96536-01 Oracle9i Database Reference
A96652-01 Oracle9i Database Utilities
A96529-01 Oracle9i Database Globalization Support
Guide
A96539-01 Oracle9i Sample Schemas
A96540-01 Oracle9i SQL Reference
A96544-01 Oracle9i Heterogeneous Connectivity Administrator's
Guide
A97290-01 Oracle9i Database Generic Documentation
Master Glossary
A96625-01 Oracle9i Database Generic Documentation Master Index
A96519-01 Oracle9i Backup and Recovery Concepts
A96566-01 Oracle9i Recovery Manager User's Guide
A96565-01 Oracle9i Recovery Manager Reference
A96564-01 Oracle9i Recovery Manager Quick Reference
A96572-01 Oracle9i User-Managed Backup and Recovery
Guide
A97202-01 Oracle9i Backup and Recovery Documentation Online Roadmap
A96520-01 Oracle9i Data Warehousing Guide
A95959-01 Oracle9i Data Mining Administrator's Guide
A95961-01 Oracle9i Data Mining Concepts
A96567-01 Oracle9i Advanced Replication
A96568-01 Oracle9i Replication Management API Reference
A90842-01 SQL*Plus User's Guide and Reference
A90843-01 SQL*Plus Quick Reference
A96629-01 Oracle9i Data Guard Broker
A96653-01 Oracle9i Data Guard Concepts and Administration
A96580-01 Oracle9i Net Services Administrator's Guide
A96581-01 Oracle9i Net Services Reference Guide
A96573-01 Oracle Advanced Security Administrator's Guide
A96578-01 Oracle Label Security Administrator's Guide
A96582-01 Oracle9i Security Overview
A96574-01 Oracle Internet Directory Administrator's Guide
A96577-01 Oracle Internet Directory Application Developer's
Guide
A96579-01 Oracle9i Directory Service Integration and Deployment Guide
A96599-01 Oracle9i Real Application Clusters Documentation
Online Roadmap
A96596-01 Oracle9i Real Application Clusters Administration
A96600-01 Oracle9i Real Application Clusters Setup
and Configuration
A96597-01 Oracle9i Real Application Clusters Concepts
A96598-01 Oracle9i Real Application Clusters Deployment
and Performance
A96601-01 Oracle9i Real Application Clusters Real
Application Clusters Guard I - Concepts and Administration
A96590-01 Oracle9i Application Developer's Guide -
Fundamentals
A96587-01 Oracle9i Application Developer's Guide -
Advanced Queuing
A96591-01 Oracle9i Application Developer's Guide -
Large Objects (LOBs)
A96594-01 Oracle9i Application Developer's Guide -
Object-Relational Features
A96628-01 Oracle9i Application Developer's Guide -
Workspace Manager
A96621-01 Oracle9i XML Developer's Kits Guide - XDK
A96620-01 Oracle9i XML Database Developer's Guide
- Oracle XML Database
A88895-01 Oracle9i Case Studies - XML Applications
A96595-01 Oracle9i Data Cartridge Developer's Guide
A96584-01 Oracle Call Interface Programmer's Guide
A96583-01 Oracle C++ Call Interface Programmer's Guide
A96624-01 PL/SQL User's Guide and Reference
A96108-01 Pro*C/C++ Precompiler Programmer's Guide Vol-I
A97263-01 Pro*C/C++ Precompiler Programmer's Guide Vol-II
A97269-01 Pro*C/C++ Precompiler Programmer's Guide (whole
set)
A96109-01 Pro*COBOL Precompiler Programmer's Guide
A42523-01 Pro*FORTRAN Supplement to the Oracle Precompilers
Guide
A42525-01 Programmer's Guide to the Oracle Precompilers
A58231-01 SQL*Module for Ada Programmer's Guide
A96616-01 Oracle9i XML API Reference - XDK and Oracle
XML Database
A96621-01 Oracle9i XML Developer's Kits Guide - XDK
A96609-01 Oracle9i Supplied Java Packages Reference
A96612-01 Oracle9i Supplied PL/SQL Packages and Types Reference
A96120-01 Oracle interMedia Annotator User's Guide
A96121-01 Oracle interMedia Java Classes User's Guide and
Reference
A88786-01 Oracle interMedia User's Guide and Reference
A96630-01 Oracle Spatial User's Guide and Reference
A88787-01 Oracle Syndication Server User's and Administrator's Guide
A96517-01 Oracle Text Application Developer's Guide
A96518-01 Oracle Text Reference
A96670-01 Oracle Enterprise Manager Administrators Guide
A96673-01 Oracle Enterprise Manager Configuration Guide
A96674-01 Oracle Enterprise Manager Concepts Guide
A96675-01 Oracle Enterprise Manager Event Test Reference
Manual
A96671-01 Oracle Enterprise Manager Messages Manual
A86647-01 Database Tuning with the Oracle Tuning Pack
A96679-01 Getting Started with Oracle Change Management Pack
A88720-01 Getting Started with Oracle Management Pack for
Oracle Applications
A88748-01 Getting Started with the Oracle Diagnostics Pack
A96680-01 Getting Started with the Oracle Standard Management Pack
A96656-01 Oracle9i Java Developers Guide
A96659-01 Oracle9i Java Stored Procedures Developers
Guide
A96654-01 Oracle9i JDBC Developers Guide and Reference
A96658-01 Oracle9i JPublisher Users Guide
A96655-01 Oracle9i SQLJ Developers Guide and Reference
A96657-01 Oracle9i Support for JavaServer Pages Reference
A95297-01 Oracle9i OLAP Developer's Guide to the OLAP
API
A95298-01 Oracle9i OLAP Developer's Guide to the OLAP
DML
A95295-01 Oracle9i OLAP User's Guide
A96644-01 Oracle Workflow Client Installation Notes (release
2.6.2)
A95265-03 Oracle Workflow Guide (release 2.6.2)
A96643-01 Oracle Workflow Server Installation Notes (release 2.6.2)
A88783-01 Oracle Dynamic Services User's and Administrator's
Guide
A96676-01 Oracle Intelligent Agent Users Guide
A96672-01 Oracle SNMP Support Reference Guide
A96690-01 Oracle Universal Installer Concepts Guide
A97204-01 Oracle Ultra Search Online Documentation
In order to fully maximize the security features offered by Oracle9i, Release 2 (9.2), it is imperative that Oracle9i itself is well protected. To provide guidance for configuring Oracle9i in a secure manner, Oracle provides a security checklist. Oracle recommends that you read this checklist and configure your database accordingly. The security checklist can be found at the following URL:
http://otn.oracle.com/deploy/security/oracle9i/pdf/9iR2_checklist.pdf
Note that Chapter 23, "Establishing Security Policies", of
the Oracle9i Database Administrator's Guide, Release 2, also
contains a copy of the security checklist. However, the checklist has been
updated since the close date for publication of the book. Please refer
to the listed URL for the most recent version of the security checklist.
In Oracle9i, Release 2 (9.2), all default database
user accounts except SYS, SYSTEM, SCOTT, and
DBSNMP are locked upon database creation using the Database Configuration
Assistant (DBCA).
At the end of the database creation process, the DBCA displays
a page requiring that you change the default passwords for user accounts
SYS and SYSTEM. This page prompts you to enter and confirm
new passwords for the SYS and SYSTEM user accounts. It
also provides access to a password management page that enables you to
selectively unlock and specify new passwords for other Oracle9i
default user accounts.
If you create a database using the DBCA in silent mode, and
pass the -passwordDialog true parameter, then Oracle also presents
a dialog requiring that you change the default passwords for user accounts
SYS and SYSTEM.
You can optionally activate locked accounts manually at a
later time by using, for example, an ALTER USER statement.
Data dictionary protection is now enabled by default. Specifically,
the O7_DICTIONARY_ACCESSIBILITY initalization parameter is set
to FALSE on installation. As a result, regular users who are not
database administrators with ANY privileges (for example, SELECT
ANY TABLE) can no longer use the ANY privilege upon data
dictionary objects; however, the user can access non-SYS schema objects
using the ANY privilege.
Users making a database administrator user type connection
(for example, CONNECT / AS SYSBDA)
can exercise privilege on data dictionary objects, since SYSDBA
has all privileges.
A new system privilege, SELECT ANY DICTIONARY, provides
users with SELECT access to any object in the SYS schema
without giving them DBA privileges.
For example,
Fred, a non-DBA user, has the SELECT ANY TABLE privilege. He is able to view tables in his schema, and other non-SYS schemas. Fred is a developer and requires SELECT access to data dictionary objects. A database administrator can grant Fred SELECT ANY DICTIONARY privilege to allow Fred to select, but not update, dictionary objects.
Oracle Corporation recommends that the dictionary protection
feature remain enabled as it is a more secure configuration. If O7_DICTIONARY_ACCESSIBILITY
is set to TRUE, then regular users with the ANY privilege
can use these privileges -- perhaps maliciously -- to alter data dictionary
objects.
Due to stringent access control enforced by Fine Grained
Access Control, queries that are subjected to any policy predicate may
encounter ORA-28133 when attempting to execute a DDL statement
against the VPD-enabled object. The workaround is to temporarily disable
VPD or use EXEMPT ACCESS POLICY privilege to complete the DDL
changes. Please also report this error as a bug. The exceptions to this
are DBMS_STATS and ALTER TABLE .. ADD
COLUMN with DEFAULT value.
When session resource limits are exceeded, the session will
be automatically closed.
The tablespace size recommendations shown in Table 2-1 of Oracle9i Real Application Clusters Setup and Configuration have increased for four of the tablespaces as follows:
SYSTEM -- 420MB TEMP -- 120MB UNDOTBS -- 320MB ODM -- 280MB
Only use oradebug commands as documented in Oracle9i
Real Application Clusters Deployment and Performance with the supervision
of Oracle Support.
The tablespace for the Oracle XML DB feature is incorrectly
referred to as the XML tablespace, and its datafile file name
contains the string xml instead of xdb.
In Oracle9i Real Application Clusters Setup and
Configuration Release 2 (9.2), change the references to XML
tablespace to XDB tablespace and change xml to xdb
in the datafile names. These appear in Tables 2-1, 2-2, 2-3, and 2-4, and
in Example 2-1. For example, the datafile name db_name_raw_xml_50m
should be db_name_raw_xdb_50m and the datafile symbolic link name
db_name_xml should be db_name_xdb.
To perform deinstallation in the Real Application Clusters
environment, please do not choose the deinstall products option from the
Welcome page in OUI.
Please follow the following steps to perform Real Application Clusters deinstallation.
This section contains recommendations on upgrading to this
release, and on preparing for future changes (for example, to comply with
future SQL ANSI standards). If you follow these recommendations, upgrading
to newer releases of Oracle will be simplified.
Starting with Oracle9i release 9.2, the nesting of
quotation marks using the backslash (\) escape character is no longer supported.
This may affect how Oracle interprets parameter values in your initialization
parameter file or server parameter file.
For example, assume you have the following initialization parameter in your parameter file:
CONTROL_FILES = "ctlfile\'1.ora"
In releases prior to release 9.2, the filename would be interpreted
as ctlfile'1.ora. Starting with release 9.2, the filename in the
previous example would be interpreted as ctlfile\'1.ora.
Oracle Corporation highly recommends modifying your parameter
files to remove such references. Refer to the Oracle9i Database
Reference for other methods of nesting quotation marks in initialization
parameter values.
CONNECT INTERNAL has been desupported in this release. Users making DBA-privileged connections to the database must use one of the following syntaxes:
CONNECT / AS SYSDBA or CONNECT <username/password> AS SYSDBA
Users who have relied on CONNECT INTERNAL in connect strings
must replace CONNECT INTERNAL with one of the above commands. See the Oracle9i
Database Administrator's Guide for further details.
Oracle recommends not changing SQL.bsq before installation
because some modifications may jeopardize customer supportability.
LU6.2 protocol is not supported for Oracle9i. Migrate
or upgrade to TCP/IP based protocols.
There is a known problem with connecting from a client using the 8.1.7.0 or 8.1.7.1 JDBC Thin driver to the 9.0.1 Oracle database. The problem causes the Oracle server to return ORA-600 errors during operation. A JDBC patch is available for download from:
http://metalink.oracle.com/metalink/plsql/m12_documents.showNOT?p_id=147731.1
The patch must be applied to the 8.1.7.0 and 8.1.7.1 JDBC
drivers to ensure proper operation.
For up-to-date information about database URLs and connection cache timeouts in the JDBC Developer's Guide and Reference, please see
http://otn.oracle.com/tech/java/sqlj_jdbc/content.html
If an upgrade is performed to RDBMS 9.2, from an earlier
database version in which OJMS queues or topics already exist, then the
OJMS type bodies that contain PL/SQL member functions may become invalid
and unusable. The OJMS PL/SQL types and the AQ queues and topics that were
created using these types however remain usable and no data is lost or
corrupted. The OJMS Java client library functionality is also not affected.
This problem is documented in Bug 2365565 and a patch can be downloaded
from Metalink.
When upgrading an Oracle9i release 9.0.1 database
that does not have ODM installed, DBCA will not permit configuring ODM
in the upgraded database if XML DB is not already configured. The ODM option
is grayed out and, therefore, cannot be selected.
ODM requires that the XML database option be either configured
or be selected to be configured. If you select the Oracle XML DB feature
and then select ODM, you will be able to configure ODM.
The seed database now contains a set of sample schemas (in
addition to the traditional SCOTT schema), which are installed
by default when you install Oracle. These sample schemas are used in many
of the examples that appear in the documentation and training materials
for Oracle9i. These new schemas are documented in the book Oracle9i
Sample Schemas, which is on the documentation CD.
Calls from one server to functions and procedures on a second
server running a different version of Oracle may have problems when using
defaulted argument values if one of the servers is a 8.0.X release. Symptoms
may vary depending upon the source code involved. The errors PLS-801[1411]
and PLS-801[1407] are quite likely, but other symptoms, including
silently wrong results, are possible. The suggested workaround is to provide
an explicit value for each argument on any call through a database link
if it is possible that the sending and receiving systems are running different
versions of Oracle.
For functions implemented in PL/SQL, the following checks
are not completely enforced for argument or return types: the datetime
and interval day to seconds fractional seconds precision check, and the
interval leading field precision check. This affects the precision part
of the datetime/interval data when SQL calls a PL/SQL function.
In Oracle9i release 2, EXTPROC, by default,
will only load DLLs that are in the $ORACLE_HOME/bin or $ORACLE_HOME/lib
directory. Also, only local sessions, that is, Oracle client process that
are running on the same machine, are allowed to connect to EXTPROC.
To load DLLs from other directories, the environment variable
EXTPROC_DLLS should be set. The value for this environment variable
is a colon (:) separated list of DLL names qualified with the complete
path.
For example:
EXTPROC_DLLS=/private1/home/scott/dll/myDll.so:/private1/home/scott/dll/newDll.so
The preferred method to set this environment variable is
through the ENVS parameter in the listener.ora file.
Please refer to the Oracle Net manual and the Oracle9i Application
Developer's Guide - Fundamentals for more information on the EXTPROC
feature.
The BUFFERS keyword is no longer supported for input
file processing.
For direct path loads in previous versions of SQL*Loader,
the size of a CHAR or RAW field specified with no length
in the data file was assumed to be the same size as the column in the table.
As of Oracle9i, direct path loads operate in the same way as conventional
path loads and assume a length of 255.
SQL*Loader does not support multi-level collections in Oracle9i.
One possible workaround is to load each table individually as a single-table
load, and supply your own correlation information; for example, supply
the SIDs. If you do this, you must be careful about rows in the parent
table without corresponding child rows in the child nested tables, and
orphan rows in the child nested tables without parent rows. Currently,
this method works only for direct path loads; it does not work in conventional
path.
If the character set of the datafile is a unicode character
set (UTF-16 or UTF-8) and it contains a byte-order mark
(BOM) in the first few bytes of the file, then use of the command line
parameter SKIP is not supported. If SKIP is specified,
the byte-order mark will not be read and interpreted as a byte-order mark.
If you use SQL*Loader (with conventional path) or OCI to load data into varrays or primary-key-based-refs (pkrefs) and the data being loaded is in a different character set from the database character set, then unexpected problems may occur. The problems include:
To avoid these problems, set the client character set (using
the environment variable NLS_LANG) to the database character set
before loading the data.
Another general problem when converting data to a different
character set is data loss because the output database character set is
not a superset of the input character set. To avoid data loss problems,
be sure to follow the guidelines in the Oracle9i Globalization
Support Guide.
A user must have privileges explicitly granted rather than
through roles in order to access an external table directory object. Otherwise,
the user may or may not have access to the directory object.
If the access parameters for an external table specify TERMINATED BY with a string that only contains whitespace (blanks, tabs, and so on), then also specifying ENCLOSED BY for the same field will cause all rows to be rejected. An example of this is shown below.
ACCESS PARAMETERS (FIELDS (DEPTNO CHAR(10) TERMINATED BY " " ENCLOSED BY "(" AND ")"), DNAME CHAR(30))
The problem is the code that parses the fields will skip
all whitespace after the last enclosing delimiter is found before it looks
for a terminator string. After the whitespace is skipped, the code then
looks for the terminator, but since all whitespace has been skipped, the
delimiter is not found. There is no single workaround to this problem,
though omitting the TERMINATED BY clause may work in some cases.
When using the Locale Builder utility to create new locale
definitions, it is important for the users to review the semantics of their
changes according to SQL specifications. If the generated locale object
contains custom formats, for example, a custom date/time format, which
does not conform to SQL standards, the users may experience runtime errors
when trying to activate their new locale definitions. Therefore, it is
advisable that the users take extra care in creating custom formats and
validating their changes.
When adding classification data for customized multibyte
character sets, users should base the definition on an 8-bit or multibyte
character set, since classification verification for multibyte character
sets does not apply to 7-bit character sets.
The new DBMS_METADATA package (See Oracle9i
Supplied PL/SQL Packages and Types Reference) generates database
object definitions as either XML or SQL DDL. In an OSS environment, it
must disable session migration and connection pooling. This results in
any shared server process servicing a session that runs the package to
effectively become a default, dedicated server for the life of the session.
Make sure sufficient shared servers are configured when the package is
used and that the number of servers is not artificially limited by too
small a value for the MAX_SHARED_SERVERS initialization parameter.
A new parameter called Waits is now supported in
the TKPROF Utility. With this fix a new parameter, wait information
is available for tkprof. The parameter has a default value of
yes. When set to yes, any wait events captured in the
trace file will result in a summary that is produced per cursor involved,
along with a overall summary for recursive and non-recursive calls for
each wait event encountered. The summary will include count, max and sum
aggregates for each wait event. To disable the feature, specify waits=no.
The changing of the database name does not require a resetlogs, therefore all previous backups are still useable. However, all datafiles require the header to be updated so all datafiles must first be placed into read-write mode. In addition, the following text is displayed if the database name has been changed successfully.
Database name changed to PROD12. Modify parameter file and generate a new password file before restarting. Succesfully changed database name. DBNEWID - Completed succesfully.
For execution plans that involve OR expansion, you
should avoid using stored outlines if possible. This recommendation is
due to the nature of stored outlines, which use hints to influence the
execution plan, and to the nature of OR expansion, which is represented
internally through a set of OR chains, each of which represents
a distinct join order. Hints are only useful for influencing a single join
order, because there is no way to target a specific OR chain.
Therefore an outline's hints are applied to the first OR chain
represented internally. The net effect is that these hints simply get propagated
across the remaining OR chains by the optimizer, often leading
to suboptimal execution plans that differ from the originally saved plans.
Stored outlines that involve OR expansion can be identified by querying the USER_OUTLINE_HINTS view for hint text containing USE_CONCAT. Issue the following query:
SELECT NAME, HINT FROM USER_OUTLINE_HINTS WHERE HINT LIKE 'USE_CONCAT%';
Any outline containing this hint should either be dropped using the DROP OUTLINE command or moved to an unused category with the following command:
ALTER OUTLINE <outline-name> CHANGE CATEGORY TO <unused-category-name>;
In Oracle9i, there are circumstances where EXPLAIN
PLAN cannot be used to predict the execution plan for a query with
certainty. The optimizer can peek at the value of a user bind variable
at the first invocation of the query and use that value in generating an
execution plan. That plan may be different from the plan that would be
produced by EXPLAIN PLAN. The peeking feature is enabled if the
parameter optimizer_features_enable is set to Oracle9i
release 9.0.0 or higher. There can also be differences between EXPLAIN
PLAN and the plan the optimizer would generate for actually running
the query if the parameter cursor_sharing is set to similar. To
obtain the actual execution plan for a cursor, use v$sql_plan.
This feature enables parallel reads during a table lookup
nested loop join. When activated, the table lookup node is pushed up above
the nested loop join node in the EXPLAIN PLAN output. This allows
blocks containing rows matching multiple join key values to be prefetched
in parallel with a single vector read.
This change to the EXPLAIN PLAN output is not considered
a different execution plan. It does not affect the join order, join method,
access method, or parallelization scheme.
The GROUP BY clause does not define a sort
order. If you have not specified an ORDER BY clause in
your SELECT statement, and have been relying on the row order
returned by the DBMS, you may see a different result order in Oracle9i
release 2. Refer to the GROUP BY clause in the Oracle9i
SQL Reference for more information.
Select Failover is implemented by transparently re-executing
the SELECT statement and then bringing the cursor up to the same
point as it was before the failure. Thus, if the original query took an
hour, failing over the query will also take about an hour. Also, Select
Failover only occurs after Session Failover has occurred. Thus, if a client
is doing a parallel query and a slave fails, the query will not be transparently
restarted.
In this release, Oracle has bundled a new version of the
Legato backup and recovery software for Oracle databases. This is a single
version of Legato NetWorker and the NetWorker Module for Oracle client,
which has been designed to operate on the same server as your Oracle database.
After installing this version of Legato NetWorker, you will receive a license
notice each time you back up your database to tape using the Oracle Recovery
Manager (RMAN) interface. To eliminate this license notice, you can enter
an authorization code, which can be obtained directly from Legato without
charge. Instructions for obtaining this code are in the displayed license
notice.
Oracle no longer distributes Legato documentation. Legato
NetWorker documentation can be obtained directly from Legato and can be
found at the following Web site:
This site will also contain any product updates for this
NetWorker version.
If you have previously installed and used Legato Storage
Manager (LSM) on your Oracle database server, you can uninstall it and
install this new version of Legato NetWorker. Any backups made by LSM are
still usable by the new Legato NetWorker software.
For single byte and multibyte character set data, including
varying-width character sets, the following functions have known issues:
NVL and COUNT: bug1475475 ORA-00932 when
using NVL() or COUNT() on CLOB.
DECODE: bug 1473512 - ORA-0932 in DECODE
(NCHAR,NCLOB,NCHAR,NCHAR) in PL/SQL
This note applies to you if you:
When a temporary LOB (either standalone or embedded inside
an object) is passed as an IN parameter to a procedure (or function)
implemented in Java or C, and the LOB is written into the procedure, the
caller of the procedure will not see the modifications made inside the
procedure. If the caller needs to see the modifications, the LOB parameter
must be declared as an IN OUT parameter.
Prior to Oracle9i release 2, even if the LOB parameter
was declared as IN instead of IN OUT, changes made to
temporary LOBs inside the procedure were visible outside the procedure.
However, this is no longer true in Oracle9i release 2.
Since there are existing applications that may have come
to rely on being able to write into IN temporary LOBs, this behavior
is being maintained in Oracle9i release 2 for the most common case,
which is, writes into top level temporary LOBs (not embedded inside object
types) for invocations from PL/SQL to both Java and C. For all the other
cases, the parameter must be declared as IN OUT if the intent
is to modify the temporary LOB.
This behavior for temporary LOBs passed in from PLSQL to
Java and C will be desupported in Oracle10i, and it will then become
mandatory to declare these LOB parameters as IN OUT. It is recommended
that such parameters be changed to IN OUT prior to Oracle10i.
To change a parameter to IN OUT:
An example of changing the parameter type for a Java implementation is given below.
Rem --------------------------------------------------- Rem A procedure with a clob declared as an IN parameter Rem and its java implementation Rem --------------------------------------------------- create procedure foo (x clob) is language java name 'fooclass.jfoo(oracle.sql.CLOB)'; / import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.driver.*; public class fooclass { public static void jfoo(CLOB x) throws SQLException { try { Writer w = x.getCharacterOutputStream(); w.write("hello world"); w.close(); } catch (Exception e) { } } } Rem ------------------------------------------------------ Rem The modified procedure with the clob declared as an Rem IN OUT parameter and its modified java implementation. Rem Note that the parameter type has been changed to Rem IN OUT and the java parameter has been changed to an Rem array. Rem ------------------------------------------------------ create procedure foo (x IN OUT clob) is language java name 'fooclass.jfoo(oracle.sql.CLOB[])'; /
import java.sql.*; import java.io.*; import oracle.sql.*; import oracle.jdbc.driver.*; public class fooclass { public static void jfoo(CLOB x[]) throws SQLException { try { Writer w = x[0].getCharacterOutputStream(); w.write("hello world"); w.close(); } catch (Exception e) { } } }
Oracle 8.1.6 introduced a number of new function names and
keywords. If your applications encounter naming compatibility problems
as a result of these new terms, you can set event 10408 to block out the
new function names and keywords.
Event 10408 is set in the initialization parameter file and is disabled by default. Enable it as follows:
event = "10408 trace name context forever, level 1"
When event 10408 is enabled, it blocks the keywords OVER
and CASE, and the following built-in SQL functions: FIRST_VALUE,
LAST_VALUE, LAG, LEAD, RANK, DENSE_RANK, CUME_DIST, NTILE, RATIO_TO_REPORT,
ROW_NUMBER, COVAR_POP, COVAR_SAMP, CORR, REGR_SLOPE, REGR_INTERCEPT, REGR_COUNT,
REGR_R2, REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY, VAR_POP, STDDEV_POP,
VAR_SAMP, STDDEV_SAMP, and PERCENT_RANK.
SQL access to Analytic Workspaces in 9.2.0.1 is achieved
by using table functions, that is, through calls to the olap_table
function. Attempting to use this table function in a multi-user environment
in shared server mode causes the client sessions to crash (end of communication
channel error). The workaround is to run all SQL access to Analytic Workspaces
in dedicated process mode.
Restriction: Do not use the DBMS_MVIEW.REFRESH_ALL_MVIEWS
and DBMS_MVIEW.REFRESH_DEPENDENT procedures to refresh materialized
views used in a replication environment.
Workaround: Use the DBMS_REFRESH.REFRESH or the
DBMS_MVIEW.REFRESH procedures to refresh your materialized views
in a replication environment.
Oracle9i master sites can only interact with Oracle7
release 7.3.4 or higher master sites. Oracle9i materialized view
sites can only interact with Oracle7 release 7.3.4 or higher master sites.
Oracle9i master sites can only interact with Oracle7 release 7.3.4
or higher materialized view sites.
The Advanced Replication feature is now automatically installed
and upgraded in every Oracle9i database. There is no need to explicitly
run the catrep.sql script as it is now called from catproc.sql.
In a replication environment involving Oracle9i and
previous releases of the database at sites utilizing NCHAR and
NVARCHAR2 datatypes, an Oracle patch must be installed at the
earlier release (pre-9i) site. Contact Oracle Support to obtain
the appropriate NLS patch as recommended in ALERT 140014.1 Oracle8/8i
to Oracle9i using New AL16UTF16 available in MetaLink:
For multimaster replication, there must be no VPD restrictions
on the replication propagator and receiver schemas.
For materialized views, the defining query for the materialized
view may not be modified by VPD. VPD must return a NULL policy
for the schema that performs both the create and refresh of the materialized
view. Creating a remote materialized view with a non-NULL VPD
policy will not generate an error but may yield incorrect results.
Type evolution is not supported in a replication environment.
If a column of a replicated table or a replicated object table is based
on a user-defined type, then you cannot alter the user-defined type.
In normal operation, DML and DDL events captured with Oracle
Streams are placed in an optimized queue buffer. In situations where a
downstream replication site is unavailable or unable to process incoming
events quickly enough, the buffer can spill over into the Streams queue
table. The queue table must be sized appropriately to handle these overflow
events.
The optional capture process of Oracle Streams requires that
the SHARED_POOL_SIZE initialization parameter be set to a minimum
value of 100 MB and that the database be running in ARCHIVELOG
mode.
The first capture process created on a database builds the
Streams dictionary. If this initial dictionary build is interrupted, an
error indicating that the dictionary is invalid may occur. The typical
cause of an interrupted dictionary build is a shutdown abort operation.
All other shutdown modes will wait for the dictionary build to complete.
If you encounter this error, drop and recreate the capture process.
JMS Types and XMLType access to the Streams queue table is
not enabled by default to minimize the impact of Bug 2248652 that causes
export of the Streams queue table to fail. Users can enable this access
by calling the DBMS_AQADM.ENABLE_JMS_TYPES(queue_table) procedure
where the VARCHAR2 parameter queue_table is the name
of the queue table. This procedure should be invoked after the call to
DBMS_STREAMS_ADM.SET_UP_QUEUE. Sites dependent on the Export utility
as their backup strategy should avoid enabling this access.
When using the Oracle Streams Wizard, via OMS or in standalone
mode, to configure replication, the generated scripts are incorrect and
may produce errors such as "ORA-26687 Instantiation SCN not set" or "ORA-1403
no data found" (bugs 2301915, 2318295 and 2326341). Please contact Oracle
Support for an Enterprise Manager and Oracle Management Server patch that
will produce the correct scripts. Use the bug numbers listed here as a
patch reference.
The following situation is known to have issues in this release:
Concurrent session issue, that is, executing a DDL in one
session and concurrently using DDL or DML in another session, has known
issues. We recommend that you issue DDLs in one session only and not do
anything in other sessions until the DDLs are complete.
Any data based on user-defined SQL object types that are
part of inheritance hierarchies, or which have been evolved, are not supported
in persistent table columns. They are supported for transient usage for
parameter passing and so on. However, attempts to store such values persistently
will return an error.
Oracle9i release 2 cannot be downgraded to a previous
release if user-defined type(s) exist that contain user-defined constructors,
or which embed a user-defined type (at an arbitrary level of nesting) that
contains user-defined constructors, and if such a type is used in a column
of a table. If such a type exists, but it is not used in a column of a
table, then downgrade is permitted. However, upon downgrade, the constructors
are automatically replaced with static functions. If downgrade was not
permitted due to the presence of offending types in tables, then the user
may choose to either drop the constructor function from the type, or drop
the column from the table.
The following V$ views may change as follows in a future release of Oracle:
When using ODM in a shared JVM environment, such as when
integrated with a servlet application, all connections made to an ODM server
(also known as a Data Mining Server) must be based on databases with compatible
character sets. Otherwise, string length tests conducted in the JVM may
not recognize these differences, allowing data to pass to the database,
which could result in server-side failures.
Importing Naive-Bayes PMML models fails when TargetValueCount
element's count attribute contains a real number. The count
attribute's value for the TargetValueCount element in a Naive-Bayes
model most commonly contains an integer value; however, the PMML specification
allows for real numbers. If the document to be imported contains real numbers
for this attribute, the import will fail.
Convert the values of the count attribute to integers before
performing the PMML model import MATERIALIZED VIEWS.
The creation and refresh features of materialized views are
supported in both the Standard and Enterprise Editions. However, Query
Rewrite and Summary Advisor are available in the Enterprise edition only.
Oracle9i ignores event 30441. because Oracle9i supports
fast refresh of materialized views with joins and aggregates after SQL
DML
When using certain materialized views as described below, you must ensure that your NLS parameters are the same as when you created the materialized view. Materialized views that fall under this restriction contain the following constructs:
When processing a workload, the Summary Advisor attempts to validate each statement in order to identify table and column references. If the current database user does not have SELECT privilege on a particular table, the Advisor bypasses the statement referencing the table. This may cause many statements to be excluded from analysis. If the Advisor excludes all statements in a workload, the workload is invalid and the Advisor returns the following message:
ORA-30446, valid workload queries not found
To avoid missing critical workload queries, the current database
user must have SELECT privilege on the tables that are targeted
for materialized view analysis. Moreover, the SELECT privilege
cannot be obtained via a role.
If using the Database Configuration Assistant to create a database that uses one of the pre-defined templates, you can choose any of the following templates:
Data Warehouse and the Transaction Processing database templates
support the Oracle Change Data Capture feature.
You may choose the New Database option to build a custom
database. If so, you must select the database feature "Oracle JVM" from
the Additional database configurations dialog box. Oracle JVM is already
selected by default: do not deselect it. Oracle Change Data Capture requires
the Oracle JVM feature.
See the README.txt file that is installed in the
<ORACLE_HOME>/ord/im/admin directory on Solaris systems or
<ORACLE_HOME>\ord\im\admin directory on Windows NT systems
for information on new features, problems, fixes and differences between
the software and the supplied documentation for Oracle interMedia.
In this release, Oracle is no longer providing the Visual
Information Retrieval option. Instead, we are providing a compatible API
to the ORDVir object for current users of Visual Information Retrieval.
Please refer to the README.txt file that is installed in the <ORACLE_HOME>/ord/vir/admin
directory on Solaris systems or <ORACLE_HOME>\ord\vir\admin
directory on Windows NT systems for more information.
This ORDVir-compatible API may not ship with the
next version of Oracle.
Applications that use Visual Information Retrieval Java Classes
(contained in ordvir.zip or ordvir817.zip) cannot be
compiled with the JDK 1.2 specific version of the SQLJ runtime library
(contained in runtime12.zip). The javac compiler displays the
following error if you try to compile an application using the OrdVir
class when runtime12.zip is included in the CLASSPATH
variable:
error: The method java.util.Dictionary getTypeMap()
declared in nested class oracle.ord.im.OrdVirBase. _Ctx cannot
override the method of the same signature declared in interface sqlj.runtime.ConnectionContext.
They must have the same return type.
Specify the classes contained in the following libraries
in the CLASSPATH variable to compile an application that uses
Visual Information Retrieval Java Classes:
ORACLE_HOME>/ord/jlib/ordvir.zip or ordvir817.zip
ORACLE_HOME>/ord/jlib/ordim11.zip or ordim817.zip
ORACLE_HOME>/jdbc/lib/classes111.zip
ORACLE_HOME>/sqlj/lib/runtime.zip or runtime11.zip
ORACLE_HOME>/jdbc/lib/classes12.zip
ORACLE_HOME>/sqlj/lib/runtime.zip
ORACLE_HOME>\ord\jlib\ordvir.zip or ordvir817.zip
ORACLE_HOME>\ord\jlib\ordim11.zip or ordim817.zip
ORACLE_HOME>\jdbc\lib\classes111.zip
ORACLE_HOME>\sqlj\lib\runtime.zip or runtime11.zip
ORACLE_HOME>\jdbc\lib\classes12.zip
ORACLE_HOME>\sqlj\lib\runtime.zip
In Oracle9i release 9.0.1, interMedia introduced image content based retrieval (CBR) to allow applications to query the database for images that are similar to a sample image based on color, texture and structure. While this feature is available in both Standard Edition and Enterprise Edition, indexing of image content based retrieval with the new ORDImageIndex requires bit-mapped index support only available in Enterprise Edition. Attempting to use this feature with Standard Edition will result in the following error message:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-00439: feature not enabled: Bit-mapped indexes ORA-06512: at "ORDSYS.ORDIMGIDXMETHODS", line 159
The Oracle9i interMedia User's Guide and Reference
release 1 (9.0.1) neglects to mention that indexing for image content based
retrieval is available only with Enterprise Edition.
The functions in the Time Series option migrated to SQL in
Oracle8i release 8.1.7, obviating the need for a Time Series option.
The classes of functions that were implemented in SQL as part of the base Oracle8i release 8.1.7 include the following:
These functions will be callable from SQL, and will be implemented
in a relational framework (no PL/SQL or Oracle Objects). See Oracle9i
SQL Reference for a description of these SQL functions.
The benefits of this approach include:
The technical details of the migration from the Time Series option to SQL are discussed in the following white papers at:
http://technet.oracle.com/products/oracle8i/
The Configuration Assistants should be run by a user who
is a member of the DBA group and is owner of the ORACLE_HOME.
The name of Oracle Data Migration Assistant (ODMA) has changed
to Database Upgrade Assistant (DBUA). DBUA upgrades single instance Oracle
7.3.4, 8.0.6, 8.1.7 and 9.0.1 database releases to 9.2.0. DBUA supports
Real Application Clusters database upgrade: it upgrades 8.1.7 Oracle Parallel
Server (OPS) database release and 9.0.1 Real Application Clusters (RAC)
database release to 9.2.0.
The Oracle Net readme file is located at:
$ORACLE_HOME/network/doc/README_OracleNet.htm.
The following area has a known issue:
Inheritance is not supported in the XML Schema generated
by the XSU
In addition to the limitations mentioned in the documentation, the following classes/methods are not supported:
Use the pseudo column SYS_NC_ROWINFO$ within triggers
to refer to the XMLType row value of a XMLType table
or view. See also bug 2220374 regarding how to improve this expression's
usability.
XMLType is not supported in materialized views.
JNDI and Java Beans are not supported in Oracle9i
release 2 Oracle XML DB.
Multibyte encodings, including UTF-8, are not supported on
this release.
XML entities inserted using the Oracle XML Database FTP,
HTTP, or WebDAV Protocol Server must be encoded using an equivalent encoding
to or a subset of the database character set. For example, a document with
the encoding ISO-8859-1, windows-1252, or US-ASCII
can be stored in a server of WE8MSWIN1252, but the encoding windows-1252
cannot be stored in a server of WE8ISO8859P1.
Bug 2275973: HASPATH and INPATH in the
Oracle XML Database do not work with 8-bit data When you use registerSchema
from a BFILE, the XML schema is accepted in ASCII format only.
The Import utility's TOUSER functionality cannot
be used in Oracle9i release 2 when the FROMUSER schema
has references to XDB tables, views, types, or schemas.
In general, when a LogMiner session is started using a dictionary
from the redo logs and with DDL tracking turned on, all SQL_REDO
and SQL_UNDO fields in V$LOGMNR_CONTENTS are fully translated.
There are no strings in either of these fields of the form ...HEXTORAW...
There is an exception to this that can occur when you start LogMiner and all of the following conditions are present:
Specifically, if a DDL event occurs beyond the end of the
dictionary in the redo log stream, but before the user-specified startSCN,
then that DDL event will not automatically be applied to LogMiner's internal
dictionary.
The workaround is to never specify a startSCN that is greater
than the endSCN of the dictionary in the redo log stream.
The following example illustrates this problem.
execute dbms_logmnr.start_logmnr( startSCN => 2050, Options => dbms_logmnr.DDL_DICT_TRACKING + dbms_logmnr.DICT_FROM_REDO_LOGS);
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS;
The results of the query show that the SQL_REDO for the following statement is not completely translated:
"INSERT INTO FOO VALUES('2a, before')"
Therefore, the value in the SQL_REDO field may appear as follows:
insert into "SCOTT"."FOO"("COL 1") values (HEXTORAW('32612c206265666f7265'));
execute dbms_logmnr.start_logmnr(startSCN => 0, Options => dbms_logmnr.DDL_DICT_TRACKING + dbms_logmnr.DICT_FROM_REDO_LOGS);
Or
execute dbms_logmnr.start_logmnr(startSCN => 1000, Options => dbms_logmnr.DDL_DICT_TRACKING + dbms_logmnr.DICT_FROM_REDO_LOGS);
If it is necessary to only view SQL_REDO starting at SCN 2050, you can use an AND operator to add a predicate to any query made against V$LOGMNR_CONTENTS. For example:
SELECT SQL_REDO FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER = 'SCOTT' AND SCN >= 2050;
If DBMS_LOGMNR.START_LOGMNR is called without specifying the COMMITTED_DATA_ONLY flag in the OPTIONS parameter, then the following columns of the V$LOGMNR_CONTENTS view will be empty where the operation column value is DDL:
The following is a known issue with the behavior of the V$LOGMNR_CONTENTS
view:
When rollback redo operations are translated in absence of
minimal supplemental logging there may be some invalid delete operations
returned by V$LOGMNR_CONTENTS view.
If a DML UPDATE statement uses the BUFFER
hint, then supplemental logging is disabled for the update operation.
The ALTER DATABASE ADD SUPPLEMENTAL LOG DATA statement is not properly handling the Real Application Cluster environment. For environments with multiple instances the following workarounds are available:
LogMiner does not support redos generated by compressed heap
segments in addition to other documented unsupported data types.
The following sections contain release notes about Oracle9i Data Guard:
This note concerns Data Guard configurations running in maximum
protection mode with physical standby databases.
If you configured Oracle9i Data Guard to support a
primary database in a Real Application Clusters environment and the primary
database is running in maximum protection mode, a network outage between
the primary database and all of its physical standby databases will disable
the primary database until the network connection is restored. The maximum
protection mode dictates that if the last participating physical standby
database becomes unavailable, processing halts on the primary database.
If you expect the network to be down for an extended period
of time, consider changing the primary database to run in either the maximum
availability or the maximum performance mode until network connectivity
is restored. If you change the primary database to maximum availability
mode, it is possible for there to be a lag between the primary and standby
databases, but you gain the ability to use the primary database until the
network problem is resolved.
If you choose to change the primary database to the maximum
availability mode, it is important to use the following procedures to prevent
damage to your data.
Perform the following steps if the network goes down and you want to change the protection mode for the Real Application Clusters configuration.
Later, when the network comes back up, perform the following steps to revert to the maximum protection mode:
Using a database link to connect to a read-only database to select data from a view fails returning the errors ORA-4052, ORA-604, ORA-372, ORA-1110, and ORA-2063. You cannot use a database link to select data from a view using a cursor loop. The workaround is to avoid using the database link to query views using a cursor loop.
This problem only occurs with a view. It does not occur if you use a SELECT statement directly from the view if you use a cursor loop to select from a table.
This problem corresponds with Bug 2033385 and Bug 2181830.
To instantiate a logical standby database following the steps
in Chapter 4, Section 4.1 "Preparing to Create a Logical Standby Database,"
you must manually build the dictionary for logical standby database.
In Section 4.2, step 2 replace numbers 6 and 7 on page 4-11,
as follows:
6. Open the primary database and build the LogMiner dictionary:
SQL> ALTER DATABASE OPEN; SQL> EXECUTE DBMS_LOGSTDBY.BUILD; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT NAME FROM V$ARCHIVED_LOG 2> WHERE DICTIONARY_BEGIN='YES' AND STANDBY_DEST='NO';
7. Use an operating system copy utility to copy the following files to the logical standby site:
In Section 4.2, step 2 replace number 6 (page 4-12), as follows:
6. Put the primary database in a quiesced state to obtain a starting point for building the standby database.
Putting the database in a quiesced state allows you to perform
administrative operations that cannot be safely performed in the presence
of concurrent transactions, queries, or PL/SQL operations. This step may
take some time depending on how long it takes the system to put all active
sessions in this state. (See the Oracle9i SQL Reference for more
information about the ALTER SYSTEM statement.)
The following example shows the ALTER SYSTEM QUIESCE statement and a query being performed on the V$DATABASE view to obtain the recovery SCN. Record the SCN number and the archive log file name for later use. Then, take the database out of the quiesce state and switch log files, as shown:
SQL> ALTER SYSTEM QUIESCE; SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE; CHECKPOINT_CHANGE# -------------------- 443582 SQL> EXECUTE DBMS_LOGSTDBY.BUILD; SQL> ALTER SYSTEM SWITCH LOGFILE; SQL> SELECT NAME FROM V$ARCHIVED_LOG 2> WHERE DICTIONARY_BEGIN='YES' AND STANDBY_DEST='NO'; SQL> ALTER SYSTEM UNQUIESCE; SQL> ALTER SYSTEM SWITCH LOGFILE;
In addition to the datafiles copied previously, you must also copy all archived redo logs that were created during the hot backup procedure (up to the end of the database quiesce) to the standby location. Use an operating system copy utility to copy the archived redo logs to the standby system and apply them to the backup copy of the database.
In addition, for both Cold and Hot Backup methods, replace
Section 4.2, step 13 on Page 4-16 with the following:
You must register the first redo log which contains the dictionary build so that the log apply services can begin to apply data to the logical standby database. Do this by executing the ALTER DATABASE REGISTER LOGICAL LOGFILE statement on the logical standby database. When you execute this statement, specify the filename and location of the archived redo log identified during either the cold or hot backup procedure (in step 6) that you copied to the logical standby site. For example:
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE "/Oracle/remote_arc/db1/arc0.arc';
Section 4.2, Step 10 on page 4-15 of Oracle9i Data Guard Concepts and Administration incorrectly states that you should use the RESETLOGS parameter when you open the logical standby database. You do not need to include the RESETLOGS parameter when you open the database. The correct code example for Step 10 is as follows:
Enter the following statement to start and open the database to user access:
SQL> STARTUP PFILE=init$Log1.ora EXCLUSIVE MOUNT; SQL> ALTER DATABASE OPEN;
Do not issue the CREATE TABLE AS SELECT DDL statement on a primary database that has logical standby databases. Performing the CREATE TABLE with a clustered column results in the creation of an empty table on the logical standby database. If this happens, you will not see any other indications that the associated DML statement was errant. However, if you subsequently try to update a row in the empty table (that does not exist), the NO DATA FOUND error is returned. When this happens:
Do not issue the ALTER TABLE SET COLUMN UNUSED statement
on a primary database that has logical standby databases. Using the ALTER
TABLE statements to set a column unused may fail and return the "ORA-26689:
column datatype mismatch in LCR" error. After this error occurs, use the
DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure for the table listed
in the DBA_LOGSTDBY_EVENTS view and then restart SQL apply operations.
While the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure
will refresh the logical standby database's copy of the table,all subsequent
updates to the table will fail.
To work around this problem:
Issuing the CREATE OUTLINE, ALTER OUTLINE,
or DROP OUTLINE DDL statements on a primary database that has
logical standby databases may result in an "ORA-00600 internal error code"
message being returned. (Query the DBA_LOGSTDBY_EVENTS view to
see the actual error message.)
If an OUTLINE-related DDL statement stops SQL apply operations to the logical standby database, you can work around the problem by:
To prevent SQL apply operations from stopping during OUTLINE-related DDL work on logical standby databases, Oracle Corporation recommends executing the following.
EXECUTE: DBMS_LOGSTDBY.SKIP('CREATE OUTLINE','%','%');EXECUTE DBMS_LOGSTDBY.SKIP('ALTER OUTLINE','%','%');EXECUTE DBMS_LOGSTDBY.SKIP('DROP OUTLINE','%','%');EXECUTE DBMS_LOGSTDBY.SKIP('DML','OUTLN','%');EXECUTE DBMS_LOGSTDBY.SKIP('SCHEMA_DDL','OUTLN','%');EXECUTE
For this release of Oracle9i Data Guard, logical standby
databases do not support creating nested tables. Attempting to create a
nested table on the logical standby database will result in the "ORA-02320:
failure in creating storage table for nested table column" error being
returned. If the DDL statement causes SQL apply operations to stop on the
logical standby database, provide the transaction ID information (obtained
by querying the DBA_LOGSTDBY_EVENTS view) to the DBMS_LOGSTDBY.SKIP_TRANSACTION
procedure and restart SQL apply operations. Although you can create the
table on the logical standby database, rows will not be maintained for
it.
On logical standby databases, SQL statements such as the
CREATE TABLE tablename AS SELECT * FROM bar@dblink,
issued may fail. Avoid issuing this type of statement, because there are
many errors that can arise from this type of operation.
When the statement is executed on the logical standby database,
it will access the database link at that time and it's not possible to
know if the information on the logical standby database is the same as
it was at the time the statement was executed on the primary database.
For example, additional columns may have been added or dropped that would
make it impossible to apply the rows that follow. Assuming the network
was set up so that the initial creation succeeded, you might see the following
error: "ORA-26689: column datatype mismatch in LCR" for a table containing
nested table columns. Also, the "ORA-02019: connection description for
remote database not found" error may be returned if the database link or
the TNS service was undefined on the logical standby database.
When this happens, use the DBMS_LOGSTDBY.INSTANTIATE_TABLE
procedure for the table being created and then restart SQL apply operations.
If a logical standby database is located on the same computer
system as the primary database, it's likely that both Oracle instances
will have access to the same directory structure. There are Oracle commands
that reuse datafiles. If Oracle commands that reuse datafiles are applied
on the primary database, the commands may also be applied on the logical
standby database. If that happens while the primary database is shut down,
it's possible for the logical standby database to claim the file as part
of its database and possibly cause damage to the primary database.
For this reason only, Oracle Corporation recommends using the following settings when running the primary and logical standby databases on the same computer system.
EXECUTE DBMS_LOGSTDBY.SKIP('ALTER TABLESPACE');
For this release, tables with columns of type NCLOB cannot
be maintained on logical standby databases. However, these columns will
not be listed in the DBA_LOGSTDBY_VIEW view as unsupported for
logical standby databases and the tables will not be skipped automatically
by SQL apply operations on the logical standby database.
Thus, if a table on the primary database has a column of type NCLOB, execute the following PL/SQL statement on the logical standby database to skip the table:
DBMS_LOGSTDBY.SKIP('DML','myschema','mytabwithnclob');
When you use Oracle Enterprise Manager to connect to a remote
logical standby database, the O7_DICTIONARY_ACCESSIBILITY initialization
parameter must be set to TRUE. This is required so that non SYSDBA
users can connect to a logical standby database.
Do not issue an ALTER TABLE statement on a primary
database that has a logical standby database to add a column. This statement
may fail and return the "ORA-26689: column datatype mismatch in LCR" error.
Although the DBA_LOGSTDBY_EVENTS view does not provide enough information to indicate whether this error was caused by an ADD COLUMN request, you may want to skip the transaction if you believe this is the case. To skip the transaction, perform the following steps:
Alternatively, you can use the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure to instantiate the table listed in the DBA_LOGSTDBY_EVENTS view.
The "Invalid Dictionary Error" message may be returned when
a new logical standby database (or a database that has just been moved
into the logical standby role as a result of a switchover operation) is
interrupted while it is in the initialization phase. You cannot recover
from this error. The only solution is to re-create the logical standby
database from the primary database.
If you query the V$LOGSTDBY view and it shows the
"ORA-16115 loading LogMiner dictionary data" message for one of the processes
indicated in the process ID (PID) column, do not attempt to perform
a SHUTDOWN ABORT operation. Logical standby databases can recover
from an ABORT operation if other activity is underway.
Attempts to query the DBA_LOGSTDBY_PROGRESS.APPLIED_TIME
column when running in an Oracle Real Application Clusters environment
will return the "ORA-01427 single-row subquery returns more than one row"
error. In Oracle Real Application Clusters environments, you should explicitly
specify your column selections when selecting from the DBA_LOGSTDBY_PROGRESS
view and avoid selecting the APPLIED_TIME column.
Oracle Corporation recommends setting the OPEN_CURSORS
initialization parameter to 600 or greater on a logical standby database.
When the OPEN_CURSORS initialization parameter is set to less
than 600, it may result in the SQL apply operations to the logical standby
database failing, returning the "ORA-01000 maximum open cursors exceeded"
error message.
If the primary database from which a new logical standby database is created was previously a standby database, the "ORA-01289 cannot add duplicate logfile string" error message may be returned when you register the first logfile. To work around this problem, issue the following SQL statements:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL SCN 1; SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY; SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 'yourlogfile'; SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
If SQL apply operations fail on a logical standby database and return the "ORA-01749 you may not GRANT/REVOKE privileges to/from yourself" error message, it is probably caused by an attempt to execute a GRANT DDL statement to grant a privilege or a role to SYS. To verify that this is the problem, query the DBA_LOGSTDBY_EVENTS view to see the failed DDL statement along with it's transaction ID (shown in the XIDUSN, XIDSLT, XIDSQN columns) in the view. To fix the problem:
If the DBMS_LOGSTDBY.BUILD procedure returns an "ORA-16108 database is no longer a standby database" error message, execute the following procedure in it's place:
EXECUTE DBMS_LOGMNR_D.BUILD(options=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Materialized views must be refreshed separately on a logical standby database using the GUARD_BYPASS_ON and GUARD_BYPASS_OFF procedures of the DBMS_LOGSTDBY package. For example:
EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON; EXECUTE DBMS_MVIEW.REFRESH ( 'BMVIEW', 'F', '',TRUE,FALSE,0,0,0,FALSE); EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;
| See
Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the DBMS_LOGSTDBY package |
Also, if you are using the DBMS_LOGSTDBY.APPLY_SET
procedure but you are not using the default value of FULL for
the TRANSACTION_CONSISTENCY parameter, you should stop SQL apply
operations before refreshing materialized views on the logical standby
database.
For logical standby databases, if you set the MAX_SERVERS
initialization parameter to a value greater than 5 and you use the default
TRANSACTION_CONSISTENCY parameter value of FULL, then
an apply process may wait an indefinite period of time if there is a transaction
that is dependent on another transaction with a higher SCN. When an apply
process detects such a dependency, information is recorded in the alert
log and apply process trace file for the database.
To avoid such a situation, consider increasing the PCTFREE
attribute value of the storage parameter for the schema object.
.
LogMiner uses temporary tables to stage its internal dictionary as it is mining it out of the redo stream. If the temporary tablespace is not configured adequately (for example, it either does not have enough space or is not backed up by a temporary file), the initialization phase of a logical standby database will fail with the following errors:
ORA-1652: unable to extend segment by 512 in tablespace TEMP ORA-01332: internal Logminer Dictionary error
To work around the problem, increase the size of the temporary
tablespace and start the logical standby database.
After performing a switchover operation involving a logical standby database, the "ORA-16104 invalid Logical Standby option requested" error message may be returned. For example:
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY; ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY * ERROR at line 1: ORA-16104: invalid Logical Standby option requested
This is because immediately after a switchover operation
involving a logical standby database, some additional work occurs in the
background. This background work typically lasts a few minutes. Until this
background work has completed, you cannot perform another switchover operation.
Occasionally, when performing Data Guard broker operations, you may see the following message in the Data Guard configuration log file:
RSM: error in accessing metadata.
You can safely ignore this message. It is benign and does
not indicate any operational problem with the broker or the databases included
in the broker configuration.
When connecting to a database using the Data Guard command-line interface (DGMGRL), a connect string that includes multiple domains separated by dots may result in the "invalid username/password; logon denied" message being returned. For example, the following CONNECT command can be problematic:
DGMGRL> CONNECT sys/change_on_install@standby.acme.com
Use one of the following solutions to work around this problem:
DGMGRL> CONNECT sys/change_on_install@"standby.acme.com"
This section lists additions and corrections to Oracle9i
Data Guard Concepts and Administration, Release 2 (9.2).
In Chapter 4, Section 4.1 "Preparing to Create a Logical Standby Database," the list of unsupported items should include the following:
Tables created with ORGANIZATION INDEX are not supported by logical standby databases in this release. Also, Index-Organized Tables are not displayed when you query the DBA_LOGSTDBY_UNSUPPORTED view, even though rows in Index-Organized Tables are not supported by logical standby databases.
DML statements performed to user tables that use data segment compression are not supported by logical standby databases. Note that the Oracle9i SQL Reference recommends that you use data segment compression for tables in environments where the amount of insert and update operations is small.
In Appendix B, Section B.3.2 "Determining Whether an Archive
Gap Exists," the text recommends querying the V$ARCHIVE_GAP view
to determine whether there is an archive gap.
Instead, you should query the V$ARCHIVED_LOG and V$LOG views, which more accurately reflect if there is a gap on a particular standby destination. For example, the following query shows that there is a difference in the RECD and SENT sequence numbers for the destination specified by DEST_ID=2, indicating that there is a gap:
SQL> SELECT MAX(R.SEQUENCE#) LAST_SEQ_RECD, MAX(L.SEQUENCE#) LAST_SEQ_SENT FROM 2> V$ARCHIVED_LOG R, V$LOG L WHERE 3> R.DEST_ID=2 AND L.ARCHIVED='YES'; LAST_SEQ_RECD LAST_SEQ_SENT ------------- ------------- 7 10
Use the following query to determine the names of the archived redo logs on the local system that must be copied to the standby system that has the gap:
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND 2> SEQUENCE# BETWEEN 7 AND 10; NAME -------------------------------------------------------------------------------- /primary/thread1_dest/arcr_1_7.arc /primary/thread1_dest/arcr_1_8.arc /primary/thread1_dest/arcr_1_9.arc /primary/thread1_dest/arcr_1_10.arc
In Chapter 14 and in the New Features preface, the new values of the TRANSMIT_MODE column of the V$ARCHIVE_DEST view should be as follows:
The documentation incorrectly states that the new column
names are SYNC=PARALLEL, SYNC=NOPARALLEL, and ASYNC.
The DBMS_LOGSTDBY PL/SQL procedure does not support the BLOB data type even though BLOB data types are supported by logical standby databases. Also, the order of the parameters for the INSTANTIATE_TABLE procedure is incorrect in the Oracle9i Data Guard Concepts and Administration and in the Oracle9i Supplied PL/SQL Packages and Types Reference. The correct parameter order is as follows:
DBMS_LOGSTDBY.INSTANTIATE_TABLE schema_name IN VARCHAR2, table_name IN VARCHAR2, dblink IN VARCHAR2);
If only selected Oracle database session(s) are enabled for
Oracle Trace data collection under Oracle Shared Server, using the ALTER
SESSION command, then incomplete or irrelevant database event data
may be collected by Oracle Trace. This will occur only if an Oracle Trace-enabled
database session migrates to another shared server process which has not
been collecting, and vice versa. This results from Oracle Trace collection
being process-based, rather than session-based; and the process collection
state (that is, whether it is collecting or not) is not correctly migrated
during shared server session migration.
Conversely, this is not a problem if either Oracle Trace
collection has been enabled for all database sessions (or none of them),
or for non-shared server Oracle database environments.
One of the columns in the OLAP PS$ table was declared with too low a precision. The 10,000th attempt to update an analytic workspace will result in this error message:
ORA-00604: error occurred at recursive SQL level 1 ORA-01438: value larger than specified precision allows for this column
To avoid this problem, connect to the database as the sysdba user and execute the following SQL statement:
ALTER TABLE PS$ MODIFY (PSGEN NUMBER(10))
If you are planning to install Oracle9i release 9.2.0.1
into an Oracle Home containing Oracle 9i release 9.0.1 OLAP Services,
and you plan to select either the General Purpose or Data Warehousing form
of Enterprise Edition, or a Custom Install selecting the OLAP Option, you
need to explicitly deinstall 9.0.1 OLAP Services before proceeding with
the Oracle9i release 9.2.0.1 install.
The following sentences from the description of the BASIS
basisname argument are incorrect.
If you do not specify a basis, then the ALLOCATE
command uses the variable specified by the TARGET keyword as the
basis. If you specify a variable as basisname and you do
not specify a target variable, then ALLOCATE uses basisname
as the target.
If you do not specify a basis object, then the ALLOCATE
command uses the source object as the basis.
The name of the SOURCELIST argument should be SOURCE, so the syntax of the ALLOCATE command is the following.
ALLOCATE source [SOURCE conjoint] - [BASIS basisname [ACROSS dimname]] - [TARGET targetname [TARGETLOG targetlogname]] - USING aggmap [ERRORLOG errorlogfileunit]
Note that the ERRORLOG argument is after the USING
argument and not before it.
The "Editing an aggregation map in the Worksheet editor"
section of the ALLOCMAP Command Examples incorrectly states that
you can edit an ALLOCMAP type aggregation map with the OLAP Worksheet
editor.
You cannot use the OLAP Worksheet edit window to either create
or revise an aggregation map for allocation. To add contents to an ALLOCMAP
type aggregation map, you must use the ALLOCMAP command.
The following information should be added for the CREATE
keyword.
When you create an analytic workspace with the CREATE
keyword, Oracle OLAP automatically executes a COMMIT command.
If you want others to use the workspace, you must give them access to the relational table in which the workspace is stored. The name of the table is "AW$" followed by the workspace name that you specified in your AW CREATE command. To give read access to another user, execute a command like the following one in SQL. In this example, the workspace name is "demo" and the user's name is "scott".
GRANT SELECT ON aw$demo TO scott
To give write access to another user, execute a SQL command like the following one.
GRANT UPDATE ON aw$demo TO scott
As in any SQL GRANT command, you can specify a group
or role instead of a user.
The description of the "name" argument incorrectly
states that an object name must consist of 1 to 16 characters. The correct
maximum is not 16 characters; it is 64 characters.
In the example, the definitions for geog.gid and
geography.newparent are incorrect.
The following definitions are correct:
DEFINE geog.gid INTEGER VARIABLE <geography> DEFINE geography.newparent RELATION geography <geography>
In the example, the definitions for chan.gid, prod.gid,
geog.gid, time.gid, chan.hierrel, prod.hierrel,
geog.hierrel, time.hierrel, chan.inhier, prod.inhier,
and geog.inhier are incorrect.
The following definitions are correct:
DEFINE chan.gid INTEGER VARIABLE <channel> DEFINE prod.gid INTEGER VARIABLE <product> DEFINE geog.gid INTEGER VARIABLE <geography> DEFINE time.gid INTEGER VARIABLE <time> DEFINE chan.hierrel RELATION channel <channel chan.lvldim> DEFINE prod.hierrel RELATION product <product prod.lvldim> DEFINE geog.hierrel RELATION geography <geography geog.lvldim> DEFINE time.hierrel RELATION time <time time.lvldim> DEFINE chan.inhier BOOLEAN VARIABLE <channel> DEFINE prod.inhier BOOLEAN VARIABLE <product> DEFINE geog.inhier BOOLEAN VARIABLE <geography> DEFINE time.inhier BOOLEAN VARIABLE <time>
The keywords for the ARGS argument should be ADD
or ASSIGN and not ADDTO or ASSIGN.
The YESSPELL and NOSPELL options should
be marked as read-only. Their values reflect the NLS parameter settings
for the database. You cannot change their values using these OLAP DML options.
As part of the upgrade process, integration profiles for iPlanet synchronization, namely, iPlanetImport and iPlanetExport, are created. For these profiles to be configured and used for synchronization, they need to be added to configset1. If the profiles are available in configset1 as part of upgrade, it will be shown in Oracle Directory Manager under Integration Server as part of Configuration Set1. If the profiles are not available, then they can be added by using ldapmodify as follows:
ldapmodify -h <OID Host> -p <OID Port> -D <OID Super-user> -w <OID Super-user password> <<EOF dn: cn=configset1,cn=metadird,cn=configsets,cn=oracle internet directory changetype: modify add: orclodipconfigdns orclodipconfigdns: orclodipagentname=IPlanetImport, cn=subscriber profile, cn=changelog subscriber, cn=oracle internet directory orclodipconfigdns: orclodipagentname=IPlanetExport, cn=subscriber profile, cn=changelog subscriber, cn=oracle internet directory EOF
If you install Oracle9i release 9.2 server on top
of an existing ORACLE_HOME, then the existing databases can be
started only using the startup migrate option. If you try to start
an existing database without the migrate option, then you'll get
'ORA-01092: ORACLE instance terminated. Disconnection forced'.
Enqueue of JMS types and XML types does not work with Sys.Anydata
streams queues.
Call dbms_aqadm.enable_jms_types(<queue_table_name>); after dbms_streams_adm.set_up_queue(). However enabling a streams queue for these types may affect import/export of the queue table. Refer to bug 2250422.
When messages are propagated to remote destination and remote
Queue is not created (or disabled for enqueue or no subscribers are added,
and so on) the message becomes UNDELIVERABLE and is not moved
to the exception queue.
Create the remote queues before scheduling propagation to that destination.
When there are multiple database links from the same source
q to the same destination, internal errors [kwqpudpse: subelem]
or [kwqprcvpbq: dest hwm < ] may occur.
Use a single database link to propagate messages from one source to a destination.
A problem occurs if apply with rule set is altered and a
drop propagation is issued. This causes the drop propagation to hang.
Drop the apply and issue drop propagation. The drop propagation will succeed, and the user can recreate the the apply and create propagation again.
This problem occurs when the application issues a dequeue
with a next message navigation mode. AQ keeps the cursors open until all
the dequeues are complete. This results in the error snap shot too
old [1555] being raised.
Issue fewer number of dequeues with next_message navigation mode. (that is, issue a dequeue with first_message navigation mode more often).
Create an Oracle 8.1-style multi consumer queue and propagate
using loopback database link to multiconsumer (80,81) and single conssumer
(80,81 ) queues. Now, Drop queue/queue table hangs.
Increase the inittrans parameter in the storage clause of create queue table.
This occurs only when the queue name provided to set_up_queue
is greater than 24 characters, over the supported limit.
Specify queue names within the supported limit.
Http propagation for anydata queues not supported
for Oracle9i release 9.2.
None.
Http propagation between XMLType queues not supported inOracle9i
release 9.2
None.
Http propagation of XML queues not supported for Oracle9i
release 9.2 This occurs when propagation is scheduled between XMLType queues
over http.
This is a duplicate of bug 2206689.
Currently, the queue owner must be enabled as a database
user in order to enqueue or dequeue into their own secure queue (using
dbms_aqadm.enable_db_privs).
Use dbms.aqadm.enable_db_access to explicitly map
the agent to the queue owner.
Row migration rules incorrectly filter out some logical change
records (LCR), leading to incorrect results.
Modify rules for row migration manually to use the old values of the LCR if the new values are not present.
When configuring Streams capture, if multiple archive log
destinations are specified, the creation will fail with an ORA-1422 error.
Do not specify multiple archive log destinations at a capture site.
If there are tables with delete cascade foreign key relationships
among them and if the replication and subsequent apply of a delete DML
on the parent table raises an ORA-1403 (row not found) at the destination
sites, then you have run into this bug.
Do not use delete cascade constraints among tables being replicated. Instead replace the delete cascade constraints with a before row trigger on the "parent" tables to delete the child rows.
When global DDL and DML rules are used, a create outline
DDL will force the capture process to stop with an error saying "datatype
not supported". The problem is that the create outline DDL inserts rows
into the outln.ol$ table which contains a long column (which is
not supported by capture).
Modify the filtering rules to exclude DML statments on the outln.ol$ tables (and other similar outln.* tables).
Interrupting a DBMS_AQADM.UNSCHEDULE_PROPAGATION
command can cause an ORA-600 error when you try to reschedule the propagation
using DBMS_AQADM.SCHEDULE_PROPAGATION.
Avoid interrupting the UNSCHEDULE_PROPAGATION command. The ORA-600 [kwqpaspse2c: invalid status] error can be cleared by restarting the database.
When global level rules are in place to replicate DDL in
a Streams environment, DDL on public objects (e.g public synonyms) are
placed in the apply error queue with an ORA-26687 error (instantiation
scn not found).
At the destination sites, set a schema level instantiation scn for the user PUBLIC.
If a Streams environment is set up for replication of GLOBAL
DDL (such as grants and create user DDL) and
if the user creates a user via a grant DDL (e.g grant connect,
resource to new_user identified by ..) which is applied at the
destination sites, then subsequent DDL/DML in the newly created user are
not applied at the replicated destination and result in an ORA-26687 (instantiation
scn not found).
a) Create any new users with the create user DDL. For example,
give any desired grants to the new_user in separate grant DDL statements
b) If the new user has already been created via the grant DDL, then at the destination sites (for this DDL), set the schema level instantiation scn for this newly created user.
Streams capture and apply processes continue to run in restricted
mode.
Stop the capture and apply processes manually while in restricted mode.
DDL executed using execute immediate is not successfully
applied at the destination sites in a Streams replication environment.
Do not execute DDL using execute immediate.
Calling DBMS_PROPAGATION_ADM.DROP_PROPAGATION with
the value of the parameter PROPAGATION_NAME specified in lowercase
causes ORA-23601:PROPAGATION_NAME %prop_name% does not exist .
Specify the PROPAGATION_NAME parameter value as an uppercase name.
If the value of the parallelism parameter for the apply process
is greater than 1 and a transformation to change the schemaname of the
LCR is specified, an apply error occurs.
Either set the apply parallelism value to 1 or use a DML_HANDLER
to change the schemaname of the LCR.
User-defined statistics cannot currently be stored in an
external table, so any DBMS_STATS operation which involves user-defined
statistics (such as setting domain index statistics) will work incorrectly
if an external table is specified as the source or target (the stattab
argument is not null).
This restriction corresponds to bugs 2085026 and 2087959.
Querying v$logmnr_contents view returns the ORA:1280
error if the mining session was started with dbms_logmnr.continuous_mine
option in conjunction with a specified endScn.
Do not specify endScn parameter in start_logmnr()
procedure call if dbms_logmnr.continuous_mine option is also specified.
Similar functionality can be obtained by specifying the endTime
parameter in start_logmnr() procedure and specifying the endScn
in the where clause of the query.
Selecting column of rowid type from external tables fails.
ORA-01984 auditing directory object for write fails.
Audit trail for external tables not being recorded.
When specifying identifiers (for example, column or table
names) in the external table access parameters certain values are considered
to be reserved words by the access parameter parser. If a reserved word
is to be used as an identifier it must be quoted using double quotes.
The following are considered to be reserved words by the
access parameter parser:
The access parameter parser incorrectly handles the parsing
of the VARCHAR, VARCHARC, VARRAW, and VARRAWC
datatypes. If only a maximum length is specified without the "length of
the length field" the parser fails, returning a syntax error to the user.
Have the user specify a CHAR datatype
In INSERT and UPDATE statements, if a function is used as the value for an object type column (or attribute), the function may potentially be invoked several times for each row. To avoid this overhead, the function should be declared to be DETERMINISTIC. Also, INSERT..VALUES statements should be rewritten as INSERT..SELECT ... FROM DUAL to avoid multiple calls to the function.
Errors caused by improperly configured native compilation
are not currently trapped and sent to error$ or a trace file.
Instead, they go to the stderr on the computer where Oracle is
running. To diagnose such errors, you must run your client on the same
computer as Oracle is running. (These errors are typically configuration
errors, thus they should not occur after an initial correct ncomp
setup.)
We have identified some problems in our mechanisms for choosing
which userid or roles to use when executing certain constructs
from within a SQL statement.
These concerns affect uses within a SQL statement of the DEREF operation and calls from a SQL statement to "invoker's rights" PL/SQL, Java, and callout C procedures. Calls to both user-written invoker's rights procedures and to Oracle-provided ones such as UTL_REF and DBMS_LOB are of concern.
When a SQL statement contains any of these affected operations which are meant to execute using their caller's privileges, it is somewhat unpredictable which userid and roles will be used if the statement is not parsed, executed, and fetched within identical privilege environments.
Passing REF CURSOR or statement handle values from one privilege environment to another should be avoided for any statement that includes calls to invoker's rights functions or DEREF operations. Instead of passing the original REF CURSOR, do the parse and fetch from the statement in a single environment. Either pass the results as an array, or create a temporary table and pass a REF CURSOR that selects from that temporary table.
The privileges of the owner of an invoker's rights routine may be used instead of those of the caller to it when performing any of the affected operations from within a SQL statement, if the invoker's rights routine uses a separate FETCH statement or a cursor FOR loop construct on that SQL statement.
Programmers of invoker's rights routines are cautioned to
not use a FETCH or cursor FOR loop construct on a passed
REF CURSOR or on a SQL statement that itself contains calls to
an invoker's rights routine or DEREF operations. Use a single
SELECT ... INTO or SELECT ... BULK COLLECT INTO statement
instead of using FETCH or cursor FOR loop constructs
to avoid this difficulty.
While worth a separate caution, this issue is largely a symptom of Bug 1757894.
Views are not acting predictably as a definer's rights layer. Depending on circumstance either the view owner's privileges or the privileges of the code executing a query or DML statement on the view may be used when executing the affected operations.
Views are intended to behave as definer's rights layers, that is, all operations within a view should take place using the privileges of the owner of the view. A workaround for cases in which one of the affected operations is invoked from a view definition's query but does not execute with the view owner's privileges is to create a definer's rights PL/SQL function which does the desired operation, and to call to that function instead of the original operation in the definition of the view.
When executing a Java or callout C routine through a definer's rights call spec, enabled session-level roles may sometimes be used when doing privilege checks. This is not desirable; a definer's rights routine should behave the same regardless of its caller's privileges.
Programmers creating call specs should label them as invoker's rights using the AUTHID CURRENT_USER clause if it is desired that enabled session-level roles propagate into the target routine. Note that doing so may affect which userid propagates into the target routine as well: if it is not desirable for the function to execute using the caller's userid's privileges, the use of the caller's roles should not be relied upon either.
If you are using JDB and the JDWP-based PL/SQL debugger to
debug a PL/SQL program, you may see Oracle internal error 17278 when typing
JDB command quit immediately after an exception occurred.
There is no direct work around for this. Do not do quit immediately after an exception occurs; try doing step then doing quit.
In Oracle9i release 9.2, you can grant and revoke
the DEBUG object privilege (as described in Table 17-3 of the
Oracle9i SQL Reference), but Oracle does not yet meaningfully
use this privilege. Debugging of PL/SQL and Java routines using the JDWP
interface is currently allowed only if the session user has debugging permission
on all objects, as granted through the DEBUG ANY PROCEDURE system
privilege.
The DEBUG ANY PROCEDURE system privilege is currently checked along with the DEBUG CONNECT SESSION system privilege when deciding whether to allow a connection to be established between a database session and a JDWP-based debugger; a connection will only be allowed at all if the user has both of these system privileges.
The new JDWP-based debugging mechanisms for PL/SQL and Java
do not yet follow our desired rules for single-stepping behavior. In particular:
a) Single-stepping will currently stop within PL/SQL program
units that are compiled without debug information. In future releases it
is likely that we will tune stepping to stop only within program units
that were compiled with debug information and for which the current debugging
session has DEBUG privilege.
b) At times the otherwise "correct" place to stop when single-stepping
is a place at which we cannot actually stop. This may be due to it being
in natively-compiled code, or due to a lack of sufficient debug information
and/or privilege. In these cases, our current implementations of various
subcomponents differ in the rules they apply regarding where to actually
next stop instead. There are also similar differences in the rules used
to decide where to actually stop on a step up or next/step
over kind of command that causes the current frame to finish.
In future releases it is likely that we will tune the various subcomponents
to agree on their rules for such cases, causing some visible changes in
where stops actually occur.
Both application and development tool developers should avoid
becoming dependent on our existing behavior in such cases. We consider
the existing behavior simply incorrect and are likely to adjust it in future
without providing any backwards compatibility mechanism.
Application developers should consider using the next or step over functionality exposed through their debugger, or setting a breakpoint explicitly where they next desire a stop and letting the program run freely until it hits that breakpoint, if inconveniencing stops are occurring within code that was compiled without debug information or is otherwise not of interest to the developer.
When using PL/SQL native compilation on NT, the program might
get a stack overflow exception when the call stack is too deep either because
of recursion or because of a long call chain.
The workaround is to compile such highly recursive programs to interpreted mode.
The native compilation of very large PL/SQL procedures/functions
fails on Windows NT because of a compiler limitation in Visual C++ compiler.
In such cases, split the procedure into smaller procedures.
The server parameter file (SPFILE) cannot be used to start
up the database in a Real Application Clusters Guard I environment. Use
the parameter file (PFILE).
Materialized views based on user-defined types may fail with
an ORA-00600 [4882] error if the data or index exceeds a single
extent.
The workaround is to specify an initial storage extent size
for both the materialized view and the index in the create statement so
that the initial extent for each will be large enough to contain all the
data of the materialized view and index in a single extent.
For example, if the following statement produces an ORA-600 [4882] error:
create materialized view foo refresh fast for update as select * from bar;
then modify the create statement similarly to the following:
create materialized view products storage (initial 1M) using index storage (initial 1M) refresh fast for update as select * from bar;
Compatibility problem for AL16UTF16 NCHAR between
the Oracle9i server and Oracle8i client:
There will be error for connection. OCI fixed the problem by changing the protocol, but the fix cannot be backported to Oracle8i. NLSRTL provides a workaround for it by backporting the AL16UTF16 character set to Oracle8i. Please see bug 1634613 and Bug 165200 (backport).
Clients of OCI connection pooling should be aware that it
is possible to run into an artificial deadlock with competing DML statements.
Consider an example with two threads operating on a pool
of one physical connection. Thread 1 updates a row and sleeps. Thread 2
tries to update the same row and blocks holding the only connection in
the pool. Thread 1 comes back to commit the update and blocks on the pool.
This scenario with one connection in the pool and two threads can be generalized
to a pathological case of 'n' connections and 'm' threads where m > n.
The current workaround is to increase maximum connections in the pool so that threads holding locks will be allowed to proceed.
When the OCCI interface is used to populate a nested table
or varray column with more than 245 elements and then SQL*Plus is used
to query these columns, an internal error is generated.
The above columns can be queried by using the OCCI interface.
If an OCI application creates a subtype and works on it in
the same session, the following scenario will not work:
- First, select instances of super-type.
- Now selecting instances of the subtype, in this same session,
can lead to errors or incorrect results.
After creating the subtype, reconnect the session to access instances of the new subtype correctly.
An OCI application which manipulates objects should not create
more than 255 physical connections per OCI environment handle. If it does,
an ORA-21522 error is raised during OCI calls manipulating objects.
Use multiple OCI environment handles or use OCI?s connection
pooling feature. The connection pooling feature will give you a large number
of logical connections (sessions), mapped to fewer physical connections.
Failover does not work with OCI scrollable cursors
When inserting a string into a buffer with insufficient length,
insertion finishes without warning although truncation has happened. In
such scenarios calling SQL LENGTH() function will return incorrect
value.
JDBC-OCI driver will hang applications when executing a query
with one extra double quote and environment variable, NLS_LANG,
is set. Problem occurs in both single and multibyte database character
set.
NLS: XSL:SORT does not sort base on XSL:LANG.
SQLX functions currently do not accept BFILE parameters
Functional indexes are not picked up by queries on schema-based
tables.
Use a hint specifying the table and the index to be used for the query.
Oracle9i release 9.2.0.1, allows inserts of AnyData
containing XMLtype into AnyData columns directly. This is invalid,
since the XMLtype image may contain LOB or other data that cannot be stored
in a packed AnyData form. This could lead to loss of data when
trying to retrieve the same.
ALTER TABLE MOVE TABLESPACE for an XMLType
table raises an ORA-600 error.
Create the table in the right tablespace during CREATE TABLE.
You cannot modify SYS_NC_ROWINFO$ in before-row triggers for XMLType tables.
XMLType is not supported through database links.
Spurious rows may be inserted into out-of-line tables, if there are triggers.
XMLType.toObject() does not handle subtypes.
Query rewrite on non-schema-based XMLType views
using the XMLType member functions on SYS_NC_ROWINFO$
does not work.
Use the SQL function extract() instead.
Duration datatype not supported in query rewrite.
Function-based indexes are not picked up on schema-based
XMLType tables, when queries are executed which could use the
function-based index.
Use a query hint specifying the index to be used.
SQLX function does not support BFILE parameters.
Duration datatype not working with updateXML function.
NLS: CREATE MATERIALIZED VIEW REFRESH COMPLETE causes an ORA-00600 error.
Setting of user parameters does not work with XMLTransform
or XMLType transform method.
Use the PLSQL DOM package to set the parameters.
The V7 OCI LONG API is not supported for LOBs. This includes functions such as oflng.
ORA-0932 in DECODE (NCHAR,NCLOB,NCHAR,NCHAR)
in PL/SQL
If the arguments to DECODE are passed in the following
order: nchar, nclob, nchar, nchar,
then an ora-0932 error is returned. However if the arguments are
passed with the nclob as the first parameter as: nclob,nchar,nchar,nchar,
it is fine.
A user may encounter ORA-600 [4882] when creating a materialized
view based on an object table or a table with object columns having a sufficiently
large amount of data.
Specify storage parameters for the initial extent to be large
enough both in the materialized view's storage clause as well as in the
USING INDEX clause in the CREATE MATERIALIZED VIEW statement.
Another workaround would be to specify larger MINEXTENTS
storage value for both the table and index segments. If a larger initial
is specified the space is locked by the segment, which might be a undesirable
(INITIAL extent values cannot be ALTERed.) Whereas a
user specifying a large MINEXTENTS value can ALTER it
down to smaller number later on.
Binds and defines in queries mapped to a remote database
always use byte semantics in the remote database, even if the user specified
character semantics or used nchar.
While performing an online redefinition of a table, if a
TRUNCATE TABLE command is issued concurrently, the online
redefinition task may hang or give incorrect results.
The workaround for this is to avoid issuing a TRUNCATE
TABLE command while performing an online reorganization of the
same table.
|
Note: In Data Guard Manager, the READ-WRITE state is represented as the Paused state, and the READ-WRITE-XPTON state is represented as the Online state. |
Object type tables/views are not supported.
If you can use a language other than PL/SQL, you could construct
the VARRAY using OCI or SQL directly.
Consider a type which has a user-defined constructor, that
doesn't hide the attribute value constructor. Add a column to a table using
the ALTER TABLE statement specifying a column default expression
invoking the type's attribute value constructor. Instead of succeeding,
an ORA-4044 error is returned.
For example, the following statement returns ORA-4044:
ALTER TABLE tb2 ADD (c2 t1 DEFAULT t1(1));
Use two ALTER TABLE statements. The first one should
add the column without specifying a default clause and the second ALTER
TABLE statement should modify the column indicating the default clause.
For example, use the following two statements in place of the single ALTER TABLE statement above:
ALTER TABLE tb2 ADD (c2 t1); ALTER TABLE tb2 MODIFY (c2 DEFAULT t1(1));
SQL statements containing a call to a user-defined constructor
will return error ORA-24409 when executed from an Oracle 8.1.7 client.
Note that user-defined constructors are only available with Oracle9i
release 9.2 compatibility.
Embed the SQL statement inside a PL/SQL block.
ORA-00923 no longer gets raised when a foreign-key constraint
is added to a table whose schema name contains a single-quote.
Parallel multi-joins (INNER, CROSS, OUTER
using ANSI syntax) of parallel table functions run on Oracle MTS mode can
hang.
Turn off parallelism for table functions or use regular non-ANSI joins.
In some rare cases, updating or deleting parallel DML operations
may not achieve same degree of parallelism as before for tables with large
number of partitions.
"_intrapart_pdml_enabled=FALSE"
Parallel Join query plan with ORDER BY
in the right input can hang.
Turn off parallelism. Run the query serially.
Queries/subqueries which reference an external table defined
with a named pipe as the data source may hang if the SQL operation requires
the underlying data source(s) to be scanned more than once.
NLS:T9N: EMWEBSITE_ZH_CN.HTML is corrupted. When
using EM via browser in a Simplified Chinese environment, users may see
garbled characters.
When the OLAP Cube Viewer is launched from OEM, it starts
up normally and functions normally, but it causes OEM to go into a wait
state until Cube Viewer is exited. Once Cube Viewer is exited, OEM functions
normally.
If the user wants to make changes to one of their OLAP Cubes
and see it reflected in Cube Viewer, there have two methods. The first
is to stop Cube Viewer, modify the Cube and then restart Cube Viewer. The
second method is to simply launch another instance of the OEM client on
your machine.
The Character Set Scanner is unable to detect character data
stored within a VARRAY type.
In order to scan character data in a VARRAY type, the data must be moved to a datatype such as CHAR/CLOB which the scanner can support in this release.
If the Oracle database server is running in Shared Server
mode and a PL/SQL program attempts to connect to a host/port combo by means
of the UTL_TCP.OPEN_CONNECTION() call where there is not currently
a server listening at that remote host/port, then the connect will appear
to succeed without actually doing so. However, when the server attempts
to actually read or write on the socket for the first time, an error will
be returned as if the remote end had suddenly disconnected (ORA-12537:
TNS:connection closed). The same situation applies to users using
the UTL_HTTP or the UTL_SMTP packages to connect to a
HTTP or SMTP server. This problem does not exist when the database server
is running in the dedicated server mode.
No known workaround exists.
ALTER SYSTEM does not change the current session's
setting of NLS_LENGTH_SEMANTICS, at least not in the opinion of
DDL.
Set NLS_LENGTH_SEMANTICS in the initialization parameter file and leave it alone, or use ALTER SESSION.
Internal error QCTSTC2O1 is returned when starting
up an old database with Oracle9i with COMPATIBLE=9.0.0,
and compatibility errors are returned when starting up with COMPATIBLE
< 9.0.0. This only happens when the upgrade script wasn't run before
starting up with COMPATIBLE=9.0.0.
Setting event 10619 allows the database to be started up with COMPATIBLE=9.0.0.
TIMEOUT for OracleOCIConnectionPool only
works in JDK 1.2.2_07 or a higher version
None
OCI scrollable cursors for queries involving database links
are not supported.
None
Users get ORA-600 [6051] doing various index operations for
indexeswith keys exceeding half a block size.
Use larger block sizes for indexes, redefine tables to keep index key size of less than half a block, or do not use indexes.
LONG-to-LOB functionality will not work for distributed SQL.
none
For a migrated tablespace, from dictionary managed to locally
managed, when the metadata is exported for plugin tablespace the migration
information in the metadata is not correctly exported. This results in
internal errors, once the tablespace is plugged in and the metadata is
imported, on dropping pre-existing segments and the tablespace. This results
in internal errors and data corruption, once the tablespace is plugged
in and the metadata is imported.
None. Do not transport migrated tablespaces until the bug is resolved.
For a migrated tablespace, from dictionary managed to locally
managed, when the metadata is exported for plugin tablespace the migration
information in the metadata is not correctly exported. This results in
internal errors, once the tablespace is plugged-in and the metadata is
imported, on dropping pre-existing segments and the tablespace.
None. Do not transport migrated tablespaces until the bug
is resolved. A patch for this bug will be made available on the Metalink
support website.
Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|