第32次活动：2017-11-13（星期一晚上7:00-9:30）

botang · 发表于 2017-11-13 19:25:04

64位Linux要做Filewatcher一定要：

[root@station90 lib64]# ls -l libpam.so*
lrwxrwxrwx. 1 root root 16 10月 6 00:23 libpam.so.0 -> libpam.so.0.82.2
-rwxr-xr-x. 1 root root 55280 7月 10 18:59 libpam.so.0.82.2
[root@station90 lib64]# ln -s libpam.so.0.82.2 libpam.so
[root@station90 lib64]# pwd
/lib64
[root@station90 lib64]#

复制代码

关于总file_watcher_job和原来就有的file_watcher_scheduler（而且都是关闭或disabled）：

--------

SYS:

select * from dba_scheduler_credentials;
select * from dba_scheduler_file_watchers;
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher1',
directory_path => '/home/oracle/myfile',
file_name => 'botang*.txt',
credential_name => 'cred1');
end;
grant execute on filewatcher1 to hr;
grant execute on SYS.SCHEDULER_FILEWATCHER_RESULT to hr;
grant create external job to hr;
grant create job to hr;

复制代码

HR:

create table hr.tfilewatcher1 ( a varchar2(200) ) ;
create or replace procedure hr.procfilewatcher ( p_1 SYS.SCHEDULER_FILEWATCHER_RESULT)
is
begin
insert into hr.tfilewatcher1 values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||' '
||p_1.directory_path||' '
||p_1.actual_file_name||' '
||p_1.file_size ) ;
commit;
end;
----
begin
dbms_scheduler.create_program(
program_name => 'HR.program10',
program_type => 'STORED_PROCEDURE',
program_action => 'HR.procfilewatcher',
number_of_arguments => 1,
enabled => false);
end;
----
begin
dbms_scheduler.define_metadata_argument(
program_name => 'HR.program10',
metadata_attribute => 'event_message',argument_position => 1 ) ;
end;
-----
begin
dbms_scheduler.enable('HR.program10');
end;
----
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
jOB_NAME=> 'hr.job10',
PROGRAM_NAME=> 'hr.program10',
EVENT_CONDITION => 'tab.user_data.file_size > 10',
QUEUE_SPEC=> 'filewatcher1',
AUTO_DROP=> FALSE,
ENABLED=> true);
END;
begin
dbms_scheduler.set_attribute('hr.job10','parallel_instances',true);
end;
select * from hr.tfilewatcher1;

复制代码

Remote Scheduler：

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 13 20:09:24 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter dispa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD
B)
max_dispatchers integer
SQL> alter system set dispatchers=
2 ' (PROTOCOL=TCP) (dispatchers=3)';
System altered.
SQL> show parameter dispa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (dispatchers=3
)
max_dispatchers integer
SQL> !lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-NOV-2017 20:09:58
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=station90.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:259 refused:0 state:ready
LOCAL SERVER
Service "orcl.example.com" has 1 instance(s).
Instance "orcl", status READY, has 4 handler(s) for this service...
Handler(s):
"DEDICATED" established:259 refused:0 state:ready
LOCAL SERVER
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station90.example.com, pid: 27496>
(ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=22919))
"D001" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station90.example.com, pid: 8105>
(ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=13743))
"D002" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station90.example.com, pid: 8110>
(ADDRESS=(PROTOCOL=tcp)(HOST=station90.example.com)(PORT=32283))
Service "orclXDB.example.com" has 1 instance(s).
Instance "orcl", status READY, has 0 handler(s) for this service...
The command completed successfully
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 36909875
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SQL> alter system set shared_servers=3;
System altered.
SQL>

复制代码

走以下脚本会多一个人：

SQL> @?/rdbms/admin/prvtrsch.plb

复制代码

BEGIN
DBMS_XDB.SETHTTPPORT(8888);
END;
SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
BEGIN
DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('buhaoji');
END;

复制代码

组策略：

select * from dba_scheduler_external_dests;
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
grant all on station199 to hr;

复制代码

select * from dba_scheduler_external_dests;
select * from dba_scheduler_dests;
begin
dbms_scheduler.drop_agent_destination(destination_name => 'station14');
end;
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
----
begin
dbms_scheduler.create_file_watcher(
file_watcher_name => 'filewatcher2',
directory_path => 'c:\Users\oracle\yourfile',
file_name => 'botang*.txt',
credential_name => 'cred1',
destination => 'STATION199');
end;
grant execute on filewatcher2 to hr;
select * from hr.tfilewatcher1;
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
jOB_NAME=> 'hr.job12',
PROGRAM_NAME=> 'hr.program10',
EVENT_CONDITION => 'tab.user_data.file_size > 10',
QUEUE_SPEC=> 'filewatcher2',
AUTO_DROP=> FALSE,
ENABLED=> true);
END;
begin
dbms_scheduler.set_attribute('hr.job12','parallel_instances',true);
end;

复制代码