6 Upgrading Oracle Data Mining

This chapter explains how to upgrade or downgrade Oracle Data Mining 11g Release 2 (11.2). It contains the following sections:

Data Mining Upgrade Overview

Oracle Data Mining upgrade is fully integrated with the Oracle Database upgrade process. Whether you are upgrading from 11.1 release or from 10g releases, Data Mining models and metadata are upgraded automatically during the upgrade of Oracle Database.

To upgrade a database, you can use Database Upgrade Assistant (DBUA) or you can perform a manual upgrade using export/import utilities.

See:

Oracle Database Upgrade Guide for complete database upgrade instructions

Data Mining Model Upgrade

Upgraded models will continue to work as they did in prior releases. New models that you create in the upgraded environment can make use of the new mining functionality introduced in the new release.

Note:

Models created by the Oracle Data Mining PL/SQL API can be upgraded from 10.1 to 11g.

Models created by the Oracle Data Mining Java API cannot be upgraded from 10.1 to 11g. The 10.1 version of the Java API was no longer supported in Oracle Data Mining 10.2.

Model upgrade from 9.2 release to 11g releases is not supported.

Upgrading from 10g

In Oracle Data Mining 10g, Data Mining metadata and PL/SQL packages are stored in the DMSYS schema. In Oracle Data Mining 11g, DMSYS no longer exists; Data Mining metadata objects are stored in SYS.

During the upgrade from 10g to 11g, all Data Mining metadata objects and PL/SQL packages are migrated from DMSYS to SYS. After the upgrade, when you determine that there is no need to perform a downgrade, set the initialization parameter COMPATIBLE to 11.2 and drop the DMSYS schema and its associated objects from the upgraded database as follows:

SQL> CONNECT / AS sysdba;
SQL> DROP USER dmsys CASCADE;
SQL> DELETE FROM sys.exppkgact$ WHERE schema = 'DMSYS';
SQL> SELECT COUNT(*) FROM dba_synonyms WHERE table_owner = 'DMSYS';

If the result is non-zero rows, create and run a SQL script as follows:

SQL> set head off 
SQL> spool directory_path/drop_dmsys_synonyms.sql 
SQL> SELECT 'Drop public synonym ' ||'"'||SYNONYM_NAME||'";' 
             FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';
SQL> spool off 
SQL> @directory_path/drop_dmsys_synonyms.sql

Once DMSYS is removed, the SYS.DBA_REGISTRY view will no longer list Oracle Data Mining as a component.

After upgrading to 11g, you can no longer switch to the Data Mining Scoring Engine (DMSE). The Scoring Engine does not exist in 11g.

Upgrading from 11.1

If you upgrade from 11.1 release to 11.2, check for the existence of DMSYS. If the database was previously upgraded from 10g, DMSYS may still be present. If DMSYS is still present in the database, you should remove it.

Upgrade Using Database Upgrade Assistant

Oracle Database Upgrade Assistant provides a graphical user interface that guides you interactively through the upgrade process. To start the Upgrade Assistant:

  1. Go to the Windows Start menu and choose the Oracle home directory.

  2. Choose the Configuration and Migration Tools menu.

  3. Launch the Upgrade Assistant.

On Linux platforms, run the DBUA utility to upgrade Oracle Database.

Upgrade Using Export/Import

If you wish, you can use a less automated approach to upgrading Oracle Data Mining. You can export the models created in a previous version of Oracle Database and import them into a new 11g Release 2 (11.2) database.

Export/Import Release 11.1 Data Mining Models

To export models from an 11.1 database to a dump file and import them into an 11.2 database, follow the instructions in "Exporting and Importing Mining Models".

Export/Import Release 10g Data Mining Models

To export models from a 10g database to a dump file, follow the instructions in "Exporting and Importing Mining Models". If any models were created by the 10.1 Java API, do not include them in the export; they are not supported in 11g.

Before importing the models from the dump file, run the DMEIDMSYS script to create the DMSYS schema in the 11g database.

SQL>CONNECT / as sysdba;
SQL>@ORACLE_HOME\RDBMS\admin\dmeidmsys.sql
SQL>EXIT;

Note:

The TEMP tablespace must already exist in the 11g database. The DMEIDMSYS script uses the TEMP and SYSAUX tablespaces to create the DMSYS schema.

To import the dump file into the database:

%ORACLE_HOME\bin\impdp system\<password> 
       dumpfile=<dumpfile_name> 
       directory=<directory_name> 
       logfile=<logfile_name> .....
SQL>CONNECT / as sysdba;
SQL>EXECUTE dmp_sys.upgrade_models('11.0.0');
SQL>ALTER SYSTEM FLUSH SHARED_POOL;
SQL>EXIT;

If you shutdown the database before operating on the upgraded mining models, this will also flush the shared pool.

After the import is complete, you should drop the DMSYS schema from the database.

Post Upgrade Steps

After upgrading the database, check the upgrade log file and the SYS.DBA_REGISTRY view to ensure that the upgrade process completed successfully. Also check the DBA_MINING_MODELS view in the upgraded database. The newly upgraded mining models should be listed in this view.

After you have verified the upgrade and confirmed that there will be no need to downgrade, you should set the initialization parameter COMPATIBLE to 11.2.

Important:

In Oracle 11g, the CREATE MINING MODEL privilege must be granted to Data Mining user accounts. This privilege is required for creating Data Mining models in 11g. Refer to Chapter 4, "Users and Privileges for Data Mining" for more information.

Downgrade Data Mining

Before downgrading the database back to the previous version, ensure that no 11g Release 2 (11.2) mining models were created in the upgraded database. Issue the following SQL statement in SYS to verify:

SQL>SELECT o.name FROM sys.model$ m, sys.obj$ o 
                  WHERE m.obj#=o.obj# AND m.version=2;

If there are any 11g Release 2 (11.2) mining models in the database, you must manually delete them using the DBMS_DATA_MINING.DROP_MODEL routine before downgrading the database. If you do not do this, the database downgrade process will be aborted. See Oracle Database PL/SQL Packages and Types Reference for the calling syntax of DROP_MODEL.