mysqloracle备份之备份测试脚本的方法（冷备、热备、rman)
数据库DBID及打开模式
?
1
2
3
4
SQL> select dbid,open_mode from v$database;
DBID OPEN_MODE
---------- ----------
4106451204 READ WRITE
数据文件:
?
1
2
3
4
5
6
7
8
9
10
11
SQL> select file#,status,enabled,name from v$datafile;
FILE# STATUS ENABLED NAME
---------- ------- ---------- --------------------------------------------------------------------------------
1 SYSTEM READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf
2 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/undotbs01.dbf
3 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf
4 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf
5 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf
6 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf
7 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf
控制文件：
?
1
2
3
4
5
6
7
SQL> select status,name from v$controlfile;
STATUS NAME
------- --------------------------------------------------------------------------------
/u01/app/oracle/ocmdb/cfile/ora_control1
/u01/app/oracle/ocmdb/cfile/ora_control2
/u01/app/oracle/ocmdb/cfile/control3
联机日志：
?
1
2
3
4
5
6
7
8
9
10
11
12
SQL> select group#,status,type,member from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo01.log
1 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo11.log
3 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo03.log
2 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo02.log
5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo05.log
5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo55.log
4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo04.log
4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo44.log
2、数据库备份脚本
冷备份脚本
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#rem script:coldbak.sh
#rem creater:chon
#rem date:2011
#rem desc:offline full backup database
#--connect database
sqlplus / as sysdba <<EOF
#--shutdown database
shutdown immediate;
#--Copy Data file
!cp /u01/app/oracle/oradata/ocmdb/dfile/IT之家.dbf /backup
!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup
#--Copy Control file
!cp /u01/app/oracle/ocmdb/cfile/IT之家 /backup
#--Copy Log file
!cp /u01/app/oracle/oradata/ocmdb/lfile/IT之家.log /backup
#--Copy archive log
!cp -R /u01/app/oracle/rmanbak/OCMDB/archivelog/IT之家 /backup
#--startup database
startup;
说明：
1、以上脚本在数据库关闭状态下备份数据库所有的数据文件，联机日志，控制文件，归档日志（在一个目录下），如果成功备份，所有文件是一致的。
2、没有备份参数文件，参数文件可以另外备份，没有必要每次都备份，只需要在改变设置后备份一次。
3、如果以上命令没有成功依次执行，那么备份将是无效的，如连接数据库不成功，那么肯定关闭数据库也不成功，那么备份则无效
4、冷备份建议下人工干预下执行。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OS热备份
查看表空间和数据文件对应关系
?
1
2
3
4
5
6
7
8
9
10
11
SQL> select file_id,tablespace_name,file_name from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ --------------------------------------------------------------------------------
7 RMAN_TS /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf
6 BIG_TBS /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf
5 TBS3 /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf
4 TBS_1 /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf
3 SYSAUX /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf
2 UNDOTBS /u01/app/oracle/oradata/ocmdb/undotbs01.dbf
1 SYSTEM /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf
数据库OS热全备份脚本
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#rem script:hotbak.sh
#rem creater:chon
#rem date:2011
#rem desc:backup all database datafile in archive
#--connect database
sqlplus / as sysdba <<EOF
#--archive
alter system archive log current;
#--start hotbak
alter tablespace system begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf /backup
alter tablespace system end backup;
alter tablespace UNDOTBS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup
alter tablespace UNDOTBS end backup;
alter tablespace SYSAUX begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf /backup
alter tablespace SYSAUX end backup;
alter tablespace TBS_1 begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf /backup
alter tablespace TBS_1 end backup;
alter tablespace TBS3 begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf /backup
alter tablespace TBS3 end backup;
alter tablespace BIG_TBS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf /backup
alter tablespace BIG_TBS end backup;
alter tablespace RMAN_TS begin backup;
!cp /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf /backup
alter tablespace RMAN_TS end backup;
#--end
#--bak control file
#--binary
alter database backup controlfile to '/backup/controlbinbak.000';
#--ascii
alter database backup controlfile to trace;
alter system archive log current;
说明：
1、热备份必须在数据库归档方式下才可以运行
2、以上脚本可以在数据库运行状态下备份数据库所有的数据文件（除了临时数据文件），没有必要备份联机日志。
3、归档日志至少需要一次完整备份之后的所有日志。
4、如果以上命令没有成功依次执行，那么备份也是无效的，如连接数据库不成功，那么备份则无效
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RMAN备份脚本
--本地
创建文件夹
?
1
2
3
4
mkdir /home/oracle/backup
mkdir /home/oracle/backup/script
mkdir /home/oracle/backup/log
mkdir /home/oracle/backup/export
--备份目的地
?
1
mkdir /home/oracle/rman_backup
创建备份表空间
?
1
create tablespace rman_tbs datafile '/oradata/luke/rman_tbs01.dbf' size 1024M;
创建备份用户
?
1
create user rman identified by rman default tablespace rman_tbs temporary tablespace temp;
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
grant connect,resource ,recovery_catalog_owner to rman;
rman catalog rman/rman
create catalog tablespace rman_tbs;
connect target sys/lukewhx@luke
register database;
report schema;
configure retention policy to redundancy 2;
configure retention policy to recovery window of 7 days;
--倒出RMAN用户数据脚本exp_rman.par (即备份catalog库）
userid=rman/rman
file=/home/oracle/backup/export/rman.dmp
log=/home/oracle/backup/log/rman.log
-- 倒出RMAN数据SHELL脚本exp_rman.sh
#!/bin/bash
cd $HOME
. .bash_profile
cd $HOME/backup/script
exp parfile=exp_rman.par
-- 零级备份RMAN脚本level0_backup.rcv
connect catalog rman/rman
connect target sys/unumall@unu2
run {
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
allocate channel d1 type disk;
allocate channel d2 type disk;
backup incremental level 0 database format '/home/oracle/rman_backup/level0_%d_%s_%p_%u.bak'
tag='level 0' include current controlfile;
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;
release channel d2;
release channel d1;
}
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
resync catalog;
--零级备份SHELL脚本的level0_backup.sh
#!/bin/bash
cd $HOME
. .bash_profile
cd $HOME/backup/script
rman cmdfile=level0_backup.rcv msglog=$HOME/backup/log/level0_backup.log
. /home/oracle/backup/script/exp_rman.sh
--一级差异增量备份RMAN脚本 level1_backup.rcv
connect catalog rman/rman
connect target sys/luke@luke
run {
allocate channel d1 type disk;
backup incremental level 1 format '/home/oracle/rman_backup/level1_%d_%s_%p_%u.bak' tag = 'level 1' database;
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;
release channel d1;
}
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
resync catalog;
--一级差异增量备份SHELL脚本level1_backup.sh
#!/bin/bash
cd $HOME
. .bash_profile
cd /home/oracle/backup/script
rman cmdfile=level1_backup.rcv msglog=$HOME/backup/log/level1.log
. /home/oracle/backup/script/exp_rman.sh
--二级差异增量备份RMAN脚本 level2_backup.rcv
connect catalog rman/rman
connect target sys/luke@luke
run {
allocate channel d1 type disk;
backup incremental level 2 format '/home/oracle/rman_backup/level2_%d_%s_%p_%u.bak' tag = 'level 2' database;
sql 'alter system archive log current';
backup archivelog all format '/home/oracle/rman_backup/log_%d_%s_%p_%u.bak' delete all input;
release channel d1;
}
crosscheck backup;
delete noprompt expired backup;
delete noprompt obsolete;
resync catalog;
--二级差异增量备份SHELL脚本level2_backup.sh
?
1
2
3
4
5
6
#!/bin/bash
cd $HOME
. .bash_profile
cd /home/oracle/backup/script
rman cmdfile=level2_backup.rcv msglog=$HOME/backup/log/level2.log
. /home/oracle/backup/script/exp_rman.sh
提高 RMAN增量备份性能
?
1
2
3
4
alter database enable block change tracking using file
'/u01/app/oracle/admin/devdb/bdump/luke.log';
desc v$block_change_tracking;
linux下定时执行备份脚本
?
1
2
3
4
5
6
7
8
9
10
crontab -e -u oracle
SHELL=/bin/bash --以下脚本在bash下执行
MAILTO=oracle --执行日志以邮件形式邮给oracle用户，可以/var/spool/mail/oracle下查收
10 1 IT之家 IT之家 0 /home/oracle/backup/script/level0_backup.sh
10 1 IT之家 IT之家 1 /home/oracle/backup/script/level2_backup.sh
10 1 IT之家 IT之家 2 /home/oracle/backup/script/level2_backup.sh
10 1 IT之家 IT之家 3 /home/oracle/backup/script/level1_backup.sh
10 1 IT之家 IT之家 4 /home/oracle/backup/script/level2_backup.sh
10 1 IT之家 IT之家 5 /home/oracle/backup/script/level2_backup.sh
10 1 IT之家 IT之家 6 /home/oracle/backup/script/level2_backup.sh
一周差异备份策略：
备份目标库和catalog库
周日0级全备，周一周二为2级，周三为1级，周四周五周六为2级。
每天凌晨1点10分开始备份
零级备份
?
1
backup incremental level 0 database;
一级差异增量 差异增量是默认增量备份方式
?
1
backup incremental level 1 database;
一级累计增量
?
1
backup incremental level 1 cumulative database;
总结
到此这篇关于oracle备份之备份测试脚本的方法（冷备、热备、rman)的文章就介绍到这了,更多相关oracle备份测试脚本内容请搜索聚合云库文库以前的文章或继续浏览下面的相关文章希望大家以后多多支持聚合云库文库！
原文链接：https://www.cnblogs.com/chinaops/p/13587704.html
本站内容来源于网络，如有侵权请与我们联系，我们会及时删除，我们深感抱歉！
注：本站所有信息仅供用于网络技术学习参考,学习中请遵循相关法律法规！
本文地址: https://v30.fanwenzhu.com/sql/oracle/8944.shtml
相关文章
热门TAG
win10 ecshop 主机 阿里云 解决 配置 C# C++ 解析 SQL语句 命令 Go语言 方法 CSS3 HTML5 CSS win7 MSSQL 服务器配置 IIS7.5 IIS7 IIS6 IIS CentOS 7 Linux oracle数据库 oracle phpcms discuz discuz教程最新文章
-
是因为scan的IP问题补占用
时间：2021-01-20
-
还你一个干净的model 2、功
时间：2021-01-20
-
既然 ROWNUM 列并不是真实存
时间：2021-01-20
-
Real Application Clusters
时间：2021-01-20
-
在tools-preferences-connection（
时间：2021-01-20
-
Windows Sever 2012下Oracle 12c安
时间：2021-01-10
-
Oracle安装监听器错误的解
时间：2021-01-07
-
oracle远程连接服务器数据
时间：2021-01-07
热门文章
-
Oracle存储过程编程详解
时间：2020-12-07
-
Azure Queue Storage 基本用法 Azure Storage 之
时间：2020-12-26
-
win10下oracle 11g安装图文教程
时间：2020-12-25
-
oracle 数据库学习 基本结构介绍
时间：2020-12-13
-
Azure File Storage 基本用法 Azure Storage 之 F
时间：2020-12-26
-
windows使用sqlpus连接oracle 数据库的教程图
时间：2020-12-25
-
Window下Oracle Database 11g 发行版2安装教程
时间：2020-12-29
-
Oracle解锁的方式介绍
时间：2020-12-14
-
Oracle 12c Study之Installer Oracle
时间：2021-01-06
-
linux下oracle设置开机自启动实现方法
时间：2020-12-13