Transcription
CMSDATABASE ADMINISTRATIONORACLE STANDARDS5/16/2011
Contents1.Overview . 42.Oracle Database Development Life Cycle. 43.2.1Development Phase. 42.2Test Validation Phase. 52.3Production Phase . 52.4Maintenance Phase . 62.5Retirement of Development and Test Environments . 6Oracle Database Design Standards. 63.1Oracle Design Overview. 63.2Instances . 73.2.1Instance Naming Standards . 73.2.2Object Usage. 73.2.3Required Parameters (DDL Syntax) . 73.3Databases . 93.3.1Database Naming Standards . 93.3.2Object Usage. 93.3.3Required Parameters (DDL Syntax) . 93.4Tablespaces .103.4.1Tablespace Naming Standards .113.4.2Object Usage.113.4.3Required Parameters (DDL Syntax)--Locally Managed .113.5Tables .123.5.1Table Naming Conventions .123.5.2Object Usage.123.5.3Required Components of the CREATE TABLE Statement .123.6Columns .123.6.1Column Naming Standards .133.6.2Object Usage.133.6.3Datatypes .143.7Indexes.145/16/2011CMS Oracle Standards and Guidelinesi
3.7.13.8Foreign Key Naming Standards.153.8.2Object Usage.153.8.3Required Parameters (DDL Syntax) .15Temporary Tables .163.9.1Object Usage.163.9.2Syntax .173.10Views.173.10.1Naming Standards for Views .173.10.2Object Usage.173.10.3Required Parameters (DDL Syntax) .183.11Materialized Views.183.11.1Naming Standards for Materialized Views .183.11.2Object Usage.183.11.3Required Parameters (DDL Syntax) .193.12Synonyms.203.12.1Object Usage.203.12.2Syntax .20Standard Naming Conventions.214.1Object and Dataset Names .214.1.1Usage .214.1.2Standard Naming Format .214.2File Names .234.2.1File Naming Convention .234.2.2Sample File Name .234.35.Referential Constraints (Foreign Keys) .153.8.13.94.Object Usage.14Utility File Names and Script Names .24Oracle Standards: Packages .245.1Overview .245.2Naming Standards.245.3Object Usage .245.4Required Parameters (DDL Syntax) - Package Specification .255/16/2011CMS Oracle Standards and Guidelinesii
6.7.Oracle Standards: Stored Procedures/Functions .256.1Overview .256.2Naming Standards.256.3Object Usage .256.4Required Parameters (DDL Syntax) - Stored Procedure Specification .266.5Required Parameters (DDL Syntax) - Stored Procedure Body.27Oracle Security Standards .277.1Overview .277.2Oracle Security Requirements .287.3Database Link Requirements .285/16/2011CMS Oracle Standards and Guidelinesiii
1. OverviewStandards are established rules, principles, or measures that are widely used,available, or supplied, and recognized and accepted as having permanent value.These Oracle standards identify steps necessary to implement an Oracle applicationdatabase at the Centers for Medicare and Medicaid Services (CMS). The standardsare intended to compliment the methodology and procedures described in the Rolesand Responsibilities document and Oracle reference manuals.These standards must be followed with care and consideration given to databaseobject naming conventions, appropriate database object usage, and required objectparameter settings. Any requests for deviation from these standards must besubmitted in writing to, reviewed by, and approved by the Central Oracle DBA staff atCMS.2. Oracle Database Development Life Cycle2.1Development Phase1.The Division of Data Services (DDS) is formally notified of the new projectinitiative and a Central Data Administrator (DA) and Central DatabaseAdministrator (DBA) are assigned. If DDS is going to provide Local Data orDatabase Administration support, these resources are assigned as well.2.Requirements are gathered, analyzed, and documented by project orapplication analysts. To initiate a DDS project, contact the DDS Director. You will be askedto submit a Database Development Form. You should submit an initial project plan to DDS for review andapproval of Data Administration and Database Administration tasksand schedule.3. The Local Data Administrator determines the project data requirements anddevelops a preliminary logical data model. All models must be developedaccording to DDMSS Data Administration standards.4. The logical model is reviewed and approved by the Central DA. It isrecommended that the Local and Central DBA be involved in the review aswell. For more information, see the DDMSS Data Administration Standards.5. A preliminary physical model can now be developed by the Local DA/DBA andsubmitted to the Central DBA for review and conditional approval. At thispoint the Central DBA will create a new instance or modify an existinginstance for the Local DBA to use in developing the database based on theapproved model.5/16/2011CMS Oracle Standards and Guidelines4
6. Local DBAs will be given access to create database objects within their ownschemas, and should keep the Central DBA informed of all activity takingplace in the database. The Central DBA will provide all Oracle support relatedto the database software, space allocations, backup and recovery, anddatabase troubleshooting.2.2 It is expected that through the normal development process, the datamodel will change to address issues related to application design,requirement changes, etc. The local DA and DBA can make thesechanges at their discretion, but should involve the Central DA/DBA inany significant changes to the model by scheduling an ApplicationArchitecture Review. The final version of the data model will be reviewed, and must beapproved, by the Central DA/DBA staff before the database will beallowed to migrate to the test/validation server for testing andvalidation (which must be done prior to moving to production).Test Validation Phase1. When database development is complete, the database must be moved tothe test/validation server for user testing, migration plan testing, andperformance monitoring. However, before moving to the test/
An Oracle instance refers to the System Global Area (SGA) and the database background processes. An instance is started (memory allocated and background processes started) and then a database (datafiles) is mounted by the instance. To start an instance, Oracle must read a parameter file. Parameters must be modified based on database requirements.File Size: 306KBPage Count: 30
