Thursday, February 18, 2010

orainstRoot.sh and root.sh scripts execution in Oracle

Usage of orainstRoot.sh and root.Sh execution in Oracle 10g Installation:

The first Script that we run is orainstRoot.sh which is located in $ORACLE_BASE/oraInventory for example : /u01/opt/app/oracle/oraInventory

The script should be run as root user

The usage of this script is :

It creates the inventory pointer file (/etc/oraInst.loc), The file shows the inventory location and group it is linked to.
Changing groupname of the oraInventory directory to oinstall group.

The second Script is root.sh, which should be run as root user

root.sh script performs many things, namely
It changes or correctly sets the environment variables
copying of few files into /usr/local/bin , the files are dbhome,oraenv,coraenv etc.
creation of /etc/oratab file or adding database home and SID's entry into /etc/oratab file.

root.sh script is located in $ORACLE_HOME directory.

The best way what else operations are performed when we run these script is to pay attention when we execute those and read the script execution log.

hope it help you all a bit.


Cheers...

Friday, February 12, 2010

dbms_job interval time meaning and on how to set interval in dbms_job in pre- 10g databases.

Its been a month having not written a new post in my blog and thought I could post a message to keep my blog update to date and even if it is a trivial info. This post came as outcome of a discussion with a friend on how to change interval time in earlier version of oracle db and here is the post about it with few other additional info.

Simple technique to schedule jobs in oracle 10g is explained in earlier post http://mdvreddy.blogspot.com/2009/11/job-scheduling-in-oracle-10g.html but sometimes we may need to work with pre-10g Oracle databases which do not provide a mechanism to schedule jobs as we do in 10g database or above.

Just for the purpose of completion, I would show how we can create a scheduler job in earlier database (not from 10g onwards) is as follows

declare
testjob number;
begin
dbms_job.submit(job => testjob,
what => 'procedure1(1);'
next_date => sysdate+1,
interval => 'sysdate+1');
end;
/
The simple job creation demo has two date/time related parameters and a procedure execution call. The date related parameters in this call are next_date and interval and which are date datatype or the values we pass can be varchar or char but they should be in such away that they would be evaluated to a date format later. Next_Date should always be greater than or equal to sysdate.

This post especially written to help in setting next_date parameter value of dbms_job.sumbit_job procedure and thought it will help you all as well as it helps me when needed. How to change REPEAT_INTERVAL of scheduler job in oracle 10g can be seen http://mdvreddy.blogspot.com/2009/04/2009.html .

This post is all about how to set interval which meets the various conditions or time set values at which we want job to be executed. The calculations are very simple arithmetic.

SELECT SYSDATE,sysdate + 1/(60*60*24) AS SYSDATE_1SECONDAFTER , sysdate + 1/(60*24) AS SYSDATE_1MINUTEAFTER , sysdate + 30/(60*24) AS SYSDATE_30MINUTESAFTER,
SYSDATE+1/24 AS SYSDATE_HRAFTER ,SYSDATE+1 AS SYSDATE_1DAYAFTER FROM DUAL

The above query is just to show how we can pass date time value to interval and you may use any one the listed in query incase suits.

Few more examples:
Sysdate+1 – same time but tomorrow
Trunk(sysdate+1) – tomorrow, today midnight i.e. just before starting of new day , we can say 12 AM;
trunc(sysdate)+5/24 – 5 am in the morning.

Interval parameter is incremented on each job run and you may set NULL to interval if job should not run any further at all.

DBMS_JOB.CHANGE procedure can be used to change parmaeter values of job.

Final say: go for dbms_scheduler for creation on new jobs and it is rich in all aspects.

Thank for reading and welcome your posts as all the time.

dbms_job interval time meaning and on how to set interval in dbms_job in pre- 10g databases.

Its been a month having not written a new post in my blog and thought I could post a message to keep my blog update to date and even if it is a trivial info. This post came as outcome of a discussion with a friend on how to change interval time in earlier version of oracle db and here is the post about it with few other additional info.

Simple technique to schedule jobs in oracle 10g is explained in earlier post http://mdvreddy.blogspot.com/2009/11/job-scheduling-in-oracle-10g.html but sometimes we may need to work with pre-10g Oracle databases which do not provide a mechanism to schedule jobs as we do in 10g database or above.

Just for the purpose of completion, I would show how we can create a scheduler job in earlier database (not from 10g onwards) is as follows

declare
testjob number;
begin
dbms_job.submit(job => testjob,
what => 'procedure1(1);'
next_date => sysdate+1,
interval => 'sysdate+1');
end;
/
The simple job creation demo has two date/time related parameters and a procedure execution call. The date related parameters in this call are next_date and interval and which are date datatype or the values we pass can be varchar or char but they should be in such away that they would be evaluated to a date format later. Next_Date should always be greater than or equal to sysdate.

This post especially written to help in setting next_date parameter value of dbms_job.sumbit_job procedure and thought it will help you all as well as it helps me when needed. How to change REPEAT_INTERVAL of scheduler job in oracle 10g can be seen http://mdvreddy.blogspot.com/2009/04/2009.html .

This post is all about how to set interval which meets the various conditions or time set values at which we want job to be executed. The calculations are very simple arithmetic.

SELECT SYSDATE,sysdate + 1/(60*60*24) AS SYSDATE_1SECONDAFTER , sysdate + 1/(60*24) AS SYSDATE_1MINUTEAFTER , sysdate + 30/(60*24) AS SYSDATE_30MINUTESAFTER,
SYSDATE+1/24 AS SYSDATE_HRAFTER ,SYSDATE+1 AS SYSDATE_1DAYAFTER FROM DUAL

The above query is just to show how we can pass date time value to interval and you may use any one the listed in query incase suits.

Few more examples:
Sysdate+1 – same time but tomorrow
Trunk(sysdate+1) – tomorrow, today midnight i.e. just before starting of new day , we can say 12 AM;
trunc(sysdate)+5/24 – 5 am in the morning.

Interval parameter is incremented on each job run and you may set NULL to interval if job should not run any further at all.

DBMS_JOB.CHANGE procedure can be used to change parmaeter values of job.

Final say: go for dbms_scheduler for creation on new jobs and it is rich in all aspects.

Thank for reading and welcome your posts as all the time.