Rman备份及不完全恢复操作

最后更新时间：2018/12/18

启用归档

--检查是否为归档
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1423
Current log sequence           1425

No Archive Mode 表示非归档模式

开归档：
alter database archivelog;

关归档：
alter database noarchivelog;

★ 开启归档步骤
--关库
shutdown immediate;

startup mount;

--配置归档路径

alter system set log_archive_dest_1='location=/home/oracle/arch';

--开启归档
alter database archivelog;
-- 确认开启了归档

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/arch
Oldest online log sequence     1423
Next log sequence to archive   1425
Current log sequence           1425

Rman备份

前置条件

做全量备份时，rman也会生成控制文件快照，只是这个控制文件快照生于备份集产生，不包含最后一次的备份信息，因而不能用于最后一次备份集恢复，解决办法有两种：

1.  开启控制文件自动备份

RMAN> configure snapshot controlfile name to '/home/oracle/product/11gr2/db_1/dbs/snapcf_orcl.f';

RMAN> configure controlfile autobackup on;

2.  全量备份后再备份控制文件

一般建议在全量备份后，备份控制文件、SPFILE 到指定路径，以备需要时可以恢复。

Rman自动备份相关脚本

startrmans.sh 

#!/bin/bash

bak_dir=/home/oracle/rmans
# mkdir -p $bak_dir/{logs,data,init_files}
day=`date +%Y%m%d`

tm=`date "+%Y%m%d %H:%M:%S"`
echo "$tm begin backup."

echo "delete archive logs"
find /home/oracle/arch -name "*.dbf" -mtime +10 -exec rm -f {} ;

rman target / nocatalog <<EOF >$bak_dir/logs/fullbak`date +%Y%m%d`.log
run{
allocate channel c1 device type disk;

backup database
format "$bak_dir/data/fullback_%d_%T_%s";

backup current controlfile tag='bak_ctlfile' format="$bak_dir/init_files/ctl_file_%U_%T";
backup spfile tag='spfile' format='$bak_dir/init_files/ORCL_spfile_%U_%T';

release channel c1;

}

report obsolete;
delete noprompt obsolete; 

crosscheck backup;
delete noprompt expired backup;
list backup summary; 

exit;
EOF

tm=`date "+%Y%m%d %H:%M:%S"`
echo "$tm end backup."

Rman备份恢复验证

在数据库运行过程，将数据文件、控制文件、redo文件移到别的目录，强行关掉数据库，再开数据库验证失败。

