远程外部作业（甚至是Windows)课程第48次（2017-10-26星期四）

botang

1Z0-053第17章
1Z0-052共19章（上完13章），1Z0-053共21章（上完15章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的28章
服务器端要做：

1. [root@station26 ~]# su - oracle
   
2. [oracle@station26 ~]$ lsnrctl services
   
3. 
   
4. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-OCT-2017 21:20:13
   
5. 
   
6. Copyright (c) 1991, 2014, Oracle.  All rights reserved.
   
7. 
   
8. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
   
9. Services Summary...
   
10. Service "+ASM" has 1 instance(s).
    
11.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
12.     Handler(s):
    
13.       "DEDICATED" established:5505 refused:0 state:ready
    
14.          LOCAL SERVER
    
15. Service "cdb1" has 1 instance(s).
    
16.   Instance "cdb1", status READY, has 1 handler(s) for this service...
    
17.     Handler(s):
    
18.       "DEDICATED" established:8037 refused:0 state:ready
    
19.          LOCAL SERVER
    
20. Service "cdb1XDB" has 1 instance(s).
    
21.   Instance "cdb1", status READY, has 1 handler(s) for this service...
    
22.     Handler(s):
    
23.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
24.          DISPATCHER <machine: station26.example.com, pid: 10924>
    
25.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=46517))
    
26. Service "orcl" has 1 instance(s).
    
27.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
28.     Handler(s):
    
29.       "DEDICATED" established:19901 refused:0 state:ready
    
30.          LOCAL SERVER
    
31. Service "orcl2" has 1 instance(s).
    
32.   Instance "orcl2", status READY, has 1 handler(s) for this service...
    
33.     Handler(s):
    
34.       "DEDICATED" established:20960 refused:0 state:ready
    
35.          LOCAL SERVER
    
36. Service "orcl2XDB" has 1 instance(s).
    
37.   Instance "orcl2", status READY, has 1 handler(s) for this service...
    
38.     Handler(s):
    
39.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
40.          DISPATCHER <machine: station26.example.com, pid: 13973>
    
41.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=19539))
    
42. Service "orclXDB" has 1 instance(s).
    
43.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
44.     Handler(s):
    
45.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
46.          DISPATCHER <machine: station26.example.com, pid: 13328>
    
47.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=27200))
    
48. Service "pdb1_1" has 1 instance(s).
    
49.   Instance "cdb1", status READY, has 1 handler(s) for this service...
    
50.     Handler(s):
    
51.       "DEDICATED" established:8037 refused:0 state:ready
    
52.          LOCAL SERVER
    
53. The command completed successfully
    
54. [oracle@station26 ~]$ sqlplus /nolog
    
55. 
    
56. SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 21:20:20 2017
    
57. 
    
58. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
59. 
    
60. SQL> conn / as sysdba
    
61. Connected.
    
62. SQL> show parameter dispa
    
63. 
    
64. NAME                                     TYPE         VALUE
    
65. ------------------------------------ ----------- ------------------------------
    
66. dispatchers                             string         (PROTOCOL=TCP) (SERVICE=orclXD
    
67.                                                  B)
    
68. max_dispatchers                      integer
    
69. SQL> alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=3)';  
    
70. 
    
71. System altered.
    
72. 
    
73. SQL> show parameter shared_ser
    
74. 
    
75. NAME                                     TYPE         VALUE
    
76. ------------------------------------ ----------- ------------------------------
    
77. max_shared_servers                     integer
    
78. shared_server_sessions                     integer
    
79. shared_servers                             integer         1
    
80. SQL> alter system set shared_servers=3 ;
    
81. 
    
82. System altered.
    
83. 
    
84. SQL> exit
    
85. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    
86. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
87. [oracle@station26 ~]$ lsnrctl services
    
88. 
    
89. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 24-OCT-2017 21:21:24
    
90. 
    
91. Copyright (c) 1991, 2014, Oracle.  All rights reserved.
    
92. 
    
93. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    
94. Services Summary...
    
95. Service "+ASM" has 1 instance(s).
    
96.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
97.     Handler(s):
    
98.       "DEDICATED" established:5505 refused:0 state:ready
    
