Database administrative tasks - Oracle

Several scripts are provided to manage server components and the Dynamic Workload Console, performing tasks such as granting permissions and reorganizing databases.

The path and the available database tools vary depending on the component, as follows:

master domain manager and backup master domain manager: <MDM_installation_directory>/TWS/dbtools/<database_vendor>/<OS_type>/scripts
dynamic domain manager and backup dynamic domain manager: <DDM_installation_directory>/TDWB/dbtools/<database_vendor>/<OS_type>/scripts
Dynamic Workload Console: <DWC_installation_directory>/DWC/dbtools/<database_vendor>/<OS_type>/scripts

The following scripts are available:

dbgrant (available only for the master domain manager): This script grants the user permissions for the views of the server components and the Dynamic Workload Console.
dbmaintenanceOracle: This script updates the database statistics (updateStat parameter) and reduces index fragmentation by physically reordering pages and compacting space without fully rebuilding the index (reorg parameter). These operations improve query performance and data read efficiency.
dbmove (available only for the master domain manager): This script is to be run only if requested by Software Support.

Note: Do not run the dbmove script unless instructed to do so by Software Support. Running the script autonomously might damage or overwrite the data in your database.

To run the scripts, perform the following steps:

Copy the oracle folder to the database server. This folder contains the scripts and the related .sql files.
Check that the user who is going to run the procedure has the appropriate rights:
- Database administrator permissions
- Full access to and control of the HCL Workload Automation or Dynamic Workload Console installation directory
On the database server, open a database shell.
From within the shell, browse to the directory where you copied the script.
Run the script with the -help parameter to display a list of supported parameters.

Syntax and arguments for the dbgrant script

Windows operating systems:

dbgrant.bat new_user database TWS_user TWS_password

UNIX operating systems:

./dbgrant.sh new_user database TWS_user TWS_password

Supported parameters for the dbgrant script

new_user: The username of the account that receives read permissions.
database: The name of the specific database instance.
TWS_user: The administrative account with authority to grant permissions.
TWS_password: The password for the administrative account.

Syntax and arguments for the dbmaintenanceOracle script

Windows operating systems:

dbmaintenanceOracle.exe -db connection_string -u user -p password [-a updateStat|reorg]

UNIX operating systems:

./dbmaintenanceOracle -db connection_string -u user -p password [-a updateStat|reorg]

Supported parameters for the dbmaintenanceOracle script are as follows:

-db connection_string: Connection string or alias for the Oracle database, for example localhost:1521/xe. This parameter is required.
-u user: Name of the user accessing the database. This parameter is optional when you specify user name and password in shell where you run the command.
-p password: Password of the user accessing the database. This parameter is optional when you specify user name and password in the shell where you run the command.
-a action: The action to be performed by the script. Supported values are updateStat and reorg. The default value is updateStat. If you choose the updateStat argument, the script updates the database statistics. If you choose the reorg, the script reduces index fragmentation by physically reordering pages and compacting space without fully rebuilding the index.

Other administrative tasks for Oracle databases

You can perform a number of other tasks on Oracle databases as follows:

Changing the Oracle access password

For more information, see Changing the properties for the database.

Maintaining the Oracle database

Like DB2, Oracle has a routine that regularly maintains the database. Similarly, this too can be run manually. The tool is invoked as follows:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

See the Oracle documentation for full details of how and when to run it.

Obtaining information about the HCL Workload Automation databases installed on an Oracle instance

To determine which HCL Workload Automation databases are installed on an Oracle instance, do the following:

su - oracle (UNIX only)
 sqlplus system/system_password@service_name
SQL> select * from all_tws_schemas;

The output should look like the following:

SCHEMA_NAME
------------------------------
MDL
mdm10.2.7 <TWS_user>

Note:

More than one instance of HCL Workload Automation can be shared in one instance of Oracle, using different schemas.
In Oracle, the concept of "schema" and "user" are the same, so dropping an Oracle schema means dropping an Oracle user, which you do as follows:
```
SQL> drop user MDL cascade;
```