Creating an Oracle 10g or 11g database

Applies to the following products: 
Questionmark Perception
Applies to the following Perception versions: 
Perception 5.2
Perception 5.1

To create an Oracle 10g or 11g database for use with Perception, you need to:

The steps for creating an Oracle 10g or 11g database are very similar. The instructions given below assume that you are using Oracle 10g but they will be very similar for Oracle 11g. If you are using 9i Release 2, please refer to Creating an Oracle 9i Release 2 database.

We assume that your Oracle installation is already in place and that you are familiar with administering Oracle databases. We also assume that you are able to log on to Oracle under an account with Oracle DBA privileges and that you have created a table space using the correct Character Sets. For further information on the correct Character Sets to use in a table space please refer to Supported databases in the System Requirements section.

If you will be using Japanese characters with an Oracle database on an English operating system, you will need to set the NLS_Characterset to WE8MSWIN1252when you install your Oracle server. Otherwise you will need to set the NLS_Characterset to AL32UTF8. Refer to Oracle Help for more information.

Creating a Tablespace

To create a Tablespace for your database:

  1. Open Enterprise Manager Console on your database server.
  2. Log in to the database server you will be creating the new database on.
  3. Open the Administration tab.
  4. Under Storage, click Tablespaces
  5. Click Create
  6. Enter a name for the new Tablespace.
  7. Under Datafiles, click Add

  8. Enter the following information: 
    • Enter a File Name for the datafile
    • Set the File Size to a larger number than the default 100mb for high-use systems.
    • Tick Automatically extend datafile when full (AUTOEXTEND) and specify a suitably high value in the Increment box.
  9. Click Continue to return to the Create Tablespace page, and then click Ok to create the Tablespace. The new table will appear in the list of Tablespaces.

Creating a database user

To create a database user:

  1. Open the Enterprise Manager Console.
  2. Log in to the database server you will be creating the new database on.
  3. Open the Administration tab.
  4. Under Security, click Users
  5. Click Create

  6. Enter the following information:
    • Enter a database user name and password.
    • In the Default Tablespace box, enter the name of the Tablespace you created in Creating a Tablespace.
    • In the Temporary Tablbespace box, enter the name of a temporary Tablespace. By default, this is TEMP.
  7. Open the Roles tab and click Modify

  8. Assign the user the following roles: 
    • CONNECT
    • CTXAPP (only available if OracleText is installed)
    • RESOURCE
  9. Click OK
  10. Open the System Privileges tab and click Modify

  11. Select the following System Privileges and click OK:
    • CREATE SESSION
    • CREATE TABLE
    • CREATE PROCEDURE
    • CREATE SEQUENCE
    • CREATE SYNONYM
    • CREATE TRIGGER
    • CREATE VIEW
  12. Select the database user you created. Ensure that the Default Tablespace and Temporary Tablespace values are correct.
To be able to change security settings for administrators using Authoring Manager, you will also need to assign the CREATE TABLE system privilege to the individual user who will be changing the security settings

Next, you will need to configure the Local Service Name and then create an ODBC data source.

Configuring the Local Service Name

To configure the Local Service Name, you will need the Oracle client installed on your Perception Server machine.

To configure the Local Service Name:

  1. Open Oracle Net Manager on your Perception Server machine
  2. Select the Service Naming folder in the Oracle Net Configuration hierarchy and click the Add (+) button

    The Net Service Name Wizard screen is displayed

  3. Enter a Net Service Name and click Next. Make a note of this name as you will need to use it when you create an ODBC data source
  4. Select TCP/IP (Internet Protocol) and click Next
  5. Enter the Host Name and click Next. This is the name of the machine that the Oracle database is installed on. If you are not using the default Port Number, you can enter the port you are using as well
  6. Enter the Service Name of the Oracle instance and click Next
  7. Click Test to verify the connection
  8. Click Finish

Creating an ODBC data source

Once you have created your database, you will need to create a data source. This should be done on the Perception Server machine, and not the Oracle database server.

To do so:

  1. Open the ODBC Data Source Administrator on your Perception Server machine

    To do so on a:

    • 32-bit operating system go to Control Panel double-click Administrative Tools and then Data Sources (ODBC)
    • 64-bit operating system navigate and launch the following ODBC driver:

      C:\Windows\SysWOW64\odbcad32.exe

  2. Open the System DSN tab
  3. Click Add...
  4. Select Oracle in OracleVersion from the list and click OK (Where OracleVersion is the version name of your Oracle client install)

  5. Enter a name and description for the new data source in the appropriate text-boxes
  6. Specify in the TNS Service Name text-box the Net Service Name of the server you want to connect to (that is, the machine on which your database is installed). This is the Net Service Name you specified in Configuring the Local Service Name above
  7. Enter the name of the database user you have created in the User ID text-box
  8. Click OK, and OK again to close the ODBC Data Source Administrator

You can now create your repository.

If you are unable to successfully connect your Oracle client to the Oracle database server you should check that the Oracle Client installed with all the necessary permissions. To do this:

  1. Navigate to where the client is installed (For example, in Oracle 11g client the default installation path is C:\app\Administrator\product\11.2.0)
  2. Right click on the Oracle client install folder (\11.2.0\) and select Properties
  3. Select the Security tab
  4. Check if Authenticated Users exists in the Group or user names list box and that it has fill control

If Authenticated Users does not appear, complete the following steps:

  1. In the Properties window click Edit followed by Add
  2. In the Enter the object names to select text-box enter "Authenticated Users" (without the quotation marks) and click Check Names
  3. Click OK to add the user
  4. Place a tick in the Allow column for the Full control check-box
  5. Click OK and wait for the permissions to be updated and return to the Properties window
  6. Click Advanced followed by Change Permissions...
  7. Select the Authenticated Users from the Permission entries list
  8. Check the Replace all child object permissions with inheritable permissions from this object check-box and click OK
  9. Click OK to all the open windows and restart your computer

You should now be able to access the ODBC connection again and make a successful connection