First, introduction
In the past, if the user accidentally deleted the DBA to provide a new technical means to the database, it can be restored as a user for the error, and it has not recovered as a user, and it is Oracle9i. Flashing back to the query, the user uses flashback query to pay, and all this does not require DBA to have a direct method for recovery, and he is embarrassed to improve. Oracle 9i can make data prior to malfunction in time and pre-.
Below we use an example to specifically describe the usage of flashback queries.
Second, the example
1. Before using the flashback query, you must determine the following two parameters:
Undo_management = auto
Undo_retrion = 120 time, 1200 is reserved 20 points 0; # This time can be casually set, he clock. Sign in the submitted UNDO information in the system
2, use flashback query
SQL> CONN SYS / SYS AS SYSDBA
Connected.
SQL> CREATE User Flash Identified by Flash;
User created.
SQL> Grant Connect, Resource to Flash;
Grant succeeded.
SQL> Grant Execute on dbms_flashback to flash;
Grant succeeded.
SQL> CONN FLASH / FLASH
Connected.
SQL> Set echo on
SQL> CREATE TABLE T (a varchar2 (10));
Table created.
SQL> INSERT INTO T VALUES ('Gototop');
1 row created.
SQL> INSERT INTO T VALUES ('www.ncn.cn');
1 row created.
SQL> /
SQL> SELECT * FROM T;
A
------------
Gototop
Www.ncn.cn
Www.ncn.cn
SQL> SET TIME ON
15:00:22 SQL>
15:00:22 SQL>
15:00:23 SQL> Delete T where a = 'gototop';
1 row deleded.
15:00:35 SQL> commit;
COMMIT COMPLETE.
15:00:38 SQL> SELECT * FROM T;
A
------------
Www.ncn.cn
Www.ncn.cn
15:08:22 SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME (to_date ('2003-05-16 15:00:00');
PL / SQL Procedure SuccessFully Completed.
15:09:02 SQL> SELECT * FROM T;
A
------------
Gototop
Www.ncn.cn
Www.ncn.cn
15:16:43 SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
PL / SQL Procedure SuccessFully Completed.
15:17:05 SQL> SELECT * FROM T;
A
------------
Www.ncn.cn
Www.ncn.cn
3. Use the flashback query to recover data:
15:36:55 SQL> SELECT *.
No rows selected
15:36:57 SQL> INSERT INTO T VALUES ('www.ncn.cn'); 1 row created.
15:37:15 SQL> /
1 row created.
15:37:16 SQL> /
1 row created.
15:37:17 SQL> /
1 row created.
15:37:18 SQL> /
1 row created.
15:37:19 SQL> commit;
COMMIT COMPLETE.
15:37:21 SQL> /
COMMIT COMPLETE.
15:37:22 SQL> /
COMMIT COMPLETE.
15:37:23 SQL> SELECT * FROM T;
A
------------
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
6 rows selected.
15:37:28 SQL>
15:38:44 SQL> Delete T;
6 rows deleded.
15:38:50 sql> commit;
COMMIT COMPLETE.
15:38:53 SQL> commit;
COMMIT COMPLETE.
15:38:54 SQL> /
COMMIT COMPLETE.
15:38:57 SQL> DECLARE
15:39:28 2 Cursor Flash_Recover IS
15:39:28 3 SELECT *.
15:39:28 4 T_recode T% RowType;
15:39:28 5 Begin
15:39:28 6 dbms_flashback.enable_at_time (to_date ('2003-05-16 15:37:23 ");
15:39:28 7 Open flash_recover;
15:39:28 8 dBMS_FLASHBACK.DISABLE;
15:39:28 9 LOOP
15:39:28 10 Fetch Flash_Recover INTO T_RECODE;
15:39:28 11 EXIT WHEN FLASH_RECOVER% NOTFOUND;
15:39:28 12 INSERT INTO T VALUES (T_Recode.a);
15:39:28 13 End loop;
15:39:28 14 Close Flash_Recover;
15:39:28 15 commit;
15:39:28 16 END;
15:39:28 17 /
PL / SQL Procedure SuccessFully Completed.
15:39:28 SQL> 15:39:28 SQL>
15:39:31 SQL> SELECT * FROM T;
A
------------
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
Www.ncn.cn
15:39:35 SQL>
We can have resumed 5 below. Record, but we have to recover 6 records, why will it be less? Reason is
Third, limitations
1. Flashback query is based on SCN, although I am executed:
DBMS_FLASHBACK.ENABLE_AT_TIME (To_Date ('2003-05-16 15:37:23'));
But Oracle does not accurate this time point, start recovery. And Oracle 9i is remembered every five minutes to Round Down to the nearest SCN, then enable SCN from this SCN and a record of the SCN and the corresponding time.
This is why we have a restart of the reasons for the rest of our recovery. So if you recover, in order to avoid recovery failure, I can use DBMS_FLASHBA to wait for 5 minutes, then restore. Mapping information using dBMS_FLASHBACK.E and the corresponding time will only pay 5 days ago. If you want to SCN, then use Nable_AT_TIME to restore another 5 days, so we cannot use the flashback query to recover several shortcomings in 5 days, that is, in Oracle 9i, SCNS_FLASHBACK.ENABLE_AT_TIME is to resume, you must To determine if you need to recover
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_ Point, the following is how to use: change_number (SCN_NUMBER); to locate your recovery time
15:58:58 SQL> variable scn_save number;
16:13:43 SQL> EXECUTE: SCN_SAVE: = DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
PL / SQL Procedure SuccessFully Completed.
16:13:50 SQL> Print SCN_SAVE;
SCN_SAVE
------------
2.1202e 11
16:28:34 SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (: SCN_SAVE);
PL / SQL Procedure SuccessFully Completed.
In addition, in the use of dbms_flashback.enabl, Oracle default is accurate to the day, if you e_at_time, you must set your NLS_DATE_FORMAT you do not set, like the example above you will not get expected.
2, if you use the sysdatescn value, you must pay attention to them and dbms_flashback.get_system_ to be current time points and SCN values. Change_number to get a time point or
3, you can only enter the flashback query mode at the beginning of the transaction, if there is a DML operation, you must work.
4. Flashback query cannot be restored to the table structure change, because the current data dictionary used by flashback query.
