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

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?
LVL 1
mtaeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
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
DanielztCommented:
maybe it should be:

ALTER DATABASE BACKUP CONTROLFILE TO 'file_name' ;
0
kalosiCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mtaeAuthor Commented:
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
kalosiCommented:
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
schwertnerCommented:
You have to log as SYSDBA. The syntax should be exact. Try with apostrofy, without apostrofy, make experiments. Post the SQL here.
0
ubascheCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mtaeAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.