5 Managing the Oracle Data Miner Repository
- About Oracle Data Miner Administration Scripts
Oracle Data Miner includes a set of Structured Query Language (SQL) scripts for installing and managing the repository. - Setting the Path to Oracle Data Miner Scripts
You can set the path to Oracle Data Miner scripts using SQL*Plus or SQL Developer Websheet. - Determining the Status and Version of the Repository
The version of the repository must be compatible with the version of the Oracle Data Miner client. If the client and server versions are not compatible, then the client cannot connect to the server. - Backing Up and Restoring the Repository
Before upgrading the Oracle Data Miner repository or performing a database upgrade, you should perform a full backup of Oracle Data Miner, includingODMRSYS
and the Oracle Data Miner user schemas. - Migrating the Repository
This section contains topics related to repository migration. - Dropping the Repository
ThedropRepositoryAndUserObjects
script drops the Oracle Data Miner repository and related objects in the users' schemas.
5.1 About Oracle Data Miner Administration Scripts
Oracle Data Miner includes a set of Structured Query Language (SQL) scripts for installing and managing the repository.
The SQL scripts are installed with SQL Developer in the following directory:
SQL_Developer_Home\sqldeveloper\dataminer\scripts
You can run the SQL scripts in SQL*Plus or in SQL Developer Worksheet. All the Oracle Data Miner scripts must be run with DBA privilege.
Note:
Many of the Oracle Data Miner scripts are integrated with SQL Developer, enabling access to some administrative functions through the Data Graphical User Interface.
Parent topic: Managing the Oracle Data Miner Repository
5.2 Setting the Path to Oracle Data Miner Scripts
You can set the path to Oracle Data Miner scripts using SQL*Plus or SQL Developer Websheet.
To set the default search path for scripts:
-
SQL*Plus: Start SQL*Plus from the
scripts
directory.SQL_Developer_Home\sqldeveloper\dataminer\scripts
-
SQL Developer Worksheet: Set the default search path to the
scripts
directory in the Worksheet properties.Also in SQL Developer Worksheet properties, you must change the maximum number of rows to print in a script to 500000.
Parent topic: Managing the Oracle Data Miner Repository
5.3 Determining the Status and Version of the Repository
The version of the repository must be compatible with the version of the Oracle Data Miner client. If the client and server versions are not compatible, then the client cannot connect to the server.
The following query returns the repository version and status:
set echo on;
-- value of VERSION and REPOSITORY_STATUS
SELECT property_name, property_str_value
FROM ODMRSYS.ODMR$REPOSITORY_PROPERTIES
WHERE property_name IN ('VERSION','REPOSITORY_STATUS', 'WF_VERSION');
PROPERTY_NAME PROPERTY_STR_VALUE
------------------------------ ------------------------------
REPOSITORY_STATUS LOADED
VERSION 12.1.0.2.3
WF_VERSION 12.1.0.2.3
The Oracle Data Miner repository has two values for status: NOT_LOADED
and LOADED
.The status NOT_LOADED
, usually indicates that the Repository is being upgraded to support a new version of SQL Developer or a new patch release. When the upgrade completes, then the status is LOADED
.
Parent topic: Managing the Oracle Data Miner Repository
5.4 Backing Up and Restoring the Repository
Before upgrading the Oracle Data Miner repository or performing a database upgrade, you should perform a full backup of Oracle Data Miner, including ODMRSYS
and the Oracle Data Miner user schemas.
Oracle Data Miner also provides scripts for backing up the workflow metadata in ODMRSYS
without including the user schemas.
- Full Backup and Restore
For Oracle Databases 11.2.0.4 and later, you can perform a full backup and restore of the Oracle Data Miner repository and user schema independently of a full database backup. - Workflow Only Backup
Oracle Data Miner provides a script for backing up the workflow metadata in the repository without including the objects in the users' schemas that are generated by the workflows. - Workflow Only Restore
You can restore workflows from the backup table created bycreatexmlworkflowsbackup2.
- Workflow Only Restore Examples
This topic provides examples on selective workflow restore and full workflow restore.
Parent topic: Managing the Oracle Data Miner Repository
5.4.1 Full Backup and Restore
For Oracle Databases 11.2.0.4 and later, you can perform a full backup and restore of the Oracle Data Miner repository and user schema independently of a full database backup.
- Full Backup and Restore in Database 11.2.0.4 and Later
In Oracle Database 11.2.0.4 and later, the XML storage in the Oracle Data Miner repository is binary. In these databases, you can use Oracle Data Pump to back up and restoreODMRSYS
and the user schemas independently of a full backup and restore of the database.
Parent topic: Backing Up and Restoring the Repository
5.4.1.1 Full Backup and Restore in Database 11.2.0.4 and Later
In Oracle Database 11.2.0.4 and later, the XML storage in the Oracle Data Miner repository is binary. In these databases, you can use Oracle Data Pump to back up and restore ODMRSYS
and the user schemas independently of a full backup and restore of the database.
Using Oracle Data Pump, you can back up and restore individual schemas. Alternatively you can back up and restore Oracle Data Miner with Oracle RMAN.
Related Topics
Parent topic: Full Backup and Restore
5.4.2 Workflow Only Backup
Oracle Data Miner provides a script for backing up the workflow metadata in the repository without including the objects in the users' schemas that are generated by the workflows.
The simplified backup strategy safeguards the workflow specifications and enables you to restore a workflow if you accidentally delete it. After the workflow is restored, you must re-run it to ensure that all the supporting objects are present in the user's schema. The creatxmlworkflowsbackup2
script backs up all the workflows in ODMRSYS
to a table called ODMR$WORKFLOWS_BACKUP
in a separate backup account. Before you run the script, ensure that the backup schema exists and is available.
Syntax:
createxmlworkflowsbackup2.sql backup_account
Parameter:
backup_account
is the name of the schema of the backup table, ODMR$WORKFLOWS_BACKUP
.
This example backs up the workflows in a backup account called BACKUPACCT
:
set serveroutput on
@createxmlworkflowsbackup2l BACKUPACCT
Note:
The dropRepositoryAndUserObjects
script drops all the backup tables when it drops the repository. If you run the dropRepositoryAndUserObjects
script to drop the repository, then all the workflow backups are lost.
Each time you run createxmlworkflowsbackup2
, a full set of workflows is added to the backup table. The backup script maintains up to 30 distinct backups within the backup table. Older backups are automatically deleted. For example, if the backup was run each day, then a user has up to 30 days to request a restore of a workflow.
In the backup script, the DEFINE_MAX_VERSIONS
specifies the number of backups that are preserved in the backup table. If you want to preserve more than 30 backups, then in the backup script createxmlworkflowsbackup2
, change the value of DEFINE_MAX_VERSIONS
to the desired number.
Related Topics
Parent topic: Backing Up and Restoring the Repository
5.4.3 Workflow Only Restore
You can restore workflows from the backup table created by createxmlworkflowsbackup2.
To restore the workflows from the backup table created by createxmlworkflowsbackup2
, run the restorexmlworkflowfrombackup2
script.
restorexmlworkflowfrombackup2.sql
restore workflows from the backup table to the Oracle Data Miner repository. Use it as follows:
Syntax:
restorexmlworkflowfrombackup2.sql [option] [backup_account] [workflow_definition]
Parameters:
option is an optional parameter that can have one of the following values:
-
ADD_ONLY
— Restore workflows that do not already exist in the repository, creating missing projects if necessary. (Default) -
DROP_AND_ADD
— Drop all existing workflows and projects in the repository, then restore all workflows from backup, creating missing projects if necessary. -
OVERRIDE_ONLY
— Only restore workflows that already exist in the repository. -
OVERRIDE_AND_ADD
— Applies both theOVERRIDE_ONLY
andADD_ONLY
options.
backup_account
is optional unless workflow_definition
is specified, in which case it is required. If no backup account is specified, then workflows are restored from the backup table in the repository. If the backup tables does not exist, then an exception is raised.
workflow_definition
is an optional parameter that identifies a table or view that specifies which workflows to restore from backup. The table or view must contain these four columns: USER_NAME
, PROJECT_NAME
, WORKFLOW_NAME
, and VERSION
. Each row in the table identifies a workflow to restore. If the VERSION
number is null, then the latest version number is used for the restore. When no workflow definition is provided, then the latest backup version is the default
Example:
This example drops all the workflows in the repository and restores the workflows from the backup table in BACKUPACCT.
set serverput on
@restorexmlworkflowfrombackup2 DROP_AND_ADD BACKUPACCT
Related Topics
Parent topic: Backing Up and Restoring the Repository
5.4.4 Workflow Only Restore Examples
This topic provides examples on selective workflow restore and full workflow restore.
Example 5-1 Selective Workflow Restore
Let us assume the user SCOTT
had accidentally deleted all his workflows last week. You can use the ADD_ONLY
option to restore his workflows. You will have to query the backup table to determine which version of backups contain his missing workflows. If the version is 12, then the following script example, run with DBA privilege, will reload only those workflows.
@restorexmlworkflowfrombackup2.sql ADD_ONLY BACKUPACCT BACKUPACCT.WORKFLOW_V
The WORKFLOW_V
view, shown as follows, selects all the workflows present for the user SCOTT
from a specified version backup number.
CREATE VIEW BACKUPACCT.WORKFLOW_V AS
SELECT user_name, project_name, workflow_name, version
FROM backupacct.odmr$workflows_backup
WHERE user_name='SCOTT' AND version = 12;
Example 5-2 Full Workflow Restore
Let us assume that there was some critical repository failure that requires a full reload of all workflows from the latest backup. You can use the DROP_AND_ADD
option to insure that all the old workflows are dropped and all the workflows on the backup are reloaded. In this case, the backup table is located in another account separate from the ODMRSYS
account. The latest backup version will be used for the recovery, so no workflow definition parameter is required.
@restorexmlworkflowfrombackup2.sql DROP_AND_ADD BACKUPACCT
Parent topic: Backing Up and Restoring the Repository
5.5 Migrating the Repository
This section contains topics related to repository migration.
Topics include:
- Upgrading ODMRSYS
Themigratebinaryodmr
script upgrades the workflow data in anODMRSYS
schema that uses binary XML storage. TheODMRSYS
tablespace is an Oracle Automatic Segment Space Management (ASSM) tablespace. - Scripts to Migrate the Repository
Starting with SQL Developer 4.0, Oracle Data Miner migration scripts are available for specific upgrade paths. - Upgrading New ODMRSYS Tablespace From Object-Relational to Binary
TheupgradeRepoWithNewTableSpace
script upgrades the specified tablespace from object-relational to binary XML storage and migrates the workflow data inODMRSYS
to the newt tablespace. The new tablespace must be an Oracle Automatic Segment Space Management (Oracle ASSM) tablespace. - Upgrading ODMRSYS From Object-Relational to Binary
TheupgradeRepoFromORtoBinary
script migrates theODMRSYS
workflow data from object-relational XML storage to binary XML storage. The upgradedODMRSYS
tablespace is an Oracle Automatic Segment Space Management (ASSM) tablespace.
Parent topic: Managing the Oracle Data Miner Repository
5.5.1 Upgrading ODMRSYS
The migratebinaryodmr
script upgrades the workflow data in an ODMRSYS
schema that uses binary XML storage. The ODMRSYS
tablespace is an Oracle Automatic Segment Space Management (ASSM) tablespace.
If any sessions that have the ODMRUSER
role are currently running, then the session object locks block the upgrade.You can use the session_disconnect
parameter to disconnect any active sessions, thus enabling the upgrade process to proceed.
Syntax:
@migratebinaryodmr.sql session_disconnect
Parameters:
session_disconnect
can have one of the following values:
R
— Report active sessions. Do not disconnect them.
D
— Disconnect active sessions. Do not report them.
DR
or RD
— Disconnect and report active sessions.
Example:
This example upgrades the binary XML workflow data in ODMRSYS
, disconnecting and reporting any active ODMRUSER
sessions.
@migratebinaryodmr DR
Parent topic: Migrating the Repository
5.5.2 Scripts to Migrate the Repository
Starting with SQL Developer 4.0, Oracle Data Miner migration scripts are available for specific upgrade paths.
Table 5-1 Oracle Data Miner Upgrade Scripts
SQL Developer Version | Database Version | Script | Description |
---|---|---|---|
4.0 and later | 11.2.0.4 and later | migratebinaryodmr.sql |
Relevant for future releases, when XML conversion from object storage to binary storage will no longer needed. |
4.0 and later | 11.2.0.3 or earlier originally, and then the database was upgraded to 11.2.0.4 or later | upgradeRepoWithNewTableSpace.sql or upgradeRepoFromORtoBinary.sql |
Since the database can be upgraded independently of the Oracle Data Miner repository, this has to be taken into account. The choice of scripts will depend on whether ODMRSYS tablespace is ASSM or not.
|
3.2.2 or earlier | 11.2.0.4 and later, where the default ODMRSYS tablespace is not ASSM-based
|
upgradeRepoWithNewTableSpace.sql |
ASSM tablespace required as input parameter. This will be used to migrate the workflow data from XML object storage to XML binary storage. |
3.2.2 or earlier |
11.2.0.4 and later, where the default |
upgradeRepoFromORtoBinary.sql |
Workflow data will be migrated from XML object storage to XML binary storage. |
Parent topic: Migrating the Repository
5.5.3 Upgrading New ODMRSYS Tablespace From Object-Relational to Binary
The upgradeRepoWithNewTableSpace
script upgrades the specified tablespace from object-relational to binary XML storage and migrates the workflow data in ODMRSYS
to the newt tablespace. The new tablespace must be an Oracle Automatic Segment Space Management (Oracle ASSM) tablespace.
If any sessions that have the ODMRUSER
role are currently running, then the session object locks block the upgrade. You can use the session_disconnect
parameter to disconnect any active sessions, thus enabling the upgrade process to proceed.
Syntax:
upgradeRepoWithNewTableSpace.sql ASSMtablespace session_disconnect
Parameters:
ASSMtablespace
is the name of an ASSM tablespace.
session_disconnect
can have one of the following values:
R
— Report active sessions. Do not disconnect them.
D
— Disconnect active sessions. Do not report them.
DR
or RD
— Disconnect and report active sessions.
Example:
This example migrates object-relational XML data in ODMRSYS
to the new ASSM tablespace, my_ASSM_space
, that uses binary XML storage. If any ODMRUSER
sessions are active, they are disconnected and reported.
@upgradeRepoWithNewTableSpace my_ASSM_space DR
Related Topics
Parent topic: Migrating the Repository
5.5.4 Upgrading ODMRSYS From Object-Relational to Binary
The upgradeRepoFromORtoBinary
script migrates the ODMRSYS
workflow data from object-relational XML storage to binary XML storage. The upgraded ODMRSYS
tablespace is an Oracle Automatic Segment Space Management (ASSM) tablespace.
If any sessions that have the ODMRUSER
role are currently running, then the session object locks block the upgrade.You can use the session_disconnect
parameter to disconnect any active sessions, thus enabling the upgrade process to proceed.
Syntax:
@upgradeRepoFromORtoBinary.sql session_disconnect
Parameters:
session_disconnect
can have one of the following values:
R
— Report active sessions. Do not disconnect them.
D
— Disconnect active sessions. Do not report them.
DR
or RD
— Disconnect and report active sessions.
Example:
This example upgrades ODMRSYS
from object-relational XML storage to binary XML storage. The upgraded tablespace is ASSM-based. If any ODMRUSER
sessions are active, they are disconnected and reported.
@upgradeRepoFromORtoBinary DR
Related Topics
Parent topic: Migrating the Repository
5.6 Dropping the Repository
The dropRepositoryAndUserObjects
script drops the Oracle Data Miner repository and related objects in the users' schemas.
If any sessions that have the ODMRUSER
role are currently running, then the session object locks block the upgrade. You can use the session_disconnect
parameter to disconnect any active sessions, thus enabling the upgrade process to proceed.
Syntax:
dropRepositoryAndUserObjects.sql session_disconnect
Parameters:
session_disconnect
can have one of the following values:
R
— Report active sessions. Do not disconnect them.
D
— Disconnect active sessions. Do not report them.
DR
or RD
— Disconnect and report active sessions.
Example:
This example drops the ODMRSYS
, schema and related objects in the Oracle Data Miner users' schemas, disconnecting and reporting any active ODMRUSER
sessions.
@dropRepositoryAndUserObjects DR