Partner relationship architecture. Oracle Eco-System at Master Lock. Business Use Case. To allow these allocations to occur most efficiently, the database segregates a small amount of the shared pool for the reserved pool. The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. The large pool can provide large memory allocations for the following:.
UGA for the shared server and the Oracle XA interface used where transactions interact with multiple databases. By allocating session memory from the large pool for shared SQL, the database avoids performance overhead caused by shrinking the shared SQL cache.
The large pool is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool. The large pool does not have an LRU list. Pieces of memory are allocated and cannot be freed until they are done being used. As soon as a chunk of memory is freed, other processes can use it. Oracle Database Performance Tuning Guide for more information about the large pool. This memory includes Java objects that are migrated to the Java session space at end-of-call.
For dedicated server connections, the Java pool includes the shared part of each Java class, including methods and read-only memory such as code vectors, but not the per-session Java state of each session.
For shared server, the pool includes the shared part of each class and some UGA used for the state of each session. The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate.
These statistics reset when the advisor is turned off. Oracle Database Java Developer's Guide. Oracle Database Performance Tuning Guide to learn about views containing Java pool advisory statistics. The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes.
The Streams pool is used exclusively by Oracle Streams. Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as required by Oracle Streams.
The fixed SGA is an internal housekeeping area. For example, the fixed SGA contains:. General information about the state of the database and the instance, which the background processes need to access. Information communicated between processes, such as information about locks see "Overview of Automatic Locks".
The fixed SGA size can change from release to release. Software code areas are portions of memory that store code that is being run or can be run. Oracle Database code is stored in a software area that is typically more exclusive and protected than the location of user programs. Software areas are usually static in size, changing only when software is updated or reinstalled.
The required size of these areas varies by operating system. Software areas are read-only and can be installed shared or nonshared. When possible, database code is shared so that all users can access it without having multiple copies in memory, resulting in reduced main memory and overall improvement in performance.
Multiple instances of a database can use the same database code area with different databases if running on the same computer. Skip Headers. Introduction to Oracle Database Memory Structures When an instance is started, Oracle Database allocates a memory area and starts background processes. The memory area stores information such as the following: Program code Information about each connected session , even if it is not currently active Information needed during program execution, for example, the current state of a query from which rows are being fetched Information such as lock data that is shared and communicated among processes Cached data, such as data blocks and redo records, that also exists on disk See Also: Chapter 15, "Process Architecture".
Software code areas Software code areas are portions of memory used to store code that is being run or can be run. Figure illustrates the relationships among these memory structures. Oracle Database Memory Management Memory management involves maintaining optimal sizes for the Oracle instance memory structures as demands on the database change.
The basic options for memory management are as follows: Automatic memory management You specify the target size for instance memory. Automatic shared memory management This management mode is partially automated. Manual memory management Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.
Overview of the User Global Area The UGA is session memory , which is memory allocated for session variables, such as logon information, and other information required by a database session.
Overview of the Program Global Area The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Note: Background processes also allocate their own PGAs. This discussion focuses on server process PGAs only.
Figure Cursor Description of "Figure Cursor". Note: The server and background processes do not reside within the SGA, but exist in a separate memory space. Database Buffer Cache The database buffer cache , also called the buffer cache , is the memory area that stores copies of data blocks read from data files. Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk When Database Smart Flash Cache flash cache is enabled, part of the buffer cache can reside in the flash cache.
Buffer States The database uses internal algorithms to manage buffers in the cache. A buffer can be in any of the following mutually exclusive states: Unused The buffer is available for use because it has never been used or is currently unused. Clean This buffer was used earlier and now contains a read-consistent version of a block as of a point in time.
Dirty The buffer contain modified data that has not yet been written to disk. Buffer Modes When a client requests data, Oracle Database retrieves buffers from the database buffer cache in either of the following modes: Current mode A current mode get , also called a db block get , is a retrieval of a block as it currently appears in the buffer cache.
Consistent mode A consistent read get is a retrieval of a read-consistent version of a block. See Also: "Read Consistency and Undo Segments" Oracle Database Reference for descriptions of database statistics such as db block get and consistent read get.
DBW n writes buffers in the following circumstances: A server process cannot find clean buffers for reading new blocks into the database buffer cache. Tablespaces are changed to read-only status or taken offline. Buffer Reads When the number of clean or unused buffers is low, the database must remove buffers from the buffer cache. The algorithm depends on whether the flash cache is enabled: Flash cache disabled The database re-uses each clean buffer as needed, overwriting it.
Flash cache enabled DBW n can write the body of a clean buffer to the flash cache, enabling reuse of its in-memory buffer. The search order is as follows: The server process searches for the whole buffer in the buffer cache. The server process searches for the buffer header in the flash cache LRU list. If the process does not find the buffer in memory a cache miss , then the server process performs the following steps: Copies the block from a data file into memory a physical read Performs a logical read of the buffer that was read into memory Figure illustrates the buffer search order.
Note: The database does not physically move blocks in memory. The movement is the change in location of a pointer on a list. Buffer Pools A buffer pool is a collection of buffers. The possible buffer pools are as follows: Default pool This pool is the location where blocks are normally cached. Keep pool This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space.
Recycle pool This pool is intended for blocks that are used infrequently. Redo Log Buffer The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database. Shared Pool The shared pool caches various types of program data. The database performs the following steps: Checks the shared pool to see if a shared SQL area exists for a syntactically and semantically identical statement: If an identical statement exists, then the database uses the shared SQL area for the execution of the subsequent new instances of the statement, thereby reducing memory consumption.
Allocates a private SQL area on behalf of the session The location of the private SQL area depends on the connection established for the session. The database also removes a shared SQL area from the shared pool in the following circumstances: If statistics are gathered for a table, table cluster , or index, then by default the database gradually removes all shared SQL areas that contain statements referencing the analyzed object after a period of time.
Data Dictionary Cache The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users.
The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data: Data dictionary cache This cache holds information about database objects.
Library cache All server processes share these caches for access to data dictionary information. Server Result Cache Unlike the buffer pools, the server result cache holds result sets and not data blocks. SQL Query Result Cache The database can store the results of queries and query fragments in the SQL query result cache , using the cached results for future queries and query fragments.
Note: You can specify the database objects that are used to compute a cached result so that if any of them are updated, the cached result becomes invalid and must be recomputed. Reserved Pool The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.
Large Pool The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. Figure is a graphical depiction of the large pool. Streams Pool The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes. Up-to-date features are covered for tables, indexes, data types, sequences, partitioning, data loading, temporary tables, and more.
All the examples are demonstrated using modern techniques and are executed in container and pluggable databases. Try something. See the result. Understand why the result is what it is. One is gaining information about the users listed in them, the other is related to schema ownership. More on the data dictionary will be presented in this chapter. The other feature is that any object created in any login as sysdba or sysoper will be owned by the SYS schema. 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: alter database enable restricted session.
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.
0コメント