一丶备份和恢复概要
1丶高可用性
(1)HA（High Availability，HA）
(2)RAC(Real Application Clusters)
(3)DG(Data Guard)
(4)Oracle Replication
(5)RAID和镜像驱动器
2丶备份和恢复。
(1)收集备份与恢复策略需求
(2)精心设计备份与恢复计划
二丶Oracle备份
1丶ORACLE各种备份
(1)类型
①物理备份
②逻辑备份
(2)数据库状态
①联机备份(热)和脱机备份(冷)
②不一致性备份和一致性备份
(3)备份规模
①完全备份:归档模式丶非模式模式
②表空间备份
③数据文件备份
④控制文件备份
⑤归档日志文件备份
三 丶ORACLE恢复
(1)错误的类型
①用户误操作导致错误
②系统进程导致的错误
③实例导致错误
④存储介质导致错误
(2)恢复类型
①实例恢复:1缓存恢复(前滚),2事务恢复(后滚)
②介质恢复(数据文件或数据块)
(3)恢复方式
①完全恢复
②不完全恢复
(4)恢复操作
①restore --还原
②recover --恢复
(5)resetlogs：alter database open resetlogs;
incarnation:化身号
四丶常见备份与恢复技术
(1)用户管理的备份和恢复(User-Managed Backup&Recovery)
(2)RMAN备份和恢复
(3)闪回（flashback）
①闪回查询(Flashback Query):通过查询UNDO段,能够重现操作之前的数据
②闪回表(Flashback Table):通过recycle Bin回收drop的表
③闪回数据库(Flashback Database)
(4)逻辑导入/导出（Export/Import和Data Pump）
(5)数据卫士（Data Guard）
五丶用户管理备份
(1)主要数据:oradata/* dbs/* arch/*
相关视图:v$datafile,v$controlfile,v$logfile,v$archived_log,v$tablespace,v$tempfile
(2)冷备:关闭-->copy-->启动
select name from v$datafile;
select name from v$controlfile;
--select member from v$logfile;
--select name from v$tempfile;
shutdown immediate;
df -hl
mkdir /backup
chown -R oracle:oinstall /backup
chmod -R 755 /backup
su - oracle
cd /backup
mkdir cold
mkdir hot
cd /backup/cold
cp -rf /u01/app/oracle/oradata/ocp/* .
startup
(3)热备:
SQL> col file_name for a50;
SQL> select tablespace_name, file_name from dba_data_files;
SQL> alter tablespace tp1 begin backup;
SQL> !cp /oradata/bxdb/user01.dbf /backup/
SQL> alter tablespace users end backup;
SQL> alter system switch logfile;
--dd if=/u01/app/oracle/oradata/ocp/tp1.dbf skip=205 bs=8192 count=2|strings
--alter system dump datafile block;
*批量操作脚本:(v$backup,dba_data_files,dba_tablespaces)
select
\'alter tablespace \'||tablespace_name|| \' begin backup;\' ||chr(10)||
\'host cp \'||file_name||\' /backup\' ||chr(10)||
\'alter tablespace \'||tablespace_name|| \' end backup;\'
from dba_data_files order by tablespace_name;
begin
for i in 1 .. 10000 loop
insert into t1 values(i,\'gyj\'||i);
commit;
end loop;
end;
/
(4)控制文件备份:
alter database backup controlfile to \'/backup/control.bin\';
alter database backup controlfile to trace as \'/backup/control.ctl\';
(5)初始化文件备份:create pfile=\'/backup/pfile.ora\' from spfile;
(6)口令文件备份:orapwd file=$ORACLE_HOME/dbs/orapw
password=oracle;
六丶RMAN配置与备份
1.RMAN连接
(1)连接本地数据库
rman target /
(2)连接远程数据库
rman sys/oracle@bxdb
(3)辅助连接
rman target sys/oracle@ocp auxiliary sys/oracle@ocm
duplicate target database for standby dorecover nofilenamecheck;
(4)连接恢复目录(ppt-les_03_catalog)
create tablespace rc_data datafile \'/u01/app/oracle/oradata/ocp/rc_data01.dbf\' size 100m;
create user rc_admin identified by rc_admin default tablespace rc_data quota unlimited on rc_data;
grant connect,resource,recovery_catalog_owner to rc_admin;
rman catalog rc_admin/rc_admin
create catalog tablespace rc_data;
exit
rman target / catalog rc_admin/rc_admin
register database;
*******************************************************
虚拟专用目录：
create user v_user1 identified by v_user1 default tablespace tp1 temporary tablespace temp quota unlimited on tp1;
grant recovery_catalog_owner to v_user1;
[oracle@ocm ~]$ rman catalog rc_admin/rc_admin
grant catalog for database ocm to v_user1;
[oracle@ocm ~]$ rman catalog v_user1/v_user1
create virtual catalog;
(5)退出rman
exit
2.RMAN基本操作
(1)启动关闭数据库
rman target /
shutdown immeidate
startup
(2)执行sql语句
sql \'alter system checkpoint\';
sql"create tablespace tp3 datafile \'\'/u01/app/oracle/oradata/ocp/tp3.dbf\'\' size 10M";
(3) RMAN备份类型
①镜像复制(Image Copies)
②备份集(Backup sets)
(4)RMAN命令执行方式
①单个命令 backup database;
②批处理 run{ ...
};
③运行脚本
*命令行方式调用操作系统中保存的脚本
rman target / @script.rman (或 cmdfile=script.rman)
*在rman中执行操作系统保存的脚本文件
@scrip.rman
*运行储储在CATALOG中的脚本(需要首先为RMAN创建恢复目录)
create script full_db{
backup format=\'/back/rman/full_%U\'
database include current controlfile
plus archivelog;
sql \'alter system archive log current\';
}
run{execute script full_db;}
rman target sys/oracle@oca catalog rc_admin/rc_admin@oca script full_db
list script names;
print script full_db;
replace script full_db{
backup format=\'/backup/rman/full_%U\'
database;
}
delete script full_db;
resync catalog;
unregister database;
upgrade catalog;
drop catalog;
--查恢复目录 sqlplus rc_admin/rc_admin
select dbid,name,resetlogs_change# from rc_database;
select ts#,name,creation_change# from rc_tablespace;
select file#,name,bytes from rc_datafile;
select script_name from rc_stored_script;
select line,text from rc_stored_script_line;
3. RMAN备份
(1)备份数据库
backup database;
backup database format \'/backup/rman_full_%U\';
list backup of database;
(2)备份一个数据文件
report schema
select file#,name from v$datafile;
backup datafile 4 format \'/backup/md_%U\';
list backup of datafile 4;
(3)备份表空间
select tablespace_name,contents from dba_tablespaces;
backup tablespace trans format \'/backup/trans_%U\';
list backup of tablespace trans;
(4)备份控件文件(4种方法)
①自动备份:configure controlfile autobackup on;
②backup current controlfile;
③backup datafile 4 include current controlfile;
④sql "alter database backup controlfile to \'\'/backup/control/back_controlfile.bin\'\'";
list backup of controlfile;
(5)备份参数文件(2种方法)
①自动备份:configure controlfile autobackup on;
②backup spfile format \'/backup/spfile.ora\';
(6)备份归档文件
①backup archivelog all format \'/backup/arch_%U\';
②backup archivelog sequence between 85 and 89 delete all input;
③backup archivelog from time "sysdate - 14" until time "sysdate - 7 ";
④backup database plus archivelog format \'/back/rman/full_%U\' delete all input;
list backup of archivelog all;
(7)备份备份集
①backup backupset 1 format \'/backup/backupset1_%U\';
②backup backupset all;
(8)镜像拷贝
①只能拷贝到磁盘上
②恢复(restore)只要switch,速度快
③backup copy 或直接用copy
copy datafile 5 to \'/u01/app/oracle/oradata/bxdb/tp5.dbf \';
--copy datafile 5 to \'+dg/oradata/bxdb/tp5.dbf \';
（9）多条命令
run {allocate channel c1 type disk;
maxpieces size=2G;
backup format \'/backup/backup_%u\' filesperset 3 database;
}
run {allocate channel d1 type disk;
format \'/backup/all_%u\' maxpieces=100m;
backup database filesperset=3;
}
(9)增量备份
①差异增量备份(默认)
vi increment0.sql
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export ORACLE_SID=ocp
#export NLS_LANG="SIMPLIFIED CHINESE_CHINA".ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH
rman target / <
backup incremental level =0 database;
EXIT
EOF
vi increment1.sql
rman target / <
backup incremental level =1 database;
EOF
vi increment2.sql
rman target / <
backup incremental level =2 database;
EOF
crontab -e
0 2 * * 0 /home/oracle/increment0.sql > /tmp/increment0.log 2>&1
0 2 * * 1 /home/oracle/increment2.sql > /tmp/increment1.log 2>&1
0 2 * * 2 /home/oracle/increment2.sql > /tmp/increment2.log 2>&1
0 2 * * 3 /home/oracle/increment1.sql > /tmp/increment3.log 2>&1
0 2 * * 4 /home/oracle/increment2.sql > /tmp/increment4.log 2>&1
0 2 * * 5 /home/oracle/increment2.sql > /tmp/increment5.log 2>&1
0 2 * * 6 /home/oracle/increment2.sql > /tmp/increment6.log 2>&1
②累积增量备份
七丶RMAN常用命令
5.1 显示默认配置 --show命令
show all;
show controlfile autobackup;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \'/backup/rman/full_%U\';
set nocfau;--关闭自动备份控制文件和参数文件
5.2 列出备份信息 --list命令
list backup;
list backup of controlfile;
list backup of datafile \'/oradata/bxdb/users.dbf\';
list backup of 5;
list backup of archivelog all;
list copy of tablespace \'SYSTEM\';
list device type disk backup;
list archivelog all;
list expired backup;
5.3 删除备份 --delete 命令
delete obsolete; --备份策略
delete expired backup; --物理路径
delete expired copy;
delete expired archivelog all;
delete backupset 5;
delete backuppiece \'/backup/user_demo_20.bak\';
delete backup;
delete datafile copy \'/oradata/bxdb/users.dbf\';
delete copy;
backup archivelog all delete input;
5.4 报表显示 --report命令
report schema;
report need backup;
report need backup tablespace system;
report obsolete;
5.5 执行检查 --crosscheck命令
crosscheck archivelog all;
crosscheck backup;
5.6 配置configure
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM:
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!
答案在：http://blog.csdn.net/guoyjoe/article/details/8624392
DSI&Core Search（QQ群）:127149411
