RAC备份与恢复

1 备份

1.1 备份策略

1.在节点1每天3点定时进行全量备份，同时删除过期备份和归档日志文件。
2.本地节点1发生故障时，节点2可以接管备份操作，在节点2上生成全备。

1.2 配置RMAN自动备份

1.2.1 打开归档模式并修改归档路径

RAC开启归档模式并修改归档路径

• 配置RMAN备份参数

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON；
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/rman_bak/hot_bak/controlfile/%F'; 

• 创建本地RMAN备份存储目录

# mkdir /rman_bak
# cd /rman_bak
# mkdir hot_bak
# mkdir controlfile
# mkdir log
# chown -R oracle:oinstall datafile
# chown -R oracle:oinstall controlfile
# chown -R oracle:oinstall log

1.2.2 RMAN备份脚本

• 节点1

#/bin/bash
source /home/oracle/.bash_profile
backtime=`date +%Y%m%d`
rman target /  log=/rman_bak/log/rac1_backupall_$backtime.log <<EOF
run{
sql 'alter system switch logfile';
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup database format '/rman_bak/hot_bak/rac1_%d_%T_%U'
include current controlfile plus archivelog delete input format '/rman_bak/hot_bak/rac1_arch_%U.bak';
report obsolete;
delete noprompt  obsolete;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
release channel ch1;
release channel ch2;
}
EOF
echo "backup complete!"
scp /rman_bak/log/rac1_backupall_$backtime.log oracle@rac2:/rman_bak/log/
exit

• 节点2

#/bin/bash
source /home/oracle/.bash_profile
backtime=`date +%Y%m%d`
if [ -f /rman_bak/log/rac1_backupall_$backtime.log ]
then
echo "rac1 backup have been success!"
exit
else
echo "rac1 backup have been filed,start rac2 backup!"
fi
rman target /  log=/rman_bak/log/rac2_backupall_$backtime.log <<EOF
run{
sql 'alter system switch logfile';
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup database format '/rman_bak/hot_bak/rac2_%d_%T_%U'
include current controlfile plus archivelog delete input format '/rman_bak/hot_bak/rac2_arch_%U.bak';
report obsolete;
delete noprompt  obsolete;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
release channel ch1;
release channel ch2;
}
EOF
echo "backup complete!"
exit

• 设置脚本权限

# chown -R oracle:oinstall bak.sh
# chmod 744 bak.sh

1.2.3 在oracle用户下添加定时任务脚本

• 节点1

# su - oracle
$ crontab -e

