Oracle Database Modeling and Design

Oracle is a relational database management system suitable for large-scale, medium-sized, and micro-computers. It uses structured guery language as its database language.

SQL mainly includes three functions: Data Definition, data manipulation (including query), and data control. SQL is a language with a high degree of non-procedural nature. You only need to describe "what to do" without specifying "How to Do". The language is simple and easy to use and powerful, integrates the interaction and embedding of integrated machines to adapt to a wide range of use environments. Oracle databases consist of three types of files: database files, log files, and control files.

A data dictionary is a set of tables automatically created and updated by Oracle. These tables record user names, description tables, views, and information about user permissions. The data dictionary is read-only and can only be queried. That is to say, the data dictionary is a database resource. Every user can access the data dictionary. DBA can monitor the use of Oracle RDBMS through the data dictionary and help users complete their applications. Oracle RDBMS also uses the database dictionary to manage and control the entire database.

When talking about databases, the first company that comes to mind is generally oracle. Founded in 1977, the company was initially a specialized database development company.

Oracle has been in the leading position in the database field. In 1984, relational databases were first transferred to desktop computers. Then, Oracle's next version, version 5, took the lead in introducing new concepts such as distributed databases and customer/Server structures. Oracle version 6 is the first line lock mode and supports Symmetric Multi-processing computers ...... The latest oracle8 mainly adds object technology to become a relational-object database system. Currently, Oracle products cover dozens of large, medium, and minicomputers. Oracle databases have become one of the world's most widely used relational data systems.

Oracl Basics

1. a tablespace can belong to only one database.

2. Each database must have at least one control file (three files are recommended to be stored on different disks ).

3. Each database has at least one tablespace (system tablespace ).

4. the purpose of creating a system tablespace is to store tables with the same purpose as much as possible to improve the efficiency of use. Only data dictionaries should be stored.

5. Each database has at least two online log groups and each group has at least one online log file.

6. A data file can belong to only one tablespace.

7. Once a data file is added to a tablespace, it cannot be removed from the tablespace or be added to other tablespaces.

8. Create a new data file to create a new tablespace.

9. The data file is formatted as an oracle block by Oracle. In versions earlier than Oracle9i, the size of the Oracle block is set during the first database creation.

10. The database cannot be changed in the future. To change the database, you must recreate the database.

11. A Segment segment can belong to only one tablespace, but can belong to multiple data files.

12. A zone extent can belong to only one data file, that is, the range (extent) cannot span data files.

13. The sum of pctfree and pctused cannot be greater than or equal to 100.

14. A single transaction cannot span multiple rollback segments.

15. The index table does not contain the rowid value.

16. rollback segments of different sizes have no benefit.

17. After commit, the data may not be immediately written to the disk (data file ).

18. A transaction will be written to the redo log even if it is not committed.

19. In Oracle 8.0.4, the default database created during initial installation is named orcl.

20. The maximum length of a block is 16 KB (2 k, 4 K, 8 K, 16 K ).
Maximum number of files per database (by block size)
2 K block, 20000 files
4 K block 40000 files
8 K or more 65536 files

21. Oracle server can start multiple databases at the same time.

22. multiple versions of Oracle Database can be installed on one operating system (either Unix or NT ).

23. A set of Oracle Database Systems can contain multiple Oracle databases and their corresponding instances.

24. Each Oracle database has one database instance (except OPS ).

25. Therefore, multiple Oracle database instances can be started on one operating system at the same time.

Excellent features of Oracle database products

(1) Compatibility

Oracle adopts standard SQL and has been tested by NIST. Compatible with IBM SQL/DS, DB2, INGRES, IDMS/R, etc.

(2) portability

Oracle products can run on a wide range of hardware and operating system platforms. It can be installed on 70 or more large, medium, and small computers. It can be installed in VMS,
Work in multiple operating systems, such as DOS, UNIX, and windows.

(3) connectability

Can be connected to multiple communication networks and support various protocols (TCP/IP, decnet, lu6.2, etc ).

(4) High Productivity

A variety of development tools are provided to facilitate further development.

(5) Openness

