oracle怎么备份脚本,数据库备份脚本oracle

oracle rman备份脚本怎么写

在ORACLE数据库中，RMAN备份的脚本非常多，下面介绍一例shell脚本如何通过RMAN备份，以及FTP上传RMAN备份文件以及归档日志文件的脚本。

创新互联公司专注于铁力网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供铁力营销型网站建设，铁力网站制作、铁力网页设计、铁力网站官网定制、微信小程序开发服务，打造铁力网络公司原创品牌,更为您提供铁力网站排名全网营销落地服务。

fullback.sh 里面调用RMAN命令做数据库备份，它使用的cmdfile为/home/oracle/backup/bin/fullback.rcv，同时在/home/oracle/backup/logs目录下生成日志文件。

1: [oracle@DB-Server bin]$ more fullback.sh

2:

3: #!/bin/bash

4:

5: export ORACLE_BASE=/u01/app/oracle

6:

7: export ORACLE_SID=gps

8:

9: ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

10:

11: TMP=/tmp; export TMP

12:

13: TMPDIR=$TMP; export TMPDIR

14:

15: PATH=/usr/sbin:$PATH; export PATH

16:

17: PATH=$ORACLE_HOME/bin:$PATH; export PATH

18:

19: LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

20:

21: CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;

22:

23: export CLASSPATH

24:

25: TODAY=`date +%Y_%m_%d`

26:

27: rman nocatalog target / cmdfile /home/oracle/backup/bin/fullback.rcv log /home/oracle/backup/logs/fullbackup_$TODAY.log

28:

29: /home/oracle/backup/bin/ftpbackup.sh

30:

fullback.rcv文件非常简单， 如下所示：

1: [oracle@DB-Server bin]$ more /home/oracle/backup/bin/fullback.rcv

2:

3: run{

4:

5: allocate channel c4 type disk;

6:

7: backup as compressed backupset

8:

9: skip inaccessible

10:

11: tag fullbackupwitharchivelog

12:

13: (database);

14:

15: backup current controlfile;

16:

17: backup spfile;

18:

19: sql "alter system archive log current";

20:

21: delete noprompt obsolete;

22:

23: release channel c4;

24:

25: }

26:

RMAN生成的备份文件，需要通过FTP上传到FTP服务器，一则数据库服务器没有这么多空间存储多天的备份，二则是出于容灾、数据安全需要。

下面脚本中FTP服务器，用户名密码均使用xxx替代，在实际环境中，使用具体的信息替代即可。

1: [oracle@DB-Server bin]$ more ftpbackup.sh

2:

3: #!/bin/sh、

4:

5: rm -f /home/oracle/.netrc

6:

7: date_yesterday=`date -d'yesterday' +%Y_%m_%d`

8:

9: date_today=`date +%Y_%m_%d`

10:

11: echo "default login xxxx password xxxxxx" /home/oracle/.netrc

12:

13: echo "macdef init" /home/oracle/.netrc

14:

15: echo "binary" /home/oracle/.netrc

16:

17: echo "cd archivelog" /home/oracle/.netrc

18:

19: echo "mkdir $date_yesterday" /home/oracle/.netrc

20:

21: echo "cd $date_yesterday" /home/oracle/.netrc

22:

23: echo "lcd /u04/flash_recovery_area/gps/archivelog/$date_yesterday" /home/oracle/.netrc

24:

25: echo "mput *" /home/oracle/.netrc

26:

27: echo "cd .." /home/oracle/.netrc

28:

29: echo "mkdir $date_today" /home/oracle/.netrc

30:

31: echo "cd $date_today" /home/oracle/.netrc

32:

33: echo "lcd /u04/flash_recovery_area/gps/archivelog/$date_today" /home/oracle/.netrc

34:

35: echo "mput * "/home/oracle/.netrc

36:

37: echo "cd .." /home/oracle/.netrc

38:

39: echo "cd ../backupset" /home/oracle/.netrc

40:

41: echo "mkdir $date_today" /home/oracle/.netrc

42:

43: echo "cd $date_today" /home/oracle/.netrc

44:

