OraFAQ Forum: Windows » Batch file issues

Home » Infrastructure » Windows » Batch file issues

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Batch file issues [message #99977]

Tue, 04 June 2002 07:59

Leo Hernandez
Messages: 1
Registered: June 2002

Junior Member

Dear Oracle DBA (or anyone good with dos commands...)

I ran into something quirky in WinNT4.0 with regards to SQL*plus, batch file, and dos commands.
I have created script files and a batch file to automate a hotbackup. The scripts and batch file works!
However, the simplest part of the project is what is giving me a road block.

For some reason, in WinNT4.0, when a batch file executes sql*plus, which in turn issue a command like this:
Host echo "some string......"

The echo to host does not get outputted, rather I get a "The handle is invalid" on the console.
Why am I trying to echo out to "some string..." from within sql*plus (you might ask)? Well the batch file and sql-scripts works a little something like this:

The batch file will start sql*plus, login, start the first sql-script, AND redirect output to a log file.

For example: my_batch.bat
---------------------------------------------------------------------------
set oracle_sid=orcl
set oracle_home=c:oracleora81

sqlplus system/manager @my_sqlscript.sql > my_err.log
---------------------------------------------------------------------------

my_sqlscript.sql then would run it's on sql statements, issue os copy commands (host copy c:filepath.... f::filepath......), run other sql-scripts, AND echo out strings to host (host echo "some string...."). I echo out the strings to host so that it becomes part of the output to the log file (my_err.log). This way, I can make notation regarding the stages of the scripts.

For example: my_sqlscript.sql
---------------------------------------------------------------------------
alter tablespace system begin backup;
host copy c:oradataorclsystem01.dbf f:system.dbf
alter tablespace system end backup;

host echo "end of system tablespace backup..."
---------------------------------------------------------------------------

Here's the quirk: When I developed these scripts in Windows2000, the output to my_err.log read like this:
---------------------------------------------------------------------------
Tablespace altered.
1 file(s) copied
Tablespace altered.

"end of system tablespace backup..."
---------------------------------------------------------------------------

This is what I expected to get. All feedbacks coming from both sql*plus ("Tablespace altered") and all os feedback ("1 file(s) copied) and the echo were outputted and redirected to my_err.log.

HOWEVER, the DBs I need to run these scripts against are on WinNT4.0. And when I run these same scripts there, this is all I get:
---------------------------------------------------------------------------
Tablespace altered.
Tablespace altered.
---------------------------------------------------------------------------

Obviously, the os feedbacks and echo are missing. In addition, a message gets outputted to the console while this batch runs: "The handle is invalid" And the amount of times this message gets outputted to the console while the batch runs corresponds to the how many "host echo..." commands the sql-scripts issued.

Anyone ever run into this? Does anyone know if this is a patch issue with NT or SQL*plus. I appreciate any "feedbacks.."

I can live without the "host echo..." I can simply replace it with a "select 'some string...' from dual;" But the os feedbacks are crucial.

Thanks,

Leo D. Hernandez
(303) 401-1696
dbbyleo@yahoo.com

Report message to a moderator

Re: Batch file issues [message #99978 is a reply to message #99977]

Tue, 04 June 2002 08:48

Grant
Messages: 578
Registered: January 2002

Senior Member

Here is a script that I generated a long time ago using EZSQL. The first thing you will notice is no double quotes are used with the echo command. I have included it because I think it is a good script with good ideas.

doc
*********************************************************************
* Backup Script Created by EZSQL *
* and SYS *
* for Database MESS.MARLINESS.NET *
* *
* The script will perform the following tasks : *
* *
* 1. Set individual tablespace into backup mode. *
* 2. Copy the datafile(s) to a backup directory. *
* 3. Take the individual tablespace out of backup mode. *
* 4. Optional - Create export parameter file. *
* 5. Optional - Perform a full export. *
* 6. Optional - Create import parameter file. *
* 7. Copy listener.ora file to backup directory. *
* 8. Copy init.ora file to backup directory. *
* 9. Optional - Copy all Files in Backup Directory to 2nd Disk *
* 10. Optional - Copy all Files to tape using NTBACKUP command *
* (overwrites all data on tape) *
* *
*********************************************************************
#
set feedback off
set verify off
set heading off

-- First make backup directory if it does not exist.
-- Then delete old backup files if they do exist.
host MD C:Oracle_backup
host DEL C:Oracle_backup*.*

-- Backup Data Files
-- Put Each tablespace in backup mode,
-- Copy then file to backup directory,
-- Take tablespace out of backup mode
Alter tablespace USERS begin backup;
host echo copying datafile /oradb/d1/oradata/mess/users01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/users01.dbf C:Oracle_backup
Alter tablespace USERS end backup;
Alter tablespace TOOLS begin backup;
host echo copying datafile /oradb/d1/oradata/mess/tools01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/tools01.dbf C:Oracle_backup
Alter tablespace TOOLS end backup;
Alter tablespace TEMP begin backup;
host echo copying datafile /oradb/d1/oradata/mess/temp01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/temp01.dbf C:Oracle_backup
Alter tablespace TEMP end backup;
Alter tablespace SYSTEM begin backup;
host echo copying datafile /oradb/d1/oradata/mess/system01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/system01.dbf C:Oracle_backup
Alter tablespace SYSTEM end backup;
Alter tablespace RBS begin backup;
host echo copying datafile /oradb/d1/oradata/mess/rbs01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/rbs01.dbf C:Oracle_backup
Alter tablespace RBS end backup;
Alter tablespace INDX begin backup;
host echo copying datafile /oradb/d1/oradata/mess/indx01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/indx01.dbf C:Oracle_backup
Alter tablespace INDX end backup;
Alter tablespace DATA begin backup;
host echo copying datafile /oradb/d1/oradata/mess/data01.dbf to C:Oracle_backup
host copy /oradb/d1/oradata/mess/data01.dbf C:Oracle_backup
Alter tablespace DATA end backup;

-- Force a log switch
alter system switch logfile;
--Wait 1 minute for Export to complete...
exec dbms_lock.sleep(60);

-- Back up the archive log files, then delete them
-- from the archive directory. Note : ALL FILES IN THE
-- ARCHIVE DIRECTORY ARE DELETED!
host echo copying archive files to C:Oracle_backup
host copy C:oracleora81DatabaseArchive*.* C:Oracle_backup
host echo deleting old archive files...
host DEL C:oracleora81DatabaseArchive*.*

-- Make Backup Control File in backup directory
host echo backing up control file to C:Oracle_backupControl.Ctl
alter database backup controlfile to 'C:Oracle_backupControl.Ctl';

-- Backup Control File in trace file
host echo backing up control file to trace
alter database backup controlfile to trace;

-- Back up Online REDO log files. Most likely these will not be used, but you should have them backed up anyway.
host echo backing up redo log file /oradb/d1/oradata/mess/redo01.log to C:Oracle_backup
host copy /oradb/d1/oradata/mess/redo01.log C:Oracle_backup
host echo backing up redo log file /oradb/d1/oradata/mess/redo02.log to C:Oracle_backup
host copy /oradb/d1/oradata/mess/redo02.log C:Oracle_backup
host echo backing up redo log file /oradb/d1/oradata/mess/redo03.log to C:Oracle_backup
host copy /oradb/d1/oradata/mess/redo03.log C:Oracle_backup

-- Backup Network configuration files
host copy C:oracleora81NetworkAdmin*.* C:Oracle_backup

-- Backup INIT.ORA file
host copy C:oracleora81DatabaseInitORCL.ORA C:Oracle_backup

--Perform Full Database Export
host EXP parfile=C:backup_EXP.Par

--Wait 5 minutes for Export to complete...
exec dbms_lock.sleep(300);

--Copy Files to 2nd backup directory
host xcopy C:Oracle_backup*.* Z: /f /i
host ntbackup backup C:Oracle_backup /d "Oracle Backup" /b /t normal
Exit

Report message to a moderator

Re: Batch file issues [message #99981 is a reply to message #99977]

Wed, 05 June 2002 07:24

Grant
Messages: 578
Registered: January 2002

Senior Member

Not sure what the problem is. This site might help:

http://www.robvanderwoude.com/index.html

You might also set echo on in your SQL script.

Report message to a moderator

Previous Topic:	Re: Installing Oracle on Windows 2000 m/c with Pentium 4 processor
Next Topic:	Re: Oracle 8i Installer dies in Win 2000 Prof. - also on 98SE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 01:16:05 CDT 2025