Friday, July 19, 2013

Linux Crontab: 15 Awesome Cron Job Examples

An experienced Linux sysadmin knows the importance of running the routine maintenance jobs in the background automatically.

Linux Cron utility is an effective way to schedule a routine background job at a specific time and/or day on an on-going basis.

This article is part of the on-going Productivity Tips For Geeks series. In this article, let us review 15 awesome examples of crontab job scheduling.

Linux Crontab Format

MIN HOUR DOM MON DOW CMD
Table: Crontab Fields and Allowed Ranges (Linux Crontab Syntax)
Field Description Allowed Value
MIN Minute field 0 to 59
HOUR Hour field 0 to 23
DOM Day of Month 1-31
MON Month field 1-12
DOW Day Of Week 0-6
CMD Command Any command to be executed.

1. Scheduling a Job For a Specific Time

The basic usage of cron is to execute a job in a specific time as shown below. This will execute the Full backup shell script (full-backup) on 10th June 08:30 AM.

Please note that the time field uses 24 hours format. So, for 8 AM use 8, and for 8 PM use 20.
30 08 10 06 * /home/ramesh/full-backup
  • 30 – 30th Minute
  • 08 – 08 AM
  • 10 – 10th Day
  • 06 – 6th Month (June)
  • * – Every day of the week

2. Schedule a Job For More Than One Instance (e.g. Twice a Day)

The following script take a incremental backup twice a day every day.

This example executes the specified incremental backup shell script (incremental-backup) at 11:00 and 16:00 on every day. The comma separated value in a field specifies that the command needs to be executed in all the mentioned time.
00 11,16 * * * /home/ramesh/bin/incremental-backup
  • 00 – 0th Minute (Top of the hour)
  • 11,16 – 11 AM and 4 PM
  • * – Every day
  • * – Every month
  • * – Every day of the week

3. Schedule a Job for Specific Range of Time (e.g. Only on Weekdays)

If you wanted a job to be scheduled for every hour with in a specific range of time then use the following.

Cron Job everyday during working hours

This example checks the status of the database everyday (including weekends) during the working hours 9 a.m – 6 p.m
00 09-18 * * * /home/ramesh/bin/check-db-status
  • 00 – 0th Minute (Top of the hour)
  • 09-18 – 9 am, 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
  • * – Every day
  • * – Every month
  • * – Every day of the week

Cron Job every weekday during working hours

This example checks the status of the database every weekday (i.e excluding Sat and Sun) during the working hours 9 a.m – 6 p.m.
00 09-18 * * 1-5 /home/ramesh/bin/check-db-status
  • 00 – 0th Minute (Top of the hour)
  • 09-18 – 9 am, 10 am,11 am, 12 am, 1 pm, 2 pm, 3 pm, 4 pm, 5 pm, 6 pm
  • * – Every day
  • * – Every month
  • 1-5 -Mon, Tue, Wed, Thu and Fri (Every Weekday)

4. How to View Crontab Entries?

View Current Logged-In User’s Crontab entries

To view your crontab entries type crontab -l from your unix account as shown below.
ramesh@dev-db$ crontab -l
@yearly /home/ramesh/annual-maintenance
*/10 * * * * /home/ramesh/check-disk-space

[Note: This displays crontab of the current logged in user]

View Root Crontab entries

Login as root user (su – root) and do crontab -l as shown below.
root@dev-db# crontab -l
no crontab for root

Crontab HowTo: View Other Linux User’s Crontabs entries

To view crontab entries of other Linux users, login to root and use -u {username} -l as shown below.
root@dev-db# crontab -u sathiya -l
@monthly /home/sathiya/monthly-backup
00 09-18 * * * /home/sathiya/check-db-status

5. How to Edit Crontab Entries?

Edit Current Logged-In User’s Crontab entries

To edit a crontab entries, use crontab -e as shown below. By default this will edit the current logged-in users crontab.
ramesh@dev-db$ crontab -e
@yearly /home/ramesh/centos/bin/annual-maintenance
*/10 * * * * /home/ramesh/debian/bin/check-disk-space
~
"/tmp/crontab.XXXXyjWkHw" 2L, 83C

[Note: This will open the crontab file in Vim editor for editing.
Please note cron created a temporary /tmp/crontab.XX... ]
When you save the above temporary file with :wq, it will save the crontab and display the following message indicating the crontab is successfully modified.
~
"crontab.XXXXyjWkHw" 2L, 83C written
crontab: installing new crontab

