Flash back technology and recovery
Overview of flash back TechnologyIn Oracle10g, the flashback technology can be used at the row, table, and database levels. The specific categories are as follows: 1. the row-level flashback technology can be used to cancel incorrect changes to individual rows. There are three row-level flash back technologies, all of which rely on the Undo data stored in the Undo tablespace. (1) Flash query: allows you to view the old row data based on the time point or SCN. You can view the old data and retrieve it if necessary and cancel the incorrect changes. (2) flash back VERSION Query: allows you to view all versions of the same row within a period of time to cancel logical errors. It also provides audit history for changes, allowing you to compare the current data and historical data without executing DML activities. (3) flashback transaction query: allows you to view transaction-level changes. This technology helps analyze and audit transactions. For example, if a batch job is executed twice, you want to determine which objects are affected. This technique can be used to cancel changes made by a firm within a specified period of time. 2. Table-level flash back (1) flash back table: restore a table to a certain time point, or to the specified SCN without restoring data files. This feature uses DML changes to undo changes in the table. The flashback table feature relies on data revocation. (2) Flash-back deletion: allows you to roll back the result of a drop TABLE statement, instead of restoring it at a time point. The flashback deletion feature uses the recycle bin to restore the deleted table. 3. Database-level flash-back database feature allows you to restore the entire database to a certain point in time, thus revoking all changes since that time. The flashback database mainly uses the flashback log to retrieve the old version of the data block. It also relies on archiving and redo logs to completely restore the database without restoring data files and performing traditional media recovery.
Comparison between flashback and traditional restoration technologiesUnlike traditional restoration techniques, flash recovery is mainly designed to recover from loss of media, but from human errors. For example, the user may mistakenly delete a table or mistakenly change the data of a table. At this time, you want to cancel the operation you just performed. Traditionally, the only way to recover data from human errors is to use user-managed backup and recovery technologies. It takes a long time to recover the database file and then roll it back using all the redo log files. Flash back provides a more effective and faster way to recover from logical errors. In most cases, when this technology is used for recovery, the database is still online and available to users. In addition, flash back technology allows selective recovery of certain objects. Using traditional technologies, in a sense, a certain choice can only restore the entire database.
Flash back DeletionThe flashback deletion feature provides a means to restore accidentally deleted tables (or indexes) without losing the most recent transactions. In Oracle10g, Oracle does not discard a table immediately when it is deleted. Instead, the table and its dependent objects are listed in the recycle bin and kept as long as possible. If you quickly realize that you have made a mistake, you can simply use some commands to retrieve the table you have deleted.
| SQL> conn Scott/tiger; connected. SQL> Create Table mytest as select * from Dept; the table has been created. SQL> drop table mytest; the table has been deleted. SQL>Flashback table mytest to before drop;Flash back complete. SQL> select * From mytest; deptno dname loc ---------- -------------- --------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations Boston SQL> drop table mytest; the table has been deleted. |
How does flashback work?Before Oracle10g, executing the drop table command will immediately delete the table and all its dependent objects, and all the related spaces in the table segment will be released back to the database. In Oracle10g, tables and their dependent objects are not immediately deleted, but renamed. They are retained in the original location, and Oracle will keep them as long as possible based on the space emergency. In oracle10, there is a concept of a recycle bin: the recycle bin is a logical container (a data dictionary table that stores information about the deleted table, such as their new names and original names.
The preceding command shows the system field of the recycle bin. If you want to view the content in the recycle bin, you can use
| SQL> select * From recyclebin; SQL> select object_name, droptime, dropscn, purge_object from recyclebin; object_name droptime dropscn purge_object contains bytes ---------- bin $ ugr + records = $0: 21:33: 43 5456762 54660 |
In Oracle10g Release 2, you can use the recyclebin initialization parameter to disable the flashback deletion function. By default, this parameter is set to on, indicating that all deleted tables must enter the recycle bin. You can use the flashback deletion feature to restore them. You can set this parameter to off to disable the flashback deletion feature. The table is not moved to the recycle bin after it is deleted. To delete a table, clear the recycle bin. U user uses the purge command (drop table table_name purge) permanently deleting objects from the recycle bin u oracle is under space pressure (if Oracle does not have enough space in the tablespace to create new objects or cannot expand more space for existing objects) automatically delete objects in the recycle bin. To delete a table
| SQL> select * From mytest; deptno dname loc ---------- -------------- --------------- 10 Accounting New York 20 research Dallas 30 sales Chicago 40 operations Boston SQL> dDrop table mytest purge;The table has been deleted. SQL> select object_name, droptime, dropscn, purge_object from recyclebin; object_name droptime dropscn purge_object ----------------------------------------------------------------------- |
In this way, the table cannot be found. We deleted it from the recycle bin.
What is the recycle bin?The recycle bin is a logical structure and a data dictionary named recyclebin. You can view the content you have registered in the recycle bin in the user_recyclebin view. You can also view the content in the recycle bin in the dba_recyclebin view.
| SQL> select owner, original_name, object_name, ts_name, droptime from dba_recyclebin; owner original_name object_name ts_name ------------------------------ example ------------------------------ ----- Scott bonus bin $ tables = $0 users Scott salgrade bin $ tables = $0 users |
As you can see, when a table is moved to the recycle bin, Oracle assigns it a name generated by the system, which is usually 30 characters long. If you want to query an object in the recycle bin, you must use the name generated by the system to expand it in double quotation marks:
| SQL> conn Scott/tiger; connected. Use the Scott user to log on and query. SQL> select * from "bin $ mytqyz98sn6oi6iampa4la = $0"; grade losal hisal ---------- 1 700 1200 2 1201 1400 3 1401 4 2000 2001 5 3000 3001 |
Note: objects in the recycle bin can only be queried. Any DML operation will not work.
Restore the deleted tableYou can run the command to restore the deleted table in the recycle bin. Flashback table table_name to before drop after recovery, Oracle will delete them from the recycle bin.
Permanently delete a tableRun the following command to delete a file:
Drop table table_name prugeYou can also use the purge table or purge Index Command to permanently remove previously deleted tables or indexes from the recycle bin. You can use the purge tablespace command to delete all objects in the recycle bin as corresponding tablespace components, for example:
| Purge tablespace users user Scott |
The preceding command deletes all objects of a single user Scott from the users tablespace (and all dependent objects that exist in other tablespaces ).
Required PermissionsTo use the flashback table table_name to before drop command to retrieve a table, you must have or have the permission to delete the table (drop tablespace or drop any table ). Similar permissions are required to use the purge command. To query objects in the recycle bin, you must have select and flashback permissions.
Flash back database
Conditions for database flash1. The database log system must be in archive mode. 2. If a data file is lost or a specific data file cannot be used for some reason, flash back to the database for restoration. 3. If a control file has been restored or rebuilt within the desired time range, you cannot use the database flashback feature. 4. A database cannot be flashed back to the resetlogs operation. 5. data files cannot be flashed back to be shortened or deleted within the time range covered by the flashback table operation. In Oracle10g, the flashback database function restores data files that do not need to be backed up. It can only use partial archiving and redo log information. The flashback database operation reverts all data files in the database to a specific time point in the past. You can use the flashback operation in the following cases: 1. Recover a deleted table. 2. When a user error affects the entire database. 3. When a table is truncated incorrectly. 4. When only some changes are performed in a batch job
