暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 数据库恢复部分表空间

数据库恢复部分表空间

Oracle一体机用户组 2019-12-12
503
概述


某政府单位数据库由于应用维护人员的误操作，删除了HTQD2. KJ_MDT这张表中的数据，由于发现较晚，UNDO中的数据已被覆盖，已经无法通过闪回查询来恢复数据，需要对数据库的相关表空间做异机恢复，再导回该表数据。
 
htqd2上备份文件系统使用两个，/rmanhtqd2、/htqd2bak_huawei，通过检查需要恢复涉及的备份集在/htqd2bak_huawei，而部分归档在/rmanhtqd2，所以决定把/htqd2bak_huawei挂载到目标机器，而相关归档备份集通过ftp复制到目标机器。(/rmanhtqd2文件系统因为一些其它原因无法挂载到目标机器)
 
原数据库有将近5T的数据量，只恢复涉及的表空间，只需要恢复160g的数据量，大大加快故障处理时间。

恢复准备


检查要恢复的控制文件
 
$ rman target  nocatalog
 
Recovery Manager: Release 9.2.0.7.0 - 64bit Production
 
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
 
connected to target database: HTQD (DBID=975357423)
using target database controlfile instead of recovery catalog
 
RMAN> list backup of controlfile;
 
 
List of Backup Sets
===================
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50417   Full    10M        DISK        00:00:01     28-JUN-14      
        BP Key: 155019   Status: AVAILABLE   Tag:
        Piece Name: rmanhtqd2/ctrl_c-975357423-20140628-01
  Controlfile Included: Ckp SCN: 13938282616646   Ckp time: 28-JUN-14
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50492   Full    10M        DISK        00:00:01     29-JUN-14      
        BP Key: 155094   Status: AVAILABLE   Tag:
        Piece Name: rmanhtqd2/ctrl_c-975357423-20140629-00
  Controlfile Included: Ckp SCN: 13946400562431   Ckp time: 29-JUN-14
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50496   Full    10M        DISK        00:00:02     29-JUN-14      
        BP Key: 155098   Status: AVAILABLE   Tag:
        Piece Name: rmanhtqd2/ctrl_c-975357423-20140629-01
  Controlfile Included: Ckp SCN: 13946401583696   Ckp time: 29-JUN-14
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50571   Full    10M        DISK        00:00:02     01-JUL-14      
        BP Key: 155173   Status: AVAILABLE   Tag:
        Piece Name: rmanhtqd2/ctrl_c-975357423-20140701-00
  Controlfile Included: Ckp SCN: 13947769011242   Ckp time: 01-JUL-14
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50575   Full    10M        DISK        00:00:02     01-JUL-14      
        BP Key: 155177   Status: AVAILABLE   Tag:
        Piece Name: rmanhtqd2/ctrl_c-975357423-20140701-01
  Controlfile Included: Ckp SCN: 13947770026754   Ckp time: 01-JUL-14


确认需要恢复的控制文件在备份集/rmanhtqd2/ctrl_c-975357423-20140701-00中，需要把该备份集复制到目标机器
 
检查要恢复的表空间和数据文件
 
SQL> select tablespace_name from dba_tables t where t.table_name = 'KJ_MDT' and owner = 'HTQD2';
 
TABLESPACE_NAME
------------------------------
HTQD2_JK_DAT


需要恢复的数据文件

SQL> select file#, name from v$datafile where ts# in (
  2  select ts# from v$tablespace where name in ('UNDOTBS1', 'UNDOTBS2', 'SYSTEM', 'HTQD2_JK_DAT'));
 
     FILE# NAME
---------- --------------------------------------------------------------------
         1 dev/rlv3system01
         2 dev/rlv3undotbs11
        89 dev/rlv3htqd4g080
        90 dev/rlv3htqd4g081
        93 dev/rlv3htqd4g075
       356 dev/rlv3htqd10g161
       358 dev/rlv3htqd10g163
       357 dev/rlv3htqd10g162
       262 dev/rlv3htqd10g062
         3 dev/rlv3undotbs21
        91 dev/rlv3htqd4g082
       152 dev/rlv3htqd4g142
       154 dev/rlv3htqd4g144
       155 dev/rlv3htqd4g145
       153 dev/rlv3htqd4g143
        92 dev/rlv3htqd4g083
        72 dev/rlv3htqd10g005
        73 dev/rlv3htqd4g042
       525 dev/rlv3htqd10g337
       454 dev/rlv3htqd10g265
       426 dev/rlv3htqd10g239
       275 dev/rlv3htqd10g075
       226 dev/rlv3htqd4g190
        74 dev/rlv3htqd4g043
        75 dev/rlv3htqd4g044

 

