mission critical computing moving data and other planning considerations

MISSION CRITICAL COMPUTING

Moving Data and Other Planning Considerations


Overview

Repository and Schema Management

DATAEXP and DATAIMP Utilities

Planning Considerations

Managing User Accounts

Database Access Control


Lesson: Repository and Schema Management

Repository Overview

Repository Migration

Utilities used in Repository Migration

Comparing the Migration Process Between Siebel 7.x and Siebel 6.x


Repository Overview

Core of the Siebel application

Contains definitions for the physical, logical, and presentation layers of your Siebel implementation

Repository is manipulated using Siebel Tools

Siebel provides a “default” Repository and you have likely customized the Repository to meet your business requirements

Customizations may include schema extensions, new business objects, custom code (eScript or VBScript), or new screens/views


Repository Overview (…continued)

Logical schema is defined in the Repository

Repository includes a logical representation of Tables, Columns, and Indexes

Typically extend the schema by using Siebel Tools to modify the logical schema

New Columns are named X_… to avoid future conflicts. Definition stored in S_COLUMN

New Tables are named CX_… to avoid future conflicts. Definition stored in S_TABLE

New Indexes include …_X… somewhere in the name to avoid future conflicts. Definition stored in S_INDEX

Utility to synchronize the logical and physical schemas as part of the migration process


Repository Migration

Siebel provides utilities to migrate the Repository between Enterprises (ie., environments)

One Repository “master” – typically in the Development environment

Process migrates the Repository and not reference data (LOVs) or transactional data (Accounts and Contacts)

Customer determines source and target Siebel Enterprises when performing a migration

An Enterprise can have 1+ Repositories. Might keep multiple versions of Repository in PROD as a precaution


Utilities Used in Repository Migration

Imports entire Repository from a binary fileImports entire Repository from a binary fileImport

Exports entire Repository to a single binary fileExports entire Repository to a single binary fileExport

Synchronizes logical and physical schemasSynchronizes logical and physical schemasDDLSYNC

Increments the schema version numberIncrements the schema version numberBumpver

Process hasn’t really changed between Siebel 6.x and 7.x (only the utilities have changed)


Comparing the Migration Process Between Siebel 7.x and Siebel 6.x

DEV2PROD script in \bin folder of each Siebel Server

DEV2PROD not limited to migrating a Repository from DEV to PROD Enterprises – think of it as source target

Performs Export, Import, DDLSync, and Bumpver steps in a single script

Customer specifies source and target systems

DEV2PROD script in \bin folder of each Siebel Server

DEV2PROD not limited to migrating a Repository from DEV to PROD Enterprises – think of it as source target

Performs Export, Import, DDLSync, and Bumpver steps in a single script

Customer specifies source and target systems

Multiple GUI tools installed with the DB Server scripts

Export, Import, and DDLSync (includes Bumpver) are each done with separate GUI requests

DDLSync is with “Run Database Utilities” and includes Unicode and Non-Unicode schemas

Multiple GUI tools installed with the DB Server scripts

Export, Import, and DDLSync (includes Bumpver) are each done with separate GUI requests

DDLSync is with “Run Database Utilities” and includes Unicode and Non-Unicode schemas

Siebel 7.x

Siebel 6.x


Lesson: DATAEXP and DATAIMP Utilities

Overview of DATAEXP and DATAIMP

Review of DATAEXP and DATAIMP

Limitations of DATAEXP and DATAIMP

DATAEXP

Preparing for DATAEXP

DATAIMP

Considerations for Using DATAEXP and DATAIMP


Overview of DATAEXP and DATAIMP

DATAEXP and DATAIMP are Siebel utilities

Used to export (DATAEXP) and import (DATAIMP) data

Installed in <siebsrvr>\bin on every Siebel Server

Available on Siebel 5.x, 6.x, and 7.x

Additional options available in recent Siebel versions (e.g. 7.7)

You are indirectly familiar with them (hint: Repository import and export!)

Our primary tool to migrate non-Repository data from Oracle to SQL Server


Review of DATAEXP and DATAIMP

DATAEXP and DATAIMP are database-independent utilities

Easy to use

Operate from command line, so can be included in scripts

Can preserve Siebel ROW_ID values

Good performance overall, and can be setup to run parallel export and import tasks

You can use them to support your Platform Migration


Limitations of DATAEXP and DATAIMP

Cannot be used to move data between different versions of Siebel (e.g. 7.5.3 to 7.7)

May not provide optimal export or import performance for very large Tables


DATAEXP

You specify the desired Tables and rows at runtime

Resulting data is exported to a single, binary file

Some options not available prior to Siebel 7.7

dataexp /h to see all command-line switches

All data is preserved including ROW_ID, CREATED, LAST_UPD etc.


Preparing for DATAEXP

Reaffirm that source and target schemas are identical

Truncate EIM Tables where appropriate

Understand your data

Identify largest Tables by total rows and by total Mbytes

May need to temporarily collect statistics in Oracle

Exclude Repository Tables from DATAEXP

Determine if there is one process or many/parallel processes

Prepare a list of Table names for each process


DATAIMP

Reads the binary file produced by DATAEXP

Expects the identical Table structures in target database

Some options not available prior to Siebel 7.7

dataimp /h to see all command-line switches

All data is preserved including ROW_ID, CREATED, LAST_UPD etc.


Considerations for Using DATAEXP and DATAIMP

All non-Repository data in target/ new Enterprise database needs to be truncated prior to moving

No changes to the File System or its contents

Large and Chained rows > 8K (Oracle) may need to be handled with extension tables

Script to check consistency in Oracle prior to dataexp/dataimp

Large Table move planning must be done

DATAEXP and DATAIMP can be run in parallel


Lab 3: Running DATAEXP

In this lab, you will:

Prepare a Table List

Run DATAEXP

Review Counts


Lab 4: Running DATAIMP

In this lab, you will:

Run DATAIMP

Review Counts and audit with DATAEXP counts

Review Results in SQL Server

Review results online in Siebel application


Lab Discussion

Discuss the following:

User Accounts

Security


Lesson: Planning Considerations

Data Type Considerations

Repository and Physical Schema check

Large Rows

Code Page Change

Security


Data Type Considerations

Dates

The SQL Server earliest date is 1753-01-01 00:00:00, while the Oracle Server earliest date is January 1 4713BC 00:00:00

Therefore, ensure that dates fall into this range before importing

Bad Data in Oracle DB

Ensure data type consistency in source database


Repository and Physical Schema check

Before starting the migration, a check needs to be performed to ensure the physical database schema matches the Siebel Repository

This ensures the success of the Export/Import processes

Validation is performed with Siebel’s DBCHCK utility


Large Rows

Oracle Data Block size is typically 8K, and can “chain” a row across blocks if necessary

SQL Server Data Page size is 8K and cannot be extended, except for Text (LONG) Columns

Beware of a Table with multiple extension columns (X_…) of large width such as VARCHAR(2000)

Critical to do multiple test migrations using all data from existing Production database


Code Page Change

May have an existing Siebel database but only by using the wrong code page or collation sequence

Can use the DATAEXP/ DATAIMP process to migrate the data to a new “siebeldb”


Security

Manage access to database through user accounts

Control access to data through privileges and roles


Lesson: Managing User Accounts

User Accounts in Oracle and SQL Server

Creating User Accounts

Maintaining User Accounts


User Accounts in Oracle and SQL Server

In Oracle a username is database system wide. SQL Server uses login accounts to the database system and user accounts for the individual databases

Oracle usernames and SQL Server logins can be operating system authenticated or database authenticated. In addition SQL Server logins could be authenticated by the network domain

In SQL Server a user account has to be created in every database that a login needs access to and can be named different from the login name


Creating User Accounts

