-
Alter Database Datafile Offline Drop
Hi,
One of my datafile got corrupted and one of the DATA tablespace is in danger. Unfortunately, I do not have any backup of the datafile.
I referred to metalink, and got some info. I am attaching the metalink document here.
METALINK DOCUMENT
-----------------
Displayed below are the messages of the selected thread.
Thread Status: Closed
From: Hans-Gerd Haase 03-Sep-02 10:46
Subject: Drop datafile before removing it from database
RDBMS Version: 7.3.4.0.0
Operating System and Version: HP-UX 10.20
Error Number (if applicable):
Product (i.e. SQL*Loader, Import, etc.):
Product Version: 7.3.4
Drop datafile before removing it from database
Hallo
We have migrated some databases to Oracle 8.x.x and I had to drop some old users/schematas from our oracle test server, which we do not need anymore. But I removed one datafile on unix, after 'drop user xyz cascade', but before setting the tablespace offline and before removing it from the database.
The database is in noarchive mode, there are no backups of the datafiles, there is only a full export.
What can I do to solve the problem with the next database start?
Thanks in advance
Hans-Gerd
--------------------------------------------------------------------------------
From: Oracle, Harm Joris ten Napel 03-Sep-02 10:55
Subject: Re : Drop datafile before removing it from database
Hi,
you need to drop all datafiles of the tablespace this one datafile
belonged to with the following command:
ALTER DATABASE DATAFILE '' OFFLINE DROP;
Then drop the tablespace:
DROP TABLESPACE INCLUDING CONTENTS;
The 'OFFLINE DROP' syntax was especially designed for disasters like
this or practices where for instance you don't backup temp or index
tablespaces and you need this to be able to recover the database with
only the really important datafiles,
greetings,
Harm
--------------------------------------------------------------------------------
From: Hans-Gerd Haase 03-Sep-02 11:10
Subject: Re : Re : Drop datafile before removing it from database
Hi
it works fine,
thanks for the quick answer.
Hans-Gerd
--------------------------------------------------------------------------------
From: Navtej Boparai 03-Sep-02 16:24
Subject: Re : Drop datafile before removing it from database
Do the follwing steps.
SQL > Shutdown Immediate;
SQL > Startup Mount;
SQL > Alter database datafile '' offline drop;
SQL > Alter database open;
SQL > drop tablespace ;
SQL > COMMIT:
thanx
Navtej Boparai
--------------------------------------------------------------------------------
From: Oracle, Debra Chase 03-Sep-02 21:10
Subject: Re : Drop datafile before removing it from database
Thanks for the good input.
Deb
Oracle Corporation
I was trying to simulate this in a test database before doing it on my devolopment DB.problem
1. Created a tablespace called “TEST” with two datafiles one with 100 MB size and othe one is 20 MB.
2. Created a user “testuser” with default tablespace “TEST”
3. Logged in with testuser and created two tables.
4. Deteled the second datafile of TEST tablespace (size 20 MB) from OS.
5. Tried shutting down the DB
6. It gave me the error and could not shutdown the database.
7. I did a shutdown abort and the DB is down now.
8. Tried starting the DB. I got me ORA-01110 and ORA-01116
9. Droped the 20 MB datafile by ALTER DATABASE DATAFILE OFFLINE DROP
10 Started the database.
11. Logged in with testuser
12 I could see all the tables which I created in “TEST tablespace!!
Now my doubt is,
Metalink document says after this exercise we need to give DROP TABLESPACE TABLESPACE_NAME
In my case I have not given this command and still able to access the tables from TEST tablespace.
Could you please tell me
1. Why am I able to access tables from TEST even after droping the datafile.
2. If people can access the data after droping the datafile (one or two , not all datafiles) why should we give DROP TABLESPACE.
Thanks in Advance.
Regards,
Ajith
METALINK DOCUMENT Now my doubt is,
-
you are able because it's reading data from existing datafile, you are just lucky for now, when it needs data from dropped datafile or it needs to allocate freespace you will start get errors
-
Thanks for the reply. I was thinking the same thing. Needed the confirmation from an experienced person. One more query...
Even though I have droped the data file, I am able to see the droped file in the dba_data_files. Why it is not getting removed from this view?
Tablespace had two datafiles and one is corrupted/unavailable now.
In which table/view this inconsistency is recorded ?
Regards,
Ajith
-
well offline drop does not remove file from dictionary that´s why you have to drop tablespace, what you see is expected behaviour
-
Thaks pando for making it clear.
Regards,
Ajith
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|