I have a PLSQL application, the application has a oracle job (ABC.AUDIT_LOG_IMPORT_JOB) that is runs for every 10 min and picks the file from the log pool and process it using a oracle procedure (ABC.PR_IMPORT_AUDIT_LOG).
Sometimes very large files (4 to 6 GB) are imported in the log pool, when oracle job(ABC.AUDIT_LOG_IMPORT_JOB) is successfully executed the procedure takes some time to process these files. The procedure uses some external tables.
When the procedure is started then some external table is called to read the data from the file. Since the file is large it takes some time to import these files while the procedure is already started which causes the following error.
KUP-05011: Size of file inProgress.txt in directory /opt/app/logpool/auditlog/ has changed from 3630039040 to 3852599296.
How to solve this problem?
oracle job is as shown below
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ABC.AUDIT_LOG_IMPORT_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'ABC.PR_IMPORT_AUDIT_LOG',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=10',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => '');
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'ABC.AUDIT_LOG_IMPORT_JOB',
attribute => 'store_output', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'ABC.AUDIT_LOG_IMPORT_JOB',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
DBMS_SCHEDULER.enable(
name => 'ABC.AUDIT_LOG_IMPORT_JOB');
END;
the procedure that uses this external table is as shown below
--------------------------------------------------------
-- DDL for Procedure PR_IMPORT_AUDIT_LOG
--------------------------------------------------------
set define off;
CREATE OR REPLACE EDITIONABLE PROCEDURE "ABC"."PR_IMPORT_AUDIT_LOG"
AS
CURSOR CUR_FILE_LIST
IS SELECT FNAME
FROM EXT_TAB_IMPORT_FILE_LIST
WHERE REGEXP_LIKE(FNAME,'AL_\d{2}-\d{2}-\d{2}_\d{8}_\d{6}.txt');
V_INSERT_TIMESTAMP TIMESTAMP := SYSTIMESTAMP;
BEGIN
-- This is to prevent the logs from being created in other than English
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''AMERICAN''';
FOR REC_FILE_LIST IN CUR_FILE_LIST
LOOP
-- Copy the original file to the AL_IMPORTBAD directory
UTL_FILE.FCOPY('ABC_AUDITLOG', FNAME, 'ABC_AL_IMPORTBAD', FNAME||'-Original_file');
--RENAME FILE IN inProgress.txt
UTL_FILE.FRENAME('ABC_AUDITLOG', REC_FILE_LIST.FNAME, 'ABC_AUDITLOG', 'inProgress.txt', TRUE);
IF PR_AL_IMPORT_CHECK_CORIAID (REC_FILE_LIST.FNAME) --If there are any errors, then there are no records with correct records are saved in the AL_IMPORT folder.
THEN
--Insert into audit_log
PR_INSERT_AUDIT_LOG_FROM_FILE(V_INSERT_TIMESTAMP);--Records are inserted from file with correct coridid and during insert just oracle regexp are checked.
--Check log file
PR_CHECK_AUDIT_LOG_FILE(REC_FILE_LIST.FNAME);
--ERROR HANDLING
PR_IMPORT_CHECK_LOG_FILES( REC_FILE_LIST.FNAME
, V_INSERT_TIMESTAMP);
--move inProgress into archive and rename it with the old name
UTL_FILE.FRENAME('ABC_AUDITLOG', 'inProgress.txt', 'ABC_AL_IMPORTLOG', REC_FILE_LIST.FNAME, TRUE);
ELSE
--move inProgress into archive and rename it with the old name
-- UTL_FILE.FRENAME('ABC_AUDITLOG', 'inProgress.txt', 'ABC_AL_IMPORTBAD', REC_FILE_LIST.FNAME, TRUE);
END IF;
--remove inProgress.bad
BEGIN
UTL_FILE.FREMOVE('ABC_AUDITLOG','inProgress.bad');
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -29283 -- suppresses no file found exception
THEN
NULL;
ELSE
RAISE;
END IF;
END;
--remove inProgress.log
BEGIN
UTL_FILE.FREMOVE('ABC_AUDITLOG','inProgress.log');
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -29283 -- suppresses no file found exception
THEN
NULL;
ELSE
RAISE;
END IF;
END;
--remove inProgress.txt
BEGIN
UTL_FILE.FREMOVE('ABC_AUDITLOG','inProgress.txt');
EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -29283 -- suppresses no file found exception
THEN
NULL;
ELSE
RAISE;
END IF;
END;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
V_PROCEDURE_NAME VARCHAR2(30) := $$PLSQL_UNIT;
V_SUBJECT VARCHAR2(255) := 'Error on DB procedure '||V_PROCEDURE_NAME||' on '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss');
V_BODY VARCHAR2(10000) := 'Hi all,'||chr(10)||chr(10)||'Procedure '||V_PROCEDURE_NAME||' returned the following error:'||chr(10)||SQLERRM;
BEGIN
SEND_MAIL ( 'GENERIC_DB_ERROR', V_SUBJECT,V_BODY);
RAISE;
END;
END;
/
the external table that reads the records from the file is as follows
--------------------------------------------------------
-- DDL for Procedure PR_INSERT_AUDIT_LOG_FROM_FILE
--------------------------------------------------------
set define off;
CREATE OR REPLACE EDITIONABLE PROCEDURE "ABC"."PR_INSERT_AUDIT_LOG_FROM_FILE" (P_IMPORT_TIMESTAMP IN TIMESTAMP)
AS
BEGIN
INSERT INTO AUDIT_LOG
( ID
, PRIMARY_NAME
, PRIMARY_VALUE
, TERMINAL_DATE
, LAND_ID
)
SELECT ID
, TRIM(PRIMARY_NAME)
, TRIM(PRIMARY_VALUE)
, TERMINAL_DATE
, LAND_ID
, P_IMPORT_TIMESTAMP
FROM EXT_TAB_AUDIT_LOG AL
LEFT OUTER JOIN VERFIY_NAME VWZ ON VWZ.VERFIY_TABLE_ID = TRIM(AL.VERFIY_TABLE_ID)
WHERE REGEXP_LIKE (ID,'\d{2}-\d{2}-\d{2}')
AND PRIMARY_NAME IS NOT NULL
AND PRIMARY_VALUE IS NOT NULL
AND TERMINAL_DATE IS NOT NULL
AND LAND_ID IS NOT NULL
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DECLARE
V_PROCEDURE_NAME VARCHAR2(30) := $$PLSQL_UNIT;
V_SUBJECT VARCHAR2(255) := 'Error on DB procedure '||V_PROCEDURE_NAME||' on '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss');
V_BODY VARCHAR2(10000) := 'Hi all,'||chr(10)||chr(10)||'Procedure '||V_PROCEDURE_NAME||' returned the following error:'||chr(10)||SQLERRM;
BEGIN
SEND_MAIL ( 'GENERIC_DB_ERROR', V_SUBJECT,V_BODY);
RAISE;
END;
END PR_INSERT_AUDIT_LOG_FROM_FILE;
/