This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Feb 5, 2013 8:22 PM by Arun Go to original post RSS
  • 15. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    am i querying the right table?

    CISADM@MX210POC>select * from test where owner_flg='XX';
    select * from test where owner_flg='XX'
    *
    ERROR at line 1:
    ORA-01578: ORACLE data block corrupted (file # 8, block # 869)
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'


    CISADM@MX210POC>select * from V$DATABASE_BLOCK_CORRUPTION;

    no rows selected
  • 16. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    I took the datafile offline.. and tried to recover it.. now i am getting the below error.


    CISADM@MX210POC>alter database datafile '/mwm_oradata_01/MX210POC/cists03.dbf' offline;

    Database altered.


    CISADM@MX210POC>recover datafile '/mwm_oradata_01/MX210POC/cists03.dbf';
    ORA-00283: recovery session canceled due to errors
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
  • 17. Re: how to find out a specific record in which datafile?
    yoonas Expert
    Currently Being Moderated
    >
    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    >

    Whats there in trace file?
  • 18. Re: how to find out a specific record in which datafile?
    JohnWatson Guru
    Currently Being Moderated
    Arun wrote:
    I took the datafile offline.. and tried to recover it.. now i am getting the below error.


    CISADM@MX210POC>alter database datafile '/mwm_oradata_01/MX210POC/cists03.dbf' offline;

    Database altered.


    CISADM@MX210POC>recover datafile '/mwm_oradata_01/MX210POC/cists03.dbf';
    ORA-00283: recovery session canceled due to errors
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    Of course recovery failed. You need to re-create the datafile, then recover it. This is two commands. The method is described in the paragraph which I suggested you read.

    Trust me: I'm a DBA.
  • 19. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    #> cat /orasw/app/oracle/admin/MX210POC/bdump/diag/rdbms/mx210poc/MX210POC/trace/MX210POC_dbw0_3206.trc
    Trace file /orasw/app/oracle/admin/MX210POC/bdump/diag/rdbms/mx210poc/MX210POC/trace/MX210POC_dbw0_3206.trc
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORACLE_HOME = /orasw/app/oracle/product/11.2.0/dbhome_1
    System name: Linux
    Node name: tudevlv0338.us.oracle.com
    Release: 2.6.32-200.19.1.el5uek
    Version: #1 SMP Thu Aug 25 00:56:49 EDT 2011
    Machine: x86_64
    Instance name: MX210POC
    Redo thread mounted by this instance: 1
    Oracle process number: 10
    Unix process pid: 3206, image: oracle@tudevlv0338.us.oracle.com (DBW0)


    *** 2013-02-05 03:54:14.060
    *** SESSION ID:(318.1) 2013-02-05 03:54:14.060
    *** CLIENT ID:() 2013-02-05 03:54:14.060
    *** SERVICE NAME:(SYS$BACKGROUND) 2013-02-05 03:54:14.060
    *** MODULE NAME:() 2013-02-05 03:54:14.060
    *** ACTION NAME:() 2013-02-05 03:54:14.060

    DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-27046: file size is not a multiple of logical block size
    Additional information: 1
    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-27046: file size is not a multiple of logical block size
    Additional information: 1

    *** 2013-02-05 03:54:58.991
    DDE rules only execution for: ORA 1110
    ----- START Event Driven Actions Dump ----
    ---- END Event Driven Actions Dump ----
    ----- START DDE Actions Dump -----
    Executing SYNC actions
    ----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
    Successfully dispatched
    ----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
    Executing ASYNC actions
    ----- END DDE Actions Dump (total 0 csec) -----
    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-27046: file size is not a multiple of logical block size
    Additional information: 1

    *** 2013-02-05 03:55:11.413
    DDE: Problem Key 'ORA 1110' was flood controlled (0x5) (no incident)
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-27046: file size is not a multiple of logical block size
    Additional information: 1
    ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    ORA-01110: data file 8: '/mwm_oradata_01/MX210POC/cists03.dbf'
    ORA-27046: file size is not a multiple of logical block size
    Additional information: 1
  • 20. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    Yes.. i read that.. But when i said i have archivelogs, i have only from the time i inserted the records into table. But i dont have the archives from the time the datafile is created.

    i am exploring this idea now. My DR is still running fine. I read in the document given by someone that if there is datagaurd setup it should recover automatically by reading from standby? is it correct?

    Thanks,
    Arun
  • 21. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    I have one more question. Can i copy datafile of my standby database to primary and recover it?

    Thanks,
    Arun
  • 22. Re: how to find out a specific record in which datafile?
    yoonas Expert
    Currently Being Moderated
    See, what you heard from someone is something like this (http://docs.oracle.com/cd/E14072_01/backup.112/e10642/rcmblock.htm)
    "If the database on which the corruption occurs is associated with a real-time query physical standby database, then the database automatically attempts to perform block media recovery."
  • 23. Re: how to find out a specific record in which datafile?
    JohnWatson Guru
    Currently Being Moderated
    Arun wrote:
    I have one more question. Can i copy datafile of my standby database to primary and recover it?

    Thanks,
    Arun
    This is all in the docs, you know. Chapter 11 of your Data Guard Concepts and Admin Guide. You do need to learn how to use the docs of you want to become a competent database administrator.
  • 24. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    Thanks Yoonus and John,

    Agree with you on reading docs. So here is what i done

    1. took rman abckup at standby database
    2. transferred to primary site and registered using catalog command.
    3. Restore datafile;
    4. Recover datafile.

    Thank you
    Arun
  • 25. Re: how to find out a specific record in which datafile?
    EdStevens Guru
    Currently Being Moderated
    Andrejs Karpovs wrote:
    Hi LaserSoft,

    Can you please share your email ID at noflow@inbox.lv
    Now that you have placed your email on a public forum, be prepared for a flood of spam.



    I would like to contact you with regards to 11g Grid Control installation media. I saw you had it in some other thread.
    I would appreciate if we could get in touch. Thanks.

    Regards,
    Andrejs.
  • 26. Re: how to find out a specific record in which datafile?
    jgarry Guru
    Currently Being Moderated
    Arun wrote:
    Thanks a lot for all the correct answers... It all solved my question.. Before marking it answered i have few more quiestions.. Appreciate if you could answer..

    I am experimenting few things. I have found the datafile and opened the file with vi editor. I found the record 'xx' and updated with yy on vi editor and saved it.

    As expected it shows the block corruption while i query for that data. But now i am trying to recover using rman.
    Not really sure what corruption you expected, but doing this will likely truncate your data file, as well as set off the crc checking for corruption for that and probably other physical blocks. Didn't you get a "line too long" error? Was the db shutdown when you did this? You probably don't want to do this, regardless of what you are testing.
  • 27. Re: how to find out a specific record in which datafile?
    Arun Newbie
    Currently Being Moderated
    Not really sure what corruption you expected, but doing this will likely truncate your data file, as well as set off the crc checking for corruption for that and probably other physical blocks. Didn't you get a "line too long" error? Was the db shutdown when you did this? You probably don't want to do this, regardless of what you are testing.
    I think same happened. After i saved the file by editing it manually, i got the block corruption error only when i am querying the data. But i could not get the corrupt block details when i used 'dbv' utility. dbv stopped with some fatal error which i do not recall now. I was trying for block corruption and later i came to know it was not the correct way to simulate it. However i end up doing restore datafile and recover than my intended block recovery :)

    Thanks,
    Arun
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points