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文件 目的备份书库）

--创建 
begin
dbms_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;
/
--删除
begin
dbms_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 set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): no >

Import grants (yes/no): yes >

Import table data (yes/no): yes >

Import entire export file (yes/no): no >
Username: indonesiaprod2

Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: