RMAN演练数据文件和控制文件丢失后的恢复

今天我实验了一下用RMAN来对数据库进行备份以及恢复。
详细过程如下： 
环境：database
SQL> select * from v$version;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


OS：
Redhat Enterprise Linux 6.3 x86_64


首先启动到RMAN命令模式
[oracle@ww ~]$ rman target /


Recovery Manager: Release 10.2.0.4.0 - Production on Mon Jan 6 11:14:31 2014


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


connected to target database: ORCL (DBID=1360271917)


RMAN> 


进入RMAN后我们可以查看RMAN的配置属性 


RMAN> show all;


using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default


输入show all就能查看，以下属性设置的功能及如何应用下次再来研究。 


今天我们先来做一下RMAN的备份与恢复。
首先对我们当前的数据库做一个全备(包括数据文件、控制文件、初始参数文件、归档日志文件)，命令很简单：




RMAN> backup database plus archivelog;




Starting backup at 2014-01-06 11:22:53
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=21 stamp=836133773
channel ORA_DISK_1: starting piece 1 at 2014-01-06 11:22:55
channel ORA_DISK_1: finished piece 1 at 2014-01-06 11:22:56
piece handle=/oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112254_9dn8dh51_.bkp tag=TAG20140106T112254 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2014-01-06 11:22:56
..............
Starting Control File and SPFILE Autobackup at 2014-01-06 11:23:53
piece handle=/oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836133833_9dn8g9d4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2014-01-06 11:23:55
（中间输出命令太多 这里省略部分）


备份成功~！上面在我执行了备份命令后，输出结果可以发现，备份文件全部都装入了快速恢复区。由于我在备份的时候没有指定备份文件存放位置，RMAN默认是存入快速恢复区，如果你没有开快速恢复区，那备份文件就自动存放在ORACLE_HOME/dbs目录下。


在RMAN下用以下命令可以查看当前所有的备份信息


RMAN> list backup;




List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
21      266.50K    DISK        00:00:01     2014-01-06 11:22:55
        BP Key: 21   Status: AVAILABLE  Compressed: NO  Tag: TAG20140106T112254
        Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112254_9dn8dh51_.bkp


  List of Archived Logs in backup set 21
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    1       1469692    2014-01-06 11:01:35 1470560    2014-01-06 11:22:53


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
22      Full    829.66M    DISK        00:00:49     2014-01-06 11:23:45
        BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20140106T112256
        Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp
  List of Datafiles in backup set 22
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1470565    2014-01-06 11:22:56 /oracle/app/oradata/orcl/system01.dbf
  2       Full 1470565    2014-01-06 11:22:56 /oracle/app/oradata/orcl/undotbs01.dbf
  3       Full 1470565    2014-01-06 11:22:56 /oracle/app/oradata/orcl/sysaux01.dbf
  4       Full 1470565    2014-01-06 11:22:56 /oracle/app/oradata/orcl/users01.dbf
  5       Full 1470565    2014-01-06 11:22:56 /oracle/app/oradata/orcl/whj.dbf


BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
23      2.00K      DISK        00:00:01     2014-01-06 11:23:52
        BP Key: 23   Status: AVAILABLE  Compressed: NO  Tag: TAG20140106T112351
        Piece Name: /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_annnn_TAG20140106T112351_9dn8g89s_.bkp


  List of Archived Logs in backup set 23
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    2       1470560    2014-01-06 11:22:53 1470586    2014-01-06 11:23:51


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
24      Full    6.80M      DISK        00:00:00     2014-01-06 11:23:53
        BP Key: 24   Status: AVAILABLE  Compressed: NO  Tag: TAG20140106T112353
        Piece Name: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836133833_9dn8g9d4_.bkp
  Control File Included: Ckp SCN: 1470592      Ckp time: 2014-01-06 11:23:53
  SPFILE Included: Modification time: 2014-01-06 10:41:33


现在备份成功后我们来模拟数据文件丢失后怎么恢复


删除users01.dbf数据文件
[oracle@ww orcl]$ rm users01.dbf 
[oracle@ww orcl]$ ll
total 1668300
-rw-r----- 1 oracle dba   7061504 Jan  6 11:38 control01.ctl
-rw-r----- 1 oracle dba  52429312 Jan  6 11:22 redo01.log
-rw-r----- 1 oracle dba  52429312 Jan  6 11:23 redo02.log
-rw-r----- 1 oracle dba  52429312 Jan  6 11:36 redo03.log
-rw-r----- 1 oracle dba 283123712 Jan  6 11:23 sysaux01.dbf
-rw-r----- 1 oracle dba 524296192 Jan  6 11:23 system01.dbf
-rw-r----- 1 oracle dba  31465472 Dec  7 10:47 temp01.dbf
-rw-r----- 1 oracle dba 686825472 Jan  6 11:23 undotbs01.dbf
-rw-r----- 1 oracle dba  33562624 Jan  6 11:23 whj.dbf


删除后操作users表空间下的表就会报错
SQL> insert into HR values(3,'aa');
insert into HR values(3,'aa')
            *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oracle/app/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


users01.dbf数据文件已经不存在  


恢复：
将users01数据文件处于离线状态
SQL> alter database datafile 4 offline;


