LINUX/UNIX - setup cron jobs

Cron Jobs
Cron is one of the most useful tool in Linux or UNIX like operating systems. The cron service (daemon) runs in the background and constantly checks the /etc/crontab file, /etc/cron.*/ directories. It also checks the /var/spool/cron/ directory.
  1. Create your own shell script/repetitive job (backup script etc...)
  2. Install / Create / Edit  Own Cronjobs
To edit your crontab file, type the following command at the UNIX / Linux shell prompt:$ crontab –e

Now add the your cron job to this file like below
     * * * * * /u01/my.sh ( command to be executed )
Where,
  • 1: Minute (0-59)
  • 2: Hours (0-23)
  • 3: Day (0-31)
  • 4: Month (0-12 [12 == December])
  • 5: Day of the week(0-7 [7 or 0 == sunday])
  • /path/to/command - Script or command name to schedule
Easy to remember format:
* * * * * command to be executed
- - - - -
| | | | |
| | | |  ----- Day of week (0 - 7) (Sunday=0 or 7)
| | |  ------- Month (1 - 12)
| |  --------- Day of month (1 - 31)
|  ----------- Hour (0 - 23)
------------- Minute (0 - 59)

More Examples

To run /path/to/command five minutes after midnight, every day, enter:
5 0 * * * /path/to/command
Run /path/to/script.sh at 2:15pm on the first of every month, enter:
15 14 1 * * /path/to/script.sh
Run /scripts/phpscript.php at 10 pm on weekdays, enter:
0 22 * * 1-5 /scripts/phpscript.php
Run /root/scripts/perl/perlscript.pl at 23 minutes after midnight, 2am, 4am ..., everyday, enter:
23 0-23/2 * * * /root/scripts/perl/perlscript.pl
Run /path/to/unixcommand at 5 after 4 every Sunday, enter:
5 4 * * sun /path/to/unixcommand

Task: List All Your crontab Jobs

Type the following command :
# crontab -l
# crontab -u username -l
To remove or erase all crontab jobs use the following command:
# crontab -r
crontab -r -u username

Use special string to save time

Instead of the first five fields, you can use any one of eight special strings. It will not just save your time but it will improve readability.
Special string
Meaning
@reboot
Run once, at startup.
@yearly
Run once a year, "0 0 1 1 *".
@annually
(same as @yearly)
@monthly
Run once a month, "0 0 1 * *".
@weekly
Run once a week, "0 0 * * 0".
@daily
Run once a day, "0 0 * * *".
@midnight
(same as @daily)
@hourly
Run once an hour, "0 * * * *".
Run ntpdate every hour:
@hourly /path/to/ntpdate
Make a backup everyday:
@daily /path/to/backup/script.sh

