ORA-03113 while running Catupgrd.sql to upgrade from 10.2.0.3.to 10.2.0.4

Problem Description
It is tried to patch database to upgrade from 10.2.0.3.to 10.2.0.4 after "startup upgrade". But while running catupgrd.sql script, the upgrade fails repeatedly by ORA-03113 error.

SQL> create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t
2 with object identifier (schemaoid) as
3 select '1','0',
4 u.user#, u.name, extractvalue(VALUE(s), '/schema/@x:schemaURL',
5 'xmlns="http://www.w3.org/2001/XMLSchema" xmlns:x="http://xmlns.oracle.com/xdb"'),
6 s.sys_nc_oid$,
7 case when under_path(value(r), '/sys/schemas/PUBLIC') = 1
8 then 0 else 1 end,
9 s.getclobval(), -- unstripped
10 xdb.dbms_xdbutil_int.XMLSchemaStripUsername(XMLTYPE(s.getClobVal()),
11 u.name) -- stripped
12 from sys.user$ u, xdb.xdb$schema s, xdb.xdb$resource r
13 where extractvalue(VALUE(r), '/Resource/XMLRef') = ref(s)
14 and u.user# = sys_op_rawtonum(extractvalue(VALUE(r),'/Resource/OwnerID'))
15 AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (u.user#, 0) OR
16 EXISTS ( SELECT * FROM session_roles
17 WHERE role='SELECT_CATALOG_ROLE' ))
18 /
create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

The simple select statement also fails with the same ORA-3113 error.

Cause of the Problem
The problem happened due to Oracle bug.
Specifically the following event was set as a workaround for Bug 4684209 which is fixed in patch set 10.2.0.4.

event='10195 trace name context forever, level 1'

Solution of the Problem
Bug 5161782 can be encountered if the following query returns any rows where the objects are owned by a user other than "XDB".
select a.name "OWNER",
b.name "NAME",
b.obj# "OBJ#"
from sys.user$ a,
sys.obj$ b
where b.name in ('XDB$NMSPC_ID', 'XDB$QNAME_ID', 'XDB$PATH_ID')
and a.user# = b.owner#;

SQL> select a.name "OWNER",
  2  b.name "NAME",
  3  b.obj# "OBJ#"
  4  from sys.user$ a,
  5  sys.obj$ b
  6  where b.name in ('XDB$NMSPC_ID', 'XDB$QNAME_ID', 'XDB$PATH_ID')
  7  and a.user# = b.owner#;

OWNER                          NAME                                 OBJ#
------------------------------ ------------------------------ ----------
XDB                            XDB$NMSPC_ID                        42063
XDB                            XDB$QNAME_ID                        42064
XDB                            XDB$PATH_ID                         42065

As all the rows return OWNER as XDB so we are not hitting Bug 5161782 but might be some other bug as Oracle is extremely buggy product :). In fact we are hitting this bug for the initialization parameter EVENT which was workaround for Bug 4684209 and this bug is resolved in 10.2.0.4 patchset. So,

1. Remove the following event from the parameter file as the workaround for Bug 4684209 is no longer needed with patch set 10.2.0.4

event='10195 trace name context forever, level 1'

2. Execute the 10.2.0.4 catupgrd.sql script again.

ORA-29857: domain indexes and/or secondary objects exist in the tablespace

Problem Description
While dropping a tablespace it fails with error ORA-29857 like below.
SQL> drop tablespace example including contents and datafiles cascade constraints;
drop tablespace example including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-29857: domain indexes and/or secondary objects exist in the tablespace

Cause of the Problem
The problem arises because there was an attempt to drop a tablespace which contains secondary objects and/or domain indexes.

Solution of the Problem
Drop the domain indexes on the tablespace which you are going to delete. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

You can find whether tablespace EXAMPLE contains any domain index or not by running query,
SQL> select index_name from dba_indexes where index_type = 'DOMAIN' and tablespace_name = 'EXAMPLE';
no rows selected

which means it does not have any domain index. Next you should look for secondary objects. However if you want to drop a tablepsace and if you think the user's objects in the tablespace is no longer needed you can try deleting all objects in that tablespace before dropping tablespace. You can also look for the list of users contain objects inside specified tablespace.

You can check it by,

SQL> select distinct owner from dba_tables where tablespace_name='EXAMPLE';
OWNER
------------------------------
HR
OE
PM
SH
IX

If you think you no longer need these user you can try to delete users.

SQL> drop user HR casecade;
User dropped.

SQL> drop user OE casecade;
User dropped.

SQL> drop user sh cascade;
User dropped.

SQL> drop user oe cascade;
User dropped.

SQL> drop tablespace example including contents;
drop tablespace example including contents
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop user ix cascade;
User dropped.

SQL> drop tablespace example including contents;
Tablespace dropped.

ORA-7445 [EVAOPN2] while running a query

Problem Description
While running a query we get ORA-7445: exception encountered: core dump [evaopn2()+209]. Simply this is Oracle bug and this indicates Oracle is extremely buggy product. The failing function evaopn2() in ORA-7445 error generally occurs during the execution of a query. The error typically only occurs with a specific stream of data being fetched from the objects in the execution plan. The problem is caused often by an optimization but it is not until a row is fetched and evaluated that the error happens.

After you hit ORA-7445 [EVAOPN2] please refer to the related trace file and review the execution plan generated from the offending SQL. Using the information provided in the execution plan, review the bug descriptions and try to solve if already existed in oracle support. Try to find a match. If already exist patch then apply that.

You can have workaround though unless you don't have patch.

For example,
- If query_rewrite_enabled=TRUE then this bug might fire. You can make the query works with query_rewrite_enabled=FALSE.

- In case of temp table replacement with star transformation cause a query to dump if it includes an unmerged lateral view then workaround set star_transformation_enabled=temp_disable.

- If a query with an inline view containing a GROUP BY is merged and a core dump occurs when NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI are set, then workaround disable complex view merging or prevent GROUP BY view merging using a NO_MERGE hint
"_complex_view_merging"=false

- If a select query with a CONNECT BY clause using a functional index may dump then workaround set "_disable_function_based_index" = true.

- If queries using CONTAINS clause and undergoing star temp table transformation and dumps on evaopn2 or evaopn3 then workaround set "star_transformation_enabled"=false.

- If query includes nested views column and projection information shows that an unreferenced column is projected up through the view levels then workaround set "_pred_move_around"=FALSE.

- If wrong results with function based index then workaround do,
1. alter session set "_b_tree_bitmap_plans"=false;
2. alter session set "_disable_function_based_index"=true;

- If wrong results or error from UPDATE statement involving views and subqueries then workaround set "_optimizer_cost_based_transformation" = false.

- If dump on evaopn2/evaopn3 if transitive predicates are generated based on the expression used in a function based index then workaround do,
post 11g - Set "_replace_virtual_columns" = true
pre 11g - Set "disable_function_based_index" = true;

- A dump occur (typically under evaopn2) if a STAR transformation occurs and a bitmap semijoin subquery is involved then workaround disable star transformation.

- If a dump can occur (in evaopn2 or qervwRestoreVWBufptrs) then workaround set _pred_move_around=false.