Database altered.
在RMAN命令行下输入以下恢复命令




RMAN> restore datafile 4;


Starting restore at 2014-01-06 12:15:14
using channel ORA_DISK_1


channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/flash_recovery_area/ORCL/backupset/2014_01_06/o1_mf_nnndf_TAG20140106T112256_9dn8dj7d_.bkp tag=TAG20140106T112256
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2014-01-06 12:15:16


恢复成功~！现在看下/oracle/app/oradata/orcl目录下有没有users.dbf文件
[oracle@ww orcl]$ ll
total 1673428
-rw-r----- 1 oracle dba   7061504 Jan  6 12:15 control01.ctl
-rw-r----- 1 oracle dba  52429312 Jan  6 11:43 redo01.log
-rw-r----- 1 oracle dba  52429312 Jan  6 12:14 redo02.log
-rw-r----- 1 oracle dba  52429312 Jan  6 11:43 redo03.log
-rw-r----- 1 oracle dba 283123712 Jan  6 12:10 sysaux01.dbf
-rw-r----- 1 oracle dba 524296192 Jan  6 12:01 system01.dbf
-rw-r----- 1 oracle dba  31465472 Dec  7 10:47 temp01.dbf
-rw-r----- 1 oracle dba 686825472 Jan  6 12:10 undotbs01.dbf
-rw-r----- 1 oracle dba   5251072 Jan  6 12:15 users01.dbf
-rw-r----- 1 oracle dba  33562624 Jan  6 11:49 whj.dbf
[oracle@ww orcl]$ pwd
/oracle/app/oradata/orcl


数据文件恢复出来了，最后recover一下将他的检查点与其他数据文件同步就可以了


RMAN> recover datafile 4;


Starting recover at 2014-01-06 12:17:47
using channel ORA_DISK_1


starting media recovery


archive log thread 1 sequence 2 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_2_9dn8g780_.arc
archive log thread 1 sequence 3 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_3_9dn9lmxy_.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_4_9dn9ls3q_.arc
archive log filename=/oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_2_9dn8g780_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:03
Finished recover at 2014-01-06 12:17:50


将users.dbf数据文件启动到在线状态


SQL> alter database datafile 4 online;


Database altered.  


现在执行一下刚才的insert命令就可以成功了
SQL> insert into HR values(3,'aa');


1 row created.
SQL> select * from HR;


        ID NAME
---------- --------------------
         1 whj
         3 aa
         2 qq




 接下来模拟控制文件全部丢失


[oracle@ww orcl]$ rm control01.ctl 
[oracle@ww ~]$ rm control02.ctl 
[oracle@ww ORCL]$ rm control03.ctl 
我删除了所有的控制文件 


SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/app/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
现在数据库都不能正常关闭，报错说找不到控制文件


SQL> shutdown abort
ORACLE instance shut down.
只能强行关闭




RMAN> startup nomount;


connected to target database (not started)
Oracle instance started


Total System Global Area     557842432 bytes


Fixed Size                     2085456 bytes
Variable Size                155192752 bytes
Database Buffers             394264576 bytes
Redo Buffers                   6299648 bytes
在RMAN下启动数据库到nomount状态


RMAN> restore controlfile from autobackup;


Starting restore at 2014-01-06 13:31:59
using channel ORA_DISK_1


recovery area destination: /oracle/app/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836137127_9dnco756_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/app/oradata/orcl/control01.ctl
output filename=/home/oracle/control02.ctl
output filename=/oracle/app/flash_recovery_area/ORCL/control03.ctl
Finished restore at 2014-01-06 13:32:04
从自动 备份里恢复控制文件


然后启动到mount状态下recover数据库
RMAN> alter database mount;


database mounted
released channel: ORA_DISK_1


RMAN> recover database ;


Starting recover at 2014-01-06 13:34:32
Starting implicit crosscheck backup at 2014-01-06 13:34:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2014-01-06 13:34:32


Starting implicit crosscheck copy at 2014-01-06 13:34:32
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2014-01-06 13:34:32


searching for all files in the recovery area
cataloging files...
cataloging done


List of Cataloged Files
=======================
File Name: /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc
File Name: /oracle/app/flash_recovery_area/ORCL/autobackup/2014_01_06/o1_mf_s_836137127_9dnco756_.bkp


using channel ORA_DISK_1


starting media recovery


archive log thread 1 sequence 5 is already on disk as file /oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc
archive log thread 1 sequence 6 is already on disk as file /oracle/app/oradata/orcl/redo03.log
archive log filename=/oracle/app/flash_recovery_area/ORCL/archivelog/2014_01_06/o1_mf_1_5_9dngm4k3_.arc thread=1 sequence=5
archive log filename=/oracle/app/oradata/orcl/redo03.log thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014-01-06 13:34:33 
  
最后启动数据库到open，因为是不完全恢复需要resetlogs
RMAN> alter database open resetlogs;


database opened


好了  控制文件恢复成功，可以进行数据操作了
SQL> create table whj as select * from HR;


Table created.


SQL> select * from whj;


        ID NAME
---------- --------------------
         1 whj
         3 aa
         2 qq

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28834607/viewspace-1295254/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28834607/viewspace-1295254/