Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

alter database backup controlfile to trace - finding the file generated in windows batch script

Posted on 2003-10-24
8
Medium Priority
?
3,803 Views
Last Modified: 2011-09-20
Hello,

I'm trying to automate my Oracle backups.  I'm using Oracle 8.1.7 and Windows 2000.

I can issue both hot and cold backups fine.  When I issue the command:

alter database backup controlfile to trace

it is created a textural copy of the database's control file in the USER_DUMP_DEST location.  I want to then take the trace file I've just generated and move it to a directory that is being backed up to tape (I cannot use a directory under ORACLE_BASE as backing this up in windows causes my databases to crash and hence the need to move the backed up control file.)  I'm automating my backups through windows command batch programs and wondered if there was a way I could get the latest trace file from the command line within a batch and then copy it to another location?  This is tricky because I do not know the name of the file generated so would have to do it based on a timestamp.

Can anyone help me here?
0
Comment
Question by:mtae
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 9614541
Point the directory and the name of the trace file:


ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS <Path and Controlfilename>;

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/u03/ctl_file’;
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 9614638
maybe it should be:

ALTER DATABASE BACKUP CONTROLFILE TO 'file_name' ;
0
 
LVL 4

Expert Comment

by:kalosi
ID: 9627782
The syntax of the command is the following


ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<file_name>'.  
You can specify also the REUSE clausule to allow overwriting the existing file.

david
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:mtae
ID: 9643273
Thank you for your comments.

I don't think the SQL syntax is correct for the ALTER DATABASE statement.  

When I specify "TO TRACE" I cannot provide a file_name, so cannot use the AS '<file_name>' like you suggest.  

"I get a ORA-00933: SQL command not properly ended" error.  When I look at the SQL reference I have it doesn't seem that I can specify a file name when I use TO TRACE.

I can backup a binary copy of my controlfile to a file name, but I think it would be useful to have one backed up to trace.  Perhaps this is not necessary if I have a binary copy?  I may modify my batch programs to make a binary and textural backup of the controlfile and make sure that the binary one is in the location on the server that is backed up to tape.  The only problem with this is that if I need to create the controlfile during recovery and I needed to restore from tape, I would manually have to write the statement rather than be able to use the trace file.
0
 
LVL 4

Expert Comment

by:kalosi
ID: 9643300
The command is correct. At least it should be. it's a copy paste from the oracle documentation. can you please post the command exactly as you typed it ??

david
0
 
LVL 48

Expert Comment

by:schwertner
ID: 9648231
You have to log as SYSDBA. The syntax should be exact. Try with apostrofy, without apostrofy, make experiments. Post the SQL here.
0
 
LVL 3

Accepted Solution

by:
ubasche earned 1000 total points
ID: 9648407

The syntax

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '<file_name>'.

is for Oracle9 only. As you are using Oracle8 you can't use the
"AS <file_name>" part of the syntax. In Oracle8 you have to backup the binery version of the control file with specifying the location or you backup to trace without filename. You then have to find the trace file.

Backup the binary control file:
ALTER DATABASE BACKUP CONTROLFILE TO 'file_name' ;

Backup the trace file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Here is some code that backs up the control file to trace and returns the file name. You can use it in Oracle8 and don't have to specify the file name:

declare
     location varchar2(255);
begin
    dbms_utility.exec_ddl_statement('alter database backup controlfile to trace');

    SELECT iv.traceloc
    INTO location
    FROM (
      SELECT m.value||'/'||lower(i.instance_name)||'_ora_'||p.spid||'.trc' traceloc
      FROM v$session s, v$process p, v$instance i, v$parameter m
      WHERE m.name = 'user_dump_dest' AND p.addr= s.paddr AND userenv('SESSIONID') = s.audsid
      AND s.server='DEDICATED'
      UNION
      SELECT m.value||'/'||lower(i.instance_name)||'_'||lower(substr(substr(p.program,-5),1,4))||'_'||p.spid||'.trc' traceloc
      FROM v$session s, v$process p, v$instance i, v$parameter m
      WHERE m.name = 'background_dump_dest' AND p.addr= s.paddr AND userenv('SESSIONID') = s.audsid
      AND s.server!='DEDICATED'
    ) iv;

    dbms_output.put_line('TRACE FILE LOCATED AT: '||location);
end;
/



0
 
LVL 1

Author Comment

by:mtae
ID: 9648446
Hi,

Perhaps I'm doing something else wrong.

I am issuing the following:

C:\> sqlplus "/ as sysdba"

I'm then connected to Oracle 8.1.7.0.0.  I then issue:

SQL> alter database backup controlfile to trace as 'c:\oracle\ctl_file';
alter database backup controlfile to trace as 'c:\oracle\ctl_file'
                                                            *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL>

I don't suppose this is a version issue?  Here are the details from v$version:

Oracle 8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

I'm sure this isn't related but incidentially I am using Windows 2000 version 5.0 (Build 2195: Service Pack 2)

The compatible init parameter is 8.1.0 (I changed this to 8.1.6 to see if it had any effect and I still get the error)

I have found the syntax diagram that shows the TO TRACE AS 'file_name' as you mention, but only in documentation for 9i, I'm wondering whether I can do this in 8.1.7?

I think I will do both a binary file backup and a backup to trace.  Can you confirm that the TO TRACE AS 'file_name' option is only available in 9i, or am I doing something wrong in 8.1.7?
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question