ORACLE 11G CONCEPTS PDF
Oracle Database Concepts, 11g Release 2 (). E Copyright © , , Oracle and/or its affiliates. All rights reserved. Primary Authors: Lance. Oracle Database Concepts, 11g Release 1 (). B Copyright © , , Oracle and/or its affiliates. All rights reserved. Primary Author: Richard. Oracle Database Administrator's Guide, 11g Release 2 (). E Copyright © , , Oracle and/or its affiliates. All rights reserved. Primary.
|Language:||English, Spanish, Arabic|
|Genre:||Children & Youth|
|ePub File Size:||17.40 MB|
|PDF File Size:||14.28 MB|
|Distribution:||Free* [*Regsitration Required]|
11g Release 2 () Part Number PDF · Mobi · ePub . 18 Concepts for Database Administrators · Duties of 19 Concepts for Database Developers. Oracle Essentials: Oracle Database 11g, Fourth Edition by Rick .. Chapter 2, Oracle Architecture, describes the core concepts and structures (e.g., files. His best-selling books include Oracle Database 11g DBA Critical Database Concepts. 1 Oracle 2 Installing Oracle Database 11g and Creating a Database .
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. What is the tool used for starting the Oracle database? What connection must be used for the task?
What are the five options for database startup? Shutting Down the Oracle Database 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: Shutdown normal Shutdown immediate Shutdown abort 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: DBA issues shutdown normal from Server Manager at 3 p.
User X is logged onto the system at and performs data entry until p. User X will experience no interruption in database availability as a result of shutdown normal. User Y attempts to log into the database at p. User Z is the last user logged off at p. The database will now shut down. When the DBA starts the database up again, there will be no need to perform a database recovery. 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. What connection must be used for the task of database shutdown?
Storemags.com - Free Magazines Download in PDF for iPad/PC
What are the three options for database shutdown? Changing Database Availability and Restricting Login 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. 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 guides provide advanced conceptual and architectural information applicable to all development environments and languages with which you can develop Oracle applications. Oracle also provides extensions for Microsoft's Visual Studio development environment to support developing applications on. NET platforms. With only a Web browser, users with limited programming experience can develop scalable, secure, and efficient Web applications.
The Java programming language is used for database code that runs inside the database, on a middle tier, or on a client system. You can store XML content in the database, and write applications to process it and use it as a data interchange format. You can store data from different character sets and code pages, and manipulate and present that data as appropriate for each locale. All rights reserved.
Installing and Upgrading. Linux Installation Guides.
Solaris Installation Guides. AIX Installation Guides. Supplementary Installation Guides. Getting Started. New to Oracle Database 11 g. Supporting Documentation. Upgrade Information. License Information.
Developer Essentials. Database Administration. Clients for Database Administration.
Network Management. Backup and Recovery. Application Development. Advanced Application Development Concepts. Database Development Clients. Application Express. Oracle Call Interface.
Grid Computing. Oracle Real Application Clusters.A table consists of rows and columns All the tables and other objects in Oracle are stored in tablespace logically, but physically they are stored in the datafiles associated with the tablespace.
Start the instance. These guides provide advanced conceptual and architectural information applicable to all development environments and languages with which you can develop Oracle applications.
The chapters of this book are logically organized into four parts that closely track the way your database administration career will naturally evolve. 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.