backup control file to trace on NT
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: backup control file to trace on NT

  1. #1
    Join Date
    Aug 2001
    Posts
    390
    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]

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    You can use the time stamp

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jan 2002
    Posts
    474
    interesting point !!!!


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

  4. #4
    Join Date
    Aug 2001
    Posts
    390
    Sam,

    Can you please thow out some hint ????


    How do you use timestamp on OS side ???

    Thanks

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Aug 2001
    Posts
    390
    Thanks for your scripts





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

  7. #7
    Join Date
    Aug 2001
    Posts
    390
    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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Aug 2001
    Posts
    390
    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;


  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width