Oracle's good compatibility, portability, connectivity, and high productivity are good open features of Oracle RDBMS.

Oracle products mainly include database servers, development tools, and connection products.

Database Server

The latest version is 8, but 7 is widely used. Version 7 has the following features:

(1) multi-process and multi-clue Architecture

Multi-process and multi-clue architecture. Oracle7 includes one or more clue processes, multiple server processes, and multiple background processes. The lead process is responsible for listening to user requests, linking user requests to the Request queue in the memory, and returning the execution results in the response queue to the corresponding user. The server process processes user requests in the Request queue and links the results to the response queue. The number of threads and threads can be dynamically adjusted based on the current workload. The process structure of oracle7 can make full use of the multi-processor capability on the Parallel Machine. However, the clues are simulated by processes and scheduled by the operating system, to some extent, this weakens the advantage of low clue overhead.

(2) core technologies for improving performance

Oracle7 has improved its core technologies. Its concurrency control mechanism is more refined, including unlimited row-level blocking, no competition query, and the sequence generation mechanism of clues. The shared SQL cache is added to the shared memory buffer to improve the running efficiency.

(3) High Availability

Oracle7 provides multiple mechanisms to ensure high availability and fault tolerance.

(4) SQL implementation

Oracle7 SQL complies with ANSI/iso SQL 89 standards. Integrity constraints comply with the ANSI/ISO standards for entity integrity and referential integrity constraints. Provides role-based security.

A role is a set of permissions. With the concept of roles, the security management mechanism can combine some permissions on tables or other database objects and assign them to a role. When needed, you only need to assign this role to one user or a group of users, which can reduce the burden and cost of the security mechanism.

For clusters and MPP parallel computer platforms, oracle7 provides parallel server options for disk sharing. At the same time, oracle7 also provides parallel query options for SMP, cluster, and MPP platforms to achieve parallel query, parallel data loading, and other operations.

Oracle7 provides distributed database functions through distributed options. The distributed option provides multi-site distributed query and multi-site update functions, with location transparency and venue autonomy. It provides a global database name and supports remote process calls.

Development Tools

The Development kits provided by Oracle are developer/2000, designer/2000, discover/2000, and Oracle office. They cover all aspects from modeling, analysis, design to implementation.

Developer/2000 includes:

Oracle Forms is used to quickly generate complex screen-based applications. It has GUI and multimedia functions and is mainly used to manipulate data and query.

Oracle Reports is a quick report generation tool. It can generate various complex reports and process multimedia information.

Oracle graphics is used to generate various graphics applications.

Oracle books is used to generate online documents.

Designer/2000:

This is the case tool provided by Oracle. This tool can help users build, analyze, and design complex systems. It can also help users draw ergraphs, functional hierarchy graphs, data flow graphs, and positional graphs.

Discover/2000:

This is an OLAP tool mainly used to support data warehouse applications. It can mine historical data to find development trends and analyze profile data at different levels, so as to find detailed information about the business.

Oracle office is applicable to office automation and can receive and send enterprise-wide messages, schedule, calendar management, directory management, and spelling check.

 

Modeling and Design of Oracle databases:

 

To develop a database-based application system, the most critical step is the database modeling design on which the entire system is based. From logical to physical, the design is neglected in one link, the entire application system is built on a dangerous building. As the development process continues to deepen, it must face various unpredictable risks at any time, developers have to pay an unpredictable price for modifying or re-designing a database system that is not designed. Therefore, a good database design is essential for an efficient system.

I. Logical Modeling

The database design methods vary with specific databases, but the modeling stages are the same. Therefore, you can use some common tools, such as Rational Rose and powerdesigner, in this phase, the application system information to be implemented is obtained and analyzed based on system requirements for internal and external data analysis, in this way, we can effectively establish the data structure of the entire system (usually called the table structure in relational databases). On this basis, we can estimate and analyze the data volume, data traffic, and response speed of the database, in this way, the data model is generated. The specific operating principles are several database paradigms, user-specific requirements, and analysts' experience. They are based on the database performance, security, convenient management, and ease of development, the specific method varies with analyst's preferences and habits. You don't need tools. It is best to use tools to make the analysis process simple. The most important thing is to generate some diagrams, such as E-R diagrams, so that the analysis process is clear at a glance.

