odeve guide 2

22
 ORACLE Development Guide Revised May 2006

Upload: thiraviam

Post on 04-Oct-2015

6 views

Category:

Documents


0 download

DESCRIPTION

dba documentation template

TRANSCRIPT

DBA STANDARDS DOCUMENT

28ORACLE - Development Environment Guide

ORACLE

Development Guide

Revised May 2006

1Introduction

1Purpose

1Audience

1Oracle Project Roles

1Database Administrator (DBA)

2Oracle Instances

2Development

2Test

2Training

3Production

3Emergency Fix

3Off-site

3Application Short Name

4Application Schemas

4Object Schema

4Application Schema

5Headstart Schema

5Conversion Schema

5Web Schema

6Oracle Object Standards

6Tablespaces

6Tables

6Standards

7Views

7Columns

8Integrity Constraints

9Indexes

10Sequences

11Database Triggers

11Procedures, Functions and Packages

12Synonyms

13Virtual Private Databases

13Roles

13Database Change Request (DCR)

15User Connection Logging

16Operations Support

16Backup and Recovery

16Source Backup System

16Availability of Oracle Environments

17Appendix A - Oracle 10g Supported Features

Introduction

Purpose

This document defines the standards, procedures and guidelines set by the Database Administration (DBA) group of the Information and Technology Management Branch (ITMB) for the construction phases of applications using the Oracle Database Management System (DBMS) at the Ministries of Education and Advanced Education.

The focus of this document is on technical issues related to the construction and implementation of Oracle based systems. Audience

This guide will be of interest to the following people:

SYMBOL 183 \f "Symbol" \s 10 \hIMB Business Analysts: when defining system requirements.

SYMBOL 183 \f "Symbol" \s 10 \hProgrammers: when creating or maintaining Oracle application systems.

Oracle Project Roles

Database Administrator (DBA)

SYMBOL 183 \f "Symbol" \s 10 \hReviews all physical data.

SYMBOL 183 \f "Symbol" \s 10 \hProvides consulting to application developers on issues such as:

SYMBOL 224 \f "Symbol" \s 10 \hOracle database design techniques

SYMBOL 224 \f "Symbol" \s 10 \hprogramming techniques

SYMBOL 224 \f "Symbol" \s 10 \happlication performance tuning

SYMBOL 224 \f "Symbol" \s 10 \hdata conversion and loads

SYMBOL 224 \f "Symbol" \s 10 \hrecovery of data after system, application and media failure

SYMBOL 183 \f "Symbol" \s 10 \hIdentifies and sponsors the development of commonly used functions/procedures that can be used by multiple application systems.

SYMBOL 183 \f "Symbol" \s 10 \hDefines and enforces all Oracle database object standards.

SYMBOL 183 \f "Symbol" \s 10 \hExecutes the DDL to define all Oracle database objects (except procedures, functions and packages) for all applications in all database environments (except any off-site environments and Ministry Development environments).

SYMBOL 183 \f "Symbol" \s 10 \hEnsures the integrity of all Oracle production data by:

SYMBOL 224 \f "Symbol" \s 10 \hdeveloping adequate database backup procedures

SYMBOL 224 \f "Symbol" \s 10 \hperforming recovery procedures after system or media failure

SYMBOL 183 \f "Symbol" \s 10 \hDefines and maintains developer Oracle user accounts and assigns Oracle roles to users.

SYMBOL 183 \f "Symbol" \s 10 \hEnsures a high level of availability for all Oracle environments.

SYMBOL 183 \f "Symbol" \s 10 \hConfigures tuning parameters for Oracle instances.

SYMBOL 183 \f "Symbol" \s 10 \hIdentifies the need for Oracle and related third party product installations on the server.

SYMBOL 183 \f "Symbol" \s 10 \hReviews and customizes Oracle product installations on the server.

SYMBOL 183 \f "Symbol" \s 10 \hPerforms Oracle capacity planning (disk space and memory utilization).

