Re: Newbie question about "alter database create datafile.."

From: Sean M <smckeown_at_adelphia.net>
Date: Thu, 09 Jan 2003 06:19:06 GMT
Message-ID: <3E1D149E.1040803@adelphia.net>

oracle2000 wrote:
> Hi,
> I am new to backup and recovery concepts and I hope someone can help
> me answer this question. I came across the "alter database create
> datafile ..." command in 8i Backup and Recovery book(Rama Velpuri) and
> according to my understanding, the command can be used to create an
> empty datafile out of nothing, then use archived logs to recover it to
> its pink of health.
>
> But I got a problem. Lets say I issued a "create controlfile ...
> noresetlogs.." command, then I tried to invoke "alter database create
> datafile .." command at a later stage and *BANG*, I received the error
> message saying
>
> ORA-01178: file 4 created before last CREATE CONTROLFILE, cannot
> recreate
> ORA-01110: data file 4: 'E:\ORACLE\ORADATA\TRIAL\TBSP2.DBF'
>
> I experimented oracle9i with the following info. from v$version:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production
>
> Did I miss out something? Omitted some crucial step or did not apply
> some oracle patch? Please help. Thanks.

Did you check the details of the error message? The explanation is pretty straightforward:

$ oerr ora 1178
01178, 00000, "file %s created before last CREATE CONTROLFILE, cannot recreate"
  *Cause: Attempted to use ALTER DATABASE CREATE DATAFILE to recreate a

           datafile that existed at the last CREATE CONTROL file command.
           The information needed to recreate the file was lost with the
       controlfile that existed when the file was added to the database.

*Action: Find a backup of the file, and recover it. Do incomplete recovery to time before file was originally created.

Sounds like you did the following: 1) added a datafile, 2) recreated the controlfile with that datafile present, 3) deleted that datafile, 4) tried to recreate that datafile (as opposed to restoring it from a legitimate hot or cold backup). That won't work. If the datafile was created prior to the recreation of the controlfile, you can't use the 'alter database create datafile' command. If the datafile was added to the database *after* the recreate controlfile, everything should be fine with a 'create datafile' command. That's why the docco for the create controlfile command states that all files must be present at the time of the recreation.

Regards.
Sean Received on Thu Jan 09 2003 - 00:19:06 CST