Windows Oracle Batch Files

Category : Home > Databases > Oracle

Scripts to backup Oracle database on Windows NT

Here's a set of scripts to do database export, full database backup (putting tablespaces in

backup mode, not using RMAN) and scan the backup logs on Windows NT. The datafiles are first

copied to another filesystem using ocopy and then backuped with ntbackup. You can also see

some examples of using DOS variables in sqlplus. These scripts are examples only, obviously

you would have to adjust them for your environment.

REM ==========================================================================

REM Script# 1

REM This is the main backup script to run with Windows Scheduler / at command

REM ==========================================================================

set dbconn=system/manager@SAMPLEDB

set admdir=c:\admin\daily\

set logdir=c:\admin\daily\out\

set oradir1=c:\oracle\oradata\SAMPLEDB\

set oradir2=d:\oracle\oradata\SAMPLEDB\

set oradir3=e:\oracle\oradata\SAMPLEDB\

set orabckdir=e:\backup\SAMPLEDB\

set orabckdir1=e:\backup\SAMPLEDB\c\

set orabckdir2=e:\backup\SAMPLEDB\d\

set orabckdir3=e:\backup\SAMPLEDB\e\

REM

REM save previous copies of backup logs

REM

del %logdir%backup2.txt

ren %logdir%backup1.txt backup2.txt

ren %logdir%backup.txt backup1.txt

echo "SAMPLEHOST overnight.bat backup commenced ..." >>%logdir%backup.txt

date /t >>%logdir%backup.txt

time /t >>%logdir%backup.txt

REM

REM Run full export of the database

REM

echo "Exporting SAMPLEDB" ... >> %logdir%backup.txt

d:\oracle\ora81\bin\exp.exe parfile=d:\oracle\admin\SAMPLEDB\exp\SAMPLEDBexp.par >>

%logdir%backup.txt

REM

REM Compress the export file with winzip command line utility

REM May also put a line to remove the old dump files here

REM

c:\progra~1\winzip\wzzip.exe e:\oracle\oradata\SAMPLEDB\export\SAMPLEDB_full.zip

e:\oracle\oradata\SAMPLEDB\export\SAMPLEDB_full.dmp

REM

REM Append the database export log to backup.txt

REM

type d:\oracle\admin\SAMPLEDB\exp\logs\SAMPLEDB_full.log >> %logdir%backup.txt

echo "Finished export of SAMPLEDB" >> %logdir%backup.txt

echo "Generating sql scripts for hot SAMPLEDB backup ..." >> %logdir%backup.txt

sqlplus -s %dbconn% @%admdir%sql\backup.sql >> %logdir%backup.txt

echo "Putting SAMPLEDB in backup mode ..." >> %logdir%backup.txt

REM

REM Put all tablespaces in backup mode

REM

sqlplus -s %dbconn% @%admdir%sql\BEGIN_SAMPLEDB_BACKUP.sql >> %logdir%backup.txt

REM

REM Save the database files' location in SAMPLEDB_files.txt

REM

dir %oradir1% > %orabckdir%SAMPLEDB_files.txt

dir %oradir2% >> %orabckdir%SAMPLEDB_files.txt

dir %oradir3% >> %orabckdir%SAMPLEDB_files.txt

REM

REM File listing from sqlplus

REM

sqlplus -s %dbconn% @%admdir%sql\dbfiles.sql >> %orabckdir%SAMPLEDB_files.txt

REM

REM Copy Oracle files to backup directory on disk

REM

D:\oracle\ora81\bin\ocopy %oradir1%* %orabckdir1% >> %logdir%backup.txt

D:\oracle\ora81\bin\ocopy %oradir2%* %orabckdir2% >> %logdir%backup.txt

D:\oracle\ora81\bin\ocopy %oradir3%* %orabckdir3% >> %logdir%backup.txt

REM

REM backup filesystems

REM

REM D: drive is backed up last because it has the archived redo logs

REM

ntbackup backup c:\ e:\ d:\ /v /e /b /hc:on /d "SAMPLEHOST Overnight backup" /t normal /l

"%logdir%backup.txt"

ntbackup eject

date /T >> %logdir%backup.txt

time /T >> %logdir%backup.txt

echo "Putting SAMPLEDB in normal mode" >> %logdir%backup.txt

sqlplus -s %dbconn% @%admdir%sql\END_SAMPLEDB_BACKUP.sql >> %logdir%backup.txt

echo "SAMPLEHOST backup finished ..." >>%logdir%backup.txt

date /t >>%logdir%backup.txt

time /t >>%logdir%backup.txt

REM

REM Scan the logs for errors and produce the summary log file

REM

%admdir%SearchForErr.Bat %logdir%backup.txt

REM ==========================================================================

REM Script# 2

REM Script to sumbit the backup job using at command

REM ==========================================================================

@echo off

at \\SAMPLEHOST 23:30 /every:M,T,W,Th,F c:\admin\daily\overnight.bat

REM ==========================================================================

REM Script# 3

REM clean_archive.bat

REM Remove Oracle archived logs older than seven days

REM

REM NOTE: you need nt resource kit for this script to work

REM