- If the execution plan has a COUNT STOPKEY used in a UNION ALL query (eg: for queries involving a ROWNUM predicate) then workaround set "_complex_view_merging"=false.

- If INSERT AS SELECT may dump when there is an expression in the select list and a functional index/virtual column exists on that expression in the target table then workaround set,
"_replace_virtual_column"=false.

- If executing a query with OR'ed functional index expressions and bitmap operations in its execution plan then workaround set
"_b_tree_bitmap_plans" = FALSE

I have listed some known bugs here about EVAOPN2 failing function.

NB	Bug	Fixed	Description
	8935793	11.2.0.2, 12.1.0.0	Dump on evaopn2/evaopn3 from query with user-defined aggregate function
	9023330	11.2.0.2, 12.1.0.0	Dump (evaopn2) from temp table replacement with Star transformation
	9336802	11.2.0.2, 12.1.0.0	Dump on evaopn2/3 with nls_comp=linguistic & nls_sort=binary_ci set
	9820314	11.2.0.2, 12.1.0.0	Dump [evaopn2] on hierarchial query with function based indexes
	8743476	11.2.0.2, 12.1.0.0	Dump on evaopn2/evaopn3 with CONTAINS and STAR_TRANSFORMATION_ENABLED
	9894940	11.2.0.3, 12.1.0.0	ORA-7445 [evaopn2] / [evaopn3] / ORA-600 [rwoirw: check ret val] from predicate move around
	8581792	10.2.0.5, 11.2.0.2	Dump EVAOPN2/Dump EVAOPN3/Wrong Results with function based index
	7265342	11.2.0.1	Wrong Results / Dump on EVAOPN2 or EVAOPN3 with ORDER BY aggregate
	6183577	10.2.0.5, 11.1.0.7, 11.2.0.1	OERI[15851] / wrong results / error from UPDATE involving views and subqueries
	6798650	10.2.0.5, 11.1.0.7.6, 11.2.0.1	Dump showing execution plan for sQL with ANALYTIC function
	7135135	10.2.0.5, 11.2.0.1	Dump on evaopn2/evaopn3 with transitive predicates and function based index
	7459192	10.2.0.5, 11.2.0.1	Dump (evaopn2) from GROUP BY with NLS_COMP=LINGUISTIC
	7328856	10.2.0.5, 11.2.0.1	Dump (evaopn2) from INLIST on virtual expression used in indexes
	6272288	11.2.0.1	Dump on evaopn2/evopn3 during query when _b_tree_bitmap_plans=true
	7476591	10.2.0.5, 11.2.0.1	OERI[qctcte1] / dump (evaopn2 or evaopn3) from SQL with correlated subquery
	8371884	10.2.0.5, 11.2.0.1	Dumps/Errors/Excessive Parse Time and Memory allocation with Star transformation
	5886192	11.2.0.1	Dump (evaopn2/qkabix) from bitmap semijoin and star transformation
	6474804	10.2.0.5, 11.1.0.7, 11.2.0.1	Dump [evaopn2] from push predicate with function based index
	6021687	10.2.0.5, 11.1.0.7, 11.2.0.1	Dump (evaopn2) from COUNT STOPKEY in plan with UNION ALL
	6729273	10.2.0.5, 11.2.0.1	Dump (evaopn2) from SQL using a function based index
	6653704	10.2.0.5, 11.1.0.7, 11.2.0.1	Dump (evaopn2) from nested loop join with bitmap access to functional index
	6438731	11.1.0.7, 11.2.0.1	Dump (under evaopn2) from INSERT as SELECT with function based index
	6376928	10.2.0.5, 11.1.0.7, 11.2.0.1	Dump from GROUP BY query with predicates on functional index column
	8971835	10.2.0.5, 11.2.0.1	Assorted Dumps / ORA-932 / Wrong results when accessing over 1000 columns
	8391879	11.2.0.1	Dump on evaopn2 from query with functional index
	5990716	10.2.0.4, 11.2.0.1	Query using index join dumps [evaopn2]
	6122097	10.2.0.5, 11.1.0.7	Wrong Results/Dump [evaopn2] with function based index present
	6316585	10.2.0.5, 11.1.0.7	Dump / OERI from complex view merging
	8601565	10.2.0.5	Dump in evaopn2 using INLIST with function based index (Fix 5686412 incomplete)
	9024535		A dump can occur in evaopn2 using TABLE(CAST ..)
	5574966	10.2.0.4, 11.1.0.6	Dump (evaopn2) or Wrong Results with views and function-based index
	5196061	10.2.0.4, 11.1.0.6	Dump in evaopn2() from WITH query using WINDOW functions
	4684209	10.2.0.4, 11.1.0.6	Dump (under evaopn2) with transitive predicate from CHECK constraint
	5488174	10.2.0.4, 11.1.0.6	Dump [evaopn2] on a CONNECT BY query
	4502658	10.2.0.2, 11.1.0.6	Dump with STAR temp table transformation
	4954651	10.2.0.3, 11.1.0.6	Wrong Results / Dumps from view with select list subquery
	5879865	10.2.0.5, 11.1.0.6	Select statement may dump under evaopn2
	5225168	10.2.0.4, 11.1.0.6	Dump (evaopn2) from CONNECT BY
	5649765	10.2.0.4, 11.1.0.6	Dump [evaopn2] from star transformation with pushed predicate
	5913244	10.2.0.5, 11.1.0.6	Dump from CASE or DECODE expression that resolves to a constant
	5686412	10.2.0.4, 11.1.0.6	Dump (evaopn2) from query using functional index
	4517291	10.2.0.2, 11.1.0.6	Dump (evaopn2) from native pipelined table function with a CONCATENATION
	5381446	10.2.0.4, 11.1.0.6	Wrong results / dump from STAR transformation with INDEX join
	4939157	9.2.0.8, 10.2.0.3, 11.1.0.6	Dump (evaopn2) using a functional index
	3870037	9.2.0.6, 10.1.0.5, 10.2.0.1	Dump (qertbFetchByRowID) from CONNECT BY
	4098853	9.2.0.7, 10.1.0.5, 10.2.0.1	Dump (qerixGetKey->evaopn2) using COUNT(NOT_NULL_COLUMN) with a view
	4156916	9.2.0.7, 10.1.0.5, 10.2.0.1	Dump / wrong results from TO_DATE without a full date format mask
	3869959	10.1.0.5, 10.2.0.1	Dump (evaopn2) if SQL has an XMLType column as a correlated variable of a subquery
	3823887	9.2.0.7, 10.1.0.4, 10.2.0.1	Dump / wrong results from NVL expansion
	3785459	10.1.0.4, 10.2.0.1	Dump (evaopn2) when updating a view with an INSTEAD OF trigger
	3463926	9.2.0.7, 10.1.0.4, 10.2.0.1	Dump (evaopn2) possible from UNNEST of a subquery column
	3249318	9.2.0.8, 10.1.0.5, 10.2.0.1	A query using UNION ALL with multiple mergable views may dump in evaopn2
	3512123	9.2.0.6, 10.1.0.3, 10.2.0.1	Dump (evaopn2) with BETWEEN predicate on PARTITIONED table
	4178560	9.2.0.7, 10.1.0.5, 10.2.0.1	Complex view merge of DISTINCT view may dump
	3625392	9.2.0.6, 10.1.0.4, 10.2.0.1	wrong results / dump (evaopn2) with star transformation
	3253133	9.2.0.6, 10.1.0.3, 10.2.0.1	Dump (in evaopn2) from queries using a FUNCTIONAL INDEX with complex view merging
	3250883	9.2.0.6, 10.1.0.4, 10.2.0.1	Inline view using functional index can give wrong results
	2364585	9.0.1.4, 9.2.0.2, 10.1.0.2	STAR transformation may dump (EVAOPN2) if fact column is leading prefix of bitmap index
	2711380	9.2.0.5, 10.1.0.2	Dump (in evaopn2) using SYS_CONNECT_BY_PATH in an inline view
	2189463	9.0.1.4, 9.2.0.2, 10.1.0.2	ORA-7445 [EVAOPN2] with FUNCTIONAL INDEX on semi-join (EXISTS) predicate
	2293722	9.0.1.4, 9.2.0.2, 10.1.0.2	ORA-7445 [evaopn2] possible using BITMAP JOIN INDEX with may NULLs
	2286794	9.0.1.4, 9.2.0.2, 10.1.0.2	Dump in evaopn2 if STAR transformation FACT table has >1 bitmap index on a join column
	2468542	9.2.0.3, 10.1.0.2	ORA-7445 [EVAOPN2] with DECODE predicate involving FUNCTIONAL index
	2143154	9.0.1.4, 9.2.0.2, 10.1.0.2	Dump (in evaopn2) from view merging of semi joins
	2500323	9.2.0.5, 10.1.0.2	ORA-7445[evaopn2] for index join with functional index
	3042674	9.2.0.5, 10.1.0.2	Wrong results on ANSI object queries / XDB "extract" operations may dump (evaopn2)
	2742863	9.2.0.5, 10.1.0.2	Dump (evaopn2) possible using ANSI join
	3156460	9.2.0.5, 10.1.0.2	Dump (in evaopn2) possible using functional indexes
	2630054	9.2.0.3, 10.1.0.2	ORA-7445 [EVAOPN2] / wrong results from CASE expression
	3224383	9.2.0.5, 10.1.0.2	Dump in evaopn2 from STAR transformation with function based indexes
	3137752	9.2.0.5, 10.1.0.2	Dump in evaopn2 from SELECT against LIST PARTITIONED TABLE with join
	3163361	9.2.0.5, 10.1.0.2	SELECT rowid from an object type dumps
	3173332	9.2.0.5, 10.1.0.2	Dump (in evaopn2) from temp table Star transformation with an ORDER BY
	2671151	9.2.0.4, 10.1.0.2	ORA-7455 [EVAOPN2] querying PARTITIONED table with functional index
	3051660	9.2.0.5, 10.1.0.2	Dump in evaopn2 with duplicate predicates against a LIST partitioned table
	3196059	9.2.0.5, 10.1.0.2	Wrong results / dump (evaopn2) querying list partitioned tables
	2656368	9.2.0.5, 10.1.0.2	Dump in evaopn2 when query uses function based indexes on partitioned table
	2708898	9.2.0.3, 10.1.0.2	STAR_TRANSFORMATION with a WINDOW function may dump (evaopn2)
	2906041	9.2.0.5, 10.1.0.2	Dump selecting from object view using UNIONed object tables
	2908284	9015PSE, 9.2.0.4, 10.1.0.2	Dump in EVAOPN2 using DESCENDING functional index
