远程数据库作业

botang

orcl:

1. select  * from dba_users order by username;
   
2. 
   
3. create user hr identified by oracle_4U ;
   
4. 
   
5. grant connect,resource to hr;
   
6. 
   
7. alter user hr quota unlimited on users;
   
8. 
   
9. grant create job to hr;
   
10. 
    
11. select  * from dba_sys_privs  where grantee='HR';
    
12. 
    
13. select  * from dba_role_privs where grantee='HR';

复制代码

准备what部分：
HR用户操作

1.         BEGIN
   
2. DBMS_SCHEDULER.CREATE_PROGRAM(
   
3. program_name=>'"HR"."PROGRAM13"',
   
4. program_action=>'begin
   
5.   update spoto.t062ad21_a set a=a+1;
   
6.   commit;
   
7. end;',
   
8. program_type=>'PLSQL_BLOCK',
   
9. number_of_arguments=>0,
   
10. comments=>'',
    
11. enabled=>TRUE);
    
12. END;

复制代码

when部分：
HR用户操作

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"."SCHEDULE13"');
   
6. END;
   

复制代码

where部分：
SYS来操作
prerequitise: 在目的地机器上装好scheduler agent（已经做好），如果有防火墙要把1025打来接受连接。

改参数前：

1. [oracle@classroom ~]$ lsnrctl services
   
2. 
   
3. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-12月-2020 20:15:56
   
4. 
   
5. Copyright (c) 1991, 2014, Oracle.  All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=classroom.example.com)(PORT=1521)))
   
8. Services Summary...
   
9. Service "+ASM" has 1 instance(s).
   
10.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
11.     Handler(s):
    
12.       "DEDICATED" established:77 refused:0 state:ready
    
13.          LOCAL SERVER
    
14. Service "em12rep" has 1 instance(s).
    
15.   Instance "em12rep", status READY, has 1 handler(s) for this service...
    
16.     Handler(s):
    
17.       "DEDICATED" established:4404 refused:0 state:ready
    
18.          LOCAL SERVER
    
19. Service "em12repXDB" has 1 instance(s).
    
20.   Instance "em12rep", status READY, has 1 handler(s) for this service...
    
21.     Handler(s):
    
22.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
23.          DISPATCHER <machine: classroom.example.com, pid: 9504>
    
24.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=14089))
    
25. Service "orcl" has 1 instance(s).
    
26.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
27.     Handler(s):
    
28.       "DEDICATED" established:1112 refused:0 state:ready
    
29.          LOCAL SERVER
    
30. Service "orcl2" has 1 instance(s).
    
31.   Instance "orcl2", status READY, has 1 handler(s) for this service...
    
32.     Handler(s):
    
33.       "DEDICATED" established:1391 refused:0 state:ready
    
34.          LOCAL SERVER
    
35. Service "orclXDB" has 1 instance(s).
    
36.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
37.     Handler(s):
    
38.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
39.          DISPATCHER <machine: classroom.example.com, pid: 28683>
    
40.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=23035))
    
41. Service "rcat.example.com" has 1 instance(s).
    
42.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
43.     Handler(s):
    
44.       "DEDICATED" established:1170 refused:0 state:ready
    
45.          LOCAL SERVER
    
46. Service "rcatXDB.example.com" has 1 instance(s).
    
47.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
48.     Handler(s):
    
49.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
50.          DISPATCHER <machine: classroom.example.com, pid: 10384>
    
51.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=31917))
    
52. The command completed successfully
    
53. [oracle@classroom ~]$
    

复制代码

1. alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=3)';

复制代码

1. [oracle@classroom ~]$ lsnrctl services
   
2. 
   
3. LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-12月-2020 20:19:43
   
4. 
   
5. Copyright (c) 1991, 2014, Oracle.  All rights reserved.
   
6. 
   
7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=classroom.example.com)(PORT=1521)))
   
8. Services Summary...
   
9. Service "+ASM" has 1 instance(s).
   
10.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
11.     Handler(s):
    
12.       "DEDICATED" established:78 refused:0 state:ready
    
13.          LOCAL SERVER
    
14. Service "em12rep" has 1 instance(s).
    
15.   Instance "em12rep", status READY, has 1 handler(s) for this service...
    
16.     Handler(s):
    
17.       "DEDICATED" established:4452 refused:0 state:ready
    
18.          LOCAL SERVER
    
19. Service "em12repXDB" has 1 instance(s).
    
20.   Instance "em12rep", status READY, has 1 handler(s) for this service...
    
21.     Handler(s):
    
22.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
23.          DISPATCHER <machine: classroom.example.com, pid: 9504>
    
