Contoh Batch File Untuk Backup Database Oracle Per Schema

pfile.sql

create pfile='f:\exp\pfile.ora' from spfile;
exit;

pfile.bat

sqlplus wi/%sqlstr%@itcapps2 as sysdba @pfile.sql

ren f:\exp\pfile.ora pfilekvn%date:~4,2%%date:~7,2%%date:~12,2%.log

delpath.bat

d:

REM --------------------------------

cd D:\app\Administrator\diag\rdbms\itcapps2\itcapps2\incident

call f:\delold.bat

REM --------------------------------

cd D:\app\Administrator\diag\rdbms\itcapps2\itcapps2\trace

call f:\delold.bat

REM --------------------------------

cd D:\app\Administrator\diag\tnslsnr\athena\listener\alert

call f:\delold.bat

REM --------------------------------

cd D:\app\Administrator\diag\tnslsnr\athena\listener\trace

call f:\delold.bat

REM --------------------------------

delold.bat

FORFILES /S /D -14 /C "cmd /c IF @isdir == TRUE rd /S /Q @path"

FORFILES /M *.xml /d -14 /c "cmd /c del @file

FORFILES /M *.trm /d -14 /c "cmd /c del @file

FORFILES /M *.trc /d -14 /c "cmd /c del @file

ddl.sql

set pagesize 0
set long 90000 

connect userdp/pword

/*------------------------- TABLE ---*/
spool table.scd 

  SELECT DBMS_METADATA.GET_DDL ('TABLE', A.OBJECT_NAME, A.OWNER)
    FROM ALL_OBJECTS A
   WHERE     A.OWNER IN
                ('MIS',
                 'MISB',
                 'HRIS',
                 'ICS',
                 'BUDGETING',
                 'SPS',
                 'HDIS',
                 'AKTIVA',
                 'ITCSUITE',
                 'ITIS',
                 'IAS',
                 'ELEARN',
                 'ITCM',
                 'IPOINT',
                 'ISGATE',
                 'ITCENTERP',
                 'MONWARE',
                 'BILLING',
                 'PMO')
         AND A.OBJECT_TYPE = 'TABLE'
ORDER BY OBJECT_NAME;

spool off 
/*------------------------- TABLE ---*/
/*---ULANG UNTUK OBJECT TYPE LAIN ---*/


EXIT

ddl.bat

sqlplus /nolog @ddl.sql
rem set dt=%date:~4,2%%date:~7,2%%date:~12,2%
rem set fld=ddl\DDL%dt%

rem mkdir %fld%

ren tble.scd tble%dt%.scd

ren view.scd view%dt%.scd

ren func.scd func%dt%.scd

ren proc.scd proc%dt%.scd

ren pach.scd pach%dt%.scd

ren pacb.scd pacb%dt%.scd

ren trig.scd trig%dt%.scd

ren indx.scd indx%dt%.scd

ren mzvw.scd mzvw%dt%.scd

ren seqc.scd seqc%dt%.scd

ren type.scd type%dt%.scd

f:\7z a -pxxx f:\exp\kvnddl%date:~4,2%%date:~7,2%%date:~12,2%.7z *.scd

del *.scd

mv.bat

f:\7z a -pxxx f:\exp\kvnlog%date:~4,2%%date:~7,2%%date:~12,2%.7z f:\exp\*.log

f:\7z a -pxxx f:\exp\kvnlog%date:~4,2%%date:~7,2%%date:~12,2%.7z f:\*.bat

f:\7z a -pxxx f:\exp\kvnlog%date:~4,2%%date:~7,2%%date:~12,2%.7z f:\*.sql

move f:\exp\*.7z f:\exp\done\

del f:\exp\*.log

pack.bat

@echo off
setlocal enabledelayedexpansion

call :getdate

set fld=f:\exp\
set file=7z
set /a flnm=wi.7z

set log=%fld%chain%dt%.log
set tab= 
set schema=mis,misb,pmo,billing,monware,isgate,ipoint,itcm,elearn,ias,itis,itcsuite,aktiva,hdis,sps,budgeting,ics,hris

echo start %tab%%tab% %date% %time% >> %log%

echo beginpfile %tab% %date% %time% >> %log%
call pfile.bat
echo endpfile %tab%%tab% %date% %time% >> %log%

echo begindelold %tab% %date% %time% >> %log%
call delpath.bat
echo enddelold %tab%%tab% %date% %time% >> %log%

f:

echo beginddl %tab% %date% %time% >> %log%
call ddl.bat
echo endddl %tab%%tab% %date% %time% >> %log%

FOR %%A IN (%schema%) DO (
   set sch=%%A
   echo begin!sch! %tab% %date% !time! >> %log%
   set schf=kvn!sch!
   call :rtrim

   set file=!schf:~0,7!%dt%
   set flnm=%fld%done\!file!.7z
   echo !flnm! >> %log%
   if exist !flnm! (
      echo file !flnm! sudah ada.>> %log%
   ) else (
      echo !flnm! >> !file!.dmp
      del !file!.dmp
      expdp userid=userdp/%userdp% schemas=!sch! dumpfile=!file!.dmp logfile=!file!.log directory=DUMPDIR
      rem F:\7z a -pxxx %fld%!file!.7z %fld%!file!.dmp
      rem del %fld%*.dmp  
   )

   echo end!sch! %tab% %date% !time! >> %log%
)

rem untuk 7z hasil dmp smua file
for /f %%f in ('dir f:\exp\*.dmp /b') do (
   rem echo %%~nf.dmp
   rem 7z a -pxxx %%~nf.7z %%~nf.dmp
   f:\7z a -pxxx -m0=lzma2 -mmt8 %fld%%%~nf.7z %fld%%%~nf.DMP
   del %fld%%%~nf.DMP
)

echo finish %tab%%tab% %date% %time% >> %log%

call F:\mv.bat
:: End of main program
GOTO End

::subroutine

:: getdate subroutine starts here
:getdate
   
   for /F "tokens=1-4 delims=/- " %%a in ('date/T') do set TGL=%%d%%b%%c
   set /a dt = TGL-1
   set /a jam = %time:~0,2%
   set /a thn = %date:~12,2%

   if %jam% geq 19 ( 
      if %jam% leq 24 ( 
         set dt = %TGL%
      ) else (
  set dt = TGL-1
      )
   ) else (
      set dt = TGL-1                               
   )

   set dt=%dt:~4,4%%thn%

:: end of getdate subroutine
GOTO :EOF


:: rtrim subroutine starts here
:rtrim
   rem set str=KVNPMO               
   rem echo."%str%"
   set schf=%schf%##
   set schf=%schf:                ##=##%
   set schf=%schf:        ##=##%
   set schf=%schf:    ##=##%
   set schf=%schf:  ##=##%
   set schf=%schf: ##=##%
   set schf=%schf:##=%
   rem echo."%str%"
:: end of rtrim subroutine
GOTO :EOF

:: End of batch file
:End