Daycart Installation

1.1 Detailed Installation Instructions

A quick overview of the installation process can be found in the Daylight Installation Guide. This section is intended to give detailed installation instructions. For the purpose of consistency in the examples, this document will show actual commands and changes that were performed on a Solaris machine named 'snowcrash'. Also installed on this machine are Oracle 9.2 with an instance name of 'snow' and installed in the location '/usr/local/oracle/product/9.2.0', defined as ORACLE_HOME and Daylight owned by the user 'daylight', group 'dba', and installed in the directory /usr/local/daylight/v491, defined as DY_ROOT. When an alternate platform needs something done another way, that platform will be referenced and then a relevant alternate example given.

1.1.1 Installation of the Daylight Tar File

If you do not have Daylight already installed, unpack the Daylight tar distribution that you have received from Daylight and follow the relevant installation instructions. Daylight is usually located in /usr/local/daylight/v491, where 'v491' is the version of the Daylight software installed, and is owned by an account 'thor' or 'daylight'. As stated before this location is refered to as DY_ROOT. It might be useful to have the owning account of the daylight software, also be a member of the 'dba' group with Oracle. The Daylight distribution contains two directories that are relevant to the daycart install. 'dcischem', which has the installation SQL scripts and 'lib' which has the required shared object library for Oracle.

1.1.2 Connecting to Oracle as a DBA User

In this section the following commands must be performed by an Oracle user that has dba privileges. Once you are logged in as the owning Oracle account you can login to oracle as a dba with the following:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 18 09:53:01 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL>

Now that we are logged in as a DBA we can continue.

1.1.3 Space Creation and Creating the Daycart User in Oracle.

It is recommended by Oracle that the 'SYSTEM' tablespace not be used for user code. Decide which tablespace to use to store the cartridge definitions and create or enlarge that tablespace. The cartridge code only requires about 2MB of space, so size isn't a huge issue. In this example, we are creating a tablespace named 'daylight_ts' and giving that tablespace 100 megabytes of space.

create tablespace daylight_ts
   datafile '/data/d01/oracle/oradata/snow/daylight_01.dbf'
   size 100M;

Now create the user 'c$dcischem' and grant necessary privileges. Execute the following commands in your DBA session:

create user c$dcischem
   identified by
      secret
   default tablespace
      daylight_ts
   temporary tablespace
      temp;

grant dba to c$dcischem;

Alternately, rather than granting DBA privilege to c$dcischem, one can grant the minimal required privileges:

grant
   resource,
   connect,
   create operator,
   create indextype,
   create library,
   create procedure,
   create role,
   create type,
   create public synonym
to
   c$dcischem;

grant
   create table
to
   c$dcischem
with admin option;

Exit the DBA session, and edit the file 'create.sql'. Modify the lines that contain the following sql command:

create or replace library
   c$dcischemlib
as
   '/usr/local/daylight/v491/lib/ddlib.so.10';

and substitute the absolute path to the appropriate cartridge shared object library. The file ddlib.so is usually found in the $DY_ROOT/lib directory within the normal Daylight distribution.

Daylight ships multiple versions of ddlib.so in each release. One or more of the following files will be found in your Daylight distribution (depending on the operating system):

  $DY_ROOT/lib/ddlib.so.10    32-bit ddlib.so for Oracle 10g & 10gR2.
  $DY_ROOT/lib/ddlib.so.11    32-bit ddlib.so for Oracle 11g & 11gR2.
  $DY_ROOT/lib64/ddlib.so.10  64-bit ddlib.so for Oracle 10g & 10gR2.
  $DY_ROOT/lib64/ddlib.so.11  64-bit ddlib.so for Oracle 11g & 11gR2.

Also, remove the section of code before the 'create or replace library' command. These 'echo' commands are in place as a reminder for users who neglect to edit this file before trying to run it.

Execute the script 'create.sql'.

$ sqlplus 'c$dcischem/secret' @create.sql

