课程第11次（2018-10-30星期二）

botang

Program1：

1.         
   
2.         BEGIN
   
3. DBMS_SCHEDULER.CREATE_PROGRAM(
   
4. program_name=>'"HR"."PROGRAM1"',
   
5. program_action=>'begin
   
6.   insert into t05317_1 values ( sysdate );
   
7.   commit;
   
8. end;',
   
9. program_type=>'PLSQL_BLOCK',
   
10. number_of_arguments=>0,
    
11. comments=>'',
    
12. enabled=>TRUE);
    
13. END;

复制代码

Schedule1：

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
4. start_date => systimestamp at time zone 'Asia/Shanghai',
   
5. end_date => to_timestamp_tz('2018-10-30 20:00:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
6. schedule_name => '"HR"."SCHEDULE1"');
   
7. END;

复制代码

Job1：

1. grant create job to hr;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB1"',
   
4. program_name => '"HR"."PROGRAM1"',
   
5. schedule_name => '"HR"."SCHEDULE1"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. auto_drop => FALSE,
   
8. enabled => TRUE);
   
9. END;
   

复制代码

Program2：

1. #!/bin/sh
   
2. export LANG=en_US
   
3. date >> /home/oracle/program2.txt
   

复制代码

1. BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"HR"."PROGRAM2"',
   
4. program_action=>'/home/oracle/program2.sh',
   
5. program_type=>'EXECUTABLE',
   
6. number_of_arguments=>0,
   
7. comments=>'',
   
8. enabled=>TRUE);
   
9. END;

复制代码

Schedule2：

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
4. start_date => systimestamp at time zone 'Asia/Shanghai',
   
5. schedule_name => '"HR"."SCHEDULE2"');
   
6. END;

复制代码

Job2：

1. grant create external job to hr;
   
2. 
   
3. select  * from dba_scheduler_credentials;
   
4. 
   
5. begin
   
6.   dbms_scheduler.create_credential(credential_name => 'credential1',
   
7.   username => 'oracle',
   
8.   password => 'oracle');
   
9. end;
   
10. 
    
11. grant execute on credential1 to hr;

复制代码

1.         
   
2.         BEGIN
   
3. sys.dbms_scheduler.create_job(
   
4. job_name => '"HR"."JOB2"',
   
5. program_name => '"HR"."PROGRAM2"',
   
6. schedule_name => '"HR"."SCHEDULE2"',
   
7. job_class => '"DEFAULT_JOB_CLASS"',
   
8. auto_drop => FALSE,
   
9. enabled => FALSE);
   
10. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB2"', attribute => 'credential_name', value => '"SYS"."CREDENTIAL1"' );
    
11. sys.dbms_scheduler.enable( '"HR"."JOB2"' );
    
12. END;

复制代码

在windows上外部作业（包含远程外部作业）容易报以下错误：

Host Credentials报错"Connection to host as user oracle failed: ERROR: Wrong password for user"的解决一例


我成功安装了Oracle 10g，可以登录到企业管理器，但无法执行一些操作，如数据库的备份调度，
执行恢复等，由于操作系统登录全权证书的问题。我被要求提供操作系统登录凭证才可以执行这些操作。

我在Host Credentials那里输入的是用户administrator,并且administrator已经加入到了ORA_DBA组
但执行操作是报错如下：
Error: Connection to host as user oracle failed:
ERROR: Wrong password for user

如何解决这个问题呢？
我查阅了Oracle的官方文档，发现如下解决办法：

1. 打开组策略,用gpedit.msc命令。
2. 逐步找到compute configuration -- windows setting -- security setting -- local policy
-- user rights assignment -- log on as a batch job
3.双击"log on as a batch job"，在这里加入你需要的操作系统user
4.再次在OEM的Host Credentials输入那些user和password，一切ok。

Schedule3：

1. BEGIN
   
2. /* Complex scheduling example 1: Public Holidays */
   
3. DBMS_SCHEDULER.CREATE_SCHEDULE
   
4. (schedule_name => 'HR.SCHEDULE3'
   
5. ,repeat_interval =>'freq=YEARLY;bydate=20180607,20180608,20180701'
   
6. ,comments => 'Public Holidays 2015'
   
7. );
   
8. END;
   

复制代码

Schedule31：

1. BEGIN
   
2. /* Complex scheduling example 2: Working Days */
   
3. DBMS_SCHEDULER.CREATE_SCHEDULE
   
4. (schedule_name => 'HR.SCHEDULE31'
   
5. ,repeat_interval =>
   
6. 'freq=YEARLY;byday=MON,TUE,WED,THU,FRI;'
   
7. ,comments => 'Run on every working day'
   
8. );
   
9. END;
   

复制代码

1.         
   
2. BEGIN
   
3. sys.dbms_scheduler.create_schedule(
   
4. repeat_interval => 'FREQ=yearly;bydate=20181107,20181108,20181201;BYHOUR=14;BYMINUTE=10;BYSECOND=0;exclude=HR.SCHEDULE31',
   
5. start_date => systimestamp at time zone 'Asia/Shanghai',
   
6. schedule_name => '"HR"."SCHEDULE32"');
   
7. END;

复制代码

1. declare
   
2.    v_1  timestamp;
   
3. begin
   
4.   for i in 1..1
   
5.    loop
   
6.     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
   
7.        calendar_string=>'HR.SCHEDULE32'   ,
   
8.    start_date => sysdate,
   
9.    return_date_after  => v_1,
   
10.    next_run_date    => v_1 );
    
11.   dbms_output.put_line(to_char(v_1,'YYYY-MM-DD:HH24:MI:SS'));
    
12. end loop;
    
13. end;

复制代码

Program4：

