萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle exp 備份機搭建全記錄:TNS 配置、自動化腳本、定時任務與保留策略

環境準備

-- TNS 別名配置 (tnsnames.ora)
JMYBJS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 129.1.9.90)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = jmybjs))
  )

目錄結構

D:\backup\dmp\   -- 存放 .dmp 匯出檔
D:\backup\log\   -- 存放匯出日誌

每夜自動完整匯出腳本 (VBScript)

' Nightly full export using exp
Dim outfile, aa, bb, stroption1, strsql, ws
Set ws = WScript.CreateObject("WScript.Shell")

' 日期格式 YYYYMMDD
A = CInt(Month(DateAdd("d", 0, Date())))
B = CInt(Day(DateAdd("d", 0, Date())))
C = CInt(Year(DateAdd("d", 0, Date())))
If A < 10 Then aa = "0" & A Else aa = CStr(A)
If B < 10 Then bb = "0" & B Else bb = CStr(B)
outfile = CStr(C) & aa & bb

' exp 完整參數:system/密碼@tns_別名 full=y buffer=900000
stroption1 = "D:\oracle_bf\product\10.1.0\Db_1\BIN\exp system/******@jmybjs full=y buffer=900000 file=D:\backup\dmp\jmybjs" & outfile & " log=D:\backup\log\jmybjs" & outfile & ".log"
strsql = stroption1
ws.Run strsql

30 天保留策略:自動刪除過期 .dmp

' Delete dumps older than 30 days
Dim outfile, aa, bb, stroption1, strsql, ws
Set ws = WScript.CreateObject("WScript.Shell")

A = CInt(Month(DateAdd("d", -30, Date())))
B = CInt(Day(DateAdd("d", -30, Date())))
C = CInt(Year(DateAdd("d", -30, Date())))
If A < 10 Then aa = "0" & A Else aa = CStr(A)
If B < 10 Then bb = "0" & B Else bb = CStr(B)
outfile = CStr(C) & aa & bb

' 系統內建 delete.exe (XP/2003 需從其他機複製至 System32)
stroption1 = "C:\WINDOWS\system32\delete.exe D:\backup\dmp\jmybjs" & outfile & ".dmp"
strsql = stroption1
ws.Run strsql

坑點:Windows Server 2003 System32 預設無 delete.exe,需從 Windows XP/2000 複製一份過來。

Windows 排程任務配置

  1. 控制台 → 排程工作 → 新增排程工作
  2. 瀏覽選取 exp_backup.vbs (完整匯出)
  3. 設定每日執行時間 (如凌晨 02:00)
  4. 同理新增 exp_delete.vbs (刪除過期),時間錯開 (如 02:30)
  5. 執行身分:具備 Oracle Client 與網路存取權限的服務帳戶

驗證與維護

  • 定期檢查 D:\backup\log\ 下 .log 檔案,確認 Export terminated successfully without warnings
  • 每季測試 imp 匯入演練,驗證備份可用性
  • 磁碟空間監控:設定閾值告警 (如 > 80%)
copyright © 萬盛學電腦網 all rights reserved