1、数据库环境
数据库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.dbf2 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/undotbs01.dbf3 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf4 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf5 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf6 ONLINE READ WRITE /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf7 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.log1 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo11.log3 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo03.log2 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo02.log5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo05.log5 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo55.log4 ONLINE /u01/app/oracle/oradata/ocmdb/lfile/redo04.log4 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 databasesqlplus / as sysdba <<EOF#--shutdown databaseshutdown immediate;#--Copy Data file!cp /u01/app/oracle/oradata/ocmdb/dfile/*.dbf /backup!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backup#--Copy Control file!cp /u01/app/oracle/ocmdb/cfile/* /backup#--Copy Log file!cp /u01/app/oracle/oradata/ocmdb/lfile/*.log /backup#--Copy archive log!cp -R /u01/app/oracle/rmanbak/OCMDB/archivelog/* /backup#--startup databasestartup; |
说明：
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.dbf6 BIG_TBS /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf5 TBS3 /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf4 TBS_1 /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf3 SYSAUX /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf2 UNDOTBS /u01/app/oracle/oradata/ocmdb/undotbs01.dbf1 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 databasesqlplus / as sysdba <<EOF#--archivealter system archive log current;#--start hotbakalter tablespace system begin backup;!cp /u01/app/oracle/oradata/ocmdb/dfile/system01.dbf /backupalter tablespace system end backup;alter tablespace UNDOTBS begin backup;!cp /u01/app/oracle/oradata/ocmdb/undotbs01.dbf /backupalter tablespace UNDOTBS end backup;alter tablespace SYSAUX begin backup;!cp /u01/app/oracle/oradata/ocmdb/dfile/sysaux01.dbf /backupalter tablespace SYSAUX end backup;alter tablespace TBS_1 begin backup;!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_1.dbf /backupalter tablespace TBS_1 end backup;alter tablespace TBS3 begin backup;!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs3.dbf /backupalter tablespace TBS3 end backup;alter tablespace BIG_TBS begin backup;!cp /u01/app/oracle/oradata/ocmdb/dfile/tbs_big01.dbf /backupalter tablespace BIG_TBS end backup;alter tablespace RMAN_TS begin backup;!cp /u01/app/oracle/oradata/ocmdb/dfile/rman.dbf /backupalter tablespace RMAN_TS end backup;#--end#--bak control file#--binaryalter database backup controlfile to '/backup/controlbinbak.000';#--asciialter database backup controlfile to trace;alter system archive log current; |
说明：
1、热备份必须在数据库归档方式下才可以运行
2、以上脚本可以在数据库运行状态下备份数据库所有的数据文件（除了临时数据文件），没有必要备份联机日志。
3、归档日志至少需要一次完整备份之后的所有日志。
4、如果以上命令没有成功依次执行，那么备份也是无效的，如连接数据库不成功，那么备份则无效
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RMAN备份脚本
--本地
创建文件夹
|
1
2
3
4
|
mkdir /home/oracle/backupmkdir /home/oracle/backup/scriptmkdir /home/oracle/backup/logmkdir /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/rmancreate catalog tablespace rman_tbs;connect target sys/lukewhx@lukeregister 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/rmanfile=/home/oracle/backup/export/rman.dmplog=/home/oracle/backup/log/rman.log-- 倒出RMAN数据SHELL脚本exp_rman.sh#!/bin/bashcd $HOME. .bash_profilecd $HOME/backup/scriptexp parfile=exp_rman.par-- 零级备份RMAN脚本level0_backup.rcvconnect catalog rman/rmanconnect target sys/unumall@unu2run {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/bashcd $HOME. .bash_profilecd $HOME/backup/scriptrman cmdfile=level0_backup.rcv msglog=$HOME/backup/log/level0_backup.log. /home/oracle/backup/script/exp_rman.sh--一级差异增量备份RMAN脚本 level1_backup.rcvconnect catalog rman/rmanconnect target sys/luke@lukerun {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/bashcd $HOME. .bash_profilecd /home/oracle/backup/scriptrman cmdfile=level1_backup.rcv msglog=$HOME/backup/log/level1.log. /home/oracle/backup/script/exp_rman.sh--二级差异增量备份RMAN脚本 level2_backup.rcvconnect catalog rman/rmanconnect target sys/luke@lukerun {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/bashcd $HOME. .bash_profilecd /home/oracle/backup/scriptrman 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 oracleSHELL=/bin/bash --以下脚本在bash下执行MAILTO=oracle --执行日志以邮件形式邮给oracle用户，可以/var/spool/mail/oracle下查收10 1 * * 0 /home/oracle/backup/script/level0_backup.sh10 1 * * 1 /home/oracle/backup/script/level2_backup.sh10 1 * * 2 /home/oracle/backup/script/level2_backup.sh10 1 * * 3 /home/oracle/backup/script/level1_backup.sh10 1 * * 4 /home/oracle/backup/script/level2_backup.sh10 1 * * 5 /home/oracle/backup/script/level2_backup.sh10 1 * * 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