REM ==========================================================================

C:\ntreskit\forfiles -pE:\oracle\oradata\SAMPLEDB\archive -d-7 -c"CMD /C del @FILE"

REM ==========================================================================

REM Script# 4

REM zip_archive.bat

REM Compress archived logs older than 2 days

REM

REM ==========================================================================

C:\ntreskit\forfiles -pD:\oracle\oradata\SAMPLEDB\archive -d-2 -c"CMD /C

c:\progra~1\winzip\wzzip.exe -amex -k @FILE.zip @FILE"

REM ==========================================================================

REM Script# 5

REM

REM SearchForErr.Bat

REM

REM This job will search the supplied file for error messages produce

REM a summary file if any are found.

REM

REM %1 - Supplied Output File to be checked.

REM c:\admin\daily\search.txt - Strings of text to be searched for.

REM c:\admin\daily\out\Error.txt - Errorlog of this job.

REM

REM ==========================================================================

@echo off

IF EXIST DEL c:\admin\daily\out\Summary.txt

echo . > c:\admin\daily\out\Summary.txt

echo Checking file - %1 >> c:\admin\daily\out\Summary.txt

echo . >> c:\admin\daily\out\Summary.txt

echo . >> c:\admin\daily\out\Summary.txt

Findstr /i /g:\admin\daily\search.txt %1 >> c:\admin\daily\out\Summary.txt

REM ==========================================================================

REM

REM search.txt

REM File containing possible error messages to search for in the backup logs

REM Do not put this comments in search.txt file

REM

REM ==========================================================================

/* Server : SG_Q1 Date :

backup commenced

backup finished

Backup of

Backup started

Backup completed

\<* error *

^error *

\<* abort *

^abort *

\<* failed *

^failed *

\<* command *

^command *

Process Aborted

Attempt to fetch logical page

not to object

is already hashed.

was expected to be the first page of

Table Corrupt: Extent

Data size mismatch occurred

bytes found,

expected data length

Could not find row in Sysindexes

CHECKTABLE

Descriptor for system table

hash table.

via RID failed

the last RID

Could not retrieve row

less than or equal

is not correctly

Sort failed because dpages

an incorrect value.

to correct the value

your command.

Table Corrupt: Offset table is incorrect

correct offset

is=

Table Corrupt: The row number and offset

check this page

number table=

Table Corrupt: Object id wrong;

in ext=

(name =

Allocation Discrepancy: Page

pg#=

on extent=

that has an illegal

The non_clustered leaf row

indexid

tried to remove resource lock

Unable to find buffer holding

dbid =

Table corrupt:

Table Corrupt: object id does not match

Table Corrupt: Extent id

but used bit off

is empty but

Status =

is not marked suspect.

I/O error detected

is incorrectly trying

lockid=

not found;

Table Corrupt: The index id

Table Corrupt: Keys

check row

page number

Table Corrupt: Page is linked but not

extent id=

Extent not within segment

is not in segment

uninitialized pages encountered

Could not find virtual page

System error detected

error log

Memmove() was called with

Table Corrupt: A page is linked

page:page number=

Table Corrupt: Page linkage

pointing to this page=

this page=

Table Corrupt: Object id

Database Corrupt: The last checkpoint

different checkpoint

Table Corrupt: Extent structures

extent#=

be=

cannot be opened

The SA can drop

Cannot deallocate extent

does not match

deallocated.

Could not find leaf row

data row from logical data page

page split.

There is insufficient system memory

Table Corrupt: Type id

Could not find row in Sysdatabases

CHECKTABLE

Your server command (process

deadlocked

\<command\>

not match

following page:

should be

Table Corrupt: The values in adjust table

starting from the end of the table

table in this row

Verify started

Verify completed

ORA-

-- ==========================================================================

--

-- dbfiles.sql

-- SQL script to get list of all database files, redo log members

-- and control files

--

-- ==========================================================================

SELECT 'Data files' FROM DUAL;

SELECT name FROM v$datafile;

SELECT 'Redo logs' FROM DUAL;

SELECT member FROM v$logfile;

SELECT 'Control files' FROM DUAL;

SELECT name FROM v$controlfile;

EXIT;

-- ==========================================================================

--

-- backup.sql

-- SQL script to generate scripts to start and end database backup

--

-- ==========================================================================

set pagesize 0 echo off feedback off verify off

spool C:\admin\daily\sql\BEGIN_SAMPLEDB_BACKUP.sql

SELECT 'ALTER SYSTEM SWITCH LOGFILE;'

FROM dual;

SELECT 'ALTER TABLESPACE ' || tablespace_name || ' BEGIN BACKUP;'

FROM dba_tablespaces;

SELECT 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;'

FROM dual;

SELECT 'EXIT;'

FROM dual;

spool off

spool C:\admin\daily\sql\END_SAMPLEDB_BACKUP.sql

SELECT 'ALTER TABLESPACE ' || tablespace_name || ' END BACKUP;'

FROM dba_tablespaces;

SELECT 'ALTER SYSTEM SWITCH LOGFILE;'

FROM dual;

SELECT 'EXIT;'

FROM dual;

spool off

EXIT;