Edit Root Crontab entries

Login as root user (su – root) and do crontab -e as shown below.
root@dev-db# crontab -e

Edit Other Linux User’s Crontab File entries

To edit crontab entries of other Linux users, login to root and use -u {username} -e as shown below.
root@dev-db# crontab -u sathiya -e
@monthly /home/sathiya/fedora/bin/monthly-backup
00 09-18 * * * /home/sathiya/ubuntu/bin/check-db-status
~
~
~
"/tmp/crontab.XXXXyjWkHw" 2L, 83C

6. Schedule a Job for Every Minute Using Cron.

Ideally you may not have a requirement to schedule a job every minute. But understanding this example will will help you understand the other examples mentioned below in this article.
* * * * * CMD
The * means all the possible unit — i.e every minute of every hour through out the year. More than using this * directly, you will find it very useful in the following cases.
  • When you specify */5 in minute field means every 5 minutes.
  • When you specify 0-10/2 in minute field mean every 2 minutes in the first 10 minute.
  • Thus the above convention can be used for all the other 4 fields.

7. Schedule a Background Cron Job For Every 10 Minutes.

Use the following, if you want to check the disk space every 10 minutes.
*/10 * * * * /home/ramesh/check-disk-space
It executes the specified command check-disk-space every 10 minutes through out the year. But you may have a requirement of executing the command only during office hours or vice versa. The above examples shows how to do those things.

Instead of specifying values in the 5 fields, we can specify it using a single keyword as mentioned below.

There are special cases in which instead of the above 5 fields you can use @ followed by a keyword — such as reboot, midnight, yearly, hourly.
Table: Cron special keywords and its meaning
Keyword Equivalent
@yearly 0 0 1 1 *
@daily 0 0 * * *
@hourly 0 * * * *
@reboot Run at startup.

8. Schedule a Job For First Minute of Every Year using @yearly

If you want a job to be executed on the first minute of every year, then you can use the @yearly cron keyword as shown below.

This will execute the system annual maintenance using annual-maintenance shell script at 00:00 on Jan 1st for every year.
@yearly /home/ramesh/red-hat/bin/annual-maintenance

9. Schedule a Cron Job Beginning of Every Month using @monthly

It is as similar as the @yearly as above. But executes the command monthly once using @monthly cron keyword.

This will execute the shell script tape-backup at 00:00 on 1st of every month.
@monthly /home/ramesh/suse/bin/tape-backup

10. Schedule a Background Job Every Day using @daily

Using the @daily cron keyword, this will do a daily log file cleanup using cleanup-logs shell scriptat 00:00 on every day.
@daily /home/ramesh/arch-linux/bin/cleanup-logs "day started"

11. How to Execute a Linux Command After Every Reboot using @reboot?

Using the @reboot cron keyword, this will execute the specified command once after the machine got booted every time.
@reboot CMD

12. How to Disable/Redirect the Crontab Mail Output using MAIL keyword?

By default crontab sends the job output to the user who scheduled the job. If you want to redirect the output to a specific user, add or update the MAIL variable in the crontab as shown below.
ramesh@dev-db$ crontab -l
MAIL="ramesh"

@yearly /home/ramesh/annual-maintenance
*/10 * * * * /home/ramesh/check-disk-space

[Note: Crontab of the current logged in user with MAIL variable]

If you wanted the mail not to be sent to anywhere, i.e to stop the crontab output to be emailed, add or update the MAIL variable in the crontab as shown below.
MAIL=""

13. How to Execute a Linux Cron Jobs Every Second Using Crontab.

You cannot schedule a every-second cronjob. Because in cron the minimum unit you can specify is minute. In a typical scenario, there is no reason for most of us to run any job every second in the system.

14. Specify PATH Variable in the Crontab

All the above examples we specified absolute path of the Linux command or the shell-script that needs to be executed.

For example, instead of specifying /home/ramesh/tape-backup, if you want to just specify tape-backup, then add the path /home/ramesh to the PATH variable in the crontab as shown below.
ramesh@dev-db$ crontab -l

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/home/ramesh

@yearly annual-maintenance
*/10 * * * * check-disk-space

[Note: Crontab of the current logged in user with PATH variable]

15. Installing Crontab From a Cron File

