oracle,RMAN定时备份shell脚本。恢复步骤及恢复示例

2020/11/15 09:58
阅读数 34

oracle,RMAN定时备份shell脚本。恢复步骤及恢复示例

一、环境配置

  1. 开启归档日志
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database  archivelog; 
SQL>alter database open;

#查看归档日志是否开启 Automatic archival  Enabled
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     119
Next log sequence to archive   121
Current log sequence           121
  1. 配置rman备份参数
su - oracle
rman target/
#开启控制文件自动备份(当出现数据库结构改变时,RMAN会自动备份控制文件。如创建表空间,删除表空间等。注,控制文件有延迟自动备份,默认300s)
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
#控制文件备份路径,格式
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rmanbak/controlfile/%F';
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/rmanbak/controlfile/backup%U';
  1. 查看所有设置
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL 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 '/u01/rmanbak/controlfile/%F';
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 CHANNEL DEVICE TYPE DISK FORMAT   '/u01/rmanbak/controlfile/backup%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/tools/oracle11g/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

二、备份脚本

#/bin/bash
rmanbakDir=/u01/rmanbak
#数据文件备份目录
rmanbakDatafile=$rmanbakDir/datafile
#控制文件备份目录
rmanbakControlfile=$rmanbakDir/controlfile
#日志输出路径
logDir=$rmanbakDir/log
backtime=`date +%Y%m%d`


isDirectory(){
   
   
        if test -d $1
        then
                echo "$1目录存在!"
                else
                        echo "$1目录不存在!,创建目录!"
                        mkdir -p $1
        fi
}
rmanbak(){
   
   
	echo "rman备份开始!"
	su - oracle -c "
	rman target /   <<EOF
	run{
	allocate channel ch1 device type disk;
	allocate channel ch2 device type disk;
	#手动备份当前控制文件
	backup current controlfile;
	#数据文件备份路径,格式
	backup database format '$1/orcl_%d_%T_%U';
	#归档日志文件备份路径,格式
	backup archivelog all format '$1/orcl_arch_%t_%s_%c' delete input;
	report obsolete; 
	delete noprompt  obsolete; 
	crosscheck backup;
	crosscheck archivelog all;
	delete noprompt expired backup;
	release channel ch1;
	release channel ch2;
	}
	exit
	EOF"
	echo "rman备份完成!"
	
}

removeLog(){
   
   
	echo "删除过期日志!"
	find $1 -type f -name "*.log" -mtime +7 -exec rm -rf {
   
   } \;
}

log(){
   
   
	tee -a $logDir/node1_backupall_$backtime.log
}

chown(){
   
   
		echo "更改目录用户名用户组!"
        su root -c "chown -R oracle:oinstall $1"
}

startRmanbak(){
   
   
	removeLog $logDir  | log
	isDirectory $rmanbakDatafile  | log
	isDirectory $rmanbakControlfile  | log
	isDirectory $logDir  | log
	rmanbak $rmanbakDatafile | log
	chown $rmanbakDir/  | log
	 	
}
startRmanbak
  1. 查看刚刚完成的备份情况
RMAN> list backupset summary;
RMAN> list backupset ;
RMAN> list backup of controlfile ;
RMAN> list backup of archivelog all;
RMAN> list backup of database;
RMAN> list backup of datafile n ;

三、rman数据恢复

  1. 表空间恢复步骤
#注:RMAN命令执行报错,可以先exit,再重新登陆rman
su - oracle
rman target/
RMAN>restore database;
RMAN>recover database;
  1. 控制文件恢复步骤
SQL>shutdown abort; 
SQL>startup force nomount; 
RMAN>list backup of controlfile;
RMAN>restore controlfile to 'xxx.ctl' from 'xxx/c-xx-xx-xx';
SQL>alter database mount;
RMAN> recover database; 
SQL> alter database open resetlogs;

四、举个栗子

  1. 恢复表空间dbf文件
