rbs tablespace missing datafile

SQL> select file_name, tablespace_name, bytes from dba_data_files where tablespace_name like '%RB%';

FILE_NAME                                          TABLESPACE_NAME                     BYTES
-------------------------------------------------- ------------------------------ ----------
/oradata8/dev2/rbs_02.dbf                          RBS                            2097152000
/oradata8/dev2/rbs_01.dbf                          RBS                            2097152000
/export/home/oracle/product/10_2/dbs/MISSING00052  RBS                            1048576000

Is there a way to drop this datafile?
LVL 1
bkreynolds48Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wietmanCommented:
You might be able to use the alter database drop datafile command in mount mode.
0
wietmanCommented:
If that does not work, then see if you can drop and recreate the RBS tablespace in mount mode.
0
bkreynolds48Author Commented:
wietman,
I tried both of those but neither worked - when dropping the datafile it says it does but really does not.  cannot drop the tablespace in mount mode because the database is not open
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Franck PachotOracle DBACommented:
Hi,

Why do you want to drop that datafile ? don't do that ! You need the undo tablespace in order to open the database !
Oracle has detected that there is a datafile that is needed, but that was missing in the controlfile (probably because you restored a controlfile that was backed-up before the createion fo the datafile)
If you have the datafile (oracle do not know the name, but you should - you can search in alert.log for the log of the add datafile statement) then just rename '/export/home/oracle/product/10_2/dbs/MISSING00052' to the real name.

But, I'm quite sure you have other datafiles missing... Don't do resky things if you do not understand your current situation. What has been done on that database ?

Regards,
Franck.
0
bkreynolds48Author Commented:
I copied this database from an existing one - I did not need all three rollback datafiles so only put two in the create control file script.  
I just tried........
 SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1744830464 bytes
Fixed Size                  2038640 bytes
Variable Size             397673616 bytes
Database Buffers         1330380800 bytes
Redo Buffers               14737408 bytes
Database mounted.
Database opened.

SQL> select file_name from dba_data_files where file_name like '%MIS%';
no rows selected
SQL> shutdown
ORA-00376: file 52 cannot be read at this time
ORA-01110: data file 52: '/oradata8/dev2/rbs_03.dbf'
 
0
wietmanCommented:
Sorry!
My assumption was that he knew that and could not recover that file.
The only option I know of at that point is to recreate rollback tablespace or undo.
0
bkreynolds48Author Commented:
I have tried dropping the rbs tablespace but can't do that in mount or open mode so how do I recreate it?
0
wietmanCommented:
I'll see what I can find on this.
0
wietmanCommented:
This is on Oracle's support sight:

Undo Tablespace
   -------------------
   While handling situation with lost datafile of an undo tablespace one has
   to be extra cautious so as not to loose active transactions in the undo
   segments. If need be please call Oracle Support for assistance.

   The prefer option in this case is to restore the datafile from backup and
   perform media recovery.


      i.  Database was cleanly shutdown.
          Ensure that database was cleanly shutdown in NORMAL or IMMEDIATE mode.
          Update your init file with "undo_management=manual"
          Restart the database
          Drop and recreate the undo tablespace
          Update your init file with "undo_management=auto"
          Restart the database

      ii. Database was NOT cleanly shutdown.
          If the database was shutdown abort or had crashed last time, in this
          case it is possible that you will not be able to offline and drop
          the datafile as the undo segments contained in it could contain
         active transactions. You will have to restore the file from a backup
          and do a media recovery. If this can not be achieved, please contact
          Oracle Support for further assistance.
0
wietmanCommented:
re:  I have tried dropping the rbs tablespace but can't do that in mount or open mode so how do I recreate it?

   

Did you have "undo_management=manual"?
0
wietmanCommented:
Make sure you use the create this as an undo tablespace, not a regular user one.
i.e.  Something of this form.

CREATE UNDO TABLESPACE undotbs_02
     DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;
0
bkreynolds48Author Commented:
I have undo_management set to manual.
 1* select file_name, status from dba_data_files where tablespace_name = 'RBS'
SQL> /
FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/oradata8/dev2/rbs_02.dbf                          AVAILABLE
/oradata8/dev2/rbs_01.dbf                          AVAILABLE
/oradata8/dev2/rbs_03.dbf                          AVAILABLE

SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
RECOVER
SYSTEM

SQL> alter tablespace rbs offline;
alter tablespace rbs offline
*
ERROR at line 1:
ORA-01546: tablespace contains active rollback segment 'RBS_01'

SQL> alter rollback segment rbs_01 offline;
alter rollback segment rbs_01 offline
*
ERROR at line 1:
ORA-00376: file 52 cannot be read at this time
ORA-01110: data file 52: '/oradata8/dev2/rbs_03.dbf'
 
0
wietmanCommented:
OK.  I have a better idea.
Create a new undo tablespace.
The switch to it, and

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

then restart the database and drop the old tablespace.
0
bkreynolds48Author Commented:
I was able to drop the rbs tablesape and recreate it but now I cannot open the database as it does not have any rollback segments - I guess all I can do is recopy all the datafiles and start over unless you can think of something else.

ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1744830464 bytes
Fixed Size                  2038640 bytes
Variable Size             397673616 bytes
Database Buffers         1330380800 bytes
Redo Buffers               14737408 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
 
0
wietmanCommented:
Did you Update your init file with "undo_management=auto"?
0
wietmanCommented:
Also, look at your alert log to see what it says about the ORA-1092.
Don't lose hope.  I think you are close and am confident you will get this.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bkreynolds48Author Commented:
thanks
0
wietmanCommented:
I hope this means you got it fixed?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.