preferably some knowledge of Oracle an introduction to the architecture of Oracle . •The Oracle Database, which is the set of operating system files, where the. Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions. “This book will help you make the best use of Oracle. Database. Buffer Cache. Redo Log files. Java Pool. Large Pool. Parameter file. Archived. Log files. Overview of Primary Components. The Oracle architecture.
|Language:||English, Spanish, Indonesian|
|Genre:||Business & Career|
|ePub File Size:||30.46 MB|
|PDF File Size:||20.75 MB|
|Distribution:||Free* [*Register to download]|
Oracle Database Concepts, 11g Release 2 (). E . Relational Database Management System (RDBMS). . Oracle Database Architecture. Oracle Database Concepts, 11g Release 1 (). B Copyright . Oracle Database Architecture. .. Overview of the Oracle Database Data Dictionary. Oracle Database Concepts, 10g Release 2 (). B . Part I What Is Oracle? 1 Introduction to the Oracle Database. Oracle Database Architecture.
Oracle Net Services allows the database servers and the client applications or servers acting as clients that access it to run on separate machines, and provides a means for moving data between the nodes on a network. Oracle Net Services is also used for Inter Process Communication if clients and servers are running on the same machine. The shared pool is like a buffer for SQL statements. Oracle's parsing algorithm ensures that identical SQL statements do not have to be parsed each time they're executed. The shared pool is used to store SQL statements. Contains the current SQL execution plan information.
An enqueue allows the user to store a value in the lock, i. The OS lock manager keeps track of the resources locked. If a process cannot be granted the lock because it is incompatible with the mode requested and the lock is requested with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO.
Advantages Allows several concurrent processes to have varying degree of known resources. If a process cannot be granted lock then it puts the process in wait queue. There is ordered queue of waiters. Differences between Latches and Enqueues Latches - Low level of serialization. Enqueue - Concurrency Latches - Process acquires latch for a short time and there is no ordered queue of waiters. Enqueue Obtained using OS Specific locking mechanism, there are waiters. These are small data structures, such as latches and pointers, which refer to other areas of the SGA.
The size of the fixed area is static. It also contains general information about the state of the database and the instance which the background processes need to access. When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. Following are its Tasks Storage for data blocks that have been retrieved from data files. When Oracle SQL updates a table a process called Data Manipulation Language, or DML , redo images are created and stored in the redo log buffer Serves for assistance with database recovery tasks Records all changes made to database blocks Places changes recorded to redo entries for redo logs Oracle will eventually flush the redo log buffer to disk, only after a commit operation occurs.
It is suited for system, storage, and database administrators deploying Oracle Database 12c Release 1 Every version has brought us some ground breaking technology and a leap forward innovation, that is a lot impressive and inspirational. It lists all the processes and the relationship between processes and other database components. Database Administrator is one of the highly sought after jobs in the IT industry. Some processes only appear when certain functionality of 12c is being used.
What are the Diagnostic Files? In this course, you will learn about the Oracle Server architecture, expand the size of the database, implement security Oracle Database 12c R2: Managing Multitenant Architecture Ed 2 Training On Demand DGC20 During the Oracle Database 12c Administration Workshop, students cover a brief overview of the multitenant container database and its pluggable databases.
When my wife and I had our daughter, Zoe, it was fascinating observing her evolution and movement. Maintain a scalable, highly available enterprise platform and reduce complexity by leveraging the powerful new tools and cloud enhancements of Oracle Database 12c.
Following are the new capabilities available with Oracle Data Pump:. A persistent question Oracle users face when deciding to move their databases to Oracle Control Files D.
The below diagram shows the migration architecture using Oracle Data Pump. The next set of examples walks you through the steps to create a container database with the DBCA. It is the go-to book for these wishing to create surroundings pleasant and scalable functions.
Those of you who are parents know exactly what I'm talking The differences between Oracle Database 9i, 10g,11g and 12c With Oracle Database 9i and 10g now both out of support, you should be thinking about an Oracle Database Upgrade to Oracle 11g or 12c?
With each release there is is always improvements and new features. This 5-day Oracle 12c DBA online training course will provide you with a solid understanding of restricting and sorting data, walks you through using conversion functions and conditional expressions, and addresses displaying data from multiple tables, manipulating data Oracle Database 12c - All about Multitenant Architecture For the past several years, we have witnessed significant contributions to Oracle Database.
This Oracle Database 12c: Managing Multitenant Architecture training will help you understand the multitenant architecture. The next section wraps up the architecture discussion by giving some insight into the system files that support the 12c database. How to create a multitenant database environment in Oracle 12c. Up to and including Oracle Database 12c release This article provides a basic overview of the multitenant option, with links to more detailed articles on the functionality.
RAC Attack is a free curriculum and platform for hands-on learning labs related to Oracle RAC cluster database , motivated by the belief that the best way to learn RAC is through lots of hands-on experience.
While this fact of ownership may not be all that important, especially given the ability to create and use public synonyms to obtain data without regard to schema ownership, it is an important point to make that the DBA should not look for objects they may create using their own username as the owner. In the event that the DBAs simply log in as themselves without the as sysdba trailer, the objects they create will be owned by their schemas.
The distinction is between an Oracle instance and an Oracle database. The Oracle database is a set of tables, indexes, procedures, and other data objects that store information that applications place into storage in the Oracle product.
The Oracle instance is the memory structures, background processes, and disk resources, all working together to fulfill user data requests and changes. With those points made, it should also be said that the Oracle instance is very close in similarity to the Oracle database. With that distinction made, attention should now turn to starting the Oracle instance.
This step is the first that should be accomplished when creating a new database or allowing access to an existing database. To start the Oracle database instance, the DBA should do the following:. Within the Server Manager tool there are several different options for database availability at system startup. These different options correspond to the level of access to the database once the database instance is running.
Each startup feature has several associated facts about the access level permitted while the database is running in that mode. This set of options is listed below:. The first option available is starting up the instance without mounting the database.
In Server Manager, the command used for starting any database is the startup command. For starting the instance without mounting the database, the startup command can be issued with an option called nomount.
This option will start the instance for database creation, but will not open any other database that may be available to the Oracle instance. This option is used for preventing any problems with existing databases that are mounted at the time that a new database is created.
Another recommended safety measure for creating new databases in the same instance that already owns a database is to back up the existing database before creating the new one. A complete discussion of backup and recovery occurs in Unit III. In other situations, the DBA may want to start the instance and mount but not open an existing database for certain DBA maintenance activities.
In situations where the DBA needs to perform a full database recovery, the DBA should mount but not open the database. The same startup command is used for starting the database in all modes, but the mode option used in this situation is the mount option.
The DBA will need to specify the database name and the parameter file for this option to mount the database to the instance for the physical database object maintenance activities described above. A nonmounted database can be mounted to an instance after creation of that database using commands described in the section of this chapter titled "Altering Database Availability.
All other options for starting a database will allow access to the database in one form or another. Hence, the options considered now are called opening the database. The DBA will open the database for many reasons, first and foremost so that users and applications can access the database in order to work. In order for the DBA to start the instance and then mount and open the database, the DBA must use the startup open option. In some cases, the DBA may want to open the database without letting users access the database objects.
This is the most common situation for a DBA to start the database in when there is DBA maintenance activity required on the logical portion of the Oracle database. In this case, the DBA will execute the startup option as before.
However, in addition to starting and opening the database, the DBA will execute a special command that restricts database access to only those users on the system with a special access privilege called restricted session.
Although any user on the database can have this privilege granted to them, typically only the database administrator will have it. In some cases, such as in the case of reorganizing large tables that involves a large-volume data load, the DBA may grant the restricted session privilege to a developer who is assisting in the database maintenance work. In these situations, the DBA may want to consider a temporary grant of restricted session to the developer, followed by a revocation of the privilege afterward to prevent possible data integrity issues in later maintenance cycles.
This method is generally preferable to a permanent grant of restricted session to someone outside the DBA organization. Typically, the DBA will want to use the restrict option for logical database object maintenance such as reorganizing tablespaces, creating new indexes or fixing old ones, large-volume data loads, reorganizing or renaming objects, and other DBA maintenance activities.
There are two special cases for database startup left to consider, both of which are used for circumstances outside of normal database activity. One of those two situations is when the database has experienced a failure of some sort that requires the DBA to perform a complete database recovery of the database and the instance. In this case, the DBA may want the instance to initiate its complete recovery at the time the instance is started. To accomplish the task, the DBA can issue the startup recover command from the Server Manager tool, and Oracle will start the instance and initiate the complete recovery at instance startup.
In cases where archiving is used, Oracle may require certain archived redo logs to be present for this option to complete successfully. In any event, the use of this option will be more carefully considered in the next unit, the treatment of OCP Exam 3 on database backup and recovery. The final option for database startup is used in unusual circumstances as well. Sometimes rarely there is a situation where the Oracle database cannot start the instance under normal circumstances or shut down properly due to some issue with memory management or disk resource management.
In these cases, the DBA may need to push things a bit. The DBA can give database startup an additional shove with the startup force command option.
This option will use a method akin to a shutdown abort see the next section on database shutdown in order to end the current instance having difficulty before starting the new instance. It is not recommended that the DBA use this option without extreme care, as there is usually a need for instance recovery in this type of situation.
Shutting down the Oracle instance works in much the same way as starting the instance, with the requirement to cease allowing access to the database and the requirement to accomplish the task while being logged on as internal.
The task must also be accomplished from the Server Manager, either graphically with the use of the Shut Down menu under the Instance menu or with the shutdown command in line mode.
The options for database shutdown are listed below:. There are three priorities that can be specified by the DBA for shutting down the database.
The first and lowest priority is normal. It is the lowest priority because Oracle will wait for many other events to play themselves out before actually shutting down the connection. 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.
The following description of events illustrates specifically how the shutdown process works under normal priority:. There are three rules that can be abstracted from this situation. The first is that no new users will be permitted access to the system.
The second is that the database does not force users already logged onto the system to log off in order to complete the shutdown. Third, under normal shutdown situations, there is no need for instance recovery. Normal database shutdown may take some time. The time the process can take depends on several factors.
Some of the factors that the database shutdown will depend on are whether many users have active transactions executing at the time the shutdown command is issued, how many users are logged on to the system and on the shutdown priority issued by the DBA. A higher-priority shutdown that the DBA can enact in certain circumstances is the shutdown immediate command.
Shutting down a database with immediate priority is similar to using the normal priority in that no new users will be able to connect to the database once the shutdown command is issued. However, Oracle will not wait for a user to logoff as it did in points 2 and 4 above. Instead, Oracle terminates the user connections to the database immediately and rolls back any uncommitted transactions that may have been taking place. This option may be used in order to shut down an instance that is experiencing unusual problems, or in the situation where the database could experience a power outage in the near future.
A power outage can be particularly detrimental to the database; therefore, it is recommended that the DBA shut things down with immediate priority when a power outage is looming. There are two issues associated with shutting down the database with immediate priority. The first is the issue of recovery.
The database will most likely need instance recovery after an immediate shutdown. This activity should not require much effort from the DBA, as Oracle will handle the recovery of the database instance itself without much intervention. However, the other issue associated with shutting down the database immediately is that the effect of the shutdown is not always immediate!
In some cases, particularly in situations involving user processes running large-volume transactions against a database, the rollback portion of the database shutdown may take some time to execute. The final priority to be discussed with shutting down a database is the shutdown with abort priority.
This is the highest priority that can be assigned a shutdown activity. In all cases that this priority is used, the database will shut down immediately, with no exceptions. Use of this priority when shutting down a database instance should be undertaken with care. The additional item that a shutdown abort uses to prevent the database from waiting for rollback to complete is not to roll back uncommitted transactions.
This approach requires more instance recovery activity, which is still handled by Oracle. Only in a situation where the behavior of the database is highly unusual or when the power to the database will cut off in less than two minutes should the shutdown abort option be employed.
Otherwise, it is usually best to avoid using this option entirely, and use shutdown immediate in circumstances requiring the DBA to close the database quickly. During the course of normal operation on the database, the DBA may require changing the availability of the database in some way. For example, the DBA may have to initiate emergency maintenance on the database, which requires the database to be unavailable to the users.
Perhaps there are some problems with the database that need to be resolved while the instance is still running but the database is unavailable. For this and many other reasons, the DBA can alter the availability of the database in several ways. The following discussion will highlight some of those ways. The first way a DBA may want to alter the status and availability of the database instance is to change the mount status of a database.
In some situations, the DBA may need to start a database with the nomount option, as discussed earlier in the section on starting the database. After the activities that required the database not to be mounted are complete, the DBA will want to mount the database to the instance, but have the database still be closed and therefore unavailable to the users.
To change the status of a database to be mounted, the DBA can use either the graphical interface of Server Manager to mount the database or use the alter database mount statement to achieve that effect.
Mounting the database allows the DBA to do several database maintenance activities without allowing users the chance to access the database and cause contention. After database work, or in the course of a manual startup, the DBA will want to allow the users access to the database. This step can be accomplished in two ways. Like mounting the database manually, the DBA can use the graphical user interface to open the database for user access.
Alternately, the DBA can issue the alter database open statement from the SQL command prompt and open the database for user access. When the database is in open mode, then a database user with the create session privilege, or the CONNECT role, can access the database. One fact that is important to remember about the Oracle database is that it can be accessed by multiple instances.
The final option to be covered corresponds to situations where the DBA has the database open for use, and needs to make some changes to the database. Some of these changes may include re-creating indexes, large-volume data loads, tablespace reorganization, and other activities that require the database to be open but access to the data to be limited.
This option is called the restricted session. This option prevents logging into the database for any user that does not have the restricted session privilege granted to the user. This option is handled in one way, mainly. The method used to close access to the database to all users except those with the restricted session privilege is alter database enable restricted session. In order to restore access to the database to all users without the restricted session privilege is to issue the following command: 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.
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.
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.
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.
Creating a physical database out of the logical data model requires considering several issues. The database designer may ask several questions related to the physical design of that system as follows:. 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. There are three main steps in creating databases in the Oracle environment. 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.
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:. 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. Following the creation of the appropriate initialization parameter file, the DBA will need to start the database instance while connected to the database as sysdba and while running Server Manager. The task of connecting to the database as sysdba has already been discussed. To start the instance, use the startup nomount command in order to run the instance without mounting a previously existing database.
After starting the instance without mounting a database, the DBA can create the database with the create database command In order to create a database, the user must have the osdba or sysdba granted to them and enabled. The following code block contains a create database statement:. A new database is created by Oracle with several important features.