The output will consist of quite a few notes and messages. One should not see any error messages from sqlplus. If one gets a message about the COMPATIBLE parameter being set incorrectly, see the troubleshooting section below before proceeding further. A sample of the output follows:

$ sqlplus 'c$dcischem /secret' @create

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 18 11:28:38 2002

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Connected.

Library created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Role created.

Grant succeeded.

[many lines of text omitted]

Synonym created.

#########NOTE: Public synonyms "matches" and "contains" #########
######### clash with InterMedia cartridge names if #########
######### InterMedia is installed. Please see the #########
######### daycart manual and FAQ for details. #########

PL/SQL procedure successfully completed.

Grant succeeded.

[many lines of text omitted]

Grant succeeded.

Grant succeeded.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
$

If you already have a daycart license, you can put the insert statement for c$dcischem.license into the create.sql file after the creation of the table c$dcischem.license. In this example install, we will do it separately, after the create.sql script has run. If not, you'll need to contact Daylight in order to get a license key. We'll need the unique identifier from your machine. If you have installed the daylight tools and thus have testlicense installed you can get it from the command line:

$ testlicense -i
   cpu type: i686
   cpu idno: 2341395969

alternatively, in oracle's sqlplus once the cartridge is installed, one can execute the following command:

$ sqlplus 'c$dcischem /secret'

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Sep 18 08:34:44 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select ddpackage.finfo('hostid') from dual;

DDPACKAGE.FINFO('HOSTID')
-------------------
2341395969

E-mail the this identifier to Daylight, and we'll e-mail back the required license key, which would look like this (with your data substituted, of course):

insert into c$dcischem.license values ('daycart',
  '1d96f3c5d4305d57c0b4bf54eb69111c',
  to_date('01-01-05', 'DD-MM-YY'),
  'Daylight CIS',
  'Santa Fe Research Office',
  '441 Greg Avenue, Santa Fe, NM 87501, USA');

NOTE: The license key is not required in order to run the installation scripts. The license key can be added after installation is complete. However, once the cartridge is installed it will not successfully run without the license key.

1.1.4 Configuration of the Oracle Listener

The cartridge uses the 'extproc' functionality on the server within Oracle. The Oracle server contacts the network listener when it wants to run external procedures like the Daylight cartridge. Hence, the listener must always be running in order to use the cartridge.

NOTE: The Oracle Installer will start and configure the listener if you do a full default Oracle installation, however the listener will not be configured to restart automatically after a reboot. It is best to verify the configuration and state of the listener at this point, however don't be suprised if the listener configuration is already correct.

1.1.5 Checking the Listener

The first thing to do is check to see that the listener is working correctly. Using the instance name in the connect string tests the SQLNet listener:

$ tnsping snow

TNS Ping Utility for Solaris: Version 9.2.0.1.0 - Production on 28-MAY-03 21:20:25

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=snowcrash)(PORT=1521))
TNS-12541: TNS:no listener

If one gets "OK" then the listener is running. In the above example it is not running.

$ lsnrctl status

LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 18-SEP-2002 13:05:00

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Solaris Error: 146: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= snowcrash)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Solaris Error: 146: Connection refused

This confirms that the listener isn't started.

$ lsnrctl start

LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 18-SEP-2002 13:05:10

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Starting /usr/local/oracle/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
System parameter file is /usr/local/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /usr/local/oracle/product/9.2.0/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc )(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp )(HOST=snowcrash)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
Start Date 18-SEP-2002 13:05:10
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP  OFF
Listener Parameter File /usr/local/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /usr/local/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=snowcrash)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "snow" has 1 instance(s).
 Instance "snow", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Now retry the tnsping. It should return "OK". This verifies that the listener is functional.

1.1.6 Modification of the Listener's Configuration Files

There are two ways to modify the configuration for the listener. One can either edit the configuration files directly or use the network configuration assistant program, which provides a GUI front end to the configuration. This section will describe the file entries.

First change directory to where the oracle installation's files are for the listener:

$ cd $ORACLE_HOME/network/admin

The three files that are relevant are listener.ora, tnsnames.ora, sqlnet.ora.

Start with sqlnet.ora. This file need not be edited however if the default domain is used then one must change the name of the EXTPROC service in tnsnames.ora.

$ more sqlnet.ora
# SQLNET.ORA Network Configuration File: /usr/local/oracle/product/9.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

If one is using fully-qualified domains on will find a line like:

NAMES.DEFAULT_DOMAIN = daylight.com

If this line is present, note the domain name for use in the tnsnames.ora file.

Here is an example tnsnames.ora file on snowcrash. If full domains are being used the service name for EXTPROC_CONNECTION_DATA must be changed to: EXTPROC_CONNECTION_DATA.DAYLIGHT.COM (using your default domain).

$ more tnsnames.ora
# TNSNAMES.ORA Network Configuration File: /usr/local/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

<< Other entries omitted >>

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
       (PRESENTATION = RO)
    )
  )

SNOW =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = snowcrash)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = snow)
    )
  )

Here is a sample listener.ora file:

$ more listener.ora
# LISTENER.ORA Network Configuration File: /usr/local/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = snowcrash)(PORT = 1521))
      )
   )
 )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/local/oracle/product/9.2.0)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = snow)
      (ORACLE_HOME = /usr/local/oracle/product/9.2.0)
      (SID_NAME = snow)
    )
 )

In this case, the oracle installer created files that are mostly correct. All that is required (for 9.2 and higher versions of Oracle) is to add a line which allows oracle to use shared objects (aka: .so files) that are not in the $ORACLE_HOME/lib directory. Simplest is to allow any .so file on the system. This was the behavior for versions of Oracle prior to 9.2. The SID_LIST_LISTENER section of the listener.ora file has one additional line:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /usr/local/oracle/product/9.2.0)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY")                #### ADDED LINE ####
    )
    (SID_DESC =
      (GLOBAL_DBNAME = snow)
      (ORACLE_HOME = /usr/local/oracle/product/9.2.0)
      (SID_NAME = snow)
    )
 )

To verify the changes, stop and start the listener:

$ lsnrctl stop

LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 18-SEP-2002 13:52:17

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully
$ lsnrctl start

LSNRCTL for Solaris: Version 9.2.0.1.0 - Production on 18-SEP-2002 13:52:30

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Starting /usr/local/oracle/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
System parameter file is /usr/local/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /usr/local/oracle/product/9.2.0/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc )(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp )(HOST=snowcrash)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Solaris: Version 9.2.0.1.0 - Production
Start Date 18-SEP-2002 13:52:30
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP  OFF
Listener Parameter File /usr/local/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /usr/local/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=snowcrash)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "snow" has 1 instance(s).
 Instance "snow", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

One final test of extproc functionality is to use the 'tnsping' utility to verify that the SQLNet service is available:

$ tnsping extproc_connection_data
TNS Ping Utility for Solaris: Version 9.2.0.1.0 - Production on 28-MAY-03 21:18:46

(c) Copyright 1997 Oracle Corporation.  All rights reserved.

Attempting to contact (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0))
OK (0 msec)

1.1.7 Creating an Account to Access Daycart and Test the Installation

Create (or give the privileges to) a user account that uses the Daycart software. As a DBA, grant the daycart role to a non-privileged user to use the cartridge. In this example, the user 'mug' with the password 'coffee' is granted daycart privilege. Simple queries can be used to test the cartridge.

$ sqlplus "/ as sysdba"
create user mug
   identified by
      coffee
   default tablespace
      daylight_ts
   temporary tablespace
      temp;

grant connect, daycart to mug;
connect mug/coffee

select ddpackage.finfo('hostid') from dual;

DDPACKAGE.FINFO('HOSTID')
---------------------------------------------------------------
80ad7574

select ddpackage.ftestlicense('daycart') from dual;

