-
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]
-
You can use the time stamp
Sam
Thanx
Sam
Life is a journey, not a destination!
-
interesting point !!!!
[Edited by ashley75 on 03-20-2002 at 02:37 PM]
-
Sam,
Can you please thow out some hint ????
How do you use timestamp on OS side ???
Thanks
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks for your scripts
[Edited by mike73 on 03-20-2002 at 04:21 PM]
-
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
-
If this is an SQL*Plus script you would simply need to use a HOST command. For example, change my script to:
Code:
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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;
-
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|