Home » RDBMS Server » Server Administration » Actualizar vista v$database_block_corruption; (merged) (Oracle 11g)
Actualizar vista v$database_block_corruption; (merged) [message #679147] Tue, 11 February 2020 07:44 Go to next message
fmonjes
Messages: 8
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
Actualizar vista v$database_block_corruption; [message #679148 is a reply to message #679147] Tue, 11 February 2020 07:57 Go to previous messageGo to next message
fmonjes
Messages: 8
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;
Re: Actualizar vista v$database_block_corruption; (merged) [message #679149 is a reply to message #679147] Tue, 11 February 2020 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 8344
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 #679153 is a reply to message #679150] Tue, 11 February 2020 11:11 Go to previous messageGo to next message
fmonjes
Messages: 8
Registered: February 2020
Junior Member
John Watson: It's not important yet, I'm interested to know if there is any solution;

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

Thank you
Re: Actualizar vista v$database_block_corruption; [message #679154 is a reply to message #679153] Tue, 11 February 2020 11:32 Go to previous messageGo to next message
John Watson
Messages: 8344
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Actualizar vista v$database_block_corruption; [message #679712 is a reply to message #679155] Wed, 18 March 2020 09:11 Go to previous message
fmonjes
Messages: 8
Registered: February 2020
Junior Member
Thanks for the answers
Finally, as it is a test environment, I rebuilt. We also realized that the disk had serious problems, which was replaced. and we were able to solve the problem
Previous Topic: how to use catcon with username parameters
Next Topic: Cannot see DBA_USERS with SYS in a new database
Goto Forum:
  


Current Time: Sat Sep 19 14:41:16 CDT 2020