Installing Inventory Management on Oracle Database

This chapter describes the prerequisites and instructions for installing the Inventory Management database on Oracle Database:

• Inventory Management Database Configurations

• Preparing Oracle Database for Inventory Management

• Installing or Updating the Inventory Management Database

• Inventory Management Database Configurations
  
• Preparing Oracle Database for Inventory Management
  
• Installing or Updating the Inventory Management Database
  

Inventory Management Database Configurations

Oracle MICROS recommends setting the following Oracle Database configurations when creating the database instance and before running the Inventory Management product installer. While some configurations can be changed after the installation, preparing the database instance prior to schema installation can prevent unexpected errors and problems.

Oracle Database Configuration for Inventory Management Database

Use the following table for a set of guidelines for setting up the Oracle Database instance for Inventory Management:

Parameter	Recommended Value
Instance Name	<CustomerName><Environment ID><EnvironmentPurpose P/S/T>INV For example, for customer ABC, environment ID 1, and environment purpose P for production: ABC01PINV
db_block_size	8192
open_cursors	1000
db_recovery_file_dest_size	Set as the amount of storage allocated to FRA and DATA.
job_queue_processes	64
aq_tm_processes	1
session_cached_cursors	100
optimizer_mode	first_rows_10
optimizer_index_caching	20
optimizer_index_cost_adj	20
cusrsor_sharing	exact
processes	1024
sga_max_size	Set as 50% of database RAM, to be tuned by the DBA during the life of the environment.
sga_target	Set as the same value as sga_max_size.
pga_target	Set as 25% of sga_target.

Oracle Database Tablespaces for Inventory Management Database

Make sure to create the following tablespaces in the Inventory Management database. Each tablespace requires Transparent Data Encryption (TDE) encryption.

• USERS

• TEMP

Preparing Oracle Database for Inventory Management

Follow these instructions to set up the Oracle Database shell before installing Inventory Management.

1. Log in to the database using a command prompt and administrator privileges.
2. Enter the following commands:

   sqlplus sys/password@yourinstance as sysdba

   GRANT create session, resource, create view TO new username IDENTIFIED BY password;

   ALTER USER new username DEFAULT TABLESPACE existing tablespace to use TEMPORARY TABLESPACE temporary tablespace to use;

   ALTER USER new username QUOTA UNLIMITED ON new username;

3. To check the location of the DPUMP_DIR, enter the following command:

   SELECT * FROM dba_directories;

4. Place your DMP file in the directory shown, and then enter the following command:

   imdpd system/password@yourinstance DIRECTORY=DPUMP_DIR DUMPFILE=yourDMPfile PARALLEL=1 LOGFILE=yourlog.log REMAP_SCHEMA=sourceschema:targetschema REMAP_TABLESPACE=sourcetablespace:targettablespace

5. Review the log in the same directory for errors.

Installing or Updating the Inventory Management Database

1. Install the Inventory Management Database Updater on the database server:
   1. Download and extract the installation archive to the database server, double-click setup.exe, and then follow the installation wizard instructions.
   2. On the Choose Database Connection page, enter any connection name, as the name can be changed in a later step. Ignore the Warning message on the next page.
2. Navigate to root\Program Files\myinventory Database Updater\ and open sql.ini in a text editor:
   1. Find the section [win32client.dll] and verify the following line:

      comdll=sqlodb32

   2. Add the following lines to the end of the file:

      [odbcrtr]

      remotedbname=orgshortname,DSN=orgshortname

   3. Update the server name and SIDs:

      <servers>

      <server name=”servername”><DbBrand value=”SQLSERVER” />

      <ServiceName value=”MSSQL server instance” />

      </server>

      </servers>

3. Navigate to root\Program Files\myinventory Database Updater\ and open fmlogini.ini in a text editor:
   1. Add or configure a section for [orgshortname]:

      [orgshortname]

      DBLogin=server name/orgshortname/password,orgshortname,myinvenmenu.ini

      AppIINI=settings.ini

   2. If you are performing parallel database updates, you can set the maximum number of parallel updates by adding or setting the following lines:

      [BatchUpdate]

      MaxThreads=number of threads

      Depending on your hardware configuration, enter a value between 10 and 30.

4. Use the Database Updater to install or update the database with the required tables, columns, and views:
   1. From the Microsoft Windows desktop, double-click Single Database, and then log in using your Inventory Management administrator credentials.
   2. On the Settings tab, select the Daily Totals setting for the database.
   3. On the Database Update tab, click Update Database to insert the tables, columns, and views. Verify that the update completes without errors.
   4. Click Reorganization to add and remove database procedures and functions. Verify that the update completes without errors.
5. Log in to the database and enter the following command to add an index to the DAILYTOTALS database. If you are upgrading a database with a large number of records, work with a database administrator to schedule the update because of the amount of time required by the operation.

   create index DAILYTOTALS_fk_ART_ID on DAILYTOTALS ( ART_ID );