This discussion is archived
1 2 3 4 Previous Next 45 Replies Latest reply: May 15, 2013 11:34 PM by 1008335 RSS

Restore the database

952066 Newbie
Currently Being Moderated
Hello,

I lost my initilizing parameter file (PFILE) and SPFILE and if I am trying to start the database, getting below error.

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/home/oracle/product/11.2.0/db_1/dbs/initmydb.ora'


and when I am trying to restore the database through rman I am getting below error:

RMAN> restore database from '/u01/home/oracle/product/backup/b1.bkp';

Starting restore at 28-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/28/2013 04:54:10
ORA-01507: database not mounted

I have the database in this location : "/u01/home/oracle/product/backup/b1.bkp"

Please assist.

Warm Rerargs,
Himanshu
  • 1. Re: Restore the database
    sybrand_b Guru
    Currently Being Moderated
    1 You can reconstruct the pfile from the alert log as non-default parameters will be logged there

    2 'Database not mounted' just means you didn't mount the database. The database needs to be mounted for the restore command to work.

    This is all in the online documentation you refuse to read.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Restore the database
    Alvaro Pro
    Currently Being Moderated
    Hello,

    To do a succesfull restore, you would need:

    1. startup nomount the database - For this you will need a PFILE or SPFILE.

    2. Restore the control file

    3. Mount and proceed with restore.

    If you lost your PFILE/SPFILE a quick way to reconstruct is to look at your alert.log file. Look at the last startup recorded there, it will have logged all of the parameters present in the PFILE/SPFILE used to do the startup, from there should be easy to reconstruct the file using the format:

    instance.parameter='param_value'

    If it's a parameter for all instances use a *

    Hope it helps
  • 3. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    Hi Sybrand,

    Yes from alert log we can reconstrust the PFILE. But I want to make my database working by restoration of database through RMAN backup as I have the full database backup at this location :

    +/u01/home/oracle/product/backup/b1.bkp+

    Can I do the same. If yes then how. Please suggest.

    Regards,
    Himanshu
  • 4. Re: Restore the database
    Alvaro Pro
    Currently Being Moderated
    Yes you can do this solely through RMAN if you want.

    If you have SPFILE autobackup configured, then you can do a startup nomout from RMAN and then restore the autobackup SPFILE.

    Also, you can restore it manually if you know which backupset it is on.

    Edited by: Alvaro on 10/05/2013 23:38
  • 5. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    Hi Alvaro,

    I am doing the same with following command but getting below error :

    RMAN> startup force nomount;

    Oracle instance started

    Total System Global Area 626327552 bytes

    Fixed Size 2215944 bytes
    Variable Size 381685752 bytes
    Database Buffers 239075328 bytes
    Redo Buffers 3350528 bytes

    RMAN> restore spfile from '/u01/home/oracle/product/backup/b1.bkp';

    Starting restore at 28-APR-13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=19 device type=DISK

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 04/28/2013 05:54:47
    RMAN-06564: must use the TO clause when the instance is started with SPFILE


    Please Help.

    Edited by: Himanshu Gupta on 10-May-2013 23:47
  • 6. Re: Restore the database
    Alvaro Pro
    Currently Being Moderated
    That one time, it looks like you used an spfile.

    Make sure there is no initxxx.ora or spfilexxx.ora on your $ORACLE_HOME/dbs, shut it down and then try that again.

    The RMAN-06564 is generated because the instance was started using an spfile so Oracle asks that you use a "TO" clause to specify a different name.
  • 7. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    Yes Alvaro, this is making sure that there is no initmydb.ora or spfilemydb.ora. please see below:

    [oracle@localhost ~]$ ls /u01/home/oracle/product/11.2.0/db_1/dbs
    hc_DBUA0.dat hc_MYDB.dat hc_ORCL.dat initorcl.ora lkDUMMY lkORCL orapworcl snapcf_MYDB.f spfileorcl.ora
    hc_mydb.dat hc_orcl.dat init.ora initORCL.ora lkMYDB orapwmydb snapcf_mydb.f snapcf_orcl.f

    Here I am trying to get my PFILE or SPFILE for "MYDB" database
  • 8. Re: Restore the database
    Alvaro Pro
    Currently Being Moderated
    Please upload the following:


    Go on RMAN, do the
    startup force nomount
    Exit RMAN,

    Go on the database and do a
    show parameter spfile;
    Edited by: Alvaro on 11/05/2013 00:01
  • 9. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    Here I more thing I wanted to let you know that my "b1.bkp" backupset is the full database backup at the below location :

    +/u01/home/oracle/product/backup+
  • 10. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    Alvaro,

    Please find below :

    RMAN> startup force nomount

    startup failed: ORA-01078: failure in processing system parameters
    LRM-00109: could not open parameter file '/u01/home/oracle/product/11.2.0/db_1/dbs/initMYDB.ora'

    starting Oracle instance without parameter file for retrieval of spfile
    Oracle instance started

    Total System Global Area     158662656 bytes

    Fixed Size                     2211448 bytes
    Variable Size                 88080776 bytes
    Database Buffers              62914560 bytes
    Redo Buffers                   5455872 bytes


    SQL> show parameter spfile;

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    spfile                               string
  • 11. Re: Restore the database
    Alvaro Pro
    Currently Being Moderated
    Okay, now you're not using an spfile, please notice the message:
    startup failed: ORA-01078: failure in processing system parameters
    LRM-00109: could not open parameter file '/u01/home/oracle/product/11.2.0/db_1/dbs/initMYDB.ora'

    starting Oracle instance without parameter file for retrieval of spfile
    Which you didn't have on the first try.

    Now just go to RMAN and try to restore the SPFILE again please.
  • 12. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    Dude,

    Same I did and I got this :


    RMAN> restore spfile from '/u01/home/oracle/product/backup/b1.bkp';

    Starting restore at 28-APR-13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=20 device type=DISK

    channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/home/oracle/product/backup/b1.bkp
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 04/28/2013 06:31:11
    ORA-19687: SPFILE not found in backup set
  • 13. Re: Restore the database
    Alvaro Pro
    Currently Being Moderated
    RMAN did not find a SPFILE in that backupset. Please notice:

    ORA-19687: SPFILE not found in backup set
    Are you sure you took a spfile backup?

    If your controlfile autobackup is OFF, which is the default, then an SPFILE backup is not included unless you manually backup it up with:

    BACKUP SPFILE;
  • 14. Re: Restore the database
    952066 Newbie
    Currently Being Moderated
    No Alvaro while taking the backup I did not take SPFILE backup as I thought that while taking the full backup it will automatically take the SPFILE backup. And My CONTROL FILE auto backup is ON. Becayse I remember that before taking the full backup I used following command.

    RMAN>CCONFIGURE CONTROLFILE AUTOBACKUP ON;

    Edited by: Himanshu Gupta on 11-May-2013 00:32
1 2 3 4 Previous Next

Legend

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