10g Upgrade: Failed to load Media Management Library

Error while rman:
RMAN-03009: failure of allocate command on ch1 channel at 09/13/2008 12:42:27
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

Solution:
cd $ORACLE_HOME/lib
ln -s /usr/lib/libnwora.a libobk.a

CATPROC INVALID in DBA_REGISTRY

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
  2    3    4    5    6  ;


COMP_ID                        STATUS      VERSION
------------------------------ ----------- ------------------------------
COMP_NAME
--------------------------------------------------------------------------------
CATALOG                        VALID       9.2.0.7.0
Oracle9i Catalog Views

CATPROC                        INVALID     9.2.0.7.0
Oracle9i Packages and Types

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

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


SQL> exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

PL/SQL procedure successfully completed.

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
  2    3    4    5    6  ;

COMP_ID                        STATUS      VERSION
------------------------------ ----------- ------------------------------
COMP_NAME
--------------------------------------------------------------------------------
CATALOG                        VALID       9.2.0.7.0
Oracle9i Catalog Views

CATPROC                        VALID       9.2.0.7.0
Oracle9i Packages and Types

Force different index usage in stored outline

 update system.ol$hints set hint_text='INDEX_RS_ASC(@"SEL$1" "table_name"@"SEL$1" "index_name")' where hint#=7;

Force Oracle 10g to use 9.2.0 optimizer for specific query only

There is a parameter optimizer_features_enable which by default is set to 10.2.0.x in Oracle 10.2.0.x. Changing this to 9.2.0 cause that all features of 10.2.0 optimizer are disabled. If you need optimizer 9.2.0 to be applied in only one specific query (because there are no possibilities to use hints, playing with statistics also doesn't help, and execution plan from ver 9.2.0 performs much better), you can do this using stored outlines. This is how to do this:

1. alter system set use_stored_outlines = TRUE

2. create outline OUTLN_1 on <your statement>;

3. create private outline OUTLN_1_PRV from OUTLN_1;

4. select * from <schema>.ol$hints where name='OUTLN_1_PRV'

5. Now you have modify your private outline:

a) first of all you need to find in output from point 4 hint# containining OPTIMIZER_FEATURED_ENABLED and change this:
update <schema>.ol$hints set
hint_text='OPTIMIZER_FEATURES_ENABLE(''9.2.0'')'
where ol_name = 'OUTLN_1_PRV' and hint#=x;

b) also you need to reset access path, so need to find (also from output 4) line containing something like FULL, INDEX etc, and delete this:
delete from <schema>.ol$hints where ol_name='OUTLN_1_PRV'
and hint#=x;

6. exec dbms_outln_edit.refresh_private_outline('OUTLN_1_PRV');

7. create or replace outline OUTLN_1 from private OUTLN_1_PRV;