Oracle 備份策略(create_schedule+)

參考文章http://blog.itpub.net/post/33454/395121錯誤解決http://www.adp-gmbh.ch/blog/2005/may/27.html

 

定義一個計劃 （每天執行）

  CALL dbms_scheduler.create_schedule  ('BACK_UP_INDONESIAPROD2',SYSDATE ,'FREQ=DAILY', to_date('2013-03-25 10:00:00', 'yyyy-mm-dd hh24:mi:ss'));  --刪除預存程序 call dbms_scheduler.drop_schedule('TEST');
--修改 call  DBMS_SCHEDULER.SET_ATTRIBUTE('BACK_UP_INDONESIAPROD2','repeat_interval','FREQ=MINUTELY');

定義一個任務（執行.bat檔案 目的備份書庫）

--建立 begindbms_scheduler.create_job(job_name => 'backup_03_15',schedule_name => 'BACK_UP_INDONESIAPROD2',job_type => 'EXECUTABLE',job_action => 'C:\backup_indonesiaprod2.bat',enabled => true,comments => 'BACK_UP_INDONESIAPROD2');end;/--刪除begindbms_scheduler.drop_job('backup_03_15' );end;/

 

 

 任務指令碼

exp indonesiaprod2/indonesiaprod2@localhost:1521/xe full=n  file='indonesiaprod2_%date:~0,4%%date:~5,2%%date:~8,2%.dmp' log=indonesiaprod2_%date:~0,4%%date:~5,2%%date:~8,2%.txt

查看目前使用者的計劃和任務

select job_name,program_name,schedule_name from user_scheduler_jobs ;

查看目前使用者計劃和任務運行情況

select * from user_scheduler_job_run_details order by log_id desc;

開啟如下服務

 

修改並查詢如下參數

 ALTER SYSTEM SET job_queue_processes = 10;

show parameter job_queue_process;

任務執行結果:產生如下檔案

 -----

資料匯入

#set nls_lang=AMERICAN_AMERICA.UTF8
#>imp system/cici123love@xe
Import: Release 11.2.0.2.0 - Production on Fri Mar 15 23:47:24 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Productio
n
Import data only (yes/no): no > C:\Users\Administrator.cici-THINK\indonesiaprod2
.dmp
IMP-00001: respond with either yes, no, RETURN or '.' to quit
Import data only (yes/no): no >
Import file: EXPDAT.DMP > C:\Users\Administrator.cici-THINK\indonesiaprod2.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by INDONESIAPROD2, not by you
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)^C
C:\Users\Administrator.cici-THINK>imp system/cici123love@xe
Import: Release 11.2.0.2.0 - Production on Fri Mar 15 23:48:15 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Productio
n
Import data only (yes/no): no >
Import file: EXPDAT.DMP > C:\Users\Administrator.cici-THINK\indonesiaprod2.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by INDONESIAPROD2, not by you
import done in UTF8 character set and AL16UTF16 NCHAR character s