[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 18 09:57:34 2018

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3273641984 bytes
Fixed Size                  2217792 bytes
Variable Size            2466252992 bytes
Database Buffers          788529152 bytes
Redo Buffers               16642048 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> select open_mode from v$database;
select open_mode from v$database
                       *
ERROR at line 1:
ORA-01507: database not mounted

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

恢复操作

[oracle@localhost orcl]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 18 10:15:25 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area    3273641984 bytes

Fixed Size                     2217792 bytes
Variable Size               2466252992 bytes
Database Buffers             788529152 bytes
Redo Buffers                  16642048 bytes

RMAN> restore controlfile from '/home/oracle/rmans/init_files/ctl_file_11tl2p0u_1_1_20181218';

Starting restore at 18-DEC-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 18-DEC-18

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 18-DEC-18
Starting implicit crosscheck backup at 18-DEC-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=958 device type=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 18-DEC-18

Starting implicit crosscheck copy at 18-DEC-18
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-DEC-18

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

using channel ORA_DISK_1

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 00001 to /home/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/oradata/orcl/users01.dbf

…………….省略部分……….

channel ORA_DISK_1: piece handle=/home/oracle/rmans/data/fullback_ORCL_20181218_31 tag=TAG20181218T093553
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 18-DEC-18

RMAN> recover database;

Starting recover at 18-DEC-18
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=1438
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2018 10:32:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1438 and starting SCN of 62636468

RMAN> alter database open resetlogs;

database opened

执行完不完全恢复完成。

周末测试后数据库恢复

先将数据库关闭，再直接用rman开启到 mount状态

RMAN> startup nomount;                       

Oracle instance started

Total System Global Area    3273641984 bytes

Fixed Size                     2217792 bytes
Variable Size               2466252992 bytes
Database Buffers             788529152 bytes
Redo Buffers                  16642048 bytes

RMAN> restore controlfile from '/home/oracle/rmans/init_files/ctl_file_11tl2p0u_1_1_20181218';

RMAN> alter database mount;

RMAN> list backup;   #查看最新备份数据文件SCN  62636468

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22      Full    3.28G      DISK        00:00:30     18-DEC-18     
         BP Key: 22   Status: AVAILABLE  Compressed: NO  Tag: TAG20181218T093553
         Piece Name: /home/oracle/rmans/data/fullback_ORCL_20181218_31
   List of Datafiles in backup set 22
   File LV Type Ckp SCN    Ckp Time  Name
   ---- -- ---- ---------- --------- ----
   1       Full 62636468   18-DEC-18 /home/oracle/oradata/orcl/system01.dbf
   2       Full 62636468   18-DEC-18 /home/oracle/oradata/orcl/sysaux01.dbf
   3       Full 62636468   18-DEC-18 /home/oracle/oradata/orcl/undotbs01.dbf

RMAN>run
{set until scn 62636468;
restore database;
recover database;
}

rman备份集清理

手工删除了备份集磁盘文件后，使用crosscheck backupset  检查会提示EXPIRED.需要使用 delete expired backupset;  删除对应记录。

RMAN> crosscheck backupset ;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=776 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rmans/data/fullback_ORCL_20181217_2 RECID=1 STAMP=995105790
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rmans/data/fullback_ORCL_20181217_3 RECID=2 STAMP=995107713
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rmans/data/fullback_ORCL_20181217_4 RECID=3 STAMP=995107750
Crosschecked 3 objects

RMAN>  delete expired backupset; 

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   EXPIRED     DISK        /home/oracle/rmans/data/fullback_ORCL_20181217_2
2       2       1   1   EXPIRED     DISK        /home/oracle/rmans/data/fullback_ORCL_20181217_3
3       3       1   1   EXPIRED     DISK        /home/oracle/rmans/data/fullback_ORCL_20181217_4

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/home/oracle/rmans/data/fullback_ORCL_20181217_2 RECID=1 STAMP=995105790
deleted backup piece
backup piece handle=/home/oracle/rmans/data/fullback_ORCL_20181217_3 RECID=2 STAMP=995107713
deleted backup piece
backup piece handle=/home/oracle/rmans/data/fullback_ORCL_20181217_4 RECID=3 STAMP=995107750
Deleted 3 EXPIRED objects

常用命令

--查看所有配置

RMAN> show all;

--查看备份集

list backup summary

-- 列出不符合备份策略的备份记录

report obsolete

--查看备份策略

RMAN> show RETENTION POLICY;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

EXPIRED和OBSOLETE的区别

EXPIRED状态的产生，与crosscheck命令是密切相关的，RMAN通过crosscheck命令检查备份是否存在于备份介质上，如果不存在，则状态由AVAILABLE改为EXPIRED。即一般是硬盘上被人工删除掉的文件会显示为EXPIRED。

而OBSOLETE是指该超过保留策略的备份，标记为obsolete。如已有一个全量备份，之前的备份被标识为obsolete。

recover database until cancel和 recover database区别

recover database until cancel用于不完全恢复，可以一步一步的跳也就是一个一个归档的应用，也可以AUTO全部应用，当然也可以在恢复完某个archivelog后cancel退出，但是他不会恢复current logfile如果需要恢复current logfile需要自己指定。
recover database 用于完全恢复，全部一起应用，包含current logfile，但是如果没有current logfile当然也会报错。