Understanding /etc/crontab file and /etc/cron.d/* directories

/etc/crontab is system crontabs file. Usually only used by root user or daemons to configure system wide jobs. All individual user must must use crontab command to install and edit their jobs as described above. /var/spool/cron/ or /var/cron/tabs/ is directory for personal user crontab files. It must be backup with users home directory.

Understanding Default /etc/crontab

Typical /etc/crontab file entries:
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly
First, the environment must be defined. If the shell line is omitted, cron will use the default, which is sh. If the PATH variable is omitted, no default will be used and file locations will need to be absolute. If HOME is omitted, cron will use the invoking users’ home directory.
Additionally, cron reads the files in /etc/cron.d/ directory. Usually system daemon such as sa-update or sysstat places their cronjob here. As a root user or superuser you can use following directories to configure cronjobs. You can directly drop your scripts here. run-parts command run scripts or programs in a directory via /etc/crontab
Directory
Description
/etc/cron.d/
Put all scripts here and call them from /etc/crontab file.
/etc/cron.daily/
Run all scripts once a day
/etc/cron.hourly/
Run all scripts once an hour
/etc/cron.monthly/
Run all scripts once a month
/etc/cron.weekly/
Run all scripts once a week

How do I Use Above Directories To Put My Scripts?

Here is a sample shell script (clean.cache) to clean up cached files every 10 days. This script is directly created at /etc/cron.daliy/ directory i.e. create a file called /etc/cron.daily/clean.cache:
#!/bin/bash
# A sample shell script to clean cached file from lighttpd web server
CROOT="/tmp/cachelighttpd/"
DAYS=10
LUSER="lighttpd"
LGROUP="lighttpd"

# start cleaning
/usr/bin/find ${CROOT} -type f -mtime +${DAYS} | xargs -r /bin/rm

# if directory deleted by some other script just get it back
if [ ! -d $CROOT ]
then
       /bin/mkdir -p $CROOT
       /bin/chown ${LUSER}:${LGROUP} ${CROOT}
fi

How Do I Backup Installed Cronjobs Entries?

Simply type the following command to backup your cronjobs to a nas server mounted at /nas01/backup/cron/users.root.bakup directory:
# crontab -l > /nas01/backup/cron/users.root.bakup
# crontab -u userName -l > /nas01/backup/cron/users.userName.bakup

Start/Stop/Restart cron service
# /etc/init.d/crond start
# /etc/init.d/crond stop
# /etc/init.d/crond restart   

For Troubleshoot look the below location mail
/var/spool/mail/oracle


PL/SQL - LEFT OUTER JOIN with ON condition or WHERE condition ?

LEFT OUTER JOIN with ON condition or WHERE condition?

I would like your explanation of the difference between a LEFT OUTER JOIN ON condition and a WHERE condition

The difference is subtle, but it is a big difference. The ON condition stipulates which rows will be returned in the join, while the WHERE condition acts as a filter on the rows that actually were returned.

Simple example:
Consider a student table, consisting of one row per student, with student id and student name. In a second table, a list of grades that students have received, with student_id, subject, and grade. Give me a list of all students, and show their grade in Math. This requires a LEFT OUTER JOIN, because you want all students, and you know that some of them didn't take Math. Here are two queries:

select name    , grades.grade as math_grade
from students
left outer join grades
on students.id  = grades.student_id
where grades.subject = 'Math'
( most of the people write above query )
--------------------------------------------------------------------------------------------------

select name    , grades.grade as math_grade
from students
left outer join grades
on students.id    = grades.student_id
and grades.subject = 'Math'

Now for the crucial difference: the first query returns only those students who took Math, and those who didn't are not included. 
In the second query, all students are included, and those who took Math have their grade shown.

Why the difference? In the first query, the LEFT OUTER JOIN returns all students, even if they didn't take Math. If they didn't take Math, then the joined row that is returned by the LEFT OUTER JOIN will have NULLs in all the columns from the grades table. But then for each such joined row returned, the WHERE clause comes along and picks only those rows which are Math. And since NULL isn't equal to anything, students who didn't take Math disappear from the results.

In the second query, the join condition means that students who took Math are returned, or else NULL because it's a LEFT OUTER JOIN. So all students are included in the results, because there's no WHERE clause to filter them out. Their Math grade will be their Math grade or else NULL.

In effect, the first query behaves the same as an inner join. Only the matched rows are retained after the WHERE clause has done its job. Why bother returning rows to the WHERE clause that you want filtered out? Make it an INNER JOIN and save some needless processing. Of course, if you do want a LEFT OUTER JOIN, make sure that any filter conditions on the right table are in the ON clause, not the WHERE clause

PL/SQL - get the procedure execution time

Get the procedure execution time

Option 1
 
set timing on
DECLARE
 STATUS VARCHAR2(200);
BEGIN  VALIDATION_PKG.VALIDATE_PERSON_REQUIRED_FIELD( STATUS => STATUS);
DBMS_OUTPUT.PUT_LINE('STATUS = ' || STATUS);
end;

Option 2
 
DECLARE
   time_before BINARY_INTEGER;
   time_after binary_integer;
   STATUS VARCHAR2(200);
BEGIN
   time_before := DBMS_UTILITY.GET_TIME;
   VALIDATION_PKG.VALIDATE_PERSON_REQUIRED_FIELD(STATUS => STATUS);
time_after := DBMS_UTILITY.GET_TIME;
dbms_output.put_line (time_after - time_before);
END;

PL/SQL - LISTAGG function

LISTAGG

Purpose
For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.

E.x

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
    MIN(hire_date) "Earliest"
    FROM employees
    WHERE department_id = 30;

Emp_list                                                                                   Earliest
------------------------------------------------------------                      ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

oracle-12C-configure-db-express

From oracle 12C onward there is no Enterprice manager database controller and EMCTL utility. they have introdused new management console called DB-Express. This artical shows how to configure DB-Express in 12C.
Once you installed 12C you have to manually configure the DB-Experss as given below.


*************** Check if it is enabled via: ***************

[oracle@orcl12 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 11:21:23 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select dbms_xdb_config.gethttpport() from dual;

DBMS_XDB_CONFIG.GETHTTPPORT()
----------------------
     0



*******************Enable, for example on port 8080, and check HTTP access:

SQL> exec dbms_xdb_config.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> select dbms_xdb.gethttpport() from dual;

DBMS_XDB.GETHTTPPORT()
----------------------
  8080

About Listener.ora

Listener.ora

Listener is a utility and act as communication bridge between clients and oracle server, the configuration file resides on the server ,where oracle database installed .

Can configure multiple address ( like multiple ADDRESS entries with different hosts.,port etc..)

PMON register DB_NAME,SID,SERVICE_NAME with the listener dynamically this case listener.ora file like below ( this process call service registration)

NOTE: By default the SERVICE_NAME parameter is set to SID, but we can set multiple values for parameter like below 

SQL>alter system set SERVICE_NAME=DB11G,DB12G

now you can create new Service Naming using DB12G as well (using netmgr), this will store tnsname.ora file

LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oel5box)(PORT = 1521))
    )
 )

Or we can stored these information statistically in the listener.ora and that file look likes below
LISTENER =
 (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oel5box)(PORT = 1521))
    )
 )
SID_LIST_LISTENER =
 (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = DB11G)
     (SID_NAME = DB11G)
     (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
    )
 )


Export and Import schema statistics




You would do this ,in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.

SCHEMA NAME – TEST ( any of your schema name )

Create a table to store the statistics in your production database.
EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);

Export schema statistics in production database– will be stored in the ‘STATS_TABLE’
EXEC DBMS_STATS.export_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);

If required import these statistics back to TEST schema in staging or testing database.
EXEC DBMS_STATS.import_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);

Finally drop the table created to backup the schema stats in production database.
EXEC DBMS_STATS.drop_stat_table(‘TEST’,'STATS_TABLE’);