Bo's Oracle Station

查看: 2576|回复: 1

课程第11次(2018-10-30星期二)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-10-30 20:11:08 | 显示全部楼层 |阅读模式
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. select  * from dba_scheduler_credentials;

  3. begin
  4.   dbms_scheduler.create_credential(credential_name => 'credential1',
  5.   username => 'oracle',
  6.   password => 'oracle');
  7. end;

  8. 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. 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. grant execute on t_event_queue_payload to hr;

  10. BEGIN
  11.   -- Create a queue table to hold the event queue.
  12.   DBMS_AQADM.create_queue_table(
  13.     queue_table        => 'event_queue_tab',
  14.     queue_payload_type => 't_event_queue_payload',
  15.     multiple_consumers => TRUE,
  16.     comment            => 'Queue Table For Event Messages');
  17.   -- Create the event queue.
  18.   DBMS_AQADM.create_queue (
  19.     queue_name  => 'event_queue',
  20.     queue_table => 'event_queue_tab');
  21.   -- Start the event queue.
  22.   DBMS_AQADM.start_queue (queue_name => 'event_queue');
  23. END;

  24. select  * from dba_queues   q  where  q.QUEUE_TYPE='NORMAL_QUEUE' ;
  25. select  * from dba_queues q where q.QUEUE_TYPE='EXCEPTION_QUEUE';
  26. select  * from dba_queues q where q.QUEUE_TYPE   not in ('NORMAL_QUEUE','EXCEPTION_QUEUE');
  27. begin
  28.    dbms_aqadm.grant_queue_privilege(privilege => 'ENQUEUE',queue_name => 'event_queue',grantee => 'HR');
  29. end;
  30.    
  31. begin
  32.    dbms_aqadm.grant_queue_privilege(privilege => 'DEQUEUE',queue_name => 'event_queue',grantee => 'HR');
  33. end;

  34. select  * from dba_tab_privs p  where p.grantee='HR';

复制代码
  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. SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 30 21:39:07 2018

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> grant execute on dbms_aq to hr;

  7. Grant succeeded.

  8. 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.   DBMS_AQ.enqueue(queue_name          => 'sys.event_queue',
  9.                   enqueue_options     => l_enqueue_options,
  10.                   message_properties  => l_message_properties,
  11.                   payload             => l_queue_msg,
  12.                   msgid               => l_message_handle);
  13.   COMMIT;
  14. END;
复制代码












回复

使用道具 举报

1

主题

16

帖子

280

积分

中级会员

Rank: 3Rank: 3

积分
280
QQ
发表于 2018-10-31 16:49:56 | 显示全部楼层
老师,执行外部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;
复制代码
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 06:34 , Processed in 0.040534 second(s), 25 queries .

快速回复 返回顶部 返回列表