1 2 3 Previous Next 38 Replies Latest reply: Apr 30, 2013 9:50 AM by John Stegeman Go to original post RSS
      • 15. Re: RESTORE DATABASE OR RECOVER DATABASE
        Mahir M. Quluzade
        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
          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
            Mahir M. Quluzade
            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
              This is because you don't READ documentation.

              -------------
              Sybrand Bakker
              Senior Oracle DBA
              • 19. Re: RESTORE DATABASE OR RECOVER DATABASE
                yxes2013
                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
                  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
                    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
                      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
                        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
                          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
                            dup

                            Edited by: yxes2013 on 30.4.2013 3:35
                            • 26. Re: RESTORE DATABASE OR RECOVER DATABASE
                              yxes2013
                              reverted
                              • 27. Re: RESTORE DATABASE OR RECOVER DATABASE
                                Justin_Mungal
                                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
                                  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
                                    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