0 3 * * * /rmanbak/bak.sh >> /rmanbak/log/node1_rmanbak.log```

• 节点2

# su - oracle
$ crontab -e

0 4 * * * /rmanbak/bak.sh >> /rmanbak/log/node2_rmanbak.log

• 重启crond服务并设置开机自启动

# service crond restart
# chkconfig crond on

• 备份结果

2 RMAN恢复

2.1 模拟数据文件损坏

• 关闭两节点的数据库

[grid@rac1 ~]$ srvctl stop database -d oradb

• 删除一个数据文件

[root@rac1 ~]# su - grid
[grid@rac1 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCR/
ASMCMD> cd data
ASMCMD> ls
ORADB/
ASMCMD> cd oradb
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileoradb.ora
ASMCMD> cd datafile    
ASMCMD> ls
SYSAUX.260.1009379029
SYSTEM.259.1009379025
TBS1.268.1009463253TBS1.269.1009463463
UNDOTBS1.261.1009379033
UNDOTBS2.263.1009379043
USERS.264.1009379043
tbs01.dbf
tbs01_2.dbf
# 删除一个表空间，模拟文件损坏
ASMCMD> rm -rf SYSAUX.260.1009379029
ASMCMD> ls
SYSTEM.259.1009379025
TBS1.268.1009463253
TBS1.269.1009463463
UNDOTBS1.261.1009379033
UNDOTBS2.263.1009379043
USERS.264.1009379043
tbs01.dbf
tbs01_2.dbf

• 启动数据库
  这时启动数据库时发现报错：

SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size         1090519944 bytes
Database Buffers     3154116608 bytes
Redo Buffers           28884992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DATA/oradb/datafile/sysaux.260.1009379029'

2.2 恢复datafile

• 定位受损文件

SQL> select file#,error from v$recover_file;
     FILE#      ERROR
     ---------- -----------------------------------------------------------------     
     2          FILE NOT FOUND
SQL> select file#,ts#,name from v$datafile;
     FILE#      TS#---------- ----------NAME--------------------------------------------------------------------------------     
     1        0+DATA/oradb/datafile/system.259.1009379025
     2        1+DATA/oradb/datafile/sysaux.260.1009379029
     3        2+DATA/oradb/datafile/undotbs1.261.1009379033
     4        4+DATA/oradb/datafile/undotbs2.263.1009379043
     5        5+DATA/oradb/datafile/users.264.1009379043
     6        6+DATA/oradb/datafile/tbs01.dbf
     7        6+DATA/oradb/datafile/tbs01_2.dbf

7 rows selected.

• 进入rman模式进行恢复

[oracle@rac1 backup]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue May 28 15:42:37 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORADB (DBID=2762643787, not open)
RMAN> recover datafile 2;
Starting recover at 2019-05-28 15:43:01using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=715 instance=oradb1 device type=DISKRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/28/2019 15:43:03RMAN-06094: datafile 2 must be restored
RMAN> restore datafile 2;
Starting restore at 2019-05-28 15:43:14using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00002 to +DATA/oradb/datafile/sysaux.260.1009379029channel ORA_DISK_1: reading from backup piece /rman_bak/hot_bak/rac1_ORADB_20190528_0bu2mflj_1_1channel ORA_DISK_1: piece handle=/rman_bak/hot_bak/rac1_ORADB_20190528_0bu2mflj_1_1 tag=TAG20190528T153059channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:03Finished restore at 2019-05-28 15:43:18
RMAN> recover datafile 2;
Starting recover at 2019-05-28 15:43:26using channel ORA_DISK_1
starting media recoverymedia recovery complete, elapsed time: 00:00:00
Finished recover at 2019-05-28 15:43:26

• 查看数据文件已恢复

ASMCMD> ls
SYSAUX.260.1009379029
SYSTEM.259.1009379025
TBS1.268.1009463253TBS1.269.1009463463
UNDOTBS1.261.1009379033
UNDOTBS2.263.1009379043
USERS.264.1009379043
tbs01.dbf
tbs01_2.dbf

• 打开数据库

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 25870
Session ID: 664 Serial number: 17

SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE

打不开库，查看告警日志

1）导致这个错误的原因是RESTORE操作不会自动创建非数据库所属的目录，因此需要手动创建

ASMCMD> ls
DATA/
FRA/
OCR/
ASMCMD> cd fra
ASMCMD> ls
ORADB/
ASMCMD> cd oradb    
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> mkdir archivelog
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
archivelog/

恢复数据库出现ORA-15173错误
2）根据告警日志可知，不能归档，有可能是归档空间满了。可以增大闪回区的大小

SQL> startup mount;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size            2260088 bytes
Variable Size         1090519944 bytes
Database Buffers     3154116608 bytes
Redo Buffers           28884992 bytes
Database mounted.
SQL> select status,instance_name from v$instance;

STATUS         INSTANCE_NAME
------------ ----------------
MOUNTED      oradb1
SQL> alter system set db_recovery_file_dest_size=8G;
System altered.
SQL> alter database open;
Database altered.
# 成功开库!!!!!

SQL> desc v$recovery_file_dest;
Name                       Null?    Type
----------------------------------------- -------- ----------------------------
NAME                            VARCHAR2(513)
SPACE_LIMIT                        NUMBERSPACE_USED                        NUMBER
SPACE_RECLAIMABLE                    NUMBER
NUMBER_OF_FILES                    NUMBER
SQL> select * from v$recovery_file_dest;
NAME            SPACE_LIMIT SPACE_USED   SPACE_RECLAIMABLE    NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
+FRA8589934592  234881024                0                    5