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

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

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

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匯入到源資料庫中

恢復被執行truncate table的表資料

相關文章