<- Scripts
@echo off
:: Purpose: Export schema using TTS.
:: OS: Windows 20nn
:: Oracle: 11g
:: Dependencies: The Directory Object "datapump" must exist.
:: -------------------------------------------------------------------------------
:: Process Overview
:: 1. Set script user variables.
:: 2. Run each script routine in succession - QC each step:
:: Comments
:: Change this script using your TS names as required.
==============================================================================
::::::::::::::::::::
:: Script Preamble
::::::::::::::::::::
setlocal ENABLEDELAYEDEXPANSION
set appScriptName=%~n0
:::::::::::::::::::::::::::::::::::::::::::
:: Init User Variables
:::::::::::::::::::::::::::::::::::::::::::
set usrScriptsDir=C:\app\scripts\bat&
set usrSchema=SCOTT&
set usrSchemaPassword=MySchemaPW&
set usrExpSourceDir=R:\exports&
set usrExpDestDir=R:\zExports&
set usrDBFSource=C:\oradata\dbf\SCOTT1&
set usrDBFDest=R:\zExports&
:: Enable Options
set optSetTS_R=1&
set optExport=1&
set optCopyFiles=1&
set optSetTS_RW=1&
:::::::::::::::::::::::::::::::::::::::::::
:: Init Oracle Variables
:::::::::::::::::::::::::::::::::::::::::::
set ORACLE_SID=DB01&
set ORACLE_HOME=C:\app\oracle\product\11.2.0.3\dbhome_1&
set UN=sys&
set PW=MySYSPW&
:::::::::::::::::::::::::::::::::::::::::::
:: Init Common API for System Vars\Actions
:::::::::::::::::::::::::::::::::::::::::::
mkdir %usrScriptsDir%\logs > NUL 2>&1
mkdir %usrScriptsDir%\tmp > NUL 2>&1
set appTempDir=%usrScriptsDir%\tmp
set appTmpScript=%appTempDir%\%appScriptName%.tmp.sql
del %appTmpScript% > NUL 2>&1
:::::::::::::::::::::::::::::::::::::::::::
:: Header
:::::::::::::::::::::::::::::::::::::::::::
cls
echo Migrate BS Schema via TTS
echo ====================================================
echo usrSchema: %usrSchema%
echo ----------------------------------------------------
echo usrExpSourceDir: %usrExpSourceDir%
echo usrExpDestDir: %usrExpDestDir%
echo ----------------------------------------------------
echo usrDBFSource: %usrDBFSource%
echo usrDBFDest: %usrDBFDest%
echo ----------------------------------------------------
echo optSetTS_R: %optSetTS_R%
echo optExport: %optExport%
echo optCopyFiles: %optCopyFiles%
echo optSetTS_RW: %optSetTS_RW%
echo ====================================================
echo.
echo Disable any conflicting jobs before running (OT, backups, etc.)
echo.
echo Press Enter to continue or Ctrl-C to abort. & pause > NUL
call :AppendLog Started
:::::::::::::::::::::::::::::::::::::::::::
:: Set TS to READ ONLY
:::::::::::::::::::::::::::::::::::::::::::
if %optSetTS_R% == 1 (
call :AppendLog "Setting TS to READ ONLY"
set sScript=%appTempDir%\%appScriptName%.read-only.sql
echo Running: !sScript!
(
echo ALTER TABLESPACE SI_DATA_%usrSchema%_TS READ ONLY;
echo ALTER TABLESPACE SI_INDEX_%usrSchema%_TS READ ONLY;
echo ALTER TABLESPACE TIDATA_%usrSchema%_TS READ ONLY;
echo ALTER TABLESPACE TIINDX_%usrSchema%_TS READ ONLY;
echo ALTER TABLESPACE TITEXTINDX_%usrSchema%_TS READ ONLY;
) > !sScript!
echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @!sScript!
::QC
echo SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name LIKE '^%%!usrSchema!^%%'; > %appTmpScript%
echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @%appTmpScript%
echo. && echo QC: Ensure all TS READ ONLY & pause
)
:::::::::::::::::::::::::::::::::::::::::::
:: TTS Export
:::::::::::::::::::::::::::::::::::::::::::
if %optExport% == 1 (
call :AppendLog "Export Schema %usrSchema% TTS"
expdp system/%PW% directory=datapump dumpfile=%usrSchema%_tts.dmp reuse_dumpfiles=yes job_name=expTTS logfile=expTTS.log transport_tablespaces=SI_DATA_%usrSchema%_TS,SI_INDEX_%usrSchema%_TS,TIDATA_%usrSchema%_TS,TIINDX_%usrSchema%_TS,TITEXTINDX_%usrSchema%_TS
::QC
echo. && echo QC: Ensure Export Completed OK & pause
)
:::::::::::::::::::::::::::::::::::::::::::
:: Metadata Export
:::::::::::::::::::::::::::::::::::::::::::
if %optExport% == 1 (
call :AppendLog "Export Schema %usrSchema% Metadata"
expdp system/%PW% directory=datapump dumpfile=%usrSchema%_meta.dmp reuse_dumpfiles=yes job_name=expMeta logfile=expMeta.log schemas=%usrSchema% CONTENT=METADATA_ONLY
::QC
echo. && echo QC: Ensure Export Completed OK & pause
)
:::::::::::::::::::::::::::::::::::::::::::
:: Copy Files to Destination System
:::::::::::::::::::::::::::::::::::::::::::
if %optCopyFiles% == 1 (
call :AppendLog "Copying Files to Destination System"
echo Copying Export Files...
copy /y %usrExpSourceDir%\%usrSchema%_tts.dmp %usrExpDestDir%
copy /y %usrExpSourceDir%\%usrSchema%_meta.dmp %usrExpDestDir%
echo Copying Datafiles...
copy /y %usrDBFSource%\*.dbf %usrDBFDest%
::QC
echo. && echo QC: Ensure All Files Copied OK & pause
)
:::::::::::::::::::::::::::::::::::::::::::
:: Set TS to READ WRITE
:::::::::::::::::::::::::::::::::::::::::::
if %optSetTS_RW% == 1 (
call :AppendLog "Setting TS to READ WRITE"
set sScript=%appTempDir%\%appScriptName%.read-write.sql
echo Running: !sScript!
(
echo ALTER TABLESPACE SI_DATA_%usrSchema%_TS READ WRITE;
echo ALTER TABLESPACE SI_INDEX_%usrSchema%_TS READ WRITE;
echo ALTER TABLESPACE TIDATA_%usrSchema%_TS READ WRITE;
echo ALTER TABLESPACE TIINDX_%usrSchema%_TS READ WRITE;
echo ALTER TABLESPACE TITEXTINDX_%usrSchema%_TS READ WRITE;
) > !sScript!
echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @!sScript!
::QC
echo SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name LIKE '^%%!usrSchema!^%%'; > %appTmpScript%
echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @%appTmpScript%
)
:::::::::::::::::::::::::::::::::::::::::::
:: End
:::::::::::::::::::::::::::::::::::::::::::
echo.
echo.
call :AppendLog Ended
exit /b
::::::::::::::::::::
:: Functions
::::::::::::::::::::
:AppendLog %1 %2
rem Usage: call :AppendLog "<Message>" [INDENT]
rem Ex1: call :AppendLog "Exporting"
rem Ex2: call :AppendLog "Processing X15" INDENT
if not exist "logs" (mkdir logs)
rem Get Cmd Line Vars
set sMsg=%~1& rem Removes any quotes.
set sOption=%~2& rem Removes any quotes.
rem Get\Set Date and Time Vars
set sDay=%date:~7,2%&
set sMonth=%date:~4,2%&
set sYear=%date:~10,4%&
set sDate=%sMonth%\%sDay%\%sYear%&
set sHour=%time:~0,2%&
set sMinute=%time:~3,2%&
set sTime=%sHour%:%sMinute%&
rem Output to Log
if "%sMsg%" == "Started" (
echo -------------------------------------------------------------------------------- >> logs\%appScriptName%.hist.log
)
if "%sOption%" == "INDENT" (
echo ^- %sMsg% >> logs\%appScriptName%.hist.log
) else (
echo %sDate%.%sTime%,%sMsg% >> logs\%appScriptName%.hist.log
)
rem Output to Console
if "%sOption%" == "INDENT" (
echo ^- %sMsg%
) else (
if "%sMsg%" == "Ended" (
echo.
echo %sDate%.%sTime%,*** Process Ended ***
) else (
echo %sDate%.%sTime%,%sMsg%
)
)
exit /b
