Oracle备份恢复中热备份恢复及异机恢复的原理是什么
Oracle备份恢复中热备份恢复及异机恢复的原理是什么,针对这个问题,这篇文章详细介绍了相对应的分析和解答,希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
Oracle备份恢复之热备份恢复及异机恢复
原理:
数据库必须运行在归档模式下,否则备份没有意义。备份前冻结块头,使scn号不变化,然后cp物理文件,最后解冻块头。此过程dml语句可以正常执行,动作被写在日志文件里面,当解冻scn号后,日志文件中内容会自动写入数据文件。
流程:
1、全库备份:
1)alter database begin backup;
2)cp物理文件
3)alter database end backup;
脚本:
spool /u01/oracle/jiaoben/bf2.sql
select 'ho cp '||name||' /u01/oracle/rebei/' from v$datafile;
spool off
alter database backup controlfile to '/u01/oracle/rebei/control.ctl';
create pfile='/u01/oracle/rebei/initorcl.ora' from spfile;
alter database begin backup;
@/u01/oracle/jiaoben/bf2.sql
alter database end backup;
2、表空间级备份:
1)alter tablespace <tablespace_name> begin backup;
2)cp表空间下数据文件
3)alter tablespace <tablespace_name> end backup;
脚本:
spool /u01/oracle/home/thot.sql
select 'alter tablespace '||tablespace_name||' begin backup;'||chr(10)||'ho cp '||file_name||' /u01/oracle/home/'||chr(10)||'alter tablespace '||tablespace_name||' end backup;'||chr(10) from dba_data_files;
spool off
start /u01/oracle/home/thot.sql
ho rm /u01/oracle/home/thot.sql
alter database backup controlfile to '/u01/oracle/home/control.ctl';
create pfile='/u01/oracle/home/initorcl.ora' from spfile;
恢复:能脱机的数据文件即脱机恢复,不能脱机的启动到mount阶段恢复。
1、users表空间丢失还原方法:
1)将数据文件脱机
2)物理cp备份文件
3)recover database
4)将数据文件联机
2、system表空间丢失还原方法:
1)启动数据库到mount状态
2)物理cp备份文件
3)recover database
4)alter database open;
3、所有数据文件丢失:
1)shutdown abort
2)startup mount
3)cp所有备份物理文件到数据文件目录
4)recover database
5)alter database open
4、日志文件丢失
1)shutdown immediate
2)startup(自动挂住)
3)recover database until cancel
4)alter database open resetlogs
5、控制文件丢失恢复
1)shutdown abort
2)startup(自动挂住)
3)将control文件cp回原位置
4)alter database mount
5)recover database using backup controlfile; > auto
6)recover database using backup controlfile; > 分别输入在线日志路径,回车
7)alter database open resetlogs
6、控制文件、日志文件、数据文件丢失
1)shutdown abort
2)startup(自动挂住)
3)将control文件cp回原位置
4)alter database mount
5)cp所有数据文件回原位置
6)recover database using backup controlfile until cancel
7)alter database open resetlogs
异机迁移恢复+小版本升级:(oracle 11g 11.2.0.1~oracle 11g 11.2.0.4)
1、将热备份文件及归档日志传至目标机器
2、startup pfile='/备份pfile文件' mount;
3、recover database using backup controlfile until cancel;
4、alter database open upgrade resetlogs;
该升级方法将丢失最近在线日志信息。
自己做的异机恢复实战
LINUX操作系统REDHAT6.5
ORACLE是11.2.0.4
源库做一个RMAN备份
复制备份文件到测试库
在测试库的操作:
记得先将备份文件放到一个oracle用户有权限读写的文件夹,然后把备份文件都授权oracle用户可访问
chown oracle.oinstall full_bak*
其实就是改改用户所属就好
修改参数文件
vi /opt/pfile.ora
limsdb.__db_cache_size=4026531840
limsdb.__java_pool_size=33554432
limsdb.__large_pool_size=50331648
limsdb.__oracle_base='/home/db/u01/app/oracle'#ORACLE_BASE set from environment
limsdb.__pga_aggregate_target=1677721600
limsdb.__sga_target=5033164800
limsdb.__shared_io_pool_size=0
limsdb.__shared_pool_size=872415232
limsdb.__streams_pool_size=16777216
*.audit_file_dest='/home/db/u01/app/oracle/admin/limstest/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/db/u01/app/oracle/oradata/limstest/control01.ctl','/home/db/u01/app/oracle/fast_recovery_area/limstest/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='limsdb'
*.db_recovery_file_dest='/home/db/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/home/db/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=limsdbXDB)'
*.open_cursors=300
*.pga_aggregate_target=1672478720
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=5017436160
*.undo_tablespace='UNDOTBS1'
export ORACLE_SID=limsdb
rman target /
startup nomount pfile='/opt/pfile20190926.ora';
restore controlfile from '/opt/full_cont_LIMSDBxx_876748607_20190925_7356_1';
mount database;
catalog start with '/opt/bak/';
去 源库查询下数据文件路径select file#,name from v$datafile;
run{
set newname for datafile '+DATA/limsdb/datafile/system.256.942403651' to '/home/db/u01/app/oracle/oradata/limstest/system01.dbf';
set newname for datafile '+DATA/limsdb/datafile/sysaux.257.942403655' to '/home/db/u01/app/oracle/oradata/limstest/sysaux01.dbf';
set newname for datafile '+DATA/limsdb/datafile/undotbs1.258.942403659' to '/home/db/u01/app/oracle/oradata/limstest/undotbs1.dbf';
set newname for datafile '+DATA/limsdb/datafile/users.259.942403659' to '/home/db/u01/app/oracle/oradata/limstest/users.dbf';
set newname for datafile '+DATA/limsdb/datafile/undotbs2.271.942403927' to '/home/db/u01/app/oracle/oradata/limstest/undotbs2.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.281.943025409' to '/home/db/u01/app/oracle/oradata/limstest/limsdata01.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.282.943025591' to '/home/db/u01/app/oracle/oradata/limstest/limsdata02.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.283.943025709' to '/home/db/u01/app/oracle/oradata/limstest/limsdata03.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.284.943025841' to '/home/db/u01/app/oracle/oradata/limstest/limsdata04.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.285.943025965' to '/home/db/u01/app/oracle/oradata/limstest/limsdata05.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.286.943026085' to '/home/db/u01/app/oracle/oradata/limstest/limsdata06.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.287.943026201' to '/home/db/u01/app/oracle/oradata/limstest/limsdata07.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.288.943026369' to '/home/db/u01/app/oracle/oradata/limstest/limsdata08.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.289.943026563' to '/home/db/u01/app/oracle/oradata/limstest/limsdata09.dbf';
set newname for datafile '+DATA/limsdb/datafile/byhealth_data.290.943026913' to '/home/db/u01/app/oracle/oradata/limstest/limsdata10.dbf';
restore database;
}
export ORACLE_SID=limsdb
sqlplus / as sysdba
alter database open;
alter database open resetlogs;
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/limsdb/datafile/system.256.942403651'
Alter database rename file '+DATA/limsdb/datafile/system.256.942403651' to '/home/db/u01/app/oracle/oradata/limstest/system01.dbf';
Alter database rename file '+DATA/limsdb/datafile/sysaux.257.942403655' to '/home/db/u01/app/oracle/oradata/limstest/sysaux01.dbf';
Alter database rename file '+DATA/limsdb/datafile/undotbs1.258.942403659' to '/home/db/u01/app/oracle/oradata/limstest/undotbs1.dbf';
Alter database rename file '+DATA/limsdb/datafile/users.259.942403659' to '/home/db/u01/app/oracle/oradata/limstest/users.dbf';
Alter database rename file '+DATA/limsdb/datafile/undotbs2.271.942403927' to '/home/db/u01/app/oracle/oradata/limstest/undotbs2.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.281.943025409' to '/home/db/u01/app/oracle/oradata/limstest/limsdata01.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.282.943025591' to '/home/db/u01/app/oracle/oradata/limstest/limsdata02.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.283.943025709' to '/home/db/u01/app/oracle/oradata/limstest/limsdata03.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.284.943025841' to '/home/db/u01/app/oracle/oradata/limstest/limsdata04.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.285.943025965' to '/home/db/u01/app/oracle/oradata/limstest/limsdata05.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.286.943026085' to '/home/db/u01/app/oracle/oradata/limstest/limsdata06.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.287.943026201' to '/home/db/u01/app/oracle/oradata/limstest/limsdata07.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.288.943026369' to '/home/db/u01/app/oracle/oradata/limstest/limsdata08.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.289.943026563' to '/home/db/u01/app/oracle/oradata/limstest/limsdata09.dbf';
Alter database rename file '+DATA/limsdb/datafile/byhealth_data.290.943026913' to '/home/db/u01/app/oracle/oradata/limstest/limsdata10.dbf';
Select group#,member from v$logfile order by 1;
alter database clear logfile group 9;
alter database clear logfile group 10;
alter database clear logfile group 11;
alter database clear logfile group 12;
alter database clear logfile group 13;
alter database clear logfile group 14;
alter database clear logfile group 15;
alter database clear logfile group 16;
alter database clear logfile group 17;
alter database clear logfile group 18;
在此之前记得先设置spfile
create spfile from pfile='/tmp/initorcl1.ora';
alter database open resetlogs;
recover database using backup controlfile;
auto
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
recover database using backup controlfile until cancel;
cancel
alter database open resetlogs;
startup force;
alter database open resetlogs;
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
startup force;
关于Oracle备份恢复中热备份恢复及异机恢复的原理是什么问题的解答就分享到这里了,希望以上内容可以对大家有一定的帮助,如果你还有很多疑惑没有解开,可以关注亿速云行业资讯频道了解更多相关知识。
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341