Oracle Database Environment Overview

Oracle Database Environment Overview

Subject:

Oracle Database Management

Date:

03/01/2006

Version:

1.0

Table of Contents Document Change Record......................................................................................ii Introduction.............................................................................................................iii Acceptance.............................................................................................................iv Oracle Architecture Overview.................................................................................5 Oracle Architecture Overview (continued) ..........................................................6 Database Object Naming Conventions..................................................................6 Definitions:................................................................................................................................. ..6 Application Abbreviation ....................................................................................................... ..6 Object Names........................................................................................................................... ...7 Table Names...................................................................................................... .........................7 Column Names......................................................................................................................... ...7 Primary Key Fields................................................................................................ ......................8 Index Names........................................................................................................... ....................8 Constraints............................................................................................................... ...................8 Views .................................................................................................................................. ........8 Other Column Types.................................................................................................................. ..8 Packages................................................................................................................................... ..9 Procedures and Functions......................................................................................................... ..9 Role Definitions....................................................................................................... ....................9 Schema Owner...................................................................................................................... ....10 Sequences....................................................................................................... .........................10 Triggers............................................................................................................... ......................10 Synonyms................................................................................................................................ ..10 Clusters and Materialized Views............................................................................................... .10 Other Objects....................................................................................................... .....................10

Database Password Management.......................................................................10 Oracle Database Inventory ..................................................................................13 Database Schema Change Request Form..........................................................18 Database Backup Schedule.................................................................................19

This document is property of DecisionOne, for internal use only.

Page: i

Document Change Record Change Summary Date 2/21/06

By

Description Create Document

This document is property of DecisionOne, for internal use only.

Page: ii

Introduction This document provides a general overview of the Decision One Oracle database environment, including information pertinent to the maintenance, support, and recovery of the database instances. The database object naming conventions, and software coding standards are described in detail to provide rules and guidelines for developing database related applications. Also included in this document is an overview of the password management package used to catalog the database schema passwords. Additionally, a sample of the database schema change request form as well as a snapshot of the database backup schedule, for the Decision One Oracle database environment, are offered to provide a look into the processes that keep the environment up-to-date and readily available.

This document is property of DecisionOne, for internal use only.

Page: iii

Acceptance SIGNOFFS IT: tbd Signature: 

Date:

ACCEPTED BY: Project Sponsor Signature: 

Date:

Project Sponsor Signature: 

Date:

Project Sponsor Signature: 

This document is property of DecisionOne, for internal use only.

Date:

Page: iv

Oracle Architecture Overview Production – Auburn Hills, MI

PSDBABH002 IP 10.8.10.21 Model Sun V440 CPU 1.062Ghz(2) OS Solaris 2.9 RAM 12GB DISK 267GB RAID Instance Version HR80PR ODSP01

V9202(64) V9205

PSDBABH003 IP 10.8.10.23 Model Sun V440 CPU 1.062Ghz(2) OS Solaris 2.9 RAM 12 GB DISK 360GB RAID Instance Version FS80PR

V9202(64)

This document is property of DecisionOne, for internal use only.

PSDBABH004 IP 10.8.10.26 Model Sun V440 CPU 1.062Ghz(2) OS Solaris 2.9 RAM 16GB DISK 525GB RAID Instance Version CRMP01 ECMP01 TAPSP01

V8174 V8174 V8174

PSDBABH005 IP 10.8.10.28 Model Sun V440 CPU 1.062Ghz OS Solaris 2.9 RAM 8GB DISK 1100GB ?? Instance Version ASPTP01 CLKANP01 CLKOSP01 CDATAP01 CPMP01 D1MIP01 EINSP01 EWFMP01 EXCHP01 PROD RLTMP01 RPTP01

Page: 5

V9205 V8174 ?? V8174 V9205 ?? V9205 V8174 V8174 V9205 V8174 V8174 V9205 V8174

GP SQL SERVER

Oracle Architecture Overview (continued) Development / Test – Devon, PA

DSDBFRA002 IP 10.8.5.44

Model Sun V440 CPU 1.062Ghz(2) OS Solaris 2.9 RAM 8GB DISK 400GB RAID Instance Version D1MID01 V8174 EINSD01 V8174 EXCHD01 V8174 DH8QA V9202(64) HR8DM V9202(64) HR8DV V9202(64) HR8QA V9202(64) ODSD01 V9205 CLFYDM11 V8174 HELPD01 V8174 HELPQ01 V8174 HELPT01 V8174 HELPU01 V8174 TAPSD02 Definitions: V8174

DSDBFRA003

DSDBHQS001

IP 10.8.5.45

IP 10.8.5.22

Model Sun V440 CPU 1.062Ghz(2) OS Solaris 2.9 RAM 8GB DISK 400GB RAID Instance Version ASPTD01 CDATAD01 CLKAND01 CLKOSD01 CPMD01 EWFMD01 FS8DM FS8DV FS8QA

V9205 V9205 V8174 V8174 V9205 V9205 V9202(64) V9202(64) V9202(64)

Win NT Box USAHSDSCN011 IP 10.8.2.202

PSDBHQS001 (RMAN) IP 10.8.2.79

Model Sun E3500 CPU 400Mhz(4) OS Solaris 5.9 RAM 3GB DISK 160GB Instance Version

Model unknown CPU unknown Win 2000 sp4 RAM 785 MB DISK 25GB Instance Version

Model Sun E3500 CPU 336Mhz(2) OS Solaris 2.9 RAM 2GB DISK ~50GB Instance Version

D1MIT01 EINST01 EXCHT01 RPTD01 TEST

AWS

RMONP01 RMONP09 OEMP09

V8174 V8174 V8174 V8174 V8174

V8160

V8174 V9205 V9205

Database Object Naming Conventions

To move to Auburn Hills (When – TDB)

Application Abbreviation An application abbreviation is a three-character code related to a specific application that prefixes that application’s database

This document is property of DecisionOne, for internal use only.

Page: 6

objects as defined in this document.

Note: Throughout this document application abbreviation will be abbreviated as “aa”. Object Names In general objects names should be kept as short as possible without losing their meaning. This will accommodate the prefixes, and suffixes defined below. Example: A table that contains employees could be shortened to EMPS. Object Names Containing DecisionOne If an object name will contain the company title “DecisionOne”, then DecisionOne” will be shortened to “D1”. Table Names  Table names are plural.  Table names will be prefixed with the application abbreviation.  Underscores will be used to separate words in the table name rather than spaces.  If a table name contains several words then only the last word will be pluralized. Column Names  Columns will be unique within the database schema, except in the case of foreign key reference columns (columns used in the child table of a foreign key relationship may use the name of the primary key column(s) that make up the parent key of the parent table).  Column names will be singular.  In cases of denormalization the column name may be made plural. The DBA assigned to the project will make this change when it is appropriate.

This document is property of DecisionOne, for internal use only.

Page: 7

Primary Key Fields  Primary key fields will be indicated by a postfix of _pk.  The name of the primary key will be the singular form of the table name (e.g. The table emp will have a primary key named emps_id_pk). Index Names  Name the primary key index as __pk. (e.g. The table emps will have a primary key index called hrs_emps_pk)  Name a unique key index as __uk.  Name a non-unique key as __ak.  When more indexes are added to the table, append a numeric to the name (e.g. __[uk | ak]_) Constraints  Primary and unique constraints will be explicitly named; they will not use a system-generated name.  Name the Primary Key Constraint as __pk (e.g. the table emps will have a primary key constraint named hrs_emps_pk).  Name a Foreign Key Constraint referencing a single column primary key as __fk.  When more foreign keys are added to the table append a numeric to the name (e.g. __fk).  Check constraints will follow a naming standard, and will not be created using a system-generated name. Views  View names are plural.  View names will be prefixed with the application abbreviation.  Column names within views are singular except in the case where they have been denormalized for performance.  Underscores will be used to separate words in the view names, rather than spaces. Other Column Types The table shown below defines the suffixes that are to be used based on expected content.

This document is property of DecisionOne, for internal use only.

Page: 8

Column Contents Boolean Values Codes Address Line One Address Line Two City Names U.S. States U.S. Zip Codes Nine Digit U.S. Zip Codes +4 Section of U.S. Zip Codes Monetary Amounts

Suffix _tf _cde Addr1 Addr2 _cty _st _zip _zip9 _zip4 _amt or _usd

Packages  Packages will be prefixed by the three character application abbreviation.  Packages will have _pkg appended to their names. Procedures and Functions  Stored code defined in a package does not need to be prefixed with the application abbreviation.  Stored code not contained within a package will be prefixed with the application abbreviation. Role Definitions  Role names will begin with the application abbreviation. (i.e. _)

This document is property of DecisionOne, for internal use only.

Page: 9

Schema Owner  The schema owner name should be a combination of the application abbreviation and the word “owner” (e.g. The HR schema owner would be hrs_owner). Sequences  Sequence names will be prefixed by the three-character application abbreviation followed by the name of the table they are generating a key for and append _seq to the table name (i.e. __seq). (e.g. A sequence on the table emps will be named hrs_emp_seq) Triggers  Trigger names should begin with the application abbreviation followed by a meaningful name and finished with the trigger type (e.g. A “before insert for each row” trigger would be named _<MeaningfulName>_bifer). Synonyms  Synonym names should begin with the application abbreviation.  Synonyms’ names should be identical to the objects they map to unless a valid reason can be presented for breaking this rule. Clusters and Materialized Views  Clusters and Materialized views will adhere to the naming standards for tables. Other Objects All independent objects created in the database must be preceded with the application abbreviation. The DBA team will name objects not currently defined in this document, but required by the application. The new standard will be added to this document.

Database Password Management The passwords needed for access to the each of the databases are stored using an Application called KeePass. The KeePass application can be found on the Q: drive (Pnfphqs001\Data\Is\Shared) under the “DBA Admin/passwords” folder. In order to obtain the password lists, run KeePass and select “open database” from the file menu.

This document is property of DecisionOne, for internal use only.

Page: 10

When prompted for the filename of the database, select the database.kdb file. At this point, you will be prompted to enter a password, which is required for access to the password list. Upon entering the correct password, a tree diagram appears, displaying all of the servers that can be expanded to reveal the passwords for each of the Decision One databases(as shown below).

This document is property of DecisionOne, for internal use only.

Page: 11

This document is property of DecisionOne, for internal use only.

Page: 12

Oracle Database Inventory Hostname IP Address Operating Processor( Physical System s) Ram

SID

Version Schemas

Application

Application Contact

HR80PR

9.2.0.2 notesusr (64-bit)

HR 818 Migration Datbase

Wayne Clarke

odsp01

9.2.0.5

frdba odsrpt

Operational Data Store Paul Adcock

ncsystem ncstartup

Tally (Asset Management)

Paul Adcock

Production Databases: PSDBABH002

10.8.10.21

Solaris 2.9

1.062Ghz(2)

12GB

PSDBABH003

10.8.10.23

Solaris 2.9

1.062Ghz(2)

12GB

FS80PR

9.2.0.2 sysadm (64-bit) people ps

FS 818 Migration Database

Sally Ann Lombardo

PSDBABH004

10.8.10.26

Solaris 2.9

1.062Ghz(2)

16GB

crmp01

8.1.7.4

crystal seebeyond01 cprclrfy clarify

Clarify Help Desk

Paul Adcock

ecmp01

8.1.7.4

ecm dbapp ecmp01

Advise Database

Jim Cartier

tapsp01

8.1.7.4

sa

eSupport HelpDesk

Anand Subramanian

ewfmp01

9.2.0.5

oracle9i tcsadmin s31019 ccops dscattergood s31005

cpmp01

9.2.0.5

cpmdba cpmrpt devp

PSDBABH005

10.8.10.28

Solaris 2.9

1.062Ghz(2)

12GB

Paul Adcock

Comprehensive Credit Card

Paul Adcock

Hostname IP Address Operating Processor( Physical System s) Ram

SID

Version Schemas

Application

Application Contact

PSDBABH005 (continued)

ASPTP01

9.2.0.5

aspect wwwaspt asptuser dwp devp

Collection Point for call-center calls

Paul Adcock

cdatap01 9.2.0.5

calldata aspect

Calldata for IBM ODCIC Paul Adcock

rltmp01

devp dbapp

Realtime Monitoring dev/test system

Paul Adcock

clkanp01 8.1.7

clkdba w5admin webczcs dbapp webuser webbatch

Field Scheduling (Click) Analyzer

Jim Cartier

clkosp01 8.1.7

clkdba w6admin webczcs webuser webbatch

Field Scheduling (Click) Service Optimizer

Jim Cartier

d1mip01

8.1.7.4

dbapp

D1 to EDS interface

Jim Cartier

exchp01

8.1.7.4

dbapp

eXchange database

Jim Cartier

einsp01

8.1.7.4

dbapp

eInsight

Jim Cartier

prod

8.1.7.4

billadm billing ctxsys bgobran

RSO database

Cleatus Davis

rptp01

8.1.7.4

clarify dwpdba

Clarify Reporting

Cleatus Davis

rmonp01

8.1.7.4

rman mon wwwmon

8.1.7 RMAN Database

Duane Wilcox Lorraine Crawford

rmonp09

9.2.0.5

rman mon wwwmon

9.2.0 RMAN Database

Duane Wilcox Lorraine Crawford

PSDBHQS001

10.8.2.79

Solaris 2.9

336Mhz(2)

2GB

9.2.0.5

Hostname IP Address Operating Processor( Physical System s) Ram

SID

Version Schemas

PSDBHQS001 (continued)

oemp09

9.2.0.5

rmont01

8.1.7.4

Oracle 8.1.7 RMAN

Duane Wilcox Lorraine Crawford

rmont09

9.2.0.4

Oracle 9i RMAN

Duane Wilcox Lorraine Crawford

oem9d01