45: echo "lcd /u04/flash_recovery_area/gps/backupset/$date_today" /home/oracle/.netrc

46:

47: echo "mput *" /home/oracle/.netrc

48:

49: echo "cd .." /home/oracle/.netrc

50:

51: echo "cd ../autobackup" /home/oracle/.netrc

52:

53: echo "mkdir $date_today" /home/oracle/.netrc

54:

55: echo "cd $date_today" /home/oracle/.netrc

56:

57: echo "lcd /u04/flash_recovery_area/gps/autobackup/$date_today" /home/oracle/.netrc

58:

59: echo "mput *" /home/oracle/.netrc

60:

61: echo "quit" /home/oracle/.netrc

62:

63: echo "" /home/oracle/.netrc

64:

65: chmod 600 /home/oracle/.netrc

66:

67: ftp -i -v xxx.xxx.xxx.xxx 8021 /home/oracle/backup/logs/ftp$date_today.log 21

68:

另外，关于归档日志也需要每隔2小时上传一次到FTP服务器，2小时上传一次归档日志的shell脚本如下所示：

1: [oracle@DB-Server bin]$ more ftp2hours.sh

2:

3: #!/bin/sh

4:

5: rm -f /home/oracle/.netrc

6:

7: date_yesterday=`date -d'yesterday' +%Y_%m_%d`

8:

9: date_today=`date +%Y_%m_%d`

10:

11: echo "default login xxxx password xxxx" /home/oracle/.netrc

12:

13: echo "macdef init" /home/oracle/.netrc

14:

15: echo "binary" /home/oracle/.netrc

16:

17: echo "cd archivelog" /home/oracle/.netrc

18:

19: echo "mkdir $date_today" /home/oracle/.netrc

20:

21: echo "cd $date_today" /home/oracle/.netrc

22:

23: echo "lcd /u04/flash_recovery_area/gps/archivelog/$date_today" /home/oracle/.netrc

24:

25: echo "mput * "/home/oracle/.netrc

26:

27: echo "quit" /home/oracle/.netrc

28:

29: echo "" /home/oracle/.netrc

30:

31: chmod 600 /home/oracle/.netrc

32:

33: ftp -i -v xxx.xxx.xxx.xxx 8021 /home/oracle/backup/logs/ftp2hours.$date_today.log 21

34:

最后需要将RMAN备份生成的日志文件，以及FTP上传备份文件以及归档日志的记录通过邮件形式发送给DBA或系统管理员，

1: [oracle@DB-Server bin]$ more chkbackandmail.sh

2: #!/bin/bash

3: rm -f /home/oracle/backup/bin/sendmail.pl

4: date_today=`date +%Y_%m_%d`

5: subject="Oracle Backup Alert Service on $date_today"

6: content="Dear colleagues,

7:

8: Attached please find the logs of xxx(xxx.xxx.xxx.xxx) oracle database backup and transfer to FTP Server(xxx.xxx.xxx.xxx), please

9: review the file and check whether the backup succeeded or not,and double check all backups have been dumped to tape, many tha

10: nks

11:

12:

13:

14:

15: Best regards

16: Oracle Alert Services

17:

18: "

19: file="/home/oracle/backup/logs/fullbackup_$date_today.log,/home/oracle/backup/logs/ftp$date_today.log"

20: echo "#!/usr/bin/perl" /home/oracle/backup/bin/sendmail.pl

21: echo "use Mail::Sender;" /home/oracle/backup/bin/sendmail.pl

22: echo "\$sender = new Mail::Sender {smtp = 'xxx.xxx.xxx.xxx', from = 'xxxx@xxx.com'}; " /home/oracle/backup/bin/sendmai

23: l.pl

24: echo "\$sender-MailFile({to = 'xxx@esquel.com'," /home/oracle/backup/bin/sendmail.pl

25: echo "cc='xxx@xxx.com,xxx@xxx.com,xxx@xxx.com'," /home/oracle/backup/b

26: in/sendmail.pl

27: echo "subject = '$subject'," /home/oracle/backup/bin/sendmail.pl

28: echo "msg = '$content'," /home/oracle/backup/bin/sendmail.pl

