This discussion is archived
1 2 3 Previous Next 38 Replies Latest reply: Apr 30, 2013 7:50 AM by John Stegeman Go to original post RSS
  • 15. Re: RESTORE DATABASE OR RECOVER DATABASE
    MahirM.Quluzade Guru
    Currently Being Moderated
    yxes2013 wrote:
    When do I use this program? :(
    Now, from SQL*Plus, run the following PL/SQL block to restore the file:
    DECLARE
    v_devtype   VARCHAR2(100);
    v_done      BOOLEAN;
    v_maxPieces NUMBER;
    
    TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
    v_pieceName t_pieceName;
    BEGIN
    -- Define the backup pieces... (names from the RMAN Log file)
    v_pieceName(1) :=
    '/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
    v_pieceName(2) :=
    '/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
    v_maxPieces    := 2;
    
    -- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
    v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');
    
    -- Restore the first Control File...
    DBMS_BACKUP_RESTORE.restoreSetDataFile;
    
    -- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
    DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');
    
    dbms_output.put_line('Start restoring '||v_maxPieces||' pieces.');
    FOR i IN 1..v_maxPieces LOOP
    dbms_output.put_line('Restoring from piece '||v_pieceName(i));
    DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done, params=>null);
    exit when v_done;
    END LOOP;
    
    -- Deallocate the channel...
    DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_BACKUP_RESTORE.deviceDeAllocate;
    RAISE;
    END;
    /
    Thanks....
    DBMS_BACKUP_RESTORE - package is using RMAN, you know.
    I think you must use RMAN explicitly restore database.

    Mahir
  • 16. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    I thank you all,

    But i still got error :(
    RMAN> startup nomount;
    
    connected to target database (not started)
    Oracle instance started
    
    Total System Global Area    1610612736 bytes
    
    Fixed Size                     2084296 bytes
    Variable Size                553648696 bytes
    Database Buffers            1040187392 bytes
    Redo Buffers                  14692352 bytes
    
    RMAN> recover controlfile from autobackup;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found "controlfile": expecting one of: "copy, clone, database, datafile, datafilecopy, device, standby, tablespace"
    RMAN-01007: at line 1 column 9 file: standard input
    
    RMAN> restore control file from backup;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found "identifier": expecting one of: "archivelog, channel, check, controlfile, clone, database, datafile, device, from, force, high, (, preview, spfile, standby, tablespace, until, validate"
    RMAN-01008: the bad identifier was: control
    RMAN-01007: at line 1 column 9 file: standard input
    
    RMAN> restore control file from autobackup;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found "identifier": expecting one of: "archivelog, channel, check, controlfile, clone, database, datafile, device, from, force, high, (, preview, spfile, standby, tablespace, until, validate"
    RMAN-01008: the bad identifier was: control
    RMAN-01007: at line 1 column 9 file: standard input
    
    RMAN>
    Thanksss
  • 17. Re: RESTORE DATABASE OR RECOVER DATABASE
    MahirM.Quluzade Guru
    Currently Being Moderated
    You must use :
    RMAN>restore controlfile from autobackup;
    p.s. I aggree with Hemant : Re: Don't you get that feeling ....
  • 18. Re: RESTORE DATABASE OR RECOVER DATABASE
    sybrand_b Guru
    Currently Being Moderated
    This is because you don't READ documentation.

    -------------
    Sybrand Bakker
    Senior Oracle DBA
  • 19. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    Thanks :)
    RMAN> restore controlfile from backup;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found "backup": expecting one of: "autobackup, double-quoted-string, single-quoted-string, tag"
    RMAN-01007: at line 1 column 26 file: standard input
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
    RMAN-01007: at line 1 column 32 file: standard input
    
    RMAN> restore controlfile from autobackup;
    
    Starting restore at 30-APR-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=156 devtype=DISK
    
    recovery area destination: /u01/app/oracle/flash_recovery_area
    database name (or database unique name) used for search: TEST1
    channel ORA_DISK_1: autobackup found in the recovery area
    channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST1/autobackup/2013_04_30/o1_mf_s_814116069_8qyw7614_.bkp
    channel ORA_DISK_1: control file restore from autobackup complete
    output filename=/u01/app/oracle/oradata/test1/control01.ctl
    output filename=/u01/app/oracle/oradata/test1/control02.ctl
    output filename=/u01/app/oracle/oradata/test1/control03.ctl
    Finished restore at 30-APR-13
    
    RMAN> recover database;
    
    Starting recover at 30-APR-13
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 04/30/2013 17:54:49
    ORA-00210: cannot open the specified control file
    
    RMAN>
    Almost done ....
  • 20. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    RMAN> alter database mount;
    
    database mounted
    released channel: ORA_DISK_1
    
    RMAN> recover database;
    
    Starting recover at 30-APR-13
    Starting implicit crosscheck backup at 30-APR-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=156 devtype=DISK
    Crosschecked 11 objects
    Finished implicit crosscheck backup at 30-APR-13
    
    Starting implicit crosscheck copy at 30-APR-13
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 30-APR-13
    
    searching for all files in the recovery area
    cataloging files...
    cataloging done
    
    List of Cataloged Files
    =======================
    File Name: /u01/app/oracle/flash_recovery_area/TEST1/autobackup/2013_04_30/o1_mf_s_814116069_8qyw7614_.bkp
    
    using channel ORA_DISK_1
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 04/30/2013 17:56:58
    RMAN-06094: datafile 1 must be restored
    
    RMAN>
    Whats this catalog?
  • 21. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    ok i got it.....courtesy of saravanan :)

    Step 1: RMAN> Connect target /

    Step 2: Startup nomount pfile='<pfile name>'

    Step 3: restore control file from backup

    Step 4: alter database mount

    Step 5: restore database

    Step 6: recover database

    Step 7: open database resetlogs;

    Thanks,
  • 22. Re: RESTORE DATABASE OR RECOVER DATABASE
    sybrand_b Guru
    Currently Being Moderated
    You can read about it in the online documentation.
    Please stop asking doc questions.

    --------------
    Sybrand Bakker
    Senior Oracle DBA
  • 23. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    Now I am ready to face the "tough" world and to write the certification exam. ;)

    You see how this actual hands-on helps people? It is social relevant, so you should not complain. :D

    I THANK YOU ALL!!!!

    Edited by: yxes2013 on 30.4.2013 3:06
  • 24. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    YIKESS!!!!! it failed :(
    RMAN> alter database open;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of alter db command at 04/30/2013 18:11:17
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
    RMAN>  alter database open resetlogs;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of alter db command at 04/30/2013 18:11:27
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '/u01/app/oracle/oradata/test1/system01.dbf'
    Why?
  • 25. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    dup

    Edited by: yxes2013 on 30.4.2013 3:35
  • 26. Re: RESTORE DATABASE OR RECOVER DATABASE
    yxes2013 Newbie
    Currently Being Moderated
    reverted
  • 27. Re: RESTORE DATABASE OR RECOVER DATABASE
    Justin_Mungal Journeyer
    Currently Being Moderated
    yxes2013 wrote:
    YIKESS!!!!! it failed :(
    RMAN> alter database open;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of alter db command at 04/30/2013 18:11:17
    ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
    RMAN>  alter database open resetlogs;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of alter db command at 04/30/2013 18:11:27
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '/u01/app/oracle/oradata/test1/system01.dbf'
    Why?
    People need to quit encouraging you. Look up the error with google, oerr, MOS. Read the manuals already.
  • 28. Re: RESTORE DATABASE OR RECOVER DATABASE
    Fran Guru
    Currently Being Moderated
    you had an ORA- before that error when you was restoring. If you had read it, you had discover that you need to recover with "until" clause. for example

    recover until scn XXXXX;
    recover until time TO_DATE('30/04/2013','MM/DD/YYYY');
    recover until sequence XXXXX;


    choose one and try again.
  • 29. Re: RESTORE DATABASE OR RECOVER DATABASE
    sybrand_b Guru
    Currently Being Moderated
    https://forums.oracle.com/forums/ann.jspa?annID=718

    Especially the first point.

    From now on I will completely ignore you.
    I recommend all others to do that too, starting immediately,

    ------------
    Sybrand Bakker
    Senior Oracle DBA

Legend

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