Oracle数据库表空间级的异地不完全恢复

Oracle 資料庫做異地恢復時，全庫的完全恢復需要很大的空間和很長的時間。

如果採用基於模式的恢復方式僅僅恢復某一模式下所有物件，而不用去恢復所有的資料，這將節省大量的時間和空間。

這個在 Oracle 9i 版本中經常使用，在 10g 、 11g 中也可以使用一下。

下面案例是在 10g 的環境下將一個 ASM 上的 RAC 資料庫的資料恢復到一個檔案系統上單實例資料庫中，並且是只恢復幾個使用者下的資料。

操作步驟如下：

1 、將備份組拷貝到要恢復的機器上，如果空間夠的話，可以將資料備份、控制檔案備份和歸檔備份一起拷貝過來，如果不夠的話，先拷貝控制檔案備份和資料檔案備份。

2 、關閉資料庫，啟動到 nomount 狀態進行控制檔的恢復

shutdown immediate;

startup nomount;

rman target /

restore controlfile from '/data/urpdb/urpdb_ctl_ikn3tot4_1_1.20120222';

3 、修改控制檔

查詢要恢復的使用者的表空間資訊

  select owner,tablespace_name,count(*) from dba_segments group by owner,tablespace_name;

查找這些表空間和系統表空間（ 'SYSTEM','SYSAUX', 'UNDOTBS1','UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4' ）對應的資料檔案

select *

  from dba_data_files

 where tablespace_name in

       ('TSP_URP', 'TSP_URP_INDEX', 'TSP_JC', 'TSP_JC_INDEX', 'TSP_GXSJ',

        'TSP_GXSJ_INDE', 'TSP_OWB', 'TSP_YJSSJCK', 'USERS', 'SYSTEM',

        'SYSAUX', 'UNDOTBS1','UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4');

SQL> alter database backup controlfile to trace;

修改控制檔並執行：

CREATE CONTROLFILE REUSE DATABASE "URPDB" RESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES 192

    MAXLOGMEMBERS 3

    MAXDATAFILES 1024

    MAXINSTANCES 32

    MAXLOGHISTORY 9088

LOGFILE

  GROUP 1 (

    '/opt/app/oracle/oradata/URPDB/group1_2.log',

    '/opt/app/oracle/oradata/URPDB/group1_1.log'

  ) SIZE 10M,

  GROUP 2 (

    '/opt/app/oracle/oradata/URPDB/group2_2.log',

    '/opt/app/oracle/oradata/URPDB/group2_1.log'

  ) SIZE 10M,

  GROUP 3 (

    '/opt/app/oracle/oradata/URPDB/group3_2.log',

    '/opt/app/oracle/oradata/URPDB/group3_1.log'

  ) SIZE 10M,

  GROUP 11 '/opt/app/oracle/oradata/URPDB/group11_1.log'  SIZE 100M,

  GROUP 12 '/opt/app/oracle/oradata/URPDB/group12_1.log'  SIZE 100M,

  GROUP 13 '/opt/app/oracle/oradata/URPDB/group13_1.log'  SIZE 100M,

  GROUP 14 '/opt/app/oracle/oradata/URPDB/group14_1.log'  SIZE 100M,

  GROUP 15 '/opt/app/oracle/oradata/URPDB/group15_1.log'  SIZE 100M

-- STANDBY LOGFILE

DATAFILE

  '/opt/app/oracle/oradata/URPDB/system01.dbf',

  '/opt/app/oracle/oradata/URPDB/undotbs1.dbf',

  '/opt/app/oracle/oradata/URPDB/sysaux.dbf',

  '/opt/app/oracle/oradata/URPDB/users.dbf',

  '/opt/app/oracle/oradata/URPDB/undotbs2.dbf',

  '/opt/app/oracle/oradata/URPDB/tsp_urp.dbf',

  '/opt/app/oracle/oradata/URPDB/tsp_urp_index.dbf',

  '/opt/app/oracle/oradata/URPDB/tsp_gxsj.dbf',

  '/opt/app/oracle/oradata/URPDB/tsp_urp.dbf_1.ora',

  '/opt/app/oracle/oradata/URPDB/tsp_owb.ora',

  '/opt/app/oracle/oradata/URPDB/tsp_jc.dbf',

  '/opt/app/oracle/oradata/URPDB/tsp_jc_index.dbf',

  '/opt/app/oracle/oradata/URPDB/tsp_yjssjck.dbf',

  '/opt/app/oracle/oradata/URPDB/undotbs03.dbf',

  '/opt/app/oracle/oradata/URPDB/undotbs04.dbf'

CHARACTER SET AL32UTF8

;

mount 資料庫；

SQL> alter database mount;

select name from v$datafile;

--select 'alter database rename file '''||name||''' to '''|| replace(name,'+URPDBDG','/opt/app/oracle/oradata/URPDB')||''';' from  v$datafile;

3 、將備份資料檔案拷貝到測試機

rman>catalog start with '/data/urpdb/';   執行一下，就是將這個目錄下的資料檔案的備份檔案寫入了 catalog 。

4 、 restore 資料庫

select file#,name from v$datafile ； --- 看看控制檔中資料檔案存放的位置，這裡是 “ +URPDBDG/..." ，替換為目標資料庫檔存放的位置。

set linesize 300

column name format a200

set pagesize 100

select 'set newname for datafile '||file#||' to '''||replace(name,'+URPDBDG','/opt/app/oracle/oradata/urpdb')||''';' newname from v$datafile -- 如果一個目標目錄不夠，可以用多個目標目錄

union all

select 'restore datafile '||file#||';' newname from v$datafile;

select *

  from dba_data_files

 where tablespace_name in

       ('TSP_URP', 'TSP_URP_INDEX', 'TSP_JC', 'TSP_JC_INDEX', 'TSP_GXSJ',

        'TSP_GXSJ_INDE', 'TSP_OWB', 'TSP_YJSSJCK', 'USERS', 'SYSTEM',

        'SYSAUX', 'UNDOTBS1','UNDOTBS2', 'UNDOTBS3', 'UNDOTBS4');

rman>

run

{

allocate channel ch01 type disk;

allocate channel ch02 type disk;

set until time "to_date('2012-02-23 10:00:00','yyyy-mm-dd hh24:mi:ss')";

set newname for datafile 1 to '/opt/app/oracle/oradata/URPDB/system01.dbf';

set newname for datafile 2 to '/opt/app/oracle/oradata/URPDB/undotbs1.dbf';

set newname for datafile 3 to '/opt/app/oracle/oradata/URPDB/sysaux.dbf';

set newname for datafile 4 to '/opt/app/oracle/oradata/URPDB/users.dbf';

set newname for datafile 6 to '/opt/app/oracle/oradata/URPDB/undotbs2.dbf';

set newname for datafile 9 to '/opt/app/oracle/oradata/URPDB/tsp_urp.dbf';

set newname for datafile 10 to '/opt/app/oracle/oradata/URPDB/tsp_urp_index.dbf';

set newname for datafile 14 to '/opt/app/oracle/oradata/URPDB/tsp_gxsj.dbf';

set newname for datafile 16 to '/opt/app/oracle/oradata/URPDB/tsp_urp.dbf_1.ora';

set newname for datafile 18 to '/opt/app/oracle/oradata/URPDB/tsp_owb.ora';

set newname for datafile 19 to '/opt/app/oracle/oradata/URPDB/tsp_jc.dbf';

set newname for datafile 20 to '/opt/app/oracle/oradata/URPDB/tsp_jc_index.dbf';

set newname for datafile 26 to '/opt/app/oracle/oradata/URPDB/tsp_yjssjck.dbf';

set newname for datafile 29 to '/opt/app/oracle/oradata/URPDB/undotbs03.dbf';

set newname for datafile 30 to '/opt/app/oracle/oradata/URPDB/undotbs04.dbf';

restore datafile 1;

restore datafile 2;

restore datafile 3;

restore datafile 4;

restore datafile 6;

restore datafile 9;

restore datafile 10;

restore datafile 14;

restore datafile 16;

restore datafile 18;

restore datafile 19;

restore datafile 20;

restore datafile 26;

restore datafile 29;

restore datafile 30;

switch datafile all;

release channel ch01;

release channel ch02;

}

5 、修改日誌檔和暫存檔案的路徑為目標資料庫的路徑（可與 restore 同時進行）

select member from v$logfile;

select name from v$tempfile;

select 'alter database rename file '''||member||''' to '''|| replace(member,'+URPDBDG','/opt/app/oracle/oradata/URPDB')||''';' from  v$logfile;

alter database rename file '+YWKDG/ywk/onlinelog/group15_1.log' to '/opt/app/oracle/oradata2/YWKDG/group15_1.log';

。。。。。

select 'alter database rename file '''||name||''' to '''|| replace(name,'+URPDBDG','/opt/app/oracle/oradata2/urpdb/')||''';' from  v$tempfile;

alter database rename file '+YWKDG/ywk/tempfile/tsp_zc_temp02.dbf' to  '/opt/app/oracle/oradata2/YWKDG/tsp_zc_temp02.dbf';

6 、將歸檔檔拷到目標資料庫伺服器，

rman>catalog start with '/data/urpdb/';   執行一下，就是將這個目錄下的歸檔檔的備份檔案寫入了 catalog 。

run

{

allocate channel ch01 type disk;

allocate channel ch02 type disk;

set until time "to_date('2012-02-23 10:00:00','yyyy-mm-dd hh24:mi:ss')";

recover database;

release channel ch01;

release channel ch02;

}

或則

SQL> select to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') from v$datafile;

S QL> select file#,checkpoint_change# from v$datafile order by checkpoint_change#;

     FILE# CHECKPOINT_CHANGE#

---------- ------------------

        14         2793109724

        20         2793109725

         6         2793232002

        19         2793232002

        18         2793258889

        26         2793258889

         9         2793281171

        30         2793281171

         1         2793283158

        16         2793301522

        29         2793301522

         2         2793304369

        10         2793304369

         4         2793333238

         3         2793333238

15 rows selected.

run

{

allocate channel ch01 type disk;

allocate channel ch02 type disk;

set until scn  2793333238;

recover database ；

release channel ch01;

release channel ch02;

}

如果 recover 資料庫缺少檔，可以到主庫去查是哪個檔，然後拷貝過來再 recover 一次。

RMAN> list backupset of archivelog logseq 7438 thread 4;

RMAN> crosscheck backupset;

delete expired backupset;

7 、 alter database open resetlogs;

墙外：http://mikixiyou.blogspot.com/2012/04/oracle.html