99.          LOCAL SERVER
    
100. Service "cdb1" has 1 instance(s).
     
101.   Instance "cdb1", status READY, has 1 handler(s) for this service...
     
102.     Handler(s):
     
103.       "DEDICATED" established:8038 refused:0 state:ready
     
104.          LOCAL SERVER
     
105. Service "cdb1XDB" has 1 instance(s).
     
106.   Instance "cdb1", status READY, has 1 handler(s) for this service...
     
107.     Handler(s):
     
108.       "D000" established:0 refused:0 current:0 max:1022 state:ready
     
109.          DISPATCHER <machine: station26.example.com, pid: 10924>
     
110.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=46517))
     
111. Service "orcl" has 1 instance(s).
     
112.   Instance "orcl", status READY, has 4 handler(s) for this service...
     
113.     Handler(s):
     
114.       "DEDICATED" established:19902 refused:0 state:ready
     
115.          LOCAL SERVER
     
116.       "D001" established:0 refused:0 current:0 max:1022 state:ready
     
117.          DISPATCHER <machine: station26.example.com, pid: 10545>
     
118.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=52095))
     
119.       "D000" established:0 refused:0 current:0 max:1022 state:ready
     
120.          DISPATCHER <machine: station26.example.com, pid: 13328>
     
121.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=27200))
     
122.       "D002" established:0 refused:0 current:0 max:1022 state:ready
     
123.          DISPATCHER <machine: station26.example.com, pid: 10547>
     
124.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=49550))
     
125. Service "orcl2" has 1 instance(s).
     
126.   Instance "orcl2", status READY, has 1 handler(s) for this service...
     
127.     Handler(s):
     
128.       "DEDICATED" established:20963 refused:0 state:ready
     
129.          LOCAL SERVER
     
130. Service "orcl2XDB" has 1 instance(s).
     
131.   Instance "orcl2", status READY, has 1 handler(s) for this service...
     
132.     Handler(s):
     
133.       "D000" established:0 refused:0 current:0 max:1022 state:ready
     
134.          DISPATCHER <machine: station26.example.com, pid: 13973>
     
135.          (ADDRESS=(PROTOCOL=tcp)(HOST=station26.example.com)(PORT=19539))
     
136. Service "orclXDB" has 1 instance(s).
     
137.   Instance "orcl", status READY, has 0 handler(s) for this service...
     
138. Service "pdb1_1" has 1 instance(s).
     
139.   Instance "cdb1", status READY, has 1 handler(s) for this service...
     
140.     Handler(s):
     
141.       "DEDICATED" established:8038 refused:0 state:ready
     
142.          LOCAL SERVER
     
143. The command completed successfully
     
144. [oracle@station26 ~]$
     

复制代码

1. [oracle@station26 ~]$ netstat -lntp | grep :8888
   
2. (Not all processes could be identified, non-owned process info
   
3. will not be shown, you would have to be root to see it all.)
   
4. [oracle@station26 ~]$ sqlplus /nolog
   
5. 
   
6. SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 24 21:27:55 2017
   
7. 
   
8. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
9. 
   
10. SQL> conn / as sysdba
    
11. Connected.
    
12. SQL> exec DBMS_XDB.SETHTTPPORT(8888);
    
13. 
    
14. PL/SQL procedure successfully completed.
    
15. 
    
16. SQL> exit
    
17. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    
18. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
19. [oracle@station26 ~]$ netstat -lntp | grep :8888
    
20. (Not all processes could be identified, non-owned process info
    
21. will not be shown, you would have to be root to see it all.)
    
22. tcp        0      0 :::8888                     :::*                        LISTEN      13005/tnslsnr      
    

复制代码

1. BEGIN
   
2. DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
   
3. END;
   
4. 
   
5. SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;

复制代码

客户端要做：

1. DECLARE
   
2. versionnum VARCHAR2(30);
   
3. BEGIN
   
4. versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
   
5. DBMS_OUTPUT.PUT_LINE(versionnum);
   
6. END;
   
7. 
   
8. DECLARE
   
9. versionnum VARCHAR2(30);
   
10. BEGIN
    
11. versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.39');
    
12. DBMS_OUTPUT.PUT_LINE(versionnum);
    
