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