RAC Database Upgradation(11,1,0.6.0 to 11.1.0.7.0)

Steps to upgrade the RAC Database:
----------------------------------
Itz a two node RAC database. before upgrading the database, we need to upgrade the clusterware, that is successfully done on both the participating nodes.
CLUSTERWARE SOFTWARE VERSION:11.1.0.7.0
CLUSTERWARE ACTIVE VERSION:11.1.0.7.0
ORACLE S/W VERSION: 11.1.0.6.0

Before going ahead on anything like upgradation we should have a rollback strategy in plan, Incase if anything fails, and if the failure is unrecoverable then we need to revert back to our original ORACLE_HOME(11.1.0.6.0)
This is the most significant step which most of us would forget to do. Taking backup of the ORACLE_HOME
Now upgrade the ORACLE_HOME to 11.1.0.7.0 before upgrading the database that is running under it.
I'm going to initiate the Upgradation from node1
Step1:Stop all the services running from ORACLE_HOME(Node1)
Step2:srvctl stop instance -d proddb -i proddb1
Step3:srvctl stop asm -n <node1>
It is better to stop the listener, instead we are stopping the node application.
step4:srvctl stop nodeapps -n <node1>

$ cd patchset_directory/Disk1
$ ./runInstaller
STEP1: On the Welcome screen, click Next.
STEP2: In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.
STEP3:Click Next.
STEP4:The Selected Nodes screen appears. The patch set will be installed on these nodes. Click Next.
STEP5:Check for errors, if any, in the Product-Specific Prerequisite Checks and click Next.
STEP6:On the Summary screen, click Install.
It will start the installation on the ORACLE_HOME mentioned above.
Once it is done, it will show a script that we need to execute to complete the installation.
$ORACLE_HOME/root.sh  on all the participating nodes

now our ORACLE_HOME is upgraded to 11.1.0.7.0
itz time to upgrade our RAC database that is running from this ORACLE_HOME
we crossed half ocean, but now we are stepping into a turbulent sea.
STEP1: If using ASM, start the asm of the particular node.
       $srvctl start asm -n <node>
While starting the asm, an error during that process
1) CRS-215 Srvctl unable to start ASM
workaround:
-----------
login as root
./crsctl stop crs
./crsctl start crs
STEP2: stop all the services running from that node.
STEP3: Start the listener service of the particular node.
       $srvctl start listener -n <node>
STEP4: STARTUP UPGRADE
We cannot open our database in normal open mode, we will get an error
ORA-01092: ORACLE instance terminated. Disconnection forced
for more details open the alert log, to find the following error
ORA-39700: database must be opened with UPGRADE option .

STEP5: Execute the Pre-upgrade tool utlu111i.sql
       i.e., $ORACLE_HOME/rdbms/admin/utlu111i.sql
After executing the pre-upgrade tool it will show the recommendation along with that i received couple of invalid components, which i felt would be rectified once upgraded to 11.1.0.7.0
 or
we can execute the script $ORACLE_HOME/rdbms/admin/catrul.sql
Invalid objects in the components:
--> Rule Manager                 [upgrade]  INVALID

STEP6: It will display some changes we need to make before going ahead with the upgradation, like parameter changes and the components validity etc., We need to change all those requirements before accelerating our database towards the newer version.

STEP7: startup nomount to change an important parameter CLUSTER_DATABASE=FALSE,
       It would be TRUE by default in RAC installation.
STEP8: shutdown the database
STEP9: Startup Upgrade
STEP10:spool the proceeding from the sql prompt: spool cat_upgrade.log
STEP11:Execute the script catupgrd.sql to upgrade all the database component to          the newer version
       sql>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
STEP12:Check for the errors after execution of the above command. Open the spool         file to check for any errors, If any errors found re-run the catupgrd.sql
When i executed the following sql statement to find any invalid components, coz during the execution of pre-upgrade script i found some components were invalid.
select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)
      comp_id,substr(version,1,12) version,status from dba_registry;