Instead of directly editing the crontab file, you can also add all the entries to a cron-file first. Once you have all thoese entries in the file, you can upload or install them to the cron as shown below.
ramesh@dev-db$ crontab -l
no crontab for ramesh

$ cat cron-file.txt
@yearly /home/ramesh/annual-maintenance
*/10 * * * * /home/ramesh/check-disk-space

ramesh@dev-db$ crontab cron-file.txt

ramesh@dev-db$ crontab -l
@yearly /home/ramesh/annual-maintenance
*/10 * * * * /home/ramesh/check-disk-space
Note: This will install the cron-file.txt to your crontab, which will also remove your old cron entries. So, please be careful while uploading cron entries from a cron-file.txt.

Linux Directory Structure (File System Structure) Explained with Examples

      Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
    Have you wondered why certain programs are located under /bin, or /sbin, or /usr/bin, or /usr/sbin?
For example, less command is located under /usr/bin directory. Why not /bin, or /sbin, or /usr/sbin? What is the different between all these directories?
In this article, let us review the Linux filesystem structures and understand the meaning of individual high-level directories.


1. / – Root

  • Every single file and directory starts from the root directory.
  • Only root user has write privilege under this directory.
  • Please note that /root is root user’s home directory, which is not same as /.

2. /bin – User Binaries

  • Contains binary executables.
  • Common linux commands you need to use in single-user modes are located under this directory.
  • Commands used by all the users of the system are located here.
  • For example: ps, ls, ping, grep, cp.

3. /sbin – System Binaries

  • Just like /bin, /sbin also contains binary executables.
  • But, the linux commands located under this directory are used typically by system aministrator, for system maintenance purpose.
  • For example: iptables, reboot, fdisk, ifconfig, swapon

4. /etc – Configuration Files

  • Contains configuration files required by all programs.
  • This also contains startup and shutdown shell scripts used to start/stop individual programs.
  • For example: /etc/resolv.conf, /etc/logrotate.conf

5. /dev – Device Files

  • Contains device files.
  • These include terminal devices, usb, or any device attached to the system.
  • For example: /dev/tty1, /dev/usbmon0

6. /proc – Process Information

  • Contains information about system process.
  • This is a pseudo filesystem contains information about running process. For example: /proc/{pid} directory contains information about the process with that particular pid.
  • This is a virtual filesystem with text information about system resources. For example: /proc/uptime

7. /var – Variable Files

  • var stands for variable files.
  • Content of the files that are expected to grow can be found under this directory.
  • This includes — system log files (/var/log); packages and database files (/var/lib); emails (/var/mail); print queues (/var/spool); lock files (/var/lock); temp files needed across reboots (/var/tmp);

8. /tmp – Temporary Files

  • Directory that contains temporary files created by system and users.
  • Files under this directory are deleted when system is rebooted.

9. /usr – User Programs

  • Contains binaries, libraries, documentation, and source-code for second level programs.
  • /usr/bin contains binary files for user programs. If you can’t find a user binary under /bin, look under /usr/bin. For example: at, awk, cc, less, scp
  • /usr/sbin contains binary files for system administrators. If you can’t find a system binary under /sbin, look under /usr/sbin. For example: atd, cron, sshd, useradd, userdel
  • /usr/lib contains libraries for /usr/bin and /usr/sbin
  • /usr/local contains users programs that you install from source. For example, when you install apache from source, it goes under /usr/local/apache2

10. /home – Home Directories

  • Home directories for all users to store their personal files.
  • For example: /home/john, /home/nikita

11. /boot – Boot Loader Files

  • Contains boot loader related files.
  • Kernel initrd, vmlinux, grub files are located under /boot
  • For example: initrd.img-2.6.32-24-generic, vmlinuz-2.6.32-24-generic

12. /lib – System Libraries

  • Contains library files that supports the binaries located under /bin and /sbin
  • Library filenames are either ld* or lib*.so.*
  • For example: ld-2.11.1.so, libncurses.so.5.7

13. /opt – Optional add-on Applications

  • opt stands for optional.
  • Contains add-on applications from individual vendors.
  • add-on applications should be installed under either /opt/ or /opt/ sub-directory.

14. /mnt – Mount Directory

  • Temporary mount directory where sysadmins can mount filesystems.