29: echo "file = '$file'});" /home/oracle/backup/bin/sendmail.pl

30: perl /home/oracle/backup/bin/sendmail.pl

最后在Crontab 作业里面配置调用这些shell脚本。例如如下所示，在1：01分执行fullback.sh ，每隔两个小时（例如0:50、2:50...)执行一次ftp2hours.sh, 在每天早上8:40执行chkbackandmail.sh 发送fullback.sh 以及ftp2hour.sh的执行日志记录。

oracle 备份与恢复、完全备份脚本

exp system/oracle@orcl buffer=1024 file=/home/oracle/backup/orcl.dmp full=y

exp test/test@orcl file=/home/oracle/backup/test.dmp owner=test

exp test/test@orcl file=/home/oracle/backup/student.dmp tables=(student,teacher)

exp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y

log=xx

imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt

imp test/test@orcl file=/home/oracle/backup/test.dmp

imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)

imp 用户名/密码@远程的IP:端口/实例 file=存放的位置\文件名称.dmp full=y

vi /home/oracle/fullbackup.sh

sysname='centos7'

syspath=/home/oracle/backup

v_date=$(date '+%Y%m%d%H%M%S')

logfile= {v_date}.log

echo backup_time {logfile}

echo "backup jcd" {logfile}

date '+%Y-%m-%d %H:%M:%S' syspath/orcl${v_date}.dmp

exp system/oracle@orcl file= {logfile}

echo "end_time" {logfile}

gzip ${filename}

0 2 * * * sh /home/oracle/fullbackup.sh

oracle怎么用rman脚本进行备份

Oracle数据库中rman备份脚本非常实用

查询字符集

SQL select * from nls_database_parameters;

NLS_CHARACTERSET

AL32UTF8

备份时字符集很重要，不然会出乱码

一、0级备份（全备）红色是要修改的，根据个人的存放位置。

[oracle@oracle122 script]$ cat db_bak0.rman

run

{

configure retention policy to recovery window of 8 days;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F';

allocate channel ch1 device type disk format '/home/oracle/rmanbak/inc0_%d_%s_%p_%u_%T';

backup incremental level 0 tag='level 0' database skip inaccessible filesperset 10 plus archivelog filesperset 20;

release channel ch1;

}

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

delete noprompt archivelog until time 'sysdate-30';

0级运行脚本

[oracle@oracle122 script]$ cat exec_0_level.sh

ORACLE_HOSTNAME=oracle122; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

export DATE=$(date +%Y_%m_%d)

export NLS_LANG="Simplified Chinese_china".AL32UTF8

/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target sys/sys_admin cmdfile /home/oracle/rmanbak/script/db_bak0.rman log /home/oracle/rmanbak/script/log/rman_$DATE.log append

二、1级备份（增量）

[oracle@oracle122 script]$ cat db_bak1.rman

run

{

configure retention policy to recovery window of 8 days;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F';

allocate channel ch1 device type disk format '/home/oracle/rmanbak/inc1_%d_%s_%p_%u_%T';

backup incremental level 1 tag='level 1' database skip inaccessible filesperset 10 plus archivelog filesperset 20;

release channel ch1;

}

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

delete noprompt archivelog until time 'sysdate-30';

1级运行脚本

[oracle@oracle122 script]$ cat exec_1_level.sh

ORACLE_HOSTNAME=oracle122; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

export DATE=$(date +%Y_%m_%d)

export NLS_LANG="Simplified Chinese_china".AL32UTF8

/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target sys/sys_admin cmdfile /home/oracle/rmanbak/script/db_bak1.rman log /home/oracle/rmanbak/script/log/rman_$DATE.log append

三、2级备份（从0级追加到现在的备份）

[oracle@oracle122 script]$ cat db_bak2.rman

run

{

configure retention policy to recovery window of 8 days;

configure controlfile autobackup on;

configure controlfile autobackup format for device type disk to '/home/oracle/rmanbak/%F';

allocate channel ch1 device type disk format '/home/oracle/rmanbak/inc2_%d_%s_%p_%u_%T';

backup incremental level 2 tag='level 2' database skip inaccessible filesperset 10 plus archivelog filesperset 20 delete all input;

release channel ch1;

}

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

