Solved

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

Posted on 2003-10-24
8
3,488 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
maybe it should be:

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

Expert Comment

by:kalosi
Comment Utility
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
 
LVL 1

Author Comment

by:mtae
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:kalosi
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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 250 total points
Comment Utility

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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now