15. /media – Removable Media Devices

  • Temporary mount directory for removable devices.
  • For examples, /media/cdrom for CD-ROM; /media/floppy for floppy drives; /media/cdrecorder for CD writer

16. /srv – Service Data

  • srv stands for service.
  • Contains server specific services related data.
  • For example, /srv/cvs contains CVS related data.

10 Oracle SQLLDR Command Examples (Oracle SQL*Loader Tutorial)

If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file. This article provides 10 practical examples on how to upload data from a flat file to Oracle tables.


Input data file for SQL*Loader

This is the input text file that contains the data that needs to be loaded into an oracle table. Each and every records needs to be in a separate line, and the column values should be delimited by some common delimiter character. For some of the examples mentioned below, we’ll use the following employee.txt file to upload the data to the employee table.
$ cat employee.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

SQL*Loader Control File

This contains the instructions to the sqlldr utility. This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.
$ cat example1.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
The above control file indicates the following:
  • infile – Indicates the location of the input data file
  • into table – Indicates the table name where this data should be inserted
  • fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
  • ( id, name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded

1. Basic Upload Example Using SQL*Loader

First, create the employee table as shown below.
SQL> create table employee
(
  id integer,
  name varchar2(10),
  dept varchar2(15),
  salary integer,
  hiredon date
)
Next create the control file that explains what needs to be upload and where.
$ cat sqlldr-add-new.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
Note: If you have the values inside the data file enclosed with double quote, use this in your control file: fields terminated by “,” optionally enclosed by ‘”‘
Note: If you don’t have the table created, you’ll get the following error message:
SQL*Loader-941: Error during describe of table EMPLOYEE
ORA-04043: object EMPLOYEE does not exist
You can pass the userid and password to the sqlldr command using any one of the following format. As you see below, both of these will prompt you for control file location, as it was not given in the command line.
$ sqlldr scott/tiger

(or)

$ sqlldr userid=scott/tiger
control =
SQL*Loader-287: No control file name specified.
Execute the sqlldr command to upload these new record to the empty table by specifying both uid/pwd and the control file location as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new.ctl
Commit point reached - logical record count 5
Verify the the records are created in the database
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- -------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
This will create the output log file in the same name as the data file, but with the .log extension (instead of .ctl). Partial output shown below.
$ cat sqlldr-add-new.log

Control File:   /home/ramesh/sqlldr-add-new.ctl
Data File:      /home/ramesh/employee.txt

Table EMPLOYEE:
  5 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Elapsed time was:     00:00:00.04
CPU time was:         00:00:00.00
If you are new to Oracle database, and like to install it, follow this Oracle 11g installation guide.

2. Inserting Additional Records

Let us say you want to add two new employees to the employee table from the following newemployee.txt file.
$ vi newemployee.txt
600,Ritu,Accounting,5400
700,Jessica,Marketing,7800
If you create a similar control file like the previous example, you might get the following error message.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-more.ctl
SQL*Loader-601: For INSERT option, table must be empty.  Error on table EMPLOYEE
The above indicates that the table should be empty before you can upload data using sql*loader.
If you like to insert more data to the tables without having to delete the existing rows, use the “append’ command as shown in the following control file.
$ vi sqlldr-append-more.ctl
load data
 infile '/home/ramesh/newemployee.txt'
 append
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
Now, if you do sqlldr this will append the data.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-append-more.ctl
Commit point reached - logical record count 2
Verify that the records are appended successfully
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- -------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       600 Ritu       Accounting            5400
       700 Jessica    Marketing             7800

3. Data inside the Control File using BEGINDATA

You can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table as shown below.
$ cat sqlldr-add-new-with-data.ctl
load data
 infile *
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
begindata
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
Note: The infile will say ‘*’ in this case, as there is no input data file name for this example.
Execute sqlldr to upload the data from the control file.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new-with-data.ctl

4. Date format and Different Delimiter

This example shows how to specify a date format in the control file and how to handle different delimiters in a data file
The following example has different delimiters ($ after name, ^ after department).
$ cat employee-date.txt
100,Thomas$Sales^5000,31-JAN-2008
200,Jason$Technology^5500,01-Feb-2005
300,Mayla$Technology^7000,10-Aug-2000
400,Nisha$Marketing^9500,12-Dec-2011
500,Randy$Technology^6000,01-JAN-2007
Create the following control file and indicate the field delimiters for each and every field using “terminated by” as shown below.
$ cat sqlldr-date.ctl
load data
 infile '/home/ramesh/employee-date.txt'
 into table employee
 fields terminated by ","
 ( id, name terminated by "$", dept terminated by "^", salary, hiredon DATE "dd-mon-yyyy" )
Load the data using sqlldr as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-date.ctl
Verify that the data got loaded properly as shown below.
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       100 Thomas     Sales                 5000 31-JAN-08
       200 Jason      Technology            5500 01-FEB-05
       300 Mayla      Technology            7000 10-AUG-00
       400 Nisha      Marketing             9500 12-DEC-11
       500 Randy      Technology            6000 01-JAN-07

5. Fixed Length Data Upload

If you have a data file without data that are fixed length (i.e without any delimiter), you can use this example to upload this data.
For this example, let us use the following file which has data that are of fixed length. For example, 1st three characters are always employee number, Next 5 characters are always employee name, etc.
$ cat employee-fixed.txt
200JasonTechnology5500
300MaylaTechnology7000
400NishaTechnology9500
500RandyTechnology6000
Create the following control file, where you specific the position of each and every field as shown below usig the “Position(start:end)” syntax.
$ cat sqlldr-fixed.ctl
load data
 infile '/home/ramesh/employee-fixed.txt'
 into table employee
 fields terminated by ","
 ( id position(1:3), name position(4:8), dept position(9:18), salary position(19:22) )
Load this fixed length data using the sqlldr as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-fixed.ctl
Verify that the data got loaded.
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Technology            9500
       500 Randy      Technology            6000

6. Change the data during upload

You can also massage the data and change it during upload based on certain rules.
In the following control file:
  • id is incremented by 999 before uploading. i.e if the emp id is 100 in the data file, it will be loaded as 1099
  • Convert the name to upper case and load it. This uses the upper function.
  • If the department contains the value “Technology” change it to “Techies”. This uses decode function
$ cat sqlldr-change-data.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id ":id+999",
   name "upper(:name)",
   dept  "decode(:dept,'Technology','Techies', :dept)",
   salary
  )
