rman使用文档（备份脚本和恢复）

rman备份恢复模拟
全备脚本如下：
#!/bin/bash
export NLS_LANG='AMERICAN_AMERICA.AL32UTF8'
begin=`date +'%Y-%m-%d %H:%M:%S'`
echo '------backup begin------ ' $begin
export ORACLE_BASE=/u01/oracle
export ORACLE_HOME=/u01/11g
export ORACLE_SID=pu


/u01/11g/bin/rman target / <<EOF
run {
configure retention policy to recovery window of 8 days;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u01/rmanbackup/controlfile_%F';
allocate channel c1 type disk;
allocate channel c2 type disk;
backup   format '/u01/rmanbackup/all_db_%d_%T_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup archivelog all delete input format '/u01/rmanbackup/all_log_%d_%T_t%t_s%s_p%p';
release channel c1;
release channel c2;
crosscheck backup;
delete noprompt obsolete;
}
exit;
EOF


end=`date +'%Y-%m-%d %H:%M:%S'`
echo '-----backup over-----'  $end



运行脚本生成以下文件


[oracle@oracle ~]$ ls /u01/rmanbackup/
all_db_PU_20160505_t911035475_s211_p1   all_log_PU_20160505_t911035552_s215_p1
all_db_PU_20160505_t911035475_s212_p1   controlfile_c-2741963487-20160505-00
all_log_PU_20160505_t911035552_s214_p1  controlfile_c-2741963487-20160505-01


第一步：模拟控制文件丢失
找出控制文件并删除select name from v$controlfile;


SQL> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oracle/oradata/pu/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oracle/oradata/pu/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
上面是我在开启的时候删除了控制文件，这个是很危险的，立即拷贝复原
SQL> startup
ORACLE instance started.


Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             348130648 bytes
Database Buffers           54525952 bytes
Redo Buffers                8486912 bytes
ORA-00205: error in identifying control file, check alert log for more info
这个时候可以看到数据库无法打开，提示检查控制文件和告警日志，日常控制文件至少有两个以上，这里我们全部删除掉
开始恢复


RMAN> restore controlfile from '/u01/rmanbackup/controlfile_c-2741963487-20160505-01';   指定任何一个备份的控制文件都可以


Starting restore at 05-MAY-16
using channel ORA_DISK_1


channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/oradata/pu/control01.ctl
output file name=/u01/oracle/fast_recovery_area/pu/control02.ctl
Finished restore at 05-MAY-16
查看控制文件：


[oracle@oracle ~]$ ls /u01/oracle/oradata/pu/
control01.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
redo01.log     redo03.log  system01.dbf  undotbs01.dbf
[oracle@oracle ~]$ ls /u01/oracle/fast_recovery_area/
pu  PU
[oracle@oracle ~]$ ls /u01/oracle/fast_recovery_area/pu/
control02.ctl
已经全部恢复


SQL> alter database mount;


Database altered.


RMAN> recover database;


Starting recover at 05-MAY-16
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 05-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 05-MAY-16


Starting implicit crosscheck copy at 05-MAY-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-MAY-16


searching for all files in the recovery area
cataloging files...
no files cataloged


using channel ORA_DISK_1
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 1




starting media recovery


archived log for thread 1 with sequence 42 is already on disk as file /u01/oracle/oradata/pu/redo03.log
archived log file name=/u01/oracle/oradata/pu/redo03.log thread=1 sequence=42
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-MAY-16


RMAN> alter database open;


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/05/2016 10:29:44
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open




RMAN> alter database open resetlogs;


database opened


到这里，数据库已经开启
脚本：
run{
startup nomount;
restore controlfile from '/u01/rmanbackup/controlfile_c-2741963487-20160505-01';
alter database mount;
recover database;
alter database open resetlogs;
}
注意：在resetlogs后记得重新backup一次


[oracle@oracle dbs]$ ls /u01/rmanbackup/
all_db_PU_20160505_t911035475_s211_p1   all_log_PU_20160505_t911040830_s223_p1
all_db_PU_20160505_t911035475_s212_p1   all_log_PU_20160505_t911040831_s224_p1
all_db_PU_20160505_t911040753_s219_p1   controlfile_c-2741963487-20160505-00
all_db_PU_20160505_t911040753_s220_p1   controlfile_c-2741963487-20160505-01
all_log_PU_20160505_t911035552_s214_p1  controlfile_c-2741963487-20160505-02
all_log_PU_20160505_t911035552_s215_p1  controlfile_c-2741963487-20160505-03
all_log_PU_20160505_t911040830_s222_p1  controlfile_c-2741963487-20160505-04


