A Appendix: Database Parameter File

This appendix provides information on database requirements and sample database scripts.

Physical Database Upgrade Requirements

At the physical database level, the following two conditions must be met in order to be Release 14.1 compatible:

Characterset

The characterset must be AL32UTF8.

Tablespaces

Rename the tablespace RETEK_DATA to RETAIL_DATA and rename the tablespace RETEK_INDEX to RETAIL_INDEX.

Note: Releases 14.0 and later no longer use tablespaces RETEK_DATA and RETEK_INDEX and they should be renamed to RETAIL_DATA and RETAIL_INDEX. It is absolutely essential that a complete backup has been taken before performing this task.

Refer to "Installing the AIP Oracle Database - Patch Mode,"

Sample Database Scripts

This section provides sample database scripts for:

• Sample Database init.ora

• Sample Tablespace Creation Scripts

Sample Database init.ora

The following code provides a sample database, init.ora. The commented code provides instructions about making the necessary modifications for your environment.

###############################################################################
# Oracle 11.1.0.x Parameter file
#        
# NOTES: Before using this script:
# 1. Change <datafile_path>, <admin_path>, <utl_file_path>, <diag_path>
and <hostname>
# values as appropriate.
# 2. Replace the word SID with the database name.
# 3. Size parameters as necessary for development, test, and production
environments.
# ------------------------------------------------------------------------
# MAINTENANCE LOG
#
# Date By Parameter Old/New Notes
# +------+ +---------+ +-----------------+ +-------------+ +-------------+
#
#
###############################################################################
def# -------------------------------------------------------------------------------
# The policy is to give 60% for sga and 40% for PGA out of Memory Target at
startup
# -------------------------------------------------------------------------------
memory_target = 2000M
# --------------------------------------------------------------------------------
audit_file_dest = <admin_path>/adump
compatible = 11.1.0
control_files = (<datafile_path>/control01.ctl
,<datafile_path>/control02.ctl)
db_block_size = 8192 # Default is 2k; adjust before db creation,
cannot change after db is created
db_file_multiblock_read_count = 16 # Platform specific (max io
size)/(block size)
db_name = SID
diagnostic_dest = '<diag_path>'
java_pool_size = 100M
job_queue_processes = 5 # Oracle Retail required; number of
cpu's + 1
local_listener =
"(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))"
nls_calendar = GREGORIAN
nls_date_format = DD-MON-RR # Oracle Retail required; if RDW
database see later entry for proper format
nls_language = AMERICAN # Default
nls_numeric_characters = ".," # Should be explicitly set to ensure all
users/batch get the same results
nls_sort = BINARY # Should be explicitly set to ensure all
sessions get the same order
nls_territory = AMERICA # Default
open_cursors = 900 # Oracle Retail required (minimum=900);
default is 50
optimizer_features_enable = 11.1.0.7
optimizer_mode = CHOOSE # Oracle Retail required
Appendix: Oracle 11g Database Parameter File
56 Oracle Retail Merchandising System
plsql_optimize_level = 2 # 10g change; use this setting
to optimize plsql performance
processes = 500 # Max number of OS processes that can connect
to the db
query_rewrite_enabled = TRUE # Oracle Retail required for functionbased
indexes
session_cached_cursors = 900 # Oracle Retail required;
undo_management = AUTO
undo_retention = 1800 # Currently set for 30 minutes; set to avg
length of transactions in sec
undo_tablespace = undo_ts
user_dump_dest = <admin_path>/udump
utl_file_dir = <utl_file_path>
workarea_size_policy = auto # Should be set to auto
when pga_aggregate_target is set
#
# *** Set these parameters for Oracle Retail Data Warehouse (RDW) database ***
#nls_date_format = DD-MON-RRRR # Required by MicroStrategy
#query_rewrite_integrity = TRUSTED
#star_transformation_enabled = TRUE
#utl_file_dir = <Windows_utl_file_path>,
<UNIX_util_file_path>
#
# *** Archive Logging, set if needed ***
#log_archive_dest_1 = 'location=<admin_path>/arch/'
#log_archive_format = SIDarch_%r_%s_%t.log
#log_buffer = 10485760 # Set to (512K or 128K)*CPUs
#log_checkpoint_interval = 51200 # Default:0 - unlimited
#log_checkpoint_timeout = 7200 # Default:1800 seconds

Sample Tablespace Creation Scripts

The tablespaces displayed in the following code example are required.

Note: Oracle Retail recommends the use of locally managed tablespaces with auto segment space management.

create_aip_tablespaces.sql

Execute as: sysdba

Modify file paths and ORACLE_SID for your environment.

CREATE TABLESPACE RETAIL_INDEX DATAFILE
    '/u01/oradata/$ORACLE_SID/retail_index01.dbf'  SIZE 500M 
    AUTOEXTEND ON NEXT 100M MAXSIZE 2000M 
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO 
; 
CREATE TABLESPACE RETAIL_DATA DATAFILE
    '/u01/oradata/$ORACLE_SID/retail_data01.dbf'  SIZE 500M
    AUTOEXTEND ON NEXT 100M MAXSIZE 2000M 
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO