Transcript transparencies - Indico

Database Services
at Fermilab
Julie Trumbo
Fermilab, Batavia, IL USA
CSS-DSG
1
Topics




Services structure (development,
integration, production)
Scale of the service
Storage size in deployment
Hardware and Oracle technologies
used
2
Services Structure
The Fermi database group has a standard 3 tier
infrastructure for developing and deploying
production databases and applications. This
infrastructure provides 3 database instances,
development, integration and production. This
infrastructure is applicable to any application
schema, mission critical or not. It is designed to
insure development, testing, feedback, signoff,
and an protected production environment.
Each of these instances contain 1 or more
applications.
3
Services Structure
Db can be categorized in as following depending
upon the usage :
 PRODUCTION – A production database will be
used for fully working database applications and
will house only "real" data.
 INTEGRATION – An integration database will be
used for testing pre-production database
applications. Occasionally, the integration instance
may be refreshed with production data.
 DEVELOPMENT – A development database will
be used for database and application
development, testing of database features.
4
Services Structure
Development, Integration & Production
Environments




A development environment will exist for each application.
This environment will house the application's tablespaces
and the owner of the application. The application owner
will own the official version of the development tables.
The owner of the application will not be tied to an
individual person.
The development environment will contain logons for non
dba individuals. These logons will be used for
development and testing at the lowest levels, before
applications are user-ready.
An integration environment will exist and be used to test
the cutting scripts and application code before declaring
production. The integration database tables will be created
using the Oracle Designer ddl (as part of script testing).
Thus, it must minimally be large enough to enable
creation of the initial extents needed by the tables.
5
Services Structure
Development, Integration & Production
Environments




The integration and production databases will
contain logons for the application logons and dbas
only. Individual users without application-specific
roles will not have access.
The roles for an application will be added by a dba
working with the person acting as the application
owner for specifications. The dba will set up the
same roles in integration and production after the
roles in development have been tested and
validated. Grants to all roles will be established
through Designer ddl, not through the dba.
The development, integration, and production
databases will have the application tablespaces
named identically. This will provide for import and
export of data between the databases.
Replication of a production application implies
replication of the integration application as well.
6
Services Structure
Schema Evolution



There are 3 stages to a database schema evolution. They
take place first in the development database, then in
integration and finally in production.
Changes to the schema, and testing of this schema from
applications are initially made in the development
database. Once these changes have been made, a frozen
production release cannot be expected to work against the
development or integration databases.
At some point, the build managers will declare a code freeze,
tagging libraries, etc. Code frozen from the development area
will initially be pointed to the integration area for user testing.
Users may then request additional modifications to be done in
development and moved to integration code area for
additional user testing. When users are totally satisfied with
the integration environment, the production environment can
be established based on the integration environment. The
following 6 bullets describe a reiterative process by which a
cut to integration and then production can be established.
7
Services Structure
Schema Evolution






A user may request an export of the application's database
objects prior to testing of the cutting scripts in integration and
prodcution, during which time the application's tablespaces will
be in read-only mode to insure data consistency. This export,with
consistent=y, will allow quick, easy restoration of the database
objects if necessary. If no export is requested, the restoral will be
done from the most recent backup.
Pre-notification of integration and production database schema
changes must be communicated to user groups as appropriate.
A 'cutting script' will be used to modify schemas and make all
necessary changes to integration and then production database
environments. This script will act as record of all changes for
later review and be stored in a code library or other repository.
If errors occur, the application's database will be restored and the
script will be edited and re-run until the script runs successfully.
The error free cutting script will be checked into the code library.
This process will minimize down time against the production
database and insure a clean, error free run.
8
Services Structure
Schema Evolution



The initial integration data quite possibly will be an
export of the development database. When a large
enough sample of production data exists, integration
will be refreshed with some subset of production data,
if space allows.
All production database schema modifications will be
done through the use of scripts. There should be no
circumstance where structure is modified that cannot
be traced back to a person and a script.
A current version of the database's ddl (data definition
language) will be kept in a source code repository. This
ddl should reflect the production/integration database
structures. This ddl needs to be able to accurately
reproduce the schema without intervention.
9
Services Structure
Schema Evolution
Via Makefiles, the eventually cutting script will be versioned and look similar to:

<d0ora1> more convert.sql

prompt altering tables

@alter_tables.sql

<d0ora1> more alter_tables.sql

PROMPT ALTER TABLE DIMENSIONS TO ADD a column

ALTER TABLE DIMENSIONS ADD DIM_ACTIVE NUMBER(1,0)

/

