Database management maturity model
Ver 1.1
Author: Thomas B. Cox
Translation: Cui Xiaobo (Local0)
table of Contents
¨ Database management maturity model
¨ Data administrator, database administrator and data designer difference
¨ 区 Differences in the nature of the task
¨ Database life cycle
¨ Database designer and database administrator
¨ Database life cycle
¨ Database life cycle and DBA tasks
¨ DBA task process (for mature databases)
¨ SLA protocol sample
I. Database management maturity model
DBA mm: Database Administration Matureity Model
This document discusses database management, taking out Oracle database administrators in development and maintaining products
The task that should be performed during the stage.
DBA mm is divided into 5 levels
Level focus key processing domain preliminary attention: No tool: Heroism u success is an occasional and non-duplicable U work performance as: emergency response, firefighting, personal playback (repeatable): Repeat DBA process tool: DBA daily check list U project plan (DBA project, DBA development support) U project tracking, sweeping (DBA project) u child project management (DBA project outsourcing or sub-project) u configuration management (database software, DBA Tool, change management) Definition (DEFINED) Follow: DBA processing work, documentation: DBA Training U Demand Management (Service Level) U Quality Assurance (DBA Task) u Best Practice Clarified and Disaggregated ( Training) U work is clearly defined, the task execution can be exactly the managed focus: process control, that is, the DBA processing process is stable and measurable, all changes, and the causes are recorded and labeled. tool:
All of the SLAUs can be controllable and measured before and after changing to confirm whether the change is valid. Optimizing continuous process optimization: First, you must repeat the first thing to improve first, you must measure us to manage U management practices, the skills are improved, and the technique of refining U changed, resulting in system control, lesson learning More improvement
Second, data administrators, database administrators and data designers
Depending on the task in the process of use, it is divided into three task collections:
(1) Data management task
(2) Data design task
(3) Database management task
You may be a plurality of characters at the same time, but the task of the three-category roles is different, and the performance is in: on the one hand, the nature of the task is different, and on the other hand, different tasks appear at different stages of the database lifecycle.
Third, the difference between task nature
1, data design
Data design focuses on data logic, the establishment of physical models, which will be stored as objects in the database.
To address specific business issues, these models are established in the range involved in the application, the order is:
Application range à Business Needs à Data Logic Model à Data Design is responsible for the following specific aspects:
u The model established can fully meet the needs of business issues.
u Ensure the model within the application area (usually the range of the model covers is slightly larger than the application, this is for future application systems and other system interfaces, but the model cannot be too large from the actual application)
u Establish a physical model that users accept (the main factor affecting performance is the application code, the data designer should improve performance as much as possible, not to destroy it)
2, data management
Data management is responsible for developing access policies: Who has any data, who can create, change, delete what data,
Data managers must track the ownership of the control data element, this work is not related to the design of the data, and
Storage details (DBA) have nothing to do. in particular:
u Who has data
u Who has the definition of data
u In the survival period of the data, if you need to change the ownership, how to change (such as the salesperson has the order to submit, the delivery person will then have it until the goods are sent, and then it is owned by the finance staff until it is received by the goods
3, database management
3, database management
The database administrator is responsible for maintaining the physical operation of the database (such as putting data on a disk), DBA is responsible:
u Maintain the database so that it runs in a state where the database developer is required.
u conduct tuning, shacking, playing patch, upgrade, backup, recovery, etc.
u All aspects of databases other than logical access (application level).
Special note, there is no SQL application in DBA duties, write SQL applications is the development of developers. If DBA assumes this task, he will become
For developers, not DBA.
Fourth, the database life cycle
A new database is born, usually in order to meet new applications (or need to do a large number of changes on old databases), to reasonable build databases, must analyze applications, design the appropriate table structure (using the ER model, then The image is used as a data sheet or directly designed data sheet). The design should be the first in other phases.
The design table is mainly completed by the data designer. In addition to the design table, the data designer is also responsible for analysis, which occurs when the application demand changes, which changes need to be made.
Database lifecycle designer data manager DBA1. Decision, analysis table design, safety design data ownership, security policy database lifecycle designer Data Manager DBA2. Design, build influence analysis change management installation, generate data files, tables, table space establishment, backup / recovery plan, safety procedure 3. Young physics restructuring, performance tuning 4. Mature abnormal situation monitoring, performance monitoring, performance tuning, execution backup / recovery, data file management A. Small changes affect analysis change management table modified B. Big change influence analysis, table weight design change management
Whenever the application needs to change, data administrators need to change management to ensure that all data belongs to the corresponding user.
After the introduction of the project, after the design is completed, the DBA is started, including spatial division, storage parameter settings (according to data designers), distributed data to obtain load balancing. If you follow the OFA (Optimal Flexible Architecture) DBA must be divided into data files in accordance with the requirements of the OFA. V. Database designer and database administrator
Differentiation data designers and DBAs, the key points are:
Ø Data designer's work is the logic building of the database (entity, relationship, table design)
Ø Database administrator's work is the resolution of physical problems (data files, disk load balancing, backup, recovery)
This view has its correctness, but there is a potential problem if it is strictly in accordance with this. Let data designers have knowledge of database physical construction, which is a good way to make data design easily and maintenance. In the automotive manufacturing industry, similar behavior is called "designed for manufacturing." Imaginary designers face two options, they are functionally exactly, but their one is very cheap. If the designer does not have production knowledge, he will not know which program is less cheap, it is easier to implement.
In addition, this point of view is to let DBA are responsible for database design. Some people do do this, they usually design only how to easily maintain, and ignore the requirements of the application, such design usually fail to end.
The following is a different task distribution of the database life cycle based on the task:
The Phase Description Designer Table Design Once Designer Table Design 1, B Design Compliance with the application requirements, when the demand changes, do the corresponding adjustment executor task frequency lifecycle description designer security design 1 to ensure database Design to meet the safety requirements of the application, the impact analysis is required for 2, A, B when applying changes, and the aspects of the review of the database need to be adjusted accordingly. Data Manager Data Ownership One 1 Confirm Who is to have, and maintain it. Security Policy One 1 Decide who can authorize access to data, who can share those data to others change management as needed 2, a, b When the application is changed, ensure that all data remains clear all the relationships, notifications Related users DBA DBA DBA installation once 2 Installation Database Creating Data Files One 2 For specific tablespace, a data file is created, reducing disk access violations, using OFA based on design. Develop backup / recovery scenarios to establish a backup recovery scheme, and test security measures 2 Create Role, assign data access, system permissions, create users, initialize the audit function. Table - Table Space Mapping One 2 Depending on OFA or other criteria, create a table-table space correspondence, and create a table once to the document record 2, b Create a table on the corresponding tablespace. Physical Reconstruction as needed 3 in table space Internal schedule, creation / changing index, creating / changing clusters, performance tuning as needed 3, 4 originating as a serious issue of physical configuration, such as indexes are not built. When the database is completed, through performance monitoring tasks, the parts that need to be adjusted are tuned. Abnormal monitoring daily 2, 3, 4 via log, trace, etc. check the abnormal situation, and accepts the user's question by email. Performance Monitoring The performance of the database is checked every week 4 based on the SLA or the like. Data file management As needed 4 as needed to distribute data files, extended data tablespace executive task frequency lifecycle Description DBA Perform backup / recovery Every day 4 performs backups, offline, logs, etc., and recovery. Table Modifications When the application is small as needed, the DBA must be modified under the premise of ensuring the data reliability. Six, the task in the database life cycle
1, installation
2. Create a data file
3, formulate backup / recovery plan
Backup / Recovery is an important means of ensuring database availability, and minimal disruptions should be reduced as much as possible.
step:
u Establish a backup recovery policy
u form backup recovery document
u Test backup recovery
u review other experts
u makes the backup / recovery policy complies with SLA
u If the resource does not reach the requirements of the SLA, gradually upgrade.
4, security strategy
About database security has three levels:
UNIX username / password authentication
Oracle username / password certification
Security features related to application
The network authentication is not directly associated with the database, but it also constitutes a security level.
step:
u Which level of security control is used
u Deploying the corresponding security mechanism
u Manage users, roles as needed
u Check security mechanism
5, table - table space correspondence
6, build a table
7. Physical element recombination
Contains the following:
u Move a table by a table space to another
u Create the required index
u creation cluster
u define horizontal segmentation
u Compressed table of Extens, through Backup / Exp / Drop / IMP
u Compressed indexed extent, several concepts of physically stored by Drop / Re-Createoracle:
Ø Data Blocks
The smallest storage granularity, all Oracle data stores and allocates space in block (also known as logic block, Oracle block, page). A data block corresponds to a certain Bytes disk space, which specifies the size of the data block when the database is created.
Compared to comparison, the block of the operating system is only a certain amount of Bytes collection, and the physical store of the data is in units of BYTE.
Ø Extents
Extent is a set of consecutive blocks for distributing a specific information.
Ø segments
The logically extent is Segment, Segment is an extent collection, used to assign space for a specific structure, and segment is limited to a table space.
8, performance tuning
Performance adjustment is a long-term process, the correct tuning can bring a lot of benefits, but inappropriate tuning may bring negative impact, free adjustment may bring unpredictable consequences.
Usually tuning must be performed within a certain approach, and follow the pre-set criteria, so that performance can be measured, trusted and accurate.
In the case of the following cases, the database is tuning:
(1) Have appropriate SLA
(2) Has a trusted performance metrics
(3) The database does not meet the performance requirements of SLA
There are two kinds of tuning results:
(1) The performance of the database reaches the SLA requirements.
(2) Inastructive or impossible, SLA standards are negotiated to accept the current performance
9, abnormal monitoring
Abnormal monitoring is the most important routine work of DBA, which allows DBA to pre-discover the precursor of the problem.
DBA needs to monitor at least:
(1) The exception of the user reflects, error information, etc. (DBA should be recorded in detail)
(2) Abnormal records in logs such as Alert, Trace
10, performance monitoring
This work makes DBA feel the discounger of the database, at least do:
(1) Each table space is idle memory fragmentation
(2) Free space for each table space
(3) Database growth rate
(4) Measurement of the current resources will be exhausted
(5) Each table, index exceeding the number of extents
(6) Response time for inquiry or transaction (INSERT, UPDATE, DELETE)
(7) Abnormal records in Alert, Trace log
11, data file management
12, perform backup / recovery
13, the modification of the table
7. Database life cycle and DBA tasks
When do you do what tasks, premise is that the database is already in "mature period"
1. Performance monitoring
Performance monitoring must be fed back by the user according to SLA, according to experience, when the system just built, it is necessary
Monitor every day, when the system is stable, monitor once a week.
(1) Debris case of free space for each table space
If honeycomb fragments appear, use the adhesion and tool (Oracle7 automatically Coalent, if the empty shards are too large, the idle space is smaller, then ignore the hollow, create a data file; if the disk space is too small The Backup / Exp / Drop / IMP is performed to rearrange. (2) The remaining space of each table space
(3) Computing table space idle rate
Note that Temp is not concerned, confirming that Rollback uses Optimal storage parameters.
(4) When is the space will be exhausted?
(5) Pay attention to the object greater than 4 extents
Regular compression extent, if an object's extent is always compressed, consider adding its next Extent size, if an object reaches the maximum number of extents, NexT and compression should be added immediately.
(6) Check the test query, the response time of the modification routine
The build routine is part of the SLA. Once the SLA negotiation is determined, periodically run to measure the running of the database, if the problem occurs, the routine is replaced.
(7) Response time according to SLA
(8) Check the relevant log files every day
(9) Problems discovered under records or exceptions
2, formulate backup / recovery plan
(1) Develop backup plans according to SLA and generate a document
(2) Confirm by other experts
(3) Develop recovery schemes for the case of major database problems, and generate documents
(4) Test the recovery plan test on Oracle Instance
(5) If a certain requirement of SLA is not reached, or the solution or modify SLA
3, execute backup / recovery
Detailed records all the steps and time.
4, Service Level Agreement
(1) SLA should be divided into the following aspects of database services
Ø List the downtime: Hours / Day, Days / Week, Days / Year
Ø Treat typical query, responsiveness of transactions
Ø Under crash, the longest recovery time and maximum data loss
(2) The details of the SLA will reflect in the task of DBA
Ø "Maximum downtime" à DBA monitoring database frequency
Ø "Maximum Data Loss" à log file size, interval of CKPT
Eight, DBA task flow (for mature databases)
Nine, SLA protocol sample
Service Level Agreement
