/* 2008/06/10 星期二
*蒙昭良
*环境:linux5 + Oracle10gR2
*备份策略
*增量备份中的差异备份:周日和周三执行0级备份,其他每天执行1级备份
*需要的空间小,恢复效率低
*某一项目执行的备份
*/
一、备份时数据库所在的状态?
备份时数据库处于open状态,这样数据库可以正常的工作。
二、备份的数据备份在什么地方?
备份在本地磁盘,这样备份简单,不需要第三方的工具。备份在磁带库需要第三方工具。
三、备份的存储空间?
可以用操作系统命令df -lh 命令查看空间,准备留给备份的空间大小。
四、什么方式备份?
用RMAN的增量备份(差量备份),这样备份和恢复都比较简单。所占用的空间比较小。
测试环境:
1 操作系统:Redhat Linux 5
[oracle@mzl proc]$ cat /proc/version
Linux version 2.6.18-8.el5 (brewbuilder@ls20-bc2-14.build.redhat.com) (gcc version 4.1.1 20070105 (Red Hat 4.1.1-52)) #1 SMP Fri Jan 26 14:15:21 EST 2007
2 数据库版本:Oracle10gR2 32位
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
3 数据库的名字:orcl
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
4 归档状态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Next log sequence to archive 24
Current log sequence 24
5 启用 FLASHBACK DATABASE 事件记录
这样在不慎drop表时可以快速恢复。
SQL> select dbid,name,flashback_on,current_scn from v$database;
DBID NAME FLASHBACK_ON CURRENT_SCN
---------- --------- ------------------ -----------
1172558471 ORCL NO 1307040
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> select dbid,name,flashback_on,current_scn from v$database;
DBID NAME FLASHBACK_ON CURRENT_SCN
---------- --------- ------------------ -----------
1172558471 ORCL YES 0
SQL> alter database open;
Database altered.
6 启动控制文件的自动备份
这样在备份时或者表空间变化时,数据库会自动备份控制文件。
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> show controlfile autobackup;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
7 数据库启动用spfile参数
这样备份时spfile会和控制文件一起备份。
查看是否用spfile参数文件:
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
VALUE的值为空,数据库是用pfile文件启动的,如果有值,则为spfile启动
创建spfile参数文件
SQL> create spfile from pfile;
File created.
会在$oracle_home/dbs/下生成spfileSID.ora
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1218868 bytes
Variable Size 88082124 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfileORCL.ora
8 使用rman的冗余策略,默认为3
RMAN> configure retention policy to redundancy 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
9 在/home/mzl/BackupDatabase/下创建backuplevel0.rcv和backuplevel1.rcv脚本
backuplevel0.rcv内容:
# script.:bakuplevel0.rcv
# creater:mengzhaoliang
# date:2008/6/10
# desc:backup level0 database datafile in archive with rman
# connect database
export ORACLE_SID=ORCL
export PATH=/u01/app/oracle/product/10.2.0/db_1/bin
rman target/ << EOF_RMAN
run{
allocate channel c1 type disk;
backup incremental level 0 tag 'db0' format '/home/mzl/BackupDatabase/rmanbackup/db0_%d_%T_%s' database include current controlfile;
sql 'alter system archive log current';
backup filesperset 3 format '/home/mzl/BackupDatabase/rmanbackup/arch0_%d_%T_%s' archivelog all delete input;
release channel c1;
}
# end
backuplevel1.rcv内容:
# script.:bakuplevel1.rcv
# creater:mengzhaoliang
# date:2008/6/10
# desc:backup level1 database datafile in archive with rman
# connect database
export ORACLE_SID=ORCL
export PATH=/u01/app/oracle/product/10.2.0/db_1/bin
rman target/ << EOF_RMAN
run{
allocate channel c1 type disk;
backup incremental level 1 tag 'db1' format '/home/mzl/BackupDatabase/rmanbackup/db1_%d_%T_%s' database include current controlfile;
sql 'alter system archive log current';
backup filesperset 3 format '/home/mzl/BackupDatabase/rmanbackup/arch1_%d_%T_%s' archivelog all delete input;
release channel c1;
}
# end
给这两个脚本授予执行的权限:
[oracle@mzl BackupDatabase]$ chmod +x *.rcv
(注意:要试运行脚本:./backuplevel1.rcv)
11 用crontab指定执行时间
[oracle@mzl BackupDatabase]$ crontab -e
crontab: installing new crontab
[oracle@mzl BackupDatabase]$ crontab -l
0 1 * * 0 /home/mzl/BackupDatabase/backuplevel0.rcv
0 1 * * 3 /home/mzl/BackupDatabase/backuplevel0.rcv
0 1 * * 1-2 /home/mzl/BackupDatabase/backuplevel1.rcv
0 1 * * 4-6 /home/mzl/BackupDatabase/backuplevel1.rcv
周日和周三凌晨1:00执行0级备份,周一、二、四、五、六凌晨1:00执行1级备份
内容:
0 1 * * 0 /home/mzl/BackupDatabase/backuplevel0.rcv
0 1 * * 3 /home/mzl/BackupDatabase/backuplevel0.rcv
0 1 * * 1-2 /home/mzl/BackupDatabase/backuplevel1.rcv
0 1 * * 4-6 /home/mzl/BackupDatabase/backuplevel1.rcv
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12778571/viewspace-343267/,如需转载,请注明出处,否则将追究法律责任。