====== Optionen in der Datenbank deinstallieren 10g/11g ======


Welche Optionen sind in der Datenbank installiert:
<code sql>
column comp_name format a40
column status format a8
column version format a12
column schema format a12



select comp_name
     , status 
     , version
     , schema
from dba_registry
/

column parameter format a30
column value format a30


select * from v$option
/
 
</code>

Wo finde ich mehr Informationen:

Oracle Support Portal:

  * Information On Installed Database Components and Schemas [ID 472937.1]
  * How to Check and Enable/Disable Oracle Binary Options [ID 948061.1]


===Ab 11g R2 "chopt disable" beachten!===

siehe 
  * http://shrikantrao.wordpress.com/2011/12/29/oracle-11-2-new-feature-chopt-utility/



===== Perfstat User =====

<code sql>
@?/rdbms/admin/spdrop 
</code>

===== Enterprise Manager =====

Zuvor sys und sysman Passwort ermitteln oder neu setzen.

1. Versuch mit emca
<code bash>
emca -deconfig dbcontrol db -repos drop
</code>

2.Versuch mit SQL
<code sql>
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
EXEC sysman.setEMUserContext('',5);
REVOKE dba FROM sysman;
DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN'
;
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
END IF;
END LOOP;
END;
/
DROP USER mgmt_view CASCADE;
DROP ROLE mgmt_user;
DROP USER sysman CASCADE;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
</code>

Quellen:

  * Note.278100.1 Ext/Pub How to Recreate the DB Control Repository 

===== Rules Manager =====
<code sql>
@?/rdbms/admin/catnorul.sql
</code>

===== Expression Filter =====
<code sql>
@?/rdbms/admin/catnoexf.sql
</code>

Deinstallation vom Expression Filter löscht automatisch auch den Rules Manager!

siehe auch ow To Install/Uninstall Expression Filter Feature or EXFSYS schema along with Rules Manager (Doc ID 258618.1)
===== Oracle Workspace Manager =====
<code sql>
@?/rdbms/admin/owmuinst.plb
</code>


===== OLAP =====

OLAP Catalog und Oracle OLAP API / OLAP Analytic Workspace

<code sql>
@?/olap/admin/catnoamd.sql
@?/olap/admin/olapidrp.plb
@?/olap/admin/catnoaps.sql
@?/olap/admin/catnoxoq.sql
@?/olap/admin/cwm2drop.sql

drop public synonym OlapFactView;
drop public synonym OlapDimView;
drop public synonym DBMS_ODM;

#EXPDP datapump handler table handlers entfernen für OLAP data:
select * from sys.exppkgact$ where package in('DBMS_AW_EXP','DBMS_CUBE_EXP') and schema= 'SYS';

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

commit;

#Datenbank durchstarten

startup force

</code>

Option aus DB Kernel entfernen ( nur wenn man das nie wieder installieren möchte!)
<code>
# DB Stoppen
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk olap_off
make -f ins_rdbms.mk ioracle
# DB Starten
</code>

Falls folgende Fehler bei einem nächsten Export mit expdp auftritt, wurde die SYS.EXPPKGACT$ nicht komplett angepasst:
<code>
BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1245
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_CUBE_EXP' must be declared
</code>

siehe: EXPDP: PLS-201 identifier 'SYS.DBMS_CUBE_EXP' must be declared (Doc ID 1328829.1)

=== Quellen ===
  * How To Remove the Oracle OLAP API Objects From 9i and 11g Databases [ID 278111.1]
  * How To Remove The OLAP Option In 10g And 11g [ID 332351.1]


===== Spatial =====
Prüfen ob Spatial im Einsatz ist:
<code sql>

connect / as sysdba
select owner,index_name from dba_indexes where ityp_name = 'SPATIAL_INDEX'; 

set pages 200
column owner for a20
column table_name for a30
column column_name for a25

select owner
    , table_name
    , column_name
 from dba_tab_columns
where data_type = 'SDO_GEOMETRY'
  and owner != 'MDSYS'
order by 1,2,3; 

</code>

MDSYS User löschen:
<code sql>
drop user mdsys cascade;

</code>

Falls Fehler ORA-14452 attempt to drop .. index on temporary table already => "startup force" der Datenbank und nochmals versuchen.



Alte Synonyme löschen:
<code sql>
set heading off
set feedback off

spool delete_syn.sql
select 'drop public synonym "' || synonym_name || '";' from dba_synonyms where table_owner='MDSYS'; 
spool off

set heading on
set feedback on

#alle löschen
@delete_syn.sql

</code>

Weitere Spatial User löschen:

<code sql>

drop user mddata cascade;

-- Only created as of release 11g:
drop user spatial_csw_admin_usr cascade;
drop user spatial_wfs_admin_usr cascade; 

