Oracle Database How To Guide
From Swivel Knowledgebase Wiki
Contents |
Oracle Database How To Guide
Introduction
This document outlines the necessary steps to change from an internal PINsafe database to an Oracle database.
Prerequisites
- Fully configured PINsafe server
- Oracle server
Baseline
- PINsafe 3.7
- Oracle XE
Architecture
Information is taken from the repository data source and inserted into the Oracle database. Multiple PINsafe servers can use the Oracle database for user authentication.
Repository Synchronisation Considerations
Multiple PINsafe servers can connect to a database. It is essential that when multiple PINsafe 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 PINsafe 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: PINsafe 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 PINsafe 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 PINsafe 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 PINsafe 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
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.
Additional Information
For assistance in the PINsafe installation and configuration please firstly contact your reseller and then email Swivel Secure support at support@swivelsecure.com
