Updating Oracle Schema Manually

Although it is recommended that the installation process create the Master database for Oracle, users can create the Oracle schema manually. If you are installing the Unix Master from the command-line then you must first create the Oracle schema manually. Have your Oracle DBA perform these procedures.

To update the Oracle schema manually:

1. Locate the connectdb.sql script within the sql directory.

2. Edit these parameters in this script:

   Note: For debugging purposes, you can run a spool file as you run the installation script.

   • create tablespace admiral_data datafile 'ADMIRAL_DATA' size 200m reuse autoextend on;

   • create tablespace admiral_index datafile 'ADMIRAL_INDEX' size 100m reuse autoextend on;

   • create temporary tablespace admiral_temp datafile 'ADMIRAL_TEMP' size 200M reuse autoextend on;

   • create user tidal identified by <call Tidal Support Center for password> default tablespace admiral_data quota unlimited on admiral_data quota unlimited on admiral_index temporary tablespace admiral_temp;

   • grant create session, create table, create view, create Procedure to tidal; connect tidal/<call Tidal Support Center for password>@ <tnsname>;

     Note: Contact Tidal Support Center for the password to enter in the brackets < >. Replace the string “tnsname” at the end of the CONNECT statement with the real TNSName that is used to connect to the Oracle database.

     Note: If you wish to install the datafiles in a specific directory, the Oracle DBA can change the CREATE TABLESPACE statements to specify a different directory location for the datafiles. The datafile growth options may also be modified if desired. Do not lower the default SIZE values.

3. Enter the information you received from Tidal Support Center in the appropriate brackets in the connectdb.sql script.

4. Save the script.

5. Locate the orapopulate.sql script within the sql directory.

6. Find this statement and change DOMAINNAME and SUPERUSERNAME to be the domain and user name of the initial super user account:

   insert into usrmst (usrmst_id, usrmst_domain, usrmst_name, usrmst_fullname, usrmst_desc, usrmst_phoneno, usrmst_pagerno, usrmst_email, usrmst_emailtype, secmst_id, lngmst_id, usrmst_password, usrmst_suser) values (1, DOMAINNAME, 'SUPERUSERNAME', 'SUPERUSERNAME', null, null, null, null, null, 6, 1, null, 'Y');

   Note: DOMAINNAME can be null. If it is not null, be sure to add single quotes around the domain name in the SQL statement. Your Oracle TA database should now be installed.

7. Save the script.

8. Login as the SYSTEM user (or equivalent).

9. Run the connectdb.sql script to create tidal user.

10. Locate the createtidalrduser-oracle.sql script within the sql directory.

11. Find this statement and change DB_READ_ONLY_USERNAME and DB_READ_ONLY_PASSWORD to be the login ID and password.

    Example: declare userexist integer; begin
    select count(*) into userexist from all_users where LOWER(USERNAME)='DB_READ_ONLY_USERNAME'; if (userexist = 0) then
    execute immediate 'create user DB_READ_ONLY_USERNAME IDENTIFIED BY "DB_READ_ONLY_PASSWORD" DEFAULT TABLESPACE admiral_data ';
    end if;
    execute immediate 'grant connect, create session,create synonym, select any table to DB_READ_ONLY_USERNAME';
    end;
    /

12. Save the script. Run the createtidalrduser-oracle.sql script to create read-only user.

13. Run these scripts as the TA user you just created.

    • Run the adoracle.sql script and if there are no errors issue a commit; statement.

    • Run the orapopulate.sql script and if there are no errors issue a commit; statement.

    • Run the nodmst.sql script and if there are no errors issue a commit; statement.

    • Run the createviews-oracle.sql script and if there are no errors issue a commit; statement.

    Your Oracle TA database should now be installed. If any errors occurred when running those scripts, DO NOT continue. Collect as much information on the errors as possible and contact either the consultant assisting your installation or Tidal Support Center.