熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle備份與恢復案例二

2022-06-13   來源: Oracle 

   檢查數據庫的數據(完全恢復)
  
  SQL> select * from test;
  
  A
  
  
  
  
  
  
  
  說明
  
  采用熱備份需要運行在歸檔模式下可以實現數據庫的完全恢復也就是說從備份後到數據庫崩潰時的數據都不會丟失;
  
  可以采用全備份數據庫的方式備份對於特殊情況也可以只備份特定的數據文件如只備份用戶表空間(一般情況下對於某些寫特別頻繁的數據文件可以單獨加大備份頻率)
  
  如果在恢復過程中發現損壞的是多個數據文件即可以采用一個一個數據文件的恢復方法(第步中需要對數據文件一一脫機步中需要對數據文件分別恢復)也可以采用整個數據庫的恢復方法
  
  如果是系統表空間的損壞不能采用此方法
  
   RMAN備份方案
  
  RMAN也可以進行聯機備份而且備份與恢復方法將比OS備份更簡單可靠
  
  連接數據庫創建測試表並插入記錄
  
  SQL> connect internal/password as sysdba;
  
  Connected
  
  SQL> create table test(a int) tablespace users;
  
  Table created
  
  SQL> insert into test values();
  
   row inserted
  
  SQL> commit;
  
  Commit complete
  
   備份數據庫表空間users
  
  C:\>rman
  
  Recovery Manager: Release Production
  
  RMAN> connect rcvcat rman/rman@back
  
  RMAN: connected to recovery catalog database
  
  RMAN> connect target internal/virpure
  
  RMAN: connected to target database: TEST (DBID=)
  
  RMAN> run{
  
  > allocate channel c type disk;
  
  > backup tag tsuser format d:\backup\tsuser_%u_%s_%p
  
  > tablespace users;
  
  > release channel c;
  
  > }
  
  RMAN: compiling command: allocate
  
  RMAN: executing command: allocate
  
  RMAN: allocated channel: c
  
  RMAN: channel c: sid= devtype=DISK
  
  RMAN: compiling command: backup
  
  RMAN: performing implicit partial resync of recovery catalog
  
  RMAN: executing command: partial resync
  
  RMAN: starting partial resync of recovery catalog
  
  RMAN: partial resync complete
  
  RMAN: executing command: backup
  
  RMAN: channel c: starting full datafile backupset
  
  RMAN: set_count= set_stamp= creation_time=MAY
  
  RMAN: channel c: specifying datafile(s) in backupset
  
  RMAN: input datafile fno= name=D:\Oracle\ORADATA\TEST\USERDBF
  
  RMAN: channel c: piece created
  
  RMAN: piece handle=D:\BACKUP\TSUSER_ENAC__ comment=NONE
  
  RMAN: backup set complete elapsed time: ::
  
  RMAN: executing command: partial resync
  
  RMAN: starting partial resync of recovery catalog
  
  RMAN: partial resync complete
  
  RMAN: compiling command: release
  
  RMAN: executing command: release
  
  RMAN: released channel: c
  
  RMAN>
  
   繼續在測試表中插入記錄
  
  SQL> insert into test values();
  
   row inserted
  
  SQL> commit;
  
  Commit complete
  
  SQL> select * from test;
  
  A
  
  
  
  
  
  
  
  SQL> alter system switch logfile;
  
  System altered
  
  SQL>r
  
  * alter system switch logfile;
  
  System altered
  
   關閉數據庫模擬丟失數據文件
  
  SQL> shutdown immediate;
  
  Database closed
  
  Database dismounted
  
  Oracle instance shut down
  
  C:\>del D:\Oracle\ORADATA\TEST\USERDBF
  
   啟動數據庫檢查錯誤
  
  SQL> startup
  
  Oracle instance started
  
  Total System Global Area  bytes
  
  Fixed Size           bytes
  
  Variable Size        bytes
  
  Database Buffers      bytes
  
  Redo Buffers          bytes
  
  Database mounted
  
  ORA: cannot identify/lock data file see DBWR trace file
  
  ORA: data file : D:\Oracle\ORADATA\TEST\USERDBF
  
   先打開數據庫
  
  SQL> alter database datafile offline drop;
  
  Database altered
  
  SQL> alter database open;
  
  Database altered
  
   恢復該表空間
  
  恢復腳本可以是恢復單個數據文件
  
  run{
  
  allocate channel c type disk;
  
  restore datafile ;
  
  recover datafile ;
  
  sql alter database datafile online;
  
  release channel c;
  
  }
  
  也可以是恢復表空間
  
  run{
  
  allocate channel c type disk;
  
  restore tablespace users;
  
  recover tablespace users;
  
  sql alter database datafile online;
  
  release channel c;
  
  }
  
  過程如下
  
  C:\>rman
  
  Recovery Manager: Release Production
  
  RMAN> connect rcvcat rman/rman@back
  
  RMAN: connected to recovery catalog database
  
  RMAN> connect target internal/virpure
  
  RMAN: connected to target database: TEST (DBID=)
  
  RMAN> run{
  
  > allocate channel c type disk;
  
  > restore datafile ;
  
  > recover datafile ;
  
  > sql alter database datafile online;
  
  > release channel c;
  
  > }
  
  //輸出內容冗長省略編者
  
  RMAN>
  
   檢查數據是否完整
  
  SQL> alter database open;
  
  Database altered
  
  SQL> select * from test;
  
  A
  
  
  
  
  
  
  
  說明
  
  RMAN也可以實現單個表空間或數據文件的恢復恢復過程可以在mount下或open方式下如果在open方式下恢復可以減少down機時間;
  
  如果損壞的是一個數據文件建議offline並在open方式下恢復;
  
  這裡可以看到RMAN進行數據文件與表空間恢復的時候代碼都比較簡單而且能保證備份與恢復的可靠性所以建議采用RMAN的備份與恢復
  
  丟失多個數據文件實現整個數據庫的恢復
  
   OS備份方案
  
  OS備份歸檔模式下損壞(丟失)多個數據文件進行整個數據庫的恢復
  
   連接數據庫創建測試表並插入記錄
  
  SQL> connect internal/password as sysdba;
  
  Connected
  
  SQL> create table test(a int);
  
  Table created
  
  SQL> insert into test values();
  
   row inserted
  
  SQL> commit;
  
  Commit complete
  
   備份數據庫備份除臨時數據文件後的所數據文件
  
  SQL> @hotbaksql 或在DOS下 svrmgrl @hotbaksql
  
   繼續在測試表中插入記錄
  
  SQL> insert into test values();
  
   row inserted
  
  SQL> commit;
  
  Commit complete
  
  SQL> select * from test;
  
  A
  
  
  
  
  
  
  
  SQL> alter system switch logfile;
  
  System altered
  
  SQL> alter system switch logfile;
  
  System altered
  
   關閉數據庫模擬丟失數據文件
  <
From:http://tw.wingwit.com/Article/program/Oracle/201311/17254.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.