Support
Oracle and Timesheet

 

Click here for instructions for Timesheet version 7.

Requirements for using Oracle on Journyx Timesheet 8.0 or higher:

  1. Oracle 10g or Oracle 11g or higher is required. Earlier versions of Oracle are not supported.
  2. Oracle requires a certain level of database administrative knowledge, access, and expertise. If you are planning to install Timesheet to Oracle, make sure you have an Oracle DBA (Database Administrator) available to answer any questions regarding the optimal setup. If you do not have an Oracle DBA available then Journyx recommends you use the included PostgreSQL database instead which offers competitive reliablity and performance in most situations.
  3. Oracle has a set of default administration passwords that should be changed. The default Oracle accounts / passwords in some versions of Oracle are sys / change_on_install and system / manager. Both of these passwords should be changed immediately after installation, and a separate account for Journyx Timesheet should be created as described below.
  4. The instructions on how to create objects inside Oracle such as users and tablespaces are written based on Oracle 11g, the current version as of this writing. The instructions may have to be adjusted slightly under Oracle 10g. Earlier versions of Oracle such as 9i are no longer supported for Timesheet.
  5. Oracle provides two client packages for connecting to an Oracle server; the "Full Client" and the "Instant Client". Timesheet works with either one, but normally you should only install one or othe other, not both.
  6. With either client the connection to the Oracle server is specified in so-called "TNS-less" format. All you need to know is the hostname, Oracle Service Name, and your account username and password.
  7. The Oracle Instant Client is a smaller download and is the fastest way to get going. Download it from here: http://www.oracle.com/technology/tech/oci/instantclient/index.html. (Note: it may require you to register with Oracle.com.)
    • Only the "Basic" package is required for Timesheet. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Timesheet.
    • Other questions about installing and using the Instant Client may be answered here: Oracle Instant Client FAQ.
    • On Windows you must unzip the Instant Client files to a directory like c:\instantclient_11_2 and then add this directory to the beginning of your System Path variable and then reboot before installing Timesheet. Detailed instructions are found below.
    • On Unix you decompress the Instant Client files to a directory like /usr/local/instantclient_11_2 and then set the the ORACLE_HOME environment variable to that location. For instance, $ export ORACLE_HOME=/usr/local/instantclient_11_2
  8. If you use Oracle's "Full" client (not the Instant Client) then the appropriate directories are added to the system Path for you. However you may need to reboot after installing the Oracle Client. Be sure to read the setup instructions provided by Oracle.
  9. If you have both Oracle Full Client and Instant Client installed, you may run into this error booting the Timesheet database: ORA-24315: illegal attribute type. If you get this error, make sure that the Instant Client directory appears in your path before the Full Client path. Look for a path entry like C:\oracle...\bin, or c:\app\\product; and place the Instant Client directory before that one. Or place the Instant Client directory (such as c:\instantclient_11_2) at the beginning of the Path line. Make sure a semicolon (;) separates the entry from the next one. Click OK. You must now reboot the computer for this to take effect. Do so and rerun the "Setup Web Server and Database" program.

