ICCS.DB.040 - Physical Database Design
OMG = Oracle Method Guideline
OMS = Oracle Method Standard

General
OMG-10042: Document any unusual design decisions including the reasons in the Notes property of the respective object.
Rationale This helps your team members understand the decisions that have been made. It is also valuable information for anyone performing QA on the project.

OMS-61518: When a secondary access element or secondary access controlled element (SAC) is deleted, this should be recorded in the notes of the primary access element or Primary access controlled (PAC) element.
Rationale Analysts or developers might want to know what happened to the SAC. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.
Example The deletion of an attribute is recorded in the notes of the entity.

OMS-61537: Document any deviations of the CDM Standards in the respective Notes, including the OMS Standard code and the reason.
Rationale Documenting these deviations helps your team members understand the decisions that have been made. This is also valuable information for anyone performing QA on the project.

Note: If you deviate from any OMS standards on a project level, do not include the deviation for every object. If there is only one Application System involved, then document these deviations in the Description property of the Application System. When there are more Application Systems involved, then it is recommended to document the deviations in a separate document.
Application Systems
Notes
OMS-61517: Record the deletion of primary objects such as entities, modules, etc. as revision history in the Notes of the Application System. Add new entries on top.
Rationale Analysts or developers might want to know what happened to the object. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 12-11-1999 S. Muller Removed function 31
and module ASP0050F
1.0 06-07-1998 L. Jellema Removed entity STATUS

1.1
----
Removed function 31 and module ASP0050F because their functionality
was dropped as result of tighter planning.

1.0
----
Removed Entity STATUS, functionality now in domain STATUS

Storage Definitions
Storage Label
OMS-61592: Use the following naming convention for the Storage Label:
[application code]_[tablespace name]_STOR
Rationale The storage is the default for a tablespace. This naming convention makes it immediately visible for which tablespace.

Initial Extent
OMS-61593: Choose one of the following initial extent sizes:
160K
5120K
160M
Rationale This is following the SAFE Standards. This is to avoid free space fragmentation.

Min Extents
OMS-42663: Always specify 1 for the minimum number of extents to be allocated, with the exception for Rollback Segments. Use 10 for Rollback Segments.
Rationale This is following the SAFE Standards. This is to avoid free space fragmentation.

Max Extents
OMS-61594: Do not enter Max Extents for Storages.
Rationale This prevents errors due to the fact that the maximum number of extents has been reached.

Percentage Increase
OMS-42665: Always specify a 0 for the percentage by which the following extent will grow over the last extent allocated.
Rationale The use of Percentage Increase causes the Oracle Server to allocate extents of different sizes in the same tablespace, causing fragmentation.

Comment
OMS-42666: Enter a short sentence indicating the use of this storage definition.
Rationale This makes obvious what is the use of the storage definition.

Table Definitions
Start Rows
OMS-42107: Define the expected initial number of rows in a table as estimated for the Production environment. Specify, if necessary, what the source for this information is in the Notes, using the keyword NO OF ROWS START.
Rationale This information is required for determining the physical requirements of the production environment.
Example BALDRICH 12-NOV-1995 11:00 NO OF ROWS START
Based on a count of the number of records in file
FGM887 after eliminating duplicates

End Rows
OMS-42108: Define the expected final number of rows in each table. Specify, if necessary, the source for this information in the Notes, using the keyword NO OF ROWS FINAL.
Rationale This information is required for determining the physical requirements of the production environment.
Example BALDRICH 12-NOV-1995 11:30 NO OF ROWS FINAL
File FGM887 grew with an average of 15,000 records a year for the last five years. Taking duplicates entries into account, the table will grow to 1,000,000 rows in the first year.

OMS-42122: Define the expected final number of rows as estimated for the Production environment based on a fixed point in the future; for example one year. Use this point, called the Database Sizing Horizon, consistently for all table definitions of the application.
Rationale This provides consistency throughout your estimating.

OMS-42123: If the sizing estimation point for the Table differs from the standard Database Sizing Horizon, as defined for the Application System, specify the Table's sizing estimation point in the Description for the Table.
Rationale This makes it clear what is the estimating point when it differs from the default.

Columns
Initial Volume
OMS-42276: Define the initial volume for each table column; 100% for non-optional columns.
Rationale This is required to perform proper sizing.