Load the data using this control file which will massage the data before uploading it.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-change-data.ctl
Verify that the data got changed while loading as per our rules.
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
      1099 THOMAS     Sales                 5000
      1199 JASON      Techies               5500
      1299 MAYLA      Techies               7000
      1399 NISHA      Marketing             9500
      1499 RANDY      Techies               6000

7. Load data from multiple files

To load data from multiple files, you just have to specify multiple infile in the control file.
The following control file loads data from two different data files (employee.txt and newemployee.txt) to the employee table.
$ sqlldr-add-multiple.ctl
load data
 infile '/home/ramesh/employee.txt'
 infile '/home/ramesh/newemployee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
Load the data using this control file which will upload data from multiple data files as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-multiple.ctl
Commit point reached - logical record count 5
Commit point reached - logical record count 7

8. Load data to Multiple Tables

Create another table called bonus which will have employee id and bonus columns.
create table bonus
  ( id integer,
    bonus integer
  );
Create the employee-bonus.txt data file that contains the fields: id, name, department, salary, bonus
$ cat employee-bonus.txt
100 Thomas Sales      5000 1000
200 Jason  Technology 5500 2000
300 Mayla  Technology 7000 2000
400 Nisha  Marketing  9500 1000
500 Randy  Technology 6000 3000
Create the control file as shown below, which will upload the data from the above file to two different tables. As shown below, you should have two “into table” commands, and specify the position of the data which needs to be used to upload the data to that column.
$ cat sqlldr-multiple-tables.ctl
load data
 infile '/home/ramesh/employee-bonus.txt'
 into table employee
 ( id position(1:3),
   name position(5:10),
   dept position(12:21),
   salary position(23:26))
 into table bonus
 ( id position(1:3),
   bonus position(28:31))
Load the data to multiple tables using this control file as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-multiple-tables.ctl
Verify that the data got loaded to multiple tables successfully.
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000

SQL> select * from bonus;

        ID      BONUS
---------- ----------
       100       1000
       200       2000
       300       2000
       400       1000
       500       3000

9. Handling Bad (Rejected) Records

In the following example, we have two bad records. Employee id 300 and 500 has salary column which is not numeric.
$ cat employee-bad.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7K
400,Nisha,Marketing,9500
500,Randy,Technology,6K
Use the following control file for this example.
$ cat sqlldr-bad.ctl
load data
 infile '/home/ramesh/employee-bad.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
