Backup & Recovery7

ORACLE CAREERS
Recovering from user errors

Damir Bersinic and John Watson
10.30.2006
Rating: -5.00- (out of 5)


This is an excerpt from Chapter 29 of Oracle Database 10g OCP Certification All-In-One Exam Guide by Damir Bersinic and John Watson, copyright 2006 from Oracle Press, a division of McGraw-Hill. Click here to read the full chapter.
In this chapter you will learn how to:
• Recover a dropped table using Flashback technology
• Manage the recycle bin
• Perform a Flashback Table operation
• Recover from user errors using Flashback Versions Query
• Perform transaction-level recovery using Flashback Transaction Query
The previous chapter introduced Flashback Database, a powerful but drastic feature that is functionally equivalent to an incomplete recovery. This chapter covers the other flashback technologies available in an Oracle 10g database. These are not as extreme as Flashback Database in that they do not entail either downtime or loss of data. They are still, however, very powerful techniques for recovering from errors by backing out changes that you would prefer not to have been committed.
The flashback technologies discussed here are, first, Flashback Drop, enabled by the way the DROP TABLE command is used and implemented, and second, various ways of exploiting the UNDO capability: Flashback Versions Query, Flashback Table Query, and Flashback Transaction Query.
Flashback and the ACID test
Remember the ACID test, described in Chapter 9. This is part of the rules to which a relational database must conform, and is critical to an understanding of the flashback technologies: both their capabilities and their limitations.
All DML transactions are terminated with either a COMMIT or a ROLLBACK statement. Until then, while the transaction is in progress, the principle of transaction isolation (the I of the ACID test) as implemented by Oracle guarantees that no one, other than the session carrying out the transaction, can see the changes it has made.
Furthermore, the principle of atomicity (the A of the ACID test) guarantees that the session can terminate the transaction with a ROLLBACK, which will reverse the changes completely; no other session will have any knowledge that the changes were ever made. If the transaction is terminated with a COMMIT, then the changes will be immediately visible to all other sessions. The only exception to this is any sessions that for reasons of read consistency (the C of the ACID test) need to be protected from the changes. Furthermore, once a transaction is committed, it must be absolutely impossible for the database to lose the changes; this is the D, for durable, of the ACID test.
In many ways, DDL commands are transactions like any other. The rules of a relational database require that the effect of committed DDL can never be reversed, and all DDL is committed, automatically. You have no control over this; the COMMIT is an integral part of all DDL commands.
Flashback Drop provides a means whereby you can reverse the effect of a DROP TABLE command, but there is no guarantee that it will succeed. This will depend on other activity in the database since the DROP was executed. You can use the various Flashback Query commands to reverse DML commands, but again, whether this will succeed will depend on what other activity has occurred in the intervening time. It is impossible to roll back a committed transaction, whether DML or DDL. The ACID test does not permit this. The flashback technologies rely on constructing another transaction that will reverse the impact of the original transaction, but it may be that this new transaction will fail, because of other, incompatible committed changes.
Flashback Drop
Accidentally dropping a table is terrifyingly easy to do. It is not just that you can drop the wrong table because of a typing error; it could be the right table, but you are connected to the wrong schema, or logged onto the wrong instance. You can reduce the likelihood of this by setting your SQL*Plus prompt, for example,
SQL> set sqlprompt "_user'@'_connect_identifier> "
SYSTEM@ocp10g>
________________________________________
TIP To set your sqlprompt automatically for all SQL*Plus sessions, put the preceding command into the glogin.sql file, in the ORACLE_HOME/sqlplus /admin directory.
________________________________________
Flashback Drop lets you reinstate a previously dropped table (but not a truncated table!) exactly as it was before the drop. All the indexes will also be recovered, and also any triggers and grants. Unique, primary key, and not-null constraints will also be recovered, but not foreign key constraints.
________________________________________
EXAM TIPThe Flashback Drop command applies only to tables, but all associated objects will also be recovered, except for foreign key constraints.
________________________________________
The implementation of Flashback Drop
In earlier releases of the Oracle Database, when a table was dropped all references to it were removed from the data dictionary. If it were possible to see the source code for the old DROP TABLE command, you would see that it was actually a series of DELETE commands against the various tables in the SYS schema that define a table and its space usage, followed by a COMMIT. There was no actual clearing of data from disk, but the space used by a dropped table was flagged as being unused and thus available for reuse. Even though the blocks of the table were still there, there was no possible way of getting to them because the data dictionary would have no record of which blocks were part of the dropped table. The only way to recover a dropped table was to do a point-in-time recovery, restoring a version of the database from before the drop when the data dictionary still knew about the table.
In release 10g of the Oracle database, the implementation of the DROP TABLE command has been completely changed. Tables are no longer dropped at all; they are renamed.
In Figure 29-1, you can see that a table, OLD_NAME, occupies one extent of 64KB, which starts in the seventeenth block of file six. After the rename to NEW_NAME, the storage is exactly the same; therefore, the table is the same. Querying the view DBA_OBJECTS would show that the table's object number had not changed either. The release 10g implementation of the DROP TABLE command has been mapped internally onto a RENAME command, which affects the table and all its associated indexes, triggers, and constraints, with the exception of foreign key constraints, which are dropped. Foreign key constraints have to be dropped for real. If they were maintained, even with a different name, then DML on the non-dropped parent table would be constrained by the contents of a dropped table, which would be absurd.
Grants on tables do not have names, so they can't be renamed. But even though when you grant a privilege you specify the object by name, the underlying storage of the grant references the object by number. As the object numbers don't get changed by a RENAME operation, the grants are still valid.
As far as normal SELECT and DML statements are concerned, a dropped table is definitely dropped. There is no change to any other commands, and all your software will assume that a dropped table really is gone. But now that DROP is in fact a RENAME, it becomes possible to un-drop, by renaming the table back to its original name. However, this is not guaranteed to succeed. It may be that the space occupied by the dropped table has been reused. There are also complications if in the interim period another table has been created, reusing the same name as the dropped table.

