OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P20

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

58
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P20: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P20

Exam Essentials 881 Know how flashback database option works. The flashback database option can greatly reduce the time required to rewind the database to a prior point in time. Understand the parameters associated with flashback database. Understand backup catalog maintenance. Show how the EM Database Control interface simplifies cataloging, crosschecking, and cleaning up.
882 Chapter 15 N Implementing Database Backups Review Questions 1. Among the failure events, which is the most serious? A. The loss of an entire redo log file group but no loss in any other group B. The loss of one member of each redo log file group C. The failure of the ARC0 background process D. The failure of the LGWR background process 2. To enable the flashback database option, the database must be in which of the following modes? A. NOARCHIVELOG mode B. ARCHIVELOG mode C. FLASHBACK LOG mode D. BEGIN BACKUP mode 3. When the database is in ARCHIVELOG mode, database recovery is possible up to which event or time? A. The last redo log file switch B. The last checkpoint position C. The last commit D. The last incremental backup using RMAN 4. From the following, choose the true statement regarding image copies and backup sets. A. An image copy stores one data file per image copy, and a backup set can store all data files in a single file. B. An image copy stores one data file per image copy, and a backup set consists of one file per data file backed up. C. Both image copies and backup sets use a single file to store all objects to be backed up. D. A backup set stores each data file in its own backup file, but an image copy places all data files into a single output file. 5. The option on the EM Database Control backup-scheduling options screen that allows you to refresh an image copy on disk with an incremental backup is known as which RMAN feature? A. Incrementally updated backups B. Incremental level-zero backups C. Compressed image-copy refresh D. Compressed incremental backups
Review Questions 883 6. When should the DBA make a trace copy of the control file using ALTER DATABASE BACKUP CONTROLFILE TO TRACE? A. After every backup B. After multiplexing the control files C. Whenever restarting the instance D. Whenever the physical structure of the database changes 7. Which of the following is not a step in configuring your database to archive redo log files? A. Place the database in ARCHIVELOG mode. B. Multiplex the online redo log files. C. Specify a destination for archived redo log files. D. Specify a naming convention for your archived redo log files. 8. Why are online backups known as inconsistent backups? A. Because not all control files are synchronized to the same SCN until the database is shut down B. Because both committed and uncommitted transactions are included in a backup when the database is online C. Because a database failure while an online backup is in progress can leave the database in an inconsistent state D. Because online backups make copies of data files while they are not consistent with the control files 9. Which parameter is used to specify the archive-log destination? A. ARCHIVE_LOG_DEST_n B. LOG_ARCHIVE_DEST_n C. DB_CREATE_FILE_DEST D. DB_RECOVERY_FILE_DEST_n 10. Which of the following initialization parameters specifies the location where the control file trace backup is sent? A. DIAGNOSTIC_DEST B. BACKGROUND_DUMP_DEST C. LOG_ARCHIVE_DEST D. CORE_DUMP_DEST 11. Which of the following pieces of information is not available in the control file? A. Instance name B. Database name C. Tablespace names D. Log sequence number
884 Chapter 15 N Implementing Database Backups 12. Which data dictionary view shows that the database is in ARCHIVELOG mode? A. V$INSTANCE B. V$LOG C. V$DATABASE D. V$THREAD 13. Which file records all changes made to the database and is used only when recovering an instance? A. Archive-log file B. Redo log file C. Control file D. Alert log file 14. Which initialization parameter contains the value used as the default for archived log file destination 10? A. LOG_ARCHIVE_DEST B. STANDBY_ARCHIVE_DEST C. LOG_ARCHIVE_DUPLEX_DEST D. DB_RECOVERY_FILE_DEST E. USE_DB_RECOVERY_FILE_DEST 15. Which of the following commands is a key step in multiplexing control files using an spfile? A. ALTER SYSTEM SET CONTROL_FILES= ‘/u01/oradata/PRD/cntrl01.ctl’, ‘/u01/ oradata/PRD/cntrl02.ctl’ SCOPE=SPFILE; B. ALTER SYSTEM SET CONTROL_FILES= ‘/u01/oradata/PRD/cntrl01.ctl’, ‘/u01/ oradata/PRD/cntrl02.ctl’ SCOPE=MEMORY; C. ALTER SYSTEM SET CONTROL_FILES= ‘/u01/oradata/PRD/cntrl01.ctl’, ‘/u01/ oradata/PRD/cntrl02.ctl’ SCOPE=BOTH; D. The number of control files is fixed when the database is created. 16. Which statement adds a member /logs/redo22.log to redo log file group 2? A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2; B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2; C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2; D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’; 17. What is the biggest advantage of having the control files on different disks? A. Database performance. B. Guards against failure. C. Faster archiving. D. Writes are concurrent, so having control files on different disks speeds up control file writes.
Review Questions 885 18. To place the database into ARCHIVELOG mode, in which state must you start the database? A. MOUNT B. NOMOUNT C. OPEN D. SHUTDOWN E. Any of the above 19. Which of the following commands places the database in ARCHIVELOG mode? A. ALTER SYSTEM ARCHIVELOG; B. ALTER DATABASE ARCHIVELOG; C. ALTER SYSTEM SET ARCHIVELOG=TRUE; D. ALTER DATABASE ENABLE ARCHIVELOG MODE; E. ALTER DATABASE ARCHIVELOG MODE; 20. Which of the following substitution-variable formats are always required for specifying the names of the archived redo log files? (Choose all that apply.) A. %d B. %s C. %r D. %t
886 Chapter 15 N Implementing Database Backups Answers to Review Questions 1. A. Losing an entire redo log file group can result in losing committed transactions that may not yet have been written to the database files. Losing all members of a redo log file group except for one does not affect database operation and does not result in lost data. A mes- sage is placed in the alert log file. The failure of LGWR or ARC0 causes an instance failure, but you do not lose any committed transaction data. 2. B. To enable the flashback database option, the database must be in ARCHIVELOG mode. FLASHBACK LOG mode is not a valid mode of database operation. BEGIN BACKUP mode is used to perform hot backups without using RMAN. 3. C. In ARCHIVELOG mode, recovering the database is possible up to the last COMMIT state- ment; in other words, no committed transactions are lost in ARCHIVELOG mode. 4. A. Image copies are duplicate data and log files in OS format. Backup sets are binary com- pressed files in Oracle proprietary format. In addition to storing multiple data files in a single output file, backup sets do not contain unused blocks. 5. A. Incrementally updated backups save time during a recovery operation because fewer incremental backups need to be applied to the restored image copy. 6. D. In the rare event that all multiplexed copies of the control file are lost, having a trace copy of the control file reduces the possibility of data loss and reduces downtime during a recovery operation. The preferred and recommended way to back up a control file is to enable control file autobackup using RMAN. 7. B. Although it is recommended that you multiplex your online redo log files, it is not required to enable ARCHIVELOG mode of the database. 8. D. During an online backup, even if all data files are backed up at the same time, they are rarely, if ever, in sync with the control file. 9. B. LOG_ARCHIVE_DEST_n specifies the archive-log location. You can configure up to 10 archive-log destinations. LOG_ARCHIVE_DEST_10 is reserved for the flash recovery area, which is specified by the parameter DB_RECOVERY_FILE_DEST. 10. A. The trace backup is created in a subdirectory under the location specified by the DIAGNOSTIC_DEST parameter—$DIAGNOSTIC_DEST/diag/// trace directory. 11. A. The instance name is not in the control file. The control file has information about the physical database structure. 12. C. The V$DATABASE view in the column LOG_MODE shows whether the database is in ARCHIVELOG mode or in NOARCHIVELOG mode. 13. B. The redo log file records all changes made to the database. The LGWR process writes the redo log buffer entries to the redo log files. These entries are used to roll forward, or to update, the data files during an instance recovery. Archive log files are used for media recovery.
Answers to Review Questions 887 14. D. DB_RECOVERY_FILE_DEST points to the flash recovery area, and this is the default for archived log-file destination number 10. 15. A. The location of the new control files is not valid until an operating-system copy is made of the current control file to the new location(s) and the instance is restarted. The SCOPE=SPFILE option specifies that the parameter change will not take place until a restart. Specifying either MEMORY or BOTH causes an error, because CONTROL_FILES is not a dynamic parameter. 16. B. When adding log-file members, specify the group number, or specify all the existing group members. 17. B. Having the control files on different disks ensures that even if you lose one disk, you lose only one control file. If you lose one of the control files, you can shut down the database and copy a control file, or you can change the CONTROL_FILES parameter and restart the database. 18. A. To put the database into ARCHIVELOG mode, the database must be in the MOUNT state; the control files and all data files that are not offline must be available to change the database to ARCHIVELOG mode. 19. B. You use the ALTER DATABASE ARCHIVELOG command while the database is in the MOUNT state to enable archiving of online redo log files. 20. B, C, D. The substitution variable %d, which represents the database ID, is required only if multiple databases share the same archive-log destination.
Chapter Recovering the 16 Database ORacle Database 11g: aDministRatiOn i exam Objectives cOveReD in this chapteR: Backup and Recovery Concepts ÛÛ NÛ Identify the types of failure that can occur in an Oracle database NÛ Describe ways to tune instance recovery Performing Database Recovery ÛÛ NÛ Overview of Data Recovery Advisor NÛ Use Data Recovery Advisor to Perform recovery (Control file, Redo log file, and Data file)
Oracle Database 11g makes it easy for you to recover from a number of database failures. In Chapter 15, “Implementing Database Backups,” I emphasized the importance of check- points, redo log files, and archived log files to maintain a high level of availability and recoverability. I also showed you how to use the flash recovery area and several ways to back up your database. In this chapter, I’ll show you how to use those backups effectively when some kind of failure inevitably occurs. First, you’ll understand the kinds of failures that can occur in an Oracle database and explore how they can occur because of mistakes by users or DBAs or because of hardware or software failures that are out of your direct control. Each of these failures can require little or no action whatsoever, as in the case of an instance failure, but at the other end of the spectrum, a crash of the disk containing the SYSTEM tablespace requires a recovery effort. To balance performance with recoverability, you will learn how to tune instance recov- ery to minimize the amount of time Oracle will require to recover from an instance failure while still providing a reasonable response time for ongoing transactions. In a nutshell, your job is to increase the mean time between failures (MTBF) by providing redundant components where possible and leveraging other Oracle high-availability features such as Real Application Clusters (RAC) and Streams (an advanced replication technology). Hand in hand with increasing MTBF is decreasing the mean time to recovery (MTTR) to ensure compliance with any service-level agreements you have in place. Last, but certainly not least, these efforts should help you minimize data loss in such a way that committed trans- actions are never lost. In this chapter, you will also learn the steps required to recover from the loss of both system-critical and non-system-critical data files for databases that are operating in both ARCHIVELOG and NOARCHIVELOG modes. I’ll also show you how to recover from the loss of a control file or a redo log file. The Data Recovery Advisor was introduced in Oracle 11g, which automates most of the recovery tasks and is integrated with Enterprise Manager (EM) Database Control. As with most DBA operations in the database, EM Database Control makes many of these adminis- tration tasks easier and less error-prone. Understanding Types of Database Failure Database-related failures fall into six general categories. Understanding which category a failure belongs in will help you more quickly understand the nature of the recovery effort
Understanding Types of Database Failure 891 you need to use to reverse the effects of the failure and maintain a high level of availability and performance in your database. The six general categories of failures are as follows: Statement A single database operation fails, such as a Data Manipulation Language (DML) statement—INSERT, UPDATE, and so on. User process A single database connection fails. Network A network component between the client and the database server fails, and the session is disconnected from the database. User error An error message is not generated, but the operation’s result, such as dropping a table, is not what the user intended. Instance The database instance crashes unexpectedly. Media One or more of the database files is lost, deleted, or corrupted. In the next six sections, I’ll provide details on these failure types and suggest some pos- sible solutions for each one. For one particular type of failure, media failure, I’ll provide more detailed solutions for recovery later in this chapter. Statement Failures Statement failures occur when a single database operation fails, such as a single INSERT statement or the creation of a table. Table 16.1 shows the most common problems that occur when a statement fails, along with their solutions. ta b l e 1 6 .1 Common Problems and Solutions for When a Statement Fails Problem Solution Attempts to access tables without Provide the appropriate privileges, or create views on the appropriate privileges the tables and grant privileges on the view. Running out of space Add space to the tablespace, increase the user’s quota on the tablespace, or enable resumable-space allocation. Entering invalid data If constraints and triggers are not in place to enforce data integrity, entering bad data may succeed and cause application issues. DBAs need to work with users to validate and correct data. Logic errors in applications Work with developers to correct program errors or provide additional logic in the application to recover gracefully from unavoidable errors.
892 Chapter 16 N Recovering the Database Although granting user privileges or additional quotas within a tablespace solves many of these problems, also consider whether there are any gaps in the user education process that might lead to some of these problems in the first place. User-Process Failures The abnormal termination of a user session is categorized as a user-process failure. After a user-process failure, any uncommitted transaction must be cleaned up. The PMON (pro- cess monitor) background process periodically checks all user processes to ensure that the session is still connected. If the PMON finds a disconnected session, it rolls back the uncom- mitted transaction and releases all locks held by the disconnected process. Causes for user- process failures typically fall into one of these categories: NÛ A user closes their SQL*Plus window without logging out. NÛ The workstation reboots suddenly before the application can be closed. NÛ The application program causes an exception and closes before the application can be terminated normally. A small percentage of user-process failures is generally no cause for concern unless it becomes chronic. A failure may be a sign that user education is lacking—for example, training users to terminate the application gracefully before shutting down their worksta- tion. A DBA intervention is not needed for user-process failures, but administrators must watch for trends, and if happens too often, they need to investigate because there may be application problems or network issues that cause an excessive number of user-process failures. More information may be available in the alert log file showing whether the user process is hitting a bug and whether there are any trace files written. Network Failures Depending on the locations of your workstation and your server, getting from your work- station to the server over the network might involve a number of hops; for example, you might traverse several local switches and WAN routers to get to the database. From a net- work perspective, this configuration provides a number of points where failure can occur. These types of failures are called network failures. In addition to hardware failures between the server and client, a listener process on the Oracle server can fail, or the network card on the server itself can fail. To guard against these kinds of failures, you can provide redundant network paths from your clients to the server, as well as additional listener connections on the Oracle server and redundant net- work cards on the server. User-Error Failures Even if all your redundant hardware is at peak performance and your users have been trained to disconnect from their Oracle sessions properly, users can still inadvertently delete
Understanding Types of Database Failure 893 or modify data in tables or drop an index. This is known as a user-error failure. Although these operations succeed from a statement point of view, they might not be logically correct: the DROP TABLE command worked fine, but you really didn’t want to drop that table! If data was inadvertently deleted from a table and not yet committed, a ROLLBACK state- ment will undo the damage. If a COMMIT has already been performed, you have a number of options at your disposal, such as using data in the undo tablespace for a flashback query or using data in the archived and online redo logs with the LogMiner utility, available as a command-line interface or a graphical user interface. You can recover a dropped table using Oracle’s recycle-bin functionality. A dropped table is stored in a special structure in the tablespace and is available for retrieval as long as the space occupied by the table in the tablespace is not needed for new objects. Even if the table is no longer in the tablespace’s recycle bin, depending on the criticality of the dropped table, you can use either tablespace point-in-time recovery (TSPITR) or flashback database recovery to recover the table, taking into consideration the potential data loss for other objects stored in the same tablespace for TSPITR or in the database if you use flashback database recovery. TSPITR and flashback database recovery are beyond the scope of this book but are covered in more detail in OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex, 2009). If the inadvertent changes are limited to a small number of tables that have few or no interdependencies with other database objects, flashback-table functionality is most likely the right tool to bring back the table to a certain point in time. Later in this chapter, in the section “Performing Recovery Operations,” I’ll show you how to recover dropped tables from the recycle bin using the flashback drop functionality, retrieve deleted rows from a table using the flashback query functionality, use the flashback table functionality to bring a table back to a specific point in time along with its dependent objects, and use LogMiner to query online and archived redo logs for the previous state of modified rows. The Oracle 11g database provides flashback technology, which is aimed to recover from user errors. Instance Failures An instance failure occurs when the instance shuts down without synchronizing all the database files to the same system change number (SCN), requiring a recovery operation the next time the instance is started. Many of the reasons for an instance failure are out of your direct control; in these situations, you can minimize the impact of these failures by tuning
894 Chapter 16 N Recovering the Database instance recovery. You will learn how to tune instance recovery later in this chapter, in the section “Tuning Instance Recovery.” Here are a few causes for instance failure: NÛ A power outage NÛ A server-hardware failure NÛ Failure of an Oracle background process NÛ Emergency shutdown procedures (intentional power outage or SHUTDOWN ABORT) In all these scenarios, the solution is easy: run the STARTUP command, and let Oracle auto- matically perform instance recovery using the online redo logs and undo data in the undo tablespace. If the cause of the instance failure is related to an Oracle background-process failure, you can use the alert log and process-specific trace files to debug the problem. EM Database Control makes it easy to review the contents of the alert log and any other alerts generated right before the point of failure. Media Failures Another type of failure that is somewhat out of your control is media failure. A media failure is any type of failure that results in the loss of one or more database files: data files, control files, or redo log files. Although the loss of other database-related files such as an init.ora file or a server-parameter file (spfile) is of great concern, Oracle Corporation does not consider it a media failure. The database file can be lost or corrupted for a number of reasons: NÛ Failure of a disk drive NÛ Failure of a disk controller NÛ Inadvertent deletion or corruption of a database file Following the best practices defined in Chapter 15—in other words, adequately mirroring control files and redo log files and ensuring that full backups and their subsequent archived redo log files are available—will keep you prepared for any type of media failure. In the next section, I will show you how to recover from the loss of control files, data files, and redo log files. Performing Recovery Operations Once the inevitable database failure occurs, you can perform a relatively quick and painless recovery operation if you have followed the backup guidelines presented in Chapter 15 and clearly understand the types of failures presented earlier in this chapter. Before I show you how to perform recovery, however, it is important for you to under- stand how an Oracle instance starts up and what kinds of failures can occur at each startup phase. Understanding the startup phases is important, because some types of recovery
Performing Recovery Operations 895 operations must occur in a particular phase. Once a database is started, the instance will fail under a number of conditions that I will describe in detail. Next, I will describe how instance recovery works and how to tune instance recovery, and then show you ways to easily recover from several types of user errors. Finally, I will show you how to recover from media failures due to the loss of both critical and non–system- critical data files. Understanding Instance Startup Starting up a database involves several phases, from being shut down to being open and available to users. If certain prerequisites are not present, the database startup halts, and you must take some kind of remedial action to permit the startup to proceed. In the follow- ing list are the four basic database states along with their prerequisites after you type the STARTUP command at the SQL*Plus prompt: SHUTDOWN No background processes are active. A STARTUP command is used when the database is in this state; the STARTUP command fails if you are in any other state unless you are using STARTUP FORCE to restart an instance. NOMOUNT Also known as the STARTED state, the instance must be able to access the initialization-parameter file, either as a text-based init.ora file or as an spfile. MOUNT In this state, the instance checks that all control files listed in the initialization- parameter file are present and identical. Even if one of the multiplexed control files is unavailable or corrupted, the instance does not enter the MOUNT state and stays in the NOMOUNT state. OPEN Most of the time spent in the instance startup occurs during this phase. All redo log groups must have at least one member available, and all data files that are marked as online must be available. You are notified in a number of ways that a redo log group member is missing or a data file is missing. If a data file is missing or corrupted, you will get a message while you are running the STARTUP command, as in this example: SQL> startup ORACLE instance started. Total System Global Area 197132288 bytes Fixed Size 778076 bytes Variable Size 162537636 bytes Database Buffers 33554432 bytes Redo Buffers 262144 bytes Database mounted.
896 Chapter 16 N Recovering the Database ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: ‘/u05/oradata/ord/users01.dbf’ SQL> The message in SQL*Plus shows only the first data file that needs attention. You will have to use the dynamic performance view V$RECOVER_FILE to list all the files that need attention. Here is a query against the view V$RECOVER_FILE and a second query joining V$RECOVER_FILE and V$DATAFILE given the previous STARTUP command: SQL> select file#, error from v$recover_file; FILE# ERROR ---------- --------------------------------------------- 4 FILE NOT FOUND 11 FILE NOT FOUND SQL> select file#, name from 2 v$datafile join v$recover_file using (file#); FILE# NAME ---------- ---------------------------------------- 4 /u05/oradata/ord/users01.dbf 11 /u08/oradata/ord/idx02.dbf SQL> If a data file is offline or taken offline, the instance can still start as long as the data file does not belong to the SYSTEM or UNDO tablespace. Once the instance is started, you can proceed to recover the missing or corrupted data file and subsequently bring it online. If all files are available but out of sync, automatic instance recovery is performed as long as the online redo log files can bring all data files to the same SCN. Otherwise, media recovery is required using archived redo log files. If a redo log group member is missing, a message is generated in the alert log, but the database will still open. Keeping an Instance from Failing Media failures are not always critical, depending on which type of data file is lost. If any of the multiplexed copies of the control file are lost, an entire redo log group is lost, or any data file from the SYSTEM or UNDO tablespace is lost, the instance will fail. In some cases, the instance becomes unavailable to users but will not shut down; in this case, you can use SHUTDOWN ABORT to force the instance to shut down without resynchroniz- ing the data files with the control file. The next time the instance is started, instance recov-
Performing Recovery Operations 897 ery will be performed. If you plan on starting up the instance right after using SHUTDOWN ABORT, you can instead use STARTUP FORCE as shorthand for a SHUTDOWN ABORT and a STARTUP. Later in this chapter, I will show you how to recover from the loss of a con- trol file, a redo log file member, or one or more data files. Recovering from Instance Failure As I discussed earlier, in the section “Instance Failures,” an instance failure is any kind of failure that prevents the synchronization of the database’s data files and control files before the instance is shut down. Oracle automatically recovers from instance failure during instance recovery. Instance recovery is initiated by simply starting up the database with the STARTUP command. Instance recovery is also known as crash recovery. During a STARTUP operation, Oracle first attempts to read the initialization file, and then it mounts the control file and attempts to open the data files referenced in the control files. If the data files are not synchronized, instance recovery is initiated. Instance recovery occurs in phases: Phase 1 Find data files that are out of sync with the control file. Phase 2 Use the online redo log files to restore the data files to the state before instance failure in a rollforward operation. After the rollforward, data files have committed and uncommitted data. Phase 3 Open the database. Once the rollforward operation completes, the database is open to users. Phase 4 Oracle then uses the undo segments to roll back any uncommitted transactions. The rollback operation uses data in the undo tablespace; without a consistent undo tablespace, the rollback operation cannot succeed. After the rollback phase, the data files contain only committed data. Tuning Instance Recovery Before a user receives a “Commit complete” message, the new or changed data must be successfully written to a redo log file. At some point in the future, the same information must be used to update the data files; this operation usually lags behind the redo log file write because sequential writes to the redo log file are by nature faster than random writes to one or more data files on disk.
898 Chapter 16 N Recovering the Database As I discussed in Chapter 15, checkpoints keep track of what still needs to be written from the redo log files to the data files. Any transactions not yet written to the data files are at an SCN after the last checkpoint. The amount of time required for instance recovery depends on how long it takes to bring the data files up-to-date from the last checkpoint position to the latest SCN in the control file. To prevent performance problems, the distance between the checkpoint position and the end of the redo log group cannot be more than 90 percent of the size of the redo log group. You can tune instance recovery by setting an MTTR target, in seconds, using the initial- ization parameter FAST_START_MTTR_TARGET. The default value for this parameter is zero; the maximum is 3,600 seconds (1 hour). A setting of zero disables the target, which reduces the likelihood of redo logs waiting for writes to the data files. However, if FAST_START_MTTR_TARGET is set to a low nonzero value, writes to the redo logs most likely have to wait for writes to the data files. Although this reduces the amount of time it takes to recover the instance in the case of an instance failure, it affects performance and response time. Setting this value too high can result in an unacceptable amount of time needed to recover the instance after an instance failure. Two other parameters control instance recovery time: LOG_CHECKPOINT_TIMEOUT This is the maximum number of seconds that any new or modi- fied block in the buffer cache waits until it is written to disk. FAST_START_IO_TARGET T his is similar to FAST_START_MTTR_TARGET, except that the recov- ery operation is specified as the number of I/Os instead of the number of seconds to finish instance recovery. Setting either of these parameters overrides FAST_START_MTTR_TARGET. As part of the enhanced manageability features introduced with Oracle9i, setting FAST_START_MTTR_TARGET is the easiest and most straightforward way to define your database’s recovery time given the time-based constraints included in most typical SLAs. The EM Database Control interface makes it easy to adjust FAST_START_MTTR_TARGET. On the Availability screen of Database Control, choose Recovery Settings. Figure 16.1 shows the Instance Recovery setting, which you can find in the top section of the Recovery Settings screen. F i g u R e 1 6 .1 Adjusting MTTR for instance recovery
Performing Recovery Operations 899 Enter the desired value using seconds or minutes. When you click the Apply button, the new value for FAST_START_MTTR_TARGET goes into effect immediately and stays in effect when the instance is restarted. Using the SQL*Plus command line, you can accomplish this task by using the ALTER SYSTEM command, as in this example: SQL> alter system set fast_start_mttr_target=60 scope=both; System altered. Using SCOPE=BOTH, the new value of the parameter takes effect immediately and stays in effect the next time the instance is restarted. Recovering from User Errors Earlier in this chapter, in the section “User-Error Failures,” you learned a number of sce- narios in which a user’s data was inadvertently changed or deleted or a table was dropped. In the following sections, you’ll learn quite a few helpful tasks, such as how to do the following: NÛ Use flashback query to retrieve selected rows from a previous state of a table NÛ Recover a table using flashback drop and a tablespace’s recycle bin NÛ Bring an entire table and its dependent objects (such as indexes) back to a specific point in time using flashback table NÛ Roll back a specific transaction and its dependent transactions using flashback transaction NÛ Query previous transactions in the online and archived redo logs using the LogMiner utility Using Flashback Query One of the features introduced in Oracle9i was called flashback query. It allows a user to “go back in time” and view the contents of a table as it existed at some point in the recent past. A flashback query looks a lot like a standard SQL SELECT statement, with the addition of the AS OF TIMESTAMP clause. Before users can take advantage of the flashback query feature, you, the DBA, must per- form two tasks: NÛ Make sure there is an undo tablespace in the database that is large enough to retain changes made by all users for a specified period of time. This is the same tablespace that is used to support COMMIT and ROLLBACK functionality (discussed in Chapter 13, “Managing Data and Undo”). NÛ Specify how long the undo information will be retained for use by flashback queries by using the initialization parameter UNDO_RETENTION. This parameter is specified in seconds; therefore, if you specify UNDO_RETENTION=172800 (default is 900), the undo information for flashback queries can be available for up to two days.
900 Chapter 16 N Recovering the Database The key to the flashback query functionality is using the AS OF TIMESTAMP clause in the SELECT statement; you can specify the timestamp as any valid expression that evaluates to a date or timestamp value. In the following example, you want to query the EMPLOYEES table as it existed 15 minutes ago: SQL> SELECT employee_id, last_name, email FROM hr.employees AS OF TIMESTAMP (systimestamp - interval ‘15’ minute) WHERE employee_id = 101; EMPLOYEE_ID LAST_NAME EMAIL ----------- --------------------- ------------------- 101 Kochhar NKOCHHAR You can just as easily specify an absolute time of day to retrieve the contents of the row at that time, as in this example: SQL> SELECT employee_id, last_name, email FROM hr.employees AS OF TIMESTAMP (to_timestamp (‘01-Sep-04 16:18:57.845993’, ‘DD-Mon-RR HH24:MI:SS.FF’)) WHERE employee_id = 101; EMPLOYEE_ID LAST_NAME EMAIL ----------- --------------------- ------------------- 101 Kochhar NTKOCHHAR If your flashback query requires undo data that is no longer available in the undo tablespace, you will receive an error message: SQL> SELECT employee_id, last_name, email FROM hr.employees AS OF TIMESTAMP (systimestamp - interval ‘10’ month) WHERE employee_id = 101; select employee_id, last_name, email * ERROR at line 1: ORA-08180: no snapshot found based on specified time