</code>

Spatial bleibt aber evlt. weiterhin die der V$OPTION , siehe Note:273573.1!

Pürfen und bei Bedarf aus der Registry entfernen:
<code sql>
sqlplus / as sysdba

sql>exec dbms_registry.removed('Spatial');

</code>

=== Quellen ===
  * Steps for Manual De-installation of Oracle Spatial [ID 179472.1]
  * How To Deinstall Oracle Spatial Keeping Oracle Locator [ID 1070647.1]
===== Oracle interMedia =====

10gR2:
<code sql>
@?/ord/im/admin/imdinst.sql
-- alle von Oracle interMedia abhängigen Objekte löschen
@?/ord/im/admin/imdtyp.sql

startup force

</code>

11gR2:
Nach dem Upgrade können so auch die User ORDPLUGINS ,ORDSYS,SI_INFORMTN_SCHEMA entfernt werden:
<code sql>
@?/rdbms/admin/catcmprm.sql ORDIM
</code>

=== Quellen ===
für 11g
  * Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? [ID 337415.1]
  * http://docs.oracle.com/cd/E16338_01/appdev.112/e10777/ap_instl_upgrd.htm#CHDFEABI

===== Oracle Data Mining =====

10g auf Unix:
<code bash>
#Datenbank zuvor stoppen
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dm_off
make -f ins_rdbms.mk ioracle
#Datenbank wieder starten
</code>

Tipp Solaris: make mit /usr/ccs/bin/make mit vollen Pfad verwenden!

DB Registry ändern:
<code sql>
exec dbms_registry.removed('ODM');
</code>

Objekte nach der Migration in 11g entfernen

<code sql>
DROP USER DMSYS CASCADE;
DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA = 'DMSYS';
commit;
SELECT COUNT(*) FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';
SELECT 'Drop public synonym ' ||'"'||SYNONYM_NAME||'";'   FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';


</code>

=== Mögliche Probleme ===

Fehler beim nächsten Datapump Export:
<code sql>

>>> ORA-31642: the following SQL statement fails: 
BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.04.00'); END;
ORA-06550: line 1, column 8:
PLS-00201: identifier 'DMSYS.DBMS_DM_MODEL_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored
</code>

Lösung => Database Data Pump Export fails with PLS-00201 identifier DMSYS.DBMS_MODEL_EXP must be declared (Doc ID 304449.1)
<code sql>
sqlplus / as sysdba

select count(*) from xppkgact$ WHERE schema='DMSYS'; 

-- >> 0

DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS'; 
commit;

</code>



=== Quellen ===
  * How To Remove the Data Mining Option from the Database [ID 297551.1]
  * How To Remove DMSYS Schema In 11G [ID 1497250.1]

===== Oracle TEXT =====

<code sql>
@?/ctx/admin/catnoctx.sql
drop procedure sys.validate_context;
</code>

=== Quellen ===
  * 10g R2 - Manual Installation, Deinstallation of Oracle Text 10gR2 [ID 979705.1]
  * 11g R2 - Manual installation, deinstallation and verification of Oracle Text 11gR2 [ID 970473.1]

Bei Fehlern mit XDB\\
Da XDB auch Oracle Text verwendet kann nach dem Entfernen von Oracle Text es zu Fehlern in XDB kommen.

siehe : XDB.DBMS_XDBT Invalid And Compilation Errors With PLS-00201 identifier CTXSYS.CTX_OUTPUT Must Be Declared [ID 296869.1]

Lösung:
<code sql>

drop package XDB.dbms_xdbt;
alter procedure xdb.XDB_DATASTORE_PROC compile 

execute dbms_regxdb.validatexdb;

</code>


===== Real Application Testing=====

Zuerst die DB stoppen


<code bash>
cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rat_off ioracle

</code>


===== Partitioning =====


Support Portal:

  * How to enable Partitioning in Oracle Database Enterprise Edition 11.2 on Windows [ID 1188238.1]


===== Oracle Database Vault deinstallieren=====

Zuerst die DB stoppen

Unter Linux:

<code bash>
cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk dv_off ioracle

</code>


Unter Windows (Powershell Beispiel!):
<code powershell>

cd $ENV:ORACLE_HOME\bin

move oradv11.dll oradv11.dll.orig 


#search service
Get-Service -Name "OracleService*"

#stop the service in a administrativen Session
Stop-Service -name OracleServiceGPI

#restart the service
Start-Service -name OracleServiceGPI

</code>


Restart the Database

Wurde Data Vault eingesetzt siehe die Support Node für die weiteren notwendigen Schritte.

See also:
  * How to uninstall Database Vault from a 11g DataGuard environment (Doc ID 1379916.1)
==== Quellen ====

Support:
  * How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade (Doc ID 753041.1)