to my surprise all the components shown invalid were valid except below one
COMP_NAME                      COMP_ID    VERSION      STATUS
------------------------------ ---------- ------------ -----------
OWB                            OWB        11.1.0.6.0   VALID
Oracle Application Express     APEX       3.0.1.00.12  VALID
Oracle Enterprise Manager      EM         11.1.0.7.0   VALID
Oracle Ultra Search            WK         11.1.0.7.0   VALID
OLAP Catalog                   AMD        11.1.0.7.0   VALID
Spatial                        SDO        11.1.0.7.0   VALID
Oracle Multimedia              ORDIM      11.1.0.7.0   INVALID
Oracle XML Database            XDB        11.1.0.7.0   VALID
Oracle Text                    CONTEXT    11.1.0.7.0   VALID
Oracle Expression Filter       EXF        11.1.0.7.0   VALID
Oracle Rules Manager           RUL        11.1.0.7.0   VALID
COMP_NAME                      COMP_ID    VERSION      STATUS
------------------------------ ---------- ------------ -----------
Oracle Workspace Manager       OWM        11.1.0.7.0   VALID
Oracle Database Catalog Views  CATALOG    11.1.0.7.0   VALID
Oracle Database Packages and T CATPROC    11.1.0.7.0   VALID
JServer JAVA Virtual Machine   JAVAVM     11.1.0.7.0   VALID
Oracle XDK                     XML        11.1.0.7.0   VALID
Oracle Database Java Packages  CATJAVA    11.1.0.7.0   VALID
OLAP Analytic Workspace        APS        11.1.0.7.0   VALID
Oracle OLAP API                XOQ        11.1.0.7.0   VALID
Oracle Real Application Cluste RAC        11.1.0.7.0   VALID
SQL> select object_type,object_name, owner from dba_objects
  where status ='INVALID'
  order by owner, object_name, object_type;
OBJECT_TYPE         OBJECT_NAME                    OWNER
------------------- ------------------------------ --------
PACKAGE BODY        ORD_DICOM_ADMIN_PRV            ORDSYS
SQL> alter package ORDSYS.ORD_DICOM_ADMIN_PRV compile body;

Warning: Package Body altered with compilation errors.
SQL> SQL> sho errors
Errors for PACKAGE BODY ORDSYS.ORD_DICOM_ADMIN_PRV:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3824/7   PL/SQL: SQL Statement ignored
3826/11  PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
3839/9   PL/SQL: SQL Statement ignored
3841/13  PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
3854/9   PL/SQL: SQL Statement ignored
3856/13  PL/SQL: ORA-00932: inconsistent datatypes: expected - got -
SQL>
It is a bug, so we need to apply an interim Generic patch while upgrading from 11.1.0.6.0 to 11.1.0.7.0
After applying it we need to execute the scripts again, in order
SQL> startup upgrade
ORACLE instance started.
..
SQL> @?/rdbms/admin/catxdbv.sql
View created.

Grant succeeded.

Synonym created.

Comment created.

Comment created.
..
..
SQL> @?/rdbms/admin/catupgrd.sql
..
..
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************
SQL>
SQL>

We applied it and after it checked again for the invalid component, Thank God! it was not found.
COMP_NAME                      COMP_ID    VERSION      STATUS
------------------------------ ---------- ------------ -----------
OWB                            OWB        11.1.0.6.0   VALID
Oracle Application Express     APEX       3.0.1.00.12  VALID
Oracle Enterprise Manager      EM         11.1.0.7.0   VALID
Oracle Ultra Search            WK         11.1.0.7.0   VALID
OLAP Catalog                   AMD        11.1.0.7.0   VALID
Spatial                        SDO        11.1.0.7.0   VALID
Oracle Multimedia              ORDIM      11.1.0.7.0   VALID
Oracle XML Database            XDB        11.1.0.7.0   VALID
Oracle Text                    CONTEXT    11.1.0.7.0   VALID
Oracle Expression Filter       EXF        11.1.0.7.0   VALID
Oracle Rules Manager           RUL        11.1.0.7.0   VALID
COMP_NAME                      COMP_ID    VERSION      STATUS
------------------------------ ---------- ------------ -----------
Oracle Workspace Manager       OWM        11.1.0.7.0   VALID
Oracle Database Catalog Views  CATALOG    11.1.0.7.0   VALID
Oracle Database Packages and T CATPROC    11.1.0.7.0   VALID
JServer JAVA Virtual Machine   JAVAVM     11.1.0.7.0   VALID
Oracle XDK                     XML        11.1.0.7.0   VALID
Oracle Database Java Packages  CATJAVA    11.1.0.7.0   VALID
OLAP Analytic Workspace        APS        11.1.0.7.0   VALID
Oracle OLAP API                XOQ        11.1.0.7.0   VALID
Oracle Real Application Cluste RAC        11.1.0.7.0   VALID

STEP13:After execution of this script the database would be automatically down,        startup the database in  normal mode to execute utlrp.sql
STEP14:execute the script utlrp.sql to check for the invalid object and compile        it. 
       sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql
After the execution of UTLRP.sql script, we found an error.
SQL> @?/rdbms/admin/utlrp

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
                          0
DECLARE
*
ERROR at line 1:
ORA-00904: "E"."OBJNUM": invalid identifier
ORA-06512: at line 31

ORDIM INVALID OBJECTS: ORD_DICOM_ADMIN_PRV - 5 - 11
ORDIM registered 0 XML schemas.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
The workaround we did for this is:
SQL> sho user
USER is "SYS"
SQL> desc sys.enabled$indexes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHEMANAME                                NOT NULL VARCHAR2(30)
 INDEXNAME                                 NOT NULL VARCHAR2(30)
SQL> drop table sys.enabled$indexes;
Table dropped.
SQL> CREATE TABLE sys.enabled$indexes( schemaname, indexname, objnum )AS select u.name, o1.name, i.obj# from user$ u, obj$ o1, obj$ o2, ind$ i where u.user# = o1.owner# and o1.type# = 1 and o1.obj# = i.obj# and bitand(i.property, 16)= 16 and bitand(i.flags, 1024)=0 and i.bo# = o2.obj# and bitand(o2.flags, 2)=0;
Table created.
SQL> @?/rdbms/admin/utlrp

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
                  0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
                          0

PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
SQL>
After much ordeal successfully upgraded database to 11.1.0.7.0

 

RAC Clusterware Upgradation(11,1,0.6.0 to 11.1.0.7.0)

For upgrading the RAC Database, first we need to upgrade the clusterware. This is a  two node RAC installation.

Steps to follow for a clusterware installation:

Note: Always before starting upgradation, take a backup of the ORACLE_HOME, incase upgradation fails, this will be used for downgrading the Oracle Instance back to our original version. i.e.,(11.1.0.7.0------>11.1.0.6.0)

1. Stop the instance  on the particular node, srvctl stop instance -d prod_db -i prod1
2. 
   

   If an Automatic Storage Management instance is present, then shut down the Automatic Storage Management instance on the node on which you intend to perform the rolling upgrade.   "srvctl stop asm -n rac1"
3. 
   

   Stop all the nodeapp services on that node "rac1", nodeapps includes GSD,Listener daemons   "srvctl stop nodeapps -n rac1"

unzip the patchset, there will be a executable file(runInstaller.sh), Execute that it will be followed by a GUI based installation steps:

1.      On the Welcome screen, click Next.

2.      In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.

3. Click Next.

4. For Oracle RAC cluster, the Selected Nodes screen appears. The patch set will be installed on these nodes. Click Next.

5. Check for errors, if any, in the Product-Specific Prerequisite Checks and click Next.

6. On the Summary screen, click Install.


7. On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.

Finally once the installation completes, we will be prompted to run the script "root111.sh"

Clusterware is successfully upgraded.

 $ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.1.0.6.0]

$ crsctl query crs softwareversion
Oracle Clusterware version on node [RAC1] is [11.1.0.7.0]

The above commands is to check the clusterware version, note here the software version is 11.1.0.7.0 that is the upgraded version, whereas the active version is still the older. Once the clusterware is successfully upgraded in other participating node, then the active version will change to 11.1.0.7.0

$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.1.0.7.0]

$ crsctl query crs softwareversion
Oracle Clusterware version on node [RAC2] is [11.1.0.7.0]

RAC Implementation

Hi guyz i would like to share some RAC implementation points

 

First OS should be installed in our nodes, this is will be taken care by the OS Team.

 

For RAC setup, Oracle has provided Clusterware, Oracle Clusterware is the cross platform cluster software required to run the Real Application Clusters (RAC) option for Oracle Database. It provides the basic clustering services at the operating system level that enable Oracle software to run in clustering mode.

 

Important files of Clusterware:-

 