13. END;

复制代码

1. grant all on station199 to hr;
   
2. 
   
3. grant all on station39 to hr;

复制代码

Windows版本（有bug,一直running）

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB11"',
   
4. job_type => 'EXECUTABLE',
   
5. job_action => 'c:\Users\oracle\c1.bat',
   
6. start_date => systimestamp at time zone 'Asia/Shanghai',
   
7. job_class => '"DEFAULT_JOB_CLASS"',
   
8. comments => 'job11',
   
9. auto_drop => FALSE,
   
10. enabled => FALSE);
    
11. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB11"', attribute => 'destination', value => 'station199' );
    
12. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB11"', attribute => 'credential_name', value => '"SYS"."CRED1"' );
    
13. sys.dbms_scheduler.enable( '"HR"."JOB11"' );
    
14. END;

复制代码

Linux版本没问题：

1.         
   
2.                         
   
3. BEGIN
   
4. sys.dbms_scheduler.create_job(
   
5. job_name => '"HR"."JOB111"',
   
6. job_type => 'EXECUTABLE',
   
7. job_action => '/home/oracle/c1.sh',
   
8. repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
   
9. start_date => systimestamp at time zone 'Asia/Shanghai',
   
10. end_date => to_timestamp_tz('2017-10-26 19:55:00 Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS TZR'),
    
11. job_class => '"DEFAULT_JOB_CLASS"',
    
12. comments => 'job111',
    
13. auto_drop => FALSE,
    
14. enabled => FALSE);
    
15. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB111"', attribute => 'destination', value => 'station39' );
    
16. DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB111"', attribute => 'credential_name', value => '"SYS"."CRED1"' );
    
17. sys.dbms_scheduler.enable( '"HR"."JOB111"' );
    
18. END;
    

复制代码

远程外部作业（文件的时间属性是格林威志时间）：

1. --SYS:
   
2. 创建远程credential
   
3. begin
   
4.   dbms_scheduler.create_credential(
   
5.         credential_name => 'credential2',
   
6.         username => 'oracle',
   
7.         password => 'oracle');
   
8. end;
   
9. 
   
10. grant execute  on credential2 to hr;
    
11. select  * from dba_scheduler_external_dests;
    
12. --查到STATION41
    
13. begin
    
14.    dbms_scheduler.create_file_watcher(
    
15.    file_watcher_name => 'filewatcher2',
    
16.    directory_path => '/home/oracle/yourfile',
    
17.    file_name =>  'botang*.txt',
    
18.    credential_name => 'credential2',
    
19.    destination => 'STATION41');
    
20. end;
    
21. 
    
22. begin
    
23. DBMS_SCHEDULER.enable('filewatcher2');
    
24. end;
    
25. 
    
26. --em界面做也可以
    
27. By default, file watchers only check for the arrival of files every 10 minutes, but this default interval can be changed by issuing the following statement.
    
28. 
    
29. BEGIN
    
30.   DBMS_SCHEDULER.set_attribute(
    
31.     'file_watcher_schedule',
    
32.     'repeat_interval',
    
33.     'freq=minutely; interval=1');
    
34. END;
    
35. /
    
36.    
    
37. select  * from dba_scheduler_file_watchers;
    
38. grant execute on filewatcher2 to hr;
    
39. 
    
40. --HR:   
    
41. create table hr.tfilewatcher2( a  varchar2(200)  ) ;  
    
42. 
    
43. create or replace procedure hr.procfilewatcher2( p_1  SYS.SCHEDULER_FILEWATCHER_RESULT)
    
44. is
    
45. begin
    
46.   insert into  hr.tfilewatcher2 values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
    
47.                                                       ||p_1.directory_path||'        '
    
48.                                                       ||p_1.actual_file_name||'       '
    
49.                                                       ||p_1.file_size  ) ;
    
50.   commit;
    
51. end;
    
52.   
    
53. begin
    
54.     dbms_scheduler.create_program(
    
55.      program_name => 'HR.program12',
    
56.      program_type => 'STORED_PROCEDURE',
    
57.      program_action => 'HR.procfilewatcher2',
    
58.      number_of_arguments => 1,
    
59.      enabled => false);
    
