Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the DataMining component in oracle. I hope you will get something after reading this article. Let's discuss it together.

I. DataMining component description

Before we explain, let's take a look at the components in DB with the following SQL:

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.

On MOS, it says:

Oracle 8i/9i/10g/11g component (Components) description

Http://blog.csdn.net/tianlesoftware/article/details/5937382

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 is a data mining component embedded in the database. ODM operates on tables or views locally, extracting and transferring data to separate tools or specified analysis services.

ODM integrated architecture provides a simple, reliable and more effective data management and analysis environment.

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.

Data mining tasks can be used as process channels for standard databases or applications can be run asynchronously and independently through the user-set interface. Data analysis can mine data in the database, build modules and algorithms, and then store the results and algorithms into application components.

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.

Users related to the ODM component:

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.

Starting with Oracle 11gR1, ODM is more closely integrated with the database, and ODM's metadata and PL/SQL packages have been migrated from DMSYS to SYS users. Starting with Oracle 11gR1, the DMSYS user no longer exists, and here is the newly installed instance. There will still be upgrades.

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.

Starting with Oracle 11g, for newly installed databases, Oracle DataMining will not be displayed in dba_registry. Because ODM no longer exists as a component of a database, in fact it has been integrated into the data.

However, if you upgrade from a lower version to 11g, then we can still see from dba_registry that this is because DMSYS users already exist, but they are not used, and all DMSYS objects are migrated to SYS users during the upgrade process. So when we drop DMSYS users, we can't see ODM information in dba_registry.

I can see the ODM information here in the previous query because I upgraded from Oracle 10g to Oracle 11g.

II. ODM uninstall

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 DataMining 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.

-- starting with Oracle 10gR2, you can only delete ODM components from OUI.

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.

-- the uninstall process in OUI will 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:

-- on some platforms, if the uninstall process cannot relink, it needs to be handled manually, as follows:

(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.

Starting with Oracle 11g, ODM is installed as part of the database, not as a component.

The Data Mining option cannot be removedbut it can be disabled:

-- because ODM is not a component in 11g, we can only disable it, not uninstall it:

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:

-- verify that it is 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.

Starting with Oracle 11gR2, you can use the chopt tool to disable the ODM component.

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

III. ODM installation

The MOS documentation is as follows:

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.xNo 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....'

Since Oracle 11g, ODM has been integrated into the data and is no longer installed as a component, the DMSYS user in the previous version does not exist, and its objects are migrated to SYS users.

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.

If you are upgrading from a previous version to 11g, then DMSYS users will still exist.

There is nolonger an 'odm' directory inthe 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:

If the above returns False, then the ODM file needs to be installed and link DM to Oracle Home.

Under Oracle 11gR2, you can use the following command:

Chopt enable dm

For 11.1 please go to the Oracle UniversalInstaller and install Data Mining files.

-- you can use OUI to install ODM in Oracle 11gR1.

If there is aneed to install Data Mining manually into an Oracle Database, please performthe following steps:

-- the steps for manually installing ODM are as follows:

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.

IV. ODM status problem

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

When we look at the status of ODM from dba_registry, it is LOADED. Such as:

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 may be valid in this case, but some ODM's patchset is not part of RDBMS, so the ODM here is shown as LOADED.

Solution:

1) Check if there are anyinvalid objects in the ODM schema.

-- check if there are any invalid objects:

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 compile; (package specification)

SQL > alter package compile body; (package body)

SQL > alter view compile; (view)

SQL > alter java class "" 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 =''

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

After reading this article, I believe you have a certain understanding of "how to use DataMining components in oracle". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.