10 Replies Latest reply: Mar 4, 2013 2:01 PM by zycoz100 RSS

    took backup of data files and controlfile but not archive file db is 11g xe

    zycoz100
      took backup of data files and controlfile but not redo file db is 11g xe r2..was trying complete shut backup,..missed redo..

      anyway to restore the db back..
      I had failed to backup redo log file..
      db was shut...properly
      any info highly appreciated.
      cheers

      Edited by: zycoz100 on Feb 27, 2013 4:42 AM
        • 1. Re: took backup of data files and controlfile but not archive file db is 11g xe
          yoonas
          Hi,

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1817039200346240329


          Regards
          Yoonas
          • 2. Re: took backup of data files and controlfile but not archive file db is 11g xe
            clcarter
            backup of data files and controlfile
            An rman backup? Using the supplied backup batch or shell script file? Or just a file copy to another folder (or drive? or filesystem? (win|linux ?))
            db was shut...properly
            Ok, that's a cold backup. If the copied files are restored, database recovery is not needed when using the backup files.
            missed redo.
            Not a big deal. For hot backups, the database has to be in archivelog mode and the important part is to also get all archivelog files created after backup mode was started, not the onlike redo files. Archivelog files are created from redo. When doing a restore generally you don't want to mess with redo- could be some transaction details in the online redo that didn't get written to archivelog files yet, the instance might need those bits in a recovery scenario.
            to restore the db back..
            Shutdown the database. Copy the backup files over the datafiles/controlfile(s). Startup. You're Done.

            For Windows, don't stop the database service to shutdown, just use sqlplus and connect /as sysdba; shutdown immediate; or you'll see an ora-12560 (?) error when trying to connect. The windows service must be running and the instance can be shutdown and started up without touching the host services.
            • 3. Re: took backup of data files and controlfile but not archive file db is 11g xe
              zycoz100
              at cold backup we are supposed to take online redo log files backup.. in hury forgot that redologfiles are in flash directory..!!

              I have missed..that..so after machine is formatted..and since i dont have the redolog files..
              the database wont even mount...
              So i assume redo log files are very important..
              Hope the method you mentioned works..but i feel it wont..
              Will try tomorrow and get back..
              When i try to open the database which is in non archive mode..i get the communicaton error ..
              database donot even mount..
              I do have the dataf iles and control file..

              If what you mentioned works nothing like ..it..
              Thanks and regards
              zycoz
              • 4. Re: took backup of data files and controlfile but not archive file db is 11g xe
                clcarter
                in hury forgot
                Yes that situation often results in missing critical bits. And spelling/grammar mistakes too ;)
                i dont have the redolog files
                That isn't really a big deal at all with a cold backup, as long as the datafiles were backed up after a proper (not crash, not shutdown abort) close of all database datafiles.
                database donot even mount.
                A startup nomount with a controlfile create might fix that. And redo files can be taken care of in that step as well, so having the controlfile backup isn't necessary either.
                • 5. Re: took backup of data files and controlfile but not archive file db is 11g xe
                  zycoz100
                  Yes this should work, Will try today and get back..
                  Thanks
                  • 6. Re: took backup of data files and controlfile but not archive file db is 11g xe
                    clcarter
                    A controlfile create is a fairly trivial task, for either a cold backup or hot backup scenario. Well, its an easy task after you've done the process a few times.

                    If you have a healthy oracle instance running any place else, preferably at 11gR2, or even a 10g instance can work as an example, get a system connection and do an `alter database backup controlfile to trace;` and the instance will create a new trace file with all SQL commands needed to rebuild the controlfile in the instance trace directory.

                    Have a look at the trace file, a `show parameter diag` reveals the diagnostic_dest parameter, look for the "trace" directory under that folder, for the latest *trc file. In 10g its `show parameter dump` and the trace file goes straight to the user_dump_dest directory, no digging required.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    • 7. Re: took backup of data files and controlfile but not archive file db is 11g xe
                      zycoz100
                      when i am trying to restore the database via controlfile backup .. the redo log file being not present it says file not found..
                      how can i get around with it..
                      since i dont have the older redo log file....
                      CREATE CONTROLFILE REUSE DATABASE "XE" NORESETLOGS NOARCHIVELOG
                      MAXLOGFILES 16
                      MAXLOGMEMBERS 3
                      MAXDATAFILES 100
                      MAXINSTANCES 8
                      MAXLOGHISTORY 292
                      LOGFILE
                      GROUP 1
                      'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_80L7C259_.LOG'
                      SIZE 50M BLOCKSIZE 512,
                      GROUP 2
                      'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_80L7C375_.LOG'
                      SIZE 50M BLOCKSIZE 512
                      -- STANDBY LOGFILE
                      DATAFILE
                      'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF',
                      'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF',
                      'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF',
                      'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'
                      CHARACTER SET AL32UTF8
                      ; i dont have those 2 log files.... in the destination mentioned
                      how do i get around with this..
                      • 8. Re: took backup of data files and controlfile but not archive file db is 11g xe
                        yoonas
                        Use the Set #2. RESETLOGS case from the tracefile

                        Remove/comment the "RECOVER DATABASE USING BACKUP CONTROLFILE" command

                        Here is sample, as its from linux you will find the directory strucure little different.


                        >
                        -- Set #2. RESETLOGS case
                        --
                        -- The following commands will create a new control file and use it
                        -- to open the database.
                        -- Data used by Recovery Manager will be lost.
                        <font color="red">-- The contents of online logs will be lost and all backups will
                        -- be invalidated. Use this only if online logs are damaged. </font>

                        -- After mounting the created controlfile, the following SQL
                        -- statement will place the database in the appropriate
                        -- protection mode:
                        -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
                        STARTUP NOMOUNT
                        CREATE CONTROLFILE REUSE DATABASE ORCL RESETLOGS ARCHIVELOG
                        MAXLOGFILES 16
                        MAXLOGMEMBERS 3
                        MAXDATAFILES 100
                        MAXINSTANCES 8
                        MAXLOGHISTORY 292
                        LOGFILE
                        GROUP 1 '/u01/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
                        GROUP 2 '/u01/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
                        GROUP 3 '/u01/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
                        -- STANDBY LOGFILE
                        DATAFILE
                        '/u01/oracle/oradata/orcl/system01.dbf',
                        '/u01/oracle/oradata/orcl/sysaux01.dbf',
                        '/u01/oracle/oradata/orcl/undotbs01.dbf',
                        '/u01/oracle/oradata/orcl/users01.dbf',
                        '/u01/oracle/oradata/orcl/example01.dbf'
                        CHARACTER SET WE8MSWIN1252
                        ;


                        <font color=red>--RECOVER DATABASE USING BACKUP CONTROLFILE</font>

                        -- Database can now be opened zeroing the online logs.
                        ALTER DATABASE OPEN RESETLOGS;
                        -- Commands to add tempfiles to temporary tablespaces.
                        -- Online tempfiles have complete space information.
                        -- Other tempfiles may require adjustment.
                        ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/oradata/orcl/temp01.dbf'
                        SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
                        -- End of tempfile additions.
                        --
                        >

                        Edited by: yoonus on Feb 28, 2013 4:57 AM

                        Edited by: yoonus on Feb 28, 2013 5:03 AM

                        Edited by: yoonus on Feb 28, 2013 5:07 AM
                        • 9. Re: took backup of data files and controlfile but not archive file db is 11g xe
                          clcarter
                          i dont have those 2 log files.... in the destination mentioned
                          Doesn't matter, the create controlfile statement specifies redo filenames- if they don't exist, the instance might gripe about it but they will get created when the database starts saving redo information.

                          And if you don't like the names, fix it, i.e.:
                          -- change:
                          ... group 1 '<path>\O1_MF_1_80L7C259_.LOG' size ... ,
                          group 2 '<path>\O1_MF_2_80L7C375_.LOG' size ...
                          -- to
                          'C:\oraclexe\app\oracle\oradata\XE\redo\redo01.dbf' size ... ,
                          'C:\oraclexe\app\oracle\oradata\XE\redo\redo02.dbf' size ... 
                          -- if you want three redo files that's fine too- add <comma> group 3 ...
                          , group 3 'C:\oraclexe\app\oracle\oradata\XE\redo\redo03.dbf' size ... 
                          You'll have to make the 'redo' folder, the complete <path> part has to be a valid directory. The "out of the box" setup in XE places redo files in the flash recovery area. And FRA has an arbitrary size limit, 10GB, I think. Better to point redo files to a different folder, not in the FRA location.

                          Redo files can also have multiple members, for additional protection. If at least one member of a redo group is healthy the database will continue to run. Specify additional members with:
                          group 1 ( '<path1>\redo01.dbf', '<path2>\redo01.dbf' ) size ... ,
                          group 2 ( '<path1>\redo02.dbf', '<path2>\redo02.dbf' ) size ... ,
                          group 3 ( '<path1>\redo03.dbf', '<path2>\redo03.dbf' ) size ... 
                          I always name the redo files with a .dbf extension, at one time there was a system administrator that got a bit too aggressive seeking log files that "needed to be cleaned up to save some space".

                          Good for him, he freed up a few MB of disk space.

                          Bad for me, the instance crashed as soon as it tried writing to a missing redo file.