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備份恢復中熱備份恢復及異機恢復的原理是什么問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。