• OCR
• VOTING DISK/VOTING FILE
• Two RAW disk  for OCR and Voting file
• OCFS to place OCR/Voting disk(optional)
• ASM [it is still not available, because ASM binaries comes along with oracle binaries. we didnt' install Oracle yet]
• Oracle Home [Oracle binaries will be stored]

If we need to create a database, storage area should be in place, so that we need to start ASM instance in all the nodes.

In Oracle 11gR2 we can place OCR & Voting File in ASM Diskgroup since ASM binaries comes bundled along with Clusterware binaries that is grid infrastructure services for cluster

Network configuration are done by the network team: Configuring Public IP, Private IP and Virtual IP.

 

Configuration of SSH: 

Whenever we create oracle users in all the participating nodes the groupid, userid should be identical in all the nodes. if we create group or users OS(Operating system) will recognize based on the id. otherwise the cluster installation will fail. Using RSA/DSA(Algorithm we are going to configure SSH from the /home/oracle/.ssh[incidentally the user's home directory]. Once the SSH is configured the password is encrypted using 128 bit encryption algorithm

11gR2----> click to configure SSH automatically(RSA/DSA) copy the public keys from the nodes and paste it or make it available in all the participating nodes.

eg: 2 nodes===>4 public keys

High Level RAC Implementation:

1. Install hte same version of OS in all the participating nodes. 
2. Create the required number of groups and users accounts in all the cluster nodes(groupid and userid should be identical in all the nodes)
3. Create the directory structures for CRS_HOME and DB_HOME in all the cluster nodes
4. Configure Kernel parameters & set the semaphore settings as per oracle  document.
5. set shell limits for oracle user account
6. edit /etc/hosts and specify the public/private ip & Virtual IP for all the cluster nodes
7. Establish trust relationship and user equivalence by configuring SSH
8. create the required number of partitions in the shared storage for OCR, Voting File and ASM Diskgroups
9. if Implementing RAC in Linux using ocfs2 and ASM interface download the corresponding RPM based on the kernel version of the node's OS.
10. ensure the date and time are close as possible in all the cluster nodes.

All the above steps will be configured by the OS Admin and Network Admin with the co-ordination of Oracle DBA. Here starts the work of DBA

1. Install Clusterware in the first node and at the end of the installation if cluvfy fails it is considered as a bug in 10g. Workaround according to the oracle metalink.
2. configure listener
3. configure ASM instance and create diskgroups needed for it
4. Create RAC database in the shared storage area. 

ASM Instance Creation(RAC)

We have two node setup,
RAC1.corp.com--->node1
RAC2.corp.com--->node2

all done, network is pinging across the nodes.

We decided to go with a database that is working in the Oracle's Logical Volume Manager, ASM.

First to setup this we need to create an ASM Instance, while we creating an ASM Instance we received an error which was really frustating for to solve later we found it that this error was because of some human error this has occurred.

In this message box it was clearly mentioning us to execute the above script.

#localconfig delete
#localconfig add

# /u01/app/oracle/product/11.1.0/db_1/bin/localconfig add
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized

Cleaning up Network socket directories
Setting up Network socket directories
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
Giving up: Oracle CSS stack appears NOT to be running.
Oracle CSS service would not start as installed
Automatic Storage Management(ASM) cannot be used until Oracle CSS service is started




when this error occurred giving up: there are two options we can go for try to localconfig add or reset or contact oracle support.

when we browsed for many docs in the oracle metalink, we could not find the one which was suitable for us.

Our team decided to go for  Service Request, but i told them let us check from the first what we did and how we did then we can raise an service request.

when we started our process of rechecking all the steps, I recollected that we got an error at the end of the clusterware installation.

I'm sure that we executed this command, coz i did it on my own[Risk Reduction]. later i told the team to execute the mandatory step root.sh. Actually i told them to execute it few minutes before we got this error. At the heat of the moment they forgot it. But some team members were denying it, later we came to conclusion that we didn't execute this root.sh

Workaround


when we ran root.sh from the /crs_home/bin .. it worked fine!!

then we ran the #localconfig delete

#localconfig add

Now the services evmd and cssd are up and running.

Cheers!!

OCFS2 was not mounting ocfs2_hb_ctl

OCFS--->Oracle cluster file system, it was not mounting. We have two node RAC.

RAC1.corp.com----> first node
RAC2.corp.com----> second node

Here in this configuration ocfs was used to store OCR and Voting disk. Since OCFS2 is a seperate mount  point, it was not mounting because of some error.

when we typed df -h, all the mount points were shown except the /ocfs which was storing OCR & Voting Disk. Without these two files we cannot run the databases. very crucial for the RAC systems.

when we tried to mount the /ocfs manually,
# mount -t /ocfs2 -a
 we found this error

Workaround:


first i checked the network connections between the nodes...


when i pinged the node2 it was not pinging and it is actually getting routed to vip of the local node. then found it because of the device name, changed it later the network was working fine, after that

stopped ocfs:
# ./o2cb stop ocfs2

then started ocfs:
# /etc/init.d/o2cb start

then mounted the ocfs::

mount -t ocfs2 -o datavolume,nointr /dev/sdb1 ocfs

it worked!!!

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