Submitting forms on the support site are temporary unavailable for schedule maintenance. If you need immediate assistance please contact technical support. We apologize for the inconvenience.
How to use DBMS_Flashback and database recovery in Toad (recycle bin/flashback archive).
설명
I am trying to recover my database from changes made. I am trying to use DBMS_Flashback, but it does not seem to apply the set up I'm trying to go back to.
원인
DBMS_FLASHBACK is a "look back in time" in your database, using Oracle's DBMS_FLASHBACK package on Oracle version 9i and up.
DBMS_FLASHBACK provides an interface for the user to view the database at a particular time in the past, with the additional capacity provided by transaction backout features that allow for selective removal of the effects of individual transactions. This should not be confused this with a flashback database which actually moves the database back in time.
Applied in Toad it will not rollback your database to a specified point in time. It will only give you a view of the schema at that point in time.
해결 방안
Refer to Oracle documentation for further details about databse backup and recovery options.
Toad has featues that make use of DBMS_Flashback (10g), Flashback Archives (11g only), and Recycle Bin (10g). These may not cover all backup and recovery needs for a whole database.
Schema Browser: Recycle Bin In Oracle version 10g and above, a recycle bin is available to retrieve tables and associated objects (such as indexes, constraints, and triggers) you have dropped from the database. From the Schema Browser's Recycle Bin page you can access this bin and retrieve dropped tables if necessary.
Objects Panel The Objects Panel lists the objects available in the recycle bin for the selected schema.
Flashback Archives Note: This Toad feature is only available in the commercial version of Toad with the optional DB Admin Module.
Flashback Archives are only available when using Oracle 11g or above. You can use the Flashback Data Archive to track and maintain changes to Oracle data. For details about using Flashback Archives, please see your Oracle documentation. The Flashback Archives page in the Schema Browser allows you to work with them from within Toad. You can view archive information, create new archives and alter existing archives, as well as creating scripts from your archives.
Different types and status of flashback archives are differentiated by different icons. See Icon Legend for more information.
DBMS_Flashback Note: This Toad feature is only available in the commercial version of Toad with the optional DB Admin Module.
Using DBMS_FLASHBACK, you can flash back to a version of the database at a specified wall-clock time or a specified system change number (SCN). When enabled, the user session uses the Flashback version of the database, and applications execute against the Flashback version of the database.
To use this package, you must have EXECUTE privileges for DBMS_FLASHBACK.
You can use the DBMS_FLASHBACK functionality to restore data to your sessions. You may want to use DBMS_FLASHBACK for the following reasons:
Self-service repair. If you accidentally delete rows from a table, you can recover the deleted rows.
Packaged applications such as e-mail and voicemail. You can use Flashback to restore deleted e-mail by re-inserting the deleted message into the current message box.
Decision support system (DSS) and online analytical processing (OLAP) applications. You can perform data analysis or data modeling to track seasonal demand, for example.
DBMS_FLASHBACK is turned off automatically when the session ends, whether by disconnection or by starting another connection.
Using Wall-Clock time When enabling Flashback using a wall-clock time, the database chooses an SCN that was generated within five minutes of the time specified.
Using an SCN You can enable an SCN for finer control of the flashback. An SCN identifies the exact version of the database, and therefor allows you to specify the exact moment you want to flashback.
Using Flashback PL/SQL cursors opened in Flashback mode return rows as of the flashback time or SCN, letting you recover data. These cursors will remain open when you disable the Flashback session so that you can transfer the data to the current session.
Different concurrent sessions (connections) in the database can perform Flashback to different wall-clock times or SCNs.
DML and DDL operations and distributed operations are not allowed while a session is running in Flashback mode.
You can use PL/SQL cursors opened before disabling Flashback to perform DML.
Note: In a Flashback-enabled session, SYSDATE will not be affected; it will continue to provide the current time.
Additional information about DBMS_FLASHBACK, please see your Oracle documentation.
To use Toad's Flashback functionality
From the Session menu, select DBMS Flashback. If necessary, change the session for the window by selecting the active session in the Change Active session dropdown. Click the Enable button. Select either Enable at Timestamp Or
Enable at System Change Number
Enter the timestamp
추가 정보
Some of these feature are only available in Toad versions 8.6.1 and newer, are only available certain databse versions, and enabled only if DBA module for Toad was purchased.
