Oracle Jobs scheduled in the past

we had an issue with the scheduled jobs, there is job scheduled to move the listener logs to another 


destination, unfortunately this job was failing for the past few days. Its last successful run was on 15 July. 




This Job's successfull run was on 15th July 2011, the next scheduled run was on 16th July, since the job didn't start on 16th July, the subsequent days it failed.


  1* select job_name,status,REQ_START_DATE from DBA_SCHEDULER_JOB_RUN_DETAILS where

JOB_NAME='MOVE_LISTENER_LOG'order by req_start_date
SQL> /

MOVE_LISTENER_LOG
SUCCEEDED
24-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
25-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
26-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG

SUCCEEDED
27-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
28-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
29-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED

30-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
01-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
02-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
03-JUL-11 09.14.59.000000 AM -08:00


MOVE_LISTENER_LOG
SUCCEEDED
04-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
05-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
06-JUL-11 09.14.59.000000 AM -08:00


MOVE_LISTENER_LOG
SUCCEEDED
07-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
08-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
09-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG

SUCCEEDED
10-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
11-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
12-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED

13-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
14-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
15-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
FAILED
17-JUL-11 12.45.04.863661 PM -08:00


MOVE_LISTENER_LOG
FAILED
17-JUL-11 12.51.15.624930 PM -08:00

MOVE_LISTENER_LOG
FAILED
19-JUL-11 09.49.17.716590 AM -08:00

MOVE_LISTENER_LOG
FAILED
19-JUL-11 09.50.14.282872 AM -08:00


26 rows selected.

when we checked our next run date, it was in the past.(i.e., the sysdate is 24 July) 23 July


SQL> select   JOB_NAME,FAILURE_COUNT,NEXT_RUN_DATE,END_DATE from  dba_scheduler_jobs where job_name='MOVE_LISTENER_LOG';


MOVE_LISTENER_LOG     0    23-JUL-11 09.14.59.600000 AM -08:00


SQL> SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='MOVE_LISTENER_LOG';

DBA_USER
MOVE_LISTENER_LOG
TRUE

SQL> select owner,job_name,ENABLED,state, next_run_date from dba_scheduler_jobs where job_name='MOVE_LISTENER_LOG';

DBA_USER
MOVE_LISTENER_LOG
TRUE            SCHEDULED
23-JUL-11 09.14.59.600000 AM -08:00


SQL> exec dbms_scheduler.run_job(job_name=>'DBA_USER.MOVE_LISTENER_LOG', use_current_session=>FALSE);

PL/SQL procedure successfully completed.

Still the Job didn't start even after force it to run using run_job procedure. It still shows 15th July as the successful run date

SQL> select OWNER,JOB_NAME,STATE,LAST_START_DATE from dba_scheduler_jobs where JOB_NAME='MOVE_LISTENER_LOG';

DBA_USER
MOVE_LISTENER_LOG
SCHEDULED
15-JUL-11 09.14.59.097255 AM -08:00


SQL>  select job_name,status,REQ_START_DATE from DBA_SCHEDULER_JOB_RUN_DETAILS where

JOB_NAME='MOVE_LISTENER_LOG';

MOVE_LISTENER_LOG
FAILED
17-JUL-11 12.51.15.624930 PM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
29-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
01-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG

SUCCEEDED
02-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
03-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
04-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED

05-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
06-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
07-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
08-JUL-11 09.14.59.000000 AM -08:00


MOVE_LISTENER_LOG
SUCCEEDED
09-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
10-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
11-JUL-11 09.14.59.000000 AM -08:00


MOVE_LISTENER_LOG
SUCCEEDED
12-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
13-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
14-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG

SUCCEEDED
15-JUL-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
24-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
25-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED

28-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
26-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
27-JUN-11 09.14.59.000000 AM -08:00

MOVE_LISTENER_LOG
SUCCEEDED
30-JUN-11 09.14.59.000000 AM -08:00


MOVE_LISTENER_LOG
FAILED
17-JUL-11 12.45.04.863661 PM -08:00

MOVE_LISTENER_LOG
FAILED
19-JUL-11 09.49.17.716590 AM -08:00

MOVE_LISTENER_LOG
FAILED
19-JUL-11 09.50.14.282872 AM -08:00