Oracle Setup Instructions (first steps for all platforms):

  1. Make sure you have read the general instructions here.
  2. Install the Oracle server (version 10g or higher) if not already installed. Change the passwords for the administrator accounts sys and system if that has not been done yet.
  3. Install the Oracle client software on any Timesheet application servers and verify that you can connect to your server using SQL*Plus or a similiar tool. Journyx recommends you install the Instant Client as described below.
  4. Do not attempt to connect to an already-populated Timesheet database, especially if the data is for an older version of Timesheet. These instructions assume you're creating a new, blank database. You can run a "restoredb" command later to populate the site from a Timesheet .jx backup file.
  5. Open Oracle's Enterprise Manager program (aka Database Control) and log in as SYS or an equivalent role. Use the "Connect as SYSDBA" option.
  6. Create a Data Tablespace for the Journyx Timesheet user if one doesn't already exist. Normally you will want to create a separate tablespace for Journyx Timesheet, but it is possible to use the Oracle-supplied USERS tablespace (which is the default for new users) provided there is enough free space. If you plan to use the USERS tablespace, when you create the Journyx Timesheet user, be sure to leave his default tablespace as USERS
    • Click on the "Server" tab.
    • Click "Tablespaces" under the Storage heading.
    • Click "Create".
    • Give the tablespace a name like JOURNYX_DATA.
    • Leave the default options: Extent: Locally Managed. Type: Permanent. Status: Read Write.
    • Under Datafiles click Add and create a new storage file.
    • Make the initial size at least 100 mb. The exact size is not important as long as the datafile is allowed to grow (as is the default.)
    • Note: if you choose not to make the tablespace auto-grow, you will be responsible for monitoring Timesheet's space usage and adjusting the tablespaces appropriately.
    • Click "Create" to bring the tablespace online.
  7. Create a Role for the Journyx Timesheet user.
    • In Enterprise Manager, click the Server tab.
    • Under "Security" click the "Roles" option then click "Create" on the next screen.
    • Name the role something like TIMESHEET_USER. Leave "Authentication" set to None.
    • Click on the Role tab then click "Edit List".
    • Assign the CONNECT role, and no others, then click OK.
    • Click on the "System Privileges" tab then click "Edit List".
    • Assign only these privileges (without the Admin/Grant option):
      • CREATE SEQUENCE
      • CREATE TABLE
      • CREATE VIEW
      • CREATE TRIGGER
      . Then click OK.
    • Click "OK" to finish creating the Role.
  8. Create a Journyx Timesheet user.
    • In Enterprise Manager, click the Server tab then select "Users" under the Security header.
    • Click "Create."
    • Assign a user name like journyx and assign a password. You will need to supply this username and password later.
    • Important: Change the default tablespace of the user to JOURNYX_DATA or whatever you called the Data Tablespace. Leave the Temporary tablespace "TEMP" unless your DBA has told you otherwise.
    • Click on the Roles tab. Assign the user the Role you created above (TIMESHEET_USER) and no other.
    • Click on the Quota tab. Give this user "unlimited" quota on the JOURNYX_DATA tablespace. If you do not give the user quota on his default tablespace, the Journyx Timesheet Database setup will fail because the user will be unable to create any objects. If you choose to set a quota, be prepared to manually monitor Timesheet's space usage and adjust the quota as needed.
    • Click "Create" to finish creating the user.
  9. Verify that you can connect as this user from your desired Timesheet application server using SQL*Plus or a similar tool.
  10. Continue with the appropriate platform-specific instructions below.

