Oracle Database Concepts, 10g Release 2 () . Oracle Database Architecture. Overview of Oracle Grid Architecture. Client/Server Architecture . Oracle Database10g Architecture on Windows. Page 3. Oracle Database 10g Architecture on Windows. EXECUTIVE OVERVIEW. With the introduction of Oracle. Contributors: David Austin, Prasad Bagal, Cathy Baird, Mark Bauer, Eric Belden, Bill Bridge, Allen. Brumm, Sudip Datta, Mark Dilman, Jacco Draaijer, Harvey.
|Language:||English, Dutch, French|
|Genre:||Children & Youth|
|ePub File Size:||19.35 MB|
|PDF File Size:||10.84 MB|
|Distribution:||Free* [*Register to download]|
Oracle 10g Architecture - Download as PDF File .pdf), Text File .txt) or read online. It Contain the Basic Oracle Architecture and Admin Part. see highlights of the Oracle Database 10g architecture. You will see detailed discussions of new features such as regular expression support, flashback. What You Need to Know About. Oracle Database Architecture and Features. Rick Greenwald,. Robert Stackowiak & Jonathan Stern. Oracle Database 10g.
For e. During mounting, a database is associated with its previously started instance. The control files of the database are opened and read. The control file contains information about the other files of the database, their status, location and synchronizing information. This information is needed for the next stage of startup which is opening of the database. This phase has to be performed so that users of the database can access the data in the database.
Once the control file has been read and the location of the physical files of the database identified after mounting, the files are opened and made available to the users. The files that are opened are the online datafiles and the online redo log files. If any of the files are unavailable, an error will be reported and the database will not be opened.
It is at this point that the Oracle server verifies the consistency of the database. In case the database was shutdown improperly the last time, it will be detected at this point and the SMON background process will perform instance recovery.
This option can be used is you wish to perform some kind of maintenance on the database such as an export and import. You can optionally specify the database name. This is the default option. The option can be used for performing certain maintenance tasks. The SGA will be created in memory and the background processes will be started.
You may have to use this option for certain operations such as when creating a database manually.
DML statements are not allowed in the database. You can also start a database using the EM Console.
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
You can either perform a proper or improper shutdown. During a proper shutdown, three phases complementary to the startup are performed in the reverse order. First the database is closed; this involves performing a checkpoint on all available datafiles and closing the files.
Next the database is dismounted. At this time, the control file is synchronized and closed. Finally the instance that was created in memory is released.
Oracle 10g Architecture
The SGA no longer exists in memory and all background processes are stopped. The Oracle server waits for all currently connected users to disconnect their sessions.
No new connections are permitted. A checkpoint is performed on all the databases and the files are closed. When a database is shutdown is this mode, an instance recovery will not need to be done during the subsequent startup.
After the transactions have been rolled back the user sessions are terminated. No new connections are allowed.
Scheduler Architecture - Oracle 10g
The database is then closed, dismounted and the instance released. No instance recovery will be performed during subsequent startup.
As soon as a user's transaction completes the user is automatically disconnected. No new user connections will be allowed. This is a case of an improper shutdown. No checkpointing is done. All user connections are abnormally terminated. The database is not closed or dismounted.
However, the next startup will require an instance recovery to be performed by the SMON background process. If the spfileSID. If one cannot be found the initSID. Error information is usually recorded automatically by the Oracle Server as and when an error occurs in diagnostic files. The most important file that contains information about errors and changes affecting the database is the ALERT log file.
The file contains errors both fatal ORA errors and warnings about impending error situations. It contains a record of all structural changes made to the database such as creation of a tablespace. It can also contain checkpoint and day-to-day operational information such as startups and shutdowns and when an instance recovery occurs during startup.
Every entry in the alert file contains a timestamp. It also contains values of non-default initialization parameters. In order to restore access to the database to all users without the restricted session privilege is to issue the following command: alter database enable restricted session. What statement is used to change the status of a database? Explain the use of the restricted session privilege. In this section, you will cover the following topics related to creating an Oracle database: Entity relationships and database objects Creating a database in Oracle Creating the Oracle data dictionary Once the DBA has set up some necessary preliminary items for running the Oracle instance, such as password authentication, the DBA can then create the database that users will soon utilize for data management.
Creating a database involves three activities that will be discussed in this section. The first activity for creating a database is mapping a logical entity-relationship diagram that details a model for a process to the data model upon which the creation of database objects like indexes and tables will be based.
The second activity that the DBA will perform as part of creating a database is the creation of physical data storage resources in the Oracle architecture, such as datafiles and redo log files. The final and perhaps the most important aspect of creating a database is creating the structures that comprise the Oracle data dictionary. A discussion of each element in the database creation process will be discussed now in detail.
Entity Relationships and Database Objects The first part of creating a database is creating a model for that database. One fundamental tenet of database design is remembering that every database application is a model of reality. Most of the time, the database is used to model some sort of business reality, such as the tracking of inventory, payment of sales bonuses, employee expense vouchers, and customer accounts receivable invoices.
The model for a database should be a model for the process that the database application will represent. Now, explore the combination of those entities and their relationships. The concept of an entity maps loosely to the nouns in the reality the database application is trying to model. In the employee expenditure system mentioned above, the entities or nouns in the model may include employees, expense sheets, receipts, payments, a payment creator such as accounts payable, and a payer account for the company that is reimbursing the employee.
The relationships, on the other hand, map loosely to the idea of a verb, or action that takes place between two nouns. Some actions that take place in this employee expenditure system may be submits expense sheet, submits receipts, deducts money from account, and pays check. These entities and relationships can translate into several different types of visual representations or models of a business reality.
Figure illustrates each entity by a small illustration, with the relationships between each entity represented by an arrow and a description. The employee fills out the expense sheets for the expenses incurred on behalf of the company. Figure 3: An entity-relationship diagram of process flow in the system Then, the employees send their vouchers to the accounts payable organization, which creates a check for the employee and mails the payment to the employee.
The process is very simple, but it accurately models the business process within an organization to reimburse an employee for his expenses. When the developers of a database application create the employee expenditure system modeled by the entity-relationship diagram above, they will first take those entities and map out the relationship, then take the entity-relationship diagram and create a logical data model out of those entities and processes.
A logical data model is a more detailed diagram than the entity-relationship diagram in that it fills in details about the process flow that the entity-relationship diagram attempts to model. Figure shows the logical data model of the employee table and the invoice table.
Figure 4: Logical data model of employee table and invoice table On the expense sheet, the employee will fill in various pieces of information, including the expense ID number, the employee ID number, and the expense amount. The line between the two entities is similar to a relationship; however, in the logical data model, the entities are called tables and the relationships are called foreign keys.
There is an interesting piece of information communicated above and below the line on the opposite side of each table as well. That piece of information identifies a pair of facts about the relationship. The first element of the pair identifies whether the relationship is mandatory from the perspective of the table appearing next to the pair. A one indicates that the relationship is mandatory for the pair, while a zero 0 indicates that the relationship is optional.
In the example in the diagram above, the relationship between employee and expense sheet is optional for employees but mandatory for expense sheets. The second component of that pair indicates whether there is a one-to-one, one-to-many, or many-to-many correspondence between records of one table and records of another table.
That is to say, each employee may have submitted one or more expense sheets, or none at all, while each expense sheet corresponds to one and only one employee. This pair of facts is referred to as the ordinality of the database tables. The relationship between columns to tables corresponds loosely to the activity or relationship that exists between the two entities that the tables represent.
In terms of the entity-relationship diagram, the empid is the tie that binds an expense sheet to the employee who created it. Therefore, the relationship of one table to another through foreign keys should correspond somewhat to the relationship that occurs between two entities in the process flow being modeled. The database designer may ask several questions related to the physical design of that system as follows: How many employees will be allowed to use the system?
What sort of company chargeback system will be used to take the employee expense payment from the budget of the department for which the expense was incurred on behalf of the employee? How many expense sheets will be submitted per month and per year? The proper creation of a database in Oracle depends on answering these and many other questions regarding the physical relationship between the machine hosting Oracle and the data Oracle stores as part of the application model.
Some of these questions relate to Oracle-specific features. For example, the designer of the database should know row count estimates for each object to be created in Oracle.
This estimate of row count should be something that is forecasted over a period of time, say two years. This forecast of sizing for the database will allow the DBA some "breathing room" when the database application is deployed, so that the DBA is not constantly trying to allocate more space to an application that continually runs out of it.
Some objects that the designer will need to produce sizing estimates for are the tables and indexes, and the tablespaces that will contain those tables and indexes. In a point related to indexes, the designer of the application should know what the users of the database will need regarding data access.
This feature of database design is perhaps the hardest to nail down after the initial estimate of transaction activity for the database application. The reason for the difficulty is knowing what the users will want with respect to data access. The developers of the application should, where possible, try to avoid providing users with free rein to access data via ad hoc queries, as many users will not know, for example, that searching a table on an indexed column is far preferable to searching on a nonindexed column, for performance reasons.
Providing the "canned" query access via graphical user interfaces or batch reporting allows the designers to tune the underlying queries that drive the screens or reports, scoring a positive response from the users while also minimizing the impact of application activity on the Oracle instance.
There are several different options for specifying character sets in the Oracle database, just as there are many different languages available for use by different peoples of the world. These languages fall into distinct categories with respect to the mechanisms on a computer that will store and display the characters that comprise those languages. The distinct categories are single-byte character sets, multibyte character sets, and languages read from right to left.
Examples of the multibyte character sets available are the languages that originated in Eastern Asia, Southeast Asia, or the Pacific Rim.
These languages include Mandarin, Japanese, and Korean. Examples of a language read right to left include Hebrew and Arabic.
One final, and perhaps the most important, area of all to consider at the onset of database system creation in the Oracle environment is how the user will preserve the data in the system from any type of failure inherent in the usage of computer machinery. Such methods may include full and partial backups for the database and the archiving or storing of redo logs created by Oracle to track changes made in the database.
The first is creating the physical locations for data in tables and indexes to be stored in the database. These physical locations are called datafiles. The second step is to create the files that will store the redo entries that Oracle records whenever any process makes a data change to the Oracle database. These physical structures are called the redo log files, or redo log members.
The final step in creating an Oracle database is to create the logical structures of the data dictionary. The data dictionary comprises an integral portion of the database system. Both the users and Oracle refer to the data dictionary in order to find information stored in tables or indexes, to find out information about the tables or indexes, or to find out information about the underlying physical structure of the database, the datafiles, and the redo log files.
Exercises What is an entity-relationship diagram? Explain both concepts of entities and relationships. What is a logical data model? How does the logical data model correspond to the entity-relationship diagram? What structures in a data model relate loosely to the entities and the relationships of an entity-relationship diagram?
Oracle 10g Related Tutorials
What is ordinality? Explain the concept of mandatory vs. What is a foreign key? Is a foreign key part of the entity-relationship diagram or the logical data model? To what item in the other model does the foreign key relate to? Creation of the Oracle database is accomplished with the create database statement. The first thing to remember about database creation is the Oracle recommended methodology for actually creating the database. The steps are as follows: Back up existing databases. Create or edit the init.
Verify the instance name. Start the appropriate database management tool. Start the instance. Create and back up the new database. Step 1 in the process is to back up the database. ORA file. More details will be given shortly about the required parameters that must be unique for database creation. Steps 1 and 2 are critical in preserving the integrity of any existing databases that may already exist on the Oracle instance. Sometimes accidents do happen in database creation.
The worst thing a DBA can face when creating a new database is when a datafile or log filename in a parameter file may not have been changed before creating the second database. This situation leaves the first database vulnerable to being overwritten when the second database is created, which causes the first database to be unusable. Always remember to back up any existing database that uses the same instance and host machine.
A critical resource used to start any instance is the file that contains any initialization parameter that the DBA cares to set for the Oracle database and instance being used.
This file is generally referred to as the init. A parameter file is as unique as the database that uses it. Each database instance usually has at least one parameter file that corresponds to it and it only. Usually, a database instance will have more than one parameter file used exclusively for starting it, to handle various situations that the DBA may want to configure the instance to handle.
For example, a DBA may have one parameter file for general use on starting the Oracle instance when users will access the system, one parameter file that is specifically configured to handle an increase in processing associated with heavy transaction periods at the end of the year, and another parameter file designed to start the instance in proper configuration for DBA maintenance weekends.
Oracle provides a generic copy of that parameter file INIT. ORA in the software distribution used to install Oracle server on the machine hosting Oracle.
Generally, the DBA will take this generic parameter file and alter certain parameters according to his or her needs. There are several parameters that must be changed as part of setting up and running a new Oracle database. The following list highlights key initialization parameters that have to be changed in order to correspond to a unique database. The list describes each parameter in some detail and offers some potential values if appropriate.
If this is not changed, permanent damage may result in the event a database is created. The control files document the physical layout of the database for Oracle. If the name specified for this parameter do not match filenames that exist currently, then Oracle will create a new control file for the database at startup.
If the file does exist, Oracle will overwrite the contents of that file with the physical layout of the database being created. Data blocks are unit components of datafiles into which Oracle places the row data from indexes and tables.The alert log file and other trace files are also started. Modify the size of the default database buffer cache to 20M, both for the current and future instances.
Stay ahead with the world's most comprehensive technology and business learning platform.
In other words, the database will make the DBA wait for all other users to finish what they are doing before the database will actually close. In other words. This feature of database design is perhaps the hardest to nail down after the initial estimate of transaction activity for the database application.
Product details Format Mixed media product pages Dimensions x x