Oracle Data Mining 组件 说明
一.DataMining 组件 说明
在说明之前，我们先用如下SQL查看一下DB中的组件：
SQL> col comp_id for a15
SQL> col version for a15
SQL> col comp_name for a30
SQL> select comp_id,comp_name,versionfrom dba_registry ;
COMP_ID COMP_NAME VERSION
--------------------------------------------- ---------------
XDB Oracle XML Database 11.2.0.3.0
AMD OLAP Catalog 11.2.0.3.0
EM Oracle Enterprise Manager 11.2.0.3.0
SDO Spatial 11.2.0.3.0
ORDIM Oracle Multimedia 11.2.0.3.0
CONTEXT Oracle Text 11.2.0.3.0
ODM Oracle Data Mining 11.2.0.3.0
EXF Oracle Expression Filter 11.2.0.3.0
RUL Oracle Rules Manager 11.2.0.3.0
OWM Oracle Workspace Manager 11.2.0.3.0
CATALOG Oracle Database Catalog Views 11.2.0.3.0
COMP_ID COMP_NAME VERSION
--------------------------------------------- ---------------
CATPROC Oracle Database Packages and T11.2.0.3.0
ypes
JAVAVM JServer JAVA Virtual Machine 11.2.0.3.0
XML Oracle XDK 11.2.0.3.0
CATJAVA Oracle Database Java Packages 11.2.0.3.0
XOQ Oracle OLAP API 11.2.0.3.0
APS OLAP Analytic Workspace 11.2.0.3.0
17 rows selected.
MOS上说明：
Oracle 8i/9i/10g/11g 组件（Components） 说明
http://www.cndba.cn/Dave/article/1445
Oracle DataMining (ODM) embeds data mining within the Oracle database. ODM algorithmsoperate natively on relational tables or views, thus eliminating the need toextract and transfer data into standalone tools or specialized analyticservers. ODM's integrated architecture results in a simpler, more reliable, andmore efficient data management and analysis environment.
--ODM 是数据库内嵌的数据挖掘组件。ODM 在本地对表或视图进行操作，提取和传输数据到单独的工具或者指定的分析服务上。
ODM综合架构提供了一个简单，可靠，更有效的数据管理和分析环境。
Data miningtasks can run asynchronously and independently of any specific user interfaceas part of standard database processing pipelines and applications. Dataanalysts can mine the data in the database, build models and methodologies, andthen turn those results and methodologies into full-fledged applicationcomponents ready to be deployed in production environments.
--数据挖掘任务可以通过用户置顶的接口，作为标准数据库的进程通道或者应用异步和独立的运行。数据分析可以在数据库中挖掘数据，构建模块和算法，然后把结果和算法存入应用组件。
The benefits ofthe integration with the database cannot be emphasized enough when it comes todeploying models and scoring data in a production environment. ODM allows auser to take advantage of all aspects of Oracle's technology stack as part ofan application. Also, fewer "moving parts" results in a simpler, morereliable, more powerful advanced business intelligence application.
ODM组件相关的用户：
ODM, ODM_MTR : Oracle 9i
DMSYS/ DMSYS : Oracle 10g
SYS : Oracle 11gR1, 11gR2
Oracle DataMining 11g Release 1 (11.1) has a tight integration with Oracle Database. DataMining metadata and PL/SQL packages have been migrated from DMSYS to SYS. TheDMSYS schema no longer exists in Oracle Database 11g Release 1 (11.1) freshinstallations.
--从Oracle 11gR1 开始，ODM 与数据库结合的更紧密，ODM的metadata 和PL/SQL 包也从DMSYS中迁移到了SYS 用户。 从Oracle 11gR1 开始，DMSYS 用户已经不存在，这里是新安装的实例。升级的还是会有。
In 11g freshinstallation 'Oracle Data Mining' will no longer appear in dba_registry view sinceit is not treated as a separate component in 11g, in fact it has beenintegrated into the database itself. However if you have upgraded yourdatabase from an earlier version to 11g, you may see this component indba_registry. This is because of the existence of DMSYS schema, which is notused in 11g and all the objects of DMSYS are shifted to SYS during the upgradeprocess. So once you drop DMSYS schema, 'Oracle Data Mining' willdisappear from the dba_registry.
--从Oracle 11g开始，对于新安装的数据库，Oracle DataMining 将不会显示在dba_registry. 因为ODM 不在作为一个数据库的组件而存在，事实上它已经整合到了数据里。
但是，如果是从低版本升级到11g的，那么我们还是可以从dba_registry中看到， 这是因为DMSYS 用户已经存在，但是其不被使用，所有DMSYS 的对象，在升级的过程中都迁移到了SYS 用户。 所以当我们drop 掉DMSYS 用户之后，在dba_registry中就不能看到ODM的信息了。
我这里在前面的查询中可以看到ODM 的信息，是因为我这里就是从Oracle 10g升级到Oracle 11g的。
二．ODM 卸载
MOS：
Note.297551.1 HowTo Remove the Data Mining Option from the Database
2.1 For Database versions 9i Release 2 (9.2.x)and 10g Release 1 (10.1.x).
TheDBA should be able to deinstall Data Mining from the 'Deinstall' icon onthe OUI (Oracle Universal Installer) page. This is a supported way.
If OUI does not work for somereasons, you may do the following to deinstall ODM :
1. Start SQLPLUS and connect with user sysas sysdba
2. Remove ODM repositories from thedatabase :
2.1.1 For Database release 9i Release 2 (9.2.x).
Drop ODM, ODM_MTR schemasusing the command:
SQL> DROP USER ODM_MTR CASCADE;
SQL> DROP USER ODM CASCADE;
Drop ODM public synonyms usingthe commands:
SQL> set hea off
SQL> spool /path/drop_odm_synonyms.sql
SQL> select 'Drop public synonym ' || SYNONYM_NAME || ' ;' fromDBA_SYNONYMS where TABLE_OWNER = 'ODM';
SQL> spool off
SQL> @/path/drop_odm_synonyms.sql
The synonyms names may needto be in double quotes in order for the script to run properly.
Note: 264 synonyms should be selected anddropped.
2.1.2 ForDatabase release 10g Release 1 (10.1.x)
Drop DMSYS schema using the command:
SQL> DROP USER DMSYS CASCADE;
Drop DMSYS public synonyms using thecommands:
SQL> set hea off
SQL> spool /path/drop_dmsys_synonyms.sql
SQL> select 'Drop public synonym "' || SYNONYM_NAME || '" ;' fromDBA_SYNONYMS where TABLE_OWNER = 'DMSYS';
SQL> spool off
SQL> @/path/drop_dmsys_synonyms.sql
Note: 568 synonyms should be selected anddropped.
Followed by:
SQL> DELETE FROM exppkgact$ WHERESCHEMA='DMSYS';
2.1.3. Update theDBA registry :
Before updatingthe DBA registry, first make sure Data Mining is registered in your database byrunning the following:
SQL> connect / as sysdba;
SQL> select comp_id, version, status from dba_registry;
If ODM (DataMining) is returned by the above query than execute the following toremove Data Mining from the DBA registry:
SQL> exec dbms_registry.removed('ODM');
For UNIX Platform thefollowing steps allow to remake Oracle executable without DM libraries
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
The sqlplusbanner should no longer display the Data Mining option and v$option should show'Oracle Data Mining' as false on a Unix system based install.
Pleasenote: It is possible synonyms may exist for Data Mining objects. Ifany exist, remove them to complete the removal.
2.2 For Database version 10g Release 2(10.2.x).
With DatabaseRelease 10g Release 2, the only supported way to remove the Data Mining optionis to use the Oracle Universal Installer.
--从Oracle 10gR2 开始只能从OUI中删除ODM组件。
The steps would be :
(1) Start The Oracle UniversalInstaller.
(2) In Installer , click on the"Deinstall" Button.
(3) Find you RDBMS home and expandit.
(4) Under it you will find 'OracleDatabase 10g 10.2.0.x' . Expand it.
(5) Under it you will find anotherfolder also called Oracle Database 10g 10.2.0.1'. Expand it.
(6) In the bottom of this tree youwill find 'Oracle Data Mining RDBMS Files 10.2.0.1'.
This is what youneed to remove.
This Data Mininguninstall process should relink oracle executable with dm_off target, whichshould be reflected in sys.v$option view and SQLPlus banner.
--在OUI的卸载进程会relink oracle with dm_off.
Note : On someplatform ( e.g: HP UX) you may notice that after selecting the 'Oracle DataMining RDBMS Files 10.2.0.1' and pressing OK you will be asked if you're sureyou want to remove basically everything, including "Oracle Database 10g"itself.
If this is the case do not proceed that waybut try this way :
--在一些平台下如果卸载进程不能relink，那么需要手工处理，如下：
（1）On unix and linux platform:
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
（2）On all platform:
Update thedatabase registry to remove the Data Mining component from it by performing thefollowing :
(1) Start SQL+ and log as SYSDBA.
(2) Execute this :
SQL> exec dbms_registry.removed('ODM');
The sqlplusbanner should no longer display the Data Mining option and v$option should show'Oracle Data Mining' as false on a Unix system based install.
It is notrecommended to remove the Data Mining schema (DMSYS) due to its dependencieswith the database component Oracle Text. This is documented in Note378159.1 Text breaks if Data Mining is deinstalled.
2.3 For Database version 11g Release 1(11.1.x).
With 11g, OracleData Mining is installed as part of the database installation. Data Miningis now part of Oracle binary and SYS metadata.
--从Oracle 11g开始，ODM 作为数据库的一部分来进行安装，而不是作为组件。
The Data Mining option cannot be removedbut it can be disabled:
--因为在11g中ODM，不是组件，所以我们只能禁用，不能卸载：
2.3.1 On Linux/Unix platform as below:
1. Shutdown database
2. cd $ORACLE_HOME/rdbms/lib
3. make -f ins_rdbms.mk dm_off
4. make -f ins_rdbms.mk ioracle
5. bring database up
2.3.2 On Windows platform as below:
Use the removeoption from the Universal Installer and select the Data Mining under EnterpriseEdition.
To verify the option have been disabled:
--验证禁用情况：
(1) Start sqlplus
(2) Connect as DBA user
(3) Query the sys.v$option using :
SQL> Selectparameter, value from v$option where parameter ='Data Mining';
If the returnedvalue for column "value" is FALSE, then the option have beendisabled.
2.4 For Database version 11g Release 2 (11.2.x).
On both Unix andWindows we use the the chopt tool (available in 11.2) to disable Oracle DataMining in the database.
--从Oracle 11gR2 开始，可以使用chopt工具来禁用ODM 组件。
2.4.1 On Linux/Unix platform as below:
$ cd $ORACLE_HOME/bin
$ srvctl stop database -d myDb
$ chopt disable dm
$ srvctl start database -d myDb
2.4.2 On Windows platform as below:
1. Shut down the database with srvctl orSQL*Plus:
srvctl stop database -d myDb
2. Stop the database service, OracleServiceSID, using the Services program inControl Panel.
3. Run the following commands:
cd %ORACLE_HOME%/bin
chopt disable dm
4. Start the database service, OracleServiceSID, using the Services program inControl Panel.
5. Start up the database:
srvctl start database -d myDb
三． ODM 安装
MOS 文档如下：
Note.420791.1 HowTo Manually Install Data Mining
How To Manually Install Data Mining InOracle 11g? [ID 818314.1]
3.1 For RDBMS 10.1.x
1. Set ORACLE_HOME and ORACLE_SID.
2. START SQLPlus and connect with SYS user as SYSDBA.
3. Execute the following commands:
Unix - Linux
SQL> run$ORACLE_HOME/dm/admin/dminst.sql SYSAUX TEMP
SQL> run $ORACLE_HOME/dm/admin/odmpatch.sql
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql
Windows
SQL> start%ORACLE_HOME%\dm\admin\dminst.sql SYSAUX TEMP
SQL> start %ORACLE_HOME%\dm\admin\odmpatch.sql
SQL> start %ORACLE_HOME%\rdbms\admin\utlrp.sql
4. Ensure 'Oracle Data Mining' is at Valid status in dba_registry by executingthe following query:
SQL> select COMP_NAME,VERSION,STATUS from dba_registry whereCOMP_NAME='Oracle Data Mining';
3.2 For RDBMS 10.2.x
1. Set ORACLE_HOME and ORACLE_SID.
2. START SQLPlus and connect with SYS user as SYSDBA.
3. Execute the following commands:
Unix - Linux
SQL> run$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP
SQL> run $ORACLE_HOME/rdbms/admin/odmpatch.sql
SQL> run $ORACLE_HOME/rdbms/admin/utlrp.sql
Windows
SQL> start %ORACLE_HOME%\rdbms\admin\dminst.sql SYSAUX TEMP
SQL> start %ORACLE_HOME%\rdbms\admin\odmpatch.sql
SQL> start %ORACLE_HOME%\rdbms\admin\utlrp.sql
4. Ensure 'Oracle Data Mining' is at Valid status in dba_registry by executingthe following query:
SQL> select COMP_NAME,VERSION,STATUS from dba_registry whereCOMP_NAME='Oracle Data Mining';
3.3 For RDBMS 11.1.x and 11.2.x
No DMSYS Schema
Oracle DataMining 11g Release 1 (11.1) has a tight integration with Oracle Database.Data Mining metadata and PL/SQL packages have been migrated from DMSYS to SYS. The DMSYS schema no longer exists in OracleDatabase 11g Release 1 (11.1) fresh installations....'
从Oracle 11g开始，ODM 已经整个到数据里里面，不在作为组件安装，以前版本中的DMSYS 用户也不存在，其对象也迁移到SYS 用户下。
If the databasehas been upgraded from earlier version, then DMSYS schema could be there, inwhich case 'DataMining Adminstration Guide' should be followed the relevant steps tomigrate data to SYS schema and then drop DMSYS schema.
--如果是从以前版本升级到11g，那么DMSYS用户还会存在。
There is nolonger an 'odm' directory in the ORACLE_HOME, and no entry for Data Mining inthe DBA_REGISTRY but there is a value in V$OPTION:
SQL> col value format a30
SQL> col parameter format a30
SQL> select * from v$option where PARAMETER = 'Data Mining';
PARAMETER VALUE
------------------------------ ------------------------------
DataMining TRUE
If the abovereturns false, then Data Mining files need to be installed and the Oracleexecutable linked with Data Mining in the Oracle Home. In 11.2 the followingcommand can be used for both Unix/Linux and Windows to "enable" DataMining in the Oracle Home:
--如果以上返回False，那么ODM 文件需要安装，并link DM 到Oracle Home。
在Oracle 11gR2 下，可以使用如下命令：
chopt enable dm
For 11.1 please go to the Oracle UniversalInstaller and install Data Mining files.
--在Oracle 11gR1中可以使用OUI来安装ODM。
If there is aneed to install Data Mining manually into an Oracle Database, please performthe following steps:
--手工安装ODM 步骤如下：
1) Go to the ORACLE_HOME/rdbms/admin directory of the RDBMS instance where youwant to install Data Mining:
2) Ensure ORACLE_HOME, ORACLE_SID are set for the upgraded DB env.
3) Login as SYS and run from SQL*Plus:
SQL>@catodm.sql
SQL>@dbmsodm.sql
SQL>@prvtodm.plb
It is always good to collect the output in a spool file for possible analysisby Support.
四．ODM 状态问题
Note.235486.1 Howto check that the Oracle Data Mining (ODM) Monitor is running
Note.237348.1 TheOracle Data Mining Catalog has a LOADED status instead of a VALID Status
当我们从dba_registry 中查看ODM的状态是LOADED。如：
SQL>select comp_name, version, statusfrom dba_registry where comp_name like '%
Data Mining';
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle Data Mining LOADED 9.2.0.1.0
The ODM catalogmay be valid but as some ODM patchset releases are not part of the RDBMSrelease, the ODM catalog is flagged LOADED.
这种情况下ODM 可能是有效的，但是一些ODM的patchset 不是RDBMS的一部分，所以这里的ODM 显示为LOADED。
解决方法：
1) Check if there are anyinvalid objects in the ODM schema.
--检查是否有无效对象：
a) Connect to SQL*Plus as the ODM user andexecute the following SQL query :
SQL> select object_name, object_type from user_objects where status='INVALID';
This query should return 0.
b) If objects are invalid, recompile allindividual objects.
Use one of the following commands depending on the object type:
SQL> alter package <package_name>compile; (package specification)
SQL> alter package <package_name>compile body; (package body)
SQL> alter view <view_name> compile; (view)
SQL> alter java class"<object_name>" resolve; (java class)
Ifan object compiles with warnings, use either of the following commands to see the errors that caused thewarnings:
SQL> show errors
or
SQL> select * from user_errors where name = '<OBJECT_NAME>'
2) Update the registry when all the objects are valid in the ODM schema.
Note : in the following examples, the ODM patchset release version is 9.2.0.3.0.
a) Check the ODM patchsetrelease version :
Connect to SQL*Plus as the ODM user and execute the following SQL query:
SQL> select value from odm_configuration where name = 'ProductBanner';
Theoutput indicates the ODM patchset release version.
VALUE
----------------------------------------------------------------------------
Oracle9i Data Mining (ODM Schema) Version 9.2.0.3.0 - PRODUCTION
b) Update the registry :
SQL> connect / as sysdba
SQL> execute sys.dbms_registry.loaded('ODM','9.2.0.3.0','Oracle Data Mining- Production');
SQL> execute sys.dbms_registry.valid('ODM');
3) Verify that the ODMCatalog is VALID now.
SQL> select comp_name, version, statusfrom dba_registry where comp_name like '%Data Mining';
This query should return :
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle Data Mining VALID 9.2.0.3.0
-------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
QQ:492913789
Email:ahdba@qq.com
Blog: http://www.cndba.cn/dave
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823
DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940