oracledba.help
Scripts

Script: tts_import.bat

<- Scripts

@echo off
:: Purpose:      Import 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=MySchemaPW4&

set usrDBFDir=C:\oradata\dbf\%usrSchema%&
set usrTEMP_TS=TEMP&

:: Enable Options
set optImportTTS=1&
set optSetTS_RW=1&
set optSetDefaultTS=1&
set optImportMeta=1&
set optGrantPrivs=1&
set optRecompObjs=1&

:::::::::::::::::::::::::::::::::::::::::::
:: Init Oracle Variables
:::::::::::::::::::::::::::::::::::::::::::
set ORACLE_SID=DB02&
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 optImportTTS:    %optImportTTS%
echo optSetTS_RW:     %optSetTS_RW%
echo optSetDefaultTS: %optSetDefaultTS%
echo optImportMeta:   %optImportMeta%
echo optGrantPrivs:   %optGrantPrivs%
echo optRecompObjs:   %optRecompObjs%
echo ====================================================
echo.
echo Disable any conflicting jobs before running ^(OT, backups, etc.^)
echo.
echo Ensure datafiles are in exactly the same place as source ^(Ex: O:\oradata\x15^)
echo.
echo Press Enter to continue or Ctrl-C to abort. & pause > NUL
call :AppendLog Started


:::::::::::::::::::::::::::::::::::::::::::
:: TTS Import TTS
:::::::::::::::::::::::::::::::::::::::::::
if %optImportTTS% == 1 (
   call :AppendLog "Import Schema %usrSchema% TTS"
   set sDatafiles='%usrDBFDir%\SI_DATA.dbf','%usrDBFDir%\SI_INDEX.DBF','%usrDBFDir%\TIDATA.DBF','%usrDBFDir%\TIINDX.DBF','%usrDBFDir%\TITEXTINDX.DBF'
   echo sDatafiles: !sDatafiles!  
   impdp system/%PW% directory=datapump dumpfile=%usrSchema%_tts.dmp job_name=impTTS logfile=%usrSchema%.tts_import.log transport_datafiles=!sDatafiles!

   ::QC
   echo. && echo QC: Ensure Import Completed OK ^(disreguard any objects that did not compile for now^) & 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%
   echo. && echo QC: Ensure all TS READ WRITE ^(ONLINE^) & pause   

)

:::::::::::::::::::::::::::::::::::::::::::
:: Set DEFAULT TS
:::::::::::::::::::::::::::::::::::::::::::
if %optSetDefaultTS% == 1 (
   call :AppendLog "Setting Default TS"
   set sScript=%appTempDir%\%appScriptName%.set_default_ts.sql

   echo ALTER USER %usrSchema% DEFAULT TABLESPACE SI_DATA_%usrSchema%_TS; > !sScript!
   echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @!sScript!

   ::QC
   echo. && echo QC: Ensure Default TS Set & pause
)

:::::::::::::::::::::::::::::::::::::::::::
:: TTS Import Meta
:::::::::::::::::::::::::::::::::::::::::::
if %optImportMeta% == 1 (
   call :AppendLog "Import Schema %usrSchema% Meta"
   impdp system/%PW% directory=datapump dumpfile=%usrSchema%_meta.dmp job_name=impMeta logfile=%usrSchema%.meta_import.log schemas=%usrSchema% content=metadata_only

   ::QC
   echo. && echo QC: Ensure Import Completed OK ^(disreguard any objects that did not compile for now^) & pause
)

:::::::::::::::::::::::::::::::::::::::::::
:: Grant Privs
:::::::::::::::::::::::::::::::::::::::::::
if %optGrantPrivs% == 1 (
   call :AppendLog "Granting Privs"
   set sScript=%appTempDir%\%appScriptName%.grant-privs.sql

   (
   echo alter user %usrSchema% default tablespace SI_DATA_%usrSchema%_TS;
   echo alter user %usrSchema% temporary tablespace %usrTEMP_TS%;   
   echo alter user %usrSchema% profile default;
   echo alter user %usrSchema% QUOTA UNLIMITED ON SI_DATA_%usrSchema%_TS;
   echo alter user %usrSchema% QUOTA UNLIMITED ON SI_INDEX_%usrSchema%_TS;
   echo alter user %usrSchema% QUOTA UNLIMITED ON TIDATA_%usrSchema%_TS;
   echo alter user %usrSchema% QUOTA UNLIMITED ON TIINDX_%usrSchema%_TS;
   echo alter user %usrSchema% QUOTA UNLIMITED ON TITEXTINDX_%usrSchema%_TS;
   echo grant connect,resource,imp_full_database,finst to %usrSchema%;
   echo grant create procedure          to %usrSchema%;
   echo grant create sequence           to %usrSchema%;
   echo grant create trigger            to %usrSchema%;
   echo grant create type               to %usrSchema%;
   echo grant execute on ctxsys.ctx_ddl to %usrSchema%;
   echo alter user %usrSchema% account unlock;
   ) > !sScript!
   echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @!sScript!

   ::QC
   echo. && echo QC: Ensure Privs Granted OK & pause
)


:::::::::::::::::::::::::::::::::::::::::::
:: Recompile Objects
:::::::::::::::::::::::::::::::::::::::::::
if %optRecompObjs% == 1 (
   call :AppendLog "Recompiling Objects"
   set sScript=%appTempDir%\%appScriptName%.recomp-objs.sql

   (
   echo SELECT object_name FROM dba_objects WHERE status='INVALID' and owner='%usrSchema%';
   echo ^@%ORACLE_HOME%\rdbms\admin\utlrp.sql
   echo SELECT object_name FROM dba_objects WHERE status='INVALID' and owner='%usrSchema%';
   ) > !sScript!
   echo exit | %ORACLE_HOME%\bin\sqlplus -s "%UN%/%PW% as sysdba" @!sScript!

   ::QC
   echo. && echo QC: Ensure No New INVALID Objects & pause
)

:::::::::::::::::::::::::::::::::::::::::::
:: 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