11 Replies Latest reply: Aug 3, 2014 9:53 PM by user10285268 RSS

    Timestamp issue in oracle schedule jobs

    user10285268

      I wrote a procedure to dump dba_audit_trail information to a flat file and once it done purge the sys.audit table. Execute the procedure manually it dump the data correctly to flat file. But it execute as a schedule job TIMESTAMP is not correct. TIMESTAMP is 6 hours less than TIMESTAMP in dba_audit_trail table.

      Table content

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

      31-07-2014 07:26:08,EOMCBOFD,CFNET\CBOEDPS5,Administrator,CBOEDPS5,LOGON,,

      31-07-2014 07:26:24,EOMCBOFD,CFNET\CBOEDPS5,Administrator,CBOEDPS5,LOGOFF BY CLEANUP,,

      Flat file content

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

      31-07-2014 01:26:08,EOMCBOFD,CFNET\CBOEDPS5,Administrator,CBOEDPS5,LOGON,,

      31-07-2014 01:26:24,EOMCBOFD,CFNET\CBOEDPS5,Administrator,CBOEDPS5,LOGOFF BY CLEANUP,,

        • 1. Re: Timestamp issue in oracle schedule jobs
          Martin Preiss

          what timezone do you see for start_date in dba_scheduler_jobs?

          • 2. Re: Timestamp issue in oracle schedule jobs
            user10285268

            Hi

            Thank you for the quick reply.

             

            SQL> select dbms_scheduler.stime from dual;

            STIME

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

            31-JUL-14 03.27.26.531309000 AM CST6CDT

            SQL> select start_date from dba_scheduler_jobs where PROGRAM_NAME='PURGE_LOG_PROG';

             

            START_DATE

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

            08-NOV-07 03.00.00.600000 AM PST8PDT

            I think, you requested this information am I right? If not please advise me to get such......

             

            SQL> select DBTIMEZONE from dual;

            DBTIME

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

            +00:00

             

            Thank you

            • 3. Re: Timestamp issue in oracle schedule jobs
              Martin Preiss

              I am not sure if I have a clear understanding of your problem: it is quite simple to get the scheduler to start at unexpected times since the start_date may include a timezone information that controls the following executions: DBMS_SCHEDULER "Start Dates and Repeat Intervals The Scheduler retrieves the date and time from the job or schedule start date and incorporates them as defaults into the repeat_interval. For example, if the specified frequency is yearly and there is no BYMONTH or BYMONTHDAY clause in the repeat interval, then the month and day that the job runs on are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY clause in the repeat interval, then the day of the month that the job runs on is retrieved from the start date. If present, BYHOUR, BYMINUTE, and BYSECOND defaults are also retrieved from the start date, and used if those clauses are not specified."

               

              But if you have indeed different data in the audit table and in a dumped file then I don't see a simple explanation.

              • 4. Re: Timestamp issue in oracle schedule jobs
                user10285268

                Yes as you said the scheduled job is executing correctly in specified time. But the dumped file content are different.

                I tested this scenario in test database also but the result is same. Once I manually execute the job the dumped file content correct.

                 

                job script is

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

                DECLARE

                  X NUMBER;

                BEGIN

                  SYS.DBMS_JOB.SUBMIT

                  ( job       => X

                   ,what      => 'SYS.DDL_AUDIT_LOG_PURGE;'

                   ,next_date => to_date('31/07/2014 00:00:00','dd/mm/yyyy hh24:mi:ss')

                   ,interval  => 'SYSDATE+1 '

                   ,no_parse  => FALSE

                  );

                  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));

                 

                COMMIT;

                • 5. Re: Timestamp issue in oracle schedule jobs
                  GregV

                  Hi,

                   

                  What is the result of:

                   

                  select sessiontimezone from dual;


                  Are you using DBMS_JOB or DBMS_SCHEDULER?

                  • 6. Re: Timestamp issue in oracle schedule jobs
                    spajdy

                    Give us code of you dump procedure so we can run it on our DB.

                    • 7. Re: Timestamp issue in oracle schedule jobs
                      user10285268

                      SQL> select sessiontimezone from dual;

                       

                      SESSIONTIMEZONE

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

                      +06:00

                       

                      DBMS_JOB as above

                       

                      Through the cron job it works fine

                      • 8. Re: Timestamp issue in oracle schedule jobs
                        user10285268

                        CREATE OR REPLACE PROCEDURE SYS.ddl_audit_log_purge AS
                        CURSOR cur_date IS`enter code here`
                          
                        SELECT sysdate FROM dual;

                          rec1 cur_date
                        %ROWTYPE;
                          created_file_name VARCHAR2
                        (100);
                          file_name UTL_FILE
                        .FILE_TYPE;

                          
                        BEGIN
                          
                        OPEN cur_date;
                          LOOP
                         
                        FETCH cur_date INTO rec1;
                         
                        EXIT WHEN cur_date%NOTFOUND;
                          created_file_name
                        :=rec1.sysdate;
                          file_name
                        :=UTL_FILE.FOPEN('AUD_DIR','cfcbo_'||created_file_name||'.exp','W');

                          
                        FOR rec in(
                          
                        SELECT to_char(TIMESTAMP, 'dd-mm-yyyy HH24:MI:SS')
                          
                        ||','||username
                          
                        ||','||userhost
                          
                        ||','||os_username
                          
                        ||','||terminal
                          
                        ||','||action_name
                          
                        ||','||owner 
                          
                        ||','||obj_name
                          out_line
                          
                        FROM dba_audit_trail)
                          LOOP
                          UTL_FILE
                        .PUTF(file_name,'%s\n',rec.out_line);
                          UTL_FILE
                        .FFLUSH(file_name);
                          
                        END LOOP;
                          UTL_FILE
                        .FCLOSE(file_name);
                         
                        END LOOP;
                         
                        CLOSE cur_date;
                         
                        insert into aud_log_job (now,task) values(sysdate,'exported audit table'); 
                         
                        execute immediate 'truncate table sys.aud$'; 
                         
                        insert into aud_log_job(now,task) values(sysdate,'truncate audit table'); 
                         
                        END;

                        • 9. Re: Re: Timestamp issue in oracle schedule jobs
                          GregV

                          Hi,

                           

                          The job will use a sessiontimezone set to UTC, hence the 6 hours less. In your procedure, you'll need to alter the session to set the proper time zone:

                           

                          execute immediate 'alter session set time_zone = ''+06:00''';


                          One more thing, do not create procedures in the SYS schema, it's not recommended (This schema belongs to Oracle and is meant only for Oracle objects).

                          • 10. Re: Timestamp issue in oracle schedule jobs
                            user10285268

                            Respect your answer. dba_audit_trail and sys.aud$ are belongs to sys. That is the reason I place the procedure in sys schema. If not I have to give select and delete permission to another user to sys schema table.

                            Ill try your opton

                             

                            • 11. Re: Re: Timestamp issue in oracle schedule jobs
                              user10285268

                              Thank you. This is working.