March 30, 2005
作者:eygle
示范案例，仅供参考:
1.察看备份文件
$ rman target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: EYGLE (DBID=1337390772)
RMAN> list backup of datafile 1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 131M DISK 00:01:50 29-MAR-05
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20050329T065513
Piece Name: /data5/flash_recovery_area/EYGLE/backupset/
2005_03_29/o1_mf_nnndf_TAG20050329T065513_14k2tmx7_.bkp
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 10605717 29-MAR-05 /opt/oracle/oradata/eygle/system01.dbf
|
2.mount数据库
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 314572800 bytes
Fixed Size 1301704 bytes
Variable Size 261890872 bytes
Database Buffers 50331648 bytes
Redo Buffers 1048576 bytes
|
3.恢复数据文件
RMAN> restore datafile 1;
Starting restore at 30-MAR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_29/
o1_mf_nnndf_TAG20050329T065513_14k2tmx7_.bkp tag=TAG20050329T065513
channel ORA_DISK_1: restore complete
Finished restore at 30-MAR-05
|
4.恢复表空间
RMAN> recover tablespace system;
Starting recover at 30-MAR-05
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 1760 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1760_14kb696y_.arc
archive log thread 1 sequence 1761 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1761_14kl1fz5_.arc
archive log thread 1 sequence 1762 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1762_14l2kcg6_.arc
archive log thread 1 sequence 1763 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1763_14l2kgcc_.arc
archive log thread 1 sequence 1764 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1764_14l2knqw_.arc
archive log thread 1 sequence 1765 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1765_14l6b55s_.arc
archive log thread 1 sequence 1766 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1766_14l6b66r_.arc
archive log thread 1 sequence 1767 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1767_14l6b72w_.arc
archive log thread 1 sequence 1 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1_14l6w0ph_.arc
archive log thread 1 sequence 1 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_1_14lndg1q_.arc
archive log thread 1 sequence 2 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_2_14lrs0ty_.arc
archive log thread 1 sequence 3 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_29/
o1_mf_1_3_14lw1hj3_.arc
archive log thread 1 sequence 4 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_30/
o1_mf_1_4_14m5ytfz_.arc
archive log thread 1 sequence 5 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_30/
o1_mf_1_5_14mmzpdk_.arc
archive log thread 1 sequence 6 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_30/
o1_mf_1_6_14mylhfk_.arc
archive log thread 1 sequence 7 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_30/
o1_mf_1_7_14n93rxk_.arc
archive log thread 1 sequence 8 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_30/
o1_mf_1_8_14nq4n5n_.arc
archive log thread 1 sequence 9 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_30/
o1_mf_1_9_14o57ft3_.arc
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1760_14kb696y_.arc thread=1 sequence=1760
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1761_14kl1fz5_.arc thread=1 sequence=1761
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1762_14l2kcg6_.arc thread=1 sequence=1762
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1763_14l2kgcc_.arc thread=1 sequence=1763
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1764_14l2knqw_.arc thread=1 sequence=1764
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1765_14l6b55s_.arc thread=1 sequence=1765
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1766_14l6b66r_.arc thread=1 sequence=1766
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1767_14l6b72w_.arc thread=1 sequence=1767
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1_14l6w0ph_.arc thread=1 sequence=1
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_1_14lndg1q_.arc thread=1 sequence=1
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_2_14lrs0ty_.arc thread=1 sequence=2
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_29/
o1_mf_1_3_14lw1hj3_.arc thread=1 sequence=3
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_30/
o1_mf_1_4_14m5ytfz_.arc thread=1 sequence=4
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_30/
o1_mf_1_5_14mmzpdk_.arc thread=1 sequence=5
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_30/
o1_mf_1_6_14mylhfk_.arc thread=1 sequence=6
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/
2005_03_30/
o1_mf_1_7_14n93rxk_.arc thread=1 sequence=7
media recovery complete
Finished recover at 30-MAR-05
|
6.启动数据库
RMAN> alter database open;
database opened
RMAN>
|
Posted by eygle at 11:15 PM
| Comments (1)
作者:eygle
启用了自动控制文件备份功能之后，在数据库发生重要改变或备份完成之后，会自动进行控制文件备份，同时备份SPFILE文件。
关于何时进行自动备份，Oracle如下说明:
When RMAN Performs Control File Autobackups
By default, control file autobackups are turned off, and no control file
autobackups are performed. If CONFIGURE CONTROLFILE AUTOBACKUP is ON,
then RMAN automatically backs up the control file and the current server
parameter file (if used to start up the database) in one of two circumstances:
when a successful backup must be recorded in the RMAN repository,
and when a structural change to the database affects the contents of the
control file which therefore must be backed up.
以下示例用于说明如何通过自动备份恢复控制文件和SPFILE文件。
1.查询当前拥有的备份
$ rman target /
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: EYGLE (DBID=1337390772)
RMAN> list backup of controlfile;
using target database controlfile instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
26 Full 3M DISK 00:00:02 29-MAR-05
BP Key: 26 Status: AVAILABLE Compressed: NO Tag: TAG20050329T171049
Piece Name: /data5/flash_recovery_area/EYGLE/autobackup/2005_03_29/o1_mf_s_554231449_14l6wv59_.bkp
Controlfile Included: Ckp SCN: 10643029 Ckp time: 29-MAR-05
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 3M DISK 00:00:02 29-MAR-05
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20050329T222158
Piece Name: /data5/flash_recovery_area/EYGLE/autobackup/2005_03_29/o1_mf_s_554250117_14ls48bd_.bkp
Controlfile Included: Ckp SCN: 10652118 Ckp time: 29-MAR-05
|
2.从备份中恢复控制文件
RMAN> restore controlfile to '/tmp/control.ctl' from
'/data5/flash_recovery_area/EYGLE/autobackup/2005_03_29/o1_mf_s_554250117_14ls48bd_.bkp';
Starting restore at 30-MAR-05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
Finished restore at 30-MAR-05
|
3.从备份中恢复SPFILE文件
RMAN> restore spfile to '/tmp/spfileeygle.ora' from
'/data5/flash_recovery_area/EYGLE/autobackup/2005_03_29/o1_mf_s_554250117_14ls48bd_.bkp';
Starting restore at 30-MAR-05
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found:
/data5/flash_recovery_area/EYGLE/autobackup/2005_03_29/o1_mf_s_554250117_14ls48bd_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 30-MAR-05
RMAN>
|
Posted by eygle at 10:51 PM
| Comments (0)
作者:eygle
具备了flashback version query查询的基础，我们就可以进行基于flashback version query的恢复.
这就是flashback transaction query。flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY中获得指定事务的历史信息以及Undo_SQL，通过这个UNDO_SQL，我们就可以恢复特定的事务。
Flashback transaction query需要用到FLASHBACK_TRANSACTION_QUERY视图，我们先看一下视图
SQL> desc FLASHBACK_TRANSACTION_QUERY;
Name Type Nullable Default Comments
---------------- -------------- -------- ------- --------------------------
XID RAW(8) Y Transaction identifier
START_SCN NUMBER Y Transaction start SCN
START_TIMESTAMP DATE Y Transaction start timestamp
COMMIT_SCN NUMBER Y Transaction commit SCN
COMMIT_TIMESTAMP DATE Y Transaction commit timestamp
LOGON_USER VARCHAR2(30) Y Logon user for transaction
UNDO_CHANGE# NUMBER Y 1-based undo change number
OPERATION VARCHAR2(32) Y forward operation for this undo
TABLE_NAME VARCHAR2(256) Y table name to which this undo applies
TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies
ROW_ID VARCHAR2(19) Y rowid to which this undo applies
UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo
|
该视图的定义为：
select xid, start_scn, start_timestamp,
decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
commit_scn, commit_timestamp,
logon_user, undo_change#, operation, table_name, table_owner,
row_id, undo_sql
from sys.x$ktuqqry
|
参考前面的文章，我们通过FLASHBACK_TRANSACTION_QUERY来恢复事务。
flashback version query参考:
Oracle10g的Flashback version Query
1.通过flashback version query获得XID
EYGLE on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, username,user_id
3 from t versions between timestamp minvalue and maxvalue
4 /
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.34.49 AM 000A000B000000F1 D DBSNMP 22
30-MAR-05 09.34.49 AM 000A000B000000F1 D WMSYS 23
30-MAR-05 09.34.49 AM 000A000B000000F1 D OPERATOR 31
30-MAR-05 09.34.49 AM 000A000B000000F1 D TRANS 27
30-MAR-05 09.34.49 AM 000A000B000000F1 D DIP 19
30-MAR-05 09.34.49 AM 000A000B000000F1 D SCOTT 29
30-MAR-05 09.34.49 AM 000A000B000000F1 D TEST 25
30-MAR-05 09.34.15 AM 0001001900000F0F U EYGLE 1
30-MAR-05 09.33.51 AM 00080016000000EF D TEST1 28
30-MAR-05 09.33.23 AM 0004000A000005EF D OUTLN 11
SYSTEM 5
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
SYS 0
30-MAR-05 09.34.49 AM TEST 25
30-MAR-05 09.34.15 AM EYGLE 26
30-MAR-05 09.34.49 AM SCOTT 29
30-MAR-05 09.34.49 AM DIP 19
30-MAR-05 09.34.49 AM TRANS 27
30-MAR-05 09.33.51 AM TEST1 28
30-MAR-05 09.34.49 AM OPERATOR 31
30-MAR-05 09.34.49 AM WMSYS 23
30-MAR-05 09.34.49 AM DBSNMP 22
30-MAR-05 09.33.23 AM OUTLN 11
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.49.24 AM 00080006000000EF I PENNY 2
23 rows selected.
|
2.恢复XID=000A000B000000F1的事务
注意:观察到x$ktuqqry的查询非常耗时，所以请注意评估你的恢复成本。
由于x$ktuqqry表的xid字段上不存在索引(很奇怪，而且我们不能自己添加，估计后续版本中会有改进)，查询该表会导致犬表扫描。
以下测试中，x$ktuqqry中存在大约19万记录，查询一次需要近6分钟。
SYS AS SYSDBA on 30-MAR-05 >select count(addr) from x$ktuqqry;
COUNT(ADDR)
-----------
196015
SYS AS SYSDBA on 30-MAR-05 >set autotrace on
SYS AS SYSDBA on 30-MAR-05 >SELECT /*+ rule */ UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE XID = '000A000B000000F1';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');
8 rows selected.
Elapsed: 00:05:53.44
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED))
Statistics
----------------------------------------------------------
393399 recursive calls
0 db block gets
1559016 consistent gets
4388 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
664 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
23164 sorts (memory)
0 sorts (disk)
8 rows processed
SYS AS SYSDBA on 30-MAR-05 >SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
2 WHERE XID = '000A000B000000F1';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DBSNMP','22');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('WMSYS','23');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('OPERATOR','31');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TRANS','27');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('DIP','19');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('SCOTT','29');
insert into "EYGLE"."T"("USERNAME","USER_ID") values ('TEST','25');
8 rows selected.
Elapsed: 00:05:55.30
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=1 Bytes=2008)
1 0 FIXED TABLE (FULL) OF 'X$KTUQQRY' (TABLE (FIXED)) (Cost=25 Card=1 Bytes=2008)
Statistics
----------------------------------------------------------
393454 recursive calls
0 db block gets
1562425 consistent gets
4644 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
664 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
23166 sorts (memory)
0 sorts (disk)
8 rows processed
SYS AS SYSDBA on 30-MAR-05 >
|
3.通过UNDO语句我们可以撤销该事物。
Posted by eygle at 10:55 AM
| Comments (0)
作者:eygle
Oracle10g通过Flashback Version Query提供查看对数据库事务级改变的方法.
当闪回事务处理查询与闪回版本查询同时使用时，我们可以轻易地从用户或者应用程序错误恢复。
以下是闪回版本查询的示例:
1.执行DML操作
EYGLE on 30-MAR-05 >create table t as select username,user_id from dba_users;
Table created.
EYGLE on 30-MAR-05 >select * from t;
USERNAME USER_ID
------------------------------ ----------
SYSTEM 5
SYS 0
TEST 25
EYGLE 26
SCOTT 29
DIP 19
TRANS 27
TEST1 28
OPERATOR 31
WMSYS 23
DBSNMP 22
USERNAME USER_ID
------------------------------ ----------
OUTLN 11
12 rows selected.
EYGLE on 30-MAR-05 >delete from t where username='OUTLN';
1 row deleted.
EYGLE on 30-MAR-05 >commit;
Commit complete.
EYGLE on 30-MAR-05 >delete from t where username='TEST1';
1 row deleted.
EYGLE on 30-MAR-05 >commit;
Commit complete.
EYGLE on 30-MAR-05 >select * from t;
USERNAME USER_ID
------------------------------ ----------
SYSTEM 5
SYS 0
TEST 25
EYGLE 26
SCOTT 29
DIP 19
TRANS 27
OPERATOR 31
WMSYS 23
DBSNMP 22
10 rows selected.
EYGLE on 30-MAR-05 >update t set user_id=1 where username='EYGLE';
1 row updated.
EYGLE on 30-MAR-05 >commit;
Commit complete.
EYGLE on 30-MAR-05 >delete from t where user_id >10;
7 rows deleted.
EYGLE on 30-MAR-05 >commit;
Commit complete.
EYGLE on 30-MAR-05 >select * from t;
USERNAME USER_ID
------------------------------ ----------
SYSTEM 5
SYS 0
EYGLE 1
EYGLE on 30-MAR-05 >insert into t values('PENNY',2);
1 row created.
EYGLE on 30-MAR-05 >commit;
Commit complete.
|
2.执行闪回版本查询
EYGLE on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid,
2 versions_operation, username,user_id
3 from t versions between timestamp minvalue and maxvalue
4 /
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.34.49 AM 000A000B000000F1 D DBSNMP 22
30-MAR-05 09.34.49 AM 000A000B000000F1 D WMSYS 23
30-MAR-05 09.34.49 AM 000A000B000000F1 D OPERATOR 31
30-MAR-05 09.34.49 AM 000A000B000000F1 D TRANS 27
30-MAR-05 09.34.49 AM 000A000B000000F1 D DIP 19
30-MAR-05 09.34.49 AM 000A000B000000F1 D SCOTT 29
30-MAR-05 09.34.49 AM 000A000B000000F1 D TEST 25
30-MAR-05 09.34.15 AM 0001001900000F0F U EYGLE 1
30-MAR-05 09.33.51 AM 00080016000000EF D TEST1 28
30-MAR-05 09.33.23 AM 0004000A000005EF D OUTLN 11
SYSTEM 5
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
SYS 0
30-MAR-05 09.34.49 AM TEST 25
30-MAR-05 09.34.15 AM EYGLE 26
30-MAR-05 09.34.49 AM SCOTT 29
30-MAR-05 09.34.49 AM DIP 19
30-MAR-05 09.34.49 AM TRANS 27
30-MAR-05 09.33.51 AM TEST1 28
30-MAR-05 09.34.49 AM OPERATOR 31
30-MAR-05 09.34.49 AM WMSYS 23
30-MAR-05 09.34.49 AM DBSNMP 22
30-MAR-05 09.33.23 AM OUTLN 11
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID
------------------------------ ------------------------------ ---------------- - ---------- ----------
30-MAR-05 09.49.24 AM 00080006000000EF I PENNY 2
23 rows selected.
EYGLE on 30-MAR-05 >
|
我们可以看到，以上事务的时间以及数据更改。
Posted by eygle at 9:56 AM
| Comments (0)
作者:eygle
Oracle10g对于闪回查询进行了增强，支持更简单的SQL操作，允许对误删除、误更新等DML操作进行闪回。
看一下以下测试:
1.原表记录
$ sqlplus eygle/eygle
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 30 08:52:04 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
EYGLE on 30-MAR-05 >select count(*) from t1;
COUNT(*)
----------
9318
|
2.误删除所有记录
并且提交更改。
EYGLE on 30-MAR-05 >delete from t1;
9318 rows deleted.
EYGLE on 30-MAR-05 >commit;
Commit complete.
EYGLE on 30-MAR-05 >select count(*) from t1;
COUNT(*)
----------
0
|
3.获得当前SCN
如果能够确切知道删除之前SCN最好，如果不知道，可以进行闪回查询尝试.
EYGLE on 30-MAR-05 >select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
10671006
EYGLE on 30-MAR-05 >select count(*) from t1 as of scn 10671000;
COUNT(*)
----------
0
EYGLE on 30-MAR-05 >select count(*) from t1 as of scn 10670000;
COUNT(*)
----------
9318
|
我们看到在SCN=10670000时，数据都在。
4.恢复数据.
EYGLE on 30-MAR-05 >insert into t1 select * from t1 as of scn 10670000;
9318 rows created.
EYGLE on 30-MAR-05 >commit;
Commit complete.
EYGLE on 30-MAR-05 >select count(*) from t1;
COUNT(*)
----------
9318
EYGLE on 30-MAR-05 >
|
Posted by eygle at 9:00 AM
| Comments (1)
