11 Replies Latest reply on Jun 2, 2016 7:37 AM by Kj - Kiran Jadhav

    Moving EBS Datafile applsysd02.dbf

    984556

      Platform: AIX 5.3

      Oracle EBS 11.5.9

      Database 9i

      Two tier config

      Database on one server

      Apps on another server

       

       

       

      While making a backup using tar, i got an error because Datafile is on bad sector.

      I have tried to restore it and check, indeed the EBS doesn't work because some of the tables are missing : see this post

       

      The Error while making backup (offline)

      /opt/freeware/bin/tar: /oradata/proddata/applsysd02.dbf: Read error at byte 4767080960, reading 10240 bytes: There is an input or output error

       

      My question:

       

      What is the possible solution?

       

      1- Move applsysd02.dbf to another location ?

      2- Copy it to another location ?

      3- Make a new datafile and move the data to it ?

        • 1. Re: Moving EBS Datafile applsysd02.dbf
          Bashar.

          Hi,

           

          None of the solutions you posted will fix the problem. The file is corrupted.

          You must recover the file from the most recent backup.

           

          Regards,

          Bashar

          • 2. Re: Moving EBS Datafile applsysd02.dbf
            984556

            I don't have a recent backup, and Database is in no archive mode.


            any  solution or fix for this ?

             

            Please note, EBS is working well, even with this bad sector on applsysd02.dbf however, the problem is when I take tar and untar, it will not work, some tables are disappearing as you seen in the other post.

            • 3. Re: Moving EBS Datafile applsysd02.dbf
              Bashar.

              You are running really old application and database releases.

              I'm afraid that no one here can help you recover from this situation.

              A backup is the only way to get everything working again unless you log a SR.

              You may want to post in one of the database forums for advice on recovering the datafile. They will probably wonder why you don't have a proper backup strategy!

               

              Regards,

              Bashar

              • 4. Re: Moving EBS Datafile applsysd02.dbf
                VishnuVinnakota

                Could be possible that there are multiple block corruptions:

                 

                select * from v$database_block_corruption ;


                Post the result here. I recommend Oracle Support though.

                • 5. Re: Re: Moving EBS Datafile applsysd02.dbf
                  984556

                  thanks vishnu for the reply

                   

                  oraprod@clodb:/oratech/proddb/9.2.0>sqlplus

                   

                   

                  SQL*Plus: Release 9.2.0.3.0 - Production on Wed Jun 1 00:22:05 2016

                   

                   

                  Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

                   

                   

                  Enter user-name: sys

                  Enter password:

                   

                   

                  Connected to:

                  Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production

                  With the Partitioning, OLAP and Oracle Data Mining options

                  JServer Release 9.2.0.3.0 - Production

                   

                   

                   

                  SQL> select * from v$database_block_corruption ;

                   

                   

                  no rows selected

                   

                   

                  SQL>

                   

                  however when i copy or tar applsysd02.dbf

                   

                  root@clodb:/clodbvg>/opt/freeware/bin/tar cvf - /oradata | gzip > /clodbvg/bkp_30MAY16.tgz

                  /opt/freeware/bin/tar: Removing leading `/' from member names

                  /oradata/

                  .........

                  .......

                  /oradata/proddata/apd01.dbf

                  /oradata/proddata/applsysd01.dbf

                  /oradata/proddata/applsysd02.dbf

                  /opt/freeware/bin/tar: /oradata/proddata/applsysd02.dbf: Read error at byte 4767080960, r eading 10240 bytes: There is an input or output error.

                  /oradata/proddata/applsysd03.dbf

                  .....

                  ......

                  oradata/proddata/zfax01.dbf

                  /oradata/proddata/zsad01.dbf

                  /oradata/proddata/zsax01.dbf

                  /opt/freeware/bin/tar: Error exit delayed from previous errors

                  root@clodb:/clodbvg>

                   

                   

                   

                  root@clodb:/oradata>cp /oradata/proddata/applsysd02.dbf /backup/applsysd02.dbf

                  cp: /oradata/proddata/applsysd02.dbf: There is an input or output error.

                   

                   


                  root@clodb:/oradata>dd if=/oradata/proddata/applsysd02.dbf of=/dev/null bs=1024

                  dd: 0511-051 The read failed.

                  : There is an input or output error.

                  4655364+0 records in.

                  4655364+0 records out.

                  root@clodb:/oradata>

                  • 6. Re: Moving EBS Datafile applsysd02.dbf
                    Dhananjay Papde

                    Hello,

                     

                    In your other related note, you mentioned "I have tried to restore it and check, indeed the EBS doesn't work because some of the tables are missing".

                     

                    Checkout if dbms_repair is helpful to  detect and fix or skip corrupt blocks.

                    https://docs.oracle.com/cd/B19306_01/server.102/b14231/repair.htm

                     

                    Thanks,

                    Dhananjay

                    • 7. Re: Re: Moving EBS Datafile applsysd02.dbf
                      984556

                      Also, I am unable to extend the Datafile (100% utilized)

                       

                      ORA-03297: file contains used data beyond requested RESIZE value

                      • 8. Re: Re: Moving EBS Datafile applsysd02.dbf
                        984556

                        Dhananjay Papde wrote:

                         

                        Hello,

                         

                        In your other related note, you mentioned "I have tried to restore it and check, indeed the EBS doesn't work because some of the tables are missing".

                         

                        Checkout if dbms_repair is helpful to  detect and fix or skip corrupt blocks.

                        https://docs.oracle.com/cd/B19306_01/server.102/b14231/repair.htm

                         

                        Thanks,

                        Dhananjay

                         

                        thanks, but repair will not work, because this is part of EBS, and the table / tablespace which is in question is something to do with APPS TOP node configuration. So, if you skip FND_NODE info will disappear, Apps tier will have problems, like the one which I had Standard Manager / Concurrent Manager...

                         

                        have to think of some other solution

                        • 9. Re: Moving EBS Datafile applsysd02.dbf
                          Kj - Kiran Jadhav

                          I also don't think you can recover it at Database Level. You need to check with AIX admin to fix the filesystem bad sectors.

                           

                           

                          Regards,

                          Kiran

                          • 10. Re: Re: Moving EBS Datafile applsysd02.dbf
                            984556

                            Thanks for the reply, there is no way, in AIX bad sector can be repaired.

                             

                            My question is: since we did not find any corrupt / bad blocks in the Database

                            SQL> select * from v$database_block_corruption ;

                             

                             

                            no rows selected

                             

                             

                            SQL>

                             

                            1) Can we copy the contents of the Tablespace -1 to another tablespace (Tablespace 2) and then delete Tablespace-1 and recreate it on a new filesystem - as TableSpace-1 and then copy back the contents of (Tablespace 2) back into TableSpace-1 ?

                             

                            2) Now since EBS is working fine and Tablespace is readable, (but the problem is at OS level you cannot copy the DBF file), is there any way to move the contents ?

                             

                            This is one of the areas where Oracle should work on, bad sectors are very common on HDDs and I am surprised, other than backup/restore there is no solution ?



                            • 11. Re: Moving EBS Datafile applsysd02.dbf
                              Kj - Kiran Jadhav

                              There are various way to migrate the objects from one tablespace to another with table move, expdbp/impdp, index rebuild etc. However in your case, issue with APPLSYSD tablespace which is one of the key tablespace for EBS application.

                              So moving objects from this tablespace might cause severe issue with your application. Please consult with Oracle Support to recover from this scenario.

                               

                              Regards,

                              Kiran

                              1 person found this helpful