+	2687315	9.2.0.4, 10.1.0.2	Parallel range scan of DESC index may dump (under QERPXSTART/QERPX_START/EVAOPN2)
	4523371	9.2.0.8	Dump (evaopn2) from query on unmerged inline view
	1956846	8.1.7.4, 9.0.1.4, 9.2.0.1	ORA-7445[EVAOPN2] possible from STAR TRANSFORMATION if SUBQUERY_PRUNING enabled
	1939622	9.0.1.4, 9.2.0.1	CONNECT BY may dump in evaopn2()
	2110054	8.1.7.4, 9.0.1.3, 9.2.0.1	Select COUNT(*) from a nested complex view with GROUP BY in inner view may dump in evaopn2
	1956826	9.0.1.0	Dump in EVAOPN2 possible with CBO when CHECK constraints exist
	1666532	9.0.1.0	Dump in EVAOPN2 possible using CONNECT BY PRIOR with a "DESC" index
	1099596	8.1.7.2, 9.0.1.0	Dump in evaopn2 on SELECT from a VIEW using a DESC INDEX
	2347493	9.0.1.0	ORA-7445 [EVAOPN2] from DESC index if predicates contain constants
	1478998	8.1.7.2, 9.0.1.0	Dump in EVAOPN2 possible
	869177	8.1.6.0	Dump from view with ORDER BY DESC and DESC/FUNCTIONAL INDEX

- '+' indicates a particularly notable bug.

Exporting database fails with PLS-00201: identifier 'XDB.DBMS_XDBUTIL_INT' must be declared

Problem Description
While performing database export operation it fails with following error messages.
. exporting triggers
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 26:
PLS-00201: identifier 'XDB.DBMS_XDBUTIL_INT' must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 26:
PLS-00201: identifier 'XDB.DBMS_XDBUTIL_INT' must be declared
ORA-06550: line 1, column 14:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

Cause of the Problem
The problem occurred because the package DBMS_XDBUTIL_INT object does not exist in the database. You can check by following query,

SQL> SELECT status,object_id,object_type,SUBSTR(owner||'.'||object_name, 1, 40) "OWNER.OBJECT" FROM dba_objects WHERE object_name = 'DBMS_XDBUTIL_INT';

no rows selected

The PLS-00201 indicates that the export utility is expecting to find the package BMS_XDBUTIL_INT. This package together with its package body should exist in the XDB schema and should be valid.

Solution of the Problem
Solution 01: If you don't have any schema objects in the database that use the XML functionality, remove the XDB schema and the XDB tablespace as it is described in http://arjudba.blogspot.com/2011/03/how-to-deinstall-and-reinstall-xml.html. The XDB schema and the XML functionality can be re-installed at a later time if actually needed.

Solution 02: If you are using the XML functionality, and you have recently applied a patch-set, check whether the data dictionary has been upgraded accordingly.

SQL> SELECT SUBSTR(comp_id,1,15) comp_id, status, SUBSTR(version,1,10)
version, SUBSTR(comp_name,1,30) comp_name
FROM dba_registry ORDER BY 1;

If the version of the components CATALOG, CATPROC, XDB (among others) is lower than the patchset version that you have applied, re-run the script catpatch.sql after a startup migrate. See the ReadMe file of the patchset for all these mandatory post-installation instructions.

Solution 03: If you are using the XML functionality, and the data dictionary components have the correct version and are VALID or LOADED, then re-create the package XDB.DBMS_XDBUTIL_INT .
SQL> connect / AS SYSDBA
SQL> @$ORACLE_HOME/rdbms/admin/prvtxdb.plb
SQL> exit

Running catqm.sql generates ORA-04098 Trigger 'SYS.XDB_INSTALLATION_TRIGGER' is Invalid

