Link to home
Start Free TrialLog in
Avatar of mtae
mtae

asked on

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?
Avatar of schwertner
schwertner
Flag of Antarctica image

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’;
Avatar of Danielzt
Danielzt

maybe it should be:

ALTER DATABASE BACKUP CONTROLFILE TO 'file_name' ;
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
Avatar of mtae

ASKER

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.
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
You have to log as SYSDBA. The syntax should be exact. Try with apostrofy, without apostrofy, make experiments. Post the SQL here.
ASKER CERTIFIED SOLUTION
Avatar of ubasche
ubasche

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mtae

ASKER

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?