需要忽略的表空间

SQL> select name from v$tablespace where name not in ('UNDOTBS1', 'UNDOTBS2', 'SYSTEM', 'HTQD2_JK_DAT');
 
NAME
------------------------------
HTQD2_DAT
HTQD_TMP
HTQD2_IDX
HTQD2_SB_DAT
HTQD2_SB_IDX
HTQD2_FP_DAT
HTQD2_CX_TMP
HTQD2_GLFW_DAT
HTQD2_FP_IDX
HTQD2_JK_IDX
HTQD2_GLFW_IDX
HTQD2_MAINT_DAT
SJCF_CS_DAT
 
13 rows selected


检查需要恢复的归档
 
RMAN> list backup of archivelog all;
 
 
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
50493   6G         DISK        00:01:20     2014/06/29 00:22:03
        BP Key: 155095   Status: AVAILABLE   Tag: TAG20140629T002043
        Piece Name: htqdbak_huawei/al_t851473243_s52174_p1-20140629
 
  List of Archived Logs in backup set 50493
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    39293   13938282616539 2014/06/28 05:09:50 13938502645070 2014/06/28 09:14:28
  1    39294   13938502645070 2014/06/28 09:14:28 13938767673627 2014/06/28 17:40:24
  1    39295   13938767673627 2014/06/28 17:40:24 13946395145605 2014/06/28 22:32:51
  1    39296   13946395145605 2014/06/28 22:32:51 13946395304622 2014/06/28 22:33:29
  1    39297   13946395304622 2014/06/28 22:33:29 13946397367953 2014/06/28 23:22:34
  1    39298   13946397367953 2014/06/28 23:22:34 13946398853449 2014/06/28 23:39:07
  1    39299   13946398853449 2014/06/28 23:39:07 13946400562457 2014/06/29 00:20:44
  1    39300   13946400562457 2014/06/29 00:20:44 13946400562471 2014/06/29 00:20:50
  2    27107   13938282616548 2014/06/28 05:09:35 13938485689497 2014/06/28 09:12:05
  2    27108   13938485689497 2014/06/28 09:12:05 13938502645006 2014/06/28 09:14:11
 
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
50494   7G         DISK        00:01:31     2014/06/29 00:22:14
        BP Key: 155096   Status: AVAILABLE   Tag: TAG20140629T002043
        Piece Name: htqdbak_huawei/al_t851473243_s52175_p1-20140629
 
  List of Archived Logs in backup set 50494
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    27109   13938502645006 2014/06/28 09:14:11 13946395157726 2014/06/28 22:32:36
  2    27110   13946395157726 2014/06/28 22:32:36 13946397201690 2014/06/28 23:09:34
  2    27111   13946397201690 2014/06/28 23:09:34 13946397362648 2014/06/28 23:13:49
  2    27112   13946397362648 2014/06/28 23:13:49 13946397367868 2014/06/28 23:22:15
  2    27113   13946397367868 2014/06/28 23:22:15 13946398845022 2014/06/28 23:30:39
  2    27114   13946398845022 2014/06/28 23:30:39 13946398848875 2014/06/28 23:31:52
  2    27115   13946398848875 2014/06/28 23:31:52 13946398853331 2014/06/28 23:38:48
  2    27116   13946398853331 2014/06/28 23:38:48 13946398856654 2014/06/28 23:40:05
 
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
50495   425M       DISK        00:00:08     2014/06/29 00:22:26
        BP Key: 155097   Status: AVAILABLE   Tag: TAG20140629T002043
        Piece Name: htqdbak_huawei/al_t851473338_s52176_p1-20140629
 
  List of Archived Logs in backup set 50495
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    27117   13946398856654 2014/06/28 23:40:05 13946400562453 2014/06/29 00:20:26
  2    27118   13946400562453 2014/06/29 00:20:26 13946400562464 2014/06/29 00:20:33
 
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
50572   35G        DISK        00:13:35     2014/07/01 05:13:58
        BP Key: 155174   Status: AVAILABLE   Tag: TAG20140701T050022
        Piece Name: rmanhtqd2/al_t851749223_s52253_p1-20140701
 
  List of Archived Logs in backup set 50572
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    39301   13946400562471 2014/06/29 00:20:50 13946429413059 2014/06/29 09:33:02
  1    39302   13946429413059 2014/06/29 09:33:02 13946434927598 2014/06/29 10:12:21
  1    39303   13946434927598 2014/06/29 10:12:21 13947173837708 2014/06/29 22:32:58
  1    39304   13947173837708 2014/06/29 22:32:58 13947174079422 2014/06/29 22:33:34
  1    39305   13947174079422 2014/06/29 22:33:34 13947174244119 2014/06/29 22:35:43
  1    39306   13947174244119 2014/06/29 22:35:43 13947215230775 2014/06/30 08:40:19
  1    39307   13947215230775 2014/06/30 08:40:19 13947215541824 2014/06/30 08:51:16
  1    39308   13947215541824 2014/06/30 08:51:16 13947219092148 2014/06/30 09:33:56
  1    39309   13947219092148 2014/06/30 09:33:56 13947219901701 2014/06/30 09:55:33
  1    39310   13947219901701 2014/06/30 09:55:33 13947220272166 2014/06/30 10:05:12
  1    39311   13947220272166 2014/06/30 10:05:12 13947220809692 2014/06/30 10:18:01
  1    39312   13947220809692 2014/06/30 10:18:01 13947221381081 2014/06/30 10:25:12
  1    39313   13947221381081 2014/06/30 10:25:12 13947222736160 2014/06/30 10:36:22
  1    39314   13947222736160 2014/06/30 10:36:22 13947224506456 2014/06/30 10:53:15
  1    39315   13947224506456 2014/06/30 10:53:15 13947226303967 2014/06/30 11:11:23
  1    39316   13947226303967 2014/06/30 11:11:23 13947227171534 2014/06/30 11:20:29
  1    39317   13947227171534 2014/06/30 11:20:29 13947228637299 2014/06/30 11:29:10
  1    39318   13947228637299 2014/06/30 11:29:10 13947231840565 2014/06/30 11:50:43
  1    39319   13947231840565 2014/06/30 11:50:43 13947242486131 2014/06/30 12:14:53
  1    39320   13947242486131 2014/06/30 12:14:53 13947249010634 2014/06/30 13:04:21
  1    39321   13947249010634 2014/06/30 13:04:21 13947250670504 2014/06/30 13:26:45
  1    39322   13947250670504 2014/06/30 13:26:45 13947252568215 2014/06/30 13:48:02
  1    39323   13947252568215 2014/06/30 13:48:02 13947253901871 2014/06/30 14:04:10
  1    39324   13947253901871 2014/06/30 14:04:10 13947257369120 2014/06/30 14:18:00
  1    39325   13947257369120 2014/06/30 14:18:00 13947262649669 2014/06/30 14:29:47
  1    39326   13947262649669 2014/06/30 14:29:47 13947267314638 2014/06/30 14:39:04
  1    39327   13947267314638 2014/06/30 14:39:04 13947351209033 2014/06/30 14:50:21
  1    39328   13947351209033 2014/06/30 14:50:21 13947352445940 2014/06/30 15:00:11
  1    39329   13947352445940 2014/06/30 15:00:11 13947358811680 2014/06/30 15:14:14
  1    39330   13947358811680 2014/06/30 15:14:14 13947361340864 2014/06/30 15:29:44
  1    39331   13947361340864 2014/06/30 15:29:44 13947362607233 2014/06/30 15:35:01
  1    39332   13947362607233 2014/06/30 15:35:01 13947365889625 2014/06/30 15:44:31
  1    39333   13947365889625 2014/06/30 15:44:31 13947374209539 2014/06/30 15:58:00
  1    39334   13947374209539 2014/06/30 15:58:00 13947378237056 2014/06/30 16:09:07
  1    39335   13947378237056 2014/06/30 16:09:07 13947383458907 2014/06/30 16:29:34
  1    39336   13947383458907 2014/06/30 16:29:34 13947386103778 2014/06/30 16:40:05
 
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
50573   29G        DISK        00:13:43     2014/07/01 05:14:06
        BP Key: 155175   Status: AVAILABLE   Tag: TAG20140701T050022
        Piece Name: rmanhtqd2/al_t851749223_s52254_p1-20140701
 
  List of Archived Logs in backup set 50573
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    39337   13947386103778 2014/06/30 16:40:05 13947387121549 2014/06/30 16:45:10
  1    39338   13947387121549 2014/06/30 16:45:10 13947391028265 2014/06/30 16:59:44
  1    39339   13947391028265 2014/06/30 16:59:44 13947393739555 2014/06/30 17:09:08
  1    39340   13947393739555 2014/06/30 17:09:08 13947400159182 2014/06/30 17:29:13
  1    39341   13947400159182 2014/06/30 17:29:13 13947403913434 2014/06/30 17:52:47
  1    39342   13947403913434 2014/06/30 17:52:47 13947405613115 2014/06/30 18:04:04
  1    39343   13947405613115 2014/06/30 18:04:04 13947408806312 2014/06/30 18:24:11
  1    39344   13947408806312 2014/06/30 18:24:11 13947409778932 2014/06/30 18:37:06
  1    39345   13947409778932 2014/06/30 18:37:06 13947411143999 2014/06/30 18:46:06
  1    39346   13947411143999 2014/06/30 18:46:06 13947414017273 2014/06/30 19:08:48
  1    39347   13947414017273 2014/06/30 19:08:48 13947415303448 2014/06/30 19:24:04
  1    39348   13947415303448 2014/06/30 19:24:04 13947439196398 2014/06/30 22:33:14
  1    39349   13947439196398 2014/06/30 22:33:14 13947439405762 2014/06/30 22:33:51
  1    39350   13947439405762 2014/06/30 22:33:51 13947439560860 2014/06/30 22:38:11
  1    39351   13947439560860 2014/06/30 22:38:11 13947740844665 2014/07/01 02:34:18
  1    39352   13947740844665 2014/07/01 02:34:18 13947769016407 2014/07/01 05:00:26
  1    39353   13947769016407 2014/07/01 05:00:26 13947769019401 2014/07/01 05:00:29
  2    27119   13946400562464 2014/06/29 00:20:33 13946434927638 2014/06/29 10:12:06
  2    27120   13946434927638 2014/06/29 10:12:06 13946436911900 2014/06/29 11:48:06
  2    27121   13946436911900 2014/06/29 11:48:06 13947174245162 2014/06/29 22:35:27
  2    27122   13947174245162 2014/06/29 22:35:27 13947219094174 2014/06/30 09:33:42
  2    27123   13947219094174 2014/06/30 09:33:42 13947220810109 2014/06/30 10:17:46
  2    27124   13947220810109 2014/06/30 10:17:46 13947224506644 2014/06/30 10:52:59
  2    27125   13947224506644 2014/06/30 10:52:59 13947226117641 2014/06/30 11:09:12
  2    27126   13947226117641 2014/06/30 11:09:12 13947228646191 2014/06/30 11:28:57
  2    27127   13947228646191 2014/06/30 11:28:57 13947231602357 2014/06/30 11:46:43
  2    27128   13947231602357 2014/06/30 11:46:43 13947248419035 2014/06/30 12:54:28
  2    27129   13947248419035 2014/06/30 12:54:28 13947249020974 2014/06/30 13:05:29
  2    27130   13947249020974 2014/06/30 13:05:29 13947253902640 2014/06/30 14:03:54
  2    27131   13947253902640 2014/06/30 14:03:54 13947267330794 2014/06/30 14:38:49
  2    27132   13947267330794 2014/06/30 14:38:49 13947358825673 2014/06/30 15:13:59
  2    27133   13947358825673 2014/06/30 15:13:59 13947365903931 2014/06/30 15:44:17
  2    27134   13947365903931 2014/06/30 15:44:17 13947383461415 2014/06/30 16:29:18
  2    27135   13947383461415 2014/06/30 16:29:18 13947391032456 2014/06/30 16:59:31
  2    27136   13947391032456 2014/06/30 16:59:31 13947400539394 2014/06/30 17:30:00
  2    27137   13947400539394 2014/06/30 17:30:00 13947402396085 2014/06/30 17:44:13
  2    27138   13947402396085 2014/06/30 17:44:13 13947403909985 2014/06/30 17:52:28
  2    27139   13947403909985 2014/06/30 17:52:28 13947406747567 2014/06/30 18:14:07
  2    27140   13947406747567 2014/06/30 18:14:07 13947406951489 2014/06/30 18:15:05
  2    27141   13947406951489 2014/06/30 18:15:05 13947411148417 2014/06/30 18:45:52
  2    27142   13947411148417 2014/06/30 18:45:52 13947414550388 2014/06/30 19:14:44
  2    27143   13947414550388 2014/06/30 19:14:44 13947439414944 2014/06/30 22:33:37
  2    27144   13947439414944 2014/06/30 22:33:37 13947482694652 2014/07/01 01:00:32
 
BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
50574   4G         DISK        00:00:58     2014/07/01 05:15:12
        BP Key: 155176   Status: AVAILABLE   Tag: TAG20140701T050022
        Piece Name: rmanhtqd2/al_t851750054_s52255_p1-20140701
 
  List of Archived Logs in backup set 50574
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  2    27145   13947482694652 2014/07/01 01:00:32 13947485199367 2014/07/01 01:38:38
  2    27146   13947485199367 2014/07/01 01:38:38 13947740844140 2014/07/01 02:34:01
  2    27147   13947740844140 2014/07/01 02:34:01 13947744221340 2014/07/01 03:40:20
  2    27148   13947744221340 2014/07/01 03:40:20 13947768486690 2014/07/01 04:50:31
  2    27149   13947768486690 2014/07/01 04:50:31 13947769016275 2014/07/01 05:00:09
  2    27150   13947769016275 2014/07/01 05:00:09 13947769021757 2014/07/01 05:00:15


通过检查，其它归档备份集都在/htqd2bak_huawei文件系统，需要把/rmanhtqd2/al_t851749223_s52253_p1-20140701和/rmanhtqd2/al_t851749223_s52254_p1-20140701两个归档备份集复制到目标机器

恢复过程


把备份文件系统挂载到目标机器

把/htqd2bak_huawei挂载到目标机器
 