Problem Description
Re-installation of XDB as is it described in http://arjudba.blogspot.com/2011/03/how-to-deinstall-and-reinstall-xml.html fails with ORA-04098 error like below.

SQL> @catqm password xdb temp
CREATE TABLE sys.xdb_installation_tab (
*
ERROR at line 1:
ORA-04098: trigger 'SYS.XDB_INSTALLATION_TRIGGER' is invalid and failed re-validation

Cause of the Problem
In case of an XDB installation fails it might not get to the end of the catqm.sql script where some temporary objects are normally dropped. The existence of XDB_INSTALLATION_TRIGGER leads to above error.

Solution of the Problem
Before re-installing XDB execute the following after connecting as sysdba:

SQL> connect / as sysdba
SQL> @ $ORACLE_HOME/rdbms/admin/catnoqm.sql
SQL> drop trigger sys.xdb_installation_trigger;
SQL> drop trigger sys.dropped_xdb_instll_trigger;
SQL> drop table dropped_xdb_instll_tab;

How to Deinstall and Reinstall XML Database (XDB) in Oracle 9.2 and 10.2

Things to remember
1. This Deinstall and Reinstall XML Database (XDB) technique is only applicable for Oracle version 9.2 and 10.2g. Don't use it for other Oracle database version like 11g.

2. Before doing Deinstall and Reinstall XML Database (XDB) ensure that whether the XML DB features are currently being utilized. If you are using it and you are experiencing an invalid XML DB repository or installation, you might choose DBPITR point-in-time recovery option to a point prior to when the problem occurred.

3. In addition, the following DB features also use XDB: Oracle Intermedia EXIF metadata, Oracle GEORASTER, EXFSYS, Oracle Locator.

4. You can use the following query in order check the owner of XMLType Tables.

SQL> conn / as sysdba

SQL> SELECT OWNER, TABLE_NAME
FROM DBA_XML_TABLES
where STORAGE_TYPE in('OBJECT-RELATIONAL','BINARY');

5. When you create a binary XMLType the data is stored is a proprietary format on disk that represents a post parse persistence model. In doing this we have the need to store information about how to transverse the XML data without having to parse it again. The data that we store in order to accomplish this is maintained in dictionary type tables in the XDB user schema, not in the user schema of the user that created the table. What this means is that if you are ever directed to remove and reinstall the XDB feature with the catqnoqm.sql and catqm.sql scripts, that you must verify that you do not have any XMLType tables and/or columns that are stored as binary. The removal scripts drop the XDB user and in turn lose this information. As an additional item this section also applies to schema base XMLType tables and/or columns so the code block will check for those as well.

You can use following query in order to check the storage of XMLType columns.

SQL> SELECT OWNER, TABLE_NAME
from DBA_XML_TAB_COLS
where STORAGE_TYPE in('OBJECT-RELATIONAL','BINARY');

XDB Reload Procedures for 10.2g and above
Step 01: Connect to database as sysdba
SQL> conn / as sysdba

Step 02: Shutdown the instance.
SQL> Shutdown Immediate;

Step 03: Start the database in upgrade mode.
SQL> Startup upgrade;

Step 04: Execute xdbrelod.sql
SQL> @ $ORACLE_HOME/rdbms/admin/xdbrelod.sql

Step 05: Shutdown the database.
SQL> Shutdown immediate;

Step 06: Start the database.
SQL> Startup

Step 07: Run urlrp script.
SQL> @?/rdbms/admin/utlrp.sql

You can now check whether your problem is resolved. If not you should either go for DBPITR point-in-time recovery or deinstall and reinstall XML Database procedure.

De-installation of XML Database (XDB) in Oracle 9.2 and 10.2g
The steps here is to remove the XML Database feature (XDB schema) which will completely remove the XDB repository including any user defined information contained within it.
Therefore, if there are any registered schemas or XML Database items created, you will be required to recreate all of your user defined structures, data and re-register all schemas previously registered.

Note that, if you are on database release 10.1.x or 10.2.x the XDB Feature is Mandatory in order to use any of the member functions of the XMLTYPE. This is true even if you are not using the repository , or registered schema aspects of the XDB feature.

1. Shutdown and start the database.
SQL> shutdown immediate
SQL> startup upgrade

2. Connect as sysdba and run the catnoqm.sql script. Maintain a spool of the script so that later you can check output.
SQL> conn / as sysdba
SQL> set echo on
SQL> spool xdb_removal.log
SQL> @$ORACLE_HOME/rdbms/admin/catnoqm.sql

3. Modify the init.ora file or spfile to include the following minimums to prepare the DB for reinstalling the XDB schema:

shared_pool_size =150M
java_pool_size =150M

4. Turn AUTOEXTEND ON for the XDB tablespace.
If you do not want to do this, make sure you target a tablespace with at least .100 MB of free space for non-UTF8 DB and .150 MB of free space for an AL32UTF8 DB.

5. Shutdown the database immediate, and startup the database normal
SQL> shut immediate
SQL> startup
Re-installation XML Database (XDB) in Oracle 9.2 and 10.2g
Step 01: Connect as sysdba and run the catqm.sql script. Maintain a spool of the script so that later you can check output.

The catqm.sql script requires the following parameters be passed to it when run:
a. XDB user password
b. XDB user default tablespace (You can use any tablespace other than system undo and temp. This tablespace has to exist prior to running the script.)
c. XDB user temporary tablespace
Therefore the syntax to run catqm.sql will be:
SQL> catqm.sql a b c

For Example:
SQL> set echo on
SQL> spool xdb_install.log
SQL> @$ORACLE_HOME/rdbms/admin/catqm.sql password XDB TEMP

Step 02: This step is only required if you are running Oracle 9.2 version.
SQL> @$ORACLE_HOME/rdbms/admin/catxdbj.sql

Step 03: Change dispatchers initialization parameter like below.
a. For Non-RAC
dispatchers="(PROTOCOL=TCP) (SERVICE=<SID>XDB)"
b. RAC
instance_name1.dispatchers="(PROTOCOL=TCP) (SERVICE=<SID>XDB)"
instance_name2.dispatchers="(PROTOCOL=TCP) (SERVICE=<SID>XDB)"
etc ...

Instead of instance_name1, instance_name2 use your own Oracle RAC SID.

c.If you are not using the default Listener ensure you have set LOCAL_LISTENER in the (init.ora/spfile).

Step 04: Check for any invalid XDB owned objects:

SQL> select count(*) from dba_objects
where owner='XDB' and status='INVALID';

COUNT(*)
----------
0

Step 05: Check DBA_REGISTRY for XDB status:

SQL> select comp_name, status, version from DBA_REGISTRY where comp_name= 'Oracle XML Database';
The results should indicate the correct version and patch in a valid status.

6. Restart database and listener to enable Oracle XML DB protocol access.

UDE-00018: Data Pump client is incompatible with database version 11.01.00.07.00

Problem Description
Whenever we try to export 11.2.0.1 database using Oracle datapump Client version 11.1.0.6 or 11.1.0.7 it fails with the following errors:

Export: Release 11.1.0.7.0 - 64bit Production on Thursday, 03 march, 2011 16:11:24

Copyright (c) 2003, 2007, Oracle. All rights reserved.
Password:
UDE-00018: Data Pump client is incompatible with database version 11.01.00.07.00

Cause of the Problem
The problem happened due to unpublished Oracle Bug 7176766. If you are trying to connect to an 11.2 database instance with an 11.1.0.6 or 11.1.0.7 Data Pump export client and you encounter the "UDE-00018: Data Pump client is incompatible with database version" error you could be running into this bug.

Solution of the Problem
If you normally see this error then you should look into UDE-00018. Also make sure you have idea about Data pump compatibility. In this scenario we are connecting from 11.1.0.6 or 11.1.0.7 expdp client to 11.2g database and to fix this bug we need to do the following things.

1) Download and review the readme for Patch 7176766.

