No backups
Miguel Garcia, June 07, 2002 - 9:46 am UTC
I'm afraid I no have a backup. I understand that if I don`t have it y can restore the system.
I have a backup of the filesystem a backup of the file system01.dbf can this be usefull?
Thank you.
June 07, 2002 - 11:34 am UTC
"I understand that if I don`t have it y can
restore the system."
(besides your keyboard being on the verge of failure -- it drops letters frequently -- very important ones like vowels and such!...) that statement is inaccurate.
You should understand that if you don't have it (a backup), you CANNOT restore the system.
If you have a filesystem backup AND
the filesystem backup was done whilst
a) the database was down OR
b) the tablespaces were in backup mode
then yes, of course you can use it -- that would be a backup!
If the filesystem backup was done whilst the database was up and you did not put tablespaces into backup mode -- doubtful that you can use it.
If you are in noarchivelog mode (which I'm guessing you are), then the database MUST have been shutdown and you can restore as of the time of the last backup.
The index that has the bad block is a "much needed one"
sys@ORA817DEV.US.ORACLE.COM> alter index i_col3 rebuild;
alter index i_col3 rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered
You can try exporting whatever you can and creating a new one.
only one of several datafiles in a tablespace is corrupt
steve, July 02, 2002 - 10:01 am UTC
Hi Tom,
We are getting ORA-01578 on a couple of datafiles (not index).
Suppose we have tablespace TS1 that is comprised of multiple
datafiles:
/db100/abc/ts1_data01.dbf
/db100/abc/ts1_data02.dbf
/db100/abc/ts1_data03.dbf
Suppose that only /db100/abc/ts1_data02.dbf is experiencing
the ORA-01578. Note that the database is up and running.
Would the following be the proper procedure to recover:
1. From SQL*Plus, enter:
ALTER TABLESPACE TS1 OFFLINE TEMPORARY;
(I'm assuming this "flushes" all pending writes to
the datafiles of this tablespace).
2. From OS level, copy /db100/abc/ts1_data02.dbf
to a temporary location (in case of problems)
3. restore /db100/abc/ts1_data02.dbf from the last full backup.
4. Run dbv on the restored file to make sure it is valid.
5. If necessary, restore archived redo log files to the
archive destination.
6. *** This is where I'm not 100% sure. ***
Do we recover the single datafile or the whole tablespace?
I'm assuming we only need to recover the datafile. But I
also think recovering the tablespace will accomplish the same.
RECOVER TABLESPACE TS1
or
RECOVER DATAFILE /db100/abc/ts1_data02.dbf
(will likely be prompted for applying necessary redo log files)
7. Put the tablespace back online, enter:
ALTER TALBESPACE TS1 ONLINE;
~
July 02, 2002 - 12:44 pm UTC
6) you can do either or -- the other files are "recovered" and it'll just do the datafile.
Block corruption
vinodhps, June 14, 2003 - 10:42 am UTC
Hi tom ,
In this same senario..if iam running my database on noarchive mode.When ever i try to take Export it does not exporting that particular tablespace.
we are using
OS-UNIX HP
Oracle 8.0.4.
24x7
Can you please suggest me what to do.. now tom
thanks in advance tom
ora-00604,ora-01578,ora-01110
A reader, August 11, 2003 - 9:27 am UTC
Hi Tom,
If for instance, corruption occurred in unique/primary key index and only tiny (say 1 block) part of it is affected, would i be able to insert records into that table? if yes , would i be able to delete records from child tables?
Thanks
RameshG
August 11, 2003 - 10:01 am UTC
maybe, maybe not. depends.
you need to recover or rebuild this index asap anyway, so the point is sort of "moot"
undo tablespace's corruption
Pravesh Karthik from chennai, December 25, 2004 - 7:25 am UTC
Tom,
Can you please help me out by providing a link - how to recover if undo tablespace's datafile gets corrupted.
I searched, googled -- could not come across any good link
Thanks,
Pravesh Karthik
December 25, 2004 - 9:13 am UTC
metalink.oracle.com
actually it is quite easy:
</code>
https://docs.oracle.com#index-BAC <code>
backup and recovery concepts -- the one thing a dba is not allowed to get wrong is recovery, we can fix any other mistake but mess up recovery and we cannot help you at all.
undo is just a tablespace, you recovery it like you would system, tools, etc...
Identifying the content of the corrupted block
A reader, January 05, 2005 - 9:22 pm UTC
I have a corrupted block in the system tablespace.
I used the standard query to identify the object that the block but it failed:
SQL>
1 SELECT segment_name
2 , segment_type
3 , owner
4 , tablespace_name
5 , block_id
6 , blocks
7 FROM sys.dba_extents
8 WHERE file_id = 1
9* AND 8002 BETWEEN block_id and block_id + blocks -1 FROM sys.dba_extents
*
ERROR at line 7:
ORA-01578: ORACLE data block corrupted (file # 1, block # 8002)
ORA-01110: data file 1: '/u100/oradata/fstst/system01.dbf'
Then I did this:
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER, LEVEL 10';
select owner
, segment_name
, segment_type
, tablespace_name
, block_id
, blocks
from dba_extents
where file_id=1
and block_id in (
select max(block_id)
from dba_extents
where file_id=1
and block_id <= 8002
);
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BLOCK_ID BLOCKS
----- ------------ ------------ ---------------- -------- -------- ------
SYS C_OBJ# CLUSTER SYSTEM 7978 25
So, the object that the corrupt block belongs to is c_obj#. Is that correct?
If so, and since c_obj# stores data about objects (metadata), it basically means that I can not access the object whose metadata is in that block. So, I'm trying to identify which object is lost. For example, if it's just an index, I should be able to export all the objects from database into a new database and rebuild the index, and reslove the corruption in that way. Any idea how I can identify the object whose metadata is lost?
January 06, 2005 - 10:02 am UTC
please work this issue with support.
you don't have any backups? that would be my approach.
Identifying the content of the corrupted block II
Vlado, January 05, 2005 - 9:26 pm UTC
Sorry, hit the submit button instead of the review button. I wanted to add that I'm aware that Oracle support should be contacted, however a friend of mine asked me to look into this before they contact Oracle Support since they don't have a support contract anymore...
BTW, this is 8.0.6
January 06, 2005 - 10:06 am UTC
do you have BACKUPS
Identifying the content of the corrupted block III
Vlado, January 05, 2005 - 9:33 pm UTC
Also, forgot to mention that they identified this problem 3 months, and of course they don't have a good backup from which to recover...
January 06, 2005 - 10:08 am UTC
oh goody -- I'd start by
a) kick every off the system
b) table level exports of each and every application table, noting which ones "don't work"
c) concentrating on getting as much data as I could out of the ones that don't work
d) rebuilding the database.
then.
table level exports with corrupted block
Vlado, January 06, 2005 - 12:08 pm UTC
You mention 'table level exports of each and every application table, noting which ones "don't work"', but how do I identify all the application tables (Pepoplesoft, 1000s of tables)?
Doing a select against dba_tables returns an ORA-01578...
Is there any other view that lists all the tables in the database without using c_obj#? Maybe obj$?
January 06, 2005 - 12:43 pm UTC
peoplesoft, no support, really old release, no backups -- geez.
you can query obj? but you know what -- c_obj# is a CLUSTER, it holds many tables!
sys@ORA806> select table_name from user_tables where cluster_name = 'C_OBJ#';
TABLE_NAME
------------------------------
ATTRCOL$
CLU$
COL$
COLTYPE$
ICOL$
IND$
LIBRARY$
LOB$
NTAB$
REFCON$
TAB$
TYPE_MISC$
12 rows selected.
all of those tables are affected by this
Listing corrupt blocks
csakanytibor@hotmail.com, February 26, 2005 - 6:04 am UTC
Dear Tom,
We have an incomplete recovery recently due to loss of online redo logs. Also some object was in NOLOGGING mode. Now, we sometimes get:
ORA-01578: ORACLE data block corrupted (# 5 file, # 7748 block)
ORA-01110: datafile 5 : 'E:\SINESCO\TBS_USR.DAT'
ORA-26040: Az adatblokk a NOLOGGING opcióval lett betöltve
would you show me how can I list all the corrupted block in all the datafiles? Also what can I do with them?
Thank you very much.
Oleg
February 26, 2005 - 8:02 am UTC
[tkyte@localhost tkyte]$ oerr ora 26040
26040, 00000, "Data block was loaded using the NOLOGGING option\n"
//* Cause: Trying to access data in block that was loaded without
//* redo generation using the NOLOGGING/UNRECOVERABLE option
//* Action: Drop the object containing the block.
basically, by doing a nonlogging operation in an archivelog mode database without coordinating a backup of the affected datafiles with the DBA right after is another way of saying "I don't care about this data, we can lose it"
and you lost it.
If you have an index on this table (the index would always be logged) you might be able to save the data row by row by:
(assumption, T has a column indexed_column that is a positive NON-NULL number)
begin
for x in
( select /*+ index( t t_idx ) */ indexed_column, rowid rid
from t where indexed_column > 0 )
loop
begin
insert into another_table select * from t where rowid = x.rid;
exception
when others
then
l_err_msg := sqlerrm;
insert into log_table values ( x.indexed_column, l_err_msg );
end;
end loop;
end;
/
You should be able to save all data that was indexed by reading each index in turn, saving the indexed data + the rowid and then joining the N saved pieces of index data and joining them together -- and then picking up the remaining non-indexed columns for the rows you can.
ORA-01578
Arun Mozhi Varman, September 02, 2010 - 4:33 am UTC
Hi Tom,
I do get the ora-01578 error while querying a table on test instance.
We scrapped the database and refreshed from cold backup.
Now we get the error on some other object.
I would like to know if we can pin point the OS blocks corresponding to the corrupt oracle block.
September 09, 2010 - 6:51 pm UTC
$ oerr ora 1578
01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"
// *Cause: The data block indicated was corrupted, mostly due to software
// errors.
// *Action: Try to restore the segment containing the block indicated. This
// may involve dropping the segment and recreating it. If there
// is a trace file, report the errors in it to your ORACLE
// representative.
you get the block information right there in the message??
If you mean some sort of physical "cylinder/sector" from the disk itself, check your OS error logs - they would contain that.
ORA-01578
Arun Mozhi Varman, September 02, 2010 - 10:28 am UTC
update of the above question..
OS guys say that there is no issue with the disk after running the fsck utility.
September 09, 2010 - 6:56 pm UTC
but fsck checks the health of a filesystem, not all of the blocks on a disk???
that proves nothing.
01110
Prashanth Kumar, October 31, 2011 - 3:16 am UTC
Thanks...
ORA-01578 + ORA-01110 + ORA-01113
Andre, April 04, 2013 - 11:01 am UTC
Hi Tom,
Our site (still on 9iR2 - hence limited support) was showing I/O errors on Win-2003 as well as Oracle.
Many times ORA-00201 with CKPT terminating instance due to this error = failure to write into control file (1 out of 3) then one day DB support tried to restart DB = 20 consecutive time getting ORA-01113 - finally Oracle managed to startup.
ORA-01113 is classified as MEDIA-related error.
When we hit ORA-01578 with ORA-01110 + crash, then decision was made to restore the database.
Although we had an on-line backup - we had opted for an Apps schema export taken one day before the crash. Business users wanted to have access to the restored data as well as the most recent data prior to crash = this is why import had been chosen.
H/w support + Win team say that they could not find I/O issues on their event logs.
Questions:
(1) If the ORA-01578 occurs on a server with local disks deployed (no SAN) and these disks are mirrored - should we assume that whatever corruption may occur would be on both disks..?
(2) Is there a way to diagnose I/O from O/S angle or is it too subtle for Windows..?
Thanks
Andre