6 Upgrading Oracle Data Mining

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.

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.

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;

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.