amos_tl
- 浏览: 343480 次
- 性别:
- 来自: 长沙
-
文章分类
最新评论
-
努力吧飞翔：
...
[ExtJS] MVC应用架构示例 -
coolnight：
[Maven]Nexus 安装与配置 -
Kevin_jiang2011：
官网的文档写的不好。 简单的执行命令，又要重新下载一个ecli ...
jBPM5 入门 -
litterdeer：
好东西....
[ExtJS] MVC应用架构示例 -
basherone：
可以用，谢谢了
[ExtJS] MVC应用架构示例
Oracle11G 联机备份与恢复
-----------------------------------
1
设置产品数据库为归档模式
2
设置存储库, 不要将RMAN目标数据库用作存储库. 存储库至少需要125M空间来存放恢复目录条目.
注意:因为我是在个人PC上操作,所以没办法,只能在自己的数据库来创建存储库.
3
创建恢复目录
4
注册数据库
5
配置备份策略
6
备份数据库
6.1 完全备份
6.2
镜像备份
7
删除备份,包括有效的备份
8
手动备份控制文件和SPFILE
9
备份归档日志文件
10
增量备份
0级备份:
1级累积备份
1级差异备份
11
备份验证
12
恢复控制文件
13
恢复表空间
14
恢复数据文件
15
恢复数据库
16
编目备份
17
Linux定时调度 crontab
17.1
以 oracle 用户登录, 创建调度计划
su -l oracle
crontab -l
crontab -e
输入并保存:
0 3 * * 6 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
0 1 * * 0-2,4-6 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
0 1 * * 3 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
30 6,12,18,23 * * * /home/oracle/oracle/scripts/hot_arc_log_bak.sh
注意: 任务是在 oracle 用户下执行,需要可执行权限.
17.2
使调度生效:
以 root 用户登录
su root
cd /etc/init.d
./crond restart
查看进程,保证crond 进程只有一个,如果有多个,则会执行多次.
ps -ef | grep crond
root 22612 1 0 09:49 ? 00:00:00 crond
root 22676 22427 0 09:50 pts/0 00:00:00 grep crond
18. 自动备份脚本
hot_arc_log_bak.sh
hot_db_inc_bak.sh
19. 配置NFS
############### 配置服务端 ##################
# 安装
portmap | rpcbind,nfs,nfslock
yum whatprovides portmap
# 启动
service portmap restart;
service nfs restart; | service nfsserver start;
# 配置NFS服务端
vi /etc/exports
/var/nfs/rman/backup/74 192.168.0.11(rw,root_squash,sync)
/var/nfs/rman/backup/75 192.168.0.11(rw,root_squash,sync)
# NFS服务配置生效
# SUSE
chkconfig -a nfsserver
/etc/init.d/nfsserver restart
# RedHat
/etc/init.d/nfs restart
# 检查NFS启动
rpcinfo -p localhost | grep nfs
# 配置服务自动启动
setup
rpcbind,nfs,nfslock
############### 配置客户端 ##################
# 安装
portmap | rpcbind,nfs,nfslock
yum whatprovides portmap
# 启动
service portmap restart;
service nfs restart; | service nfsserver start;
# 挂载
cd /mnt
mkdir 74 75
chmod 777 74 75
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 -t nfs 192.168.0.123:/var/nfs/rman/backup/74 /mnt/74
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 -t nfs 192.168.0.123:/var/nfs/rman/backup/75 /mnt/75
unmount -l /mnt/74
unmount -l /mnt/75
# 开机启动时自动挂载
vi /etc/fstab
192.168.0.123:/var/nfs/rman/backup/75 /mnt/75 nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 0 0
192.168.0.123:/var/nfs/rman/backup/74 /mnt/74 nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 0 0
# 配置服务自动启动
setup
rpcbind,nfs,nfslock
20.恢复
RMAN 恢复:
恢复控制文件:
shutdown immediate;
startup nomount;
restore controlfile; | restore controlfile from autobackup;
recover database until cancel;
alter database mount;
alter database open; | alter database open resetlogs;
恢复数据库:
shutdown immediate;
startup mount;
restore database;
recover database; | recover database using backup controlfile;
alter database open; | alter database open resetlogs; // 不完全恢复
恢复数据文件:
shutdown immediate;
startup mount;
restore datafile 7;
recover datafile 7;
alter database open;
SPFile恢复:
shutdown immediate;
startup nomount;
set dbid=<DBID>;
restore spfile from autobackup;
shutdown immediate;
startup;
口令文件恢复:
重建口令文件:
orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=admin
windows下oracle默认的位置是$ORACLE_HOME/database目录，文件名格式是pwdSID.ora。
linux下oracle默认的位置是$ORACLE_HOME/dbs目录，文件名格式是orapwSID。
创建完后，数据库需要重启动，新的口令文件才能生效。
完全恢复:
sqlplus /nolog
conn sys/<pwd>@SID as sysdba;
shutdown immediate;
startup nomount;
rman target ／ nocatalog; // 默认且必须是SYSDBA用户
RMAN 中执行SQL为： sql ‘sql’;
// 从备份控制文件恢复
restore controlfile from autobackup;
alter database mount;
restore database;
//由于使用的是备份控制文件，oracle要求使用backup controlfile 的选项来执行恢复
recover database; | recover database using backup controlfile;
// 由于使用的是备份的控制文件，所以oracle要求用resetlogs的方式打开数据库.
alter database open; | alter database open resetlogs;
完全恢复完成.
21
多元复用控制文件
select name from v$controlfile;
SQL>alter system set control_files='d:\oracle\CONTROL01.CTL''c:\oracle\CONTROL01.CTL' scope=spfile;
SQL>shutdown immediate;
SQL>host copy d:\oracle\CONTROL01.CTL c:\oracle\CONTROL01.CTL;
SQL>startup
当控制文件介质失效时，用于恢复，多元复用控制文件应该保存在不同磁盘上.
22
多元复用重做日志文件
select group#,member from v$logfile;
ALTER DATABASE ADD LOGFILE MEMBER
'F:\ORABAK\REDO01.LOG' TO GROUP 1,
'F:\ORABAK\REDO02.LOG' TO GROUP 2,
'F:\ORABAK\REDO03.LOG' TO GROUP 3;
重做日志默认有三个组.
当日志组中有一个重做日志介质失效时，可以使用备用的重做日志.多元复用重做日志文件应该保存在不同磁盘上，提高可用性.
23
查询当前使用哪个日志组
select * from v$log; // STATUS: INACTIVE | CURRENT
24
重新日志丢失解决方案:
非当前重做日志，可直接清除：
alter database clear logfile group 1;
当前重做日志，恢复：
host copy F:\ORABAK\REDO03.LOG E:\bisoft\oracle\app\oradata\bisoft\REDO03.LOG
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
25
启动闪回配置
注意: Flashback不支持sys用户，system表空间下面的对象，也不能从回收站里拿到。故使用sys或是system用户登录时，show recyclebin为空。
授权:
grant select any table, flashback any table to x;
闪回数据库:
flashback database to scn 2323534;
验证:
alter database open ready only;
验证后：
shutdown immediate；
startup mount;
alter database open resetlogs;
resetlogs是将control file中的scn#与数据文件中的scn#同步，消除时间间隔。
闪回表:
show recyclebin;
flashback table t_name to before drop;
清除回收站:
purge recyclebin
闪回事务:
select xid from v$transaction;
Flash Version Query:
表test:
select versions_starttime, versions_endtime, versions_xid, versions_operation, id
from test versions
between timestamp minvalue and maxvalue
order by versions_starttime;
在上述查询中，列 versions_starttime、versions_endtime、versions_xid、versions_operation是伪列，还有一些伪列，如versions_startscn和versions_endscn显示了该时刻的系统更改号。列versions_xid显示了更改该行的事务标识符。
当然，除了分析以上所有的变更之外，可以根据需要指定时间段，如显示在2005-05-07时间在15:30到16:30之间test表的所有变更。
SQL>select id from test
versions between timestamp to_date(’2005-05-07 15:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(’2005-05-07 16:30:00’,’
yyyy-mm-dd hh24:mi:ss’)
26
查看当前的scn:
select current_scn from v$database;
最早可闪回的SCN:
select * from v$flashback_database_log;
27
rman target / nocatalog
sql 'alter system set NLS_DATE_LANGUAGE=AMERICAN scope=spfile';
sql 'alter system set NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS" scope=spfile';
shutdown immediate;
startup;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:/orabak/autobackup/ctl_%d_%T_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
show all;
RUN{
allocate channel ch1 device type disk format 'e:/orabak/74/db_level0_%T_%s_%p';
backup as compressed backupset duration 0:05 minimize load incremental level 0 database tag 'inc0' filesperset 10 database SKIP INACCESSIBLE;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/arch_level0_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch2;
allocate channel ch3 device type disk format 'e:/orabak/74/ctl_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch3;
}
RUN{
allocate channel ch1 device type disk format 'e:/orabak/74/db_level1_%T_%s_%p';
backup as compressed backupset duration 0:05 minimize load incremental level 1 database tag 'inc1' filesperset 10 duration 2:00 minimize load SKIP INACCESSIBLE ;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/arch_level1_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch2;
allocate channel ch3 device type disk format 'e:/orabak/74/ctl_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch3;
}
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
allocate channel for maintenance type disk;
crosscheck backup;
delete expired backup;
release channel;
exit
EOF
# 重建控制文件
alter database backup controlfile to trace as 'e:/orabak/rc.trc';
# 归档日志
RUN{
sql 'alter system archive log current';
allocate channel ch1 device type disk format 'e:/orabak/74/arch_level0_%T_%s_%p';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/ctl_level0_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch2;
}
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
allocate channel for maintenance type disk;
crosscheck backup;
delete expired backup;
release channel;
基于RMAN的备份优化
1.修改large_pool_size
alter system set large_pool_size=64M scope=spfile;
show parameter large_pool_size;
2.
CONFIGURE BACKUP OPTIMIZATION ON;
3.
NFS
rsize=32768,wsize=32768
4.
NFS
异步磁盘IO操作
async
5.
配置与磁盘个数相等的通道数
allocate channel
6.
配置与CPU个数一半的并行度
PARALLELISM 1;
7.
压缩备份集
backup as compressed backupset
8.
机器负载高时时可考虑主动延长备份时间来降低负载
duration 2:00 minimize load
9.
启用块跟踪
alter database enable block change tracking using file 'd:/app/product/11.2.0/dbhome_1/dbs/block_change_tracking.trk';
10.
合理的备份时间与备份策略
11.
-----------------------------------
1
设置产品数据库为归档模式
C:\>sqlplus /nolog SQL> conn sys/root:192.168.1.222:1521:ORALCE11G as sysdba Connected. SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 38 Next log sequence to archive 40 Current log sequence 40 SQL> alter database open;
2
设置存储库, 不要将RMAN目标数据库用作存储库. 存储库至少需要125M空间来存放恢复目录条目.
注意:因为我是在个人PC上操作,所以没办法,只能在自己的数据库来创建存储库.
C:\>sqlplus sys/root:192.168.1.222:1521:ORALCE11G as sysdba SQL> create tablespace RMAN datafile 'D:\APP\ORADATA\ORALCE11G\RMAN01.DBF' size 125M autoextend on next 50M maxsize 500M; SQL> CREATE USER rman IDENTIFIED BY rman; SQL> GRANT CONNECT, RESOURCE to rman; SQL> GRANT recovery_catalog_owner to rman; SQL> alter user rman default tablespace rman quota unlimited on rman; SQL> exit
3
创建恢复目录
C:\>rman catalog rman/rman@ORALCE11G RMAN> create catalog;
4
注册数据库
C:\>rman target sys/root:192.168.1.222:1521@ORALCE11G catalog rman/rman@ORALCE11G Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 20 00:31:29 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORALCE11 (DBID=3002332376) connected to recovery catalog database RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
5
配置备份策略
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 days; old RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS; new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 8 DAYS; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE BACKUP OPTIMIZATION ON; old RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION OFF; new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; old RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP OFF; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored starting full resync of recovery catalog full resync complete
6
备份数据库
6.1 完全备份
# 修改恢复目录
C:\>sqlplus sys/root@ORALCE11G as sysdba
...
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
C:\>rman target sys/root@ORALCE11G catalog rman/rman@ORALCE11G
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Apr 20 01:33:17 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORALCE11 (DBID=3002332376)
connected to recovery catalog database
RMAN> sql 'alter system archive log current';
sql statement: alter system archive log current
RMAN> backup as backupset database;
Starting backup at 20-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF
input datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBF
input datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBF
input datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 20-APR-12
channel ORA_DISK_1: finished piece 1 at 20-APR-12
piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNNDF_TAG20120420T021651_7S0ONNQG_.BKP tag=TAG20120420T02165
1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 20-APR-12
Starting Control File and SPFILE Autobackup at 20-APR-12
piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781064318_7S0OQZNJ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 20-APR-12
上面的操作:
1 切换归档日志,保证数据文件与控制文件所有事务已经提交.
2 备份数据文件,不包括临时表空间,因为它没有必要备份.
3 备份控制文件与参数文件
RMAN> list backup by backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
370 Full 1.23G DISK 00:01:39 20-APR-12
BP Key: 371 Status: AVAILABLE Compressed: NO Tag: TAG20120420T021651
Piece Name: D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNNDF_TAG20120420T021651_7S0ONNQG_.BKP
List of Datafiles in backup set 370
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF
2 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF
3 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF
4 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\USERS01.DBF
5 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF
7 Full 1964272 20-APR-12 D:\APP\ORADATA\ORALCE11G\RMAN01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
382 Full 9.36M DISK 00:00:02 20-APR-12
BP Key: 389 Status: AVAILABLE Compressed: NO Tag: TAG20120420T021838
Piece Name: D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781064318_7S0OQZNJ_.BKP
SPFILE Included: Modification time: 20-APR-12
SPFILE db_unique_name: ORALCE11G
Control File Included: Ckp SCN: 1964431 Ckp time: 20-APR-12
RMAN>
# 可以看到备份集中包括数据文件和SPFILE,其实还包括控制文件.
6.2
镜像备份
RMAN> backup as copy database; Starting backup at 20-APR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=138 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF output file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_SYSTEM_7S0PHG0H_.DBF tag=TAG20120420T023109 RECID=4 STAMP=781065117 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF output file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_SYSAUX_7S0PK5L2_.DBF tag=TAG20120420T023109 RECID=5 STAMP=781065157 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBF output file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_RMAN_7S0PL8TC_.DBF tag=TAG20120420T023109 RECID=6 STAMP=781065168 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF output file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_UNDOTBS1_7S0PLR3F_.DBF tag=TAG20120420T023109 RECID=7 STAMP=78106518 3 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBF output file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_USERS_7S0PM74W_.DBF tag=TAG20120420T023109 RECID=8 STAMP=781065197 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF output file name=D:\BACKUP\ORALCE11G\DATAFILE\O1_MF_EXAMPLE_7S0PMGCQ_.DBF tag=TAG20120420T023109 RECID=9 STAMP=781065204 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 20-APR-12 Starting Control File and SPFILE Autobackup at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065206_7S0PMQLK_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 20-APR-12 RMAN>
7
删除备份,包括有效的备份
list backup; delete backup; crosscheck backup; crosscheck archivelog all; delete expired backup; list backup;
8
手动备份控制文件和SPFILE
RMAN> backup current controlfile spfile; Starting backup at 20-APR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 20-APR-12 channel ORA_DISK_1: finished piece 1 at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NCNNF_TAG20120420T023551_7S0PR8SD_.BKP tag=TAG20120420T02355 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 20-APR-12 channel ORA_DISK_1: finished piece 1 at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNSNF_TAG20120420T023551_7S0PRB0H_.BKP tag=TAG20120420T02355 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 Finished backup at 20-APR-12 Starting Control File and SPFILE Autobackup at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065356_7S0PRF9R_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 20-APR-12 RMAN>
9
备份归档日志文件
backup archivelog all delete all input;
10
增量备份
0级备份:
backup incremental level 0 as compressed backupset database; RMAN> backup incremental level 0 as compressed backupset database; Starting backup at 20-APR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF input datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF input datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBF input datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF input datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBF input datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF channel ORA_DISK_1: starting piece 1 at 20-APR-12 channel ORA_DISK_1: finished piece 1 at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND0_TAG20120420T024026_7S0Q0VTC_.BKP tag=TAG20120420T02402 6 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35 Finished backup at 20-APR-12 Starting Control File and SPFILE Autobackup at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065723_7S0Q3WN0_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 20-APR-12
1级累积备份
backup as compressed backupset incremental level 1 cumulative database; MAN> backup as compressed backupset incremental level 1 cumulative database; tarting backup at 20-APR-12 sing channel ORA_DISK_1 hannel ORA_DISK_1: starting compressed incremental level 1 datafile backup set hannel ORA_DISK_1: specifying datafile(s) in backup set nput datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF nput datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF nput datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBF nput datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF nput datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBF nput datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF hannel ORA_DISK_1: starting piece 1 at 20-APR-12 hannel ORA_DISK_1: finished piece 1 at 20-APR-12 iece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024235_7S0Q4WFO_.BKP tag=TAG20120420T02423 comment=NONE hannel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 inished backup at 20-APR-12 tarting Control File and SPFILE Autobackup at 20-APR-12 iece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065802_7S0Q6CHN_.BKP comment=NONE inished Control File and SPFILE Autobackup at 20-APR-12
1级差异备份
backup as compressed backupset incremental level 1 database; Starting backup at 20-APR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=D:\APP\ORADATA\ORALCE11G\SYSTEM01.DBF input datafile file number=00002 name=D:\APP\ORADATA\ORALCE11G\SYSAUX01.DBF input datafile file number=00007 name=D:\APP\ORADATA\ORALCE11G\RMAN01.DBF input datafile file number=00003 name=D:\APP\ORADATA\ORALCE11G\UNDOTBS01.DBF input datafile file number=00004 name=D:\APP\ORADATA\ORALCE11G\USERS01.DBF input datafile file number=00005 name=D:\APP\ORADATA\ORALCE11G\EXAMPLE01.DBF channel ORA_DISK_1: starting piece 1 at 20-APR-12 channel ORA_DISK_1: finished piece 1 at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024421_7S0Q86B8_.BKP tag=TAG20120420T02442 1 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46 Finished backup at 20-APR-12 Starting Control File and SPFILE Autobackup at 20-APR-12 piece handle=D:\BACKUP\ORALCE11G\AUTOBACKUP\2012_04_20\O1_MF_S_781065908_7S0Q9O8S_.BKP comment=NONE Finished Control File and SPFILE Autobackup at 20-APR-12
11
备份验证
backup validate database archivelog all;
12
恢复控制文件
shutdown immediate; startup nomount; restore controlfile from autobackup; recover database until cancel; alter database open resetlogs;
13
恢复表空间
RMAN> sql 'alter tablespace users offline immediate'; sql statement: alter tablespace users offline immediate RMAN> restore tablespace users; Starting restore at 20-APR-12 using channel ORA_DISK_1 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 00004 to D:\APP\ORADATA\ORALCE11G\USERS01.DBF channel ORA_DISK_1: reading from backup piece D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND0_TAG20120420T024026_7S 0Q0VTC_.BKP channel ORA_DISK_1: piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND0_TAG20120420T024026_7S0Q0VTC_.BKP t ag=TAG20120420T024026 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 20-APR-12 RMAN> recover tablespace users; Starting recover at 20-APR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00004: D:\APP\ORADATA\ORALCE11G\USERS01.DBF channel ORA_DISK_1: reading from backup piece D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024235_7S 0Q4WFO_.BKP channel ORA_DISK_1: piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024235_7S0Q4WFO_.BKP t ag=TAG20120420T024235 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00004: D:\APP\ORADATA\ORALCE11G\USERS01.DBF channel ORA_DISK_1: reading from backup piece D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024421_7S 0Q86B8_.BKP channel ORA_DISK_1: piece handle=D:\BACKUP\ORALCE11G\BACKUPSET\2012_04_20\O1_MF_NNND1_TAG20120420T024421_7S0Q86B8_.BKP t ag=TAG20120420T024421 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 20-APR-12 RMAN> sql 'alter tablespace users online'; sql statement: alter tablespace users online restore tablespace users validate; RMAN>
14
恢复数据文件
restore datafile 7; recover datafile 7;
15
恢复数据库
restore database; recover database delete archivelog maxsize 2gb; alter database open;
16
编目备份
list backup; cp /USER* d:\backup\USER* catalog datafilecopy 'd:\backup\USERS*'; report schema
17
Linux定时调度 crontab
17.1
以 oracle 用户登录, 创建调度计划
su -l oracle
crontab -l
crontab -e
输入并保存:
0 3 * * 6 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
0 1 * * 0-2,4-6 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
0 1 * * 3 /home/oracle/oracle/scripts/hot_db_inc_bak.sh
30 6,12,18,23 * * * /home/oracle/oracle/scripts/hot_arc_log_bak.sh
注意: 任务是在 oracle 用户下执行,需要可执行权限.
17.2
使调度生效:
以 root 用户登录
su root
cd /etc/init.d
./crond restart
查看进程,保证crond 进程只有一个,如果有多个,则会执行多次.
ps -ef | grep crond
root 22612 1 0 09:49 ? 00:00:00 crond
root 22676 22427 0 09:50 pts/0 00:00:00 grep crond
18. 自动备份脚本
hot_arc_log_bak.sh
#!/bin/sh
##################################################
##
## hot_arc_log_bak.sh
##
##################################################
##################################################
# global define
##################################################
compatible_echo(){
case $SHELL in
*/bin/Bash)
alias echo="echo -e"
;;
esac
}
# export env
source $HOME/.bash_profile
#################################################
# config
#################################################
ORACLE_HOME=/opt/oracle/product/11g
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
RMAN=$ORACLE_HOME/bin/rman
export RMAN
#################################################
# backup archive log & control file & spfile
#################################################
$RMAN target / nocatalog log="$HOME/backup/rman_arc_`date '+%Y%m%d%H%M%S'`.log" <<EOF
run {
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY to recovery window of 7 days;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE disk TO 2;
allocate channel c1 type disk format '$HOME/backup/74/arc_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/arc_%d_DBID%I_%T_T%t_%U';
sql 'alter system archive log current' ;
backup archivelog all tag 'arch' delete all input;
release channel c1;
allocate channel c2 type disk format '$HOME/backup/74/ctl_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/ctl_%d_DBID%I_%T_T%t_%U';
backup current controlfile tag 'ctl';
release channel c2;
allocate channel c3 type disk format '$HOME/backup/74/spfile_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/spfile_%d_DBID%I_%T_T%t_%U';
backup spfile tag='spfile';
release channel c3;
}
list backup;
exit;
EOF
hot_db_inc_bak.sh
#!/bin/sh
##################################################
##
## hot_db_inc_bak.sh
##
##################################################
##################################################
# global define
##################################################
compatible_echo(){
case $SHELL in
*/bin/Bash)
alias echo="echo -e"
;;
esac
}
source $HOME/.bash_profile
##################################################
# config
##################################################
ORACLE_HOME=/opt/oracle/product/11g
export ORACLE_HOME
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
# backup policy
WEEK_DAILY=`date +%a`
case "$WEEK_DAILY" in
"Mon")
BAK_LEVEL=2
;;
"Tue")
BAK_LEVEL=2
;;
"Wed")
BAK_LEVEL=1
;;
"Thu")
BAK_LEVEL=2
;;
"Fri")
BAK_LEVEL=2
;;
"Sat")
BAK_LEVEL=2
;;
"Sun")
BAK_LEVEL=0
;;
"*")
BAK_LEVEL=error
;;
esac
export BAK_LEVEL=$BAK_LEVEL
RMAN=$ORACLE_HOME/bin/rman
export RMAN
##################################################
# backup datafile
##################################################
$RMAN target / nocatalog log="$HOME/backup/rman_db_`date '+%Y%m%d%H%M%S'`.log" <<EOF
run {
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY to recovery window of 7 days;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE disk TO 2;
allocate channel c1 type disk format '$HOME/backup/74/db_inc_lv"$BAK_LEVEL"_%d_DBID%I_%T_T%t_%U', '$HOME/backup/75/db_inc_lv"$BAK_LEVEL"_%d_DBID%I_%T_T%t_%U';
backup incremental level $BAK_LEVEL filesperset 2 tag 'db_inc_lv"$BAK_LEVEL"' database skip readonly;
release channel c1;
}
list backup summary;
report obsolete;
delete noprompt obsolete;
crosscheck backup;
delete noprompt expired backup;
list backup summary;
exit;
EOF
19. 配置NFS
############### 配置服务端 ##################
# 安装
portmap | rpcbind,nfs,nfslock
yum whatprovides portmap
# 启动
service portmap restart;
service nfs restart; | service nfsserver start;
# 配置NFS服务端
vi /etc/exports
/var/nfs/rman/backup/74 192.168.0.11(rw,root_squash,sync)
/var/nfs/rman/backup/75 192.168.0.11(rw,root_squash,sync)
# NFS服务配置生效
# SUSE
chkconfig -a nfsserver
/etc/init.d/nfsserver restart
# RedHat
/etc/init.d/nfs restart
# 检查NFS启动
rpcinfo -p localhost | grep nfs
# 配置服务自动启动
setup
rpcbind,nfs,nfslock
############### 配置客户端 ##################
# 安装
portmap | rpcbind,nfs,nfslock
yum whatprovides portmap
# 启动
service portmap restart;
service nfs restart; | service nfsserver start;
# 挂载
cd /mnt
mkdir 74 75
chmod 777 74 75
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 -t nfs 192.168.0.123:/var/nfs/rman/backup/74 /mnt/74
mount -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 -t nfs 192.168.0.123:/var/nfs/rman/backup/75 /mnt/75
unmount -l /mnt/74
unmount -l /mnt/75
# 开机启动时自动挂载
vi /etc/fstab
192.168.0.123:/var/nfs/rman/backup/75 /mnt/75 nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 0 0
192.168.0.123:/var/nfs/rman/backup/74 /mnt/74 nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0 0 0
# 配置服务自动启动
setup
rpcbind,nfs,nfslock
20.恢复
RMAN 恢复:
恢复控制文件:
shutdown immediate;
startup nomount;
restore controlfile; | restore controlfile from autobackup;
recover database until cancel;
alter database mount;
alter database open; | alter database open resetlogs;
恢复数据库:
shutdown immediate;
startup mount;
restore database;
recover database; | recover database using backup controlfile;
alter database open; | alter database open resetlogs; // 不完全恢复
恢复数据文件:
shutdown immediate;
startup mount;
restore datafile 7;
recover datafile 7;
alter database open;
SPFile恢复:
shutdown immediate;
startup nomount;
set dbid=<DBID>;
restore spfile from autobackup;
shutdown immediate;
startup;
口令文件恢复:
重建口令文件:
orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=admin
windows下oracle默认的位置是$ORACLE_HOME/database目录，文件名格式是pwdSID.ora。
linux下oracle默认的位置是$ORACLE_HOME/dbs目录，文件名格式是orapwSID。
创建完后，数据库需要重启动，新的口令文件才能生效。
完全恢复:
sqlplus /nolog
conn sys/<pwd>@SID as sysdba;
shutdown immediate;
startup nomount;
rman target ／ nocatalog; // 默认且必须是SYSDBA用户
RMAN 中执行SQL为： sql ‘sql’;
// 从备份控制文件恢复
restore controlfile from autobackup;
alter database mount;
restore database;
//由于使用的是备份控制文件，oracle要求使用backup controlfile 的选项来执行恢复
recover database; | recover database using backup controlfile;
// 由于使用的是备份的控制文件，所以oracle要求用resetlogs的方式打开数据库.
alter database open; | alter database open resetlogs;
完全恢复完成.
21
多元复用控制文件
select name from v$controlfile;
SQL>alter system set control_files='d:\oracle\CONTROL01.CTL''c:\oracle\CONTROL01.CTL' scope=spfile;
SQL>shutdown immediate;
SQL>host copy d:\oracle\CONTROL01.CTL c:\oracle\CONTROL01.CTL;
SQL>startup
当控制文件介质失效时，用于恢复，多元复用控制文件应该保存在不同磁盘上.
22
多元复用重做日志文件
select group#,member from v$logfile;
ALTER DATABASE ADD LOGFILE MEMBER
'F:\ORABAK\REDO01.LOG' TO GROUP 1,
'F:\ORABAK\REDO02.LOG' TO GROUP 2,
'F:\ORABAK\REDO03.LOG' TO GROUP 3;
重做日志默认有三个组.
当日志组中有一个重做日志介质失效时，可以使用备用的重做日志.多元复用重做日志文件应该保存在不同磁盘上，提高可用性.
23
查询当前使用哪个日志组
select * from v$log; // STATUS: INACTIVE | CURRENT
24
重新日志丢失解决方案:
非当前重做日志，可直接清除：
alter database clear logfile group 1;
当前重做日志，恢复：
host copy F:\ORABAK\REDO03.LOG E:\bisoft\oracle\app\oradata\bisoft\REDO03.LOG
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
25
启动闪回配置
SQL> startup mount; ORACLE 例程已经启动。 Total System Global Area 744910848 bytes Fixed Size 1374696 bytes Variable Size 318768664 bytes Database Buffers 419430400 bytes Redo Buffers 5337088 bytes 数据库装载完毕。 SQL> alter database flashback on; 数据库已更改。 SQL> alter database open; 数据库已更改。 SQL> select flashback_on from v$database; FLASHBACK_ON ------------------------------------ YES SQL> show parameter recyclebin; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ recyclebin string on SQL> 设置闪回区: SQL> show parameter db_recovery_file_dest; 设置闪回日志保留时间: SQL> alter system set db_flashback_retention_target=4320; #3days
注意: Flashback不支持sys用户，system表空间下面的对象，也不能从回收站里拿到。故使用sys或是system用户登录时，show recyclebin为空。
授权:
grant select any table, flashback any table to x;
闪回数据库:
flashback database to scn 2323534;
验证:
alter database open ready only;
验证后：
shutdown immediate；
startup mount;
alter database open resetlogs;
resetlogs是将control file中的scn#与数据文件中的scn#同步，消除时间间隔。
闪回表:
show recyclebin;
flashback table t_name to before drop;
清除回收站:
purge recyclebin
闪回事务:
select xid from v$transaction;
Flash Version Query:
表test:
select versions_starttime, versions_endtime, versions_xid, versions_operation, id
from test versions
between timestamp minvalue and maxvalue
order by versions_starttime;
在上述查询中，列 versions_starttime、versions_endtime、versions_xid、versions_operation是伪列，还有一些伪列，如versions_startscn和versions_endscn显示了该时刻的系统更改号。列versions_xid显示了更改该行的事务标识符。
当然，除了分析以上所有的变更之外，可以根据需要指定时间段，如显示在2005-05-07时间在15:30到16:30之间test表的所有变更。
SQL>select id from test
versions between timestamp to_date(’2005-05-07 15:30:00’,’yyyy-mm-dd hh24:mi:ss’) and to_date(’2005-05-07 16:30:00’,’
yyyy-mm-dd hh24:mi:ss’)
26
查看当前的scn:
select current_scn from v$database;
最早可闪回的SCN:
select * from v$flashback_database_log;
27
rman target / nocatalog
sql 'alter system set NLS_DATE_LANGUAGE=AMERICAN scope=spfile';
sql 'alter system set NLS_DATE_FORMAT="YYYY-MM-DD:HH24:MI:SS" scope=spfile';
shutdown immediate;
startup;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:/orabak/autobackup/ctl_%d_%T_%F';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
show all;
RUN{
allocate channel ch1 device type disk format 'e:/orabak/74/db_level0_%T_%s_%p';
backup as compressed backupset duration 0:05 minimize load incremental level 0 database tag 'inc0' filesperset 10 database SKIP INACCESSIBLE;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/arch_level0_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch2;
allocate channel ch3 device type disk format 'e:/orabak/74/ctl_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch3;
}
RUN{
allocate channel ch1 device type disk format 'e:/orabak/74/db_level1_%T_%s_%p';
backup as compressed backupset duration 0:05 minimize load incremental level 1 database tag 'inc1' filesperset 10 duration 2:00 minimize load SKIP INACCESSIBLE ;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/arch_level1_%T_%s_%p';
sql 'alter system archive log current';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch2;
allocate channel ch3 device type disk format 'e:/orabak/74/ctl_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch3;
}
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
allocate channel for maintenance type disk;
crosscheck backup;
delete expired backup;
release channel;
exit
EOF
# 重建控制文件
alter database backup controlfile to trace as 'e:/orabak/rc.trc';
# 归档日志
RUN{
sql 'alter system archive log current';
allocate channel ch1 device type disk format 'e:/orabak/74/arch_level0_%T_%s_%p';
backup archivelog all tag 'arch' filesperset 20 delete all input;
release channel ch1;
allocate channel ch2 device type disk format 'e:/orabak/74/ctl_level0_%T_%s_%p';
backup current controlfile tag 'ctl' spfile tag 'spfile';
release channel ch2;
}
allocate channel for maintenance type disk;
change archivelog all crosscheck;
release channel;
allocate channel for maintenance type disk;
crosscheck backup;
delete expired backup;
release channel;
基于RMAN的备份优化
1.修改large_pool_size
alter system set large_pool_size=64M scope=spfile;
show parameter large_pool_size;
2.
CONFIGURE BACKUP OPTIMIZATION ON;
3.
NFS
rsize=32768,wsize=32768
4.
NFS
异步磁盘IO操作
async
5.
配置与磁盘个数相等的通道数
allocate channel
6.
配置与CPU个数一半的并行度
PARALLELISM 1;
7.
压缩备份集
backup as compressed backupset
8.
机器负载高时时可考虑主动延长备份时间来降低负载
duration 2:00 minimize load
9.
启用块跟踪
alter database enable block change tracking using file 'd:/app/product/11.2.0/dbhome_1/dbs/block_change_tracking.trk';
10.
合理的备份时间与备份策略
11.
0
顶
顶
0
踩
踩
分享到：
发表评论
-
oracle 存储过程基础
2013-10-17 03:48 1094oracle 存储过程基础 ----------------- ... -
[oracle]LogMiner的使用
2013-09-29 22:42 1066LogMiner -- 01 功能: 版本: 8i ... -
Oracle数据备份与恢复
2011-12-27 23:02 1248-- 创建用户 create user biso ... -
Oracle 基础
2011-09-09 11:23 1144Oracle 基础 －－－－－－－－－－－－－－－－ 1. ... -
Schema Export
2011-08-25 18:26 1103build.xml <?xml version ... -
MySQL JDBC Connector/J 5.x API
2011-08-23 23:49 1350MySql.java package cn.bisoft. ... -
数据库死锁问题
2011-08-04 02:52 1159数据库死锁: 锁分类: S 共享锁 U 更新锁 X 排它锁 ... -
HSQLDB 使用
2011-02-19 23:46 19241. 服务器模式 应用场景: 数据库服务器 应用步骤: ... -
Oracle 连接
2011-01-20 03:04 1204Oracle 连接 1. 连接 -- 查询当前用户 ... -
Oracle 存储过程示例
2011-01-20 02:59 1076Oracle 存储过程示例 1. S ... -
Oralce 锁机制
2011-01-14 19:09 1336Oracle DML 锁类型 1. exclusive 排它锁 ... -
ORACLE 1722 错误案例分析
2010-08-22 02:55 2356-- ORACLE 1722 错误案例分析 -- 1 ... -
ORACLE 查询Oracle错误码
2010-08-06 05:31 21551.创建一个表存储错误码 SQL> create ... -
[PDF]PLSQL Developer v7.0 用户指南中文版
2010-08-01 22:51 1427使用PLSQL Developer 工具必不能少的一本 ... -
Oracle 11g 开发指南学习笔记一
2010-06-21 00:59 1485基本概念: 1. 关系数据 ... -
hsqldb 源码剖析
2010-03-06 01:16 2003hsqldb,一个只有700多k的小型数据库. 内嵌支持JD ...
相关推荐
Oracle 11g R2 的 RMAN (Recovery Manager) 是 Oracle 数据库管理系统中的一个关键工具，主要用于数据库的备份和恢复。RMAN 提供了一种高效且灵活的方式来管理和保护数据库，确保在数据丢失或系统故障时能够快速恢复...
Oracle11g提供了一套完整的恢复机制，包括联机日志（Redo Logs）、归档日志以及控制文件等。在数据恢复过程中，通常需要以下步骤： 1. **创建备份**：在任何恢复操作之前，确保有最新的数据库备份至关重要。这可以...
Oracle 10g 备份和恢复是...总之，Oracle 10g 的备份和恢复涉及到多个层面和策略，包括RMAN的使用、不同类型的备份、恢复操作以及相关的最佳实践。全面理解和熟练掌握这些知识点，对于保障企业的数据安全至关重要。
RMAN需要在合理配置、充分利用重做日志的前提下，支持联机备份，并支持指定时间点恢复。 重做日志 重做日志（简称日志，包含联机重做日志和归档重做日志）是Oracle数据库的重要组成部分。联机重做日志（简称联机...
五、备份与恢复 Oracle 10g提供了全面的备份和恢复策略，包括逻辑备份（如导出导入）、物理备份和增量备份。重做日志系统记录了所有数据库更改，用于在发生故障时恢复数据。RMAN（恢复管理器）是Oracle 10g的主要...
Oracle 11g 的 Recovery Manager (RMAN) 是一个强大的工具，专为数据库备份和恢复设计。本手册详细阐述了 RMAN 的语法和用法，是数据库管理员进行数据保护和故障恢复的重要参考资料。以下是 RMAN 的核心知识点： 一...
备份恢复则包括物理备份、逻辑备份和RMAN（Recovery Manager）的使用。 3. **SQL与PL/SQL**：详细解释了SQL语言在Oracle 10g中的使用，包括DML（数据操作语言）、DDL（数据定义语言）和DCL（数据控制语言）；PL/SQL...
5. **备份与恢复**：涵盖Oracle的备份策略、RMAN（恢复管理器）的使用，以及如何进行数据库的完整恢复和增量恢复。 6. **性能优化**：包括SQL优化、数据库调优工具的使用、索引优化、分区技术，以及Explain Plan的...
Oracle11g数据库是Oracle公司推出的数据库管理系统，其备份与恢复功能对于数据安全至关重要。本文将深入探讨Oracle11g的备份与恢复策略，以及如何有效地执行这些操作。 首先，Oracle11g提供了多种备份类型，包括...
RMAN（Recovery Manager）是 Oracle 提供的一种备份和恢复工具，能够对数据库进行备份和恢复。 RMAN 备份可以分为两种类型：完整备份和增量备份。完整备份是对整个数据库的备份，包括所有数据文件、控制文件、归档...
### Oracle备份与恢复知识点详解 #### 一、备份与恢复概述 ##### 1.1 备份与恢复：基本概念 - **备份基本概念** 备份是指创建数据库重要组成部分（如控制文件和数据文件）的一个或多个副本。它是确保数据安全的...
### Oracle RMAN (备份与恢复管理器)详尽解析 #### 一、RMAN概述 RMAN（Recovery Manager），即恢复管理器，是Oracle数据库自带的一款强大的备份和恢复工具。RMAN的设计目的是为了简化Oracle数据库管理员的工作...
Oracle数据库的备份与恢复是确保数据安全的关键环节。在数据库管理中，由于各种原因，如硬件故障、软件错误、人为操作失误等，都可能导致数据库中的数据丢失。因此，有必要实施有效的备份策略，以防止不可预见的情况...
RMAN（Recovery Manager）是Oracle数据库自带的一种强大的备份与恢复工具，它能够帮助DBA实现对Oracle数据库的有效管理和维护，尤其适用于大型数据库环境下的高效数据保护。 #### 准备工作 为了有效地使用RMAN进行...
Oracle数据库的备份与恢复是确保数据安全性和可用性的重要环节，尤其在企业级应用中，这一环节至关重要。本文将深入探讨Oracle数据库的备份策略、常用命令以及实际操作案例。 首先，Oracle数据库支持多种备份方式，...
《基于RMAN技术的ORACLE数据库备份恢复研究》这篇论文主要探讨了Oracle数据库中使用RMAN（Recovery Manager）进行备份和恢复的技术及其重要性。RMAN是Oracle公司自8i版本开始引入的一种高效能的数据库管理工具，它...
Oracle数据库的备份与恢复是一个复杂但至关重要的过程，涵盖了多种工具和技术，包括EXP/IMP、SQL*LOADER、OS备份以及RMAN。每个工具都有其特定的应用场景和优势，理解它们的特性和合理运用是确保数据库安全稳定的...
RMAN（Recovery Manager）是Oracle数据库管理系统中的一个重要组件，主要负责数据库的备份与恢复操作。在Oracle数据库的日常管理中，确保数据的安全性和可恢复性是至关重要的，而RMAN为此提供了强大的工具和策略。 ...