2) Apply Patch 7176766 in a test environment.

3) Test the issue if it is fixed.

4) If the issue is resolved, please migrate the solution as appropriate to other environments.

Note that, this 7176766 patch is designed to be applied on only the 11.1.0.6 or 11.1.0.7 version in the client part of the code area.

Export fails with ORA-24324 ORA-07445: exception encountered: core dump [ACCESS_VIOLATION]

Problem Description
Database export operation fails with ORA-24324 and ORA-07445.
The alert.log reports the following ORA-07445 error:

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [0xF0183D] [] [] [] []
Current SQL statement for this session:
SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('XMLSCHEMA_T', '7')), 0 FROM
SYS.KU$_XMLSCHEMA_VIEW KU$ WHERE KU$.OWNER_NAME=:SCHEMA1

Oracle export log file shows following errors:

EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00056: ORACLE error 24324 encountered
ORA-24324: service handle not initialized
EXP-00000: Export terminated unsuccessfully

Cause of the Problem
The problem happened due to invalid objects in Oracle database. You can check invalid objects in Oracle by running query,
SQL> select owner, object_name, object_type from dba_objects where status='INVALID';
You can have a look at Invalid Objects Handling in Oracle in order to know details about it.
We can see the following objects are invalid in database.

DBMS_REPCAT_EXP
DBMS_LOGREP_IMP SYS
DBMS_LOGREP_EXP SYS
DBMS_RULE_EXP_UTL

Solution of the Problem
Run catexp.sql as well as utlrp.sql script and try the export operation again.

Data pump export for a schema fails with UDE-00008: operation generated ORACLE error 31623

Problem Description
While doing Oracle data pump export a certain schema exportation fails with UDE-00008 and ORA-31623.
UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2187
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3062

Cause of the Problem
After you get UDE-00008 and ORA-31623 you should enable sql tracing for data pump operation in order to investigate further what happened in the back-end. Enabling the SQL trace of the datapump operation show following trace.

PARSE ERROR #13:len=41 dep=1 uid=5 oct=3 lid=5 tim=2251248049406 err=980
SELECT COUNT(*) FROM SYS_EXPORT_SCHEMA_0
EXEC #14:c=20000,e=123888,p=5,cr=24,cu=0,mis=1,r=0,dep=0,og=1,tim=2251248050269
ERROR #14:err=980 tim=230527800

Which means it is trying to select from existing public synonyms that are pointing to no existing objects (tables).

Solution of the Problem
Step 01:
Identify the synonyms that starts with SYS_EXPORT and belongs to that schema.

SQL> col owner.synonym_name for a40 
col table_owner.table_name for a35 
SELECT owner||'.'||synonym_name "OWNER.SYNONYM_NAME", 
       table_owner||'.'||table_name "TABLE_OWNER.TABLE_NAME" 
  FROM dba_synonyms 
  WHERE synonym_name LIKE 'SYS_EXPORT%';

Step 02:
There shouldn't be any synonyms listed here. If private synonyms exist with this name, drop them and create them with a different name (ensure that applications are also updated if applicable). Syntax to drop a synonym:

DROP PUBLIC SYNONYM synonym_name;
or,

DROP SYNONYM owner.synonym_name;

ORA-31693 ORA-01466 Errors During Datapump Export Using Flashback

Problem Description
When Data pump export (expdp) is performed with FLASHBACK_TIME or FLASHBACK_SCN options the following errors are generated.

$ expdp scott/tiger DIRECTORY=test DUMPFILE=export_datapump.dmp FLASHBACK_TIME="TO_TIMESTAMP(SYSDATE)"


From Export Log File:
---------------------
Export: Release 10.2.0.4.0 - Production on Thursday, 03 march, 2011 10:42:17

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** DIRECTORY=test
DUMPFILE=export_datapump.dmp FLASHBACK_TIME=TO_TIMESTAMP(SYSDATE)
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
ORA-31693: Table data object "SCOTT"."RE$ACTION_IMP_TAB" failed to load/unload and is being skipped due to error:
ORA-01466: unable to read data - table definition has changed
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is: /tmp/export_datapump.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 10:49:11


Cause of the Problem
During datapump export if you hit ORA-31693 ORA-01466 then most probably it is Oracle bug 9110642 specially if you are exporting Scheduler Chain Rules. In this case ORA-01466 is reported when exporting auxiliary table RE$ACTION_IMP_TAB which is created by Data Pump Export in order to store details about the rule sets owned by the exported schema.

This table is created/populated at the beginning of export and dropped at the end of export. If you look at trace file then you will see ksedmp: internal or fatal error.

Solution of the Problem
This is Oracle bug and the fix is not yet available. You can use workaround whenever you hit this bug.

1. Either you have to omit FLASHBACK_TIME or FLASHBACK_SCN options during expdp whenever you want to export Scheduler Chain Rules.

2. You have to use original export/import option.

EXP-00008: ORA-00904: invalid column name during Export

Problem Description
A full database export fails with Oracle error EXP-00008 and ORA-00904.

$ exp user/password full=y file=D:\exp.dmp log=D:\exp.log consistent=y buffer=40960000
EXP-00008: ORACLE error 904 encountered
ORA-00904: invalid column name

Cause of the Problem
This error might happen due to several reasons. Once you hit EXP-00008: ORA-00904 you should immediately trace to investigate further.

1. Connect as sysdba user and run the following command using a SQLPLUS session:

SQL> alter system set events='904 trace name errorstack';

2. Now try to perform the export operation again. When the ORA-00904 occurs, a trace file will be written to the storage location specified by the user_dump_dest initialization parameter. As soon as the ORA-00904 has been returned to your export session, you can abort the export and examine the trace information.


3. Disable event tracing by running the following command:

SQL> alter system set events='904 trace name errorstack off';

4. Examine the trace file:

If the trace file says there is some problem regarding sys schema objects and try to run appropriate script such as catexp.sql or urlrp.sql.

If there is such type of words find in trace file,
ksedmp: internal or fatal error ORA-00904: invalid column name
Current SQL statement for this session:
SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM),
DBMS_JAVA.LONGNAME(SYNTAB), ...

then on systems where the Java Server has been installed the trace file could contain information on a failing select statement on DBMS_JAVA packages. Based on this you might need to run "$ORACLE_HOME/javavm/install/initdbj.sql" in order to create the required packages. If Java is disabled, run "$ORACLE_HOME/javavm/install/rmjvm.sql" to remove Java related objects.

