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备份恢复中热备份恢复及异机恢复的原理是什么问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注行业资讯频道了解更多相关知识。