原理:
資料庫必須執行在歸檔模式下,否則備份沒有意義。備份前凍結塊頭,使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;