Mount Options for Oracle files when used with NAS devices

In case of RAC

Operating System	Mount options for Binaries	Mount options for Oracle Datafiles	Mount options for CRS Voting Disk and OCR
Sun Solaris *	rw,bg,hard,nointr,rsize=32768, wsize=32768,proto=tcp,noac, vers=3,suid	rw,bg,hard,nointr,rsize=32768, wsize=32768,proto=tcp,noac, forcedirectio, vers=3,suid	rw,bg,hard,nointr,rsize=32768, wsize=32768,proto=tcp,vers=3, noac,forcedirectio
AIX (5L) **	rw,bg,hard,nointr,rsize=32768, wsize=32768,proto=tcp, vers=3,timeo=600	cio,rw,bg,hard,nointr,rsize=32768, wsize=32768,proto=tcp,noac, vers=3,timeo=600	cio,rw,bg,hard,intr,rsize=32768, wsize=32768,tcp,noac, vers=3,timeo=600
HPUX 11.23 ***	rw,bg,vers=3,proto=tcp,noac, hard,nointr,timeo=600, rsize=32768,wsize=32768,suid	rw,bg,vers=3,proto=tcp,noac, forcedirectio,hard,nointr,timeo=600, rsize=32768,wsize=32768,suid	rw,bg,vers=3,proto=tcp,noac, forcedirectio,hard,nointr,timeo=600 ,rsize=32768,wsize=32768,suid
Windows	Not Supported	Not Supported	Not Supported
Linux x86 ****	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp, vers=3, timeo=600, actimeo=0	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600
Linux x86-64 ****	rw,bg,hard,nointr,rsize=32768,  wsize=32768,tcp,vers=3, timeo=600, actimeo=0	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3, timeo=600,actimeo=0
Linux - Itanium	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3, timeo=600, actimeo=0	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,actimeo=0, vers=3,timeo=600	rw,bg,hard,nointr,rsize=32768, wsize=32768,tcp,vers=3, timeo=600,actimeo=0

* NFS mount option "forcedirectio" is required on Solaris platforms when mounting the OCR/CRS files when using Oracle 10.1.0.4 or 10.2.0.2 or later (Oracle unpublished bug 4466428)

** AIX is only supported with NAS on AIX 5.3 TL04 and higher with Oracle 10.2.0.1 and later (NetApp)

*** NAS devices are only supported with HPUX 11.23 or higher ONLY

**** These mount options are for Linux kernels 2.6 and above for older kernels

Note that,dDue to Unpublished bug 5856342, it is necessary to use the following pfile/spfile initialization parameter when using NAS with all versions of RAC on Linux (x86 & X86-64 platforms) until 10.2.0.4. This bug is fixed and included in 10.2.0.4 patchset.
The parameter is,
filesystemio_options = DIRECTIO

In case of Single Instance

Operating System	Mount options for Binaries	Mount options for Oracle Datafiles
Sun Solaris * (8, 9, 10)	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,proto=tcp,suid	rw,bg,hard,rsize=32768,wsize=32768,vers=3,[forcedirectio or llock],nointr,proto=tcp,suid
AIX (5L) **	rw,bg,hard,rsize=32768,wsize=32768,vers=3,intr,timeo=600,proto=tcp	rw,bg,hard,rsize=32768,wsize=32768,vers=3,cio,intr,timeo=600,proto=tcp
HPUX 11.23 ***	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,proto=tcp,suid	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,proto=tcp, suid, forcedirectio
Windows	Not Supported	Not Supported
Linux x86 ****	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp,actime=0*
Linux x86-64 ****	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp,actime=0*
Linux - Itanium	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp	rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,tcp

Data Pump Export Fails With ORA-39125 ORA-19502 ORA-27072 Error: 11: Resource temporarily unavailable

Problem Description
In a RAC database Data Pump export or import operations are intermittently failing with:

ORA-31693: Table data object "owner"."table name" failed to load/unload and is being skipped due to error: 
ORA-31644: unable to position to block number 182645 in dump file 
"dump file name" 
ORA-19502: write error on file "dump file name", blockno 184437 (blocksize=4096) 
ORA-27072: File I/O error 
Linux-x86_64 Error: 11: Resource temporarily unavailable 
Additional information: 4 
Additional information 

In our case data pump directory is placed on OCFS2 volume.

Cause of the Problem
The mount options for the OCFS2 volume do not include the "datavolume" and "nointr".

The "datavolume" option ensures that the Oracle processes opens these files with the o_direct flag.
The "nointr" option ensures that the ios are not interrupted by signals.

These two attributes needed in order to use for Oracle.

Solution of the Problem
Mount the OCFS2 volume with the "datavolume" and "nointr" options.

For example:

# mount -o datavolume,nointr -t ocfs2 /dev/sda1 /dev/mount

EXP-00008 ORA-37002: Oracle OLAP failed to initialize

Problem Description
While performing database export operation it fails with EXP-00008 and ORA-37002 like below.

EXP-00008: ORACLE error 37002 encountered
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP
technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 473
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp

Cause of the Problem
The problem can happen for any of the following two reasons:

i) Oracle OLAP is not installed or not configured properly.
ii) You have upgraded your Oracle database recently and up gradation has bug.

Solution of the Problem
Step 01: Export full database excluding OLAP's Analytic Workspaces. Also take full backup of your database before proceeding further.

Step 02: Remove workspace package from export:

a. sqlplus / as sysdba

b. create table sys.exppkgact$_backup as select * from sys.exppkgact$;
This is nothing but just a backup of the package before deleting it.

c. delete from sys.exppkgact$ where package = 'DBMS_AW_EXP' and schema= 'SYS';

d. commit;

3) Run the FULL export to see whether error occurs.

Export a domain Index fails with ORA-39127 and PLS-0030

Problem Description
While doing full database export ORA-39127 and PLS-0030 error are shown like below.

;;; 
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 4 March, 2011 19:09:13
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory= dexport full=y dumpfile=full.dmp logfile=full.log 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 89.34 MB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type 
DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type 
DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.
GET_DOMAIN_INDEX_METADATA('CHEMSTRUCT_SEARCH_IDX','SYSADMIN','MOLECULESTRUCTSEARCHTYPE','C$IDBSCHXAB',10.02.00.03.00,newblock,0) 
ORA-6550: line 1, column 162:
PLS-306: wrong number or types of arguments in call to 'ODCIINDEXGETMETADATA'
ORA-6550: line 1, column 151:
PL/SQL: Statement ignored
ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 262
ORA-6512: at line 1
ORA-6512: at "SYS.DBMS_METADATA", line 4770
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('DATADICTMOL_SEARCH_IDX','SYSADMIN',
'MOLECULESTRUCTSEARCHTYPE','C$IDBSCHXAB',10.02.00.03.00,newblock,0) 
ORA-6550: line 1, column 163:
PLS-306: wrong number or types of arguments in call to 'ODCIINDEXGETMETADATA'
ORA-6550: line 1, column 152:
PL/SQL: Statement ignored
ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 262
ORA-6512: at line 1
ORA-6512: at "SYS.DBMS_METADATA", line 4770
ORA-39127: unexpected error from call to local_str := SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('CHEMSTRUCT_SIM_IDX','SYSADMIN',
'MOLECULESIMSEARCHTYPE','C$IDBSCHXAB',10.02.00.03.00,newblock,0) 
ORA-6550: line 1, column 159:
PLS-306: wrong number or types of arguments in call to 'ODCIINDEXGETMETADATA'
ORA-6550: line 1, column 148:
PL/SQL: Statement ignored
ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 262
ORA-6512: at line 1
ORA-6512: at "SYS.DBMS_METADATA", line 4770
ORA-39127: unexpected error from call to local_str := 
SYS.DBMS_EXPORT_EXTENSION.
GET_DOMAIN_INDEX_METADATA('DATADICTMOL_SIM_IDX','SYSADMIN','MOLECULESIMSEARCHTYPE','C$IDBSCHXAB',10.02.00.03.00,newblock,0) 
ORA-6550: line 1, column 160:
PLS-306: wrong number or types of arguments in call to 'ODCIINDEXGETMETADATA'
ORA-6550: line 1, column 149:
PL/SQL: Statement ignored
ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 262
ORA-6512: at line 1
ORA-6512: at "SYS.DBMS_METADATA", line 4770
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION

Investigation of the Problem
The implementation type C$IDBSCHXAB.MOLECULESIMSEARCHTYPE shows it calls function ODCIINDEXGETMETADATA with 3 parameters.

SQL> desc C$IDBSCHXAB.MOLECULESIMSEARCHTYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- 
----------------------------
 M_NSEARCHTYPE                                      NUMBER(38)
 M_NOPTIONS                                         NUMBER(38)
 M_STRUCTCOLL                                       C$IDBSCHXAB.STRUCTCOLL
 M_NFETCHCALLS                                      NUMBER(38)
 M_NKEY                                             NUMBER(38)
 M_SZINDEXEDSCHEMA                                  VARCHAR2(128)
 M_SZINDEXEDTABLE                                   VARCHAR2(128)
 M_SZTABLE                                          VARCHAR2(128)
 M_SZCOLUMN                                         VARCHAR2(128)
 M_SZINDEXSCHEMA                                    VARCHAR2(128)
 M_NSEQNO                                           NUMBER(38)
 M_NQUERYHANDLE                                     NUMBER(38)
 M_NSAVEDSTATE                                      NUMBER(38)
 M_NCACHEFLAG                                       NUMBER(38)
 M_NCFNUMBER                                        NUMBER(38)
 M_NNEXTREC                                         NUMBER(38)
 M_SZPARAMS                                         VARCHAR2(200)

METHOD
------
 STATIC FUNCTION ODCIGETINTERFACES RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IFCLIST                        ODCIOBJECTLIST          OUT   


METHOD
------
 STATIC FUNCTION ODCIINDEXGETMETADATA RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 IA                             ODCIINDEXINFO           IN    
 EXPVERSION                     VARCHAR2                IN    
 NEWBLOCK                       PLS_INTEGER             OUT

The interface versions for the affected domain indexes show they are using version 1, what means they would need 3 parameters.

SQL> select indextype_name,interface_version from dba_indextypes
       where owner='C$IDBSCHXAB' order by indextype_name;

INDEXTYPE_NAME                 INTERFACE_VERSION
------------------------------ -----------------
MOLECULE_SEARCH_IDT                            1
MOLECULE_SIM_IDT                               1
REACTION_SEARCH_IDT                            1

Also with original export the involved objects cann't be exported: From the original export logfile,

. exporting bitmap, functional and extensible indexes
EXP-91: Exporting questionable statistics.
EXP-8: ORACLE error 20200 encountered
ORA-20200: The MOLECULE_SEARCH_IDT domain index will not be exported. Please build this again in the import schema
ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 268
ORA-6512: at line 1
EXP-78: Error exporting metadata for index CHEMSTRUCT_SEARCH_IDX. Index creation will be skipped
EXP-8: ORACLE error 20200 encountered
ORA-20200: The MOLECULE_SEARCH_IDT domain index will not be exported. Please build this again in the import schema

Cause of the Problem
The problem happened due to Oracle bug 8307012 while exporting a domain Index.

Solution of the Problem
The problem is remained up to Oracle version 11.2.0.1 and 10.2.0.4 whereas it is fixed in 11.2.0.2 (Server Patch Set).

Expdp fails with ORA-39126, ORA-06502 PL/SQL: numeric or value error: character string buffer too small