delete noprompt obsolete;

2级运行脚本

[oracle@oracle122 script]$ cat exec_2_level.sh

ORACLE_HOSTNAME=oracle122; export ORACLE_HOSTNAME

ORACLE_BASE=/u01/app; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

export DATE=$(date +%Y_%m_%d)

export NLS_LANG="Simplified Chinese_china".AL32UTF8

/u01/app/oracle/product/11.2.0/dbhome_1/bin/rman target sys/sys_admin cmdfile /home/oracle/rmanbak/script/db_bak2.rman log /home/oracle/rmanbak/script/log/rman_$DATE.log append

定时备份星期天为0级全备，星期三为2级备份，星期一，星期二，星期四，星期五，星期六为1级增量备份晚上11点开始备份

[oracle@oracle122 script]$ crontab -l

* 23 * * 0 /home/oracle/rmanbak/script/exec_0_level.sh

* 23 * * 1 /home/oracle/rmanbak/script/exec_1_level.sh

* 23 * * 2 /home/oracle/rmanbak/script/exec_1_level.sh

* 23 * * 3 /home/oracle/rmanbak/script/exec_2_level.sh

* 23 * * 4 /home/oracle/rmanbak/script/exec_1_level.sh

* 23 * * 5 /home/oracle/rmanbak/script/exec_1_level.sh

* 23 * * 6 /home/oracle/rmanbak/script/exec_1_level.sh

Oracle数据库备份脚本怎么写？

没必要用root权限执行，oracle用户同样可以执行crontab计划任务

1、su - oracle

crontab -e

0 4 * * 0 /home/oracle/exp.sh 21 /home/oracle/script_exp.log;

0～59 表示分

0～23 表示小时

1～31 表示日

1～12 表示月份

0～6 表示星期（其中0表示星期日）

2、cat /home/oracle/exp.sh

. ~/.bash_profile

bakdir=/opt/dbbak

logdir=/home/oracle/dbbak

rq=$(date +%Y%m%d)

exp system/***** owner=jysh file=${bakdir}/jysh_${rq}.dmp log=${logdir}/jysh_${rq}.log;

find ${bakdir} -type f -mtime +30 -exec rm -rf {} \;

oracle中怎么设置数据库自动备份？

Oracle自动备份的三种方法：\x0d\x0aOracle自动备份数据库不外乎以下三种方式：\x0d\x0aWINDOWS下的任务计划(At命令)\x0d\x0aUNIX下的Crontab \x0d\x0a第三方工具如Viritas\x0d\x0a在以上三种方式中Viritas属于第三方工具，很多人可能都没有接触，主要说一下windows的任务计划与unix的cron\x0d\x0a1、生成脚本文件，如backup.rcv 假定文件内容如下：\x0d\x0a$cat backup.rcv connect target sys/password rcvcat rman/rman@localname; run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; backup fileaperset 3 format ‘/u01/oradata/backup/arch%u_%s_%p’ archivelog all delete input; release channel c1; release channel c2; release channel c3; }\x0d\x0a\x0d\x0a2、生成执行文件在windows上生成backup_archive.bat，内容包括rman cmdfile = backup.rcv 在unix下生成 backup_archive.sh，内容包括oracle/ramn/rman cmdfile = backup.rcv\x0d\x0a3、加入调度在windows中用任务计划向导即可，或使用at命令。在unix中，在目标机器上编写一个文件，用以启动Oracle自动备份进程。假定文件名为ORACLE，文件将放在/var/spool/cron/crontabs目录下\x0d\x0a$cat oracle \x0d\x0a0 23 * * 0 backup_archive.sh \x0d\x0a#表示星期天23点对数据库备份 \x0d\x0a0 12,18 * * * backup_archive.sh \x0d\x0a #表示每天12点，18点备份 \x0d\x0aCrontab文件的每一行由六个域(minutes,hours,day of month,month,day of week,command)组成，域之间用空格或Tab分隔开来。