Difference between revisions of "Oracle Database How To Guide"

Latest revision as of 12:52, 11 May 2017

Oracle Database How To Guide

Introduction

This document outlines the necessary steps to change from an internal Swivel database to an Oracle database. When used, the Oracle database must be running for Swivel to start.

Swivel should also work with an Oracle RAC as a data store.

Prerequisites

• Swivel 3.2 onwards, excluding 3.9.6 and 3.9.7
• Oracle server

Baseline

• Swivel 3.7
• Oracle XE

Architecture

Information is taken from the repository data source and inserted into the Oracle database. Multiple Swivel servers can use the Oracle database for user authentication.

Repository Synchronisation Considerations

Multiple Swivel servers can connect to a database. It is essential that when multiple Swivel servers are synchronising data from the same data source they do so at different times so as to avoid the same user being created at the same time on different cluster members. This can be done by setting only one active synchronisation, or setting different times for synchronisation.

Installation

Installing a JDBC driver

The jdbc driver will need to be downloaded see http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Copy the file to <path to apache Tomcat>/webapps/pinsafe/WEB-INF/lib

Ensure that the permissions, and any file or group ownership are correct, i.e. the same as the other files in the group.

Restart Tomcat

Configure the Swivel Database

Note For their data sources Master servers should run in Synchronised mode and slave servers should run in slave mode.

The following parameters need to be set for the database:

Identifier: Database Name

Class: Swivel Java class used for database

Driver: Software driver used for communication with database

URL: Location of the database

Username: Database username

Password: Database Password

Syntax for Oracle database config on a Swivel virtual or hardware appliance:

Identifier Oracle

Class: com.swiveltechnologies.pinsafe.user.database.MySQL5Database

Driver: oracle.jdbc.driver.OracleDriver (Note: Case Sensitive)

URL: jdbc:oracle:thin:@[host][:port]/SID

Username: (your oracle username e.g. system)

Password: (your password)

Where:

[host] - The host name where Oracle server is running.

[port] - The port number where Oracle is listening for a connection. By default this is 1521.

SID - System ID of the Oracle server database instance. By default, Oracle Database 10g Express Edition creates an instance called XE.

Note: At this stage DO NOT set the database to Oracle but keep it as Internal.

Migrate Data into Oracle database

Migrate the users from the Primary Master into the Oracle database. This is normally carried out on a single server unless differing repositories are used. See also Migrate How to guide.

From the Swivel Administration console select Migration/Data then select the Oracle database and enter MIGRATE, click on apply. A message indicates the data has been successfully migrated.

The logs will indicate the following messages:

Database at jdbc:oracle:thin@[host][:port]:SID

Database oracle.jdbc.driver.OracleDriver loaded successfully.

The length of time for Migration will vary between install sites, but 500 users could approximately take upto 2 minutes.

Select the Oracle database

Select the Oracle database

From the Swivel Administration console select Database/General then select the Oracle database and click on apply.

The logs will indicate the following messages:

Database at jdbc:oracle:thin@[host][:port]:SID opened successfully.

Database oracle.jdbc.driver.OracleDriver loaded successfully.

Verify that the status page lists an Active database of Oracle

Active database Oracle

Troubleshooting

Error Messages

Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.lang.ClassNotFoundException: oracle.jdbc.OracleDriver

The Oracle database driver has not been found, ensure that it has been downloaded from the Oracle website, and uploaded to the Swivel instance and that it is in the correct location and correct permissions.

admin:Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.sql.SQLRecoverableException: I/O-Error: The Network Adapter could not establish the connection

ERROR x.x.x.x admin:Unable to open the database: java.sql.SQLRecoverableException: I/O-Error: The Network Adapter could not establish the connection

The connection to the firewall cannot be established. Check firewall rules are not blocking the connection.

ERROR x.x.x. admin:Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.sql.SQLRecoverableException: I/O-Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]"

ERROR x.x.x.x admin:Unable to open the database: java.sql.SQLRecoverableException: I/O-Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]"

The database connection URL is incorrect, check the settings.

ERROR x.x.x.x admin:Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.sql.SQLRecoverableException: I/O-Error: Unknown host specified

ERROR x.x.x.x admin:Unable to open the database: java.sql.SQLRecoverableException: I/O-Error: Unknown host specified

Incorrect hostname specified for Db server

ERROR x.x.x.x admin:Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.sql.SQLRecoverableException: I/O-Error: Invalid number format for port number

ERROR x.x.x.x admin:Unable to open the database: java.sql.SQLRecoverableException: I/O-Fehler: Invalid number format for port number

The port number is not specified correctly, check for non numeric characters and that it has been specified.

ERROR x.x.x.x admin:Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.lang.ClassNotFoundException: oracle.jdbc.driver.oracledriver

ERROR x.x.x.x admin:Failed trying to load JDBC driver class

The driver has failed to load, in this instance oracle.jdbc.driver.oracledriver has been specified instead of oracle.jdbc.driver.OracleDriver

java.sql.SQLException: The url cannot be null

The correct Oracle driver has not been installed.

PINsafe data migration failed! com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: Einfangen von NULL in ("SWIVELDB"."PINSAFEM"."B") nicht maeglich

The database column PINSAFEM must be modified to accept nullable entries.

Exception occurred checking agent: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-00018: maximum number of sessions exceeded

ORA-00604: error occurred at recursive SQL level 1

ORA-00018: maximum number of sessions exceeded

ORA-06512: at line 12

There are too many connections being made to the Oracle server. Investigate which applications are accessing the Oracle database and if they are using many connections.

Additional Information

For assistance in the Swivel installation and configuration please firstly contact your reseller and then email Swivel Secure support at support@swivelsecure.com