Sunday, February 9, 2014

Windows batch job script for oracle export import backup

Reference:
http://halimdba.blogspot.com/2013/10/windows-batch-job-script-for-oracle.html?


windows batch job script for oracle export (and import) backup (2 copy 1. ORCL 2. ORCL_1)

-------------------------------------------------------------------------------
@echo off
set ORACLE_SID=ORCL

if exist C:\oracle_exp\export_backup\ORCL\ORCL.log copy C:\oracle_exp\export_backup\ORCL\ORCL.log C:\oracle_exp\export_backup\ORCL\ORCL_1.log >NUL:

if exist C:\oracle_exp\export_backup\ORCL\ORCL.dmp copy C:\oracle_exp\export_backup\ORCL\ORCL.dmp C:\oracle_exp\export_backup\ORCL\ORCL_1.dmp >NUL:

exp 'sys/sys@ORCL_10g as sysdba' full=yes file=C:\oracle_exp\export_backup\ORCL\ORCL.dmp log=C:\oracle_exp\export_backup\ORCL\ORCL.log 
--------------------------------------------------------------------------------

=========================================== 
 =========================================
  Expdp logical backup with windows batch job script
  ========================================
========================================== 



 The below script is for windows batch job to backup an Oracle database 10g/11g with expdp (datapump) utility. This script setting oracle_sid, NLS_LANG, some local and remote (mapped) drive locations and Archive_program (7-Zip) location.
it creating and zipped an expdb backup, then copied to various location. (local and remote).

This script keeping three copy of backup file to the local location and eight copy to the remote location.

[Note: select batch job option "Run whether user is logged on or not" and "run with highest Privileges" and give the user password once.]

---------------------------------------------------------------------------------
@echo off

set ORACLE_SID=HALIMDB
set NLS_LANG=.WE8MSWIN1252

SET Today=%Date:~4,2%-%Date:~7,2%-%Date:~10,4% 


SET BASE_FOLDER=C:\oracle_exp\export_backup\HALIMDB
SET BASE_NAME=zip_%ORACLE_SID%
SET DUMPFILE_FOLDER=C:\oracle_exp\export_backup\HALIMDB

SET ARCHIVE_PROGRAM="C:\Program Files\7-Zip\7z.exe"

SET REMOTE_LOC=\\sbwc_share\h$\10g_backup_220_important\Export_backup_10g_220\HALIMDB


SET DUMPFILE_NAME=%ORACLE_SID%.dmp
SET LOGFILE_NAME=%ORACLE_SID%.log
SET BACKUP_FOLDER=%BASE_FOLDER%


SET BACKUP_FILENAME=%BASE_NAME%


REM Script begin
REM expdp, full backup to expdb_dw_dump_dir, 
expdp 'EXPDP_HALIMDB/EXPDP_HALIMDB123@HALIMDB' full=yes directory=expdb_HALIMDB_dump_dir dumpfile=%DUMPFILE_NAME% logfile=%LOGFILE_NAME% PARALLEL=5 

IF %ERRORLEVEL% NEQ 0 GOTO ERROR


if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_3.zip /Y
 if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip /Y
if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip /Y



%ARCHIVE_PROGRAM% a -tzip %BACKUP_FOLDER%\%BACKUP_FILENAME% %DUMPFILE_FOLDER%\%DUMPFILE_NAME% %DUMPFILE_FOLDER%\%LOGFILE_NAME%


IF %ERRORLEVEL% NEQ 0 GOTO ERROR

if exist %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip %REMOTE_LOC%\%BACKUP_FILENAME%_7.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip /Y 
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip /Y
 if exist %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%.zip %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip /Y



copy %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip %REMOTE_LOC%\%BACKUP_FILENAME%.zip /Y


IF %ERRORLEVEL% NEQ 0 GOTO ERROR

DEL %DUMPFILE_FOLDER%\%DUMPFILE_NAME%
DEL %DUMPFILE_FOLDER%\%LOGFILE_NAME%


EXIT 0

:ERROR
EXIT 1

Labels

10g (1) 11g (1) ADRCI (1) Application Server(12) ARCHIVE (1) ASH report (1) ASM (1) Audit trail (1)Authentication (1) AWR report (1) Backup (1) bigtable (1)Bind peeking (1) bug (1) Catalog (1) Command (1) comparison(1) Configure (1) connectivity (1) control file (1)corruption (1) Data Dictionary (1) Data Guard (1) Database(1) Date (2) DB (1) DB Size (1) DBA (1) DEBUG (1) delete(1) delimited (1) EM (1) error (1) exclusive (1) exp (1)expdp (1) find (1) Firewall (1) Flash (1) Flashback in oracle 10g (1) forms (2) Function (1) google (1) how to (1)image (1) impdp (1) insert (1) Job (1) Jobs (1) license (1)lock (2) log Miner (1) LogMiner (1) mail (1) materialized(1) migration (1) MMNL (1) Monitoring (1) Multimedia (1) my sql (1) mysql (1) network (1) null (1) OCA (1) ocp (1) ocr(1) ora (1) ORA-00257 (1) ora-00600 (2) ORA-00604 (1) ORA-01555 (1) ORA-01653 (1) ora-07445 (1) ORA-29701 (1) ORA-32004 (1) oracle (1) oracle 11g (1) Oradim (1) Os (1)Parallel (1) pfile (1) pga (1) PL/SQL (26)plsql (1) port (1) Procedure (1) Profile (1) query (1) quiz(1) RAC (1) RAC 10g (1) RAC 11g (2) recovery (1) redo (1)REGEXP (4) report (1) resetlog (1) rman (5) root ssh solaris (1) rowid (1) Scripts (1) security (1) Send mail (1)SEO (1) server (1) sga (1) share business (1) sms (1) SMTP Server (2) spfile (1) sql (1) sql server (1) Sqlplus(4) sso (1) startup (1) streams (1) sysaux (1) sysdba (1)system (1) table (1) Tablespaces (1) target (1) Temp (1) TNS(1) Trace (1) Trigger (4) truncate (1) Tuning(15) undo (1) Unix (1) update (1) Utility (10)V$ACTIVE_SESSION_HISTORY (1) version (2) view (1) vmware(1) wallet (1) windows (2) x64 bits VS x86 bits (1) xml in oracle (8)