Home » SQL & PL/SQL » SQL & PL/SQL » Host command NOT getting executed in Hot backup script. (windows Machine,oracle 9i)
Host command NOT getting executed in Hot backup script. [message #333778] Mon, 14 July 2008 07:33 Go to next message
zee3110
Messages: 4
Registered: July 2008
Location: chennai
Junior Member
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 !!

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
Re: Host command NOT getting executed in Hot backup script. [message #333779 is a reply to message #333778] Mon, 14 July 2008 07:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
What do you expect?
You are using DBMS_OUTPUT to display the string.
Not to execute it.
Re: Host command NOT getting executed in Hot backup script. [message #333781 is a reply to message #333779] Mon, 14 July 2008 07:45 Go to previous messageGo to next message
zee3110
Messages: 4
Registered: July 2008
Location: chennai
Junior Member
But is that y i use '!' symbol there..?
if im wrong...
how does it work...!!??!!!pls let me know...
any correction would be appreciated???
pls cut the code and mark the corrected line with some red color.

Thanks
Re: Host command NOT getting executed in Hot backup script. [message #333784 is a reply to message #333781] Mon, 14 July 2008 07:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
DBMS_OUTPUT is a rudimentary debugging tool that is used to let server side processed output messages that can be seen by the session that ran the process.

I'm guessing that you've found this script, and are trying to make it work without understanding much of what you're doing.

What is does is to produce a list of commands that you can copy and paste into SQL*Plus, which will then execute them, performing a hot backup.
Re: Host command NOT getting executed in Hot backup script. [message #333785 is a reply to message #333781] Mon, 14 July 2008 07:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I see that you have copied the script from
http://www.orafaq.com/scripts/back_rec/backup.txt
The hint is in the last 3 commented lines.
Re: Host command NOT getting executed in Hot backup script. [message #333790 is a reply to message #333785] Mon, 14 July 2008 07:58 Go to previous messageGo to next message
zee3110
Messages: 4
Registered: July 2008
Location: chennai
Junior Member
Yes your correct...
im jus reying to learn!!
i would appreciate if you tel me how to make Host command work under this condition.

Thanks
Re: Host command NOT getting executed in Hot backup script. [message #333795 is a reply to message #333790] Mon, 14 July 2008 08:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have to type the command into SQL*Plus.

All you're doing is using SQL*Plus to display the output from this procedure.

If you want to execute the list of commands that the procedure produces, cut and paste them back into SQL*Plus.
Re: Host command NOT getting executed in Hot backup script. [message #333816 is a reply to message #333781] Mon, 14 July 2008 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
zee3110 wrote on Mon, 14 July 2008 14:45
But is that y i use '!' symbol there..?
if im wrong...
how does it work...!!??!!!pls let me know...
any correction would be appreciated???
pls cut the code and mark the corrected line with some red color.

Thanks

Read OraFAQ Forum Guide and don't use IM speak.

Regards
Michel

Re: Host command NOT getting executed in Hot backup script. [message #333824 is a reply to message #333790] Mon, 14 July 2008 09:20 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> i would appreciate if you tel me how to make Host command work under this condition.
I already gave you a hint.
Just see the last 3 commented lines in the original script!.

Re: Host command NOT getting executed in Hot backup script. [message #333858 is a reply to message #333778] Mon, 14 July 2008 11:05 Go to previous message
zee3110
Messages: 4
Registered: July 2008
Location: chennai
Junior Member
Excellent... It's working.
Now i have learnt the procedure.
Thanks Everybody.....
much appreciated.

Thanks
Previous Topic: declare and set a variable within a trigger?
Next Topic: Compile error
Goto Forum:
  


Current Time: Sat Dec 10 10:35:38 CST 2016

Total time taken to generate the page: 0.07360 seconds