9.2.0.4

Oracle 9i Enterprise Manager Repository

Duane Wilcox Lorraine Crawford

d1mid01

8.1.7.4

dbapp

D1 to EDS interface

Jim Cartier

exchd01

8.1.7.4

dbapp

eXchange database

Jim Cartier

einsd01

8.1.7.4

dbapp

eInsight database

Jim Cartier

odsd01

9.2.0.5

odsdba frdba odsrpt odsapp

Operational Data Store Paul Adcock

HR8DM

9.2.0.2

sysadm dbapp

Peoplesoft 8 HR

Wayne Clarke

HR8DV

9.2.0.2

sysadm dbapp

Peoplesoft 8 HR

Wayne Clarke

HR8QA

9.2.0.2

sysadm notesusr

Peoplesoft HR 818

Wayne Clarke

DH8QA

9.2.0.2

sysadm decision

Peoplesoft HR 818

Wayne Clarke

clfydm11 8.1.7.4

sa

Clarify v11.5

helpd01

sa

Clarify (Sized for Demo)

sdrdev1

204.170.43.12 Solaris 2.8

248Mhz(6)

4GB

Application

sysman Oracle Enterprise reports_user Manager Repository

Application Contact Duane Wilcox Lorraine Crawford

Development Databases: DSDBFRA002

DSDBFRA002 (continued)

10.8.5.44

Solaris 2.9

1.062Ghz(2)

8GB

8.1.7.4

Anand Subramanian

Hostname IP Address Operating Processor( Physical System s) Ram

DSDBFRA003

10.8.5.45

Solaris 2.9

1.062Ghz

8GB

SID

Version Schemas

Application

helpq01

8.1.7.4.0 sa seebeyond01 clarify

Clarify v11.5 Anand Subramanian (Sized for Production)

helpt01

8.1.7.4

sa seebeyond01 clarify

Clarify Anand Subramanian (Sized for Production)

helpu01

8.1.7.4

sa clarify

Clarify Anand Subramanian (Sized for Production)

tapsd02

8.1.7.4

sa

TAPS Clarify v11.5 Anand Subramanian (Sized for Production)

clkdba w6admin

Field Scheduling (Click) Analyzer

Jim Cartier

Field Scheduling (Click) Service Optimizer

Jim Cartier

clkand01 8.1.7.4

clkosd01 8.1.7.4.1 clkdba w6admin webczcs webuser webbatch

DSDBFRA003 (continued)

Application Contact

ewfmd01

9.2.0.5

notfydbowner Work Force Management Paul Adcock tcsdbowner tcsadmin s31019 ccops dscattergood s31005

cpmd01

9.2.0.5

cpmdba cpmrpt devp

Comprehensive Credit Card

Paul Adcock

asptd01

9.2.0.5

aspect wwwaspt asptuser

Collection Point for call-center calls

Paul Adcock

cdatad01 9.2.0.5

calldata aspect

calldata for IBM ODCIC Paul Adcock

rltmd01

9.2.0.5

devp

Realtime Monitoring dev/test system

Paul Adcock

FS8DM

9.2.0.2

dbapp

Peoplesoft 8 Financials

Sally Ann Lombardo

Hostname IP Address Operating Processor( Physical System s) Ram

DSDBHQS001

10.8.5.22

Solaris 5.9

400Mhz

3GB

SID

Version Schemas

Application

Application Contact

FS8DV

9.2.0.2

sysadm

Peoplesoft 8 Financials

Sally Ann Lombardo

FS8QA

9.2.0.2

sysadm

Peoplesoft 8 Financials

Sally Ann Lombardo

einst01

8.1.7.4

ex_admin

See Beyond Database

Jim Cartier

excht01

8.1.7.4

ex_admin

See Beyond Database

Jim Cartier

d1mit01

8.1.7.4

intf

EDS/D1 data interchange

Jim Cartier

RPTD01

8.1.7.4

billing Clarify Reporting cis_admin clarify_user docuser reports

test

8.1.7.4

test database

Cleatus Davis

Cleatus Davis

Database Schema Change Request Form Below is an example of the Database Schema Change Request Form. This form must be submitted to an application DBA whenever a change needs to be applied to any production database object such as a table, index, constraint, trigger, stored procedure, view, or generally any object that can be created, altered, truncated, or dropped. In development environments, changes to all of the aforementioned objects will require for this form to be completed and submitted to an application DBA, with the exception of stored procedures which may often require several attempts before compiling.

Database Backup Schedule Shown below, is a sample sheet from the database backup schedule. The full schedule is maintained in a Microsoft Excel Spreadsheet, and lists both the scheduled activities with details about the backup configuration for each of the databases of the Decision One Oracle Environment.