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;