truncate table 的恢复

最新推荐文章于 2024-06-17 22:25:53 发布

cqhiabc50405

最新推荐文章于 2024-06-17 22:25:53 发布

阅读量225

点赞数

没有开启闪回数据库的情形下，最直接的方法则是基于表空间的时间点恢复
先对数据库做个全备份--备份数据库需要注意，要么将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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-738710/