OVERVIEW: Coplink Deployment at TPD

1
OVERVIEWCoplink Deployment at TPD

• C. Boarman, AI LAB

2
Goals

• Finish Loading Stage in 10 Days
• Synchronization in 5 days
• Complete base deployment complete in 15 days

3
Overview

• Pre-deployment Tasks
• System Analysis
• Build Scripts and Procedures Review
• O/S Specific adjustments
• Deployment step-by-step
• Software Installation
• Database Creation
• Load System Objects
• Export/Import RMS tables
• Data Loading
• Refresh Deployment
• Post Database Deployment
• Applications Procedures
• OAS
• Java Interface

• Issues in Performance Tuning

• Issues in Backup Recovery

4
System Analysis

• Review Current System in terms of
• Disk/Space Allocation
• Memory Usage
• CPU Usage
• Compare HW/SW and Network environment from
  Development System to Production System

5
Build Scripts and Procedures

• Understand why sections are commented
• Logging and monitoring mechanisms are in place.
• Look for weaknesses - missing commits statements,
  naming mismatches, etc.

6
O/S Specific Adjustments

• System Initialization files
• Disk Allocation changes
• DBMS and System Objects
• Apply best practices

7
Begin Deployment - S/W Install

• NT OS (network configuration)
• Oracle 8 DBMS
• Oracle Application Server (OAS)
• Test Configuration...If OK Proceed.

8
Database Creation

• Run Scripts
• Create the database instance
• create system data files, tablespaces, rollback
  segments for DB instance
• Catalog.sql Catproc.sql - Data Dictionary
  additional DBMS objects

9
Load Coplink System Objects

• Tablespaces
• Schema Definition - Refresh
• Clusters
• Tables
• Trigger Procedures for Load
• Triggers
• Sequence
• Compile - load and update procedures

10
Export/Import RMS Tables

• Oracles Export/Import utilities - NO
• SQLLOADER

LOAD DATA INFILE INTO TABLE XNTNAMLOC FIELDS
TERMINATED BY X'9' TRAILING NULLCOLS (SA_XNTDESC,S
A_XLTLOC,LINK_DT,LOC_TYP) BEGINDATA NYS34651 Zf
PzM34408 HOME NYSx34651 ZfPzM34408 HOME NYT
k(18778 0100002107 HOME NYU34651 Zvwe4SYS H
OME NYV/34651 0100131883 HOME NYW34651 ZQdk
1dSYS HOME NYW39379 0500755465 HOME

• 15 control/data files.

11
Data Loading

• Pre-load Source Table (PK mappings)
• Build Source table Index
• Run Load procedures in Table Hierarchy
  (referential integrity)
• Run Denormalization Procedures
• Refer to Timing benchmarks and monitor
  Progress_Table.

12
Basic Load Procedure Design

• 1 - Cache all of Table PKs/FKs into memory (using
  PL/SQL Table)
• 2 - Start Loop
• 3 - Fetch new PKs from Source Table for PK FKs.
• 4 - Fetch Row
• 5 - Insert Row PK FKs in Snapshot tables -
  REFRESH components handle the rest.

13
Data Loading Troubleshooting

• If something goes WRONG
• Dont Panic and have heart attack
• Truncate/delete data in aborted table
• Reinitiate load procedure
• Investigate problem via
• Log tables
• System error messages
• Support web-sites (Metalink, Technet)
• Support Hotline

14
Data Loading Issues

• Exponential Loading times
• If on Disk
• Sequential search
• Solution B-Tree Index
• Crash
• Rollback Segments too small
• Increase segment / extent sizes
• Increase / create system data files

15
Data Loading Issues

• If too slow
• Operations on disk swap space.
• Increase SGA parameters
• DB BLOCK BUFFERS
• LOG BUFFERS
• SORT AREA SIZE
• SHARED POOL SIZE
• 3GB of RAM?!?! -gt 800MB

16
Data Loading Issues

• Caching into Memory Exponential
• Live with it
• Do not Cache
• Cache in segments

Ex. 2.5mil rows -gt 50k rows
17
A Word on PL/SQL

• Very ROBUST
• Structured ADA SQL Oracle Data structures
  Oracle Data types Packages
• Some limitations in Oracle data structures
  (PL/SQL Table - like Java Vector - not efficient)
• Limits low-level control through abstraction (ie.
  Java)

18
Refresh Deployment

• Load System Objects for Refresh
• Review critical operation code
• Review/Setup monitoring mechanisms
• Setup Refresh logging tables
• Invoke manually

19
Achieving Synchronization

• Snap_log 160k recorded transactions
• Processing at 15k - 20k per hour
• All 160k processed in 9 hours
• On average, Snap_log 25 per 3 min.
• Capacity 750 - 1,000 per 3 min.
• Stable? Set REFRESH to automatic mode!

20
Backup Recovery

• We should have done a back up prior to Data
  Loading stage

Ex. Load Procedure crashed. Database Instance
was down. DBMS was down. What happened? Start
Database. Database instance will not mount.
Discovered System Data file was
corrupt. Initiated Recovery. O/S lock on corrupt
file. Cause was Network Backup Application
locked file. Stopped backup operations.
Recovery Successful.
21
Post Database Deployment

• Compile Application Procedures
• Reconfigure OAS
• Recompile Java interface and install
• TestingIf OK DONE.