创建必要的目录
 
$ cd oracle/admin
$ mkdir htqd
$ mkdir htqd/bdump
$ mkdir htqd/cdump
$ mkdir htqd/udump


创建pfile文件

$ cd oracle/app/oracle/product/9.2.0/dbs
$ vi initctias1.ora
*.background_dump_dest='/oracle/admin/htqd/bdump'
*.cluster_database=false
*.compatible='9.2.0.7'
*.control_files='/htqdbak_huawei/htqdbak/ctrl01.ctl'
*.core_dump_dest='/oracle/admin/htqd/cdump'
*.cursor_sharing='SIMILAR'
*.cursor_space_for_time=true
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_cache_size=524288000
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_files=1200
*.db_name='htqd'
*.event='10281 trace name context forever,level 900'
*.fast_start_mttr_target=300
*.fast_start_parallel_rollback='FALSE'
*.hash_join_enabled=TRUE
*.instance_name='htqd1'
*.instance_number=1
*.java_pool_size=536870912
*.job_queue_processes=10
*.large_pool_size=104857600
*.lock_sga=true
*.log_archive_dest_1='LOCATION=/htqdbak_huawei/htqdbak/arch'
*.log_archive_start=TRUE
*.log_buffer=104857600
*.log_checkpoint_interval=10000
*.max_dump_file_size='10240'
*.open_cursors=300
*.parallel_max_servers=10
*.partition_view_enabled=true
*.pga_aggregate_target=524288000
*.pre_page_sga=true
*.processes=1800
*.remote_login_passwordfile='NONE'
*.sga_max_size=2097152000
*.shared_pool_size=536870912
*.thread=1
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=28000
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/htqd/udump'
*.workarea_size_policy='AUTO'


