| Actualizar vista v$database_block_corruption; (merged) [message #679147] |
Tue, 11 February 2020 07:44  |
 |
fmonjes
Messages: 10
Registered: February 2020
|
Junior Member |
|
|
Estimados.,
Acabo de eliminar un datafile , producto de un bloques corruptos, bueno finalmente elimine el datafile pero en la vista select * from v$database_block_corruption; sigue apareciendo el el FILE# del datafile , al igual que rman cuando ejecuto
RMAN> validate check logical database;
Hay alguna forma de actualizar la vista o el catalogo
COmo antecedente cuando listo los datafiles : no aparecen los que elimine
select file_id,file_name,tablespace_name from dba_data_files order by tablespace_name;
la forma de eliminar el datafile fue:
1.- alter database datafile '/DATA/oracle/oradata/data04.dbf' OFFLINE for drop;
2.- SQL> ! rm -rf /DATA/oracle/oradata/data04.dbf
3.- delete file$ where FILE#=12;
y claro cuando intento crear un con el mismo nombre me dice que ya existe...
ERROR at line 1:
ORA-01537: cannot add file '/DATA/oracle/oradata/data04.dbf' - file already
part of database
GRacias
|
|
|
|
|
|
| Re: Actualizar vista v$database_block_corruption; (merged) [message #679149 is a reply to message #679147] |
Tue, 11 February 2020 08:19  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum, please note this is an English.
You can post in our Spanish specific forum.
Here's a Google translation of your posts.
I just deleted a datafile, product of a corrupt blocks, well finally delete the datafile but in the view select * from v$database_block_corruption; the FILE# of the datafile still appears, just like rman when I run
RMAN> validate check logical database;
Is there any way to update the view or catalog
As previously when listed the datafiles: those that you delete do not appear
select file_id, file_name, tablespace_name from dba_data_files order by tablespace_name;
The way to remove the datafile was:
1.- alter database datafile '/DATA/oracle/oradata/data04.dbf' OFFLINE for drop;
2.- SQL>! rm -rf /DATA/oracle/oradata/data04.dbf
3.- delete file$ where FILE# = 12;
and of course when I try to create one with the same name it tells me that it already exists ...
ERROR at line 1:
ORA-01537: cannot add file '/DATA/oracle/oradata/data04.dbf' - file already
part of database
I just deleted a datafile, product of a corrupt blocks, well finally delete the datafile but in the view select * from v$database_block_corruption; the FILE# of the datafile still appears, just like rman when I run
RMAN> validate check logical database;
Is there any way to update the view or catalog
As previously when listed the datafiles: those that you delete do not appear
select file_id, file_name, tablespace_name from dba_data_files order by tablespace_name;
Quote:delete file$ where FILE# = 12;
This is your big mistake.
NEVER ever directly update SYS table.
Quote:Is there any way to update the view or catalog
The way to update the catalog is to use SQL DDL statement.
Directly updating it results to corrupting the dictionary with inconsistencies;
A data file cannot not be removed from the database, you have to drop the tablespace.
The statement "alter database datafile '/DATA/oracle/oradata/data04.dbf' OFFLINE for drop" just instructs Oracle that you put the datafile offline because you will delete the file. It does *not* mean that the file is removed from the database dictionary just Oracle can no more use it.
The way to workaround a datafile corruption is to restore it from a backup and to perform a recovery.
|
|
|
|
| Re: Actualizar vista v$database_block_corruption; [message #679150 is a reply to message #679148] |
Tue, 11 February 2020 09:07 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Deleting a row from sys.file$ will remove the reference to the file from the data dictionary, but it will not remove the reference from the controlfile. So now you are going to see all sorts of inconsistencies. For example, the file will be listed in v$datafile but not in dba_data_files.
How important is this database? Do you really need to have the problem fixed?
[Updated on: Tue, 11 February 2020 09:11] Report message to a moderator |
|
|
|
|
|
| Re: Actualizar vista v$database_block_corruption; [message #679154 is a reply to message #679153] |
Tue, 11 February 2020 11:32 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You remove a datafile with the DROP TABLESPACE command. Given the damage you have done to the data dictionary, it will be interesting to see if it succeeds. If it does not, you could insert a row back into file$ (you will be able to make sensible guesses at what the column values should be) and try again.
|
|
|
|
| Re: Actualizar vista v$database_block_corruption; [message #679155 is a reply to message #679153] |
Tue, 11 February 2020 12:39 |
|
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Michel Cadot: "The way to update the catalog is to use SQL DDL statement." How is it possible? I don't have archivelog activated
in the same way what is the correct way to delete a datafile
As I said, and John repeated, you cannot remove a datafile, the only way is to drop the tablespace "containing" it.
If you have no archive log (this should be turned on as soon as possible) you cannot recover a datafile. (You can recover some corruptions but it is not an easy way.)
You have now to answer several questions:
1/ Is the datafile the only one of the tablespace?
2/ What does this tablespace contain?
3/ How these objects are important for you?
4/ Have you some ways to rebuild and refill them?
If the answers are "nothing important" or "I can rebuild" then try John's suggestion.
[Updated on: Tue, 11 February 2020 12:39] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
