This is an excerpt from Chapter 15 of the book "OCA Oracle Database 11g: Administration I Exam Guide"
by John Watson, copyright 2008 from Oracle Press, a division of McGraw-Hill. Click here to download the full chapter.
Perhaps the most important aspect of a database administrator's job is to ensure that the database does not lose data. The mechanisms of redo and undo ensure that it is impossible to corrupt the database no matter what the DBA does, or does not, do (always assuming that there is no physical damage). After working through the section of this chapter headed Instance Recovery, you will be able to prove this. However, it is possible for an Oracle database to lose data if the DBA does not take appropriate precautions.
From release 9i onward, an Oracle database can be configured so that no matter what happens, the database will never lose a single row of committed data. It is also possible to configure an environment for one hundred percent availability. This ideal configuration requires use of Data Guard and RAC (or possibly Streams). A single-instance, non-distributed environment cannot achieve this—but it can get very close.
This chapter will go through the concepts behind Oracle's backup and recovery mechanisms: the enabling structure within which you will configure whatever level of data security and availability is demanded by your organization. The next two chapters will cover the practicalities of backup, restore, and recovery. But always be aware that this is a superficial treatment; the second OCP examination deals with backup and recovery in much greater detail. And even after passing that, you will not necessarily be fully competent. This is an area where you cannot do enough studying, research, and (most importantly) practice.
Identify the Types of Failure That Can Occur in an Oracle Database:
Statement Failure
An individual SQL statement can fail for a number of reasons, not all of which are within the DBA's domain—but even so, he/she must be prepared to fix them. The first level of fixing will be automatic. Whenever a statement fails, the server process executing the statement will detect the problem and roll back the statement. Remember that a statement might attempt to update many rows, and fail part way through execution; all the rows that were updated before the failure will have their changes reversed through use of undo. This will happen automatically. If the statement is part of a multistatement transaction, all the statements that have already succeeded will remain intact, but uncommitted. Ideally, the programmers will have included exceptions clauses in their code that will identify and manage any problems, but there will always be some errors that get through the error handling routines.
A common cause of statement failure is invalid data, usually a format or constraint violation. A well-written user process will avoid format problems, such as attempting to insert character data into a numeric field, but they can often occur when doing batch jobs with data coming from a third-party system. Oracle itself will try to solve formatting problems by doing automatic typecasting to convert data types on the fly, but this is not very efficient and shouldn't be replied upon. Constraint violations will be detected, but Oracle can do nothing to solve them. Clearly, problems caused by invalid data are not the DBA's fault, but you must be prepared to deal with them by working with the users to validate and correct the data, and with the programmers to try to automate these processes.
A second class of non-DBA-related statement failure is logic errors in the application. Programmers may well develop code that in some circumstances is impossible for the database to execute. A perfect example is the deadlock described in Chapter 10: the code will run perfectly, until through bad luck two sessions happen to try do the same thing at the same time to the same rows. A deadlock is not a database error; it is an error caused by programmers writing code that permits an impossible situation to arise.
Space management problems are frequent, but they should never occur. A good DBA will monitor space usage proactively and take action before problems arise. Space-related causes of statement failure include inability to extend a segment because the tablespace is full, running out of undo space, insufficient temporary space when running queries that use disk sorts or working with temporary tables, a user hitting his/her quota limit, or an object hitting its maximum extents limit. Database Control includes the undo advisor, the segment advisor, the Automatic Database Diagnostic Monitor, and the alert mechanism, all described in previous chapters, which will help to pick up space-related problems before they happen. The effect of space problems that slip through can perhaps be alleviated by setting datafiles to autoextend, or by enabling resumable space allocation, but ideally space problems should never arise in the first place.
Statements may fail because of insufficient privileges. Remember from Chapter 8 how privileges let a user do certain things, such as select from a table or execute a piece of code. When a statement is parsed, the server process checks whether the user executing the statement has the necessary permissions. This type of error indicates that the security structures in place are inappropriate, and the DBA (in conjunction with the organization's security manager) should grant appropriate system and object privileges.
Figure 15-1 shows some examples of statement failure: a data error, a permissions error, a space error, and a logic error.
FIGURE 15.1: Examples of statement failures
Read about the other types of failure categories and continue reading this chapter by downloading a free .pdf of Backup and Recovery Concepts from OCA Oracle Database 11g: Administration I Exam Guide.
More on Oracle 11g backup and recovery:
Get expert answers to your Oracle 11g backup and recovery questions in our Ask the Expert section.
Want access to new backup and recovery features and thinking of upgrading to Oracle 11g? Read this chapter excerpt to learn how.
Find all the 11g backup and recovery info you need in our Oracle 11g Special report.
What other Oracle 11g backup and recovery resources or articles would you like to see on SearchOracle.com? Email us and let us know.
