Pages

Tuesday 21 August 2012

AUTOMATIC IMPORT USING FILEWATCHER


                       DATA IMPORT IN DATABASE USING FILE WATCHER
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.
-- OVERVIEW --
- Setup
- Create a file watcher
- Create a scheduler program to access the event raised by the file watcher.
-  Create directory object named FILE_WATCHER which maps to  os path '/tmp/test'
- Copy the .dmp file in /tmp/test location
- Check that all objects belonging to HR schema have been imported into database.
- IMPLEMENTATION -
- SETUP -
HR>CREATE TABLE TEST AS SELECT * FROM EMPLOYEES;

$mkdir /u01/test
$mkdir /tmp/test

SYS>CREATE DIRECTORY ABC AS '/u01/test';
    GRANT READ, WRITE ON DIRECTORY ABC TO HR;
Now  export hr schema to a dump file .
$ expdp hr/hr directory=abc dumpfile=hr_Schema.dmp logfile=abc.log reuse_dumpfiles=true
-- CREATE FILE WATCHER
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.
CONN / AS SYSDBA

BEGIN
  DBMS_SCHEDULER.set_attribute(
    'file_watcher_schedule',
    'repeat_interval',
    'freq=minutely; interval=1');
END;
/
I've set the interval to 1 minute for these tests, but I would probably not set it that low in a production system.
The file watcher will need access to the OS to check for files, so we must create a credential for it to log on with. I'm going to use the Oracle software owner, but for a real system I would advise using the lowest privileged user possible.
BEGIN
  DBMS_SCHEDULER.create_credential(
    credential_name => 'local_credential',
    username        => 'oracle',
    password        => 'oracle');
END;
/
Next we create the file watcher itself. Using a "?" in the directory_path parameter signifies the ORACLE_HOME. The file_name parameter can reference a specific file name or a wildcard. Setting the destination parameter to NULL indicates the local server. For remote servers set it to a valid external destination, as shown by the ALL_SCHEDULER_EXTERNAL_DESTS view.
BEGIN
  DBMS_SCHEDULER.create_file_watcher(
    file_watcher_name => 'test_file_watcher',
    directory_path    => '/tmp/test',
    file_name         => '*.dmp',
    credential_name   => 'local_credential',
    destination       => NULL,
    enabled           => FALSE);
END;
/
-- CREATE A SCHEDULER PROGRAM TO ACCESS THE EVENT RAISED BY FILE WATCHER
 The program must reference the event_message to retrieve information about the file, such as its name.Through a metadata argument, the Scheduler can pass to an event-based job the message content of the event that started the job. The following rules apply
  • The job must use a named program of type STORED_PROCEDURE.
  • One of the named program's arguments must be a metadata argument with metadata_attribute set to EVENT_MESSAGE.
  • The stored procedure that implements the program must have an argument at the position corresponding to the named program's metadata argument. The argument type must be the data type of the queue where your application queues the job-start event.
If you use the RUN_JOB procedure to manually run a job that has an EVENT_MESSAGE metadata argument, the value passed to that argument is NULL.
BEGIN
  DBMS_SCHEDULER.create_program(
    program_name        => 'file_watcher_test_prog',
    program_type        => 'stored_procedure',
    program_action      => 'file_watcher_test_proc',
    number_of_arguments => 1,
    enabled             => FALSE);
END;
/

BEGIN
  DBMS_SCHEDULER.define_metadata_argument(
    program_name       => 'file_watcher_test_prog',
    metadata_attribute => 'event_message',
    argument_position  => 1);
END;
/
Next we define the stored procedure that we referenced in the program definition. It must accept and argument of the SCHEDULER_FILEWATCHER_RESULT type. the procedure created here will import all the hr user objects from export file in hr user schema 
create or replace procedure file_watcher_test_proc(p_sfwr sys.scheduler_filewatcher_result)
as
file_name varchar2(98);
handle number;
begin
file_name:=p_sfwr.actual_file_name;
handle := dbms_datapump.open('IMPORT','SCHEMA');
      dbms_datapump.add_file(handle,file_name,'FILE_WATCHER');
      dbms_datapump.metadata_filter(handle,'SCHEMA_EXPR','= ''HR''');
      dbms_datapump.set_parallel(handle,4);
      dbms_datapump.start_job(handle);
      dbms_datapump.detach(handle);
end;
/
Next we create a job that references the objects we've just created. The queue_spec parameter is set to the name of the file watcher, while the program_name parameter is set to the name of the program object we defined, not the procedure name.
BEGIN
  DBMS_SCHEDULER.create_job(
    job_name        => 'file_watcher_test_job',
    program_name    => 'file_watcher_test_prog',
    event_condition => NULL,
    queue_spec      => 'test_file_watcher',
    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 is already running or not, set thePARALLEL_INSTANCES attribute for the job to true. The job will then be run as a lightweight job.
BEGIN
  DBMS_SCHEDULER.set_attribute('file_watcher_test_job','parallel_instances',TRUE);
END;
/
With all the pieces in place, we can now enable all the objects we created.
EXEC DBMS_SCHEDULER.enable('test_file_watcher');
EXEC DBMS_SCHEDULER.enable('file_watcher_test_prog');
EXEC DBMS_SCHEDULER.enable('file_watcher_test_job');
-  CREATE DIRECTORY OBJECT NAMED FILE_WATCHER WHICH MAPS TO OS PATH  '/tmp/test'
create directory file_watcher as '/tmp/test';
Information about file watchers is available from the *_SCHEDULER_FILE_WATCHERS views.
SET LINESIZE 100
COLUMN file_watcher_name FORMAT A20
COLUMN destination FORMAT A15
COLUMN directory_path FORMAT A15
COLUMN file_name FORMAT A10
COLUMN credential_name FORMAT A20

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

FILE_WATCHER_NAME    DESTINATION     DIRECTORY_PATH  FILE_NAME  CREDENTIAL_NAME
-------------------- --------------- --------------- ---------- --------------------
TEST_FILE_WATCHER                    /tmp/test       *.txt      LOCAL_CREDENTIAL

SQL>
-- TESTING
- Drop test table from hr schema and verify that the table does not exist
HR>DROP TABLE TEST PURGE;
   SELECT TABLE_NAME
    FROM USER_TABLES
   WHERE TABLE_NAME = 'TEST';
no rows selected

Now  copy  hr schema dumpfile to directory /tmp/test

$cp  /u01/test/hr_Schema.dmp /tmp/test/hr_sch.dmp
- Verify that the table test has been imported automatically.
HR> SELECT TABLE_NAME
    FROM USER_TABLES
   WHERE TABLE_NAME = 'TEST';
TABLE_NAME
------------------------------
TEST

---------------------------------------------------------------------------------------------
Related links :

HOME

Friday 3 August 2012

Applications Database

For more information on Applications Database visit :
http://asteriskora.blogspot.in/


---------------------------------------------------------------------------------------------
Related links :

HOME