| DataFile Issues [message #211511] |
Fri, 29 December 2006 01:20  |
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  |
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 |
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 |
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
