eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 进京两周年记-Eygle在北京的生活之五 | Blog首页 | 如何把数据导入不同的表空间? »

使用Rman进行不完全恢复
modb.pro

作者：eygle | 【转载请注】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：
1.模拟数据损失
$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 09:26:04 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SYS AS SYSDBA on 05-APR-05 >create table t as select * from dba_objects;

Table created.

SYS AS SYSDBA on 05-APR-05 >alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

记录一下时间(具体的生产环境，可能需要你估计一个恢复时间或者通过Logminer来分析日志得到故障时间)
SYS AS SYSDBA on 2005-04-05 09:26:48 >select sysdate from dual;

SYSDATE
-------------------
2005-04-05 09:26:55

SYS AS SYSDBA on 2005-04-05 09:26:55 >select count(*) from t;

  COUNT(*)
----------
      9478

drop table模拟一次事故(当然也可以选择闪回这张表，本例为了说明不完全恢复)
SYS AS SYSDBA on 2005-04-05 09:27:05 >drop table t;

Table dropped.


SYS AS SYSDBA on 2005-04-05 09:27:19 >exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

2.执行恢复
设置环境变量时间，执行until time的不完全恢复。
$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
$ rman target /

Recovery Manager: Release 10.1.0.2.0 - 64bit Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: EYGLE (DBID=1337390772)

RMAN> run {
2> set until time '2005-04-05 09:26:55';
3> restore database;
4> recover database;
5> }

executing command: SET until clause
using target database controlfile instead of recovery catalog

Starting restore at 2005-04-05 09:36:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

datafile 8 not processed because file is read-only
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/eygle/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/eygle/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/eygle/sysaux01.dbf
restoring datafile 00006 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_test_03xv34ny_.dbf
restoring datafile 00009 to /data1/oradata/systemfile/bigtbs.dbf
restoring datafile 00010 to /opt/oracle/oradata/eygle/dfmbrc.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_31/o1_mf_nnndf_TAG20050331T104240_14prx29n_.bkp tag=TAG20050331T104240
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /opt/oracle/oradata/eygle/users01.dbf
restoring datafile 00005 to /data1/oradata/systemfile/eygle01.dbf
restoring datafile 00007 to /opt/oracle/oradata/eygle/EYGLE/datafile/o1_mf_itpub_03xv5g66_.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_31/o1_mf_nnndf_TAG20050331T104240_14ps0d47_.bkp tag=TAG20050331T104240
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/eygle/t2k01.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/data5/flash_recovery_area/EYGLE/backupset/2005_03_31/o1_mf_nnndf_TAG20050331T104240_14ps15qb_.bkp tag=TAG20050331T104240
channel ORA_DISK_1: restore complete
Finished restore at 2005-04-05 09:39:17

Starting recover at 2005-04-05 09:39:18
using channel ORA_DISK_1
datafile 8 not processed because file is offline

starting media recovery