Problem Description
While exporting database it fails with ORA-06502 error. From the data pump export logfile,

  Export: Release 11.1.0.7.0 - 64bit Production on Monday, 28 February, 2011 08:13:23
  Copyright (c) 2003, 2007, Oracle. All rights reserved.
  Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  FLASHBACK automatically enabled to preserve database integrity.
  Starting "SYS"."SYS_EXPORT_TABLE_01": /******** SYSDBA parfile=expdp_tables_script.par 
  Estimate in progress using BLOCKS method...
  Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
  Total estimation using BLOCKS method: 138 MB
  Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
  Processing object type TABLE_EXPORT/TABLE/TABLE
  Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
  Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  Processing object type TABLE_EXPORT/TABLE/RLS_POLICY
  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  Processing object type TABLE_EXPORT/TABLE/TRIGGER
  [TRIGGER:"A"."NEW_TERM"] 
  ORA-6502: PL/SQL: numeric or value error: character string buffer too small
  ORA-6512: at "SYS.DBMS_SYS_ERROR", line 95

A similar version of error message can be found as,
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [TRIGGER:"X"."Y_U"]
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 7839

Cause of the Problem
After you get above error you should immediately look for Oracle database alert log as well as trace file if there is generated any. If exporting a functional based index generates ora-06502 whose column clause expression length is more than 4000 then this issue must be Oracle bug 8983880. This bug might come with several variations for example when you try to export METADATA_ONLY then message might be different.

Solution of the Problem
Up to now there is no workaround available to solve the bug. You can search in Oracle support whether there is any one off patch for this bug. However, this issue is resolved in oracle version 11.2.0.2 (Server Patch Set).

Data pump export is hung /horrible slow - Bug 8467825

Problem Description
Oracle data pump export operation appears to be hung or it is horrible slow. For example just performing METADATA_ONLY export it is taking 24 hours but still not completed. Doing Data pump tracing of the full datapump export shows no progress in the log file for many hours with the datapump worker process showing the bulk of the execution time is spent executing the following query:

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('TABLESPACE_T', '7')), 0 ,KU$.NAME ,KU$.NAME ,
'TABLESPACE'
FROM
SYS.KU$_TABLESPACE_VIEW KU$ WHERE NOT EXISTS (SELECT 1 FROM
SYS.KU$NOEXP_TAB A WHERE A.NAME=KU$.NAME AND A.OBJ_TYPE='TABLESPACE') AND
NOT (KU$.BITMAPPED>0 AND KU$.STATUS=2)

Cause of the Problem
The problem happened due to Oracle bug 8467825. Datapump export can be very slow while fetching the data from view KU$_TABLESPACE_VIEW(ku$_file_view).

Solution of the Problem
This bug remained in oracle version 11.2.0.1, 11.1.0.7 and 10.2.0.4.
However it is fixed in following releases.

- 11.2.0.2 (Server Patch Set)
- 11.2.0.1 Patch 4 on Windows Platforms
- 10.2.0.4 Patch 29 on Windows Platforms

Data Pump export hang with INCLUDE=OBJECT_GRANT ORA-39029, ORA-4030

Problem Description
Large numbers of WITH GRANT OPTION object grants can cause Data Pump to run slowly and run out of memory. The data pump runs so slowly while exporting OBJECT_GRANT that it appears to be hung. From the export logfile.

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
 ORA-39014: One or more workers have prematurely exited.
 ORA-39029: worker 1 with process name "DW00" prematurely terminated
 ORA-31671: Worker process DW00 had an unhandled exception.
 ORA-4030: out of process memory when trying to allocate 4032 bytes (kxs-heap-w,kghsseg :kokeismo)
 ORA-06512: at "SYS.KUPW$WORKER", line 1714
 ORA-06512: at line 2

Cause of the Problem
This is Oracle bug 9791589 and it fires during exporting many "WITH GRANT OPTION" grants.
This bug has been superceded by bug 10195109.

Solution of the Problem
You can check whether there is available OPatch available for both 9791589 and 10185319 to fix this problem.
Workaround you can perform data pump export with EXCLUDE=OBJECT_GRANT option.

FULL Export with AUDIT enabled fails with ORA-01406: fetched column value was truncated

Problem Description
Full database export (FULL=Y) fails with the following error messages.

. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
EXP-00008: ORACLE error 1406 encountered
ORA-01406: fetched column value was truncated
EXP-00000: Export terminated unsuccessfully

Cause of the Problem
The problem happened due to Oracle Bug 4693439. This bug fires when initialization parameter of AUDIT_TRAIL is set to DB that is AUDIT_TRAIL=DB and users are audited.

Solution of the Problem
This bug is fixed in
- 10.2.0.2 Patch 3 on Windows Platforms
- 10.2.0.3 (Server Patch Set)
- 11.1.0.6 (Base Release)
So upgrading your Oracle is a solution.
Workaround you disable database auditing by setting AUDIT_TRAIL=NONE and then perform export.
Also you can try schema export instead of doing FULL database export.

Datapump export from standby database fails with ORA-16000

Problem Description
While performing data pump export operation from a read only standby database fails with the
following errors:

ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-2054: transaction 16.38.1709704 in-doubt
ORA-16000: database open for read-only access
ORA-2063: preceding line from DBLINK_HOTSTANDBY
ORA-39097: Data Pump job encountered unexpected error -2054

Cause of the Problem
This issue is with Oracle Datapump when the database is in read-only mode for export. In a word this is Oracle Bug 7331929: DATAPUMP EXPORT FROM STANDBY DATABASE FAILS ORA-16000. This happened when data pump export operation is performed with network_link parameter on a read only physical standby database.

Solution of the Problem
This Oracle bug exist up to Oracle version 11.1.0.7, 10.2.0.4. And it is fixed in

- 11.2.0.1 (Base Release)
- 10.2.0.5 (Server Patch Set)
- 11.1.0.7 Patch 26 on Windows Platforms
- 10.2.0.4 Patch 10 on Windows Platforms

So upgrading Oracle database software is a solution.
Alternatively, as a workaround we can place the standby database in read write mode and can perform data pump export.

Datapump export fails with ORA-39126 or ORA-22813

Problem Description:
Datapump export fails to write large volume data and gives error message ORA-39126 or ORA-22813.

Cause of the Problem:
The error is caused due to oracle bug 6991626 and this bug confirmed in following Oracle version across all platforms.

- 11.1.0.7
- 11.1.0.6
- 10.2.0.4

Solution of the Problem:
From Oracle predefined Error documentation we get the following.

ORA-22813: operand value exceeds system limits
Cause: Object or Collection value was too large. The size of the value might have exceeded 30k in a SORT context, or the size might be too big for available memory.
Action:  Choose another value and retry the operation.

ORA-39126: Worker unexpected fatal error in string [string] string
Cause: An unhandled exception was detected internally within the worker process for the Data Pump job. This is an internal error. Additional information may be supplied.
Action:  If problem persists, contact Oracle Customer Support.

But in reality this error occurred due to Oracle Bug 6991626 and this bug is fixed in the following version.
- 11.2.0.1 (Base Release)
- 11.1.0.7.2 (Patch Set Update)
- 10.2.0.5 (Server Patch Set)
- 10.2.0.4.2 (Patch Set Update)
- 11.1.0.7 Patch 1 on Windows Platforms
- 11.1.0.7 EBusiness Suite R12 Certification Patch Bundle #1
- 11.1.0.6 Patch 11 on Windows Platforms
- 10.2.0.4 Patch 16 on Windows Platforms
So you need to upgrade your Oracle version to any of these version.

Reverse key index with system generated name is not exported

Problem Description
In a table primary key constraint is created with 'using index reverse' clause to create reverse key index. I have taken export of the table and then the DDL generated by export does not include the 'reverse' keyword in the 'alter table..add constraint..using index..'
statement.

Let's see an example.

SQL> connect scott/tiger
    
SQL> CREATE TABLE test_reverse_key (
       A numeric(9) NOT NULL,
       PRIMARY KEY (A)
          USING INDEX TABLESPACE USERS REVERSE  LOGGING);
    
SQL> select index_name,index_type from user_indexes where 
table_name='TEST_REVERSE_KEY';
    
    INDEX_NAME                     INDEX_TYPE
    ------------------------------ ----------
    SYS_C005492                    NORMAL/REV
    
SQL> exp system/manager tables=scott.test_reverse_key
    
SQL> drop table scott.test_reverse_key;
    
SQL> imp system/manager full=y
    
SQL> select index_name,index_type from user_indexes where 
table_name='TEST_REVERSE_KEY';
    
    INDEX_NAME                     INDEX_TYPE
    ------------------------------ ----------
    SYS_C005412                    NORMAL

Cause of the Problem
This is Oracle bug specifically Bug 4898699. This bug is confirmed in Oracle version 9.2.0.7, 10.1.0.5, 10.2.0.2.

Solution of the Problem
This bug is fixed in following updates.
- 9.2.0.8 (Server Patch Set)
- 10.2.0.3 (Server Patch Set)
- 11.1.0.6 (Base Release)
So upgrading your Oracle database to these version is one solution.

Workaround you can Drop the constraint once you import the dump and then re-create it using an ALTER TABLE ADD CONSTRAINT using an index REVERSE clause.

Export fails with ORA-01455 when exporting snapshot logs

Problem Description
When I try to export full database it fails with ORA-01455 error like below.
....
. exporting synonyms
. exporting views
. exporting referential integrity constraints
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting materialized views
EXP-00056: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

Cause of the Problem
This is Oracle bug specifically Oracle Bug 9306119. This bug fires during exporting a snapshot log.

Solution of the Problem
If this bug fires during exporting snapshot logs then there is no known workaround available to bypass it. The only solution is to upgrade the Oracle version to 11.2.0.2 (Server Patch Set) or 11.2.0.1 Patch 6 on Windows Platform as in these version bug is fixed. Alternatively you can ignore the error and do export snapshot logs manually.