DDPACKAGE.FTESTLICENSE('DAYCART')
---------------------------------
 1

select smi2cansmi('NCC', 0) from dual;

SMI2CANSMI('NCC',0)
---------------------------------------------------------------
CCN

If the above three queries don't give the expected results, see the section on troubleshooting.

1.1.8 Revoking Privileges from c$dcischem

The user c$dcischem owns all the cartridge code, however c$dcischem requires no privileges after installation for the cartridge to work. All cartridge functionality operates with the privileges of the current user, not the cartridge owner (see the SQL reference manual for 'CREATE FUNCTION ... AUTHID CURRENT_USER and invokers rights for more details). Also, you might want to change the password for c$dcischem to something non-obvious.

revoke dba from c$dcischem;

revoke
   create public synonym,
   create type,
   create role,
   create operator,
   create indextype,
   create table,
   create library,
   create procedure,
   resource,
   connect
from
   c$dcischem;

1.2 Installation Troubleshooting

There are a number of details which must be correct for the cartridge to work properly. For a new Oracle installation, these are typically already set correctly, however following is a list of potential issues, which are included here for diagnostic purposes:

In order to use the cartridge functionality, the server must be configured to support at least 8.1 compatability. The line: compatible = "8.1.0" (or higher) must be present in your init.ora file. If it isn't, stop your server, edit the file, and restart the server. If, during the installation, you get the following message gazillions of times, it is because this parameter isn't set correctly:

 SQL> create or replace operator smi2cansmi binding (varchar2, number)
 *
 ORA-00406: COMPATIBLE parameter needs to be 8.1.0.0.0 or greater

If one gets the following error message:

 SQL> select ddpackage.finfo('hostid') from dual;
 *
 ERROR at line 1:
 ORA-06520: PL/SQL: Error loading external library
 ORA-06522: ld.so.1: extprocPLSExtProc:
   fatal: /usr/local/daylight/v491/dcischem/ddlib.so.10:
 open failed: No such file or directory

the cause is the specification of the c$dcischemlib library. Recheck the lines in the file create.sql and verify that absolute path to the file 'ddlib.so' matches the actual location of the file and that the file is readable by the Oracle user:

 create or replace library c$dcischemlib
   as '/usr/local/daylight/v491/dcischem/ddlib.so.10';

It will be necessary to completely remove and reinstall the cartridge in order to fix this problem. See the section on 'Cartridge De-installation'.

If a valid license has not been inserted into the c$dcischem.license table, then the ftestlicense function will return:

 SQL> select ddpackage.ftestlicense('daycart') from dual;

 ERROR at line 1:
 ORA-29400: data cartridge error
 C$DCISCHEM-003: FAILED: License not available

In this case, contact Daylight and get a valid license key for the cartridge. Provide the hostid to Daylight (see installation above) and we'll provide to you the insert statement which must be run. If you have inserted the license key, then perhaps the c$dcischem.license table is not visible to the current user. Try:

 SQL> select * from c$dcischem.license;

 PRODUCT KEY EXPIRATION
 --------- -------------------------------- ----------
 daycart 8befd3d06296e9b89abfb158b431d278 01-JAN-01

If this table isn't visible, then perhaps the role 'daycart' hasn't been granted to the current user. Remember that each user must have the 'daycart' role granted to it in order to use the cartridge. The 'daycart' role grants select on c$dcischem.license and c$dcischem.progob, execute on the cartridge functions, and create table privilege.

If the network listener is not running or is misconfigured, then you'll get a message like this:

 SQL> select smi2cansmi('NCC', 0) from dual
 *
 ERROR at line 1:
 ORA-28575: unable to open RPC connection to external procedure agent

when attempting to access any Daylight Cartridge functions. In this case, verify that the listener is running:

 $ lsnrctl
 LSNRCTL> status

 Connecting to (DESCRIPTION ...
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production
 Start Date 14-MAR-00 12:00:37
 Uptime 0 days 0 hr. 17 min. 15 sec
 Trace Level off
 Security OFF
 SNMP OFF
 Listener Parameter File /oracle/o815/network/admin/listener.ora
 Listener Log File /oracle/o815/network/log/listener.log
 Services Summary...
 PLSExtProc has 1 service handler(s)
 dev has 3 service handler(s)
 The command completed successfully

Note that the listener is running, and that there is a service listed for PLSExtProc. Also, verify that the files: $ORACLE_HOME/network/admin/listener.ora and $ORACLE_HOME/network/admin/tnsnames.ora have the appropriate entries. If necessary, restart the listener to make sure that any configuration file changes have taken effect.

In one case, we've seen a problem with the LD_LIBRARY_PATH not being set for the Oracle instance. This was an non-standard Oracle instance which was upgraded from 8.0.X, so it isn't clear where the problem arose. The cartridge requires the Oracle shared client library:

libclntsh.so.8.0 or libclntsh.so.9.0

This file is found in $ORACLE_HOME/lib/. It is preferred that the UNIX Oracle userid have the LD_LIBRARY_PATH set in the .profile or .cshrc to include $ORACLE_HOME. If this is not the case, define LD_LIBRARY_PATH and then restart the Oracle instance and listener.

1.3 Verification

The subdirectory 'dcischem/TEST' includes a number of stand-alone SQL scripts which test the cartridge installation. The shell script 'run_tests' will execute all the SQL scripts in turn and compare the output to reference output files. Any discrepencies will be reported. The script 'run_tests' does attempt to log in as 'c$dcischem/secret', so if you've changed the password and privileges on user 'c$dcischem ' you'll need to edit run_tests to reflect this change. For example using the mug user we created above.

The scripts use the 'EXPLAIN PLAN' facility, which requires that the plan table be present for the test user. The script $ORACLE_HOME/rdbms/admin/utlxplan.sql creates the plan table; this is executed as part of the 'run_tests' script.

 $ run_tests
 Testing dd_basic_test ...
 Comparing output to reference file ...
 dd_basic_test test OK.

 Testing dd_exact_test ...
 Comparing output to reference file ...
 dd_exact_test test OK.
 < ... >
 All tests passed.

Also, note that the SQL scripts give useful examples of using the cartridge functionality in each category.

1.4 Upgrading from Previous Versions of Daycart

In general, minor versions of Daycart (eg. 4.71 -> 4.72) do not *require* any database operations during upgrade. Typically these upgrades consist solely of a replacement for the ddlib.so shared library. In cases where Oracle DDL commands need to be executed as part of a minor version upgrade, these will be documented separately as part of the upgrade procedure.

Upgrades between major versions (4.7x -> 4.81, etc.) do require completely removing and replacing the Daycart Oracle packages. In order to perform major upgrades it is necessary to drop all dependant indexes from the system and recreate them after the instance has been upgraded.

The basic procedure for a major upgrade is:

Completely back up the database instance.
Run the 'list_indexes' program as sysdba. Provide a name prefix which will be used to generate the 'create' and 'drop' files used in subsequent steps.
Edit the '[prefix].create.sql' file created by the 'list_indexes' program with passwords for accounts
From SQL, as a dba, run the '[prefix].drop.sql' file.
Run the Daycart 'clean.sql' script.
Perform the Daycart installation by running the new 'create.sql' script.
Verify that the new version of Daycart is correct by running the regression tests in $DY_ROOT/dcischem/TEST.
Run the '[prefix].create.sql' script to add the indexes.

Detailed instructions to upgrade your instance are:

First, back up your instance!!! Use an export, a cold backup (tar the database files with the instance shut down), or a hot backup using your favorite Oracle backup utility.

Next use the 'list_indexes' program provided with the release. It is a program which generates two SQL scripts, one to drop and the other to create your Daycart indexes.

Below is a 'list_indexes' example. There are two accounts (mug and glass) that have daycart indexes in this example. The list_indexes program can be found in $DY_ROOT/bin. There are two versions; one for Oracle 9.x one for 10 & 11 (list_indexes.9 and list_indexes.10, respectively). and

$ pwd
/usr/local/daylight/v491/bin
$ ls list_index*
list_indexes.9*  list_indexes.10*
$ ./list_indexes.9
Usage: list_indexes <user/pass@sid> <output_file_prefix>

If given a user with DBA privilege, list_indexes will find and catalog all Daycart indexes on the system. If a non-privileged user is specified, then only that users indexes will be cataloged.

$ ./list_indexes.9 system/secret snow
Total Rows processed: 8

Having supplied snow as the file prefix parameter, the two files: snow.create.sql and snow.drop.sql were created.

$ more snow.create.sql 
--------------------------------------------------------------------------------
-- Index Owner: GLASS
--

CONNECT GLASS/########

--------------------------------------------------------------------------------
-- Index: GLASS.SMI_INDEX_EXACT_SMILES
-- Table: GLASS.SMI_MAIN
-- Type: DDEXACT
--

CREATE INDEX GLASS.SMI_INDEX_EXACT_SMILES
   ON GLASS.SMI_MAIN(SMILES)
   INDEXTYPE IS C$DCISCHEM.DDEXACT;
--  PARAMETERS (' ');

--------------------------------------------------------------------------------
-- Index: GLASS.SMI_INDEX_BLOB_SMILES
-- Table: GLASS.SMI_MAIN
-- Type: DDBLOB
-- Parameters: fpsize=1024
--

CREATE INDEX GLASS.SMI_INDEX_BLOB_SMILES
   ON GLASS.SMI_MAIN(SMILES)
   INDEXTYPE IS C$DCISCHEM.DDBLOB
   PARAMETERS ('fpsize=1024');

[many lines deleted]

CONNECT MUG/########

--------------------------------------------------------------------------------
-- Index: MUG.SMI_INDEX_EXACT_SMILES
-- Table: MUG.SMI_MAIN
-- Type: DDEXACT
--

CREATE INDEX MUG.SMI_INDEX_EXACT_SMILES
   ON MUG.SMI_MAIN(SMILES)
   INDEXTYPE IS C$DCISCHEM.DDEXACT;
--  PARAMETERS (' ');

[many lines lines deleted, to the end of the file]

here is the begining part of the drop script:

$ more snow.drop.sql 
--------------------------------------------------------------------------------
-- Index: GLASS.SMI_INDEX_EXACT_SMILES
-- Table: GLASS.SMI_MAIN
-- Type: DDEXACT
--

DROP INDEX GLASS.SMI_INDEX_EXACT_SMILES FORCE;

--------------------------------------------------------------------------------
-- Index: GLASS.SMI_INDEX_BLOB_SMILES
-- Table: GLASS.SMI_MAIN
-- Type: DDBLOB
--

DROP INDEX GLASS.SMI_INDEX_BLOB_SMILES FORCE;

[many lines deleted, to the end of the file]

See the manpage for list_indexes(1) in the Daylight documentation for more details on the list_indexes program.

Edit the 'prefix.create.sql' file. For each index, add optional creation parameters (eg. table_tablespace, etc.) and verify that the columns and tables are correct. Also, note that the creation script runs as multiple different users, so you'll need to either enter the passwords for each user in the script or cut apart the sql scripts for each individual user to invoke.

From SQL, as dba, run the 'prefix.drop.sql' file. This will drop all Daycart indexes.

$ sqlplus system/secret @snow.drop.sql

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 17 13:45:52 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Run the Daycart 'clean.sql' script. This will drop all daycart packages, indextypes, and operators from the database instance.

$ sqlplus 'c$dcischem/secret' @/usr/local/daylight/v473/daycart.9i/clean.sql

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 17 13:54:53 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Connected.
Connected.

Synonym dropped.

[many lines deleted]

Package dropped.


Role dropped.


Library dropped.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Perform the Daycart installation by running the new 'create.sql' script. Remember to edit create.sql and set the library path. This is covered in the installation section of this document.

$ sqlplus 'c$dcischem/secret' @/usr/local/daylight/v491/dcischem/dcischem/create.sql

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 17 13:54:53 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

PL/SQL procedure successfully completed.

Connected.

Library created.

[many lines deleted]

Grant succeeded.


Grant succeeded.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Verify that the new version of Daycart is correct by running the regression tests in $DY_ROOT/dcischem/TEST.

Now, run the 'prefix.create.sql' script. This will recreate all Daycart indexes:

$ sqlplus 'c$dcischem/secret' @snow.create.sql

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 17 14:31:04 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Connected.

Index created.


Index created.


Index created.


Index created.

Connected.

Index created.


Index created.


Index created.


Index created.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

At this point you are finished with doing the upgrade.

1.5 De-Installation

In order to cleanly de-install the cartridge, all dependent indexes must be dropped from the system first. That is, any indexes created using one of the Daylight indextypes (ddexact, ddrole , ddgraph, ddblob) should be dropped. If one does not drop all the indexes first, then they will be marked as invalid. If this occurs, the indexes and their associated index tables ([indexname] || '_DDT') must be dropped manually. The list_indexes program is convenient to generate the 'drop index' commands. See the previous section.

After any dependent indexes have been dropped, one can run the script 'clean.sql'. This script will remove all cartridge index definitions, functions, operators, packages, roles, and synonyms. The script 'clean.sql' will remove everything which is installed by the script ' create.sql' with the exception of the license and program object tables. These tables are preserved since it is convenient to not need to recreate and populate these tables. The user c$dcischem will require DBA privilege to be granted in order to run the script.

Example output follows:

$ sqlplus 'c$dcischem/secret' @clean

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Dec 12 13:27:29 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Connected.

Revoke succeeded.


Revoke succeeded.


Revoke succeeded.


Revoke succeeded.

[many lines of text omitted]


Package dropped.


Role dropped.


Library dropped.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
LICENSE                        TABLE
PROGOB                         TABLE
PLAN_TABLE                     TABLE

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
$

It is safe to repeatedly run the scripts create.sql and clean.sql. The create.sql script creates a bunch of objects, types, etc. The clean.sql script removes them. Neither script will modify any other objects, types, or tables owned by c$dcischem or any other user on the system. The only components created by the create.sql script which are not removed by the clean.sql script are the license, progob, salt, and transform tables. These tables are preserved by the clean.sql script for convenience.

When one runs create.sql after clean.sql, one will see warnings that the tables license, progob, salt, and transform already exist, however these warnings don't impact the outcome of the cartridge re-installation.

After running clean.sql , the database will be left in its original pre-cartridge state. One can then delete the license, progob, salt, and transform tables, if desired, to eliminate all remnants of the cartridge.

Note that the user c$dcischem will continue to exist after running clean.sql and any non-cartridge tables, objects, etc. owned by c$dcischem will be unaffected by either clean.sql or create.sql .

Another way to remove the cartridge is to drop the user c$dcischem. Note that this does not remove public synonyms or roles, so these should be dropped manually (or via clean.sql).

 SQL> drop user c$dcischem cascade;

This will remove the user c$dcischem and all dependent objects. It will be necessary to repeat the entire installation procedure if this method is chosen.

1. Installation

1.1 Detailed Installation Instructions

1.1.1 Installation of the Daylight Tar File

1.1.2 Connecting to Oracle as a DBA User

1.1.3 Space Creation and Creating the Daycart User in Oracle.

1.1.4 Configuration of the Oracle Listener

1.1.5 Checking the Listener

1.1.6 Modification of the Listener's Configuration Files

1.1.7 Creating an Account to Access Daycart and Test the Installation

1.1.8 Revoking Privileges from c$dcischem

1.2 Installation Troubleshooting

1.3 Verification

1.4 Upgrading from Previous Versions of Daycart

1.5 De-Installation