archive log thread 1 sequence 13 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_13_14psyb93_.arc
archive log thread 1 sequence 14 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_14_14q810df_.arc
archive log thread 1 sequence 15 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_15_14qp1w1q_.arc
archive log thread 1 sequence 16 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_16_14r5606b_.arc
archive log thread 1 sequence 17 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_17_14r7nf55_.arc
archive log thread 1 sequence 18 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_18_14rprqyz_.arc
archive log thread 1 sequence 19 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_19_14s79l9f_.arc
archive log thread 1 sequence 20 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_20_14sodfhx_.arc
archive log thread 1 sequence 21 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_21_14t6ysjt_.arc
archive log thread 1 sequence 22 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_22_14tnzplf_.arc
archive log thread 1 sequence 23 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_23_14tsobsv_.arc
archive log thread 1 sequence 24 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_24_14v6l6om_.arc
archive log thread 1 sequence 25 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_25_14vno1gg_.arc
archive log thread 1 sequence 26 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_26_14w67fn9_.arc
archive log thread 1 sequence 27 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_27_14wn8bj3_.arc
archive log thread 1 sequence 28 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_28_14x2cd0g_.arc
archive log thread 1 sequence 29 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_29_14xg2xts_.arc
archive log thread 1 sequence 30 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_30_14xmxog2_.arc
archive log thread 1 sequence 31 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_31_14y5j2n8_.arc
archive log thread 1 sequence 32 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_32_14yq2mps_.arc
archive log thread 1 sequence 33 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_33_14z8o5h9_.arc
archive log thread 1 sequence 34 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_34_14zxqc55_.arc
archive log thread 1 sequence 35 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_35_1502kl8x_.arc
archive log thread 1 sequence 36 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_36_150lvjz1_.arc
archive log thread 1 sequence 37 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_37_1514g562_.arc
archive log thread 1 sequence 38 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_38_151sjdq0_.arc
archive log thread 1 sequence 39 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_39_152gnlv0_.arc
archive log thread 1 sequence 40 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_40_152pytqn_.arc
archive log thread 1 sequence 41 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_41_1533pxgd_.arc
archive log thread 1 sequence 42 is already on disk as file /data5/flash_recovery_area/EYGLE/archivelog/2005_04_05/o1_mf_1_42_153rs55f_.arc
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_13_14psyb93_.arc thread=1 sequence=13
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_14_14q810df_.arc thread=1 sequence=14
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_15_14qp1w1q_.arc thread=1 sequence=15
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_03_31/o1_mf_1_16_14r5606b_.arc thread=1 sequence=16
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_17_14r7nf55_.arc thread=1 sequence=17
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_18_14rprqyz_.arc thread=1 sequence=18
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_19_14s79l9f_.arc thread=1 sequence=19
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_20_14sodfhx_.arc thread=1 sequence=20
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_21_14t6ysjt_.arc thread=1 sequence=21
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_22_14tnzplf_.arc thread=1 sequence=22
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_01/o1_mf_1_23_14tsobsv_.arc thread=1 sequence=23
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_24_14v6l6om_.arc thread=1 sequence=24
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_25_14vno1gg_.arc thread=1 sequence=25
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_26_14w67fn9_.arc thread=1 sequence=26
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_27_14wn8bj3_.arc thread=1 sequence=27
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_28_14x2cd0g_.arc thread=1 sequence=28
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_02/o1_mf_1_29_14xg2xts_.arc thread=1 sequence=29
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_30_14xmxog2_.arc thread=1 sequence=30
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_31_14y5j2n8_.arc thread=1 sequence=31
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_32_14yq2mps_.arc thread=1 sequence=32
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_33_14z8o5h9_.arc thread=1 sequence=33
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_34_14zxqc55_.arc thread=1 sequence=34
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_03/o1_mf_1_35_1502kl8x_.arc thread=1 sequence=35
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_36_150lvjz1_.arc thread=1 sequence=36
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_37_1514g562_.arc thread=1 sequence=37
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_38_151sjdq0_.arc thread=1 sequence=38
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_39_152gnlv0_.arc thread=1 sequence=39
archive log filename=/data5/flash_recovery_area/EYGLE/archivelog/2005_04_04/o1_mf_1_40_152pytqn_.arc thread=1 sequence=40
media recovery complete
Finished recover at 2005-04-05 09:50:42

恢复完成，使用RESETLOGS选项打开数据库。
RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.

3.检查数据恢复情况
$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Apr 5 09:58:00 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SYS AS SYSDBA on 2005-04-05 09:58:00 >archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SYS AS SYSDBA on 2005-04-05 09:58:04 >select count(*) from t;

  COUNT(*)
----------
      9478

SYS AS SYSDBA on 2005-04-05 09:58:09 >


历史上的今天...
    >> 2009-04-05文章:
           关于Oracle的Dual表
    >> 2006-04-05文章:
    >> 2004-04-05文章:

By eygle on 2005-04-05 10:21 | Comments (3) | Backup&Recovery | 241 |

3 Comments

bobmwang | August 16, 2006 4:49 PM

hello eygle,
我在Redhat上没有作成功,能帮忙看一看吗?
[oracle@suzapp1 admin]$ echo $NLS_DATE_FORMAT
yyyy-mm-dd hh24:mi:ss
[oracle@suzapp1 admin]$ date
Wed Aug 16 16:22:48 CST 2006


RMAN> run
2> {
3> set until time '08/16/2006 15:41:32';
4> restore database;
5> recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 08/16/2006 16:20:58
ORA-01843: not a valid month

Hopewell_Go | September 27, 2007 3:22 PM

我做过测试，但是报错。。报rman-03002,ora-01864错误。。不知咋回事。。

Hopewell_Go | September 27, 2007 3:36 PM

我的是在window操作系统上没有成功

CopyRight © 2004~2020 云和恩墨，成就未来！, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话：400-600-8755 业务咨询：010-59007017-7040 or 7037 业务合作: marketing@enmotech.com