24.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=14089))
    
25. Service "orcl" has 1 instance(s).
    
26.   Instance "orcl", status READY, has 4 handler(s) for this service...
    
27.     Handler(s):
    
28.       "DEDICATED" established:1126 refused:0 state:ready
    
29.          LOCAL SERVER
    
30.       "D001" established:0 refused:0 current:0 max:1022 state:ready
    
31.          DISPATCHER <machine: classroom.example.com, pid: 24983>
    
32.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=23793))
    
33.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
34.          DISPATCHER <machine: classroom.example.com, pid: 28683>
    
35.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=23035))
    
36.       "D002" established:1 refused:0 current:1 max:1022 state:ready
    
37.          DISPATCHER <machine: classroom.example.com, pid: 24985>
    
38.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=21427))
    
39. Service "orcl2" has 1 instance(s).
    
40.   Instance "orcl2", status READY, has 1 handler(s) for this service...
    
41.     Handler(s):
    
42.       "DEDICATED" established:1406 refused:0 state:ready
    
43.          LOCAL SERVER
    
44. Service "orclXDB" has 1 instance(s).
    
45.   Instance "orcl", status READY, has 0 handler(s) for this service...
    
46. Service "rcat.example.com" has 1 instance(s).
    
47.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
48.     Handler(s):
    
49.       "DEDICATED" established:1185 refused:0 state:ready
    
50.          LOCAL SERVER
    
51. Service "rcatXDB.example.com" has 1 instance(s).
    
52.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
53.     Handler(s):
    
54.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
55.          DISPATCHER <machine: classroom.example.com, pid: 10384>
    
56.          (ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=31917))
    
57. The command completed successfully
    
58. [oracle@classroom ~]$
    

复制代码

1. alter system set shared_servers=3;

复制代码

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> @?/rdbms/admin/prvtrsch.plb
   
4. 
   
5. PL/SQL procedure successfully completed.
   
6. 
   
7. 
   
8. PL/SQL procedure successfully completed.
   
9. 
   
10. 
    
11. PL/SQL procedure successfully completed.
    
12. 
    
13. 
    
14. no rows selected
    
15. 
    
16. 
    
17. Package created.
    
18. 
    
19. 
    
20. Package body created.
    
21. 
    
22. No errors.
    
23. 
    
24. PL/SQL procedure successfully completed.
    
25. 
    
26. 
    
27. PL/SQL procedure successfully completed.
    
28. 
    
29. 
    
30. User altered.
    
31. 
    
32. 
    
33. User altered.
    
34. 
    
35. 
    
36. 1 row updated.
    
37. 
    
38. 
    
39. Commit complete.
    
40. 
    
41. 
    
42. Grant succeeded.
    
43. 
    
44. 
    
45. Procedure created.
    
46. 
    
47. No errors.
    
48. 
    
49. Audit policy altered.
    
50. 
    
51. 
    
52. Function created.
    
53. 
    
54. 
    
55. Procedure created.
    
56. 
    
57. 
    
58. Procedure created.
    
59. 
    
60. 
    
61. Procedure created.
    
62. 
    
63. 
    
64. Procedure created.
    
65. 
    
66. 
    
67. Procedure created.
    
68. 
    
69. 
    
70. Procedure created.
    
71. 
    
72. No errors.
    
73. 
    
74. Procedure created.
    
75. 
    
76. No errors.
    
77. 
    
78. Procedure created.
    
79. 
    
80. No errors.
    
81. 
    
82. PL/SQL procedure successfully completed.
    
83. 
    
84. 
    
85. Commit complete.
    
86. 
    
87. 
    
88. PL/SQL procedure successfully completed.
    
89. 
    
90. 
    
91. Commit complete.
    
92. 
    
93. SQL>
    

复制代码

走了上面的脚本，多一个用户：
REMOTE_SCHEDULER_AGENT    98        EXPIRED & LOCKED    13-DEC-20    13-DEC-20    USERS    TEMP    13-DEC-20    DEFAULT    DEFAULT_CONSUMER_GROUP            N    PASSWORD    N    NO        N

1. BEGIN
   
2. DBMS_XDB.SETHTTPPORT(8888);
   
3. END;
   
4. /
   

复制代码

1. BEGIN
   
2. DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
   
3. END;
   
4. /
   

复制代码

1. begin
   
2. dbms_scheduler.create_credential( 'credential1','spoto','oracle_4U'  );
   
3. end;
   
4. /

复制代码

---------------------------------------------------------------------------------------------------------
where部分的目的地机器操作：

1. [oracle@classroom ~]$ cd /u01/app/oracle/product/12.1.0/client_1/bin/
   