Ii. Physical Design(There are many things to be aware @)

In this step, the design is related to the specific database used by the system, as well as the hardware and software platform running by the database. The purpose is to allocate physical space to the database as far as possible, this step is very important in the database design. It is related to the database data security and database performance. Specifically, this step includes the distribution of data files in the corresponding tablespace on the disk, determine the size of redolog files and rollback segments based on the size of the data volume, and then allocate them. The allocation of these files should follow some principles. In line with the principles conducive to backup and performance optimization, the principles are as follows (Taking ORACLE database as an example ):

1. Create different tablespaces for tables and indexes. Do not place objects of non-core Oracle system components in the system tablespace to ensure that the data table space and index tablespace are on different disk drives.

2. Understand how end users access data. If possible, place objects that are frequently queried and frequently queried on different physical disks.

3. When a database contains large objects that allow users to access different data elements in parallel, it is advantageous to split the objects and store them on multiple disks. You can use partitions. When defining tables with millions of rows on an operating system platform, be more careful because the size of database files is limited, which is caused by the operating system rather than oracle.

4. create at least two custom rollback tablespaces on each independent disk to store your own rollback segments. Arrange the order of rollback segments in the initialization file so that they can be switched between multiple disks.

5. Place the redo log file on a disk with fewer reads and writes. For each Oracle instance, you need to create more than two redo log groups. The two members in the same group are placed on different devices.

6. determine the size of tables and indexes, which determines the size of the tablespaces required for saving them, and also determines which tablespaces are physically mounted on which disks and which tablespaces can be combined. The specific estimation method can be based on Oracle formulas. Here, we need to define its storage parameters (such as pctfree and pctused) based on the specific characteristics and usage of each table ).

Iii. Database Parameter Design

Each database has default parameter settings when it is created. However, the parameter settings may vary depending on the specific application requirements. The default parameter settings often need to be changed based on the characteristics of the application system, for example, the operating system platform, instance number, memory size setting, thread mode, and backup mode of each database are different. The specific parameters must be modified as appropriate, this step is important to the database performance and to ensure that the database functions required by the application system are implemented.

Iv. interface problems with software development

The last thing to consider in database design is the interface problem between the software to be developed and the software to be selected. To prepare the interface program, some third-party software has been prepared, and some are required by the database itself, for example, JDBC, BDE, ADO, and other database interfaces mainly consider the availability and efficiency of interfaces. This step is mainly based on experience, because such products are constantly introduced and are all touted by various merchants. In practice, we must decide which one is the most suitable.

The above is a rough analysis of several important steps in the database modeling design. The whole design process is constantly improved. It is done by the database administrators, designers, and developers, but each has a different focus, the Database Administrator focuses on two or three steps of design. The designer focuses on one step of design, while the developer focuses on four steps of design, because the programming environment of some application systems is different from the actual application environment, we need to design two sets and pay attention to the compatibility and portability of the two sets.

 

The following describes the above steps based on the database design in a developed labor management system.

 

This system is applied to large enterprise groups, including dozens of sub-factories. The basic data is from each sub-factory. Each sub-factory has an Access database that stores its own talent file information, which is maintained by each factory, it is regularly transmitted to the general database for query and statistics by labor departments ,. This is a branch-type database.

I. Logical Modeling

The first is the estimation and analysis of the data volume, data traffic, and response speed of the database. This step is also required for physical design. The main data of the labor system is the personnel file, the total data volume is about 100 MB in some aspects, with information such as staffing and various codes such as job sequence, wage grade, and factory and Mining Workshop. Most of the data input and maintenance are carried out in various factories and mines and then concentrated everywhere. Therefore, the data traffic is large and the response speed is generally required.

On the tool of logical modeling, the rantional rose 2000 is used to design various use case views according to the user's requirements, generate the entity of the database in the logic view, and the E-R diagram between the entities, then generate data Modeler. After confirmation, generate a table with the corresponding user in the database created by schema generation.

