This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 3, 2012 8:14 AM by Fran RSS

DB open failes with ORA-01142

639113 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    639113 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    639113 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    639113 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    639113 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    can you try tablespace level end backup;

    alter tablespace system end backup ; repeat for all tablespaces
  • 13. Re: DB open failes with ORA-01142
    916205 Newbie
    Currently Being Moderated
    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
    639113 Newbie
    Currently Being Moderated
    Thanks Arunkumar for sharing the steps.

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

Legend

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