Oracle Server Setup Guide for Business Objects
Planning
Business Objects Planning 10.8
Patents
Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects:
5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352.
Trademarks
Copyright
Third-party
contributors
Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered
trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names
mentioned herein may be trademarks of their respective owners.
Copyright © 2000-2006 Business Objects. All rights reserved.
Business Objects products in this release may contain redistributions of software licensed from third-party contributors.
Some of these individual components may also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required notices, can be found at:
http://www.businessobjects.com/thirdparty
Contents
Introduction
Oracle Versions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Intended Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Technical Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Support Contact Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Chapter 1: Configuring the Planning Database in Oracle
Creating a New Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Creating New Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
Creating Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Importing the Original Business Objects Planning Database . . . . . . . . . . . . . . . . . . . . . . .8
Archiving Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
Chapter 2: Additional Administration and Configuration Issues
Installing the Oracle Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Backing Up the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Exporting a Database for Support Purposes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Changing the Default Connection ID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Configuring Multiple Schema Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
Appendix A: Oracle Installation Parameters
Database Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
Oracle Server Setup Guide for Business Objects Planning 3
Contents
4 Oracle Server Setup Guide for Business Objects Planning
Introduction
This document provides information necessary for the setup and administration of an
Oracle® server and database for use with Business Objects Planning.
Oracle Versions
Business Objects Planning is supported for use with the following Oracle versions:
z Oracle8i (8.1.7)
z Oracle9i (Standard Edition Release 2 or Enterprise Edition, 9.2.0.4 or greater)
z Oracle Database 10g (10.2.0.1 or greater)
This document applies to all supported versions of Oracle. Differences in setup
requirements and recommendations are noted in the text.
Intended Audience
Installation, configuration and administration of the Oracle database is typically handled
by a trained Oracle database administrator in the organization’s Information Technology
department. Business Objects Planning Support is also available to assist you with issues
relating to use of Oracle with Business Objects Planning.
Technical Requirements
The recommended client and server hardware and software requirements are outlined in
the separately produced document entitled Business Objects Planning Technical
Requirements. Please contact the Business Objects Planning Support department or visit
www.mysrc.com
for a copy of the most recent version.
Support Contact Information
If you have any questions after consulting this document, please contact Business Objects
Planning Support. The contact information is listed in the following table by region.
Region Phone Email
Americas 1 800 544 3477 PBUSupportNA@businessobjects.com
Asia Pacific 65 64166567 PBUSupportAsia@businessobjects.com
Europe, Middle East and Africa 0800 389 4130 PBUSupportEurope@businessobjects.com
Oracle Server Setup Guide for Business Objects Planning 5
Introduction
6 Oracle Server Setup Guide for Business Objects Planning
Configuring the Planning Database in Oracle
This chapter discusses configuration issues relating to the use of Oracle with Business
Objects Planning. It is assumed that you already have an operational Oracle server.
The following basic steps are required to configure an Oracle system for use with
Business Objects Planning:
1. Create a new database. You can also use an existing database if the Business
Objects Planning schema fits into the existing database.
2. Create new users.
3. Create tablespaces.
4. Import a database provided by your implementation consultant.
Creating a New Database
You can create a new database for use with Business Objects Planning, or use an existing
database. Please consult Appendix A for a list of minimum sizing and configuration
settings. These settings should be used for any database of up to approximately 150,000
records. If your database is significantly larger than this, please contact Business Objects
Planning Support for assistance.
The SRCAdmin user relies on function-based indexes for optimal performance. To enable
function-based indexes, you must change the initialization file for the database as follows.
1. Add the following two lines to the bottom of the INIT.ORA file:
query_rewrite_integrity = trusted
query_rewrite_enabled = true
2. After adding the information, make sure to stop and restart the database for the
changes to take effect.
Creating New Users
The Oracle objects (tables, indexes, etc.) used by Business Objects Planning are owned by
a single user account, named SRCAdmin. To create this account, execute the script
CREATEUSERS.SQL. This script will be emailed to you by your Business Objects
Planning representative.
Note that this script does not contain the SRCAdmin password. Your Business Objects
Planning representative will provide you with the password.
Oracle Server Setup Guide for Business Objects Planning 7
Configuring the Planning Database in Oracle
1
Creating Tablespaces
mation, please see “Changing the Default Connection ID” on page 11 and “Configuring
Multiple Schema Instances” on page 12.
The script also creates an account called SRCView with a password readdata. This
account is a read-only account to the database, to allow power users to view table data as
read-only without granting the write/create/drop privileges required by the SRCAdmin
account.
Creating Tablespaces
To create the tablespaces required by Business Objects Planning, use the script named
CREATETABLESPACES.SQL. This script will be emailed to you by your Business
Objects Planning representative.
Note: It is possible to set up multiple schema instances within the Business
Objects Planning database. Likewise, you can override the SRCAdmin user
account if you want to use a different naming convention. For more infor-
Important: The SRCView account and password should only be given to users
who have full permission to view all data in the database.This account is not
required and can be removed if desired.
The CREATETABLESPACES.SQL script must be edited to replace the <File Location>
parameter with the path to the files in your environment. The tablespace size parameters
may be updated already. If not, change the size parameter <Size> with the sizes specified
in Appendix A. If your system will have over 150,000 records, please speak with your
implementation consultant regarding proper tablespace sizes.
The “SRC” tablespaces have been created to assist you in optimizing the performance of
Business Objects Planning. A brief explanation of each tablespace is shown below:
Tablespace Description
SRCTLARGE This tablespace contains only a few, large tables. A separate
tablespace is used for performance reasons.
SRCTSMALL This tablespace contains all of the remaining tables, whose sizes
are relatively small.
SRCILARGE This area is the corresponding index area for the larger tables.
SRCISMALL This area is the corresponding index area for the smaller tables.
SRCTemp This tablespace is used for temporary tables created and removed
by the Business Objects Planning program code.
Importing the Original Business Objects Planning Database
Your Business Objects Planning representative will provide you with a file containing an
export of the original database. Import this database into the new database you created for
use with Business Objects Planning. No special import settings are required – use all
default settings.
8 Oracle Server Setup Guide for Business Objects Planning
Archiving Files
Configuring the Planning Database in Oracle
Archiving Files
If the FROMUSER is different than the TOUSER, after restoring the Oracle database you
must run the following SQL query to recreate a required trigger. The TOUSER in this
example is “SRCAdmin”.
CREATE SEQUENCE "SRCADMIN"."TASKSEQ";
CREATE OR REPLACE TRIGGER "SRCADMIN"."SCHDTASKSB4INSTRIG"
BEFORE INSERT ON "SRCADMIN"."SCHDTASKS"
FOR EACH ROW
DECLARE
nId NUMBER;
BEGIN
SELECT TASKSEQ.NEXTVAL INTO nId FROM DUAL;
:new.TaskId := nId;
END;
/
COMMIT;
It is strongly recommended to disable archive logging for the Business Objects Planning
database. This feature is unnecessary for Business Objects Planning due to the nature of
updates to the database (low volume of large transactions that are easily repeatable).
1
If you choose to enable archive logging, the archive file sizes should be monitored
initially for disk space consumption. You should also schedule the automatic backup and
removal of the archive log files.
Oracle Server Setup Guide for Business Objects Planning 9
Configuring the Planning Database in Oracle
Archiving Files
10 Oracle Server Setup Guide for Business Objects Planning1Oracle Server Setup Guide for Business Objects Planning 11
Additional Administration and Configuration Issues
Installing the Oracle Client
Each workstation that will access Business Objects Planning using the Professional
Edition must also have an installation of the Oracle client (8, 9i, or 10g Runtime client as
appropriate). The Oracle client is not required on workstations that will only use the
Business Objects Planning web access options.
Note that the 10g Instant Client is not supported for use with Business Objects Planning.
The full client must be used.
If you did not specify a TNS Descriptor when installing the Professional Edition (or when
configuring Application Server, if applicable), you must ensure that the Oracle client is
correctly configured to reach the Business Objects Planning database instance via Oracle
Internet Directory, Oracle Names, or a TNSNAMES.ORA file.
Backing Up the Database
It is imperative to back up the SRCAdmin user account in coordination with your normal
backup activities. It is beyond the scope of this guide to discuss backup procedures for
various operating system environments.
Important: Most standard file system backups do NOT back up in-use files,
such as those associated with an Oracle database. You should configure a
process where the Oracle database is copied to a separate backup file, which
can then be backed up as normal (since it is not in use).
Exporting a Database for Support Purposes
Occasionally it may be necessary to send a backup of the Business Objects Planning
database to Support to diagnose issues you may be facing. When performing the export,
use the standard Oracle Export (EXP) client and send only the SRCAdmin user account.
Changing the Default Connection ID
By default, Business Objects Planning attempts to connect to the database as the user
SRCAdmin. To override the default, you must manually create a file with the new user
account name.
To change the default connection ID:
Additional Administration and Configuration Issues
2
Configuring Multiple Schema Instances
1. Create a new text file named CONINST.TXT in the root of your User directory. For
reference, you should also see a CONNECT.TXT file in the root of your User
directory.
2. Type the new name of the connection ID on the first line of the CONINST.TXT file,
then save the file. For example, you could designate a new name of OracleAdmin.
3. Make sure that all Oracle objects (tables, indexes, etc.) are owned by the new user
ID. For example, if the ID is changed to OracleAdmin, then the OracleAdmin user
must be the owner of all tables, indexes, views, triggers, sequences, etc., that
SRCAdmin had previously owned. Please consult your Oracle documentation for
information on how to change object ownership.
Configuring Multiple Schema Instances
In some Business Objects Planning configurations, it may be necessary to load multiple
schemas into the same SRCIA database.
Many Business Objects Planning installations are comprised of multiple systems – for
example, one BusinessObjects Budgeting system and one BusinessObjects Sales Planning
system. To accommodate multiple systems, you can use separate Oracle databases for
each system, or you can use the same SRCIA database with different schema instances for
each system. The latter approach would require you to setup two distinct connection IDs
(for example, user_bud and user_sales) for connection and ownership purposes.
To configure multiple schema instances:
1. Each system should have its own unique user directory with a CONINST.TXT file.
To set up each CONINST.TXT file, see “Changing the Default Connection ID” on
page 11.
For example, the User directory for the Sales Planning system should have a
CONINST.TXT created for user_Sales, whereas the Budgeting system should have
one created for user_Budgeting.
2. In addition to ensuring that all of the Oracle database objects are owned by the appropriate connection ID, you must set the proper database security settings for each
connection ID. Each connecting user must have the following permissions revoked:
z Delete Any Table
z Insert Any Table
z Lock Any Table
z Select Any Table
z Update Any Table
Revoking these rights ensures that the connecting user will only have access to the
designated schema for that user.
12 Oracle Server Setup Guide for Business Objects Planning
Oracle Installation Parameters
The table below details the installation parameters to use when configuring your Oracle
server and client workstations for use with Business Objects Planning. The values are the
same for each Oracle version except where specifically noted.
These values are the minimum recommended starting settings for a database. After
installation, the system should be tuned by an Oracle database administrator for optimal
performance.
Parameter Value
Database Settings
Database Structure Compatibility z Oracle 8i: 8.1.7 or higher
z Oracle 9i: 9.2.0.4 or higher
z Oracle 10g: 10.2.0.1 or higher
Character set WE8ISO8859P1
Database Name (SID) SRCIA
Concurrent users 25
Database primary use Multipurpose
Server Mode (Dedicated or Shared) Dedicated
Oracle options None
Control Files Recommend at least 3 control files on 3 separate
physical disks, if possible.
Control File settings:
Maximum Datafiles 25
Maximum Log Files 12
Maximum Log Members 2
Default tablespaces:
System 100 MB
Rollback (8i)
Undo tablespaces (9i or 10g)
Oracle Server Setup Guide for Business Objects Planning 13
500 MB
Oracle Installation Parameters
A
Tablespaces
Parameter Value
Temp 500 MB
Redo Logs (recommend 3) 25,000 KB each
Archive logging Off
Processes 30
Block Size 8,192 Bytes
Sort Area Size 2,048K
Large Pool Size Oracle 8i only: 614,400
Shared Pool Size
Block Buffers Oracle 8i only: 19,000
Log Buffer Size Oracle 8i only: 32,768
DB Cache Size Oracle 9i only: 200MB
PGA Aggregate Target Oracle 9i and 10g only: 50MB
SGA Target Oracle 10g only: 300MB
z Oracle 8i: 60MB
z Oracle 9i: 100MB
z Oracle 10g: N/A
Tablespaces
SRCILARGE 250 MB
SRCISMALL 200 MB
SRCTLARGE 500 MB
SRCTSMALL 500 MB
SRCTEMP 500 MB
14 Oracle Server Setup Guide for Business Objects Planning
Loading...