Load the data (including the invalid records) using this control file as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-bad.ctl
Commit point reached - logical record count 5
As you see from the abvoe output, it still says “logical record count 5″, but you should check the log files to see if it has rejected any records.
The log file indicates that 2 records are rejected as shown below:
Control File:   /home/ramesh/sqlldr-bad.ctl
Data File:      /home/ramesh/employee-bad.txt
  Bad File:     /home/ramesh/employee-bad.bad
  Discard File:  none specified

Table EMPLOYEE:
  3 Rows successfully loaded.
  2 Rows not loaded due to data errors.
By default the rejected records are stored in a file that has the same name as the data file (but with .bad extension)
$ cat employee-bad.bad
300,Mayla,Technology,7K
500,Randy,Technology,6K
As you see below, the employee table has only 3 records (as 2 of them were rejected).
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       400 Nisha      Marketing             9500

10. Load Specific Rows from a datafile

If you want to load only a specific records from a data file use the WHEN in the control file.
Add the line “when” next to “into table” line. In the following control file, the when clause indicates that it will load only the records that have dept as “Technology”.
$ cat sqlldr-when.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 when dept = 'Technology'
 fields terminated by ","
 ( id, name, dept, salary )
Load the selective data (only the “Technology” records) using this control file as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-when.ctl
Commit point reached - logical record count 5
As you see from the above output, it still says “logical record count 5″, but you should check the log files to see how many records were loaded, and how many records were discarded because it didn’t match the when condition.
The following from the log file shows that 5 records were read, and 2 of them were discarded as it didn’t match the when condition.
Discard File:  none specified
Total logical records read:             5
Total logical records discarded:        2
Verify that only the selective records were loaded into the table.
SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       500 Randy      Technology            6000

Wednesday, July 10, 2013

RMAN Block Change Tracking File



Using block tracking one can improve performance of the backup time of incremental backup from hours to minutes depending on size of database as it no longer needs to scan blocks in data files to find blocks changed it can use the block tracking file to identify changed blocks for incremental backup. Enabling this starts a new background process called Change Tracking Writer (CTWR) which manages this file.
Here are the steps to enable block tracking:
– Note if the file already exists the command will fail unless the REUSE option is used so first check if block tracking is not enabled before overwriting the file.
– If it returns DISABLED it means block tracking is off
SQL> SELECT DISTINCT STATUS FROM V$BLOCK_CHANGE_TRACKING;
STATUS
----———-
DISABLED
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE  ‘/U04/ORADATA/TEST/RMAN_BLOCK_TRACKING.F’;
Database altered.
The below command create a block change tracking file for the database on ASM.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘+DATA01′;
– indicating block tracking is enabled and the find the block tracking file

SQL> SELECT STATUS, FILENAME FROM V$BLOCK_CHANGE_TRACKING;
STATUS          FILENAME
————————————————————-----------------------------——————–
ENABLED         /U04/ORADATA/TEST/RMAN_BLOCK_TRACKING.F’
To disable block tracking:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
Check the status with below command.
SQL> SELECT DISTINCT STATUS FROM V$BLOCK_CHANGE_TRACKING;
STATUS
——----—-
DISABLED

Monitoring TEMP Space Usage in Oracle RAC Databases



It’s a common problem encountered by many DBAs on a daily basis; developers writing queries which run out of TEMP space. It can come at the worst of times, too…
For example, you’ve run in the scripts many times overnight into a development environment and they worked fine. They are signed off and run into PROD. However, when running them into PROD they run out of TEMP space because some other processes were contending for TEMP space.
TEMP Space Usage in Oracle
I use this query to monitor TEMP space. We have an OEM job which runs every 5 minutes during selected times and then alerts us when the TEMP space reaches a certain threshold.
SELECT   S.INST_ID,S.SID || ',' || S.SERIAL# SID_SERIAL,
             S.USERNAME,
             S.OSUSER,
             P.SPID,
             S.MODULE,
             S.PROGRAM,
             SUM (T.BLOCKS) * TBS.BLOCK_SIZE /1024/1024 MB_USED,
             T.TABLESPACE,
             COUNT(*) SORT_OPS
FROM  GV$SORT_USAGE T,
             GV$SESSION S,
             DBA_TABLESPACES TBS,
             GV$PROCESS P
