# Genesys Quality Management Suite 8.1.520 : Creating DB Schema in Oracle

## Installing the Database Schema

When configuring an Oracle connection for the first time, create Call Recording and Quality Manager user schema (database tables, triggers, etc.) in the Oracle database. This is achieved in one operation by running a schema creation script in the `/opt/callrec/dbscripts/oracle` directory.

To remove the existing Call Recording and Quality Manager schema from the Oracle database, see Removing the Database Schema.

The script is available in two versions: The `create_schemas.sh` script is a Linux shell script, while the `create_schemas.bat` script is a Windows DOS script. In both cases, the script must be run on a host server that has the Oracle 11g database client installed. This Oracle client software is automatically included as part of the Genesys Quality Management Suite installation process, so the `create_schemas.sh`. Linux script can be run directly on the Genesys Quality Management Suite server, as described here. An additional benefit of running the schema creation script on the Genesys Quality Management Suite server, is the possibility of ensuring that there is correct connectivity between the server and Oracle database.

Icon

If you are running the command from your QM server, make sure to execute the create_schemas script after the Call Recording-setup process.

The Linux version of the script must be run by the root user. The script’s usage and parameters are as follows:

    ./create_schemas.sh [system_user] [system_password] [database_name] [callrec_schema_name] [wbsc_schema_name] [options]

The DOS version is similar to the Linux version, though `create_schemas.bat` is run without the sh command preceding it.

The following parameters are required:

- `system_user`: Username of database administrator account (see Prerequisites: Preparing for Oracle).
- `system_password`: Password of database administrator account.
- `database_name`: Oracle Service name, in the following form:
  `//hostname.domain.com:port/servicename`
  For example: `//oracle.mycompany.com:1521/zoomdb`.
- `callrec_schema_name`: Call Recording schema user entered as Oracle User earlier during Genesys Quality Management Suite setup.
- `wbsc_schema_name`: Quality Manager schema user entered as Oracle WBSC User earlier during Genesys Quality Management Suite setup.

Type: sh create_schemas.sh [without parameters] to view this parameter list.

The following options can also be specified (not required in a standard installation):

`--tbscallrec value:` Name of tablespace used for Call Recording (default: `USERS`).

`--tbswbsc value:` Name of tablespace used for Quality Manager (default: `USERS`).

`--temptbs` value: Name of tablespace for temporary files (default: `TEMP`).

`--data Y [or] N`: create default data: User admin, roles, etc. (default: `Y`).
This should normally be set to Y for new installations – the only case where the data is not required is when preparing a new database for the migration of existing data.

`--create_admin Y [or] N`: create the user `callrec_wbsc_admin` with administrative rights for the Call Recording and Quality Manager schema (default: `N`).
This user has the following default credentials:
username: `callrec_wbsc_admin`
password: `adm`

See the following Linux example:

    cd /opt/callrec/dbscripts/oracle
    sh create_schemas.sh system sys //oracle.mycompany.com:1521/zoomdb callrec wbsc --tbscallrec USERS --tbswbsc USERS --temptbs TEMP --data Y --create_admin Y

## Additional Tasks

### Update Oracle Schema

After the `create_schema.sh` script is run, the new Call Recording and Quality Manager schema users have their passwords set to the default password values on the Oracle configuration screen in the Genesys Quality Management Suite setup. See the Oracle User Password and Oracle WBSC User Password properties in the chapter: Single Server Configuration for more details. If the default values were used, no updates are required.

- If different password values were used, reset the passwords for these Call Recording and Quality Manager schema users within Oracle.
  Consult the Oracle documentation for how to reset database user passwords.

### Start Call Recording

- After schema installation is complete, start Call Recording at the command line, ensuring that the Call Recording Core service starts (indicating correct database connection):

    service callrec start

Note that some other services may not start as they are not fully configured or await license activation, see the Genesys Quality Management Suite Implementation Guide for more details.

Installation and basic setup are now complete.

### Removing the Database Schema

If an attempt at installing the Call Recording and Quality Manager database schema was only partially successful, or they are no longer required in the Oracle database, remove the schema using the `drop_schemas` script (in the same scripts directory as the `create_schemas` script:`/opt/callrec/db_oracle_scripts/scripts`).

The removal script is available in two versions; the `drop_schemas.sh` script is a Linux shell script, and the `drop_schemas.bat` script is a Windows DOS script.

The script’s usage and parameters (for the Linux version) are as follows:

    sh drop_schemas.sh [system_user] [system_password] [database_name] [callrec_schema_name] [wbsc_schema_name] [options]

The DOS version is similar to the Linux version, but `drop_schemas.bat` is run without the `sh` command preceding it.

The following parameters are required:

- `system_user`: Username of database administrator account (see Prerequisites: Preparing for Oracle).
- `system_password`: Password of database administrator account.
- `database_name`: Database name, in the form: `//hostname.domain.com:port/servicename` for example: `//oracle.mycompany.com:1521/zoomdb`.
- `callrec_schema_name`: Call Recording schema user; by default this is the Oracle User value on the Oracle parameters screen in Genesys Quality Management Suite Setup.
- `wbsc_schema_name`: Quality Manager schema user; by default this is the WBSC User value on the Oracle parameters screen in Genesys Quality Management Suite Setup.

Type: `sh create_schema.sh` [without parameters] to view this parameter list.

The following options can also be specified (not required in a standard installation):

`--drop_admin Y [or] N`: delete the user `callrec_wbsc_admin`. This user is created by the `create_schemas` script when the `create_admin Y` option is specified. The user has administrative rights for the Call Recording and Quality Manager schema. See the topic Install the DB Schema for more information.

See the following Linux example:

    cd /opt/callrec/db_oracle_scripts/scripts
    sh drop_schemas.sh system sys //oracle.mycompany.com:1521/zoomdb callrec wbsc --drop_admin Y

### Troubleshooting Database Parameters

If there are any issues in starting up, check the database parameters in `/opt/callrec/etc/core.xml`, and the error log at `/opt/callrec/logs/error.log`.

After completing the Genesys Quality Management Suite setup with the Oracle Database Client service activated, the core.xml file should contain database pool configuration entries similar to the following (here with the default entries used earlier):

     <Pool name="callrec" poolType="cz.zoom.util.db.pool.ibatis.IbatisPool">
     <Url dbName="zoomdb" host="oracle.mycompany.com" port="1521"/>
     <Login password="callrec" userName="callrec"/>
     <Connections init="1" max="20" timeOut="5"/>
     <SpecificSetting>
     <Value name="sqlMapClass">
    cz.zoom.callrec.core.callstorage.pojo.oracle.SqlMap</Value>
    </SpecificSetting>
     </Pool>
     <Pool name="scorecard" poolType="cz.zoom.util.db.pool.ibatis.IbatisPool">
     <Url dbName="zoomdb" host="oracle.mycompany.com" port="1521"/>
     <Login password="wbsc" userName="wbsc"/>
     <Connections init="1" max="20" timeOut="5"/>
     <SpecificSetting>
     <Value name="sqlMapClass">
    cz.zoom.scorecard.business.data.xmlOracle.SqlMap</Value>
     </SpecificSetting>
     </Pool>

Modify the `dbName`, `host`, `password`, and `username` properties (for all occurrences) if required, then restart Call Recording:

    service callrec restart

If there are issues with connections between Call Recording or Quality Manager and the Oracle database instance, contact Genesys Tech Support.