This setup consists of setting up manual compressed log shipping for oracle using windows batch scripts.
Prod site:
* Create following directories in log_archive_dest: OLD, TMP
* Guessing archives are created with ARC extension. Other case scripts should be modified
* Reserve a domain user account and grant him access to both prod and standby servers. For easy, i granted him local administrator account in both servers but u may try to restrict access. If there is no domain available, then create local user account with the same user name & password on both prod and standby servers.
* You should download gnu awk and arj32 utility from net and place then on somewhere at %PATH%
* Share log_archive_dest folder of the standby and schedule script on prod with the user account you reserved
* You should modify {...} parameters for your environment
logmove.bat
set ORACLE_SID={ORACLE_SID}
set LOG_DEST={log_archive_dest}
set SCRIPT_DEST={where to place this script}
set LOGFILE=%SCRIPT_DEST%\logmove.log
set TEMP_FILE=%SCRIPT_DEST%\logmove.tmp
dir /OD %LOG_DEST%\*.ARC|findstr .ARC|awk "{print $4}">%TEMP_FILE%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @arj32 a -e %LOG_DEST%\TMP\%%i.ARJ %LOG_DEST%\%%i >> %LOGFILE%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @move /Y %LOG_DEST%\%%i %LOG_DEST%\OLD\ >> %LOGFILE%
dir /OD %LOG_DEST%\TMP\*.ARC.ARJ |findstr .ARC.ARJ |awk "{print $4}">%TEMP_FILE%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @move /Y %LOG_DEST%\TMP\%%i \\{standby server}\archive >> %LOGFILE%
rem del /Q %TEMP_FILE% >> %LOGFILE%
Standby Site:
* Guessing archives are created with ARC extension. Other case scripts should be modified
* U should download gnu awk and arj32 utility from net and place then on somewhere at %PATH%
* You should modify {...} parameters for your environment
* Schedule script with the user account you reserved
logapply.bat
set ORACLE_SID={ORACLE_SID}
set LAGTIME=0.0417
rem set LAGTIME=0
set SCRPTLOC={where to place this script}
set LOG_DEST={log_archive_dest} set TEMP_FILE=%SCRPTLOC%\logapply.tmp
set LOGFILE=%SCRPTLOC%\logapply.log
dir /OD %LOG_DEST%\*.ARC.ARJ |find ".ARC.ARJ" |awk "{print $5}">%TEMP_FILE%
{Drive of {log_archive_dest}: for example C:, D: ...}
cd %LOG_DEST%
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @arj32 e -n %%i >> %LOGFILE%
sqlplus /nolog @%SCRPTLOC%\standby_recover.sql %LAGTIME% 1 >> %LOGFILE%
sqlplus /nolog @c:\temp\recover.sql >> %LOGFILE%
if {%ERRORLEVEL%} == {0} (
echo Database %ORACLE_SID% is available >> %SCRPTLOC%\sby.log
) else (
echo Database %ORACLE_SID% is NOT available >> %SCRPTLOC%\sby.log
)
del /Q c:\temp\recover.sql
del /Q %TEMP_FILE%
standby_recover.sql
connect sys/{sys password} as sysdba
set trimspool on linesize 200 head off feedback off sqlprompt "--" verify off
spool c:\temp\recover.sql
select 'connect sys/{sys password} as sysdba'||chr(10)||
'set autorecovery on'||chr(10)||
'recover standby database until time '''||
to_char(sysdate - &1,'YYYY-MM-DD:HH24:MI:SS')||''';'||chr(10)||
'exit;' from dual;
spool off
exit
logremove.bat
set ORACLE_SID={ORACLE_SID}
set SCRPTLOC={where to place this script}
set LOG_DEST={log_archive_dest}
set TEMP_FILE=%SCRPTLOC%\tmp.txt
set TEMP_FILE2=%SCRPTLOC%\tmp2.txt
set LOGFILE=%SCRPTLOC%\logremove.log
set LASTLOG=99999
echo.|time|date|find "current" >> %LOGFILE%
echo.|sqlplus /nolog @%SCRPTLOC%\lastarchno.sql |find "MAKEGREP"|awk "{print $2}" > %TEMP_FILE%
echo Set and Check if last archive no is valid >> %LOGFILE%
set LAST_ARCHNO=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set LAST_ARCHNO=%%i
echo last archive is %LAST_ARCHNO% >> %LOGFILE%
rem
rem Following checks if log is recycled to beginning, then exits script. This stuation should be handled manually
rem
for %%i in (%LOG_DEST%\*%LASTLOG%.ARC) do goto end
rem
rem Following checks if last archive log is valid
rem
for %%i in (%LOG_DEST%\*%LAST_ARCHNO%.ARC) do goto cont
echo Last archive is not valid >> %LOGFILE%
goto end
:cont
echo %LAST_ARCHNO% > %SCRPTLOC%\lastarchno.txt
echo deleting archives >> %LOGFILE%
dir /O-N %LOG_DEST%\*.ARC |find ".ARC" |awk "{print $5}">%TEMP_FILE%
:repeat
set CURRENT_FILE=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set CURRENT_FILE=%%i
if "%CURRENT_FILE%" == "NONE" goto end
call set TMP_STR=%%CURRENT_FILE:%LAST_ARCHNO%=%%
if NOT "%TMP_STR%" == "%CURRENT_FILE%" goto end
type %TEMP_FILE%|find /v "%CURRENT_FILE%" > %TEMP_FILE2%
type %TEMP_FILE2% > %TEMP_FILE%
set CURRENT_FILE=%LOG_DEST%\%CURRENT_FILE%
echo deleting %CURRENT_FILE% and %CURRENT_FILE%.ARJ >> %LOGFILE%
del /Q %CURRENT_FILE% >> %LOGFILE%
del /Q %CURRENT_FILE%.ARJ >> %LOGFILE%
goto repeat
:end
del /Q %TEMP_FILE% >> %LOGFILE%
del /Q %TEMP_FILE2% >> %LOGFILE%
lastarchno.sql
connect sys/{sys password} as sysdba
set trimspool on linesize 200 head off feedback off sqlprompt "--" verify off
select 'MAKEGREP' grep,max(sequence#) from v$log_history;
exit
Prod site:
* Share script folder of the standby where lastarchno.txt is in and schedule script on prod with the user account you reserved
logremoveprod.bat
set ORACLE_SID={ORACLE_SID}
set LOG_DEST={log_archive_dest}\OLD
set SCRIPT_DEST={where to place this script}
set LOGFILE=%SCRIPT_DEST%\logremoveprod.log
set TEMP_FILE=%SCRIPT_DEST%\logremoveprod.tmp
set TEMP_FILE2=%SCRIPT_DEST%\logremoveprod2.tmp
set LASTLOG=199999
echo.|time|date|find "current" >> %LOGFILE%
set LAST_ARCHNO=NONE
for /F "eol=; tokens=1" %%i in (\\{standby server}\scripts\lastarchno.txt) do @set LAST_ARCHNO=%%i
echo last archive is %LAST_ARCHNO% >> %LOGFILE%
rem
rem Following checks if log is recycled to beginning, then exits script. This stuation should be handled manually
rem
for %%i in (%LOG_DEST%\*%LASTLOG%.ARC) do goto end
rem
rem Following checks if last archive log is valid
rem
for %%i in (%LOG_DEST%\*%LAST_ARCHNO%.ARC) do goto cont
echo Last archive is not valid >> %LOGFILE%
goto end
:cont
echo checking archive validity >> %LOGFILE%
dir /O-N %LOG_DEST%\*.ARC |find ".ARC" |awk "{print $4}">%TEMP_FILE%
:rpt
set CURRENT_FILE=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set CURRENT_FILE=%%i
if "%CURRENT_FILE%" == "NONE" goto hata
call set TMP_STR=%%CURRENT_FILE:%LAST_ARCHNO%=%%
if NOT "%TMP_STR%" == "%CURRENT_FILE%" goto cnt
type %TEMP_FILE%|find /v "%CURRENT_FILE%" > %TEMP_FILE2%
type %TEMP_FILE2% > %TEMP_FILE%
goto rpt
:hata
echo Applied last archive does not exists in list >> %LOGFILE%
goto end
:cnt
echo deleting archives >> %LOGFILE%
dir /O-N %LOG_DEST%\*.ARC |find ".ARC" |awk "{print $4}">%TEMP_FILE%
:repeat
set CURRENT_FILE=NONE
for /F "eol=; tokens=1" %%i in (%TEMP_FILE%) do @set CURRENT_FILE=%%i
if "%CURRENT_FILE%" == "NONE" goto end
call set TMP_STR=%%CURRENT_FILE:%LAST_ARCHNO%=%%
if NOT "%TMP_STR%" == "%CURRENT_FILE%" goto end
type %TEMP_FILE%|find /v "%CURRENT_FILE%" > %TEMP_FILE2%
type %TEMP_FILE2% > %TEMP_FILE%
set CURRENT_FILE=%LOG_DEST%\%CURRENT_FILE%
echo deleting %CURRENT_FILE% >> %LOGFILE%
del /Q %CURRENT_FILE% >> %LOGFILE%
goto repeat
:end
del /Q %TEMP_FILE% >> %LOGFILE%
del /Q %TEMP_FILE2% >> %LOGFILE%
No comments:
Post a Comment