ITPub博客

首页 > Linux操作系统 > Linux操作系统 > truncate table 的恢复

truncate table 的恢复

原创 Linux操作系统 作者:aaqwsh 时间:2012-07-30 14:23:22 0 删除 编辑

没有开启闪回数据库的情形下,最直接的方法则是 基于表空间的时间点恢复
先对数据库做个全备份--备份数据库需要注意,要么将archive log一起备份,要么将从备份时间点开始产生的archive log复制到新的auxiliary数据库的归档日志目录下
然后创建一个新的auxiliary数据库
创建一个新的initora文件,db_name保持不变,修改db_unique_name

[oracle@testdb dbs]$ cat  initqwsh.ora
*.control_files='/data/oracle/oradata/testqq/control1.ctl','/data/oracle/oradata/testqq/control2.ctl','/data/oracle/oradata/testqq/control3.ctl'
*.db_block_size=8192
*.db_name='wuxuan1'
*.db_unique_name='testqq'
*.UNDO_MANAGEMENT='auto'
*.undo_tablespace='undotbs1'
*.compatible='11.1.0.6'

创建口令文件
[oracle@testdb dbs]$ $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwtestqq password=oracle entries=5
开始测试
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
TEST1                          TABLE
TEST2                          TABLE

SQL> select * from test1;

no rows selected

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

PL/SQL procedure successfully completed.

SQL>
SQL> select * from test1;

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

10 rows selected.


SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     434935

SQL>
SQL> truncate table test1;

Table truncated.

SQL>
SQL> insert into test2 values(10000);

1 row created.

SQL> commit;

Commit complete.


先从备份中恢复出控制文件
restore controlfile from '/data/oracle/backup/rman/test01_06ng8hjk_1_1';

sql "alter database mount clone database";

 

catalog start with '/data/oracle/backup/rman';

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

恢复部分数据文件,对于不需要用到的数据文件选择跳过:
 run{
set until scn 434935;
  set newname for datafile 1 to '/data/oracle/oradata/testqq/system.dbf';
   set newname for datafile 2 to '/data/oracle/oradata/testqq/sysaux.dbf';
   set newname for datafile 3 to '/data/oracle/oradata/testqq/undotbs01.dbf';
   set newname for datafile 4 to '/data/oracle/oradata/testqq/qwsh.dbf';
  restore tablespace system,sysaux,undotbs1,qwsh;
  switch datafile all;
  sql 'alter database datafile 1,2,3,4 online';
recover database skip forever tablespace sysaux;
 }
以上skip对应的日志如下
Executing: alter database datafile 2 offline drop
starting media recovery

 

--可以只选择必要的表空间进行恢复:
run
{
allocate channel t1 type sbt_tape
parms='SBT_LIBRARY=/home/usupport/liblsm.so';

set until time "to_date( '08-10-2007 06:00', 'DD-MM-RRRR HH24:MI')";

set newname for datafile 1 to '/fs01/oradata/tspitr/system01.dbf';
set newname for datafile 2 to '/fs01/oradata/tspitr/undotbs01.dbf';
set newname for datafile 4 to '/fs01/oradata/tspitr/tools01.dbf';

restore tablespace system, undotbs1, tools;

switch datafile all;

sql "alter database datafile 1,2,4 online";

recover database skip forever tablespace TEMP,INDX,USERS,OLTS_ATTRSTORE,
OLTS_CT_DN,OLTS_CT_CN, OLTS_CT_OBJCL,OLTS_CT_STORE,OLTS_DEFAULT,
OLTS_TEMP,OLTS_IND_ATTRSTORE,
OLTS_IND_CT_DN,OLTS_IND_CT_CN,OLTS_IND_CT_OBJCL,OLTS_IND_CT_STORE,
P1TS_ATTRSTORE,P1TS_IND_STORE;

sql "alter database rename file ''/fs01/oradata/primary/REDO01.LOG'' to ''/fs01/oradata/tspitr/REDO01.LOG''";

sql "alter database rename file ''/fs01/oradata/primary/REDO02.LOG'' to ''/fs01/oradata/tspitr/REDO02.LOG''";

sql "alter database rename file ''/fs01/oradata/primary/REDO03.LOG'' to ''/fs01/oradata/tspitr/REDO03.LOG''";

/*NOTE: Syntax within rman is two single quotes around each name, this may be operating system specific. */

release channel t1;
}

 

 

SQL> alter database open read only;

Database altered.

SQL> conn qwsh/qwsh
Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select * from test1;

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

10 rows selected.

SQL> select * from test2;

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

11 rows selected.


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

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

下一篇: drop tablespace in 11G
请登录后发表评论 登录
全部评论

注册时间:2010-11-24

  • 博文量
    132
  • 访问量
    265736