The detailed diagram is cumbersome and will not be shown here.

Ii. Physical Design

1. Because the employee archive information database is large and there are still photos, we divide them into two tables, put the photos in one table separately, and adopt the Partitioning technology to create partitions in the archives and photo tables, different partitions are placed in different tablespaces.

2. Because there are many codes in the employee archive information database, read operations must be performed with the encoding database during query. Therefore, the employee archive information table and the encoding database are not placed in a tablespace.

3. Create two rollback tablespaces, ten rollback segments, partitions in two tablespaces, and create a large rollback segment, because the employee archive information table is often modified and added in batches.

4. Place the redo log file on a disk with fewer reads and writes. Create three redo log groups and put the two members in the same group on two disks. The archivelog backup method is used.

5. Determine the storage parameters of each table based on its nature. For example, there are many changes, deletions, and inserts in the employee archive information table. Therefore, set the size of pctfree 10 and pctused 80, estimate the approximate number of blocks required by the table and calculate the size of the data file that stores the tablespaces of these tables.

6. Establish a database security system based on users' needs. That is to say, we have summarized several permission-level users, established these users, and granted them corresponding limited permissions for developers to program.

3. parameter settings

Select large in the default initialization parameter file. Because there are many users, about 100.

Shared_pool_size is related to the database size and application software. Set it to 1.5 times the default value.

Db_block_buffers is set to 25 MB Based on the memory and transaction volume of the server used.

Sort_area_size is set to 10 MB Based on the memory of the server used

Log_buffer is 1.5 times the default value.

Dbwr_io_slaves is set to 1

Rollback_segments is set to 10 Based on the transaction volume. Put them on two disks respectively.

Process 80

Mts_servers 3

Mts_dispatchers TCP, 3

Archive_log_start true adopts archive backup.

The confirmation of these parameters is temporary. No system can determine the parameters correctly before running, and it must be adjusted after the application is running.

Iv. interface problems with software development

Because the development software of our system uses Borland C ++ Builder 5, we use a relatively mature BDE interface, which is provided by Borland and has better performance.

The above is a general idea of Database Modeling and design based on the practice of database management and development over the years. I hope this will help you.

Oracle database backup and recovery

---- When we use a database, we always hope that the content of the database is reliable and correct. However, due to computer system faults (including machine faults, media faults, and misoperations ), databases may also be damaged, so it is imperative to restore data as soon as possible. If the database is backed up at ordinary times, it is easy to restore data. This shows how important it is to back up a database. Let's take oracle7 as an example to describe how to back up and restore the database. Oracle databases have three standard backup methods: export/import, cold backup, and hot backup. Exporting backup is a logical backup, while cold backup and hot backup are physical backup.

---- 1. Export/import (export/import)

---- Export can be used to extract data from the database, and import can be used to send the extracted data back to the Oracle database.

---- 1. Simple export of data (export) and import of data (import)

---- Oracle supports three types of output:

---- (1) Export the data of the specified table in the table (t) mode.

---- (2) user mode (U mode): exports all objects and data of the specified user.

---- (3) full database (full): export all objects in the database.

---- The data export process is the inverse process of the data import (export). Their data flows are different.

---- 2. incremental Export/Import

---- Incremental export is a common data backup method. It can only be implemented for the entire database and must be exported as a system. During this export, the system does not require any answers. The default export file name is export. dmp. If you do not want your output file to be named export. DMP, you must specify the file name to use in the command line.

---- Incremental export includes three types:

---- (1) "complete" incremental Export (complete)

---- Back up the entire database, for example:

---- $ Exp system/manager inctype = complete file = 990702.dmp

---- (2) incremental Export

---- Back up the data changed after the last backup. For example:

---- $ Exp system/manager inctype = incremental file = 990702.dmp

---- (3) cumulative)

---- The accumulative export method only exports the changed information in the database after the last "full" export. For example:

---- $ Exp system/manager inctype = cumulative file = 990702.dmp

---- The database administrator can schedule a backup schedule and export data in three different ways to achieve reasonable and efficient completion.

---- For example, the database backup task can be arranged as follows:

