3 Upgrading Oracle Data Mining

This chapter explains how to upgrade or downgrade your database.

Upgrading Oracle Data Mining

Oracle Data Mining upgrade is fully integrated with the upgrade of Oracle Database. In 11g Release 1 (11.1), Data Mining metadata is migrated from DMSYS to SYS during the upgrade of Oracle Database.

Data Mining models are also upgraded during the upgrade of Oracle Database. The upgraded models are available for use in the new upgraded environment. Upgraded models will continue to work as they did in the prior release. All new mining functionality in 11g can be used in the upgraded environment.

The privilege CREATE MINING MODEL is required for creating models in 11g. This privilege should be added to any accounts being upgraded to 11g. Refer to Chapter 4, "Users and Privileges for Data Mining".

Supported Version Upgrades

Oracle Data Mining 10.2, including both metadata and models, can be upgraded to 11g.

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 to 11g is not supported.

Using Oracle Database Upgrade Assistant

You can use Oracle Database Upgrade Assistant to upgrade a database to 11g. During the upgrade, all Data Mining metadata that previously existed in the DMSYS schema is created in SYS. After the upgrade, all Data Mining metadata will reside in SYS and will no longer reside in DMSYS. Data Mining models, which reside in the owner's schema, will also be upgraded to 11g.

On Windows platforms, you can use the Database Upgrade Assistant graphical tool to upgrade to 11g. To start the Upgrade Assistant:

Go to the Windows Start menu and choose the Oracle home directory.
Choose the Configuration and Migration Tools menu.
Launch the Upgrade Assistant.

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

After upgrading, check the upgrade log file and DBA_REGISTRY 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.1. At that point, you can drop the DMSYS schema from the database. Once DMSYS is removed, the DBA_REGISTRY will no longer list Oracle Data Mining as a component.

Note:

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

Exporting and Importing from a Dump File

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

To export your Data Mining models to a dump file, follow the instructions in Chapter 6 on import/export.

Before importing 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 TEMP and SYSAUX to create the DMSYS schema.

To import the dump file into the database:

%ORACLE_HOME/bin/impdp system/password file_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 can drop the DMSYS schema from the database. Once DMSYS is removed, the DBA_REGISTRY will no longer list Oracle Data Mining as a component.

Downgrading Oracle Data Mining

Before downgrading the database back to the previous version, ensure that there are no 11g mining models in the upgraded database. Issue the following SQL statement in SYS to verify this:

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