Oracle Database Support Standards

Development, Integration and Production Environments

• A development environment will exist for each application. This environment will house the application's data files and the owner's schema 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 a  ddl script (as part of script testing). Thus, it must minimally be large enough to enable creation of the initial space 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 ddl scripts, not through the dba.
• The development, integration, and production databases will have the application data files 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

This entire section assumes work is done by the applications owner. This is not dba work, but needs to be clarified and standardized to protect the data and data integrity.

• 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 (applications person, not dsg) 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 a backup of the application's database objects prior to testing of the cutting scripts in integration and production, during which time the application's data files 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 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.

Data Integrity

Css-dsg does not take any responsibility for data integrity on freeware databases. This is the responsibility of the application owner.

Application Evolution

Css-dsg does not do application support on freeware databases. This is the responsibility of the application owner.

Monitoring

To date we have no monitoring tools available for freeware databases. We will continue to look at freeware monitoring products and make them available if/when we can.

Upgrades and Patches

With an mou, css-dsg will support patches and upgrades to freeware databases. Security patches need timely installations and users will be expected to accommodate that need or risk security shutting down the database until the patch is done. Patches will be applied to dev first, then int, then prod.

Upgrades will be applied as agreed to by the user and css-dsg, if dsg is administering the database.. It is mandatory that upgrades be done before decertification of the current version occurs. Css-dsg will not administer a decertified version of freeware.

Access Control

• By default, individual users in the development databases will be given connect and resource privileges by the application owner. DSG will establish an application and some semblance of granularity as to security for the application, including a group, access from applications. All individual access to applications on databases will be done through the application owner.
• 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. DSG is privy to NO application owner password.
• 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, if applicable. Grants to those roles will be maintained through the ddl.
• Individual users will be granted roles by the application owner, not the dbas.

Backup and Recovery

Backup and recovery on freeware databases comes down to a simple cold backup of the data files on the operating system. the responsibility for backup and recovery will be that of the application owner working with the system administrator.

24x7 Response

24x7 response to database issues cannot be accommodated due to the nature of the product. A freeware product has issues settled via email to the freeware product website. We can insure no further support than that. if a freeware database has a serious problem, it may be down for an extended and unknown period.

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