远程数据库作业
orcl:select* from dba_users order by username;
create user hr identified by oracle_4U ;
grant connect,resource to hr;
alter user hr quota unlimited on users;
grant create job to hr;
select* from dba_sys_privswhere grantee='HR';
select* from dba_role_privs where grantee='HR';准备what部分：
HR用户操作
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'"HR"."PROGRAM13"',
program_action=>'begin
update spoto.t062ad21_a set a=a+1;
commit;
end;',
program_type=>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'',
enabled=>TRUE);
END;
when部分：
HR用户操作
BEGIN
sys.dbms_scheduler.create_schedule(
repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
start_date => systimestamp at time zone 'Asia/Shanghai',
schedule_name => '"HR"."SCHEDULE13"');
END;
where部分：
SYS来操作
prerequitise: 在目的地机器上装好scheduler agent（已经做好），如果有防火墙要把1025打来接受连接。
改参数前：
$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-12月-2020 20:15:56
Copyright (c) 1991, 2014, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=classroom.example.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:77 refused:0 state:ready
LOCAL SERVER
Service "em12rep" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4404 refused:0 state:ready
LOCAL SERVER
Service "em12repXDB" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 9504>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=14089))
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1112 refused:0 state:ready
LOCAL SERVER
Service "orcl2" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1391 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 28683>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=23035))
Service "rcat.example.com" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1170 refused:0 state:ready
LOCAL SERVER
Service "rcatXDB.example.com" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 10384>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=31917))
The command completed successfully
$
alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=3)';
$ lsnrctl services
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-12月-2020 20:19:43
Copyright (c) 1991, 2014, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=classroom.example.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:78 refused:0 state:ready
LOCAL SERVER
Service "em12rep" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4452 refused:0 state:ready
LOCAL SERVER
Service "em12repXDB" has 1 instance(s).
Instance "em12rep", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 9504>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=14089))
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:1126 refused:0 state:ready
LOCAL SERVER
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 24983>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=23793))
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 28683>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=23035))
"D002" established:1 refused:0 current:1 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 24985>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=21427))
Service "orcl2" has 1 instance(s).
Instance "orcl2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1406 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 0 handler(s) for this service...
Service "rcat.example.com" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1185 refused:0 state:ready
LOCAL SERVER
Service "rcatXDB.example.com" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: classroom.example.com, pid: 10384>
(ADDRESS=(PROTOCOL=tcp)(HOST=classroom.example.com)(PORT=31917))
The command completed successfully
$
alter system set shared_servers=3;
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/prvtrsch.plb
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
no rows selected
Package created.
Package body created.
No errors.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
User altered.
User altered.
1 row updated.
Commit complete.
Grant succeeded.
Procedure created.
No errors.
Audit policy altered.
Function created.
Procedure created.
Procedure created.
Procedure created.
Procedure created.
Procedure created.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
PL/SQL procedure successfully completed.
Commit complete.
PL/SQL procedure successfully completed.
Commit complete.
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
BEGIN
DBMS_XDB.SETHTTPPORT(8888);
END;
/
BEGIN
DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
END;
/
begin
dbms_scheduler.create_credential( 'credential1','spoto','oracle_4U');
end;
/---------------------------------------------------------------------------------------------------------
where部分的目的地机器操作：
$ cd /u01/app/oracle/product/12.1.0/client_1/bin/
$ ./schagent -h
Oracle Scheduler Agent V 12.1.0.1.1 Usage
-registerdatabase databaseHost databasePort
Register with a database.
-unregisterdatabase databaseHost databasePort
Unregister from a database.
-start
Start the Scheduler agent
-status
Get the current status of this agent
-stop
Stop the Scheduler agent gracefully
-abort
Stop the Scheduler agent forcefully
$ ./schagent -stop
Agent not running or agent.pid file removed
$ ./schagent -registerdatabase classroom.example.com 8888
Agent Registration Password ? ***********
*
Oracle Scheduler Agent Registration for 12.1 Agent
Agent Registration Successful!
$ ./schagent -start
Scheduler agent started
在orcl验证:
sys运行：
set serveroutput on;
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('classroom.example.com');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
/anonymous block completed
12.1.0.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
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = classroom.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl2)
)
)
begin
dbms_scheduler.create_database_destination(
destination_name => 'orcl2',
agent => 'CLASSROOM',tns_name => 'orcl2');
end;
/
把where部分授权给hr:
grant execute on credential1 to hr;
grant all on orcl2 to hr;
grant all on classroom to hr;
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
---------------------------------------------------------------------------------------------------------------------
从界面上取下来以后并不能运行（书上也是不对的）：
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB13"',
program_name => '"HR"."PROGRAM13"',
schedule_name => '"HR"."SCHEDULE13"',
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => FALSE);
DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB13"', attribute => 'destination', value => 'sys.orcl2' );
DBMS_SCHEDULER.SET_ATTRIBUTE(name => '"HR"."JOB13"', attribute => 'credential_name', value => '"SYS"."CREDENTIAL1"' );
sys.dbms_scheduler.enable( '"HR"."JOB13"' );
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.
正确的方式：
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB13"',
program_name => '"HR"."PROGRAM13"',
schedule_name => '"HR"."SCHEDULE13"',
job_class => '"DEFAULT_JOB_CLASS"',
auto_drop => FALSE,
enabled => true,
destination_name => 'sys.orcl2',
credential_name=> 'SYS.CREDENTIAL1' );
END;
/
页:
[1]