| 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
|
|
|
|
| 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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