SYMBOL 183 \f "Symbol" \s 10 \hCreates application directories on the server.

Oracle Instances

An Oracle Instance contains one or more Oracle schema. Each instance is assigned a four character Oracle system identifier (SID). The system identifier is of the format EEEn where EEE is the environment alias name and n is a sequential number (e.g. DEV1).

Development

Instance Name:DEV2

The Development environment is to be used by Programmers for the development and unit testing of application components. This environment is not normally available to end-users.

Test

Instance Name:TST2

The Test environment is to be used for integration and acceptance testing by Programmers and end-users alike. This environment is separate from development in order to ensure that users can perform testing in a stable environment. The purpose of integration and acceptance testing is to ensure that major problems will be detected before systems are implemented in the production environment.

The only way components are created or updated in the Test environment is through a formal release from the Development environment. For instance, developers therefore cannot make ad-hoc changes to this environment that impact end-users in the process of acceptance testing.

Training

Instance Name:TRN2

The Training environment may or may not be used by a particular application system. Its purpose is to teach end-users how to use a new or changed application. This stable environment is to contain production-like data that can be updated without impacting the actual production application. The application components in the Training environment are normally exact copies of those in Production.

Production

Instance Name:PRD2

The Production environment is the "live" environment where the Ministry's business functions are performed. The Production environment differs from the other environments in the following ways:

SYMBOL 183 \f "Symbol" \s 10 \hTargets are established for system availability and performance.

SYMBOL 183 \f "Symbol" \s 10 \hIntegrity of data are carefully managed. For example, full point-in-time recovery of data are guaranteed.

SYMBOL 183 \f "Symbol" \s 10 \hChange to the environment is tightly controlled in order to meet targets for performance, availability and reliability.

SYMBOL 183 \f "Symbol" \s 10 \hComponents are created or updated in the Production environment only through a release from the Test environment.

Emergency Fix

Instance Name:EFX2

The Emergency Fix environment may or may not be used by a particular application system. Its purpose is to test patches that need to be applied into the Production environment for critical errors. This environment is to contain production-like data that can be updated without impacting the actual production application. The application components in the Emergency Fix environment are normally exact copies of those in Production.

Off-site

Consulting firms developing applications for the Ministry may create and manage their own "off-site" development environment. These environments will be constructed using the consulting firm's own hardware and software.

The Off-site environment is to be used by consultants (Programmers) for the development and unit testing of application components. Once unit testing is complete, the application should be migrated to the Ministry's Development environment. The consulting firm will be responsible for the migration of the application to the Ministry's Development infrastructure.

Application Short Name

The application short name is a three-character code that uniquely identifies the system within the Oracle Systems Environment at the Ministry of Education IMB. The application short name comprises the first three or four characters of the associated Oracle Designer application names.

Format:aaawhere:

aaa is the unique application short name

Length:3 characters

Example:HRS

CDM Ref;OMS-61605

Application Schemas

Each project will have a minimum of two (2) schemas and a maximum of five (5) schemas. Each schema will have specific purpose, with specific objects, and specific system grants and privileges. Any additional schemas or grant will need to be submitted to the DBA group for approval.

Object Schema

The object schema should contain all database objects for the application. Developers will not have access to this schema beyond the development environment. All changes to this schema beyond development will have to be done through the DCR process (* See Page 18*).

Objects within the Object schema:

Tables

Indexes

SequencesTriggers

Views

Constraints

Polices

Schema privileges

Create Session

Create Database Link

Create SequencesCreate Synonyms

Create Table

Create Trigger

Create View

Create Role

Naming Convention

Format:aaawhere:

aaa is the unique application short name

Length:3 charactersExample:HRSApplication Schema

The application schema should contain all application code. Developers will have access to this schema in all environments. Developers will do all changes to this schema and no DCR's are required.

Objects within the Application schema:

ProceduresPackages

Functions

Schema privileges

Create Session

Create Procedure

Create Synonyms

Naming Convention

Format:aaa_APPwhere:

aaa is the unique application short name

Length: 6 charactersExample:HRS_APPHeadstart Schema

The headstart schema should contain all headstart objects and data related to the application.

Objects within the Headstart schema:

Tables

Indexes

SequencesTriggers

Views

Constraints

Polices

Procedures

PackagesFunctions

Naming Convention

Format:aaa_HSTwhere:

aaa is the unique application short name

Length:6 charactersExample:HRS_HSTConversion Schema

The conversion schema should contain all code and objects required to convert archival data for the new application. Naming Convention

Format:aaa_CONVwhere:

aaa is the unique business application short name

Length:7 charactersExample:HRS_CONVWeb Schema

The web schema should contain NO objects or code. This schema is used as an entry point for applications being used over the internetNaming Convention

Format:aaa_WEBwhere:

aaa is the unique business application short name

Length:6 charactersExample:HRS_WEBOracle Object Standards

Tablespaces

Each application will have a maximum of six tablespaces: three for all application data and three for the application indexes. The amount of data stored in an object determines the tablespace to be used. See the guidelines in the table below to determine which tablespace should be used for the object:

Object SizeTablespaceObject Indexes

0K - 500Kaaa_small_dataaaa_small_idx

501K - 5 Maaa_medium_dataaaa_medium_idx

5.01M and upaaa_large_dataaaa_large_idx

All tablespaces will be created as locally managed

Naming Convention

Format:aaa_sss_tttt

where:aaais the application short name

sssis the size of tablespace

ttttis the tablespace type:

SYMBOL 183 \f "Symbol" \s 10 \hDATA

SYMBOL 183 \f "Symbol" \s 10 \hIDX

Length:13 characters maxium

Example:HRS_MEDIUM_DATA, HRS_SMALL_IDX

Tables

Standards

SYMBOL 183 \f "Symbol" \s 10 \hThe Oracle DBA is the only user who has the authority to create tables and related database objects (excluding procedures, packages and functions) in the TST, TRN, PRD and EFX environments. SYMBOL 183 \f "Symbol" \s 10 \hEach table must have its primary key defined using an integrity constraint. For more information on integrity constraints, please refer to the Integrity Constraint section.

SYMBOL 183 \f "Symbol" \s 10 \hTable Names are prefixed with the application short name of the application that is primarily responsible for the entry of the data contained in them. An example would be HRS_EMPLOYEE.

Guidelines

SYMBOL 183 \f "Symbol" \s 10 \hTables should always be in third normal form (3NF). An exception to this rule is when de-normalization is required for performance reasons. For example, a column may be duplicated from another table in order to avoid performing a specific costly table join. Approval from the DBA is required before performing any de-normalization.

SYMBOL 183 \f "Symbol" \s 10 \hUse the SQL COMMENT statement to document the purpose of each table and column. Limit the comment to 80 characters to comply with the Oracle Designer standard.

SYMBOL 183 \f "Symbol" \s 10 \hColumns that contain nulls should be grouped at the end of the table definition to conserve space.

Naming Convention

Format:aaa_tttttttttttttttttttttttttt

where:

aaais the business application short name

tt...tis a descriptive name for the table

Length:30 characters maximum

Example:CES_CONTRACT, HRS_EMPLOYEE, BIS_SPAN_MATERIAL

CDM Ref:OMS-42103, OMS-61561Views

Views need not be defined for every table. However, views may be created in order to:

SYMBOL 183 \f "Symbol" \s 10 \hEnable users and/or programmers to retrieve information from multiple tables without coding an SQL join. This is done by coding the join in the view definition.

SYMBOL 183 \f "Symbol" \s 10 \hLimit access to certain rows or columns in a table to authorized users.

SYMBOL 183 \f "Symbol" \s 10 \hAllow different column names to be used instead of those in the base table.

SYMBOL 183 \f "Symbol" \s 10 \hPresent data in a different format. For instance, a view could summarize data in a table.

