文章目录
1 备份
1.1 备份策略
1.在节点1每天3点定时进行全量备份,同时删除过期备份和归档日志文件。
2.本地节点1发生故障时,节点2可以接管备份操作,在节点2上生成全备。
1.2 配置RMAN自动备份
1.2.1 打开归档模式并修改归档路径
- 配置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