2. [oracle@classroom bin]$ ./schagent -h
   
3. Oracle Scheduler Agent V 12.1.0.1.1 Usage
   
4. 
   
5. -registerdatabase databaseHost databasePort
   
6. Register with a database.
   
7. 
   
8. -unregisterdatabase databaseHost databasePort
   
9. Unregister from a database.
   
10. 
    
11. -start
    
12. Start the Scheduler agent
    
13. 
    
14. -status
    
15. Get the current status of this agent
    
16. 
    
17. -stop
    
18. Stop the Scheduler agent gracefully
    
19. 
    
20. -abort
    
21. Stop the Scheduler agent forcefully
    
22. 
    
23. [oracle@classroom bin]$ ./schagent -stop
    
24. 
    
25. Agent not running or agent.pid file removed
    
26. 
    
27. [oracle@classroom bin]$ ./schagent -registerdatabase classroom.example.com 8888
    
28. Agent Registration Password ? ***********
    
29. *
    
30. Oracle Scheduler Agent Registration for 12.1 Agent
    
31. Agent Registration Successful!
    
32. [oracle@classroom bin]$ ./schagent -start
    
33. Scheduler agent started
    

复制代码

在orcl验证:
sys运行：

1. set serveroutput on;
   
2. 
   
3. 
   
4. DECLARE
   
5.   versionnum VARCHAR2(30);
   
6. BEGIN
   
7.   versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('classroom.example.com');
   
8. DBMS_OUTPUT.PUT_LINE(versionnum);
   
9. END;
   
10. /

复制代码

anonymous block completed
12.1.0.1.1

1. select  * from dba_scheduler_external_dests;

复制代码

SYS    CLASSROOM    classroom.example.com    1025    172.25.250.254    TRUE    Registered on 13-DEC-20 08.49.31.646611 PM PRC

--------------------------------------------------------------------------
在orcl机器上准备tnsnames.ora，使其能够tnsping通orcl2

/u01/app/oracle/product/12.1.0/dbhome_2/network/admin

1. ORCL2 =
   
2.   (DESCRIPTION =
   
3.     (ADDRESS = (PROTOCOL = TCP)(HOST = classroom.example.com)(PORT = 1521))
   
4.     (CONNECT_DATA =
   
5.       (SERVER = DEDICATED)
   
6.       (SERVICE_NAME = orcl2)
   
7.     )
   
8.   )
   

复制代码

1. begin
   
2.    dbms_scheduler.create_database_destination(
   
3.    destination_name => 'orcl2',
   
4.    agent => 'CLASSROOM',tns_name => 'orcl2');
   
5. end;
   
6. /

复制代码

把where部分授权给hr:

1. grant execute on credential1 to hr;
   
2. 
   
3. grant all on orcl2 to hr;
   
4. 
   
5. grant all on classroom to hr;

复制代码

1. select  * from dba_tab_privs where grantee='HR';

复制代码

HR    SYS    CREDENTIAL1    SYS    EXECUTE    NO    NO    NO    UNKNOWN
HR    SYS    CLASSROOM    SYS    ALTER    NO    NO    NO    DESTINATION
HR    SYS    ORCL2    SYS    ALTER    NO    NO    NO    DESTINATION


---------------------------------------------------------------------------------------------------------------------
从界面上取下来以后并不能运行（书上也是不对的）：

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

复制代码

Perform the following tasks to create a remote job:
1. Set up the originating database for remote jobs.
2. Create the job by using DBMS_SCHEDULER.CREATE_JOB.
3 Create a credential by using
3.DBMS_SCHEDULER.CREATE_CREDENTIAL.
4. Set the job CREDENTIAL_NAME attribute by using
DBMS_SCHEDULER.SET_ATTRIBUTE.
5. Set the job DESTINATION attribute by using
DBMS_SCHEDULER.SET_ATTRIBUTE
DBMS_SCHEDULER.SET_ATTRIBUTE.
6. Enable the job by using DBMS_SCHEDULER.ENABLE.
正确的方式：

1. BEGIN
   
2. sys.dbms_scheduler.create_job(
   
3. job_name => '"HR"."JOB13"',
   
4. program_name => '"HR"."PROGRAM13"',
   
5. schedule_name => '"HR"."SCHEDULE13"',
   
6. job_class => '"DEFAULT_JOB_CLASS"',
   
7. auto_drop => FALSE,
   
8. enabled => true,
   
9. destination_name => 'sys.orcl2',
   
10. credential_name=> 'SYS.CREDENTIAL1' );
    
11. END;
    
12. /

复制代码