Login or Register to Ask a Question and Join Our Community


Programming

Host command NOT getting executed in Hot backup script.

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming Host command NOT getting executed in Hot backup script.
# 1  
Old 07-14-2008
Host command NOT getting executed in Hot backup script.
Hi All,
i have problem in the following Sql Script.
problem is : HOST (!) command is not getting executed.pls de-bugg this HoTBackup Script......
Most urgent...!
Thank You very Much !!

Code:
SQL> set serveroutput on
SQL> set trimspool on
SQL> set line 500
SQL> set head off
SQL> set feed off
SQL>
SQL> spool c:\test\backup.txt
SQL>
SQL> declare
  2    copy_cmnd constant varchar2(30) := 'copy';       -- Use "ocopy" for NT
  3    copy_dest constant varchar2(30) := 'c:\test\'; -- C:\BACKUP\ for NT
  4
  5    dbname  varchar2(30);
  6    logmode varchar2(30);
  7  begin
  8    select name, log_mode
  9    into   dbname, logmode
 10    from   sys.v_$database;
 11
 12    if logmode <> 'ARCHIVELOG' then
 13       raise_application_error(-20000,
 14                       'ERROR: Database must be in ARCHIVELOG mode!!!');
 15       return;
 16    end if;
 17
 18    dbms_output.put_line('spool backup.'||dbname||'.'||
 19                         to_char(sysdate, 'ddMonyy')||'.log');
 20
 21    -- Loop through tablespaces
 22    for c1 in (select tablespace_name ts
 23               from   sys.dba_tablespaces)
 24    loop
 25      dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
 26      -- Loop through tablespaces' data files
 27      for c2 in (select file_name fil from   sys.dba_data_files where  tablespace_name = c1.ts)
 28      loop
 29        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
 30      end loop;
 31
 32      dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
 33    end loop;
 34
 35    -- Backup controlfile and switch logfiles
 36    dbms_output.put_line('alter database backup controlfile to trace;');
 37    dbms_output.put_line('alter database backup controlfile to '||''''|| copy_dest||'control.'||dbnam
 38    dbms_output.put_line('alter system switch logfile;');
 39    dbms_output.put_line('spool off');
 40  end;
 41  /
spool backup.ORA9I.14Jul08.log
alter tablespace SYSTEM begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\SYSTEM01.DBF c:\test\
alter tablespace SYSTEM end backup;
alter tablespace UNDOTBS1 begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\UNDOTBS01.DBF c:\test\
alter tablespace UNDOTBS1 end backup;
alter tablespace TEMP begin backup;
alter tablespace TEMP end backup;
alter tablespace CWMLITE begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\CWMLITE01.DBF c:\test\
alter tablespace CWMLITE end backup;
alter tablespace DRSYS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\DRSYS01.DBF c:\test\
alter tablespace DRSYS end backup;
alter tablespace EXAMPLE begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\EXAMPLE01.DBF c:\test\
alter tablespace EXAMPLE end backup;
alter tablespace INDX begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\INDX01.DBF c:\test\
alter tablespace INDX end backup;
alter tablespace ODM begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\ODM01.DBF c:\test\
alter tablespace ODM end backup;
alter tablespace TOOLS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\TOOLS01.DBF c:\test\
alter tablespace TOOLS end backup;
alter tablespace USERS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\USERS01.DBF c:\test\
alter tablespace USERS end backup;
alter tablespace XDB begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\XDB01.DBF c:\test\
alter tablespace XDB end backup;
alter tablespace TTS begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\TTS.DBF c:\test\
alter tablespace TTS end backup;
alter tablespace TESTING begin backup;
!copy C:\ORANT\ORA9I\ORADATA\ORA9I\TESTING.DBF c:\test\
alter tablespace TESTING end backup;
alter database backup controlfile to trace;
alter database backup controlfile to 'c:\test\control.ORA9I.14Jul081739';
alter system switch logfile;
spool off
SQL>
SQL> spool off
SQL>
SQL> set head on
SQL> set feed on
SQL> set serveroutput off
SQL>

AS you can see,it's the Host command is being printed instead of getting executed.
Plsssssssss Help.

Thanks
# 2  
Old 07-14-2008
Tools Not sure if I understand your question, but
Why the following?
Code:
29        dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);

If I am reading this right, by putting the '!' at the beginning of the line it will not be executed.
# 3  
Old 07-14-2008
dbms writes to stdout. Adding a ! in front of it has no reall effect.

If your requirement is to call OS commands from inside PL/SQL read this:
http://www.idevelopment.info/data/Or..._1.shtml<br />
# 4  
Old 07-14-2008
Hi,
yes your exectly right.. im trying to execute OS command inside pl/sql.
and therez the error occurs..i dnt know how to do it.
The link u have given is not working for me.

Thanks
Zaheer
# 5  
Old 07-17-2008
Sorry about the link -use dbms_pipes
from orafaq.com - a site you should visit.
Quote:
There is no direct way to execute operating system commands from PL/SQL. PL/SQL doesn't have a "host" command, as with SQL*Plus, that allows users to call OS commands. Nevertheless, the following workarounds can be used:

Database Pipes

Write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
Code:
CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
  RETURN INTEGER IS
    status   NUMBER;
    errormsg VARCHAR2(80);
    pipe_name VARCHAR2(30);
BEGIN
  pipe_name := 'HOST_PIPE';
  dbms_pipe.pack_message( cmd );
  status := dbms_pipe.send_message(pipe_name);
  RETURN status;
END;
/

Quote:
External Procedure Listeners:

From Oracle 8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.

Using Java

See example at http://www.orafaq.com/scripts/plsql/oscmd.txt

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell script to check a command executed sucessfully or not

Hi All, I am trying to write a shell script to check if a command executed successfully or not in rhel 7 and finding the installed tomcat version. I am using below script. var4=$(find / -name "catalina.jar" ! -size 0 |egrep -v... (6 Replies)
Discussion started by: sravani25
6 Replies

2. Shell Programming and Scripting

awk command not getting executed in shell script

I am able to execute awk command from shell prompt. but the same command is not getting executed when written and run in a bash script the command from bash cmd prompt. awk '/world/{for (i=2; i<NF; i++) printf $i " "; print $NF}1' myfile >tmp$$ ; mv tmp$$ myfile file: # hello world my... (4 Replies)
Discussion started by: ashima jain
4 Replies

3. Shell Programming and Scripting

Check/get the exit status of a remote command executed on remote host through script

Geeks, Could you please help me out in my script and identify the missing piece. I need to check/get the exit status of a remote command executed on remote host through script and send out an email when process/processes is/are not running on any/all server(s). Here's the complete... (5 Replies)
Discussion started by: lovesaikrishna
5 Replies

4. UNIX for Dummies Questions & Answers

Set Command to output a log of every command executed in the script

Hi Guys, I like to output every command executed in the script to a file. I have tried set -x which does the same. But it is not giving the logs of the child script which is being called from my script. Is there any parameters in the Set command or someother way where i can see the log... (2 Replies)
Discussion started by: mac4rfree
2 Replies

5. AIX

Script not getting executed via cron but executes when executed manually.

Hi Script not getting executed via cron but executes successfully when executed manually. Please assist cbspsap01(appuser) /app/scripts > cat restart.sh #!/bin/ksh cd /app/bin date >>logfile.out echo "Restart has been started....." >>logfile.out date >>logfile.out initfnsw -y restart... (3 Replies)
Discussion started by: samsungsamsung
3 Replies

6. Shell Programming and Scripting

Need help! command working ok when executed in command line, but fails when run inside a script!

Hi everyone, when executing this command in unix: echo "WM7 Fatal Alerts:", $(cat query1.txt) > a.csvIt works fine, but running this command in a shell script gives an error saying that there's a syntax error. here is content of my script: tdbsrvr$ vi hc.sh "hc.sh" 22 lines, 509... (4 Replies)
Discussion started by: 4dirk1
4 Replies

7. Shell Programming and Scripting

Making script show command (e.g. copy) being executed and variable substitution?

When script is running you only see when some of the commands are not successfull. Is there a way to see which command are executed and to show the substitution of variables as every line is executed ? (3 Replies)
Discussion started by: gr0124
3 Replies

8. Shell Programming and Scripting

Oracle hot backup script issue

Hi, I have a script which we use to take hot backup of oracle database.It connects to database and saves all database related activities in a spool file.Then the spool file is run for taking hot backup. But at one stage it is giving an error. Please find my hot backup script as below: ... (2 Replies)
Discussion started by: dwiravi
2 Replies

9. Shell Programming and Scripting

perl - why is the shell script executed before the print command?

i'm writing some simple scripts to help me learn perl. why does the print command get called after the shell script is executed? the purpose of the shell script is to simply echo to the screen "script run". which is does, but before the print command, you can clearly see the shell script is... (3 Replies)
Discussion started by: mjays
3 Replies

10. UNIX for Advanced & Expert Users

Shell script on Oracle hot backup got an error

Hi Unix Shell Script Expert, We have a robust shell script (from old time) for our 9i database hot backup but generated error: Shell Script Content: #check for auto log mode and redirect as early as possible # autolog_enable $AUTOLOG_MODE $ORACLE_BASE/${ORASID_LOW}log/cron_hotbackup.log... (1 Reply)
Discussion started by: oradbus
1 Replies
Login or Register to Ask a Question