PROMPT UPDATE DIMENSIONS table for DIM_ACTIVE as 1

UPDATE DIMENSIONS SET DIM_ACTIVE = 1

/

COMMIT;

PROMPT MAKE THE COLUMN AS NOT NULL

ALTER TABLE DIMENSIONS MODIFY DIM_ACTIVE NOT NULL

/

PROMPT Creating Trigger 'DIM_CREATES_BEF_I'

CREATE OR REPLACE TRIGGER DIM_CREATES_BEF_I

BEFORE INSERT

ON DIMENSIONS

FOR EACH ROW

begin

:new.create_date := sysdate;

:new.create_user := lower(user);

:new.dim_active := 1;

end;

/

SHOW ERROR
10
Services Structure
Application Evolution


Applications will follow the same 3 stages of evolution as the
database, development, integration and finally, production.
Applications will not be considered 'production' releases until






QA tests on integration show that database/application interaction
works as expected
Data written by applications moves as expected to replication test
database if application functions in a replicated environment.
Database roles work as expected within the context of the
application
Database schema evolution requirements in the previous section
are satisfied, ensuring that the production database will be in
expected state for new applications.
Applications will use a source code library for version control.
UPS/UPD/SRT will be used to support application
development and deployment.
11
Services Structure
Monitoring




Database(s) will be monitored using a commercial
monitoring tool. Currently, we are using Oracle's
Enterprise Manager. Databases will minimally be
monitored for space, database up/down
conditions, listener up/down conditions.
We will work with the users of the database to
monitor additional database, or system needs as
required by the user. The details are available in
the following document: OEM Planning
Database downtime can be expected in order to
schedule preventative maintenance.
*We also use an additional tool, Toolman, which is
home grown and maintained.
12
Services Structure
Access Control







By default, individual users in the development databases will be
given connect and resource privileges.
Application owners will be given connect, resource, create public
synonym, drop public synonym privileges.
There will be 1 logon to act as application owner. All other access
to the application's tables will be provided through roles and
grants to be used by application owners or other application
logons. Dbas can insist on seeing this structure if necessary. The
password to the application owner's logon must be protected.
Neither the application owner nor any database user with
insert/update/delete authority will share their passwords with
another person.
A read only account should exist for the application. This may be
optional. Any individual can use the read only logon.
Roles will be created by the dbas. Grants to those roles will be
maintained through Designer and the ddl.
Individual users will be granted roles by the dbas. Any application
roles granted to a user must be given to the dba through the
application owner liason.
13
Services Structure
Backup/Recovery





The development database will be backed up with hot
backups during the week. Additional disk space needs to
be allocated for archived redo logs and RMAN backup files.
The integration database will be backed up with hot
backups during the week. Additional disk space needs to
be allocated for archived redo logs and RMAN backup files.
The production databases will be backed up nightly, using
hot backups to minimize down time. Additional disk space
needs to be allocated for archived redo logs and RMAN
backup files.
A recovery test, using different types of failure scenarios,
will be performed monthly, using development, integration
and production databases.
Tape backups need to be coordinated with the system
administrator.
14
Services Structure
Space Requirements Planning



For integration and production, a space allocation
report provided through Designer, must be completed
and given to the dbas so space can be assessed.
Production and integration ddl must contain the
storage parameters as suggested by the space report
provided by Designer. No space will be allotted until the
space assessment has been furnished.
Production and integration ddl must be supplied to the
dbas, and used in the creation of all tables and indices.
These ddl need to reside in a code library repository,
and will be compared to the space requirements report.
Currently, there is a bug in Designer that attaches a
username to the .syn ddl. Until this bug is fixed, the
cdsddl.syn file must be checked to delete any reference
to a username.
15
Services Structure
Maintenance Scheduling


Database downtime can be
expected in order to schedule
preventative maintenance, security
or other patches/upgrades
necessary to keep the database in a
secure, supportable environment.
It is suggested that maintenance be
scheduled every 30 days, and used
as needed.
16
Service Scale




Database Support
System Support
Levels of Support
Shifts
17
Service Scale
Database Support
Basic Infra Structure
The Fermi database systems group (dsg)
has a standard operating procedure for
production databases. This will be
reviewed in future slides. Suffice to say,
mention of support for any production
database also includes 2 additional
databases for production’s support,
integration and development databases.
18
Service Scale
Levels of DB Support







Basic DB Support
Assist to keep instances up and running
during normal working hours.
Accounts and privileges.
Storage/space consulting support.
Query tuning and consultation as time
allows.
Database monitoring and alerts.
General consultation.
Higher priority given to crisis situations.
19
Service Scale
Levels of DB Support