26 rows selected.

SQL> select sysdate from dual;

24-JUL-11

SQL> select   JOB_NAME,FAILURE_COUNT,NEXT_RUN_DATE,END_DATE from  dba_scheduler_jobs where

job_name='MOVE_LISTENER_LOG';


MOVE_LISTENER_LOG     0    23-JUL-11 09.14.59.600000 AM -08:00

The next scheduled date is on 23 Jul-11, that is yesterday. So we need to change the next scheduled time. for that sake, we need to disable and enable the particular job.

-- to Disable a job
exec dbms_scheduler.disable('MOVE_LISTENER_LOG',true);

-- to enable a job
exec dbms_scheduler.enable('MOVE_LISTENER_LOG');



SQL> select owner,job_name,ENABLED,state, next_run_date from dba_scheduler_jobs where

job_name='MOVE_LISTENER_LOG';

OWNER   JOB_NAME   ENABLED     STATE   NEXT_RUN_DATE
----------------------------------------------------------------------------------------------------
DBA_USER MOVE_LISTENER_LOG TRUE       SCHEDULED     25-JUL-11 09.14.59.800000 AM -08:00

Oracle Scheduler Jobs

First we will be creating the program, and then the scheduler afterwards a job:

SQL> begin
  2  dbms_scheduler.create_program(
  3  program_name=>'test',
  4  program_type=>'PLSQL_BLOCK',
  5  program_action=>'begin dbms_stats.gather_schema_stats(''scott''); end;',
  6  enabled=>TRUE,
  7  comments=>'program to gather scott''s statistics using a pl/sql block.');
  8* end;


PL/SQL procedure successfully completed.

SQL> SELECT owner, program_name, enabled FROM dba_scheduler_programs;

OWNER                          PROGRAM_NAME                   ENABLED
----------------                -----------------------               --------------
SYS                                 TEST                                          TRUE


Creating Hourly Scheduler 

SQL> begin
  2  dbms_scheduler.create_schedule(
  3  schedule_name=>'test_hourly',
  4  start_date=>systimestamp,
  5  repeat_interval=>'freq=hourly; byminute=0',
  6  end_date=>NULL,
  7  comments=>'repeats forever');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Creating Scheduler that runs every minute.

SQL> begin
  2  dbms_scheduler.create_schedule(
  3  schedule_name=>'test_min',
  4  start_date=>systimestamp,
  5  repeat_interval=>'freq=minutely,interval=5',
  6  end_date=>NULL,
  7  comments=>'Repeat every 5 mins');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Now, Creating a job with the name Test_job and associating the program which is already created and scheduling it according to the schedule(Minutely or hourly)

SQL> begin
  2  dbms_scheduler.create_job(
  3  job_name=>'TEST_JOB',
  4  program_name=>'TEST',
  5  schedule_name=>'test_min',
  6  enabled=>TRUE,
  7  comments=>'job defined by an existing program and schedule.');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select owner,job_name,enabled from dba_scheduler_jobs where Job_name='TEST_JOB'

OWNER         JOB_NAME          ENABLED
--------------------------------------------------
SYS                TEST_JOB              TRUE




This query will show the job name and the last or recently executed date with timestamp

SQL> select job_name,last_start_date from dba_scheduler_jobs where job_name='TEST_JOB';

JOB_NAME        LAST_START_DATE
---------------      --------------------------------------------
TEST_JOB          24-JUL-11 08.16.53.477586 AM +05:30



  1* select LOG_ID,JOB_NAME,STATUS from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_JOB'
SQL> /

    LOG_ID      JOB_NAME   STATUS
    ----------    ---------------  ----------
      131            TEST_JOB      SUCCEEDED

      142            TEST_JOB      SUCCEEDED

      143            TEST_JOB      SUCCEEDED

Migration of Non-ASM to ASM

[oracle@server1 orcl]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jul 23 10:40:27 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (not mounted)

RMAN>  restore controlfile from '/u01/orcl/control01.ctl';

Starting restore at 23-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+NORDG/orcl/control.ctl
Finished restore at 23-JUL-11

RMAN> mount database;

database mounted
released channel: ORA_DISK_1



RMAN> backup as copy database format '+nordg';

Starting backup at 23-JUL-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/orcl/system01.dbf
output file name=+NORDG/orcl/datafile/system.261.757248091 tag=TAG20110723T104131 RECID=2 STAMP=757248162
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/orcl/sysaux01.dbf
output file name=+NORDG/orcl/datafile/sysaux.260.757248167 tag=TAG20110723T104131 RECID=3 STAMP=757248223
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/orcl/example01.dbf
output file name=+NORDG/orcl/datafile/example.262.757248233 tag=TAG20110723T104131 RECID=4 STAMP=757248243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/orcl/undotbs01.dbf
output file name=+NORDG/orcl/datafile/undotbs1.259.757248247 tag=TAG20110723T104131 RECID=5 STAMP=757248251
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+NORDG/orcl/controlfile/backup.257.757248255 tag=TAG20110723T104131 RECID=6 STAMP=757248256
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/orcl/users01.dbf
output file name=+NORDG/orcl/datafile/users.263.757248259 tag=TAG20110723T104131 RECID=7 STAMP=757248259
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 23-JUL-11
channel ORA_DISK_1: finished piece 1 at 23-JUL-11
piece handle=+NORDG/orcl/backupset/2011_07_23/nnsnf0_tag20110723t104131_0.264.757248259 tag=TAG20110723T104131 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-JUL-11

RMAN> exit


Recovery Manager complete.
[oracle@server1 orcl]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jul 23 10:45:58 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1285027665, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+NORDG/orcl/datafile/system.261.757248091"
datafile 2 switched to datafile copy "+NORDG/orcl/datafile/sysaux.260.757248167"
datafile 3 switched to datafile copy "+NORDG/orcl/datafile/undotbs1.259.757248247"
datafile 4 switched to datafile copy "+NORDG/orcl/datafile/users.263.757248259"
datafile 5 switched to datafile copy "+NORDG/orcl/datafile/example.262.757248233"

RMAN> exit


Recovery Manager complete.
[oracle@server1 orcl]$
[oracle@server1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 23 10:46:32 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 560291 generated at 07/23/2011 10:35:57 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_2_757246933.dbf
ORA-00280: change 560291 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/arch/1_2_757246933.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/u01/arch/1_2_757246933.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter database open resetlogs;

Database altered.

SQL> select file_name,file_id,tablespace_name from dba_temp_files;

FILE_NAME               FILE_ID TABLESPACE_NAME
--------------------    ------- -------------------
/u01/orcl/temp01.dbf     1       TEMP


SQL> alter database tempfile '/u01/orcl/temp01.dbf' drop including datafiles;

Database altered.


SQL> alter tablespace temp add tempfile '+extdg/temp1.dbf' size 20m;

Tablespace altered.

SQL> select file_name,file_id,tablespace_name from dba_temp_files;

FILE_NAME               FILE_ID TABLESPACE_NAME
--------------------    ------- -------------------
+EXTDG/temp1.dbf         1       TEMP



SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 UNUSED
         3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>  select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 ACTIVE
         3 CURRENT

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance orcl (thread 1)
ORA-00312: online log 1 thread 1: '/u01/orcl/redo01.log'

This error occurs when the checkpoint is on this logfile. so we need to advance the checkpoint from the logfile.


SQL> alter system checkpoint global;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '+nordg' size 10m;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 INACTIVE
         3 CURRENT

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '+nordg' size 10m;

Database altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 UNUSED
         2 UNUSED
         3 CURRENT

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.


SQL> alter system checkpoint global;

System altered.


SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '+nordg' size 10m;

Database altered.


SQL>  select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group# = b.group#;

    GROUP# MEMBER                                  BYTES
---------- ------------------------------       ----------
         3 +NORDG/orcl/onlinelog/group_3. 10485760
           269.757249297

         2 +NORDG/orcl/onlinelog/group_2.   10485760
           270.757249363

         1 +NORDG/orcl/onlinelog/group_1.   10485760
           268.757249249


SQL>
SQL>
SQL>
SQL>

SQL> create pfile from spfile
  2  ;

File created.

SQL> create spfile='+nordg/spfileorcl.ora' from pfile='/crs/app/oracle/product/11.1.0/dbs/initorcl.ora';

File created.