Final Volume
OMS-45005: Define the final volume for each table column; 100% for mandatory columns.
Rationale This is required to perform proper sizing.

Databases
Comment
OMS-42607: Enter a short sentence indicating the use of this database.
Rationale This makes it obvious what is the use of the database.

Oracle Databases
OMS-42640: Define at least the following tablespaces:
Tablespace Description
SYSTEM data dictionary segments only
TEMP temporary segments only
RBS rollback segments only
TOOLS general-purpose tools only
USERS default tablespace for users with resource privilege
Rationale This is according to the OFA standards.

Attention: The SYSTEM tablespace is automatically created on database creation.
Name
OMS-42600: Although the database name can be up to eight characters, you should limit its length to four characters.
Rationale This is the OFA standard for database names. It results in shorter paths for all datafiles.

Reuse Control File ?
OMS-42601: Do not reuse any existing control files.
Rationale In this way you avoid inadvertently losing a complete database by destroying a set of existing control files.

Max Data Files
OMS-42605: The maximum number of data files accessible to your instance is operating system specific. Enter the maximum number for your platform.
Rationale This allows for future growth.

Archive Redo Files ?
OMS-42603: Set the Archive Redo Flag for a database to Yes.
Rationale This enables point in time recovery and thereby avoids loosing data.

Max Log History
OMS-61590: Do not set the Max Log History of a database too low, use 100 at a minimum.
Rationale The longer the list, the longer you can perform automatic recovery.

Max Log Members
OMS-61591: Do not use more than 4 Max Log Members for a database.
Rationale Keeping more than 4 copies takes up too much space unnecessarily.

Rollback Segments
Name
OMS-42650: Name rollback segments using the convention
RBS##
where ## is a sequence number beginning with 01 to make the rollback segment unique.
Rationale In this way, it is immediately visible that the segment is a rollback segment.

Public ?
OMS-42651: Do not use public rollback segments.
Rationale With private rollback segments, it is always known which rollback segments are on-line at any time. When public rollback segments are used, they are randomly selected.

Optimal Size
OMS-45027: Define the optimal size of a rollback segment in bytes. This storage parameter is unique to rollback segments.
Rationale This is to ensure that rollback segments do not grow larger than necessary.

Comment
OMS-42654: Enter a short sentence indicating the use of this rollback segment.
Rationale In this way, it is obvious what is the usage of the rollback segment.

Table Implementations
Storage Definition
OMS-61583: Do not use Storage Definitions for tables.
Rationale Using only the default Storage Definition of the tablespace eliminates the risk of fragmentation.

Max Trans
OMS-42126: Do not limit the Max Trans for a table.
Rationale Limiting the maximum number of transaction slots is hardly ever useful. Only if a large amount of transactions are expected once for a table, this might be useful to prevent that unnecessary space is taken by transaction slots. Transaction slots are never released for rows when they once have been used. If the figure is set too low, a higher amount of transactions are required than available transaction slots. This results in locking problems.

Sequence Implementations
OMS-42352: Do not make maximum and minimum values larger than the length of the column for which the sequence is used.
Rationale This prevents attempting to enter a value in the column that is too large.

Cycle ?
OMS-42357: Do not cycle sequences.
Rationale This may lead to unexpected Oracle errors due to duplicate values.

User Object Index Storages
Init Trans
OMS-42163: Do not enter Init Trans for a cluster index.
Rationale The storage definition should be based on the definitions of the dedicated tablespace.

Index Storages
Tablespace
OMS-42309: Assign a tablespace to each index, based in the size of the index, and the estimated rebuild frequency. Group the indexes with similar sizes and rebuild frequencies together.
Rationale This allows for better space management.

Max Trans
OMS-61584: Do not limit the Max Trans for an index.
Rationale Limiting the maximum number of transaction slots is hardly ever useful. Only if a large amount of transactions is expected once for a table, this might be useful to prevent that unnecessary space is taken by transaction slots. Transaction slots are never released for rows when they once have been used. If the figure is set too low, a higher amount of transactions is required than available transaction slots. This results in locking problems.

Tablespaces
Storage Definition
OMS-42643: Each tablespace should have a default storage assigned to it.
Rationale The default storage definition is used for all segments residing within this tablespace.

Comment
OMS-42644: Enter a short sentence indicating the use of the tablespace.
Rationale This makes it obvious what is the usage of the tablespace.