?
Solved

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

Posted on 2003-10-24
8
Medium Priority
?
3,978 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

862 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