oracle表数据恢复1

转载 2012年03月31日 11:58:06

1、建表

-- Create table
create table DARCY
(
ID   NUMBER,
INFO NVARCHAR2(32)
)
tablespace DATA_SGPM
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

2、插入数据

insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');

insert into "SGPM"."DARCY"("ID","INFO") values ('2','bbb');

insert into "SGPM"."DARCY"("ID","INFO") values ('3','ccc');

3、删除数据

SQL> select * from darcy;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

SQL> delete from darcy where id = 1;

1 row deleted

SQL> commit;

Commit complete

SQL> select * from darcy;

        ID INFO
---------- --------------------------------------------------------------------------------
         2 bbb
         3 ccc

4、恢复数据

方法1:

查询最新的系统变更number

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                18144344

查看此次变更后的表记录

SQL> select * from darcy as of scn 18144344;

        ID INFO
---------- --------------------------------------------------------------------------------
         2 bbb
         3 ccc

说明这是删除数据后的表记录,我们只要找到某个scn,即删除表记录前的scn,

恢复到这个scn时的记录。

SQL> SELECT * FROM DARCY as of scn 18144252;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

然后直接执行insert语句

方法2:

SQL> select * from flashback_transaction_query where table_name='DARCY';

XID               START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER                     UNDO_CHANGE# OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
07001500AB280000   18144149 2010-9-9 10:14:   18144281 2010-9-9 10:17:1 SGPM                                      1 DELETE                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAA insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');
080018005F370000   18144244 2010-9-9 10:16:   18144252 2010-9-9 10:16:3 SGPM                                      1 INSERT                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAC delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAC';
080018005F370000   18144244 2010-9-9 10:16:   18144252 2010-9-9 10:16:3 SGPM                                      2 INSERT                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAB delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAB';
080018005F370000   18144244 2010-9-9 10:16:   18144252 2010-9-9 10:16:3 SGPM                                      3 INSERT                           DARCY                                                                            SGPM                             AAAYQwAAcAAAKk2AAA delete from "SGPM"."DARCY" where ROWID = 'AAAYQwAAcAAAKk2AAA';

执行UNDO_SQL,即:insert into "SGPM"."DARCY"("ID","INFO") values ('1','aaa');即可恢复数据。

或者直接运行:

SQL> flashback table DARCY to timestamp to_timestamp('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss');

flashback table DARCY to timestamp to_timestamp('2010-9-9 10:16:3','yyyy-mm-dd hh24:mi:ss')

ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> alter table DARCY enable row movement;

Table altered

SQL> flashback table DARCY to timestamp to_timestamp(2010-9-9 10:17:1,'yyyy-mm-dd hh24:mi:ss');

Done

此处注意闪回时间点的选取,如时间点在表建立之前会提示ORA-01466:无法读取数据的错误;

如在闪回前对表结构进行修改也会提示此错误.

 

SQL> SELECT * FROM DARCY;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc

5、drop表后的恢复

SQL> drop table darcy;

Table dropped

SQL> select * from darcy;

select * from darcy

ORA-00942: 表或视图不存在

SQL> select * from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$CrbfFp0nRTWzETrAMvbD+A==$0 DARCY                            DROP      TABLE                     DATA_SGPM

SQL> SELECT * FROM USER_RECYCLEBIN;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE                      TS_NAME                        CREATETIME          DROPTIME               DROPSCN PARTITION_NAME                   CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$CrbfFp0nRTWzETrAMvbD+A==$0 DARCY                            DROP      TABLE                     DATA_SGPM                      2010-09-09:10:15:50 2010-09-09:11:12:04   18154031                                  YES        YES            99376       99376        99376          8

SQL> flashback table darcy to before drop;

Done

SQL> select * from darcy;

        ID INFO
---------- --------------------------------------------------------------------------------
         1 aaa
         2 bbb
         3 ccc 

Oracle Study之案例--数据恢复神器Flashback(1)

Oracle Study之案例--数据恢复神器Flashback(1)Flashback:            Flashback 技术是以Undo segment中的内容为基础的, 因此受限于UN...
  • lqx0405
  • lqx0405
  • 2015年03月31日 12:11
  • 451

Oracle 进行表数据恢复(转)

Oracle 表数据恢复

Oracle 表和表数据恢复

1. 表恢复    对误删的表,只要没有使用 purge 永久删除选项,那么基本上是能从 flashback table 区恢复回来的。    数据表和其中的数据都是可以恢复回来的,记得 ...

oracle 锁表与解锁、数据恢复

锁表与解锁 SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK...

ORACLE将表中的数据恢复到某一个时间点

概念: You perform a Flashback Query by using a SELECT statementwith an AS OF clause.You use a flashba...

oracle表数据恢复2

1.表查询闪回 create table xcp as (select * from b_za_bzdzkxx); select * from xcp; select count(1) from...

oracle表数据恢复

  • 2013年04月11日 17:38
  • 149B
  • 下载

Oracle 11G Rman备份ASM数据恢复到本地磁盘

在日常工作中,我们经常会遇到需要将使用ASM存储的数据迁移到本地磁盘中,迁移之后的数据库可用于测试等用途。可以选择的工具很多,exp/imp、 expdp/impdp、rman,前两种方法因为比较适合...

专为Oracle数据恢复而生 - PRM

PRM是Oracle企业级灾难恢复工具,其具备Oracle DUL的数据恢复能力,又兼顾了软件的易用性。   PRM For Oracle Database 3.1版本主界面:   ...

oracle数据库delete 后数据恢复

oracle数据库delete后数据删除后恢复,需要依靠scn的编号来恢复,但是恢复时候需要找到之前scn活动的,才能恢复;...
  • bzhzhc
  • bzhzhc
  • 2016年08月31日 09:06
  • 581
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle表数据恢复1
举报原因:
原因补充:

(最多只允许输入30个字)