0

This question is an exact duplicate of:

First to mention that I came to the need of asking help after furious research I reattempts for about 3 weeks. I checked previous threads in Oracle community but nothing works for me. I am trying to restore Oracle 11.1.0.7. multi HDD cluster production on single hdd locally on amazon ec2.

Restore is done from full back-up copy I downloaded from ftp location (I have no access to production,export and etc.) just have access to the files, i.e. I am not able to change anyhow export procedure.

So here are my steps in brief (I guess that problem might be in some of them):

  1. rman target /
  2. set DBID ...
  3. startup nomount
  4. RESTORE CONTROLFILE FROM 'file destination'
  5. shutdow;startup mount;
  6. Catalog the backup files to rman: catalog backuppiece 'C:/BKUP/AL_2851492942_20151016_893271615_PGQJSFHV_1_1';for all AL and BK files
  7. sqlplus / as sysdba
  8. Rename all redo files to match my local directory structure
  9. rman:crosscheck backup; Run{
  10. Rename all data files to match my local directory structure

    SET UNTIL SEQUENCE 50511;      
    RESTORE DATABASE;
    SWITCH DATAFILE ALL;
    RECOVER DATABASE; 
    }
    
  11. sqlplus sys/sys as sysdba

  12. shutdown immediate; startup mount;
  13. alter database noarchivelog;
  14. Drop the no longer used log files:-drop these in state UNUSED left Active and Inactive
  15. Drop all temp files(they point to other drive letter;
  16. ALTER DATABASE OPEN;
  17. ALTER TABLESPACE mytempspace ADD TEMPFILE 'c:\oracle............dbf' SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

My problem is that on my second query (get) to db through my app I receive:

ORA-01578: ORACLE data block corrupted (file # 64, block # 2559928)
ORA-01110: data file 64: 'C:\ORACLE\ORADATA\mydb\mydbLOBDATA2_1.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "mydbUSER.POBJECTSTATE", line 78
ORA-06512: at line 1

What I was able to find with validation and it is LOBSEGMENT - Lob_ObjectState and it logically corrupted.

I try DBMS_REPAIR and with rman advise failure (which suggest to restore my copy from back-up- nothing useful).

Since back-up are refreshed every week I try with new ones from the next week, again the same problem.

Is there a possibility that the problem starts from export (how can I check this-I do not have access but i may ask db admin to check anything)?

Any other ideas?

marked as duplicate by Philᵀᴹ, RLF, Max Vernon, mustaccio, RolandoMySQLDBA Nov 10 '15 at 18:47

This question was marked as an exact duplicate of an existing question.

migrated from stackoverflow.com Nov 9 '15 at 14:56

This question came from our site for professional and enthusiast programmers.

  • Interesting, a man make non-purpose formatting mistake and get -4. At least may be some of these "gurus" who minus me will try at least to answer. Giving '-' is easy and so is hiding ;) – Rumen Tonev Nov 9 '15 at 9:21
  • Have you tried opening the database with the RESETLOGS option? – Yaron Idan Nov 9 '15 at 10:35
  • Hi,Yaron, Yes I do but it gives me "RESETLOGS option valid only after incomplete recovery", actually my recovery is complete(no errors). – Rumen Tonev Nov 9 '15 at 12:35
  • Could you attach the query that produces the error and the ddl of the objects you query from? – Yaron Idan Nov 9 '15 at 13:16
1

This line clearly states the reason for the block corruption:

ORA-26040: Data block was loaded using the NOLOGGING option

NOLOGGING operations are not logged in the redo as traditional operations, the amount of information logged is greatly reduced to an extent, where data recovery is not possible. Because of this, if you did not make a backup of the affected datafiles after these operations had finished, those modifications can not be recovered with archivelogs, and you will receive the above error.

NOLOGGING should be handled with care, and because of the above, you should perform a backup after performing a NOLOGGING operation. If your backup was created earlier, you will not be able to fully recover data using this backup, even if you have the archivelogs.

So, perform the backup after the NOLOGGING operations was finished.

Another way is to control the NOLOGGING attribute of your objects. You can set this at table, index, LOB segment, tablespace level. You can set it to LOGGING, so operations on the object will be logged (or this will be the default inherited value in case of tablespace).

Finally, you can enable FORCE LOGGING at the tablespace or database level, so operations will be logged regardless of the object level setting.

Not the answer you're looking for? Browse other questions tagged or ask your own question.