第二步：模拟参数文件丢失
删除spfile文件和pfile文件
手动写init.ora文件
只需写一行：
db_name='PU'
startup nomount  pifile='/u01/11g/dbs/init.ora'   ---可以不加nomount选项，反正也只能启动到nomount
RMAN> restore spfile from '/u01/rmanbackup/controlfile_c-2741963487-20160505-04';      --指定控制文件备份集


Starting restore at 05-MAY-16
using channel ORA_DISK_1


channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/rmanbackup/controlfile_c-2741963487-20160505-04
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 05-MAY-16


[oracle@oracle dbs]$ ls
hc_pu.dat  init.ora.3132016172945  lkPU     snapcf_pu.f
init.ora   init.ora.bak            orapwpu  spfilepu.ora


关闭数据库，用spfile打开
RMAN> shutdown immediate;


Oracle instance shut down


RMAN> startup


connected to target database (not started)
Oracle instance started
database mounted
database opened


Total System Global Area     413372416 bytes


Fixed Size                     2228904 bytes
Variable Size                348130648 bytes
Database Buffers              54525952 bytes
Redo Buffers                   8486912 bytes


脚本：
cat init.ora<<EOF
db_name='PU'
EOF
run{
startup nomount pfile='/u01/11g/dbs/init.ora';
restore spfile from '/u01/rmanbackup/controlfile_c-2741963487-20160505-04';
shutdown immediate;
startup;}


第三步：归档日志恢复
如何查看归档日志：
RMAN> list backup of archivelog all;
列出所有archive log 备份
RMAN> list backup of archivelog from logseq 100 until logseq 120;
列出archive log 从100到120
RMAN> list backup of archivelog sequence between 100 and 110;
列出archive log 从100到120
–说明:between……and只能使用sequence，而不能使用logseq
RMAN> list backup of archivelog from logseq 100;
列出seq大于等于100的archive log
RMAN> list backup of archivelog low logseq 120;
列出seq大于等于120的archive log
RMAN> list backup of archivelog sequence 100;
列出seq为100的archive log
–说明:在对于rman中关于archivelog的操作中logseq与sequence作用相同，但是建议尽量使用sequence
RMAN> list backup of archivelog logseq 85;
列出seq为85的archive log
RMAN> list backup of archivelog until logseq 85;
列出seq小于等于85的archive log
RMAN> list backup of archivelog high logseq 40;
列出seq小于等于40的archive log
RMAN> list backup of archivelog from time ‘sysdate-7′;
列出7天以前的archive log
RMAN> run {
2> set archivelog destination to ‘/opt/oracle/oradata/test/newlog’;
3> restore archivelog low logseq 40;
4> }
从seq为40开始，恢复到/opt/oracle/oradata/test/newlog中
–说明:list backup of archivelog中限定日志的位置也适合restore archivelog
RMAN> backup archivelog sequence between 100 and 110 format ‘/tmp/text_test.rman’ delete input;
备份seq为100至110的archive log
–说明:list backup of archivelog中限定日志的位置也适合backup archivelog
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7′;
删除7天前archive log
RMAN>DELETE ARCHIVELOG low logseq 40;
删除seq大于等于40的archive log
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
删除无效archive log
–说明:DELETE ARCHIVELOG中限定日志的位置也适合restore archivelog
如何恢复归档日志
一.备份所有归档日志文件
   RMAN> backup archivelog all delete input;
二: restore archivelog 的各种选项
 1.恢复全部归档日志文件
RMAN> restore archivelog all;
2.只恢复5到8这四个归档日志文件
RMAN> restore archivelog from logseq 5 until logseq 8;
3.恢复从第5个归档日志起
RMAN> restore archivelog from logseq 5;
4.恢复7天内的归档日志
RMAN> restore archivelog from time 'sysdate-7';
5. sequence between 写法
RMAN> restore archivelog sequence between 1 and 3;
6.恢复到哪个日志文件为止
RMAN> restore archivelog until logseq 3;
7.从第五个日志开始恢复
RMAN> restore archivelog low logseq 5;
8.到第5个日志为止
RMAN> restore archivelog high logseq 5;
三:如果想改变恢复到另外路径下 则可用下面语句
   set archivelog destination to 'd:\backup';
   RMAN> run
   2> {allocate channel ci type disk;
   3> set archivelog destination to 'd:\backup';
   4> restore archivelog all;
   5> release channel ci;
   6> }
   
实际上recover的时候就是在恢复归档日志。