Comparison of the syntax to create a user and the associated properties

Description Oracle SQL Server

Account CREATE USER user_name sp_addlogin (SQL Server login) or sp_grantlogin

(Windows login) and sp_grantdbaccess (Database user)

Authentication IDENTIFIED {BY password | EXTERNALLY} sp_addlogin or sp_grantlogin

Default storage

DEFAULT TABLESPACE tablespace_name sp_addlogin

Temporary storage

TEMPORARY TABLESPACE tablespace_name Equivalent feature provided by TempDB

Storage allocation

QUOTA { n | UNLIMITED} ON tablespace_name N/A

Profile PROFILE profile_name N/A

RoleDEFAULT ROLE {NONE | role_name | ALL | ALL EXCEPT role_name}

Equivalent feature provided by fixed server role, database role and custom role creation.

Password PASSWORD EXPIRE Equivalent feature provided by windows authentication

Account ACCOUNT {LOCK | UNLOCK} Equivalent feature provided by windows authentication and sp_revokelogin


Maintaining User Accounts

Options in maintaining user accounts are –

Data dictionary information on users in Oracle can be viewed using DBA_USERS and DBA_TS_QUOTAS

SQL Server system stored procedures sp_helplogins, sp_change_users_login and sp_helpuser can be used to view login and user information

Operation Oracle SQL Server

Drop account DROP USER user_name [CASCADE]

sp_revokelogin (Windows login)

sp_droplogin (SQL Server login)

sp_revokedbaccess (Database user)

Change password

ALTER USER user_name IDENTIFIED BY new_password

sp_password 'old_password' , 'new_password', 'login_name'

Change Login for User N/A

sp_change_users_login 'action_type', 'user_name', 'login_name', 'password

Change storageALTER USER user_name DEFAULT TABLESPACE new_tablespace_name

sp_defaultdb ‘login_name’, ‘new_database_name’'


Lesson: Database Access Control

Managing Privileges

Managing Roles

User Roles


Managing Privileges

Oracle and SQL Server control access and activity within the database using system and object privileges

ALTER DATABASE and GRANT are examples of system privileges while object privileges can be SELECT, INSERT, UPDATE, DELETE, etc.

Oracle and SQL Server use GRANT statement to give privileges and REVOKE statement to remove privileges. In addition SQL Server has DENY statement to suspend privileges

Use the system stored procedure sp_helprotect to list permissions on objects and statements in SQL Server


Managing Roles

Oracle and SQL Server offer system defined roles with predefined privileges and user defined roles

The SQL Server system defined roles are of two categories – fixed server roles which are defined for the database instance and fixed database roles whose scope is limited to a database

sysadmin fixed server role is equivalent to Oracle’s DBA role

In Oracle there is a single DBA role that has database instance wide privileges spanning all schemas. In SQL Server administrative privileges can be limited to individual databases by the use of fixed database roles


User Roles

In Oracle and SQL Server the creation and use of roles differ

Both Oracle and SQL Server use GRANT and REVOKE statements to control permissions on rolesOracle and SQL Server offer ways to modify and drop user rolesRoles granted to Oracle users need to be enabled through the users login properties or using SET ROLE command. In SQL Server all granted roles are enabled by default except with application roles which need to be enabled using sp_setapprole

Operation Oracle SQL Server

Regular role CREATE ROLE role_name sp_addrole ‘role_name’, ‘owner_name’

Application role

CREATE ROLE role_name IDENTIFIED USING package_name

sp_addapprole ‘role_name’, ‘password’

Local role CREATE ROLE role_name IDENTIFIED BY password

External role CREATE ROLE role_name IDENTIFIED EXTERNALLY

External role CREATE ROLE role_name IDENTIFIED GLOBALLY


Review

Repository and Schema Management

DATAEXP and DATAIMP Utilities

Planning Considerations

Managing User Accounts

Database Access Control

mission critical computing moving data and other planning considerations

Documents