课程第9次

secoug

Notice: This blog is written by Bo Tang.

job9：

1. select  * from dba_scheduler_credentials;
   
2. 
   
3. select  * from hr.scheduler_test;
   
4. 
   
5. select hr.scheduler_test_seq.currval from dual;
   
6. 
   
7. alter table hr.scheduler_test add ( event_name  varchar2(40)) ;
   
8. 
   
9. select sysdate from dual;
   
10. 
    
11. create or replace procedure procnew( p_1     sys.t_event_queue_payload    )
    
12. is
    
13. 
    
14. BEGIN
    
15.      INSERT INTO scheduler_test
    
16.          VALUES (scheduler_test_seq.NEXTVAL, 'job9',SYSDATE,  p_1.event_name   );
    
17.          COMMIT;
    
18. END;
    
19. 
    
20. 
    
21. select  * from user_errors;
    
22. 
    
23. 
    
24. begin
    
25.   dbms_scheduler.create_program (
    
26.    program_name => 'programnew',
    
27.     program_action=> 'procnew',
    
28.     program_type => 'STORED_PROCEDURE',
    
29.     number_of_arguments => 1,
    
30.     enabled => false) ;
    
31. end;
    
32. 
    
33. 
    
34. select  * from user_scheduler_programs;
    
35. 
    
36. select  * from user_scheduler_program_args;
    
37. 
    
38. 
    
39. 
    
40. begin
    
41.    dbms_scheduler.define_metadata_argument (
    
42.     program_name => 'programnew',
    
43.     argument_position => 1 ,
    
44.     metadata_attribute => 'EVENT_MESSAGE') ;   
    
45. end;
    
46. 
    
47. 
    
48. 
    
49. begin
    
50. dbms_scheduler.enable ('programnew');
    
51. end;
    
52. 
    
53. 
    
54. select  * from user_scheduler_programs;
    

复制代码

--------------------------------------------

	ID	DESCRIPTION	CREATED_DATE	EVENT_NAME
1	16	test_program_2	9/17/2019 8:54:12 AM
2	4	test_program_1	9/16/2019 7:46:07 PM
3	5	test_program_2	9/16/2019 7:46:07 PM
4	6	test_program_3	9/16/2019 7:46:07 PM
5	7	test_program_1	9/16/2019 7:49:20 PM
6	8	test_program_2	9/16/2019 7:49:20 PM
7	9	test_program_3	9/16/2019 7:49:20 PM
8	10	test_program_1	9/16/2019 8:04:39 PM
9	11	test_program_3	9/16/2019 8:04:39 PM
10	12	test_program_2	9/16/2019 8:08:53 PM
11	13	test_program_3	9/16/2019 8:08:53 PM
12	14	test_program_2	9/16/2019 8:16:23 PM
13	15	job9	9/17/2019 8:54:11 AM	Chemical_Wrong

-----------------------------------job10

1. [root@station79 lib64]# ln -s /lib64/libpam.so.0.82.2   /lib64/libpam.so

复制代码

1. select  * from dba_scheduler_file_watchers;
   
2. 
   
3. select  * from dba_scheduler_credentials;
   
4. 
   
5. begin
   
6.    dbms_scheduler.create_file_watcher(
   
7.        file_watcher_name => 'filewatcher1',
   
8.        directory_path => '/home/oracle/myfile',
   
9.        file_name => 'botang*.txt',
   
10.        credential_name => 'credential1');
    
11. end;
    

复制代码

由于以上截图中的变化了的queue，所以一定要按照以下方法做job10：

1. BEGIN
   
2.   DBMS_SCHEDULER.CREATE_JOB(
   
3.    jOB_NAME=> 'hr.job10',
   
4.    PROGRAM_NAME=> 'hr.program10',
   
5.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
   
6.    QUEUE_SPEC=> 'filewatcher1',
   
7.    AUTO_DROP=> FALSE,
   
8.    ENABLED=> true);
   
9. END;

复制代码

1. begin
   
2.    dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
   
3. end;
   

复制代码

job11：

1. select  * from dba_scheduler_dests;
   
2. 
   
3. select  * from dba_scheduler_db_dests;
   
4. 
   
5. select  * from dba_scheduler_external_dests;
   

复制代码

1. 
   
2. 
   
3. select  * from dba_users  order by 1;
   
4. 
   
5. BEGIN
   
6. DBMS_XDB.SETHTTPPORT(8888);
   
7. END;
   
8. 
   
9. 
   
10. @?/rdbms/admin/prvtrsch.plb
    
11. 
    
12. 
    
13. BEGIN
    
14. DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
    
15. END;
    
16. 
    
17. 
    
18. SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;

复制代码

以下是远程目的地端的配置：

1. [oracle@station79 bin]$ pwd
   
2. /u01/app/oracle/product/11.2.0/client_1/bin
   
3. [oracle@station79 bin]$ ls  
   
4. acfsroot            CompEMcentral.pm      emctl.template        genorasdksh        patchAgtStPlugin.pm  StartAgent.pl
   
5. adrci               CompEMcore.pm         EMDeploy.pm           gensyslib          Path.pm              symfind
   
6. AgentLifeCycle.pm   Directory.pm          emdfail.command       jssu               RegisterTType.pm     unzip
   
7. AgentMisc.pm        EMAgentPatch.pm       EMDiag.pm             kfed               relink               wrc
   
8. AgentStatus.pm      EMAgent.pm            EmKeyCmds.pm          kfod               sAgentUtils.pm       zip
   
9. AgentSubAgent.pm    EmCommonCmdDriver.pm  EMomsCmds.pm          LaunchEMagent.pm   schagent
   
10. bndlchk             EMconnectorCmds.pm    EMSAConsoleCommon.pm  linkshlib          SecureAgentCmds.pm
    
11. commonenv           emctl                 emutil                nfsPatchPlugin.pm  SecureOMSCmds.pm
    
12. commonenv.template  EmctlCommon.pm        emutil.bat.template   oerr               SecureUtil.pm
    
13. CompEMagent.pm      emctl.pl              emwd.pl               osdbagrp           sqlplus
    
14. [oracle@station79 bin]$ ./schagent -h
    
15. Oracle Scheduler Agent V 11.2.0.3.1 Usage
    
16. 
    
17. -registerdatabase databaseHost databasePort
    
18. Register with a database.
    
19. 
    
20. -unregisterdatabase databaseHost databasePort
    
21. Unregister from a database.
    
22. 
    
23. -start
    
24. Start the Scheduler agent
    
25. 
    
26. -stop
    
27. Stop the Scheduler agent
    
28. 
    
29. [oracle@station79 bin]$ ./schagent  -registerdatabase  192.168.0.76 8888
    
30. Agent Registration Password ? ***********
    
31. 
    
32. ERROR: Connecting to: http://192.168.0.76:8888/remote_scheduler_agent/register_agent. java.net.NoRouteToHostException: No route to host
    
33. 
    
34. [oracle@station79 bin]$ ./schagent  -registerdatabase  192.168.0.79 8888
    
35. Agent Registration Password ? ***********
    
36. 
    
37. Oracle Scheduler Agent Registration for 11.2 Agent
    
38. Agent Registration Successful!
    
39. [oracle@station79 bin]$ ./schagent  -start
    
40. Scheduler agent started
    
41. [oracle@station79 bin]$

复制代码

1. select  * from dba_scheduler_external_dests;

复制代码

	DESTINATION_NAME	HOSTNAME	PORT	IP_ADDRESS	ENABLED	COMMENTS
1	STATION79	station79.example.com	1025	192.168.0.79	TRUE	Registered on 17-SEP-19 10.40.30.975846 AM PRC