Windows-only instructions:

  1. If using the Oracle Full Client, read Oracle's instructions and install the client and reboot. Test that you can connect to your desired Oracle Server with the SQL*Plus program.
  2. If using Instant Client, download it for Windows here: http://www.oracle.com/technology/tech/oci/instantclient/index.html. Be sure to use the 64 bit version of Instant Client if you are using a 64 bit edition of Windows. Only the "Basic" package is required for Timesheet. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Timesheet.
  3. Unzip the Instant Client download to a directory like c:\instantclient_11_2
  4. Important: You must now add that directory to the system PATH. If you fail to do this, when you try to connect you will get a message like: "Sorry, I cannot load the cx_Oracle module." To add this directory to the path, do this:
    1. Right click on "My Computer" (or "Computer") and select Properties.
    2. Click "Advanced System Settings" (or sometimes just "Advanced".)
    3. Click "Environment Variables."
    4. Under "System Variables", scroll down until you see "Path" (or "PATH").
    5. Select Path and click Edit.
    6. Move to either the beginning of the line, or else somewhere before any other Oracle related paths.
    7. Now type the path from above, such as C:\instantclient_11_2.
    8. Make sure it's separated from the other path components with a semicolon (;). So it should look like: c:\instantclient_11_2;c:\[other paths...]
    9. Click OK.
    10. You must now restart the computer for this change to take effect. Do so and continue following these instructions.
    11. If you forgot to do this and got the "Sorry, I cannot load the cx_Oracle module" message, then click "Cancel Database Setup" and follow the above instructions. Then restart the "Setup Web Server and Database" program under Start -> Programs -> Journyx Timesheet.
    12. As mentioned above, if you get this error: ORA-24315: illegal attribute type then you should again check that the Instant Client directory is before any other Oracle directories in the System Path.
  5. Once you have added the Oracle Instant Client directory to the system PATH you are ready to install Timesheet.
  6. You should NOT normally need to create a "tnsnames.ora" file. Timesheet uses a "TNS-less" form of connection that can connect to any Oracle server that is accessible on your network. This applies whether you are using the Instant or Full Client, on both Windows and Unix.
  7. Run the Timesheet installation program. Allow it to reboot your computer at the end and the "Setup Web Server and Database" program will run.
  8. Choose the webserver such as IIS and other options as prompted.
  9. You will be asked if you want to use the internal PostgreSQL connection or if you want an external database connection. Choose "Connect to an external database system."
  10. Select the Oracle option.
  11. Put in your connection information as prompted:
    • Oracle hostname or IP address
    • Oracle port (default is 1521)
    • Oracle Service Name. This is defined in the "service_names" configuration parameter in Oracle server.
    • User name: the Oracle account name.
    • Password: the password for that Oracle account.
    • Index tablespace: normally you can leave this blank unless you want the indexes on a separate tablespace.
    Fill in this info and click OK. If a connection cannot be established, for instance due to a wrong password or hostname, you will be given the opportunity to either try again with the same info (for instance, if the Oracle service was temporarily offline) or else to create a new connection with different info.
  12. At this point it will create all the tables and views for Timesheet and will eventually send you to a login screen. It will pop up an alert message with the initial username and password.
  13. If necessary, you can run a "restoredb" command from the command line to populate your Timesheet data from a .jx backup file.

Linux-only instructions:

  1. Download the Instant Client for Linux here: http://www.oracle.com/technology/tech/oci/instantclient/index.html. Be sure to use the 64 bit version of Instant Client if you are using a 64 bit edition of Linux. Only the "Basic" package is required for Timesheet. The "Basic Lite" package can be used but has limited support for non-Western languages. You may wish to install the SQL*Plus package for your own testing purposes but it is not required or used by Timesheet.
  2. Download the Timesheet installation .tar.gz file for your platform. If you are using a 64 bit edition of Linux, you need to use the 64 bit edition of Timesheet, as well as the 64 bit Oracle Instant Client.
  3. Unzip the Instant Client download to a directory like /usr/local/instantclient_11_2
  4. You should NOT normally need to create a "tnsnames.ora" file. Timesheet uses a "TNS-less" form of connection that can connect to any Oracle server that is accessible on your network.
  5. A symbolic link must be created in the Oracle Instant Client directory. The Timesheet installer can create this for you if it has permissions, but just in case here is how to create the link manually.
    1. su to root, or sudo su -
    2. cd /usr/local/instantclient_11_2, or whatever directory you extracted the Instant Client files to.
    3. Run: ls libclntsh.so.*. You should see a file listed like libclntsh.so.11.1 but the number might be different.
    4. Run: ln -s libclntsh.so [FILE from above], so something like ln -s libclntsh.so libclntsh.so.11.1
  6. Un-tar-gz the Timesheet installer. Typically you run something like tar xzvf JournyxTimesheet_80_Linux.tar.gz
  7. Change into the resulting jtime directory.
  8. Set the ORACLE_HOME environment variable to the location you extracted Instant Client. For instance, export ORACLE_HOME=/usr/local/instantclient_11_2
  9. Run the ./jtinstall program. It will guide you through prompts for your Oracle information. Or you can save time by providing all the information at the command line in this format: ./jtinstall --oracle "username/password@//host:port/instance" where:
    • username is the Oracle account name.
    • password is the Oracle account password.
    • host is the Oracle hostname or IP address.
    • port is the Oracle port (default is 1521).
    • Instance is the Oracle Service Name. This is defined in the "service_names" configuration parameter in Oracle server.
    Be sure to enclose the entire connection string in double-quotes.
  10. If necessary, you can run a "restoredb" command from the command line to populate your Timesheet data from a .jx backup file.