第4步恢复数据文件
除了system的数据文件只能在mount模式下恢复，其他的都可以在open状态，这样可以减少停机的时间。


例1：数据库关闭状态下，删除 6号文件ggs.dbf
select file#,name from v$datafile
1 1 /u01/oracle/oradata/pu/system01.dbf
2 2 /u01/oracle/oradata/pu/sysaux01.dbf
3 3 /u01/oracle/oradata/pu/undotbs01.dbf
4 4 /u01/oracle/oradata/pu/users01.dbf
5 5 /u01/salary.dbf
6 6 /u01/ggs.dbf
7 7 /u01/ogg01.dbf


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ho rm -rf /u01/ggs.dbf


SQL> ho ls /u01
11g                 control02.ctl  grid        oracle        salary.dbf
clspuser_$date.dmp  dblog          lost+found  oraInventory  spfilepu.ora
control01.ctl       gg             ogg01.dbf   rmanbackup    sqluldr


SQL> startup
ORACLE instance started.


Total System Global Area  413372416 bytes
Fixed Size                  2228904 bytes
Variable Size             348130648 bytes
Database Buffers           54525952 bytes
Redo Buffers                8486912 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/ggs.dbf'
开启数据，提示6号文件丢失，只能startup到mount
SQL>  select file#,error from v$recover_file;


     FILE# ERROR
---------- -----------------------------------------------------------------
         6 FILE NOT FOUND




登录rman恢复
脚本run {  
startup force mount;  
sql 'alter database datafile 6 offline';  
sql 'alter database open';  
restore datafile 6;  
recover datafile 6;  
sql 'alter database datafile 6 online';  
}  
RMAN> run {
startup force mount;
sql 'alter database datafile 6 offline';
sql 'alter database open';
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
}  2> 3> 4> 5> 6> 7> 8>


Oracle instance started
database mounted


Total System Global Area     413372416 bytes


Fixed Size                     2228904 bytes
Variable Size                348130648 bytes
Database Buffers              54525952 bytes
Redo Buffers                   8486912 bytes


using target database control file instead of recovery catalog
sql statement: alter database datafile 6 offline


sql statement: alter database open


Starting restore at 05-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/ggs.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbackup/all_db_PU_20160505_t911052606_s227_p1
channel ORA_DISK_1: piece handle=/u01/rmanbackup/all_db_PU_20160505_t911052606_s227_p1 tag=TAG20160505T141006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 05-MAY-16


Starting recover at 05-MAY-16
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01


Finished recover at 05-MAY-16


sql statement: alter database datafile 6 online


登录数据库查看


SQL> select file#,error from v$recover_file;


no rows selected
6号数据文件已经恢复回来


SQL> ho ls /u01/ggs.dbf
/u01/ggs.dbf
将数据文件恢复到其他磁盘的位置
脚本run {  
startup force mount;  
sql 'alter database datafile 6 offline';  
sql 'alter database open';  
set newname for datafile  '/u01/ggs.dbf' to '$ORACLE_HOME/ggs.dbf';      
restore datafile 6;  
switch datafile 6;  
recover datafile 6;  
sql 'alter database datafile 6 online';  
}  
RMAN> run {
startup force mount;
2> 3> sql 'alter database datafile 6 offline';
4> sql 'alter database open';
5> set newname for datafile  '/u01/ggs.dbf' to '$ORACLE_HOME/ggs.dbf';       ---这个地方我写的文件号6，但是失败了，后来改成绝对路径成功
6> restore datafile 6;
7> switch datafile 6;                ---写入到控制文件中，因为rman修改了数据文件的路径但是控制文件没有记录
8> recover datafile 6;
9> sql 'alter database datafile 6 online';
10> }


Oracle instance started
database mounted


Total System Global Area     413372416 bytes


Fixed Size                     2228904 bytes
Variable Size                348130648 bytes
Database Buffers              54525952 bytes
Redo Buffers                   8486912 bytes


sql statement: alter database datafile 6 offline


sql statement: alter database open


executing command: SET NEWNAME


Starting restore at 05-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to $ORACLE_HOME/ggs.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbackup/all_db_PU_20160505_t911052606_s227_p1
channel ORA_DISK_1: piece handle=/u01/rmanbackup/all_db_PU_20160505_t911052606_s227_p1 tag=TAG20160505T141006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 05-MAY-16


datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=911057975 file name=/u01/11g/ggs.dbf


Starting recover at 05-MAY-16
using channel ORA_DISK_1


starting media recovery