WHERE T.SESSION_ADDR = S.SADDR
AND      S.PADDR = P.ADDR
AND      T.TABLESPACE = TBS.TABLESPACE_NAME
AND      S.INST_ID=P.INST_ID
GROUP BY S.INST_ID,S.SID,
             S.SERIAL#,
             S.USERNAME,
             S.OSUSER,
             P.SPID,
             S.MODULE,
             S.PROGRAM,
             TBS.BLOCK_SIZE,
             T.TABLESPACE
ORDER BY MB_USED DESC,
             SID_SERIAL;

You can also use the above query to insert into a table so that it measures how much TEMP you are using at regular intervals, say, every minute then you can see at which stages it maxes out during your run.
It can be easy to forget about TEMP space usage, but when it goes wrong it usually goes wrong badly. After all, if your job is using that much TEMP space it’s probably doing a lot of work and will have taken a long time to get to the point of failure. And if it has to rollback a load of transactions when it fails it can take a long time to complete.
Auto-extending Temp Files
If you are not sure how much temporary tablespace your job is going to use, and therefore how large you need to size your temp tablespace, you could change the tempfiles to have AUTOEXTEND on so as to prevent the dreaded “ORA-1652: unable to extend temp segment” error message. This will help you with your development runs so you can gauge how much TEMP you need.
Next I’d like to talk a little bit about the PGA because it is very closely associated with TEMP space usage in the database.
What is the PGA
Another factor that you should consider is the size of your Program Global Area (PGA). The PGA is the area of memory (RAM) in which your session sorts data before it is returned to the user. Say, for example, you have the following query:
SELECT ID, COUNT(*) FROM TABLE GROUP BY ID ORDER BY COUNT(*) DESC;
The database will have to organise this data somewhere before returning the results because the user has requested it grouped and ordered. And this is where the PGA comes in handy. In this case the data will be taken from “table” and stored in the PGA temporarily, using it to group and order the result set. If “table” is not very large it might fit into your PGA in which case the results can be totally held in memory which results in faster processing and returning of the data to the user.
However, if the amount of space required to store all of the data you need to sort is greater than the size of your PGA there is a problem. That problem is resolved by using an overspill area on disk, also known in Oracle as the TEMPORARY (TEMP) tablespace. The data can be read into and out of the PGA and TEMP tablespace in order to perform the sorting required and return a final result set to the user. You should be aware that if you have differently sized PGAs on your environments this can affect when the TEMP tablespace has to be used and cause performance degradation if it has to use TEMP on one environment and not on another because writing out to disk is always slower than using memory (RAM) for an operation.
NOTE: If you look at your AWR report you will see there is an area (at least on 11g there is) called “PGA Aggr Target Histogram” which shows the number of “Optimal Execs”, “1-Pass Execs” and “M-Pass Execs”. Optimal executions are what you want because it means that the sorts were done entirely in memory, without having to go over the data more than once. If you have a lot of passes over the data it can become a very inefficient way of sorting the data.
What size is your PGA?
So, how do you check the size of your PGA? There is an initialisation parameter called PGA_AGGREGATE_TARGET which controls the size of memory allocated for its use. You can alter the setting using the following command:

      ALTER SYSTEM SET PGA_AGGREGATE_TARGET = nG SCOPE=SPFILE;
Note: This means you will have to restart the database before the change take effect or you can specify SCOPE = BOTH to change it on the fly, although I haven’t had that much success with doing it this way.
.It should be noted that one user can rarely use all of the PGA. The reason for this is because the Oracle server is supposed to be a multi-user environment and if one process was allowed to use all of the memory allocated to the PGA there would be nothing left for anyone else. As such, there is a limit to what one user can utilise at any one time. Personally, I would monitor the usage of your PGA to see what it maxes out at while running your process.

How Much PGA are you Using
If you have set the PGA_AGGREGATE_TARGET parameter then you are using what’s called automatic PGA memory management which means that you are setting on value and then allowing the database to allocate the memory dynamically between the different sorting areas within the PGA. This has been the recommended way since Oracle 10g and I’d say is the best way to go seeing as it can be very difficult to use manual memory management correctly and it’s far less flexible for different load profiles.

To find out about what processes are using your PGA you can look in the following views:
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
V$PROCESS
If you want to have a look at the PGA size on a per process/session basis then you can use the following query:
             
