Archive

Posts Tagged ‘shell’

Oracle Shell Scripting in Windows.

This note presents some basic techniques for creating Windows batch files that connect to SQL*Plus and RMAN.

To run an SQL script using SQL*Plus, place the SQL along with any SQL*Plus commands in a file and save it on your operating system. For example, save the following script in a file called “C:\example.sql”.

CONNECT scott/tiger
SPOOL C:\example.txt
SET LINESIZE 100
SET PAGESIZE 50
SELECT * FROM emp;
SPOOL OFF
EXIT;

Next, create a batch file called “C:\example.bat” containing the following command.

sqlplus /nolog @C:\example.sql

The resulting batch file can be run manually, by double-clicking on it, or scheduled using the Scheduled Tasks Wizard (Start > Programs > Accessories > System Tools > Scheduled Tasks) or any other Windows scheduler.

The method is very similar when using Recovery Manager (RMAN). As an example, place the following RMAN commands in a file called “C:\rman.txt”.

run {
allocate channel dev0 type disk;
backup as compressed backupset database format 'I:\BACKUP\db_%u';
SQL 'alter system archive log current';
backup as compressed backupset archivelog all format 'I:\BACKUP\arc_%u' delete input;
backup current controlfile format 'I:\BACKUP\ctl_%u';
}
EXIT;

Next create a batch file called “C:\rman.bat” containing the following command.

rman TARGET username/pass@instance NOCATALOG @rman.txt

This command can include a ‘catalog=’ entry if a recovery catalog is used. Once again, resulting batch file can be run manually or scheduled.

Hope this helps.

For more information see:
SQL*Plus User’s Guide and Reference
Oracle Database Backup and Recovery Reference
UNIX Commands for DBAs