Batch File For Taking Cold Backup And Expdp

In this blog, you will learn about batch file for taking Cold Backup and Expdp.

In many case, we need to take the Cold backup. I am happy to share this batch file, which will take the full database backup and finally it will automatically start the database after the Cold backup.

1. Create a file name backup.txt and save the following contents:
   

   REM
   echo off
   echo coldbackup started
   for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
   set dayofweek=%%i
   set month=%%j
   set day=%%k
   set year=%%l
   set curtime=%time%
   set fname=BOOST-%%i-%%j-%%k-%%l
   )
   md e:\dbbackup\%fname%
   set oracle_sid=boost --> here change your database name
   sqlplus -s "sys/maku as sysdba" @e:\coldbackup.sql
   echo cold backup completed succesffully.
   pause

   After saving, rename it as backup.bat.
   
   
2. Now, create coldbackup.sql, given below:
   

   set lines 1000
   set head off
   set term off
   set feedback off
   spool e:\coldbackup.bat
   select 'copy '||name||' e:\dbbackup\%fname%' from v$datafile;
   select 'copy '||name||' e:\dbbackup\%fname%' from v$controlfile;
   select 'copy '||name||' e:\dbbackup\%fname%' from v$tempfile;
   select 'copy '||member||' e:\dbbackup\%fname%' from v$logfile;
   spool off
   shutdown IMMEDIATE
   host e:\coldbackup.bat
   startup
   host del e:\coldbackup.bat
   set term on
   set head on
   set feedback on
   set verify on
   EXIT

   Keep this file in e:\ folder .
   
   
3. Our script is ready. Now, run the batchfile "backup.bat". The script will first create a spool file, named "coldbackup.bat" and it contains the necessary command to copy the files from the command line and will execute this spooled batch file by host e:\coldbackup.bat. After the successful execution, we will delete this spooled batch file. We can see the Cold backup got generated in e:\dbbackup folder.

A simple batch file for taking date-wise expdp of a schema is given below: 

@ECHO OFF
: Sets the proper date and time stamp with 24Hr Time for log file naming
: convention

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2%
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

set PATH=D:\oracle\product\10.2.0\db_1\BIN;%PATH% --> set your own path
D:\oracle\product\10.2.0\db_1\BIN\expdp aiwa/aiwa@proddb dumpfile=prod_aiwa_%dtStamp%.dmp logfile=prod_aiwa_%dtStamp%.log directory=DATA_PUMP_DIR schemas=aiwa
pause