60. end;
    
61. 
    
62. begin
    
63.    dbms_scheduler.define_metadata_argument(
    
64.    program_name => 'HR.program12',
    
65.    metadata_attribute => 'event_message',argument_position => 1 ) ;
    
66. end;
    
67. 
    
68. begin
    
69.   dbms_scheduler.enable('HR.program12');
    
70. end;
    
71. 
    
72. BEGIN
    
73.   DBMS_SCHEDULER.CREATE_JOB(
    
74.    jOB_NAME=> 'hr.job12',
    
75.    PROGRAM_NAME=> 'hr.program12',
    
76.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
    
77.    QUEUE_SPEC=> 'filewatcher2',
    
78.    AUTO_DROP=> FALSE,
    
79.    ENABLED=> true);
    
80. END;
    
81. 
    
82. begin
    
83.    dbms_scheduler.set_attribute('hr.job12','parallel_instances',true);
    
84. end;
    
85. 
    
86. select  * from  hr.tfilewatcher2;

复制代码

远程数据库作业（12c有bug）：

1. select * from dba_scheduler_dests;
   
2. 
   
3. select  * from dba_scheduler_external_dests;
   
4. 
   
5. select  * from dba_scheduler_db_dests;
   
6. 
   
7. begin
   
8.    dbms_scheduler.drop_agent_destination('STATION199');
   
9. end;   
   
10. 
    
11. begin
    
12.    dbms_scheduler.create_credential('cred2','hr','oracle_4U');
    
13. end;
    
14. 
    
15. grant execute on cred2 to hr;
    
16. 
    
17. select  * from dba_scheduler_credentials;
    
18. 
    
19. begin
    
20.    dbms_scheduler.create_database_destination(
    
21.    destination_name => 'remotedb39',
    
22.    agent => 'station39',tns_name => 'pdb1_1');
    
23. end;
    
24. 
    
25. grant all on remotedb39 to hr;
    
26. 
    
27. 
    
28. select  * from dba_Tab_privs t where t.grantee='HR';
    
29. 
    
30. BEGIN
    
31. sys.dbms_scheduler.create_job(
    
32. job_name => 'hr.job131',
    
33. job_type => 'PLSQL_BLOCK',
    
34. job_action => 'begin
    
35.    update employees set salary=salary+1  where employee_id=100;
    
36.    commit;
    
37. end;',
    
38. start_date => systimestamp at time zone 'Asia/Shanghai',
    
39. job_class => '"DEFAULT_JOB_CLASS"',
    
40. comments => 'JOB131',
    
41. auto_drop => FALSE,
    
42. credential_name =>'"SYS"."CRED2"' ,destination_name =>'sys.remotedb39' ,
    
43. enabled => true);
    
44. END;

复制代码

作业组：

1. select  * from dba_scheduler_groups;
   
2. 
   
3. select  * from dba_scheduler_group_members;
   
4. 
   
5. ----
   
6. 
   
7. BEGIN
   
8.   DBMS_SCHEDULER.create_group(
   
9.     group_name    => 'hr.group1',
   
10.     group_type    => 'EXTERNAL_DEST',
    
11.     member => 'sys.cred1@LOCAL,sys.cred1@sys.station39'
    
12.     );
    
13. END;
    
14. 
    
15. ---
    
16. select  * from dba_scheduler_groups g where g.group_name='GROUP1';
    
17. select  * from dba_scheduler_group_members gm where gm.group_name='GROUP1';
    
18. 
    
19. 
    
20. ----
    
21. 
    
22. BEGIN
    
23.    DBMS_SCHEDULER.CREATE_JOB (
    
24.        job_name            =>  'hr.JOB14',
    
25.        job_type            =>  'executable',
    
26.        job_action          =>  '/home/oracle/job2.sh',
    
27.        start_date          =>  SYSTIMESTAMP,
    
28.        repeat_interval     =>  'freq=minutely; bysecond=0',
    
29.        end_date            =>  SYSTIMESTAMP + 1/24,
    
30.        credential_name     =>  'sys.cred1',
    
31.        destination_name    =>  'group1',
    
32.        enabled             =>  TRUE);
    
33. END;

复制代码