使用Rman进行不完全恢复
2024-07-21 02:34:12
供稿：网友
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 >
