Export/Import Example

=================Expdp/Impdp==========================
C:\Users\smohanty46>set ORACLE_SID=xe
C:\Users\smohanty46>sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 4 13:40:57 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL>
SQL>
SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
XE        READ WRITE
SQL>
SQL>
SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLECLRDIR
C:\oraclexe\app\oracle\product\11.2.0\server\bin\clr
SYS                            DATA_PUMP_DIR
C:\oraclexe\app\oracle\admin\xe\dpdump\
SYS                            XMLDIR
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml
OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
C:/ADE/jaikrish_xe/oracle\ccr\state
SQL> create directory test as 'C:\oraclexe\test';

Directory created.

SQL> grant read,write on directory test to hr;

Grant succeeded.
##############################full Database backup############################

C:\oraclexe\test>expdp userid=system/oracle directory=test full=y dumpfile=full_database.dmp logfile=full_database.log
#############################expdp using parfile##################

C:\oraclexe\test>expdp userid=system/oracle parfile=C:\Users\smohanty46\Documents\table.par

##############################schema backup##############################################
C:\oraclexe\test>expdp userid=system/oracle directory=test schemas=HR dumpfile=

r_schema.dmp logfile=hr_schmea.log
#########################Table backup############################################
C:\Users\smohanty46>expdp userid=hr/hr directory=test tables=jobs dumpfile=jobs.

dmp logfile=jobs.log

###############################metadata only#####################################
C:\Users\smohanty46>expdp userid=hr/hr directory=test tables=jobs content=metada

ta_only dumpfile=jobs1.dmp logfile=jobs1.log

###################################data only#########################################
C:\Users\smohanty46>expdp userid=hr/hr directory=test tables=jobs content=data_o

nly dumpfile=jobs2.dmp logfile=jobs2.log
########################object(view,sequence,procedure,trigger,index)################################################
C:\oraclexe\test>expdp userid=hr/hr include=view,sequence,procedure,trigger,inde

x directory=test dumpfile=object.dmp logfile=object.log
###########################Flash back expdp##############################################################
SQL> connect system/oracle
Connected.

SQL> create table hr.tb1(id number);

Table created.

SQL> insert into hr.tb1 values(1);

1 row created.

SQL> insert into hr.tb1 values(2);

1 row created.

SQL> insert into hr.tb1 values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     379525

SQL> insert into hr.tb1 values(4);

1 row created.

SQL> insert into hr.tb1 values(5);

1 row created.

SQL> insert into hr.tb1 values(6);

1 row created.

SQL> commit
  2  ;

Commit complete.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     379544


C:\oraclexe\test>expdp userid=hr/hr directory=test flashback_scn=379525 dumpfile=till_3rows.dmp logfile=3rows.log tables=tb1
###########################Size estimation in block method##########################################

C:\oraclexe\test>expdp userid=hr/hr estimate=blocks tables=tb1 dumpfile=tb1_size.dmp logfile=tb1_size.log directory=test

###############################Size estimation with out creating the backup#################################


C:\oraclexe\test>expdp userid=hr/hr estimate_only=y tables=tb1  directory=test
############################Creating multiple dump file accomadate space###########################################

C:\oraclexe\test>expdp full=y directory=test userid=system/oracle filesize=10M dumpfile=full%U.dmp logfile=full.log job_name=myjob
############################Creating dump file of different sizes in different directory#######################


C:\oraclexe\test>expdp userid=system/oracle filesize=40m full=y dumpfile=test:file_1.dmp,ORACLECLRDIR:file_2.dmp,DATA_PUMP_DIR:file_3.dmp,XMLDIR:file_4.dmp logfile=test:multiple_directory.log

########################################Exclude table ############################

C:\oraclexe\test>expdp hr/hr directory=test exclude=table:"in\ ('JOBS'\)" dumpfile=exclude_job_table.dmp logfile=exclude_job_table.log

