on 04-27-2007 8:56 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
73 | |
26 | |
10 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.