Please note that even though integrity constraints are defined on the table/column level, they are still automatically enforced when using a view based on that table.

Naming Convention

Format:aaa_V_vvvvvvvvvvvvvvvvvvvvvvvv

where:

aaais the application short name

vv...vis a descriptive name for the view

Length:35 characters maximum

Example:HRS_V_ACTIVE_EMPLOYEE

CDM RefOMS-42202

Note: Aside from the "V_" prefix, the view name should follow the same naming convention as table names.

Columns

Standards

SYMBOL 183 \f "Symbol" \s 10 \hIntegrity constraints must be defined for all columns that are a primary key, unique (non-primary) key or a foreign key. Refer to the Integrity Constraints section for more details.

Guidelines

SYMBOL 183 \f "Symbol" \s 10 \hForeign key column names should be the same as the referencing primary key column names.

SYMBOL 183 \f "Symbol" \s 10 \hVARCHAR2 should be the default data type for character data. Do not use the CHAR data type. The VARCHAR2 datatype should only be used to store short character data. Character data that may exceed 1000 characters should be stored in a CLOB instead.

SYMBOL 183 \f "Symbol" \s 10 \hThe data type LONG should NOT be used.

Naming Convention

Format:cccccccccccccccccccccccccccccc

where:

cc...cis a descriptive name for the column

Length:30 characters maximum

Example:SALARY_AMOUNT, DEPARTMENT_CODE, EMPLOYEE_ID,

CLIENT_COMMENT_TEXT, UPDATE_USERID, UPDATE_TIMESTAMP

Notes:

SYMBOL 183 \f "Symbol" \s 10 \hColumns names are very similar to the attribute name in the Logical Data Model (e.g. UPDATE_TIMESTAMP vs. UPDATE TIMESTAMP

SYMBOL 183 \f "Symbol" \s 10 \hThe column name of a foreign key should match the corresponding primary key column name in the parent table. For example, the DEPARTMENT_CODE column in the HRS_EMPLOYEE table matches the name of the DEPARTMENT_CODE primary key of the HRS_DEPARTMENT table.

SYMBOL 183 \f "Symbol" \s 10 \hSuffix all column names with the data class.

SYMBOL 183 \f "Symbol" \s 10 \hAbbreviate column names only when the length is greater than 30 characters

Integrity Constraints

Integrity constraints are used by Oracle to prevent invalid data from being entered into tables

Standards

SYMBOL 183 \f "Symbol" \s 10 \hWhen creating a primary or unique key constraint, explicitly specify the tablespace name for the associated index .

Guidelines

SYMBOL 183 \f "Symbol" \s 10 \hCheck constraints may be used, but are not recommended. Although they do allow simple business rules to be defined, complex rules must still be coded in database triggers. In addition, check constraints must be re-coded for each column that contains a particular business rule. The recommended approach is to code all business rules in database triggers. Rules that may be referenced by multiple columns should be defined in a common stored procedure and called directly from triggers.

SYMBOL 183 \f "Symbol" \s 10 \h

Naming Convention

Primary Key Constraints

Format:aaa_tt tt_PK

where:

aaa application short name

tt..t is the table/view alias

Example:HRS_EMPL_PK

CDM Ref:42113

Unique Key Constraints

Format:aaa_ttttt_UK#

where:

aaa is table alias name

tttis the table/view alias

#is a sequence number starting at 1, added to the constraint name to make it unique in case there are more unique key constraints defined for the same table

Example:HRS_EMPL_UK1

CDM Ref:OMS-42112

Foreign Key Constraints

Format:aaa_ttt_tttt_nnnn_FK

where:

aaa is application short name

tttis the referance table or view

nnnnis the logical name, when there is more than one foreign key contraint definedfor the same referance table or view

Example:HRS_EMPL_ACTEMPL_FK

CDM Ref:OMS-45019

Check Constraints

Format:aaa_tt...t_nnnn_CH

where:

aaa is application short name

tt...tis the table or view alias

nnnnis the logical name indicating what the check contraint is about

Example:HRS_EMPL_HIRE_DATE_ON_WKDAY_CH

CDM Ref:OMS-42115

Note: The maximum length of constraint names is 30 characters. If necessary, names should be shortened to 30 characters using the same method as for column names.

Indexes

Indexes are automatically created for primary keys and unique keys when integrity constraints are created. Additional indexes may be required for a variety of reasons.

Standards

SYMBOL 183 \f "Symbol" \s 10 \hWhen creating an index, always explicitly specify a tablespace name.

Guidelines

SYMBOL 183 \f "Symbol" \s 10 \hUse the following rules to identify columns (other than primary and unique keys) that require an index:

SYMBOL 224 \f "Symbol" \s 10 \hcreate an index on frequently-used queries. In addition, queries should retrieve less than 15% of the rows in a table.

SYMBOL 224 \f "Symbol" \s 10 \hcreate an index on columns used in table joins (e.g. foreign keys).

SYMBOL 224 \f "Symbol" \s 10 \hdo not create an index on a column that has a large percentage of null values when there are no queries searching for non-null values.

SYMBOL 224 \f "Symbol" \s 10 \hsince indexes require considerable overhead to maintain, only create those that are currently needed. Additional indexes can be easily added as the need arises.

Naming Convention

Format:aaa_ttt_cc...c_IDX

where:

aaa is the application short name

ttt is the table/view alias

cc...cis the name of all columns in the index

Length:30 characters maximum

Example:HRS_EMPL_NAME_IDX

Sequences

Oracle sequences generate sequential numbers that may be used as unique primary key values for application tables. Without sequences, primary key values must be generated with application logic

Standards

SYMBOL 183 \f "Symbol" \s 10 \hUse the CACHE keyword on the CREATE SEQUENCE statement to specify the number of sequence numbers to cache. A value of 10-20 is normally sufficient.

SYMBOL 183 \f "Symbol" \s 10 \hEnsure that an appropriate MAXVALUE is always specified.

Guidelines

SYMBOL 183 \f "Symbol" \s 10 \hThe NOCYCLE option will be specified for a large majority of sequences. Rarely will applications allow the re-assignment of sequence numbers.

Naming Convention

Format:aaa_ttt_SEQ#

where:

aaa is the application short name

tttis the table/view alias

#is a sequence starting at 1, added to the sequence name to make it unique in case there are more sequences defined for the same table

Length:30 characters maximum

Example:HRS_EMPL__SEQ3

CDM Ref:OMS-42353

Database Triggers

SYMBOL 183 \f "Symbol" \s 10 \hApplications should not use LOGON TRIGGERS. The ministry currently utilizes a Single Sign On architecture for its databases.

Although there are many types of database triggers, only two will be regularly used.

SYMBOL 183 \f "Symbol" \s 10 \htriggers to enforce business rules ("before row" triggers). These will contain all of the business rules for an individual table.

SYMBOL 183 \f "Symbol" \s 10 \htriggers to maintain a de-normalized column ("after row" triggers). These will ensure that a de-normalized column is updated whenever the parent column is altered.

Triggers are stored in the Object schema since they are vital for database security and integrity. Any procedures, functions or packages required for the triggers will also be stored in the Object schema. Triggers are compiled by the DBA.

Standards

SYMBOL 183 \f "Symbol" \s 10 \hKeep triggers to a maximum of 60 lines. The best way to do this is to put the majority of trigger logic in called procedures and functions. In addition to improving performance this allows code to be re-used. Trigger size can also be reduced by separating triggers according to the event type (i.e. have a separate trigger for inserts, updates and deletes).

Naming Convention

Format:aaaaaaaaaa_eeeeeeeeeee_TR

where:

aa...ais the alias name of the table

ee...eare the events that cause the trigger to be fired.

Length: 30 characters maximum

Example:HRS_EMPL_AUDIT_TR

Procedures, Functions and Packages

Programmers have the ability to create and maintain Oracle stored procedures, functions and packages in all environments. However, all such code must be created in the Application (aaa_APP). Procedures, functions and packages coded for trigger logic can be stored in the Object schema. .A public or private synonym will be created so that the schema name will not have to be specified in application code.

Standards

SYMBOL 183 \f "Symbol" \s 10 \hIn error situations, invoke raise_application_error with the following parameters:

SYMBOL 183 \f "Symbol" \s 10 \hAn error number between -20000 and -20999. This number should be unique within each application. If more than 999 messages are required, the numbers will have to be reused and software components (e.g. SQL*Forms code) will have to parse the error text to distinguish individual errors.

SYMBOL 183 \f "Symbol" \s 10 \hAppropriate error message text beginning with the literal 'Error: ...'.

Guidelines

SYMBOL 183 \f "Symbol" \s 10 \hWhenever possible, avoid stand-alone procedures and functions. Instead, group related functions and procedures into packages.

Naming Convention

Packages:

Format:aaa_dd...d

where:

aaais the business application short name

dd...dis a description of the purpose of the code

Length:30 characters maximum

Example:HRS_FIELD_EDITS, MIN_DATE_ROUTINES, MYTEST_FIELD_EDITS

Procedures:

Format:aaa_dd...d

where:

aaais the business application short name

dd...ddescribes the purpose of the code in the context of the package

Length:30 characters maximum

Example:HRS_HIRE_EMPLOYEE, HRS_RAISE_SALARY

Functions:

Format:aaa_dd...d

where:

aaais the business application short name

dd...ddescribes the purpose of the code in the context of the package

Length:30 characters maximum

Example:HRS_HIRE_DATE, HRS_LEAP_YEARSynonyms

All references to tables, views, sequences, packages, procedures and functions must be unqualified. That is, the schema name should never have to be specified in application programs or ad-hoc queries. This will be accomplished by defining a public or private synonym for each object after it has been created. The public or private synonym will be identical to the name of the base object.

Naming Convention

Format:nnnnnnnnnnnnnnnnnnnnnnnnnnnnnn

where:

nn...n is the synonym for a base object

Length:30 characters maximum

Example:HRS_EMPLOYEE will be a public synonym for the table HRS.HRS_EMPLOYEE.

Virtual Private Databases

Virtual Private Database (VPD) are server-enforced, fine-grained access control, together with secure application context, which enable multiple customers and partners to have secure direct access to mission-critical data. Within a single database, the Virtual Private Database enables data access control by user with the assurance of physical data separation.

The Virtual Private Database feature provided fine-grained access control and application context. It secures data in the database by providing security at the row level, across the application, by attaching a security policy directly to a table or view.

Associating one or more security policies with tables or views enables the Virtual Private Database. Direct or indirect access to a table with an attached security policy causes the database to consult a function implementing the policy.

Standards

SYMBOL 183 \f "Symbol" \s 10 \hAll policies must allow access, to all data in all objects, to users with "EXP_FULL_DATABASE" role.

Example:IF DBMS_SESSION.is_role_enabled('EXP_FULL_DATABASE') THEN

l_where := '1 = 1';Roles

In this context, a role is a collection of Oracle object (and possibly system) privileges that provide access to an application system. Object privileges can be granted on tables, views, sequences, packages, procedures and functions.

Access to an application is controlled by:

SYMBOL 183 \f "Symbol" \s 10 \hcreating a set of user ids and roles

SYMBOL 183 \f "Symbol" \s 10 \hgranting privileges to roles

SYMBOL 183 \f "Symbol" \s 10 \hgranting roles to individual users.

Naming Convention

Format:aaa_jj...j

where:

aaais the business application short name

jj...jis the job description of the user.

Descriptions may include (but are not limited to):

SYMBOL 183 \f "Symbol" \s 10 \hSYS_ADMIN- user with R/W access to all tables.

SYMBOL 183 \f "Symbol" \s 10 \hUSER

- user with R/W access to most tables.

SYMBOL 183 \f "Symbol" \s 10 \hDEVELOPER- application programmer

SYMBOL 183 \f "Symbol" \s 10 \hMANAGER- more privileges than USER role.

SYMBOL 183 \f "Symbol" \s 10 \hDATA_ENTRY - less privileges than USER role.

Length:26 characters maximum. Oracle role names must be unique in their first 10 characters.

Example:HRS_SYS_ADMIN

Database Change Request (DCR)

Oracle Designer allows the developer of an application system to define a physical database design in a central repository. The physical design can then be used to automatically generate DDL statements to create the necessary database objects. If a developer manually codes DDL to create database objects, then these objects must be reverse-engineered into the dictionary.

DBA will generate all of the necessary DDL scripts from Oracle Designer to create the database objects for production.

Since Oracle Designer can easily regenerate DDL, a complex DDL source management system will not be used. The DBA may keep such DDL files on the system for informational purposes, but objects will always be altered or recreated from Designer.

Changes to tables, sequences, views, policies, indexes, triggers and constraints (the object schema) will be submitted to the DBA in a database change request (DCR). A DCR consist of a DCR form and all of DDL files required to alter the objects grouped into one sql block of code. DCR's will be reviewed by the DBA and then promoted to the next phase of the development cycle. DCR's will be implemented by the DBA in appropriate ministry change windows.

The developers or the application support team will implement changes to procedures, functions and packages (the Application Schema. These changes do not require a DCR process.

Naming Convention

Format:dcr_xxx.sql

where:

xxxis a unique file identifier (numeric retrieved from DCR table)

Length:8 characters (plus file extension)

Examples:DCR_001.sql

SYMBOL 183 \f "Symbol" \s 10 \hDCR_001.sql contains DDL to change a table in the HRS system and their associated integrity constraints.

User Connection Logging

Every application should maintain a log of user connections. This log can be used by operations support to determine the usage characteristics of the application.

The application log should, at a minimum, contain the date/time and userid for every connection made to the application. If the application authenticates users rather than using database authentication then the log should include both successful and failed login attempts and should indicate which are which.

Applications that use database authentication can implement this requirement by adding a login trigger. Applications that perform their own user authentication will need to add this requirement to their authentication processing.

Operations Support

Backup and Recovery

The Database Administrator will ensure that appropriate backup procedures are in place to protect application data against system, media and software failure. When such outages occur, the DBA will execute all necessary restart and recovery procedures. The DBA is responsible to ensure the integrity of all application data while maximizing the availability of the Oracle production environment.

The application data in the Oracle production environment is protected by both database backups and on-line/archived redo logs. This gives the DBA the ability to restore application data to any recent point-in-time.

Some applications also require the ability to restore data on a table-by-table basis. For instance, applications with batch update programs or those allowing ad-hoc deletions may need such protection. In these cases the DBA should be notified as soon as possible so that the additional backup and recovery procedures may be implemented.

Source Backup System

The UNIX system administrator has set up a procedure to back up all application source and executables.

If you would like to have a version of a file restored from tape, contact the Helpdesk for the appropriate application. The Helpdesk will pass on the request to the UNIX system administrator and inform her/him of:

SYMBOL 183 \f "Symbol" \s 10 \hthe file name (including the full path) to be restored

SYMBOL 183 \f "Symbol" \s 10 \hthe date the file should be restored from.

Availability of Oracle Environments

The Development, Test, and Production Oracle environments are normally available seven days a week.

Additional outages to these environments may be scheduled by the DBA, the UNIX System Administrator, or the Oracle Product Support person in order to perform maintenance and upgrades. The duration of the outage will be determined on an as-needed basis. These outages will occur during change control windows.

Appendix A - Oracle 10g Supported Features

The following table lists the features that are supported by the Ministry. Please consult the DBAs about any non supported features.

AreaMinistry Support

XML DBYes

JavaYes

Application Express (HTML DB)Yes

PAGE 17