High Availability Support
Install/patch/upgrade/migrate databases
on a certified platform/os version/
database configuration.
Schedule downtime for patches and
upgrades. Unless emergency, 2 weeks
notice for production downtime required.
One day monthly reserved for routine
maintenance. Development, integration
instances have no schedule, request 24
hour notice.
20
Service Scale
Levels of DB Support

High Availability DB Support
Full recovery and backup support from
disk. Tape recoveries to disk discussed
under system support.
24x7 support of data deemed mission
critical.
Accounts and privileges.
Storage/space support.
Query tuning and consultation.

Database monitoring and alerts.




21
Service Scale
Levels of DB Support
Support with MOU (memorandum of
understanding)
Relevant on an application(s) level only
 Full high availability support
 Full database design support including
Entity Relationship diagram.
 Close consultation with application groups
and users on design.
 Complete database ddl, dml, libraried.
 Full schema support for modifications.
22
Service Scale
Levels of System Support
Basic System Support
 Limited monitoring system
 Security issue notification
 Space threshold alerts
 Creation of account
This will be limited support by the shift
support group.
23
Service Scale
Levels of System Support
High Availability System Support


Install/patch/upgrade/migrate operating
systems on a certified configuration.
Schedule downtime for patches and
upgrades. Unless emergency, 2 weeks
notice for production downtime required.
One day monthly reserved for routine
maintenance. Development, integration
machines have no schedule, request 24
hour notice.
24
Service Scale
Levels of System Support
High Availability System Support
 Full recovery and backup support from tape
for os and database files.
 24x7 support of data deemed mission
critical.
 OS accounts and privileges.
 Storage/space support.
 Consultation with system issues.
 System monitoring and alerts.
 Security issues.
25
Service Scale
Miscellaneous
Log Book
An electronic log book or web page(s)
can be maintained to log the
activities or action taken on system
and databases.
MAILING LISTS
Mailing lists for db group and system
group are maintained.
26
Service Scale
Database Support
Database support at Fermi lab includes:
High Availability:
 4 Run II experiment databases, Oracle 9i
 1 large application with MOU on 3 databases
mission critical
24x7 support
 1 Oracle basic replication instance
 1 Oracle streams replication instance
 1 Equipment database, Oracle, with MOU
mission critical
24x7 support
27
Service Scale
Database Support
Basic Support:
 1 Run II experiment database
 Freeware database support
package new products
consultation to users
documentation
28
Production Database
Storage Sizes
Fermi’s production (high availability) experiment
databases sizes are (excluding dev&int):
 D0 offline 540G and growing 256 annually, 210 users,
including application owners and nonapp owners, ~75
roles, ~12 applications.
 D0 online < 50G
 CDF offline 166G, ~120 users including application
owners(4) and nonapp owners, ~30 roles, 4
applications.
 CDF online 200G ~230 users including application
owners(9) and nonapp owners, ~55 roles, 9
applications.
 Equipment < 10G ~400 users including application
owners(7) and nonapp owners, ~67 roles, 7
applications.
 Luminosity 365G growing 125G annually
 Minos <50G
29
Oracle Technologies
& Hardware
All experiment databases are running Oracle
9i with rman hot backup processes.
All experiment databases are running on
Solaris or Red Hat Enterprise Linux AS.
Per standards, all Oracle databases run on
Oracle certified platforms/os/db versions.
See the certification matrix at the Oracle
web site. Non certified configurations will
not be supported by the database
systems group.
30
Oracle Technologies
& Hardware
Our equipment database uses Oracle
tools such as forms and reports.
This is the only database with
dependencies over and above the
database. There could be argument
that other technologies would be an
improvement. Intentionally did not
tie the experiment databases to
Oracle tool sets.
31
Oracle Technologies
& Hardware
The experiment databases run on Sun
hardware, 64b os & 64b db.
Oracle Replica instances run on linux.
There is movement afoot, though
slowly, to migrate to a linux
environment. The first experiment
to be deployed on linux is minos.
32
Plans
What does the future hold?




Develop policy and strategy for tier0 to tier1
deployments.
Work on Oracle replication prototype between Cern and
Fermi, read only streams technology to start. Would
like to get a consistent and workable framework for
long term consistency. Use existing Fermi tools
available for streams implementation and support?
Platforms? Upgrade/patch procedures? Database
versioning? Etc.
Identify players and roles.
Look at freeware options, what is viable, what is the
policy/strategy? How does freeware fit into tier 2
strategy?
33