Oracle備份分為 兩種
邏輯備份:就是邏輯導出數據(expdp/impdp)只是對數據進行轉存,所以恢復是只能恢復到備份時保存的數據,
邏輯備份適合備份那些變化很少的表。
缺點:恢復時間長優點:邏輯備份和平台無關,所以是數據遷移的重要手段
物理備份:是指通過備份物理文件拷貝的方式對數據庫進行備份.物理備份:又分為兩種
冷備份:需要一致性停庫,然後拷貝數據文件,日志文件,控制文件停庫前,
需要通過v$datafile,v$logfile,v$controlfile 確定文件路徑
非open的數據庫
優點:執行簡單,可靠性依存cp
缺點:停止數據庫,整體備份
熱備份:前提條件:數據庫必須運行中處於活動中並運行歸檔模式
分為用戶管理備份和oracle管理備份
用戶管理備份:是指用戶通過將表空間置於熱備份模式,然後通過操作系統工具進行拷貝,拷貝結束後表空間熱備份模式。
10g之前 只能對表空間逐備份alter tablespace 表空間名 begin backup;
alter tablespace 表空間名 end backup ;
10g中可以對數據庫置於熱備狀態 alter database begin backup
alter database end backup
1.表空間單獨存盤
2.凍結文件頭 其他塊繼續使用此時拷貝只有文件頭是好塊
3.改變了日志的行為
1. 驗證表空間單獨存盤
SYS@ORCL>col name for a50
SYS@ORCL>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626426
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626426
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626426
/u01/app/oracle/oradata/ORCL/users01.dbf 626426
/u01/app/oracle/oradata/ORCL/example01.dbf 626426
/u01/app/oracle/oradata/ORCL/block.dbf 626426
6 rows selected.
2. 查看當前系統SCN號
SYS@ORCL>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
626513
3. 修改users表空間為熱備狀態
SYS@ORCL>alter tablespace users begin backup;
Tablespace altered.
4. 查看當前表空間的SCN號,此時users表空間頭部的SCN號已經被凍結
SYS@ORCL>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626426
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626426
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626426
/u01/app/oracle/oradata/ORCL/users01.dbf 626522
/u01/app/oracle/oradata/ORCL/example01.dbf 626426
/u01/app/oracle/oradata/ORCL/block.dbf 626426
6 rows selected.
5. 產生系統檢查點 驗證 users 表空間文件頭SCN被凍結
SYS@ORCL>alter system checkpoint;
System altered.
SYS@ORCL>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626543
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626543
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626543
/u01/app/oracle/oradata/ORCL/users01.dbf 626522
/u01/app/oracle/oradata/ORCL/example01.dbf 626543
/u01/app/oracle/oradata/ORCL/block.dbf 626543
6 rows selected.
6. 查看當前備份的文件狀態,發現file# 4 文件 也就是users 表空間對應的文件活動狀態,說明需要進行介質恢復
SYS@ORCL>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 626522 06-MAR-14
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected.
SYS@ORCL>shutdown immediate //此時用Immediate方式關庫報錯
ORA-01149: cannot shutdown - file 4 has online backup set
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/users01.dbf'
SYS@ORCL>startup force
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 79693392 bytes
Database Buffers 201326592 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/ORCL/users01.dbf'
7. 結束users 表空間的熱備狀態
SYS@ORCL>alter tablespace users end backup;
Tablespace altered.
8.查看當前備份的文件狀態,此時file# 4 也就是users 表空間對應的文件已經 不在活動
SYS@ORCL>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 626522 06-MAR-14
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected.
SYS@ORCL>select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/ORCL/system01.dbf 626543
/u01/app/oracle/oradata/ORCL/undotbs01.dbf 626543
/u01/app/oracle/oradata/ORCL/sysaux01.dbf 626543
/u01/app/oracle/oradata/ORCL/users01.dbf 626543
/u01/app/oracle/oradata/ORCL/example01.dbf 626543
/u01/app/oracle/oradata/ORCL/block.dbf 626543
6 rows selected.
9.打開數據庫
SYS@ORCL>alter database open;
Database altered.
SYS@ORCL>select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 626522 06-MAR-14
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected.