Archive

Archive for the ‘linux’ Category

Oracle Jobs

If you are want to schedule a job in the Oracle Database to execute a certain task regularly, basically you can use one of these methods:

1. Oracle Scheduler (DBMS_SCHEDULER):

DBMS_SCHEDULER is a powerful feature that you can use to schedule jobs on the database, which can be very useful for certain DBA tasks like indexes rebuilding, backups checking or data consistency.

To view the information about currently running jobs, you can execute this query:

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

With this query you can check all the scheduled jobs into the database:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

It is a very strong feature and very recommended currently. If you want to find more information about the scheduler, check the Oracle documentation in this link:

https://docs.oracle.com/database/121/ARPLS/d_sched.htm

2. Oracle Jobs (DBMS_JOB):

DBMS_JOB is a deprecated feature but you can still use it even in Oracle 12c, even if it started in Oracle 7!

The DBMS JOB feature is a lot less intuitive to use, although some DBAs state that it is more flexible. In any case, either if you want to take advantage of its flexibility or if you need to understand how it works because your database already uses this feature, it is another option.
You can find the Oracle documentation here:

https://docs.oracle.com/database/121/ARPLS/d_job.htm#ARPLS66577

And a very good and practical post explaining how to use DBMS_JOB:

http://www.orafaq.com/node/871
3. Crontab (Unix/Linux) / Windows Task Scheduler

The third option is to externalize the task execution to the Operating System, so that the tasks are executed through a .sh in Unix, or a .bat in Windows, executing later a .sql to the database.

This method is easier to manage if you are already using crontab or Task Scheduler for other tasks not related with the database, but the disadvantage is that it is node dependent so it can be a problem in the case of Oracle RAC.
Do you know any other method to schedule jobs in Oracle? What is your prefered one?

Advertisements

Removing an Oracle Instance in silent mode

February 14, 2011 3 comments

This post will be simple but really efective. If you want to delete an instance in silent mode, without using any GUI, you just have to write the following as oracle user:

 

dbca -silent -deleteDatabase -sourceDB YOUR_DATABASE

 

I hope you will find this command as useful as it is for me 🙂

Categories: linux, oracle Tags: ,

Grid Control: EMD upload error

December 2, 2010 Leave a comment

Today I was installing the grid control agent in a new server, and I probably mistyped the password during the installation. I didn’t notice at the start, but the client was succesfully installed. However, it didn’t appear on my grid control maintenance window.

I connected to the brand new server and issued the following command:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl upload agent

And it produced this error:

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

This error was caused because I typed a bad password, and in order to change it, I had to remove first the password:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl unsecure agent

And then, securing the agent again:

/u01/app/oracle/product/grid_agent/agent10g/bin/emctl secure agent

It asked me for a new password, which I typed well this time, and everything started working fine.

Rlwrap in SQL*Plus to keep a command history

April 26, 2010 1 comment

If you regularly use sqlplus then you will probably miss a command history. Well, you can solve this problem by using the rlwrap command, which keeps a history for every command you used.

To use rlwrap, just install it from apt, yum or download id from the following url:

http://utopia.knoware.nl/%7Ehlub/uck/rlwrap/

The current version is 0.36.

To use it, after you have installed the tool, just add an alias in your .bash_profile the following way:

alias rlsqlplus=’rlwrap sqlplus’

This way, every time you call sqlplus in the future, you will keep a really useful history of your commands for future reference. I find this tool very useful, for example for correcting mistyped commands.

I don’t recommend using alias sqlplus=’rlwrap sqlplus’, because rlwrap doesn’t allow to execute commands in a interactive way. So your scheduled scripts by the method sqlplus<<EOF may fail. Be careful!

But if you use alias rlsqlplus=’rlwrap sqlplus’, then your problem will be solved.

You can also use rlwrap with other commands, like RMAN:

alias rlrman=’rlwrap rman’

Enjoy!

Categories: linux, oracle