Freeware Support Standards

Development, Integration and 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.
• 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.

Database 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.
• As insurance, a user may request an export of the application's database objects prior to testing of the cutting scripts in integration and production, 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 restore will be done from the most recent backup.
• Pre-notification of integration and production database schema changes must be communicated to cdfdb-support or d0db-support 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 document  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.
• 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.
• 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.

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.

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.

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 liaison.

Backup and 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.

24x7 Response

justification of a 24x7 database

1. there a real, calculable, financial loss to the laboratory if the database is offline, or
2. there legal repercussions to the laboratory if the database is offline

24x7 insures immediate attention to a database issue, and immediate steps to find a solution to that issue. In order to provide this type of support, requirements for 24x7 databases include:

User expectations of 24x7 database support are:

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.

Database 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.