1. CREATE TABLE hr.scheduler_test (
   
2.   id            NUMBER(10)    NOT NULL,
   
3.   created_date  DATE          NOT NULL,
   
4.   CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
   
5. );
   
6. 
   
7. CREATE SEQUENCE hr.scheduler_test_seq;

复制代码

1. BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"HR"."PROGRAM4"',
   
4. program_action=>'BEGIN
   
5.                             INSERT INTO scheduler_test (id, created_date)
   
6.                             VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
   
7.                             COMMIT;
   
8.                           END;',
   
9. program_type=>'PLSQL_BLOCK',
   
10. number_of_arguments=>0,
    
11. comments=>'',
    
12. enabled=>TRUE);
    
13. END;

复制代码

Schedule4：

1. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
   
2. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
   
3. select  * from dba_queues q where q.QUEUE_TYPE
   
4.    not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
   
5.    
   
6. CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT (
   
7.   event_name  VARCHAR2(30)
   
8. );
   
9. 
   
10. grant execute on t_event_queue_payload to hr;
    
11. 
    
12. BEGIN
    
13.   -- Create a queue table to hold the event queue.
    
14.   DBMS_AQADM.create_queue_table(
    
15.     queue_table        => 'event_queue_tab',
    
16.     queue_payload_type => 't_event_queue_payload',
    
17.     multiple_consumers => TRUE,
    
18.     comment            => 'Queue Table For Event Messages');
    
19.   -- Create the event queue.
    
20.   DBMS_AQADM.create_queue (
    
21.     queue_name  => 'event_queue',
    
22.     queue_table => 'event_queue_tab');
    
23.   -- Start the event queue.
    
24.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
    
25. END;
    
26. 
    
27. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
    
28. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
    
29. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
    
30. begin
    
31.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
    
32. end;
    
33.    
    
34. begin
    
35.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
    
36. end;
    
37. 
    
38. select  * from dba_tab_privs p  where p.grantee='HR';
    
39. 
    

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_event_schedule(
   
3. event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
   
4. queue_spec => '"SYS"."EVENT_QUEUE"',
   
5. start_date => systimestamp at time zone 'Asia/Shanghai',
   
6. schedule_name => '"HR"."SCHEDULE4"');
   
7. END;

复制代码

Job4：

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB4"',
   
4. program_name => '"HR"."PROGRAM4"',
   
5. schedule_name => '"HR"."SCHEDULE4"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. auto_drop => FALSE,
   
8. enabled => TRUE);
   
9. END;

复制代码

下面本来ADT设施的：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 30 21:39:07 2018
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> grant execute on dbms_aq to hr;
   
10. 
    
11. Grant succeeded.
    
12. 
    
13. SQL>
    

复制代码

1. DECLARE
   
2.   l_enqueue_options     DBMS_AQ.enqueue_options_t;
   
3.   l_message_properties  DBMS_AQ.message_properties_t;
   
4.   l_message_handle      RAW(16);
   
5.   l_queue_msg           sys.t_event_queue_payload;
   
6. BEGIN
   
7.   l_queue_msg := sys.t_event_queue_payload('give_me_a_prod');
   
8. 
   
9.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
   
10.                   enqueue_options     => l_enqueue_options,
    
11.                   message_properties  => l_message_properties,
    
12.                   payload             => l_queue_msg,
    
13.                   msgid               => l_message_handle);
    
14.   COMMIT;
    
15. END;

复制代码

jarlean

老师，执行外部job的时候有如下报错，但是我的操作系统，oracle账号，密码没写错，不知道什么情况了。
EXTERNAL_LOG_ID="job_74991_1846", ORA-27369: job of type EXECUTABLE failed with exit code: Argument list too long STANDARD_ERROR="Launching external job failed: Invalid username or password"

这些信息都确认过了：
Make sure $ORACLE_HOME/rdbms/admin/externaljob.ora is owned by root :
cd $ORACLE_HOME/rdbms/admin/
chown root externaljob.ora
Make sure the file permissions are correctly set :
chmod 640 externaljob.ora
make sure extjob is executable :
cd $ORACLE_HOME/bin
chmod 4750 extjob

以下是配置信息：
program2.sh

1. #!/bin/sh
   
2. date >> /home/oracle/program2.txt

复制代码

1. chmod +x program2.sh

复制代码

PROGRAM：

1. BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"TEST"."PROGRAM2"',
   
4. program_action=>'/home/oracle/program.sh',
   
5. program_type=>'EXECUTABLE',
   
6. number_of_arguments=>0,
   
7. comments=>'',
   
8. enabled=>TRUE);
   
9. END;

复制代码

SCHEDULE：

1. BEGIN
   
2. sys.dbms_scheduler.create_schedule(
   
3. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
4. start_date => systimestamp at time zone 'Asia/Shanghai',
   
5. end_date => to_timestamp_tz('2018-11-01 00:25:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
   
6. schedule_name => '"TEST"."SCHEDULE2"');
   
7. END;

复制代码

CREDENTIAL:

1. begin
   
2.   dbms_scheduler.create_credential(credential_name => 'credential2',
   
3.   username => 'oracle',
   
4.   password => 'oracle');
   
5. end;

复制代码

JOB：

1. grant create external job to test;
   
2. grant execute on credential2 to test;

复制代码

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"TEST"."JOB2"',
   
4. program_name => '"TEST"."PROGRAM2"',
   
5. schedule_name => '"TEST"."SCHEDULE2"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. auto_drop => FALSE,
   
8. enabled => FALSE);
   
9. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"TEST"."JOB2"', attribute => 'credential_name', value => '"SYS"."CREDENTIAL2"' );
   
10. sys.dbms_scheduler.enable( '"TEST"."JOB2"' );
    
11. END;

复制代码