ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > 恢复被执行truncate table的表数据

恢复被执行truncate table的表数据

原创 Linux操作系统 作者：myownstars 时间：2011-10-31 16:42:51 0 删除 编辑

1、
当开启db recovery的时候，只需闪回数据库即可

SQL> create table test1(id number(5)) tablespace data01;
Table created.

SQL> create table test2(id number(5)) tablespace data01;
Table created.

SQL> declare
  2  begin
  3    for i in 1..10 loop
  4      insert into test1 values(i);
  5      insert into test2 values(i);
  6    end loop;
  7    commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                14876294

SQL> truncate table test1;

Table truncated.

SQL> insert into test2 values(100);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1;

no rows selected

SQL> select * from test2;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
       100

11 rows selected.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2230768 bytes
Variable Size            1224738320 bytes
Database Buffers         1962934272 bytes
Redo Buffers               16932864 bytes
Database mounted.
--闪回数据库至表test1的truncate操作之前
SQL> flashback database to scn 14876294;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select * from justin.test1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> select * from justin.test2;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.
表test2只恢复了10条数据，值为100的记录不存在，此时可以将test1的数据导出；不可使用数据泵，因为会要求先创建相应的master table
[oracle@testdb ~]$ expdp justin/justin directory=exp_dir tables=test1 dumpfile=test1.dmp

Export: Release 11.2.0.2.0 - Production on Mon Oct 24 00:51:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "JUSTIN.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-16000: database open for read-only access
但是可以使用exp
[oracle@testdb ~]$ exp justin/justin tables=test1 file=test1.dmp compress=n

Export: Release 11.2.0.2.0 - Production on Mon Oct 24 01:08:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                          TEST1         10 rows exported
Export terminated successfully without warnings.
导出test1后，关闭数据库进行完全恢复
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2230768 bytes
Variable Size            1224738320 bytes
Database Buffers         1962934272 bytes
Redo Buffers               16932864 bytes
Database mounted.
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select * from justin.test1;

no rows selected

SQL> select * from justin.test2;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
       100

11 rows selected.
使用imp导入
[oracle@testdb ~]$ imp justin/justin file=test1.dmp tables=test1 data_only=y

Import: Release 11.2.0.2.0 - Production on Mon Oct 24 01:14:31 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing JUSTIN's objects into JUSTIN
. . importing table                        "TEST1"         10 rows imported
Import terminated successfully without warnings.

SQL> select * from justin.test1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

 

2、没有开启闪回数据库的情形下，最直接的方法则是 基于表空间的时间点恢复
先对数据库做个全备份--备份数据库需要注意，要么将archive log一起备份，要么将从备份时间点开始产生的archive log复制到新的auxiliary数据库的归档日志目录下
然后创建一个新的auxiliary数据库
创建一个新的initora文件，db_name保持不变，修改db_unique_name
[oracle@testdb dbs]$ cat initauxiliary.ora
compatible='11.1.0.6'
control_files='/data/oracle/oradata/auxiliary/control1.ctl','/data/oracle/oradata/auxiliary/control2.ctl','/data/oracle/oradata/auxiliary/control3.ctl'
db_block_size=8192
db_name='justin'
db_unique_name='auxiliary'
UNDO_MANAGEMENT='auto'
undo_tablespace='undotbs1'
创建口令文件
[oracle@testdb dbs]$ $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwauxiliary password=oracle entries=5
开始测试
SQL> create table test1(id number(5)) tablespace data01;
Table created.

SQL> create table test2(id number(5)) tablespace data01;
Table created.

SQL> declare
  2  begin
  3    for i in 1..10 loop
  4      insert into test1 values(i);
  5      insert into test2 values(i);
  6    end loop;
  7    commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                14879153

SQL> truncate table test1;

Table truncated.

SQL> insert into test2 values(100);

1 row created.

SQL> commit;

Commit complete.

先从备份中恢复出控制文件
restore controlfile from '/data/oracle/flash_recovery_area/justin/justin/backupset/2011_10_24/o1_mf_ncsnf_TAG20111024T014942_7b8nz4qd_.bkp';
然后将其mount
sql 'alter database mount clone database';
恢复部分数据文件，对于不需要用到的数据文件选择跳过，
run{
set until scn 14879153;
set newname for datafile 1 to '/data/oracle/oradata/auxiliary/system01.dbf';
set newname for datafile 2 to '/data/oracle/oradata/auxiliary/sysaux.dbf';
set newname for datafile 3 to '/data/oracle/oradata/auxiliary/undotbs01.dbf';
set newname for datafile 5 to '/data/oracle/oradata/auxiliary/data01.dbf';
restore tablespace system,sysaux,undotbs1,data01;
switch datafile all;
sql 'alter database datafile 1,2,3,5 online';
recover database skip forever tablespace arch;
}
以上skip对应的日志如下
Executing: alter database datafile 4 offline drop
starting media recovery

目前数据库状态为mount，对应Online log采用rename的方式将其创建
alter database rename file '/data/oracle/oradata/justin/redo1.log' to '/data/oracle/oradata/auxiliary/redo1.log';
alter database rename file '/data/oracle/oradata/justin/redo2.log' to '/data/oracle/oradata/auxiliary/redo2.log';
alter database rename file '/data/oracle/oradata/justin/redo3.log' to '/data/oracle/oradata/auxiliary/redo3.log';
alter database rename file '/data/oracle/oradata/justin/redo4.log' to '/data/oracle/oradata/auxiliary/redo4.log';

SQL>  select * from v$logfile;                                                                                
                                                                                                              
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_
---------- ------- ------- -------------------------------------------------------------------------------- ---
         1         ONLINE  /data/oracle/oradata/auxiliary/redo1.log                                         NO
         2         ONLINE  /data/oracle/oradata/auxiliary/redo2.log                                         NO
         3         ONLINE  /data/oracle/oradata/auxiliary/redo3.log                                         NO
         4         ONLINE  /data/oracle/oradata/auxiliary/redo4.log                                         NO
 
此时将数据库open resetlogs
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data/oracle/oradata/auxiliary/system01.dbf'

注：刚开始没有备份archivelog，导致不完全恢复数据库时候出现错误并失败；
查看rman备份集的时间2011-10-24 01:46:00，其对应的SCN
SQL> select timestamp_to_scn(to_timestamp('2011-10-24 01:45:00','yyyy-mm-dd hh24:mi:ss')) from dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP('2011-10-2401:45:00','YYYY-MM-DDHH24:MI:SS'))
---------------------------------------------------------------------------
                                                                   14603504
rman登录源数据库，执行backup archivelog from scn 14603504；
然后rman登录新建的auxiliary数据库，将该备份集进行catalog，接着重新执行
run{
set until scn 14879153;
set newname for datafile 1 to '/data/oracle/oradata/auxiliary/system01.dbf';
set newname for datafile 3 to '/data/oracle/oradata/auxiliary/undotbs01.dbf';
set newname for datafile 5 to '/data/oracle/oradata/auxiliary/data01.dbf';
restore tablespace system,undotbs1,data01;
switch datafile all;
sql 'alter database datafile 1,3,5 online';
recover database skip forever tablespace sysaux;
}
此时进行open resetlogs
SQL> alter database open resetlogs;

Database altered

SQL> select * from justin.test1;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

此时可以使用数据泵将test1导入到源数据库中

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-709985/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 手工创建oracle数据库
下一篇： 利用REGEXP_LIKE查看包含大写字母的用户名
请登录后发表评论 登录
全部评论
myownstars

注册时间：2010-03-18

  • 博文量
    375
  • 访问量
    3320700

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593