Thursday 10 December 2009

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; 


 

1 comment:

Blogger said...

Did you know that that you can earn money by locking special areas of your blog / site?
To begin you need to join AdscendMedia and use their content locking widget.