Home » RDBMS Server » Backup & Recovery » DataFile Issues
DataFile Issues [message #211511] Fri, 29 December 2006 01:20 Go to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Hi

Database: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning and OLAP options
JServer Release 9.2.0.8.0 - Production.
OS: Sunsolaris 10 T-2000


I have decided to offline two datafiles of two different tablespaces that we dont want to keep it in our DB.
I issued this query:
SQL>alter database datafile 'u02/oradata/XX/a.dbf' offline;
SQL>alter database datafile 'u02/oradata/YY/b.dbf' offline;
The output of these quries are "Database altered".


After this i queried this:
select name,status from v$datafile;

In the status it should show "OFFLINE".But instead of "OFFLINE" it shows "RECOVER". I dont know how and why?

After this,i tried to online those datafiles.But it is asking for media recovery.I dont know what to do?
My questions are:
1.Can i drop these tablespaces?
2.Will it allow me to drop the tablespaces and datafiles?
3.Can i maintain those datafiles in "RECOVER" status, without doing anything towards that?If i do like that,will it affect my database that is currenly running?I know that, while i restart the database, then it will ask for media recovery,Am i right?
4.Will it trouble the other tablespaces and datafiles?
5.Will i get any database performance issues?
6 If i want to perform the media recovery on these datafiles in database open state,how to do that?
7.If i drop the tablespaces and datafiles in "RECOVER" status, will it affect while i restart my database?Because, control file will have all datafile entries .


Sorry for the long and more questions.
please advice me with your valuable ideas ASAP,since this is our LIVE DB.

Thanks.




Re: DataFile Issues [message #211783 is a reply to message #211511] Tue, 02 January 2007 04:40 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,


>> I have decided to offline two datafiles of two different tablespaces that we dont want to keep it in our DB.
why??? I think they contain data not useful to you. right?

>>In the status it should show "OFFLINE".But instead of "OFFLINE" it shows "RECOVER". I dont know how and why?

You have tried to make offline file online,so it went in recover mode.Since some changes might have happened to that file.
Answers:
1>
You can drop tablespace if it contains only that offline files & data within it is not useful to you.
2> Same as above
3> If files contains not useful data, then it will not make any problem.
Yes, when you restart database it will ask for media recovery.

4>It will not trouble otther tablespace & datafiles.
5> No
6>
If your database is in archivelog mode,
recover datafile 'datafilename'
archivelogs will be applied.

7> No

If you have test database you can test all .


Re: DataFile Issues [message #211787 is a reply to message #211511] Tue, 02 January 2007 05:01 Go to previous messageGo to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member

Thanks Seema.

In the status it should show "OFFLINE".But instead of "OFFLINE" it shows "RECOVER". I dont know how and why?

>>You have tried to make offline file online,so it went in recover >>mode.Since some changes might have happened to that file.

Then how will you make a offlined data file to online?the tablespaces involved in this case is not an active member of the DB(No transactions are happening).If this is the case,then obviously these datafiles would have been onlined while i tried to do so.Am i right?

Re: DataFile Issues [message #211887 is a reply to message #211787] Tue, 02 January 2007 23:05 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
>>>Then how will you make a offlined data file to online?the tablespaces involved in this case is not an active member of the DB(No transactions are happening).If this is the case,then obviously these datafiles would have been onlined while i tried to do so.Am i right?

You are right.
The reason Oracle requires that you recover your data file is Oracle does not checkpoint a data file when we take it offline. For data files taken offline, Oracle only updates the STOP SCN for the control file entry related to that data file. The result is a control file that has a STOP SCN that is in the future of the Checkpoint SCN in the file’s header, and thusly recovery is required.

Check this link
http://esemrick.blogspot.com/2006/03/recovery-of-offline-data-files.html

[Updated on: Tue, 02 January 2007 23:55]

Report message to a moderator

Re: DataFile Issues [message #211920 is a reply to message #211511] Wed, 03 January 2007 02:02 Go to previous messageGo to next message
thiyagaraj
Messages: 41
Registered: August 2006
Member
Iam sorry , iam unable to open the link u have sent me.

http://esemrick.blogspot.com/2006/03/recovery-of-offline-data-files.html
Re: DataFile Issues [message #211923 is a reply to message #211920] Wed, 03 January 2007 02:09 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
above mentioned link is correct it is open for me..
http://esemrick.blogspot.com/2006/03/recovery-of-offline-data-files.html


try again

regards
Taj
Previous Topic: Is it possible to only exp/imp daily changes of the db?
Next Topic: How to apply archives to roll foward data changes in db?
Goto Forum:
  


Current Time: Thu Dec 12 08:22:46 CST 2024