1 2 Previous Next 16 Replies Latest reply: Dec 3, 2012 10:14 AM by Fran RSS

    DB open failes with ORA-01142

    user636110
      Hello Everyone

      I'm trying to restore one of the hot backup, however DB open fails,I dont have much information abt this backup, this was handed over to me, but feel there should be some way to end this backup and open the database.
      Any guidence in this will be much appreciated.
      following is my scenario.


      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01113: file 1 needs media recovery if it was restored from backup, or END

      SQL> select status from v$backup;

      STATUS
      ------------------
      ACTIVE
      ACTIVE
      ACTIVE
      ACTIVE
      ACTIVE
      ACTIVE
      ACTIVE

      SQL> alter database end backup;
      alter database end backup
      *
      ERROR at line 1:
      ORA-01142: cannot end online backup - none of the files are in backup


      Thanks
        • 1. Re: DB open failes with ORA-01142
          Fran
          after restore, you must recover.

          rman>run{
          rman> restore database;
          rman> recover database;
          rman>} alter database open resetlogs;

          also how you make the backup? could you post rman script?
          • 2. Re: DB open failes with ORA-01142
            user636110
            Hi Fran

            Backup has been taken using normal online backup not RMAN backup.

            Below is my scenario, i recreated the control file since backup is restored onto a different machine with different directory structure.
            My question is how can i end the backup and open the db.

            CHARACTER SET WE8MSWIN1252;

            Control file created.



            SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
            ORA-00279: change 7735416715692 generated at 11/19/2012 03:01:21 needed for
            thread 1
            ORA-00289: suggestion :

            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
            cancel
            Media recovery cancelled.

            SQL> alter database open resetlogs;
            alter database open resetlogs
            *
            ERROR at line 1:
            ORA-01113: file 1 needs media recovery if it was restored from backup, or END
            BACKUP if it was not
            ORA-01110: data file 1:


            SQL> select status from v$backup;

            STATUS
            ------------------
            ACTIVE
            ACTIVE
            ACTIVE
            ACTIVE
            ACTIVE
            ACTIVE


            SQL> alter database end backup;
            alter database end backup
            *
            ERROR at line 1:
            ORA-01142: cannot end online backup - none of the files are in backup
            • 3. Re: DB open failes with ORA-01142
              EdStevens
              user636110 wrote:
              Hi Fran

              Backup has been taken using normal online backup not RMAN backup.
              that is something that should be corrected as soon as you put out the current brush fire.

              There is simply no excuse for not using rman to handle your backups and recoveries.


              >
              Below is my scenario, i recreated the control file since backup is restored onto a different machine with different directory structure.
              My question is how can i end the backup and open the db.

              CHARACTER SET WE8MSWIN1252;

              Control file created.



              SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
              ORA-00279: change 7735416715692 generated at 11/19/2012 03:01:21 needed for
              thread 1
              ORA-00289: suggestion :

              Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
              cancel
              Media recovery cancelled.

              SQL> alter database open resetlogs;
              alter database open resetlogs
              *
              ERROR at line 1:
              ORA-01113: file 1 needs media recovery if it was restored from backup, or END
              BACKUP if it was not
              ORA-01110: data file 1:


              SQL> select status from v$backup;

              STATUS
              ------------------
              ACTIVE
              ACTIVE
              ACTIVE
              ACTIVE
              ACTIVE
              ACTIVE


              SQL> alter database end backup;
              alter database end backup
              *
              ERROR at line 1:
              ORA-01142: cannot end online backup - none of the files are in backup
              • 4. Re: DB open failes with ORA-01142
                user636110
                Steven
                I understand the importance of RMAN backup, but at the moment i would like to focus on this issue rather than looking for what were the excuses for not implementing RMAN.
                I'm sure in the RMAN world there is still the possibility of fixing this online backup issue, which I'm looking for here on this mega forum.
                • 5. Re: DB open failes with ORA-01142
                  Troll35
                  Hello,

                  When you typed :
                  RECOVER DATABASE USING BACKUP CONTROLFILE;

                  You should provide archive log path and name.
                  If you type CANCEL, then the recover is cancelled and the database may be in an inconsistent state, and you can not open it.

                  Do you have archive log generated during the backup?

                  Regards,
                  • 6. Re: DB open failes with ORA-01142
                    Girish Sharma
                    Post the output of below commands (just copy and paste here)
                    SQL>shutdown immediate;
                    SQL>startup;

                    Regards
                    Girish Sharma
                    • 7. Re: DB open failes with ORA-01142
                      vlethakula
                      post

                      select distinct error from v$recover_file;

                      select file#,checkpoint_change# from v$datafile_header;

                      select checkpoint_change# from v$database;

                      And finally can you check is it in archive log mode

                      archive log list
                      • 8. Re: DB open failes with ORA-01142
                        Fran
                        I see some errors and have some doubts:
                        ORA-01142: cannot end online backup - none of the files are in backup
                        First at all, you must put datafiles in backup mode:
                        ALTER DATABASE BEGIN BACKUP;
                        did you did it?
                        if yes, probably your backup copy is wrong or corrupted.
                        SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
                        ORA-00279: change 7735416715692 generated at 11/19/2012 03:01:21 needed for
                        thread 1
                        ORA-00289: suggestion :
                        
                        Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                        cancel
                        Media recovery cancelled.
                        it is correct? or:
                        RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
                        or
                        RECOVER DATABASE UNTIL TIME 'dd/mm/yyyy hh24:mi:ss' USING BACKUP CONTROLFILE;
                        • 9. Re: DB open failes with ORA-01142
                          user636110
                          Hi

                          I dont have archives generated during the backup, following is the output.


                          SQL> shut immediate
                          ORA-01109: database not open


                          Database dismounted.
                          ORACLE instance shut down.
                          SQL> startup
                          ORA-32004: obsolete and/or deprecated parameter(s) specified
                          ORACLE instance started.

                          Total System Global Area 1207959552 bytes
                          Fixed Size 2051680 bytes
                          Variable Size 352326048 bytes
                          Database Buffers 838860800 bytes
                          Redo Buffers 14721024 bytes
                          Database mounted.
                          ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


                          SQL> alter database open resetlogs;
                          alter database open resetlogs
                          *
                          ERROR at line 1:
                          ORA-01113: file 1 needs media recovery if it was restored from backup, or END
                          BACKUP if it was not
                          ORA-01110: data file 1:


                          SQL> select distinct error from v$recover_file;

                          ERROR
                          -----------------------------------------------------------------


                          SQL> select file#,checkpoint_change# from v$datafile_header;


                          FILE# CHECKPOINT_CHANGE#
                          ---------- ------------------
                          1 7.7354E+12
                          2 7.7354E+12
                          3 7.7354E+12
                          4 7.7354E+12
                          5 7.7354E+12
                          6 7.7354E+12
                          7 7.7354E+12
                          8 7.7354E+12
                          9 7.7354E+12
                          10 7.7354E+12
                          11 7.7354E+12

                          FILE# CHECKPOINT_CHANGE#
                          ---------- ------------------
                          12 7.7354E+12
                          13 7.7354E+12
                          14 7.7354E+12
                          15 7.7354E+12
                          16 7.7354E+12
                          17 7.7354E+12
                          18 7.7354E+12
                          19 7.7354E+12
                          20 7.7354E+12
                          21 7.7354E+12
                          22 7.7354E+12

                          FILE# CHECKPOINT_CHANGE#
                          ---------- ------------------
                          23 7.7354E+12
                          24 7.7354E+12
                          25 7.7354E+12
                          26 7.7354E+12
                          27 7.7354E+12
                          28 7.7354E+12
                          29 7.7354E+12
                          30 7.7354E+12
                          31 7.7354E+12
                          32 7.7354E+12
                          33 7.7354E+12

                          FILE# CHECKPOINT_CHANGE#
                          ---------- ------------------
                          34 7.7354E+12
                          35 7.7354E+12
                          36 7.7354E+12
                          37 7.7354E+12
                          38 7.7354E+12
                          39 7.7354E+12
                          40 7.7354E+12
                          41 7.7354E+12
                          42 7.7354E+12
                          43 7.7354E+12
                          44 7.7354E+12

                          FILE# CHECKPOINT_CHANGE#
                          ---------- ------------------
                          45 7.7354E+12
                          46 7.7354E+12
                          47 7.7354E+12
                          48 7.7354E+12
                          49 7.7354E+12
                          50 7.7354E+12
                          51 7.7354E+12
                          52 7.7354E+12
                          53 7.7354E+12
                          54 7.7354E+12
                          55 7.7354E+12

                          FILE# CHECKPOINT_CHANGE#
                          ---------- ------------------
                          56 7.7354E+12
                          57 7.7354E+12
                          58 7.7354E+12
                          59 7.7354E+12

                          59 rows selected.

                          SQL> SQL> select checkpoint_change# from v$database;

                          CHECKPOINT_CHANGE#
                          ------------------
                          0

                          SQL> archive log list



                          Database log mode Archive Mode
                          Automatic archival Enabled
                          Archive destination
                          Oldest online log sequence 0
                          Next log sequence to archive 0
                          Current log sequence 0
                          SQL> SQL> SQL> SQL>
                          • 10. Re: DB open failes with ORA-01142
                            vlethakula
                            You dont have the archioves generated during backup?

                            Most likely NO, you can't open your database.

                            Is it development or test ?
                            Undocumented parameter "_allow_restlogs_corruption"=true; This allows the database to be opened although their SCN's do not match. You might have inconsistent data. Please check on above parameter before you do something on database:

                            Check:
                            http://dba-oracle.fr/dba/index2.php?option=com_content&do_pdf=1&id=14
                            • 11. Re: DB open failes with ORA-01142
                              user636110
                              Thanks vlethakula

                              But my concern here is i cannot take DB out of backup mode, I think DB should open if I manage to take db out of backup mode .
                              • 12. Re: DB open failes with ORA-01142
                                vlethakula
                                can you try tablespace level end backup;

                                alter tablespace system end backup ; repeat for all tablespaces
                                • 13. Re: DB open failes with ORA-01142
                                  Arunkumar Bejagam
                                  Hi ,

                                  Database Cloning with hot backup:

                                  Hot backup steps:

                                  1> create pfile from spfile ; (If database is running with spfile use this command)

                                  2> Backup control file to trace;

                                  3> Alert system switch logfile; (Note down the archive sequence)

                                  4> Alert system switch logfile;

                                  5> Keep database in " begin backup mode; "

                                  6> copy all the datafiles.

                                  7> after copying all the backup files : "end backup mode; "

                                  8> alter system switch logfile; (Repeat this for multiple times) (Note down archive sequence)

                                  9> Copy all the archives which are generated between begin backup and end backup;

                                  Hot backup completed.


                                  Goto Target Where you want to created new database using backup;

                                  1> Create a parameter file (Use source database parameter file and edit it as per new database locations and keep the files in new $ORACLE_HOME/dbs location)

                                  2> Create all the directories which are needed. (get the info from parameter file )

                                  3> Copy all the datafiles to respective locations.

                                  4> Then enter the DB details in oratab.

                                  5> Set ORALCE_SID ,ORALCE_HOME and startup DB in nomount state;

                                  6> Create controlfile. (create using crontrol file backup which we have taken)

                                  7> Recover database " Recover database Until Cancel; "

                                  8> Provide the archive files (filenames with full PATH). After apply all the archive files, enter CANCEL.

                                  9> Open database with reset logs.

                                  10> Create TEMP file ( You can get this info from control file backup which we took )

                                  that's it DB cloning completed successfully. If you want to change the DBID please use nid to change it
                                  Thanks
                                  Arunkumar Bejagam
                                  • 14. Re: DB open failes with ORA-01142
                                    user636110
                                    Thanks Arunkumar for sharing the steps.

                                    But we are troubleshooting a specific scenario here.
                                    1 2 Previous Next