9 Replies Latest reply: Feb 12, 2013 12:30 PM by Hussein Sawwan-Oracle RSS

    Cold Oracle EBS backup and restore doesn't work

    984556
      Hi,

      I did a backup using tar while the database was open and not in archive mode ( archivelog disabled ) and restored it on another machine.
      OS = AIX 5.3
      Oracle EBS 11.5.9
      Database 9i

      When i started the database I got errors.

      Is there a metalink document which will help me recover the database ?


      I followed the following metalinks:

      Database Startup Fails with ORA-01113, ORA-01110 errors [ID 146039.1]
      Common Causes and Solutions on ORA-1113 Error Found in Backup & Recovery [ID 183367.1]
      However, I would like to point out that the above is applicable in case of archivelogs = enabled
      My case: archivelogs = disabled

      SQL> startup mount pfile=/oracle/proddb/9.2.0/dbs/initPROD.ora
      ORACLE instance started.

      Total System Global Area 615482512 bytes
      Fixed Size 742544 bytes
      Variable Size 436207616 bytes
      Database Buffers 167772160 bytes
      Redo Buffers 10760192 bytes
      Database mounted.
      SQL> SELECT V1.FILE#, NAME
      FROM V$BACKUP V1, V$DATAFILE V2
      WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE# ; 2 3

      no rows selected
      SQL> recover database using backup controlfile until cancel;
      ORA-00279: change 3368247542 generated at 02/10/2013 03:07:21 needed for thread
      1
      ORA-00289: suggestion : /oracle/proddb/9.2.0/dbs/arch1_637.dbf
      ORA-00280: change 3368247542 for thread 1 is in sequence #637
      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
      cancel
      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
      ORA-01194: file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/oracle/proddata/system01.dbf'
      ORA-01112: media recovery not started

      SQL> recover database using backup controlfile until cancel;
      ORA-00279: change 3368247542 generated at 02/10/2013 03:07:21 needed for thread
      1
      ORA-00289: suggestion : /oracle/proddb/9.2.0/dbs/arch1_637.dbf
      ORA-00280: change 3368247542 for thread 1 is in sequence #637
      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

      ORA-00308: cannot open archived log '/oracle/proddb/9.2.0/dbs/arch1_637.dbf'
      ORA-27037: unable to obtain file status
      IBM AIX RISC System/6000 Error: 2: No such file or directory
      Additional information: 3
      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
      ORA-01194: file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/oracle/proddata/system01.dbf'
      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01194: file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/oracle/proddata/system01.dbf'

      SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
      FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence
      from X$KCVFH;

      .....
      .....
      .....

      419 rows selected.
        • 1. Re: Oracle EBS backup and restore doesn't work
          EBSDBA
          Hi,

          The way you took backup does'nt work in Oracle. When you copy them while the database is up and running all the datafiles are not consistent. And it voilates the basic ACID principle of databases.

          So if your database is in no archive log mode, the only way to take backup is using cold backup method i.e. by shutting down the database.


          Thanks
          • 2. Re: Oracle EBS backup and restore doesn't work
            984556
            I understand that Hot backups cannot be done with noarchivelog and data will be inconsistent.

            However, shouldn't the Database be able to recover whatever data is in there in the datafiles and startup normally.

            This is my question and concern. I searched for Metalink and couldn't find anything on it.

            I want to startup the database with whatever Data is present there after the backup. Any solutions for such a scenario ?
            • 3. Re: Oracle EBS backup and restore doesn't work
              EBSDBA
              981553 wrote:
              I understand that Hot backups cannot be done with noarchivelog and data will be inconsistent.

              However, shouldn't the Database be able to recover whatever data is in there in the datafiles and startup normally.
              From where should it be recoverd, only if we store the data changes in a separate files. These files are called archive logs. The archive logs will be generated when you enable archive log.
              This is my question and concern. I searched for Metalink and couldn't find anything on it.

              I want to startup the database with whatever Data is present there after the backup. Any solutions for such a scenario ?
              ORA-00279: change 3368247542 generated at 02/10/2013 03:07:21 needed for thread
              1
              ORA-00289: suggestion : /oracle/proddb/9.2.0/dbs/arch1_637.dbf
              ORA-00280: change 3368247542 for thread 1 is in sequence #637
              Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
              When it asks for change number "637" and if you are lucky enough that the redo log file with this sequence is not over written then that redo log file can be given as input when it asks as above.

              But again, all these tries are for nothing. At the end of the day if your database is in no archive log mode the reliable backup is only the one taken using cold backup method. i.e. by shutting down the database.


              Thanks
              • 4. Re: Oracle EBS backup and restore doesn't work
                984556
                >
                But again, all these tries are for nothing. At the end of the day if your database is in no archive log mode the reliable backup is only the one taken using cold backup method. i.e. by shutting down the database.


                Thanks
                At this point, Taher . I am not asking about reliable backup or if the contents are upto date, what I am seeking for is to startup the database.

                My question is not if the data is consistent or upto date or if the backup is reliable, my question is in such a scenario, how to startup and recover your database ?

                So far there is nothing on google or this forum, so if anyone answers and has solution will be the first one.

                thanks
                • 5. Re: Oracle EBS backup and restore doesn't work
                  EBSDBA
                  At this point, Taher . I am not asking about reliable backup or if the contents are upto date, what I am seeking for is to startup the database.
                  As i told before it does not satisfy the ACID properties if it opens with out recovering to the full. Suppose if you have done a transaction for 1 million $ and that transaction is not recovered and you some how successfully open the database. Then in this case do you trust the database?

                  So all the transactions that were committed must be able recovered to the full when you restore.

                  >
                  My question is not if the data is consistent or upto date or if the backup is reliable, my question is in such a scenario, how to startup and recover your database ?

                  So far there is nothing on google or this forum, so if anyone answers and has solution will be the first one.

                  thanks
                  Sorry, I do not think you'll find it any where.

                  Thanks
                  • 6. Re: Oracle EBS backup and restore doesn't work
                    Srini Chavali-Oracle
                    You cannot recover a database in this manner when it is in NOARCHIVELOG mode - the only valid backup for a database in NOARCHIVELOG mode is a closed full backup.

                    http://docs.oracle.com/cd/B10501_01/server.920/a96519/backup.htm#1004673

                    Oracle Consulting provides a for-fee service that can possibly recover such databases

                    HTH
                    Srini
                    • 7. Re: Oracle EBS backup and restore doesn't work
                      Hussein Sawwan-Oracle
                      981553 wrote:
                      I understand that Hot backups cannot be done with noarchivelog and data will be inconsistent.
                      Correct.
                      However, shouldn't the Database be able to recover whatever data is in there in the datafiles and startup normally.

                      This is my question and concern. I searched for Metalink and couldn't find anything on it.

                      I want to startup the database with whatever Data is present there after the backup. Any solutions for such a scenario ?
                      If you have taken an offline backup, then you can recover the database by restoring all files and bring the services up (no recovery is required here).

                      And, as you mentioned "Hot backups cannot be done with noarchivelog and data will be inconsistent".

                      Thanks,
                      Hussein
                      • 8. Re: Oracle EBS backup and restore doesn't work
                        984556
                        Thank you everyone for the replies. Please correct me if I am wrong:

                        Hot backups done while the oracle database is open and noarchivelog ( archive log is not enabled ) then the only way to recover or start the database is to seek Oracle Consulting provides a for-fee service that can possibly recover such databases other-than-that there is no solution.
                        • 9. Re: Oracle EBS backup and restore doesn't work
                          Hussein Sawwan-Oracle
                          981553 wrote:
                          Thank you everyone for the replies. Please correct me if I am wrong:

                          Hot backups done while the oracle database is open and noarchivelog ( archive log is not enabled ) then the only way to recover or start the database is to seek Oracle Consulting provides a for-fee service that can possibly recover such databases other-than-that there is no solution.
                          Hot backup can be taken while the database is open and in archivelog mode.

                          If you have taken a hot backup of a noarchivelog mode, then this backup is invalid and cannot be used to restore/recover the database. Only cold backup should be used for nonarchivelog databases (while the database is down).

                          Thanks,
                          Hussein