6 Managing the Oracle Data Miner Repository

This chapter explains how to use scripts to manage 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, including ODMRSYS and the Oracle Data Miner user schemas.
• Migrating the Repository
  This section contains topics related to repository migration.
• Dropping the Repository
  The dropRepositoryAndUserObjects script drops the Oracle Data Miner repository and related objects in the users' schemas.

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.

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.

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.

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 by createxmlworkflowsbackup2.
• Workflow Only Restore Examples
  This topic provides examples on selective workflow restore and full workflow restore.

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 restore ODMRSYS and the user schemas independently of a full backup and restore of the database.

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.

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.

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 the OVERRIDE_ONLY and ADD_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

Workflow Only Restore Examples

This topic provides examples on selective workflow restore and full workflow restore.

Example 6-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 6-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

Migrating the Repository

This section contains topics related to repository migration.

Topics include:

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

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

Scripts to Migrate the Repository

Starting with SQL Developer 4.0, Oracle Data Miner migration scripts are available for specific upgrade paths.

Table 6-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 ODMRSYS tablespace is ASSM-based	upgradeRepoFromORtoBinary.sql	Workflow data will be migrated from XML object storage to XML binary storage.

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

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

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