11g File Watcher

http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm
http://www.oracle-base.com/articles/11g/scheduler-enhancements-11gr2.php
http://awads.net/wp/2011/03/29/did-you-know-about-file-watchers/


A file watcher is a new scheduler object that enables a new type of even-based job that is triggered by the arrival of a file in a specified location. File watchers can be defined to monitor locations on the local server and remote servers, provided they have an agent installed on them.

We want oracle to import all the objects belonging to HR schema automatically whenever a .dmp file arrives in a specified folder on a system.
 To implement it we will use filewatcher which is a new scheduler object in oracle 11g. It  enables a new type of event-based job that is triggered by the arrival of a file in a specified location. File watchers can be defined to monitor locations on the local server and remote servers, provided they have an agent installed on them.

I am loading a file to a oracle table every day between 8 a.m and 9 a.m. The file may come at any time between 8 a.m to 9 a.m.
As of now, we are manually checking the file availability and then loading the file into oracle table.
My client is asking us to automate this process. My team is wondering if there is any way we can schedule based on file arrival in a specified location.


SELECT file_watcher_name, destination, directory_path, file_name, credential_name FROM user_scheduler_file_watchers;

SELECT file_watcher_name, destination, directory_path, file_name, credential_name FROM dba_scheduler_file_watchers;

SELECT * FROM   DBA_scheduler_credentials;

/* Configuration */
-- by default, a destination is checked every 10 mins.
To change this:
CONN / AS SYSDBA

set serveroutput on
declare v varchar2(1000);
begin
DBMS_SCHEDULER.GET_ATTRIBUTE ( 'FILE_WATCHER_SCHEDULE','REPEAT_INTERVAL', v);
DBMS_OUTPUT.PUT_LINE(V);
end;
/

BEGIN
DBMS_SCHEDULER.set_attribute('file_watcher_schedule','repeat_interval','freq=minutely;interval=1');
END;
/

/* Creating File Watcher */
-- create OS credential:

BEGIN
DBMS_SCHEDULER.create_credential(credential_name => 'fw_credential',username => 'Administrator',password => 'adminadmin@123');
END;
/

-- Grant EXECUTE on the credential to the schema that owns the
-- event-based job that the file watcher will start:


GRANT EXECUTE ON fw_credential to LDBO;

-- create file watcher:

BEGIN
DBMS_SCHEDULER.create_file_watcher(
file_watcher_name => 'data_fw',
directory_path => 'd:\ldoutput',  -- if '?' = ORACLE_HOME
file_name => '*.dat', -- wildcard supported
credential_name => 'fw_credential',
destination => NULL, -- NULL=local server
enabled => FALSE);
END;
/


-- Grant EXECUTE on the file watcher to any schema that owns an event-based job-- that references the file watcher.

GRANT EXECUTE ON data_fw to LDBO;

-- create a program raised by the file watcher

BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'import_data_prog',
program_type => 'stored_procedure',
program_action => 'import_data_proc',
number_of_arguments => 1,
enabled => FALSE);
END;
/

-- define the metadata argument using the event_message attribute-- the metadata contains info about the file, such as its name:

BEGIN
DBMS_SCHEDULER.define_metadata_argument(
program_name => 'import_data_prog',
metadata_attribute => 'event_message',
argument_position => 1);
END;
/

-- create the defined procedure:-- It must accept an argument of the SCHEDULER_FILE WATCHER_RESULT type

DROP TABLE received_files ;

CREATE TABLE received_files ( fileinfo VARCHAR2(4000), rdate date );

CREATE OR REPLACE PROCEDURE import_data_proc
(p_sfwr SYS.SCHEDULER_FILEWATCHER_RESULT) AS
v_message received_files.fileinfo%type;
BEGIN
v_message := p_sfwr.directory_path || '\' || p_sfwr.actual_file_name || ' (' ||p_sfwr.file_size || ')';
INSERT INTO received_files VALUES (v_message, sysdate);
COMMIT;
END;
/

-- create the job:

BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'import_data_job',
program_name => 'import_data_prog',
event_condition => NULL,      -- 'tab.user_data.file_size < 1024'
queue_spec => 'data_fw', -- file watcher name
auto_drop => FALSE,
enabled => FALSE);
END;
/

-- By default, the arrival of new files will be ignored if the job is already running.-- If you need the job to fire for each new arrival, regardless of whether the job isalready-- running or not, set the PARALLEL_INSTANCES attribute for the job to true. The job-- will then be run as a lightweight job:

BEGIN
DBMS_SCHEDULER.set_attribute('import_data_job','parallel_instances',TRUE);
END;
/

-- Enable all the objects:

EXEC DBMS_SCHEDULER.enable('data_fw,import_data_prog,import_data_job');

-- to test:
echo "This is a test" > D:\LDOUTPUT\f1.dat
echo "This is a test too" > D:\LDOUTPUT\f2.dat
echo "Yes another test" > D:\LDOUTPUT\f3.dat

select * from received_files order by rdate desc;

/* Managing File Watchers */
-- enable/disable
EXEC DBMS_SCHEDULER.enable('data_fw') ;
EXEC DBMS_SCHEDULER.disable('data_fw') ;

-- change an attribute:

begin
DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'data_fw',attribute =>'directory_path',value =>'/home/oracle/receivedfiles' ) ;
end;
/

beginDBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'data_fw',
attribute =>'file_name',
value =>'*.txt' ) ;
end;
/

begin
DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'data_fw',attribute =>'credential_name',value =>'fw_credential2' ) ;
end;
/

-- to drop a file watchers:

EXEC DBMS_SCHEDULER.DROP_job('import_data_job');

EXEC DBMS_SCHEDULER.DROP_program('import_data_prog');

EXEC DBMS_SCHEDULER.DROP_FILE_WATCHER('data_fw');


BEGIN
DBMS_SCHEDULER.drop_credential(credential_name => 'fw_credential');
END;
/



--------------Enable the file watcher, the program, and the job

BEGIN
   DBMS_SCHEDULER.ENABLE('data_fw,import_data_prog,import_data_job');
END;
/




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

SELECT * FROM Dba_Scheduler_Job_Run_Details ORDER BY 1 DESC;

ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_ISCHED", line 3696
ORA-06512: at "SYS.DBMS_ISCHED", line 3907

ORA-29538 ORA-06512 FILE WATCHER

select * from product_component_version;
select comp_name,status from dba_registry;
select object_name from dba_objects where object_type like '%JAVA%' ;


set feedback on termout off
spool C:\install_java.log
@D:\app\Administrator\product\11.2.0\dbhome_1\javavm/install\initjvm.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\xdk\admin\initxml.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\xdk\admin\xmlja.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\catjava.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\catexf.sql
spool off

-------------Remove JVM:
SQL> @?/javavm/install/jvmrm.sql


-----------------------------Installing JVM on Oracle Database 11gR2
JVM was not installed on our Oracle 11gR2 database and we installed it manually by executing below steps from sqlplus login directly on the server [please follow all th steps]:

spool /home/oracle/ODMRINSTL/JVM/jvm_install.txt
set echo on
connect / as sysdba
shutdown immediate
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
shutdown immediate
set echo off
spool off
exit

Verify with below queries & outputs:

SQL> select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;

OWNER COUNT(*)
------ ----------
SYS 21313
EXFSYS 48

SQL> select comp_id,comp_name from dba_registry where comp_id in('CATJAVA','JAVAVM');

COMP_ID COMP_NAME
------- ------------------------------
JAVAVM JServer JAVA Virtual Machine
CATJAVA Oracle Database Java Packages

We have followed above for our case. For details and uninstallation please go through MOS Note:1112983.1 and prepare steps for your case.

-----------------------------------------------jssu.exe error on server

Problem signature:
  Problem Event Name: APPCRASH
  Application Name: jssu.exe
  Application Version: 0.0.0.0
  Application Timestamp: 4bb1e0d1
  Fault Module Name: StackHash_cc43
  Fault Module Version: 6.0.6001.18538
  Fault Module Timestamp: 4cb73957
  Exception Code: c0000374
  Exception Offset: 00000000000a7857
  OS Version: 6.0.6001.2.1.0.274.10
  Locale ID: 1033
  Additional Information 1: cc43
  Additional Information 2: dcf9b9d4873b0ed461caccd3a40f2997
  Additional Information 3: f0eb
  Additional Information 4: 0e4fbfefd1abdaa97da3f3d79632520f

Read our privacy statement:
  http://go.microsoft.com/fwlink/?linkid=50163&clcid=0x0409




ORA-29540: class schedFileWatcherJava does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 3744
ORA-06512: at "SYS.DBMS_ISCHED", line 3956