archived log for thread 1 with sequence 13 is already on disk as file /u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_13_cloxjydd_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_14_cloxlgg1_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_15_cloy24kg_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_16_cloy5p3q_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u01/rmanbackup/all_log_PU_20160505_t911052684_s231_p1
channel ORA_DISK_1: piece handle=/u01/rmanbackup/all_log_PU_20160505_t911052684_s231_p1 tag=TAG20160505T141123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_11_cloy5r71_.arc thread=1 sequence=11
channel default: deleting archived log(s)
archived log file name=/u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_11_cloy5r71_.arc RECID=481 STAMP=911057976
archived log file name=/u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_12_cloy5r79_.arc thread=1 sequence=12
channel default: deleting archived log(s)
archived log file name=/u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_12_cloy5r79_.arc RECID=482 STAMP=911057976
archived log file name=/u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_13_cloxjydd_.arc thread=1 sequence=13
archived log file name=/u01/oracle/fast_recovery_area/PU/archivelog/2016_05_05/o1_mf_1_14_cloxlgg1_.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-MAY-16


sql statement: alter database datafile 6 online




例2：数据库开启的状态下，数据文件丢失
同上面的例子相同，只需要把数据文件offline，然后进行rman恢复，系统system的数据文件只能在mount状态下恢复


第5步：表空间恢复
system表空间同样不能在open状态下恢复


例一：open状态下表空间文件丢失
在open状态下删除ggs表空间下的ggs.dbf文件
查看表空间信息
Select file_name,file_id,tablespace_name,status,online_status FROM DBA_DATA_FILES;
1 /u01/oracle/oradata/pu/users01.dbf4 ggs AVAILABLE ONLINE
2 /u01/oracle/oradata/pu/undotbs01.dbf3UNDOTBS1 AVAILABLEONLINE
3 /u01/oracle/oradata/pu/sysaux01.dbf2 SYSAUXAVAILABLE ONLINE
4 /u01/oracle/oradata/pu/system01.dbf1 SYSTEMAVAILABLE SYSTEM
5 /u01/salary.dbf5 SALARYAVAILABLE ONLINE
6 /u01/11g/ggs.dbf6 GGS AVAILABLE ONLINE
7 /u01/ogg01.dbf7 OGG AVAILABLE ONLINE


rm -rf /u01/11g/ggs.dbf


SQL> select file#,error from v$recover_file;


no rows selected


SQL> ho ls /u01/11g/ggs.dbf
ls: cannot access /u01/11g/ggs.dbf: No such file or directory


恢复脚本
run {  
sql 'alter tablespace ggs offline for recover';  
restore tablespace ggs;  
recover tablespace ggs;  
sql 'alter tablespace ggs online';  
} 




RMAN> run {
sql 'alter tablespace ggs offline for recover';
restore tablespace ggs;
recover tablespace ggs;
sql 'alter tablespace ggs online';
} 2> 3> 4> 5> 6>


using target database control file instead of recovery catalog
sql statement: alter tablespace ggs offline for recover


Starting restore at 05-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9 device type=DISK


channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=17 STAMP=911057975 file name=/u01/ggs.dbf
destination for restore of datafile 00006: /u01/11g/ggs.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/u01/11g/ggs.dbf RECID=0 STAMP=0
Finished restore at 05-MAY-16


Starting recover at 05-MAY-16
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:00


Finished recover at 05-MAY-16


sql statement: alter tablespace ggs online


查看：
SQL> select file#,error from v$recover_file;


no rows selected


SQL> ho ls /u01/11g/ggs.dbf
/u01/11g/ggs.dbf


将表空间的文件恢复到其他位置
run {  
 sql 'alter tablespace ggs offline for recover';  
 set newname for datafile 6 to '/u01/ggs.dbf';  
 restore tablespace ggs;  
 switch datafile all;  
 recover tablespace ggs;  
 sql 'alter tablespace ggs online';  
 } 
 
 第6步：数据块恢复
 查看数据文件数据块的位置，例如TEST1表
 select a.file_id,a.block_id,a.blocks,b.name from dba_extents a,v$datafile b  where a.file_id=b.file# and a.owner='CLSPUSER' and a.segment_name='TEST1';
 1 4 1277368 /u01/oracle/oradata/pu/users01.dbf
开始于127736数据块，共占8个块，来源于4号文件
验证4号数据文件
rman>backup validate datafile 4;
或者直接查询
select * from v$database_block_corruption;
如果有坏块
执行
RMAN> blockrecover datafile 4 block 13330,13331;  恢复4号文件的13330,13331数据块
blockrecover corruption list恢复全部的数据块
需要注意的是数据文件的1号数据块是不能修复，如果要修复只能修复整个数据文件