创建密码文件

$ cd oracle/app/oracle/product/9.2.0/dbs
$ orapwd file=orapwhtqd1 password=sys


启动数据库到nomount状态

$ export ORACLE_SID=htqd1
$ sqlplus nolog
 
SQL> conn  as sysdba
Connected to an idle instance.
SQL> startup nomount

 

恢复控制文件
 
$ export ORACLE_SID=htqd1
$ rman target  nocatalog
 
RMAN> restore controlfile from '/rmanhtqd2/ctrl_c-975357423-20140701-01';


启动数据库到mount状态

RMAN> alter database mount;
 
database mounted


使用RMAN检查必要的归档信息和数据文件信息是否都存在，以防止恢复了比较旧的控制文件。如果恢复了比较旧的控制文件，导致最后归档无法恢复，可以重新恢复比较新的控制再次恢复。
 
处理不必要的数据文件

处理一些不需要恢复的数据文件

alter database datafile '/dev/rlv3htqd10g002' offline drop;
alter database datafile '/dev/rlv3htqd4g004' offline drop;
alter database datafile '/dev/rlv3htqd4g005' offline drop;
alter database datafile '/dev/rlv3htqd4g006' offline drop;
alter database datafile '/dev/rlv3htqd4g007' offline drop;
alter database datafile '/dev/rlv3htqd4g008' offline drop;
alter database datafile '/dev/rlv3htqd4g009' offline drop;
alter database datafile '/dev/rlv3htqd4g010' offline drop;
alter database datafile '/dev/rlv3htqd4g011' offline drop;
alter database datafile '/dev/rlv3htqd4g012' offline drop;
alter database datafile '/dev/rlv3htqd4g013' offline drop;
alter database datafile '/dev/rlv3htqd4g014' offline drop;
alter database datafile '/dev/rlv3htqd4g015' offline drop;
alter database datafile '/dev/rlv3htqd4g016' offline drop;
alter database datafile '/dev/rlv3htqd10g004' offline drop;
alter database datafile '/dev/rlv3htqd4g029' offline drop;
alter database datafile '/dev/rlv3htqd4g030' offline drop;
alter database datafile '/dev/rlv3htqd4g031' offline drop;
alter database datafile '/dev/rlv3htqd4g032' offline drop;
alter database datafile '/dev/rlv3htqd4g033' offline drop;
alter database datafile '/dev/rlv3htqd4g034' offline drop;
alter database datafile '/dev/rlv3htqd4g035' offline drop;
alter database datafile '/dev/rlv3htqd4g036' offline drop;
alter database datafile '/dev/rlv3htqd4g037' offline drop;
alter database datafile '/dev/rlv3htqd4g038' offline drop;
alter database datafile '/dev/rlv3htqd4g039' offline drop;
alter database datafile '/dev/rlv3htqd4g040' offline drop;
alter database datafile '/dev/rlv3htqd4g041' offline drop;
alter database datafile '/dev/rlv3htqd10g006' offline drop;
alter database datafile '/dev/rlv3htqd4g048' offline drop;
alter database datafile '/dev/rlv3htqd4g049' offline drop;
alter database datafile '/dev/rlv3htqd4g050' offline drop;
alter database datafile '/dev/rlv3htqd4g051' offline drop;
alter database datafile '/dev/rlv3htqd4g052' offline drop;
alter database datafile '/dev/rlv3htqd4g053' offline drop;
alter database datafile '/dev/rlv3htqd4g054' offline drop;
alter database datafile '/dev/rlv3htqd4g055' offline drop;
alter database datafile '/dev/rlv3htqd4g056' offline drop;
alter database datafile '/dev/rlv3htqd4g057' offline drop;
alter database datafile '/dev/rlv3htqd4g058' offline drop;
alter database datafile '/dev/rlv3htqd4g059' offline drop;
alter database datafile '/dev/rlv3htqd4g060' offline drop;
alter database datafile '/dev/rlv3htqd8g005' offline drop;
alter database datafile '/dev/rlv3htqd4g061' offline drop;
alter database datafile '/dev/rlv3htqd4g062' offline drop;
alter database datafile '/dev/rlv3htqd4g063' offline drop;
alter database datafile '/dev/rlv3htqd4g064' offline drop;
alter database datafile '/dev/rlv3htqd4g065' offline drop;
alter database datafile '/dev/rlv3htqd4g066' offline drop;
alter database datafile '/dev/rlv3htqd4g067' offline drop;
alter database datafile '/dev/rlv3htqd4g068' offline drop;
alter database datafile '/dev/rlv3htqd4g069' offline drop;
alter database datafile '/dev/rlv3htqd4g070' offline drop;
alter database datafile '/dev/rlv3htqd4g071' offline drop;
alter database datafile '/dev/rlv3htqd4g072' offline drop;
alter database datafile '/dev/rlv3htqd4g073' offline drop;
alter database datafile '/dev/rlv3htqd10g003' offline drop;
alter database datafile '/dev/rlv3htqd4g017' offline drop;
alter database datafile '/dev/rlv3htqd4g018' offline drop;
alter database datafile '/dev/rlv3htqd4g019' offline drop;
alter database datafile '/dev/rlv3htqd10g001' offline drop;
alter database datafile '/dev/rlv3htqd4g001' offline drop;
alter database datafile '/dev/rlv3htqd4g002' offline drop;
alter database datafile '/dev/rlv3htqd4g003' offline drop;
alter database datafile '/dev/rlv3htqd8g002' offline drop;
alter database datafile '/dev/rlv3htqd4g023' offline drop;
alter database datafile '/dev/rlv3htqd4g024' offline drop;
alter database datafile '/dev/rlv3htqd4g025' offline drop;
alter database datafile '/dev/rlv3htqd8g001' offline drop;
alter database datafile '/dev/rlv3htqd4g020' offline drop;
alter database datafile '/dev/rlv3htqd4g021' offline drop;
alter database datafile '/dev/rlv3htqd4g022' offline drop;
alter database datafile '/dev/rlv3htqd8g004' offline drop;
alter database datafile '/dev/rlv3htqd4g045' offline drop;
alter database datafile '/dev/rlv3htqd4g046' offline drop;
alter database datafile '/dev/rlv3htqd4g047' offline drop;
alter database datafile '/dev/rlv3htqd8g003' offline drop;
alter database datafile '/dev/rlv3htqd4g026' offline drop;
alter database datafile '/dev/rlv3htqd4g027' offline drop;
alter database datafile '/dev/rlv3htqd4g028' offline drop;
..................................
alter database datafile '/dev/rlv3htqd10g396' offline drop;
alter database datafile '/dev/rlv3htqd10g397' offline drop;
alter database datafile '/dev/rlv3htqd10g398' offline drop;
alter database datafile '/dev/rlv3htqd10g399' offline drop;


恢复数据文件

注意：其中使用了skip tablespace来忽略掉不需要恢复的表空间

NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS'; export NLS_DATE_FORMAT
ORACLE_SID=htqd1; export ORACLE_SID
rman target   nocatalog <<EOF
run {
SET UNTIL TIME '2014/06/30 14:00:00';
ALLOCATE CHANNEL ch00;
set newname for datafile 1	to '/htqdbak_huawei/htqdbak/rlv3system01';
set newname for datafile 2	to '/htqdbak_huawei/htqdbak/rlv3undotbs11';
set newname for datafile 3	to '/htqdbak_huawei/htqdbak/rlv3undotbs21';
set newname for datafile 72	to '/htqdbak_huawei/htqdbak/rlv3htqd10g005';
set newname for datafile 73	to '/htqdbak_huawei/htqdbak/rlv3htqd4g042';
set newname for datafile 74	to '/htqdbak_huawei/htqdbak/rlv3htqd4g043';
set newname for datafile 75	to '/htqdbak_huawei/htqdbak/rlv3htqd4g044';
set newname for datafile 89	to '/htqdbak_huawei/htqdbak/rlv3htqd4g080';
set newname for datafile 90	to '/htqdbak_huawei/htqdbak/rlv3htqd4g081';
set newname for datafile 91	to '/htqdbak_huawei/htqdbak/rlv3htqd4g082';
set newname for datafile 92	to '/htqdbak_huawei/htqdbak/rlv3htqd4g083';
set newname for datafile 93	to '/htqdbak_huawei/htqdbak/rlv3htqd4g075';
set newname for datafile 152	to '/htqdbak_huawei/htqdbak/rlv3htqd4g142';
set newname for datafile 153	to '/htqdbak_huawei/htqdbak/rlv3htqd4g143';
set newname for datafile 154	to '/htqdbak_huawei/htqdbak/rlv3htqd4g144';
set newname for datafile 155	to '/htqdbak_huawei/htqdbak/rlv3htqd4g145';
set newname for datafile 226	to '/htqdbak_huawei/htqdbak/rlv3htqd4g190';
set newname for datafile 262	to '/htqdbak_huawei/htqdbak/rlv3htqd10g062';
set newname for datafile 275	to '/htqdbak_huawei/htqdbak/rlv3htqd10g075';
set newname for datafile 356	to '/htqdbak_huawei/htqdbak/rlv3htqd10g161';
set newname for datafile 357	to '/htqdbak_huawei/htqdbak/rlv3htqd10g162';
set newname for datafile 358	to '/htqdbak_huawei/htqdbak/rlv3htqd10g163';
set newname for datafile 426	to '/htqdbak_huawei/htqdbak/rlv3htqd10g239';
set newname for datafile 454	to '/htqdbak_huawei/htqdbak/rlv3htqd10g265';
set newname for datafile 525	to '/htqdbak_huawei/htqdbak/rlv3htqd10g337';
restore database skip tablespace HTQD2_DAT,HTQD2_IDX,HTQD2_SB_DAT,HTQD2_SB_IDX,HTQD2_FP_DAT,HTQD2_CX_TMP,HTQD2_GLFW_DAT,HTQD2_FP_IDX,HTQD2_JK_IDX,HTQD2_GLFW_IDX,HTQD2_MAINT_DAT,SJCF_CS_DAT ;
switch datafile all;
recover database skip tablespace HTQD2_DAT,HTQD2_IDX,HTQD2_SB_DAT,HTQD2_SB_IDX,HTQD2_FP_DAT,HTQD2_CX_TMP,HTQD2_GLFW_DAT,HTQD2_FP_IDX,HTQD2_JK_IDX,HTQD2_GLFW_IDX,HTQD2_MAINT_DAT,SJCF_CS_DAT ;
release channel ch00;
}