#查看数据文件存放位置
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------
/u01/oracle/tools/oracle11g/oradata/orcl/imagedb.dbf


 #将imagedb.dbf文件改名 imagedbbak.dbf
-rw-r----- 1 oracle oinstall   9846784 Nov 12 16:41 control01.ctl
-rw-r----- 1 oracle oinstall 209723392 Nov 12 14:48 imagedbbak.dbf
-rw-r----- 1 oracle oinstall  52436992 Nov  2 17:46 imagedbtemp.dbf
-rw-r----- 1 oracle oinstall  52429312 Nov 12 16:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Nov 12 16:28 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Nov 12 16:28 redo03.log
-rw-r----- 1 oracle oinstall 723525632 Nov 12 16:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 734011392 Nov 12 16:38 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Nov 11 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall  78651392 Nov 12 16:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 Nov 12 16:28 users01.dbf
#关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
#启动数据库报错
SQL> startup;
ORACLE instance started.

Total System Global Area 6680915968 bytes
Fixed Size                  2213936 bytes
Variable Size            3959424976 bytes
Database Buffers         2684354560 bytes
Redo Buffers               34922496 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oracle/tools/oracle11g/oradata/orcl/imagedb.dbf'

RMAN> restore database;

Starting restore at 12-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3966 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 00001 to /u01/oracle/tools/oracle11g/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oracle/tools/oracle11g/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oracle/tools/oracle11g/oradata/orcl/imagedb.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/datafile/orcl_ORCL_20201112_0gvfblen_1_1
channel ORA_DISK_1: piece handle=/u01/rmanbak/datafile/orcl_ORCL_20201112_0gvfblen_1_1 tag=TAG20201112T161415
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00002 to /u01/oracle/tools/oracle11g/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/tools/oracle11g/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/datafile/orcl_ORCL_20201112_0hvfblen_1_1
channel ORA_DISK_1: piece handle=/u01/rmanbak/datafile/orcl_ORCL_20201112_0hvfblen_1_1 tag=TAG20201112T161415
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 12-NOV-20

RMAN> recover database;

Starting recover at 12-NOV-20
using channel ORA_DISK_1

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

Finished recover at 12-NOV-20
SQL>alter database open;
Database altered.

imagedb.dbf文件恢复情况查看

在这里插入图片描述

  1. 恢复控制文件
su - oracle
sqlplus / as sysdba
#1.强制关闭数据库
SQL> shutdown abort; 
#2.强制启动数据库到nomount状态。
SQL>startup force nomount; 


#3.另开一个窗口,查看已备份的控制文件
su - oracle
rman target/
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14      Full    9.45M      DISK        00:00:00     12-NOV-20      
        BP Key: 14   Status: AVAILABLE  Compressed: NO  Tag: TAG20201112T161420
        Piece Name: /u01/rmanbak/controlfile/c-1582243166-20201112-03
  Control File Included: Ckp SCN: 2581400      Ckp time: 12-NOV-20

#4.恢复控制文件到指定的路径。(to 要恢复的控制文件  from 已备份的控制文件)
#查看控制文件位置SQL>select name from v$datafile;。 
RMAN>restore controlfile to '/u01/oracle/tools/oracle11g/oradata/orcl/control01.ctl' from '/u01/rmanbak/controlfile/c-1582243166-20201112-03';
RMAN>restore controlfile to '/u01/oracle/tools/oracle11g/flash_recovery_area/orcl/control02.ctl' from '/u01/rmanbak/controlfile/c-1582243166-20201112-03';

#5.在sql窗口,装载数据库
SQL>alter database mount;
Database altered. 

#6.恢复数据库
RMAN> recover database; 

#7.打开数据库
SQL> alter database open resetlogs;
database opened

五、定时任务添加

[root@OCR-DEVDB u01]crontab -e
#每天凌晨4点执行,脚本/u01/rman_backup.sh
00 04 * * * /u01/rman_backup.sh
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部