The dropped objects can be queried, by looking at the "recycle bin" to obtain their new names. This is a listing of all objects that have been dropped, mapping the original table and index names onto the system-generated names of the dropped objects. There is a recycle bin for each user, visible in the USER_RECYCLEBIN data dictionary view, or for a global picture, you can query DBA_RECYCLEBIN. The space occupied by the recycle bin objects will be reused automatically when a tablespace comes under space pressure (after which time the objects cannot be recovered), or you can manually force Oracle to really drop the objects with the PURGE command.
________________________________________
TIP There are no guarantees of success with Flashback Drop, but it may well work. The sooner you execute it, the greater the likelihood of success.
________________________________________
Sample questions
After dropping a table, how can you access the rows within it? (Choose the best answer.)
1. Query the table using the AS OF syntax.
2. Query the table using the BEFORE DROP syntax.
3. Query the table using its recycle bin name.
4. You can't query it until it has been recovered.
If a table has been dropped and then another table created with the same name, which of the following statements is correct? (Choose the best answer.)
1. You must rename the new table before you can flash back the dropped one.
2. You can flash back the dropped table if you specify a new name for it.
3. You can flash back the dropped table into a different schema.
4. You must drop the new table before flashing back the old one.
Under which of the following circumstances will Flashback Drop work? (Choose the best answer.)
1. When a table has been truncated
2. When a table has been purged
3. When a user has been dropped
4. When an index has been dropped
5. None of the above
Click here to read the answers and the rest of this chapter.
About the authors
Damir Bersinic, OCP, MCSE, MCSDBA, MCT, is an infrastructure consultant with Trecata Corporation, a system integration consultancy in Toronto, Canada. He has more than 20 years of industry experience and has authored a number of titles on Oracle, SQL Server, Active Directory and Windows. His credits include multiple exam preparation titles for Oracle and Microsoft certification programs.
John Watson, OCP DBA 8.0, 8.i, and 9i, Internet Application Server Administrator, and certified on Managing Oracle on Unix, is a senior consultant with BLP Management Consultants in South Africa. He has taught for Oracle University in South Africa for four years. John taught the DB10g New Features course, the DB10g Manageability and Performance seminars, validated hundreds of OCP 10g New Features exam questions and contributed to the course material for all the 10g courses. John is the author of numerous articles on technology.



Rate this Tip
To rate tips, you must be a member of searchOracle.com.
Register now to start rating these tips. Log in if you are already a member.





Share - Digg This! Bookmark with Del.icio.us

'); // -->





ORACLE RELATED LINKS





Ads by Google







HL7 to / from SQL Server
Capture and generate HL7 directly to / from your SQL Server database
www.interfaceware.com








Oracle Disaster Recovery
White Paper: Effective, low-cost disaster recovery plan & solution.
ciena.com








Oracle
Your Source for SQL, Oracle, DBMS & RDBMS News and Resources.
www.NetworkWorld.com








SAP ABAP Developer Help
Learn how to leverage NetWeaver with SAP Developer Network events.
sdn.sap.com








Oracle Expert
Qualified Technology Professionals- Post Job Listings & Connect Today
www.Dice.com













RELATED CONTENT


Oracle Careers


Recovering Oracle databases


Regulatory compliance and the DBA: What you need to know


Building an Oracle team


The most important DBA skill


DBA certification


The fad-free path to management success


Vacation? What vacation?


Microsoft expected to spell it out for us in '05


Top 10 time management tips


The morphing of database administration


Backup and Recovery


Recovering without backup


First time restoring records from backup


Forcing a user to reset password


Importing from Windows to Linux


Including OS files in cold RMAN backup


Migrating database to new storage box


Which databases are available?


Moving Oracle from C to E drive


Recovering Oracle databases


ORA-02248 error while trying to import


Backup and Recovery Research


Training and Certification


Practical tips for deploying Oracle and open source


Recovering Oracle databases


Books to prepare for OCP certification


Setting up RAC learning environment


Practicing with Oracle Database as student


Becoming a DBA without experience


Can only connect as scott/tiger


Mr. Know-IT-All's Oracle Security Challenge


OCP but no experience


Becoming more marketable as DBA






RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems


Search Bitpipe.com for the latest white papers and business webcasts


Whatis.com, the online computer dictionary



DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.