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