9 Replies Latest reply: Oct 6, 2013 8:36 PM by Hemant K Chitale RSS

    Recover database from old data filse

    user10894799

      Hi All

       

      I have oracle 10g database,

      unfortunately, our machine and system crashed and all the backups and oracle archive logs, but we have old image copies of our data files and one of the control file

       

      how I can restore our database from this copies?

       

      I tried to create a new database and replace the old files with new files, but I couldn't (I got message, we need to open the database with resetlogs, but we can't open it if we didn't make incomplete recovery)

       

      please help me

        • 1. Re: Recover database from old data filse
          gottikere

          1. Do you have OLD IMAGE COPIES ( you mean to say RMAN image copies) or backup files?

          2. If its a Manual backup , than have to taken archive log backup along with it?

           

          If you have backup files:

           

          1. Restore the controlfile.

          2. Restore the datafile

          3. Recovery database until cancel.

          4. ALTER DATABASE OPEN RESETLOGS;

           

          NOTE: If you have the archive log file from the day you have backuped up those datafile than you can do the recovery until time. If you dont have those to till date , you can recover only to the point you  have the archive logs.

           

          Thanks,

          http://gssdba.wordpress.com

          • 2. Re: Recover database from old data filse
            Anar Godjaev

            HI ,

             

            Step by step  : Please check this link  :  https://anargodjaev.wordpress.com/2013/03/01/recovery-scenario-complete-loss-of-all-database-files-including-spfile-usin…

             

            opsdba:/u01/ORACLE/opsdba>ls -l

            total 1948980

            drwxr-x---    2 oracle  dba 4096 Feb 12 13:35 arch

            -rw-r-----    1 oracle   dba 7389184 Feb 12 13:57 control01.ctl

            -rw-r-----    1 oracle   dba 7389184 Feb 12 13:57 control02.ctl

            -rw-r-----    1 oracle   dba 7389184 Feb 12 13:57 control03.ctl

            -rw-r-----    1 oracle   dba 104865792 Feb 12 13:57 drtbs1.dbf

            -rw-r-----    1 oracle   dba 104865792 Feb 12 13:57 drtbs2.dbf

            drwxr-xr-x    2 oracle  dba 4096 Feb 12 13:55 recovery

            -rw-r-----    1 oracle   dba 52429312 Feb 12 13:34 redo01.log

            -rw-r-----    1 oracle   dba 52429312 Feb 12 13:35 redo02.log

            -rw-r-----    1 oracle   dba 52429312 Feb 12 13:57 redo03.log

            -rw-r-----    1 oracle   dba 367009792 Feb 12 13:57 sysaux01.dbf

            -rw-r-----    1 oracle   dba 513810432 Feb 12 13:57 system01.dbf

            -rw-r-----    1 oracle   dba 20979712 Jan 30 12:32 temp01.dbf

            -rw-r-----    1 oracle   dba 5251072 Feb 12 13:57 undonew.dbf

            -rw-r-----    1 oracle   dba 634396672 Feb 12 13:57 undotbs01.dbf

            -rw-r-----    1 oracle   dba 31465472 Feb 12 13:57 users01.dbf

            -rw-r-----    1 oracle   dba 10493952 Feb 12 13:57 users02.dbf

            -rw-r-----    1 oracle   dba 10493952 Feb 12 13:57 users03.dbf

            -rw-r-----    1 oracle   dba 10493952 Feb 12 13:57 users04.dbf

            -rw-r-----    1 oracle   dba 10493952 Feb 12 13:57 users05.dbf

            -rw-r-----    1 oracle   dba 5251072 Feb 12 13:57 users06.dbf

            -rw-r-----    1 oracle   dba 5251072 Feb 12 13:57 users07.dbf

            opsdba:/u01/ORACLE/opsdba>rm *.dbf

                                                                                         *FILES REMOVED

            opsdba:/u01/ORACLE/opsdba>ls -l *.dbf

            ls: *.dbf: No such file or directory

             

            opsdba:/opt/oracle/product10gpr2/dbs>mv spfileopsdba.ora spfileopsdba.org

            opsdba:/opt/oracle/product10gpr2/dbs>ls -lt spfile*        *SPFILE REMOVED

            -rw-r-----    1 oracle   dba 2560 Feb 12 13:06 spfileopsdba.org

            -rw-r-----    1 oracle   dba 2560 Jan 23 11:32 spfilerman10d.ora

            -rw-r-----    1 oracle   dba 4608 Dec 18 09:28 spfileerpmig.ora

            -rw-r-----    1 oracle   dba 3584 Nov 21 08:22 spfileprimary.ora

            -rw-r-----    1 oracle   dba 3584 Oct 22 14:32 spfilebozo.ora

            -rw-r-----    1 oracle   dba 4608 Oct  8 15:24 spfilestreams2.ora

            -rw-r-----    1 oracle   dba 2560 Oct  1 11:46 spfilevaultdb.ora

             

            THE FOLLOWING STEPS WILL OUTLINE THE RECOVERY PROCESS:

             

            Step 1:           RECOVERY OF SPFILE

             

            Create spfile.rcv as:

            set dbid= 1499754868

            run {

            startup nomount force  ;

            };

             

            opsdba:/u01/ORACLE/opsdba/recovery>rman target / catalog rman10/rman10@rman10p cmdfile=spfile.rcv

             

            Recovery Manager: Release 10.2.0.2.0 - Production on Mon Feb 12 14:02:26 2007

             

            Copyright (c) 1982, 2005, Oracle.  All rights reserved.

             

            connected to target database (not started)

            connected to recovery catalog database

             

            RMAN>

            executing command: SET DBID

            database name is "OPSDBA" and DBID is 1499754868

             

            Oracle instance started

             

            Total System Global Area     264241152 bytes

             

            Fixed Size                     2070416 bytes

            Variable Size                113248368 bytes

            Database Buffers             142606336 bytes

            Redo Buffers                   6316032 bytes

             

            Recovery Manager complete.

            opsdba:/u01/ORACLE/opsdba/recovery>

             

            Now restore the spfile

             

            set dbid=1499754868

            run {

            allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

            restore spfile ;

            release channel ch1 ;

            }

             

            Step 2:           RESTORE OF CONTROLFILES

             

            Same Steps as spfile with the restore command changed. So the new script is

             

            set dbid=1499754868

            run {

            allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

            restore controlfile ;

            release channel ch1 ;

            }

             

            Step 3:           RESTORE OF DATABASE

             

            Since you have the controlfiles now mount the database

             

            SQL> conn sys as sysdba

            Enter password:

            1. Connected.

            SQL> alter database mount;

             

            Database altered.

             

            Now get the log sequence number of the database from the catalog database:

             

            select sequence# from rc_backup_redolog where db_name=’OPSDBA’;

             

            RMAN> run {

            2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';

            3> restore database ;

            4> recover database until logseq=6;                  -- GOT FROM THE ABOVE QUERY

            5> release channel ch1 ;

            6> }

            7>

            allocated channel: ch1

            channel ch1: sid=156 devtype=SBT_TAPE

            channel ch1: Data Protection for Oracle: version 5.2.4.0

             

            Starting restore at 12-FEB-07

             

            channel ch1: starting datafile backupset restore

            channel ch1: specifying datafile(s) to restore from backup set


            Step 4:           alter database open resetlogs;



            • 3. Re: Recover database from old data filse
              user10894799

              hi gottikere,

               

              I have operating system copies, so I can't execute those command

              • 4. Re: Recover database from old data filse
                saurabh

                what does this mean. you have operating system copies.

                • 5. Re: Recover database from old data filse
                  gottikere

                  So you manually backed up the datafiles by putting the tablespaces into begin backup mode or completely shutdown the database and copied the datafiles from that location to backup location? which one is correct?

                   

                  Thanks,

                  http://gssdba.wordpress.com

                  • 6. Re: Recover database from old data filse
                    user10894799

                    Hi All and many thanks fro replay

                     

                    I am sorry, and I think you will say "this is a stupid guys " but we don't know exactly how we copied the data files or who is copied it

                    I think he shutdown the database, and then he copied the files manually by "Copy & Paste"

                    we need to lose the least possible amount of information

                    • 7. Re: Recover database from old data filse
                      Hemant K Chitale

                      Follow gottikere's first advice.

                       

                      Hemant K Chitale


                      • 8. Re: Recover database from old data filse
                        user10894799


                        but I can't execute these commands, bcz I build the system from the begin, I tried to add the old data files as backup by catalog commands, but I couldn't (I couldn't access to the catalog) (any1 help me, how I can create the catalog or how I can access to the catalog)

                        • 9. Re: Recover database from old data filse
                          Hemant K Chitale

                          >we have old image copies of our data files and one of the control file

                           

                          You don't need RMAN and a Catalog to restore such a backup.  This is called a User-Managed Backup (and it is documented in the Backup and Recovery User Guide !).  You use OS commands (e.g. "cp" if the copy is on disk or "tar" or "cpio" or the appropriate tape management command if the copy is on tape) to restore the files.

                           

                          Hemant K Chitale