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博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-709985/，如需转载，请注明出处，否则将追究法律责任。