SELECT  SESS.SID
      , SESS.USERNAME
      , SESS.PROGRAM
      , SESS.MACHINE
      , TRUNC(PGA_USED_MEM/1024/1024) PGA_USED_MB
      , TRUNC(PGA_ALLOC_MEM/1024/1024) PGA_ALLOC_MB
      , TRUNC(PGA_FREEABLE_MEM/1024/1024) PGA_FREEABLE_MB
      , TRUNC(PGA_MAX_MEM/1024/1024) PGA_MAX_MB
FROM V$PROCESS PROC, V$SESSION SESS
  WHERE PROC.ADDR = SESS.PADDR
AND BACKGROUND IS NULL
ORDER BY PGA_ALLOC_MB DESC;
OK, so I think we’ve covered a reasonable amount with regard to the PGA so let’s move on to look at the TEMPORARY tablespace.

How to Create a Temporary Tablespace
Under most circumstances you would create the temporary tablespace at the time of database creation and it would be called TEMP. However, sometimes there are reasons why you might want to create a different temporary tablespace, such as:

You have a large running query and want to use a separate Oracle temporary tablespace for it to use
You’d like to move the TEMP tablespace without having to restart the database in a MOUNT state to rename the existing temporary database files
A file has become corrupted in your existing temporary tablespace and you want to re-create it
To create a TEMPORARY tablespace you can use the following syntax:
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '<path>\file_name' SIZE xxG AUTOEXTEND OFF;
Obviously you could put autoextend on if you wish, but that’s just an example of one way how to create the tablespace. I mentioned above that you might like to move the TEMP files from one location to another. This is possible by creating a new temporary tablespace first, then assigning that TEMP tablespace as the default for the database and then dropping the old one. Here is an example of how you could do that, assume that you already have a temporary tablespace in your database called TEMP.

How to Assign a Default Temporary Tablespace
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\Oracle\DB\TEMP01.DBF' SIZE 4G AUTOEXTEND OFF;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
You have to create the new TEMP tablespace first otherwise you will hit the error “ORA-12906: cannot drop default temporary tablespace“. If you really want to you could repeat the process so that your temporary tablespace goes back to being called TEMP rather than TEMP2.
When changing the default temporary tablespace of the database you should be aware that existing users who have their temporary tablespace set to the original tablespace name will remain that way. Therefore, you should either re-assign all of the users to the new TEMP2 tablespace or change back to a temporary tablespace with the same name as the original.
Checking the Current Size of the TEMPORARY Tablespace
This is the query that I use to check what size my TEMP tablespace is within Oracle:
SET LINES 120
SET PAGES 100
COL FILE_NAME FOR A50
BREAK ON REPORT;
COMPUTE SUM LABEL "TOTAL" OF "MB" ON REPORT;
COMPUTE SUM OF "MMB" ON REPORT;
SELECT FILE_ID
     , FILE_NAME
     , TABLESPACE_NAME
     , BYTES/1024/1024 MB
     , MAXBYTES/1024/1024 MMB
     , AUTOEXTENSIBLE
FROM DBA_TEMP_FILES
  ORDER BY FILE_NAME;
How to Drop a TEMP File
You can drop an individual temporary database file from a TEMP type tablespace in your Oracle database using the following command:
ALTER DATABASE TEMPFILE '<tempfile_location>' DROP INCLUDING DATAFILES;
Note: If you drop all of the tempfiles from your TEMP tablespace you might encounter the error ORA-25153: Temporary Tablespace is Empty so watch out for that one and always make sure that you have temporary files available for use.
.If the tempfile is currently being used you will get an error stating that you cannot drop the file at that time, so you will have to wait or kill any transactions which are using it. I have found in the past that when I drop the tempfile, although it no longer shows as being part of the database in the data dictionary, it does not get dropped physically from the file system. There is a workaround which does work sometimes which is to create another TEMP file to overwrite the one you just dropped with an identical name and then drop it again. For example:

ALTER DATABASE TEMPFILE 'D:\Oracle\DB\TEMP01.dbf' DROP INCLUDING DATAFILES;

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\Oracle\DB\TEMP01.dbf' SIZE 4G;

ALTER DATABASE TEMPFILE 'D:\Oracle\DB\TEMP01.dbf' DROP INCLUDING DATAFILES;

Hopefully this will remove the file from the file system this time.