DATABASE RECOVERY in Font

Drawer ANSI/AIM Code 39 in Font DATABASE RECOVERY

SQL> update hr.employees set salary = (select salary from hr.employees as of timestamp (systimestamp - interval '120' minute); where last_name='Zlotkey') where last_name='Zlotkey'; 1 row updated. SQL> commit; Commit complete. SQL> Note that at no time do you have to access or query the history table that the database maintains to track the transactional changes to a table s data. The AS_OF clause ensures that the database looks up the relevant information in the history table that supports the Flashback Data Archive. The clause SYSTIMESTAMP INTERVAL '120' MINUTE will retrieve the values from two hours ago. You can also specify seconds, days, and months in this clause, as shown in the following examples: systimestamp interval '60' second systimestamp interval '7' day systimestamp interval '12' month

You can access historical data to create reports that encompass data from the past. Specify the VERSIONS BETWEEN TIMESTAMP clause to retrieve old values of a table s columns, as shown in the following example: SQL> SELECT * FROM patient_info VERSIONS BETWEEN TIMESTAMP to_timestamp('2009-01-01 00:00:00','YYYY-MM-DD HH23:MI:SS') AND MAXVALUE WHERE name ='ALAPATI'; The query shown here retrieves all versions of the data you selected between January 1, 2009, and today.

Information Lifecycle Management (ILM) applications often require multiple versions of a table s rows over time. Specify the VERSIONS BETWEEN TIMESTAMP clause to retrieve all versions of a row or rows over a period of time, as shown in the following example: SQL> SELECT * FROM patient_info VERSIONS BETWEEN TIMESTAMP to_timestamp ('2009-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND to_timestamp ('2009-06-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE name='ALAPATI'; The query shown here retrieves all versions of the rows in the PATIENT_INFO table between January 1, 2009, and June 30, 2009.

racle has been emphasizing that the Oracle Database 11g server automates management to such an extent that it refers to the database as a self-managing database. Well, this is at least partially true, as several traditional time-consuming and error-prone tasks have been replaced with new ways of managing memory, transactions, and resources, and organizing space. In addition, there have been improvements in backup and recovery techniques. However, the DBA is just as essential as ever. If anything, the DBA s role has become even more central because of the new features added complexity. This chapter deals with the operational aspects of running an Oracle database. Several components of the database require constant monitoring and modifications, and you ll learn about some important Oracle Database 11g features in detail in this chapter. Earlier chapters introduced several topics that you ll see here, and this chapter will tie together the various aspects of Oracle Database 11g that make the DBA s job easier. The chapter highlights two main operational areas: automatic database management features and online management features. Oracle Database 10g introduced the revolutionary Automatic Storage Management (ASM) feature, which helps Oracle DBAs manage disk storage with a built-in Logical Volume Manager (LVM) without requiring a system administrator s involvement. Automatic shared memory management is a useful feature that will help you immensely in your day-to-day administration. The online table redefinition feature will help you perform several routine tasks online without reducing database availability. Furthermore, Oracle Managed Files (OMF) will help you reduce database file-management tasks. In the following sections, you ll explore how adopting these new features can make day-to-day database management easier.

Traditionally, organizations have spent considerable amounts of effort on laborious performancetuning exercises. Oracle Database 11g provides you with powerful and accurate automatic performance-tuning capabilities. The heart of this functionality is the new statistics collection facility, the Automatic Workload Repository (AWR), which automatically collects and saves crucial performance statistics (including those for SQL statements that use the most resources in the database) to help detect performance problems and self-tune the database. AWR saves its data in the Sysaux tablespace. I explain the AWR in detail in 18. Instead of running myriad SQL performance-tuning scripts, just go to the Automatic Database Diagnostic Monitor (ADDM, pronounced adam ) as the first source for all your performance troubleshooting work. You ll save a lot of time that you might otherwise spend looking at extraneous issues that really don t have a bearing on performance. Since the ADDM ranks both the problems and its