Batch File Scripts..

Batch file 1

cmd >
C: > edit test.bat ("Creating Batch file named test")

test.bat file contains

sqlplus hr/hr@ROCK @D:\ctas.sql

and ctas.sql contains

create table batch_test as select * from employees;
exit;

============================================================

Batch file 2

Now, I will create a batch file for hot backup, that will copy all the tablespaces & archived redo log files, daily. That will be running automatically (at specified time, interval of days), and copy the all the tablespaces including SYSTEM, archived redo log files, REDOLOGS, and control files.

cmd>
c:> edit hot.bat

sqlplus system/manager@rock @d:\hot.sql

This batch file is calling hot.sql and hot.sql contains following commands.

host MD d:\hot\"%DATE:/=_%"
host MD d:\hot\"%DATE:/=_%"\ARCHIVELOGS
alter tablespace SYSTEM begin backup;
host copy D:\oracle\oradata\rock\SYSTEM01.DBF d:\hot\"%DATE:/=_%";
alter tablespace SYSTEM end backup;
exit;

The above (hot.sql) sript, create a new folder named Sun 09_23_2007 (current date) in directory d:\hot\ and ARCHIVELOGS folder within that created folder, Then, backup begins and copies the SYSTEM01.dbf file and pastes in Sun 09_23_2007; when all files for specified tablespace are copied it places the tablspace in default mode. (end backup).

You can check the 09_23_2007 folder whether files are copied or not, and issue the following command to check

SELECT * from v$backup;
check the status column If its status is NOT ACTIVE that means file is not in backup mode.
SQL> select * from V$backup;
FILE# STATUS CHANGE# TIME
---------------------------- ---------- ----------------
1 NOT ACTIVE 787967 24-SEP-07

If you issue the alter tablespace system command the status of file will be changed.

SQL> alter tablespace system begin backup;
Tablespace altered.

SQL> select * from V$backup;
FILE# STATUS CHANGE# TIME
------- ---------------- ---------- ---------
1 ACTIVE 788012 24-SEP-07

You can checkup the status column of v$backup, to verify whether script ran successfuly, and all the tablspace are in default(End Backup/No Backuo mode).

Note: This hot.sql script contains only one system tablespace, i will test for all tablespace Archived redo log files, REDOLOGS, and Control Files, some time later.