Windows下编写批处理脚本来启动和重置Oracle数据库

@echo off 
net start OracleXETNSListener 2>nul 
net start OracleServiceXE 2>nul 
@oradim -startup -sid XE -starttype inst > nul 2>&1 

@echo off 
REM 
REM The script assumes that user can connect using "/ as sysdba" 
REM 
REM ================= 
REM Restore procedure 
REM ================= 
REM 
REM If Installed Oracle home is also lost and oracle binaries were 
REM re-installed or the Oracle is installed to new oracle home location 
REM compared to backup time, then user will be prompted to enter Flash 
REM Recovery Area location. 
REM 
REM For database in NoArchiveLog mode, database is restored to last offline 
REM backup time/scn; 
REM For database in Archive log mode, database is restored from last backup 
REM and a complete recovery is attempted. If complete recovery fails, 
REM user can open the database with resetlogs option provided the files 
REM are not recovery fuzzy. 
REM 
REM The restore log is saved in ?/DATABASE/OXE_RESTORE.LOG 
REM 
 
setlocal 
 
set /p inp="This operation will shut down and restore the database. Are you sure [Y/N]?" 
:checkinp 
if /i "%inp%" == "Y" goto :confirmedyes 
if /i "%inp%" == "n" exit 
:Askagain 
set /p inp= 
goto :checkinp 
 
:confirmedyes 
 
echo Restore in progress... 
 
echo db_name=xe >%temp%\rman_dummy.ora 
echo sga_target=270M >>%temp%\rman_dummy.ora 
 
 
net start oracleserviceXe 
 
REM Startup database in nomount mode using RMAN... 
@( 
echo set echo on^; 
echo startup nomount pfile=%temp%\rman_dummy.ora force^; 
) > %temp%\restore_rman0.dat 
rman target / @%temp%\restore_rman0.dat 
if not %errorlevel% == 0 set Errorstr= RMAN Error - could not startup dummy instance & goto :restorefailederr 
 
@( 
echo connect / as sysdba^; 
echo set head off 
echo set echo off 
echo set linesize 515 
echo variable var varchar2^(512^)^; 
echo execute :var := sys.dbms_backup_restore.normalizefilename^(^'SPFILE2INIT^'^)^; 
echo spool %temp%\spfile2init.log 
echo select sys.dbms_backup_restore.normalizefilename^(^'SPFILE2INIT.ORA^'^) spfile2init from dual^; 
echo exit^; 
) > %temp%\spfile2init.sql 
sqlplus /nolog @%temp%\spfile2init.sql >nul 
FOR /F %%i in (%temp%\spfile2init.log) do set SPFILE2INIT=%%i 
 
@( 
echo connect / as sysdba; 
 echo set head off 
 echo set echo off 
 echo set linesize 515 
 echo variable var varchar2^(512^)^; 
 echo execute :var := sys.dbms_backup_restore.normalizefilename^(^'FRA_LOC^'^)^; 
 echo spool %temp%\restore_rmanlog.log 
 echo select sys.dbms_backup_restore.normalizefilename^(^'OXE_RESTORE.LOG^'^) RESTORE_RMANLOG from dual^; 
 echo exit^; 
) > %temp%\restore_rmanlog.sql 
sqlplus /nolog @%temp%\restore_rmanlog.sql >nul 
FOR /F %%i in (%temp%\restore_rmanlog.log) do set RESTORE_RMANLOG=%%i 
 
if not exist ^"%SPFILE2INIT%^" goto get_rcvarea_loc 
@( 
 echo set echo on^; 
 echo shutdown immediate^; 
 echo startup nomount pfile=^"%SPFILE2INIT%^"^; 
 echo restore ^(spfile from autobackup^) ^(controlfile from autobackup^)^; 
 echo startup mount force^; 
 echo configure controlfile autobackup off^; 
 echo restore database^; 
) > %temp%\restore_rman1.dat 
rman target / @%temp%\restore_rman1.dat trace "%RESTORE_RMANLOG%" 
if not %errorlevel% == 0 set Errorstr= RMAN Error - See log for error & goto :restorefailederr 
goto restored_files 
 
:get_rcvarea_loc 
set /p rcvarea_loc="Enter the flash recovery area location:" 
@( 
 echo set echo on^; 
 echo restore ^(spfile from autobackup db_recovery_file_dest=^'%rcvarea_loc%^'^)^; 
 echo startup nomount force^; 
 echo restore ^(controlfile from autobackup^)^; 
 echo alter database mount^; 
 echo configure controlfile autobackup off^; 
 echo restore database^; 
) > %temp%\restore_rman1.dat 
rman target / @%temp%\restore_rman1.dat trace "%RESTORE_RMANLOG%" 
if not %errorlevel% == 0 set Errorstr= RMAN Error - See log for error & goto :restorefailederr 
goto restored_files 
 
:restored_files 
@( 
 echo connect / as sysdba^; 
 echo declare cursor n1 is select name from v$tempfile^; 
 echo begin 
 echo for a in n1 
 echo loop 
 echo begin 
 echo sys.dbms_backup_restore.deletefile^(a.name^)^; 
 echo exception 
 echo when others then 
 echo null^; 
 echo end^; 
 echo end loop^; 
 echo end^; 
 echo / 
 echo exit^; 
 echo / 
) > %temp%\deltfile.sql 
sqlplus /nolog @%temp%\deltfile.sql >nul 
@( 
 echo connect / as sysdba^; 
 echo set head off 
 echo set echo off 
 echo spool %temp%\logmode.log 
 echo select log_mode from v$database^; 
 echo exit^; 
) > %temp%\logmode.sql 
sqlplus /nolog @%temp%\logmode.sql >nul 
FOR /F %%i in (%temp%\logmode.log) do set LOGMODE=%%i 
 
if "%LOGMODE%" == "NOARCHIVELOG" goto process_noarchivelog 
if "%LOGMODE%" == "ARCHIVELOG" goto process_archivelog 
set Errorstr= Unknown log mode : %LOGMODE% 
goto :restorefailederr 
 
:process_noarchivelog 
@( 
 echo set echo on^; 
 echo alter database open resetlogs; 
) > %temp%\restore_rman2.dat 
rman target / @%temp%\restore_rman2.dat trace "%RESTORE_RMANLOG%" append 
if not %errorlevel% == 0 set Errorstr= RMAN Error - See log for details & goto :restorefailederr 
goto :restoresucess 
 
:process_archivelog 
@( 
 echo set echo on^; 
 echo recover database^; 
 echo alter database open resetlogs; 
) > %temp%\restore_rman2.dat 
rman target / @%temp%\restore_rman2.dat trace "%RESTORE_RMANLOG%" append 
if not %errorlevel% == 0 set Errorstr= RMAN Error - See log for details & goto :restorefailederr 
goto :restoresucess 
 
:restoresucess 
echo Restore of the database succeeded. 
echo Log file is at %RESTORE_RMANLOG%. 
pause Press any key to exit 
exit 
goto :EOF 
 
:restorefailederr 
echo ==================== ERROR ============================= 
echo Restore of the database failed. 
echo %Errorstr%. 
echo Log file is at %RESTORE_RMANLOG%. 
echo ==================== ERROR ============================= 
pause Press any key to exit 
exit 
goto :EOF