Re: Datafile corruption

DDL transactions are logged. They autocommit, which is why we cannot roll them back. It is standard procedure to create an empty datafile, which is why we have alter database create datafile. Indeed if you read your sql reference, it says, under alter database, "create a new datafile in place of an old one for recovery purposes".

Darrell L Carden (dlcarden_at_buckman.com) wrote:
: I don't see how you can perform recovery on an empty datafile. DDL
: statements are not logged, so when transactions are applied there aren't any
: tables to apply them to. Even if you restored the backup that had all the
: table definitions, recovery will fail if there has been any DDL statements
: (like ALTER TABLE to add a column).

: What am I missing here?

: Oracle wrote:
: >If you are running in archiving mode, just using alter database to take the
: >corrupt datafile offline and then physical delete it . . .
: >Then if all the log files since the datafile was created to the present are
: >available, and if your controlfile is current, then you can recover the
: datafile
: >without a backup. In order to do this, you will have to use the CREATE
: >DATAFILE clause of the ALTER DATABASE command: e.g. ALTER DATABASE CREATE
: >DATAFILE 'oldfilename' AS 'newfilename'. This will create an empty file
: the
: >same size as the missing file.
: > Following this, you must perform media
: >recovery on the empty file. During media recovery of the empty file, all
: >archived and online redo logs must be available to be applied to the file
: so
: >that it can be brought back to a transaction consistent state

--

   Tom Pall, contract Oracle DBA Received on Mon Nov 30 1998 - 17:53:25 CST