########################################Exclude object #########################

C:\oraclexe\test>expdp hr/hr exclude=procedure directory=test dumpfile=exclude_proc.dmp logfile=exclude_proc.log

#################Tablespace export#########################

C:\oraclexe\test>expdp userid=system/oracle tablespaces=users,temp dumpfile=tablespace.dmp logfile=tablespace.log directory=test

#########################stoping,Attaching,continuing,killing jobs###########################

C:\oraclexe\test>expdp system/oracle full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

xport: Release 11.2.0.2.0 - Production on Wed Feb 5 16:24:29 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 162.5 MB

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

C:\oraclexe\test>expdp system/oracle attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.2.0 - Production on Wed Feb 5 16:25:48 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Job: SYS_EXPORT_FULL_01

  Owner: SYSTEM

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: CCBD86DB9C574F7EAFFA108C6D2FDBF4

  Start Time: Wednesday, 05 February, 2014 16:25:51

  Mode: FULL

  Instance: xe

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/******** full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

  State: IDLING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: C:\oraclexe\test\dump_full.dmp

    bytes written: 4,096

Worker 1 Status:

  Process Name: DW00

  State: UNDEFINED

  Object Schema: APEX_040000

  Object Name: WWV_FLOW_PAGE_PLUGS

  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE

  Completed Objects: 377

  Worker Parallelism: 1

Export> CONTINUE_CLIENT

Job SYS_EXPORT_FULL_01 has been reopened at Wednesday, 05 February, 2014 16:25

Restarting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** full=y directory=test dumpfile=dump_full.dmp logfile=dump_full.log

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Export> KILL_JOB

Are you sure you wish to stop this job ([yes]/no): yes

C:\oraclexe\test>sqlplus system/oracle

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 5 16:26:54 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> select * from dba_datapump_jobs;

no rows selected

==========================Import==========================================

####################import table with data and metadata#########

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=all

#######################################default of table_exists_action=skip###########

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=all

#########################################table_exists_action=append###################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log table_exists_action=append

######################################table_exists_action=truncate######################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log table_exists_action=truncate

#############################################table_exists_action=replace###############

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log table_exists_action=replace

######################content=data_only################################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=data_only

###############################content=metadata_only##############################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=metadata_only

###############################content=all##########################################

C:\oraclexe\test>impdp userid=system/oracle directory=test tables=master.jobs dumpfile=master.dmp logfile=master_imp.log content=all

################################import schema to a different schema#######

C:\oraclexe\test>impdp userid=system/oracle directory=test remap_schema=hr:mamuni dumpfile=hr_schema.dmp logfile=hr_schema1_imp.log

################################import table into table###########################

C:\oraclexe\test>impdp userid=system/oracle directory=test remap_table=master.jobs:mamuni.darling dumpfile=master.dmp logfile=master_table_import.log

############################import full database#########

C:\oraclexe\test>impdp userid=system/oracle directory=test full=y dumpfile=full_db_exter.dmp  logfile=full_db_exter_imp.log

###############################remap tablespace##########################

SQL> create tablespace test datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\test.dbf' size 5m;

Tablespace created.

SQL> create user mamuni1 identified by mamuni1;

User created.

SQL> grant connect,resource to mamuni1;

Grant succeeded.

SQL> alter user mamuni1 default tablespace test;

User altered.

SQL> alter user mamuni1 quota unlimited on test;

User altered.

SQL> create table mamuni1.employees as select * from hr.employees;

Table created.

SQL> create table mamuni1.jobs as select * from hr.jobs;

Table created.

SQL> select username,default_tablespace from dba_users where username='MAMUNI1';

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

MAMUNI1                        TEST

C:\oraclexe\test>expdp userid=system/oracle directory=test tablespaces=test dumpfile=test_tablespace.dmp logfile=test_tablespace.log

Export: Release 11.2.0.2.0 - Production on Thu Feb 6 19:57:17 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  userid=system/******** directory=test tablespaces=test dumpfile=test_tablespace.dmp logfile=test_tablespace.log

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "MAMUNI1"."EMPLOYEES"                       16.81 KB     107 rows

. . exported "MAMUNI1"."JOBS"                            6.992 KB      19 rows

Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:

  C:\ORACLEXE\TEST\TEST_TABLESPACE.DMP

Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 19:57:31

SQL> create tablespace test1 datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\test1.dbf' size 2m;

Tablespace created.

C:\oraclexe\test>impdp userid=system/oracle directory=test dumpfile=test_tablespace.dmp logfile=imp_test_tablespace.log remap_tablespace=test:test1 table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Thu Feb 6 19:59:59 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  userid=system/******** directory=test dumpfile=test_tablespace.dmp logfile=imp_test_tablespace.log remap_tablespace=test:test1 table_exists_action=replace

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "MAMUNI1"."EMPLOYEES"                       16.81 KB     107 rows

. . imported "MAMUNI1"."JOBS"                            6.992 KB      19 rows

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 20:00:03

C:\oraclexe\test>

SQL> select table_name,tablespace_name from dba_tables where owner='MAMUNI1';

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

EMPLOYEES                      TEST1

JOBS                           TEST1

SQL> create table mamuni1.employee1 as select * from mamuni1.employees;

Table created.

SQL> select table_name,tablespace_name from dba_tables where owner='MAMUNI1';

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

EMPLOYEE1                      TEST

EMPLOYEES                      TEST1

JOBS                           TEST1

######################################DDL of the dumpfile using sqlfile paramaeter######

C:\oraclexe\test>impdp userid=system/oracle directory=test dumpfile=test_tablespace.dmp sqlfile=test_tablespace_ddl.sql

expdp pipe small size

#!/bin/ksh

ORACLE_SID=
ORACLE_HOME=
ORACLE_BASE=/u01/app/orascp
LOG_FILE=schema_export.log
export ORACLE_SID ORACLE_HOME ORACLE_BASE

expdp \'/ as sysdba\' DIRECTORY=DMPDIR SCHEMAS= DUMPFILE=_DUMP_%U.dmp LOGFILE=$LOG_FILE EXCLUDE=STATISTICS PARALLEL=2 FILESIZE=10G JOB_NAME=_EXPORT_NEW compression=all
echo "export datapump schema level completed at $(date)" >> /u05/expdp_backup/$LOG_FILE
cd /u05/expdp_backup
ls -ltr>>/u05/expdp_backup/$LOG_FILE
cat /u05/expdp_backup/$LOG_FILE| mailx -s "export of schema " subudba@gmail.com
exit
EOF

Questions of Oracle Export and Import

Questions regarding export and import?

1)What is the difference between traditional export and data pump export?
Ans:-


1)Impdp/Expdp has self-tuning unities. Tuning parameters that were used in original Export and Import, such as BUFFER and RECORDLENGTH, are neither required nor supported by Data Pump Export and Import.
2)Data Pump represent metadata in the dump file set as XML documents rather than as DDL commands.
3)Impdp/Expdp use parallel execution rather than a single stream of execution, for improved performance.
4)In Data Pump expdp full=y and then impdp schemas=prod is same as of expdp schemas=prod and then impdp full=y where in original export/import does not always exhibit this behavior.
5)Expdp/Impdp access files on the server rather than on the client.
6)Expdp/Impdp operate on a group of files called a dump file set rather than on a single sequential dump file.
7)Sequential media, such as tapes and pipes, are not supported in oracle data pump.But in original export/import we could directly compress the dump by using pipes.
8)The Data Pump method for moving data between different database versions is different than the method used by original Export/Import.
9)When you are importing data into an existing table using either APPEND or TRUNCATE, if any row violates an active constraint, the load is discontinued and no data is loaded. This is different from original Import, which logs any rows that are in violation and continues with the load.
10)Expdp/Impdp consume more undo tablespace than original Export and Import.
11)If a table has compression enabled, Data Pump Import attempts to compress the data being loaded. Whereas, the original Import utility loaded data in such a way that if a even table had compression enabled, the data was not compressed upon import.
12)Data Pump supports character set conversion for both direct path and external tables. Most of the restrictions that exist for character set conversions in the original Import utility do not apply to Data Pump. The one case in which character set conversions are not supported under the Data Pump is when using transportable tablespaces.
13)There is no option to merge extents when you re-create tables. In original Import, this was provided by the COMPRESS parameter. Instead, extents are reallocated according to storage parameters for the target table.

2)What is the meaning of compress=y in export command ?
Ans:-

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.
For more information please visit below site
http://oracleadmins.wordpress.com/2008/08/05/understanding-compress-parameter-in-export/

3)How to know the content of dump without importing the dump in oracle 10g?
Answer:-
Three way to get DDL information of a dump.
1)Export Data Pump client (release 10.1.x to 11.1.x)Oracle Version
% impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_s.dmp NOLOGFILE=y SQLFILE=impdp_s.sql FULL=y
A sample output of the sqlfile impdp_s.sql is: 
-- CONNECT SYSTEM 
-- new object type path is: SCHEMA_EXPORT/USER 
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' 
DEFAULT TABLESPACE "USERS" 
TEMPORARY TABLESPACE "TEMP" 
PASSWORD EXPIRE; 
-- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT 
GRANT UNLIMITED TABLESPACE TO "SCOTT"; 
-- new object type path is: SCHEMA_EXPORT/ROLE_GRANT 
GRANT "CONNECT" TO "SCOTT"; 
GRANT "RESOURCE" TO "SCOTT"; 
-- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE 
ALTER USER "SCOTT" DEFAULT ROLE ALL; 
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA 
-- CONNECT SCOTT 
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), 
export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2303625'); 
COMMIT; 
END; 
/ 
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE 
-- CONNECT SYSTEM 
...


2)classic export client (release 7.3.x to 10.2.x)Oracle Version
% imp system/manager FILE=exp_s.dmp LOG=imp_show.log FULL=y SHOW=y
. importing SYSTEM's objects into SYSTEM 
. importing SCOTT's objects into SCOTT 
"BEGIN " 
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','" 
"CURRENT_SCHEMA'), export_db_name=>'M10203WA.CH.ORACLE.COM', inst_scn=>'2301" 
"131');" 
"COMMIT; END;" 
"ALTER SESSION SET CURRENT_SCHEMA= "SCOTT"" 
"CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER" 
", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI" 
"TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "U" 
"SERS" LOGGING NOCOMPRESS" 
. . skipping table "BONUS" 
... 
Import terminated successfully without warnings. 
3)10g release2 and higher version of oracle
SET serveroutput on SIZE 1000000   
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp')  
A sample screen output of this procedure in Oracle11g for an Export DataPump dumpfile is:
-- call procedure SHOW_DUMPFILE_INFO in SQL*Plus:


SET serveroutput on SIZE 1000000   
exec show_dumpfile_info(p_dir=> 'my_dir', p_file=> 'expdp_s.dmp')  
----------------------------------------------------------------------------  
Purpose..: Obtain details about export dumpfile.        Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher  
.          Export dumpfile version: 7.3.4.0.0 or higher  
.          Export Data Pump dumpfile version: 10.1.0.1.0 or higher  
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');  
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')  
----------------------------------------------------------------------------  
Filename.: expdp_s.dmp  
Directory: my_dir  
Disk Path: /bugmnt7/em/celclnx7/user/expdp/work  
Filetype.: 1 (Export Data Pump dumpfile)  
----------------------------------------------------------------------------  

4)What is transportable tablespace and how to use it?

5)how to import data without metadata?

6)how to import metadata only?

7)How to import or export specific objects in export/import command?

8)How to use database link in oracle 10g?

9)How to check the status of the export or import job?

10)how to speed up the export or import process?

11)How to export can be done to multiple files?

12)How to perform export utility without using export commands?

13)How to upload data to external files?

14)What are the different types of export mode available in oracle 10g?

15)How to make version compatibility while performing import in oracle?

16)How to configure for stream data loading in import?

17)How to perform truncate data in oracle import command ?

18)How do you perform export if your export command contains many tables or schemas ?

19)What is the difference between SQL loader and import utilities?

20)How to import to a different schema in oracle 10g?

Crontab for export command

Setting up cronjobs in Unix and Solaris :-

Cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris.

Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.
1. Crontab Restrictions You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use

crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands
export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn't already exist.

crontab -l Display your crontab file.

crontab -r Remove your crontab file.

crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file
Crontab syntax :-
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.


* * * * * command to be executed
- - - - -
+----- day of week (0 - 6) (Sunday=0)
+------- month (1 - 12)
+--------- day of month (1 - 31)
+----------- hour (0 - 23)
+------------- min (0 - 59)
* in the value field above means all legal values as in braces for that column.

The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).

Note: The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.
30 18 * * * rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :

30 0 1 1,6,12 * -- 00:30 Hrs on 1st of Jan, June & Dec.

0 20 * 10 1-5 --8.00 PM every weekday (Mon-Fri) only in Oct.

0 0 1,10,15 * * -- midnight on 1st ,10th & 15th of month

5,10 0 10 * 1 -- At 12.05,12.10 every Monday & on 10th of every month


Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment

Cron invokes the command from the user's HOME directory with the shell, (/usr/bin/sh).

cron supplies a default environment for every shell, defining:

HOME=user's-home-directory

LOGNAME=user's-login-id

PATH=/usr/bin:/usr/sbin:.

SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email

By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .
>/dev/null 2>&1

7. Example:-

#!/bin/bash

ORACLE_BASE=/data/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

ORACLE_TERM=xterm; export ORACLE_TERM

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "pccuser" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

fi

exp PCPCC_O/LEEO920@orcl file=/data/backup/dump_orcl/PCPCC_O_`date +%d%m%H%M`.DMP log=/data/backup/dump_orcl/PCPCC_O_`date +%d%m%H%M`.LOG statistics=NONE;

exp PCPCC_PUB_O/LEEO920@orcl file=/data/backup/dump_orcl/PCPCC_PUB_O_`date +%d%m%H%M`.DMP log=/data/backup/dump_orcl/PCPCC_PUB_O_`date +%d%m%H%M`.LOG statistics=NONE;

cd /data/backup

zip -r dump_orcl.zip dump_orcl

cd /data/backup

#scp command to send the file from one server to another server

scp dump_orcl.zip root@172.22.33.48:/data/backup_orcl

cd /data/backup

rm -rf dump_orcl.zip

cd /data/backup/dump_orcl

rm -rf *

EOF export_orcl.sh

8. Generate log file To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log

9. Next Steps
This article covered a significant aspect of system administration of setting up cronjobs . Unix administration involves lots of different tasks and some of these tasks are covered in this website but still there are many areas not covered here .
Following books available for online buying from Amazon.com . You should have following two books in your bookshelf for ready reference if you are involved in Unix system administration .
Essential System Administration, Third Edition by by Æleen Frisch

Solaris Operating Environment Boot Camp by David Rhodes, Dominic Butler

If you already own these books the amazon display panel below shows some of the best-selling books for System Administration and you can choose the book here or visit my other website for more selection of best selling unix system administration books by following this link - .Unix system administration books at besttechbooks.com ,from Amazon.com.

You can show your appreciation by buying the books for yourself and encouraging the friends to buy using amazon links below or anywhere at adminschoice.com or besttechbooks.com . Thanks for your appreciation in advance.