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
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.
Model upgrade from 9.2 release to 11g releases is not supported.
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
During the upgrade from 10g to 11g, all Data Mining metadata objects and PL/SQL packages are migrated from
SYS. After the upgrade, when you determine that there is no need to perform a downgrade, set the initialization parameter
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
DMSYS is removed, the
SYS.DBA_REGISTRY view will no longer list Oracle Data Mining as a component.
Go to the Windows Start menu and choose the Oracle home directory.
Choose the Configuration and Migration Tools menu.
On Linux platforms, run the
DBUA utility to upgrade Oracle Database.
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.
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".
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.
SQL>CONNECT / as sysdba; SQL>@ORACLE_HOME\RDBMS\admin\dmeidmsys.sql SQL>EXIT;
TEMPtablespace must already exist in the 11g database. The
DMEIDMSYSscript uses the
SYSAUXtablespaces to create the
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 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.
Important:In Oracle 11g, the
CREATE MINING MODELprivilege 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.
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