重命名重做日志文件

alter database rename file '/dev/rlv3redo11a' to '/htqdbak_huawei/htqdbak/rlv3redo11a';
alter database rename file '/dev/rlv3redo11b' to '/htqdbak_huawei/htqdbak/rlv3redo11b';
alter database rename file '/dev/rlv3redo12a' to '/htqdbak_huawei/htqdbak/rlv3redo12a';
alter database rename file '/dev/rlv3redo12b' to '/htqdbak_huawei/htqdbak/rlv3redo12b';
alter database rename file '/dev/rlv3redo13a' to '/htqdbak_huawei/htqdbak/rlv3redo13a';
alter database rename file '/dev/rlv3redo13b' to '/htqdbak_huawei/htqdbak/rlv3redo13b';
alter database rename file '/dev/rlv3redo14a' to '/htqdbak_huawei/htqdbak/rlv3redo14a';
alter database rename file '/dev/rlv3redo14b' to '/htqdbak_huawei/htqdbak/rlv3redo14b';
alter database rename file '/dev/rlv3redo21a' to '/htqdbak_huawei/htqdbak/rlv3redo21a';
alter database rename file '/dev/rlv3redo21b' to '/htqdbak_huawei/htqdbak/rlv3redo21b';
alter database rename file '/dev/rlv3redo22a' to '/htqdbak_huawei/htqdbak/rlv3redo22a';
alter database rename file '/dev/rlv3redo22b' to '/htqdbak_huawei/htqdbak/rlv3redo22b';
alter database rename file '/dev/rlv3redo25a' to '/htqdbak_huawei/htqdbak/rlv3redo25a';
alter database rename file '/dev/rlv3redo25b' to '/htqdbak_huawei/htqdbak/rlv3redo25b';
alter database rename file '/dev/rlv3redo23a' to '/htqdbak_huawei/htqdbak/rlv3redo23a';
alter database rename file '/dev/rlv3redo23b' to '/htqdbak_huawei/htqdbak/rlv3redo23b';
alter database rename file '/dev/rlv3redo24a' to '/htqdbak_huawei/htqdbak/rlv3redo24a';
alter database rename file '/dev/rlv3redo24b' to '/htqdbak_huawei/htqdbak/rlv3redo24b';
alter database rename file '/dev/rlv3redo15a' to '/htqdbak_huawei/htqdbak/rlv3redo15a';
alter database rename file '/dev/rlv3redo15b' to '/htqdbak_huawei/htqdbak/rlv3redo15b';


打开数据库

SQL> alter database open resetlogs;
 
Database altered.


打开数据库之前，建议检查前面RMAN恢复日志，是否有ORA-01547、ORA-01552、ORA-01110等告警，如果有这些告警，需要进行其它分析。



原创文章，版权归本文作者所有，如需转载请注明出处


喜欢本文请长按下方的二维码订阅Oracle一体机用户组

最后修改时间：2019-12-13 09:28:57
文章转载自Oracle一体机用户组，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论