backup control file to trace on NT

[mike73]

I am running 8.1.7 on windows 2000 server.

every night, we ran an export on our database and we also backup our control file to trace. I want to backup that trace file as well in case the server crash.


does any body know how or have any scripts to copy just the trace file which is the backup of the control files. I know in Unix or Linux you can do that but I am not sure on Windows. How does windows know which trace is the backup of the control file???

any one had any advises ???



[Edited by mike73 on 03-20-2002 at 02:11 PM]

[sambavan]

You can use the time stamp

Sam

[ashley75]

interesting point !!!!


[Edited by ashley75 on 03-20-2002 at 02:37 PM]

[mike73]

Sam,

Can you please thow out some hint ????


How do you use timestamp on OS side ???

Thanks

[jmodic]

The following query from the same session that performs "alter controlfile to trace" will return the name of the trace file created. Of course it'll be put into your udump directory.

select 'ORA' || ltrim(to_char(p.spid,'09999')) || '.TRC' as trace_file
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1);

P.S.
If you are using 9i and you are doing "backup controlfile to trace" from inside oracle's DBMS_JOB scheduler, the abowe query is useles, as in 9i each queue job writs its trace information into its separate, dedicated trace file named 'J00n.TRC'. I haven't found out yet how to determine the number "n" from inside the scheduled job. If anyone knows I'd be very glad to hear it.

[mike73]

Thanks for your scripts





[Edited by mike73 on 03-20-2002 at 04:21 PM]

[mike73]

jMODIC,

Thanks for your scripts, but now, can you show me to copy from the query I ran into a scripts so it can aumatically copy to the backup location????

Thanks

[jmodic]

If this is an SQL*Plus script you would simply need to use a HOST command. For example, change my script to:

spool myscript.sql

select 'HOST COPY x:\mydir\ORA' || ltrim(to_char(p.spid,'09999')) || '.TRC x:\mybackupdir\ctrl_to_trace.txt'
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1);

spool off

@myscript.sql

On the other hand, if you are running this from a PL/SQL procedure, you should either use EXTPROC facility (you are limited to this option if you are using 8.0) or use some Java procedure from inside the database to copy files. If by any chance you are still using Oracle7 then your only option would be to use DBMS_PIPE. I'm sure you can find many scripts on the net that have samples of any and all of the above options.

[mike73]

thanks again, below is my scripts is not quite working 100%, can you tell me what happened to my scritps ???


SET ECHO ON
SET TERMOUT ON
SET STOPONERROR OFF

SPOOL C:\TEMP\INFO.LOG

CONNECT SYSTEM/MANAGER

ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

select 'HOST COPY C:\ORACLE\ORA817\ADMIN\mydata\UDUMP\ORA' || ltrim(to_char(p.spid,'09999')) || '.TRC C:\TEMP\CTRL_TO_TRACE.TXT'
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1);

DISCONNECT;

exit;

SPOOL OFF;

[jmodic]

SET ECHO ON
SET TERMOUT ON
SET STOPONERROR OFF
SET FEEDBACK OFF

CONNECT SYSTEM/MANAGER

ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

SPOOL C:\TEMP\copy.sql

select 'HOST COPY C:\ORACLE\ORA817\ADMIN\mydata\UDUMP\ORA' || ltrim(to_char(p.spid,'09999')) || '.TRC C:\TEMP\CTRL_TO_TRACE.TXT'
from v$process p, v$session s
where p.addr = s.paddr
and s.sid = (select sid from v$mystat where rownum=1);

SPOO OFF

@C:\TEMP\copy.sql
exit;