---- Monday: full export ()

---- Tuesday: Incremental Export (B)

---- Wednesday: Incremental Export (c)

---- Thursday: Incremental Export (d)

---- Friday: accumulative Export (E)

---- Saturday: Incremental Export (f)

---- Sunday: Incremental Export (g)

---- If the database is accidentally damaged on Sunday, the database administrator can follow these steps to restore the database:

---- Step 1: Use the create database Command to regenerate the database structure;

---- Step 2: create an additional return segment that is large enough.

---- Step 3: import a in full Increments:

---- $ Imp system./manager inctype = rectore full = y file =

---- Step 4: Accumulative incremental import E:

---- $ Imp system/manager inctype = rectore full = y file = E

---- Step 5: import the latest incremental F:

---- $ Imp system/manager inctype = restore full = y file = f

---- II. Cold backup

---- Cold backup occurs when the database is shut down normally. When the database is shut down normally, it will provide us with a complete database. Cold backup is a way to copy key files to another location. Cold backup is the fastest and safest way to back up Oracle information. Advantages of cold backup:

---- 1. It is a very fast backup method (just copy files)

---- 2. Easy to archive (simple copy)

---- 3. Easy to restore to a certain point in time (you only need to copy the file back)

---- 4. It can be combined with the archiving method to restore the database to the "latest State.

---- 5. Low maintenance and high security.

---- However, cold backup also has the following shortcomings:

---- 1. When used separately, only the restoration at a certain time point can be provided.

---- 2. During the whole backup process, the database must be backed up instead of other work. That is to say, in the cold backup process, the database must be closed.

---- 3. If the disk space is limited, it can only be copied to tape and other external storage devices, and the speed will be very slow.

---- 4. data cannot be restored by table or by user.

---- If possible (mainly for efficiency), back up the information to the disk and start the database (allowing the user to work) and copy the backed up information to the tape (the database can work at the same time ). Files that must be copied in cold backup include:

---- 1. All data files

---- 2. All control files

---- 3. All online redo log files

---- 4. init. ora file (optional ).

---- It is worth noting that cold backup must be performed when the database is closed. When the database is turned on, the execution of database file system backup is invalid.

---- The following is a complete example of cold backup:

---- (1) Close the database $ sqldba lmode = y

---- Sqldba> connect internal;

---- Sqldba> shutdown normal;

---- (2) use the copy command to back up all the time files, redo log files, control files, and initialize parameter files

---- Sqldba>! CP <File> <Backup directory>

---- (3) restart the Oracle database

---- $ Sqldba lmode = y

---- Sqldba> connect internal;

---- Sqldba> startup;

---- III. Hot Backup

---- Hot Backup uses archivelog mode to back up data when the database is running. Therefore, if you have a cold backup file last night and a hot backup file today, you can use this data to restore more information when a problem occurs. Hot Backup requires that the database be operated in archivelog mode and requires a large amount of archive space. Once the database is running in the archivelog state, you can back up the database. The Hot Backup command file consists of three parts:

---- 1. Data Files are backed up in one tablespace and one tablespace.

---- (1) set the tablespace to the backup status

---- (2) back up the data files in the tablespace

---- (3) restore the tablespace to normal state

---- 2. Back up the archive log file.

---- (1) temporarily stop the archiving process

---- (2) files in the target directory of archive redo log under Log

---- (3) restart the archive Process

---- (4) back up the archived redo log file

---- 3. Use the alter Database Backup controlfile command to back up and copy files

---- Advantages of Hot Backup:

---- 1. Backup can be performed at the tablespace or data file level, with a short backup time.

---- 2. The database is still available during Backup.

---- 3. It can be restored in seconds (to a specific point in time ).

---- 4. Restore almost all database entities.

---- 5. Recovery is fast, and recovery is usually performed when the database is still working.

---- Hot backup is insufficient:

---- 1. No error is allowed; otherwise, the consequences are serious.

---- 2. If the Hot Backup fails, the result cannot be used for restoration at the time point.

---- 3. Because it is difficult to maintain, you must be careful not to "end with failure ".