cancel
Showing results for 
Search instead for 
Did you mean: 

No Online backups due to datafiles offline than I canu0092t recover. How...?

Former Member
0 Kudos

Hi gurus!!!

I got into a problem with some datafiles.

We have MySAP in a BD oracle 9.2.0.6

I used Oracle Entreprise Manager Console to generate two datafiles in the tablespace PSAPPRDUSR,

but I generated them in another tablesapce (PSAPPRD) with a name of file,

size and location very different from those that there uses the tablespace PSAPPRD.

<b>So... I put them offline later to erase them later!!!</b>

<b>The datafiles contains no data.</b>

<b>Later, in the night backup:</b>

BR0334W Status of database file /oracle/PRD/sapdata4/prdusr_2/prdusr.data2 is RECOVER

BR0334W Status of database file /oracle/PRD/sapdata4/prdusr_2/prdusr.data2a is RECOVER

...

BR0301E SQL error -1128 at location BrTspAlter-3

ORA-01128: cannot start online backup - file 31 is offline

ORA-01110: data file 31: '/oracle/PRD/sapdata4/prdusr_2/prdusr.data2'

BR0280I BRBACKUP time stamp: 2007-04-20 03.01.21

BR0316E 'Alter tablespace PSAPPRD begin backup' failed

BR0056I End of database backup: bdvbtndh.ant 2007-04-20 03.01.21

<b>

And now... I neither can erase them nor can put online again.</b>

<b>To erase them I used:</b>

SQL> ALTER DATABASE DATAFILE '/oracle/PRD/sapdata4/prdusr_2/prdusr.data2' OFFLINE DROP;

Database altered.

SQL> commit;

But they continue there, do not resign.

<b>To put them online:</b>

SQL> alter database recover datafile '/oracle/PRD/sapdata4/prdusr_2/prdusr.data2';

alter database recover datafile '/oracle/PRD/sapdata4/prdusr_2/prdusr.data2'

*

ERROR at line 1:

ORA-00279: change 31203204 generated at 04/19/2007 19:30:11 needed for thread 1

ORA-00289: suggestion : /oracle/PRD/oraarch/PRDarch1_2574.dbf

ORA-00280: change 31203204 for thread 1 is in sequence #2574

SQL>

<b>But we have continued working and I don't want to lose any data.</b>

HEEEELP!!!!

What I can do to recover the datafiles and to continue with my daily Online backups?

The BD is in production and I do not have long time to fix it, only a few hours on Saturdays night.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member618758
Active Participant
0 Kudos

Hi,

alter database datafile offline drop;

This command is not meant to drop a datafile. It is meant to offline the datafile with the intention of dropping the tablespace.

For a tablespace comprising multiple datafiles you can only drop a datafile by dropping the entire tablespace, this will work even you have one datafile.

If you have 10gr2, it's finally possible to drop a datafile as long as

it's not the only datafile within the tablespace, and

the tablespace is only and in read-write status.

So since you are on 9i your situation in summary:

If you have more than one datafile in the tablespace and you wish to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the tablespace.

1. Gather information on the current datafiles within the tablespace by running some query to remember the t/s, segment, and datafile info.

2. Export all the objects that you wish to keep.

3. Once the export is done, issue the DROP TABLESPACE <tablespace name> INCLUDING CONTENTS.

4. Delete the datafiles belonging to this tablespace using the operating system.

5. Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace.

Hope this helps and good luck,

George

Former Member
0 Kudos

Hello,

a little addition to George explanation.

You can also reorganize ONLINE to a new tablespace all tables that do not contain a field type LONG RAW.

This can also save you some time.