amos_tl
- 浏览: 334235 次
- 性别:
- 来自: 长沙
-
文章分类
最新评论
-
努力吧飞翔：
...
[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 1038oracle 存储过程基础 ----------------- ... -
[oracle]LogMiner的使用
2013-09-29 22:42 1018LogMiner -- 01 功能: 版本: 8i ... -
Oracle数据备份与恢复
2011-12-27 23:02 1176-- 创建用户 create user biso ... -
Oracle 基础
2011-09-09 11:23 1048Oracle 基础 －－－－－－－－－－－－－－－－ 1. ... -
Schema Export
2011-08-25 18:26 1044build.xml <?xml version ... -
MySQL JDBC Connector/J 5.x API
2011-08-23 23:49 1295MySql.java package cn.bisoft. ... -
数据库死锁问题
2011-08-04 02:52 1127数据库死锁: 锁分类: S 共享锁 U 更新锁 X 排它锁 ... -
HSQLDB 使用
2011-02-19 23:46 18781. 服务器模式 应用场景: 数据库服务器 应用步骤: ... -
Oracle 连接
2011-01-20 03:04 1160Oracle 连接 1. 连接 -- 查询当前用户 ... -
Oracle 存储过程示例
2011-01-20 02:59 1035Oracle 存储过程示例 1. S ... -
Oralce 锁机制
2011-01-14 19:09 1293Oracle DML 锁类型 1. exclusive 排它锁 ... -
ORACLE 1722 错误案例分析
2010-08-22 02:55 2269-- ORACLE 1722 错误案例分析 -- 1 ... -
ORACLE 查询Oracle错误码
2010-08-06 05:31 21041.创建一个表存储错误码 SQL> create ... -
[PDF]PLSQL Developer v7.0 用户指南中文版
2010-08-01 22:51 1382使用PLSQL Developer 工具必不能少的一本 ... -
Oracle 11g 开发指南学习笔记一
2010-06-21 00:59 1438基本概念: 1. 关系数据 ... -
hsqldb 源码剖析
2010-03-06 01:16 1961hsqldb,一个只有700多k的小型数据库. 内嵌支持JD ...
相关推荐
《Oracle11g从入门到精通》面向数据库管理人员和数据库开发人员，从实际角度出发，系统地介绍了数据库和Oracle的相关概念和原理、Oracle的数据库管理（如安装与启动，用户权限、备份与恢复等），以及Oracle的应用...
《Oracle11g从入门到精通》面向数据库管理人员和数据库开发人员，从实际角度出发，系统地介绍了数据库和Oracle的相关概念和原理、Oracle的数据库管理（如安装与启动，用户权限、备份与恢复等），以及Oracle的应用...
第7章 备份与恢复 177 7.1 Oracle备份和恢复基本原理 178 7.1.1 从何处着手 178 7.1.2 备份的体系结构 179 7.1.3 Oracle 二进制文件 179 7.1.4 参数文件 179 7.1.5 控制文件 180 7.1.6 重做日志 180 7.1.7 ...
10.5 联机备份与恢复 10.5.1 归档日志模式的设置 10.5.2 创建恢复目录所用的表空间 10.5.3 创建RMAN用户并授权 10.5.4 创建恢复目录 10.5.5 注册目标数据库 10.5.6 使用RMAN程序进行备份 10.5.7 使用RMAN程序进行...
13.5.1联机Redo日志损坏与恢复 13.5.2数据文件脱机与恢复 13.5.3表空间脱机与恢复 13.6本章小结 第14章 RAC安全性 14.1数据访问安全性 14.1.1 Oracle安全性解决方案 14.1.2 VPD和OLS策略 14.2 Database Vault...
10.5 联机备份与恢复 10.5.1 归档日志模式的设置 10.5.2 创建恢复目录所用的表空间 10.5.3 创建RMAN用户并授权 10.5.4 创建恢复目录 10.5.5 注册目标数据库 10.5.6 使用RMAN程序进行备份 10.5.7 使用RMAN程序进行...
OCPOCA认证考试指南全册：Oracle Database 11g（1Z0-051，1Z0-052，1Z0-053） 共2部分：此为第002部分 基本信息 原书名： OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052...
而RMAN还原与恢复是实现数据库完整性、可靠性必不可少的手段之一。还原简言之即是将所需的文件从备份中复制到原来文件所在的路径。还原通常可以包括数据库、表空间、数据文件级别的还原。通常还原后的内容会滞后于...
探究 Data Recovery Advisor，对相同文件进行并行备份，并且创建和管理虚拟目录。 安全性 了解表空间加密、区分大小写的口令、数据屏蔽以及其他特性。 自动存储管理 了解新的 SYSASM 角色、可变的区大小以及其他...
五、RMAN 备份与恢复基础 11 （一）备份与恢复基本术语 11 （二）RMAN 基本架构和命令 12 六、RMAN 的备份 13 （一）RMAN 脚本基础 13 （二）RMAN 的冷备份 13 （三）RMAN 的热备份 14 （四）增量备份 14 （五）镜像...
1.数据库在非归档模式下的备份 SQL> archive log list; 数据库日志模式 非存档模式 自动存档 禁用 存档终点 USE_DB_RECOVERY_FILE_DEST 早的联机日志序列 3244 当前日志序列 3246 RMAN> run ...
OCPOCA认证考试指南全册：Oracle Database 11g（1Z0-051，1Z0-052，1Z0-053） 共2部分：此为第001部分 基本信息 原书名： OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052...
一般物理备份/恢复都采用Oracle RMAN工具来进行。 下面是“逻辑备份”与“物理备份”在数据库故障时的恢复比较： 1. Oracle逻辑错误造成无法启动 逻辑恢复: 重新create database及各tablespace，import。可恢复...
为了保证业务的顺利进行和拓展，必须建立一个高可靠性、可用性、可扩展性的数据存储系统，每天的数据库联机热备份使用NetBunker Oracle备份代理，由Oracle备份代理调RMAN（Oracle恢复服务器）进行数据库、表空间等...
六、ORACLE逻辑备份的SH文件 完全备份的SH文件：exp_comp.sh rq=` date +"%m%d" ` su - oracle -c "exp system/manager full=y inctype=complete file=/oracle/export/db_comp$rq.dmp" 累计备份的SH文件：exp_...
第三部分、备份与恢复 28 [Q]如何开启/关闭归档 28 [Q]怎样设置定时归档 29 [Q]不同版本怎么导出/导入 29 [Q]不同的字符集之前怎么导数据 29 [Q]怎么样备份控制文件 29 [Q]控制文件损坏如何恢复 29 [Q]怎么样热备份...
16. Oralce数据库在进行物理备份有联机备份和脱机备份两种方式可供选择。 . 17. 从存储结构的角度来说，Oracle数据库可分为物理结构和逻辑结构。 18. 表空间是Oracle数据库中数据的逻辑组织，每个数据库至少有一个...
Oracle9i初始化参数中文说明 Blank_trimming: 说明: 如果值为TRUE, 即使源长度比目标长度 (SQL92 兼容) 更长, 也允许分配数据。 值范围: TRUE | FALSE 默认值: FALSE serializable: 说明: 确定查询是否获取表级...