6 Replies Latest reply: Apr 23, 2014 7:16 PM by PhilHerring RSS

    Oracle 11g R2 Standard Edition Replication Methods

    bd53c94d-7012-4612-923f-0ba436478e9b

      Hi All,

       

      Currently we have Oracle 11g R2 SE RAC Env,Now we would like to implement any disaster recovery server But we can not use Data Guard or Streams

       

      and we are not suppose to go for any third party tools(DBVisit).

       

      Can you please guide me to do manual Replication through transferring Archive logs

       

      Thanks,

        • 1. Re: Oracle 11g R2 Standard Edition Replication Methods
          Renu-Oracle

          Hi,

           

           

           

          Now database can be recovered, at this step Oracle will prompt for the archive logs copied. When all the archive logs are applied, type CANCEL

           

           

           

           

           

           

          ex:

          SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

           

           

          ORA-00279: change 9603 generated at 01/12/2013 20:40:43 needed for thread 1

          ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_9_675981354.arc

          ORA-00280: change 9603 for thread 1 is in sequence #9

           

           

          21:07:48 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

           

          /u03/oradata/DEV/arch/DEV_1_9_675981354.arc

          ORA-00279: change 9614 generated at 01/12/2013 20:40:56 needed for thread 1

          ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_10_675981354.arc

          ORA-00280: change 9614 for thread 1 is in sequence #10

          ORA-00278: log file ‘/u03/oradata/DEV/arch/DEV_1_9_675981354.arc’ no longer needed for this recovery

           

           

          ……..

           

           

          21:11:25 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

          CANCEL

           

           

          Media recovery cancelled.

           

           

          Or (for point in time recovery)

          SQL> RECOVER DATABASE UNTIL TIME ’2013-01-11:15:14:30′ USING BACKUP CONTROLFILE;

          Media recovery complete

           

           

           

           

           

           

          Thanks,

           

           

          Renu

          • 2. Re: Oracle 11g R2 Standard Edition Replication Methods
            bd53c94d-7012-4612-923f-0ba436478e9b

            Thanks for quick Prompt,

             

            i am trying to do is below

             

            Source Environment:Oracle 11g Standard Edition RAC

            Destination Environment:Oracle 11g Standard Edition

             

            1)Restore RMAN backup from Source to Destination with standby controlfile

            2)and opened destination database with resetlogs option

            3)and kept destination database as mount mode.

            4)Now i am transferring latest generated archivelog files from Source to Destination(ASM to Remote ASM)

            5) As you suggested below command getting error

            SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

            ORA-00283: recovery session canceled due to errors

            ORA-01666: control file is for a standby database

             

            Thanks

            • 3. Re: Oracle 11g R2 Standard Edition Replication Methods
              Renu-Oracle

              Hi,

               

              Try below:

               

              sqlplus / as sysdba

              recover standby database ;

               

              Thanks,

              Renu

              • 4. Re: Oracle 11g R2 Standard Edition Replication Methods
                PhilHerring

                Don't open the standby. Opening stops it being a standby database. The only exception is opening it in read-only mode, which you can do.

                 

                This is the procedure I use to build a new standby:

                 

                1. Once the DB files are copied, STARTUP MOUNT the DB and use CREATE STANDBY CONTROLFILE AS '/somepath/somefilename.ctl'; to create a standby controlfile.

                2. Shut down the DB and modify the init.ora to use '/somepath/somefilename.ctl' as the only DB controlfile.

                3. STARTUP NOMOUNT the DB, then execute ALTER DATABASE MOUNT STANDBY DATABASE;

                4. At this stage, you can rename your data files, if their location is different to the production DB.

                5. Run RECOVER STANDBY DATABASE UNTIL CANCEL;

                • 5. Re: Oracle 11g R2 Standard Edition Replication Methods
                  bd53c94d-7012-4612-923f-0ba436478e9b

                  Hi,

                   

                  i have followed as suggested above,getting below error.Can you please check

                   

                  SQL> RECOVER STANDBY DATABASE UNTIL CANCEL;

                  ORA-00279: change 50959805 generated at 04/22/2014 14:51:49 needed for thread 2

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_2_seq_167

                  ORA-00280: change 50959805 for thread 2 is in sequence #167

                  Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

                  AUTO

                  ORA-00279: change 50959805 generated at 04/22/2014 14:45:32 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_199

                  ORA-00280: change 50959805 for thread 1 is in sequence #199

                  ORA-00279: change 50959813 generated at 04/22/2014 14:51:52 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_200

                  ORA-00280: change 50959813 for thread 1 is in sequence #200

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_199' no

                  longer needed for this recovery

                  ORA-00279: change 51070290 generated at 04/22/2014 22:01:54 needed for thread 2

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_2_seq_168

                  ORA-00280: change 51070290 for thread 2 is in sequence #168

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_2_seq_167' no

                  longer needed for this recovery

                  ORA-00279: change 51103019 generated at 04/22/2014 22:54:59 needed for thread 2

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_2_seq_169

                  ORA-00280: change 51103019 for thread 2 is in sequence #169

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_2_seq_168' no

                  longer needed for this recovery

                  ORA-00279: change 51120665 generated at 04/23/2014 00:00:11 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_201

                  ORA-00280: change 51120665 for thread 1 is in sequence #201

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_200' no

                  longer needed for this recovery

                  ORA-00279: change 51142905 generated at 04/23/2014 00:07:44 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_202

                  ORA-00280: change 51142905 for thread 1 is in sequence #202

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_201' no

                  longer needed for this recovery

                  ORA-00279: change 51149560 generated at 04/23/2014 00:08:00 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_203

                  ORA-00280: change 51149560 for thread 1 is in sequence #203

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_202' no

                  longer needed for this recovery

                  ORA-00279: change 51149635 generated at 04/23/2014 00:08:01 needed for thread 2

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_2_seq_170

                  ORA-00280: change 51149635 for thread 2 is in sequence #170

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_2_seq_169' no

                  longer needed for this recovery

                  ORA-00279: change 51223062 generated at 04/23/2014 04:30:33 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_204

                  ORA-00280: change 51223062 for thread 1 is in sequence #204

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_203' no

                  longer needed for this recovery

                  ORA-00279: change 51283721 generated at 04/23/2014 09:00:57 needed for thread 2

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_2_seq_171

                  ORA-00280: change 51283721 for thread 2 is in sequence #171

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_2_seq_170' no

                  longer needed for this recovery

                  ORA-00279: change 51368666 generated at 04/23/2014 15:30:37 needed for thread 1

                  ORA-00289: suggestion : +FRA/testdb/archivelog/2014_04_23/thread_1_seq_205

                  ORA-00280: change 51368666 for thread 1 is in sequence #205

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_204' no

                  longer needed for this recovery

                  ORA-00279: change 51380655 generated at 04/23/2014 16:24:20 needed for thread 1

                  ORA-00289: suggestion : +FRA

                  ORA-00280: change 51380655 for thread 1 is in sequence #206

                  ORA-00278: log file '+FRA/testdb/archivelog/2014_04_23/thread_1_seq_205' no

                  longer needed for this recovery

                   

                   

                  ORA-00308: cannot open archived log '+FRA'

                  ORA-17503: ksfdopn:2 Failed to open file +FRA

                  ORA-15045: ASM file name '+FRA' is not in reference form

                   

                   

                  Thanks,

                  • 6. Re: Oracle 11g R2 Standard Edition Replication Methods
                    PhilHerring

                    That means it worked.

                     

                    "ORA-00308: cannot open archived log" indicates that all available redo logs have been applied to the standby. Redo apply then stops.

                     

                    Since this is a user-managed standby, it's up to you to handle this. Typically you'll have a scheduled (cron) job to apply the redo at regular intervals. Your business requirements will dictate how often this has to be. It might be once a day, once a minute, or somewhere between.

                     

                    If you're on Unix, this shell script can be used as a starting point:

                     

                    ORACLE_BASE=/opt/oracle

                    ORACLE_HOME=/opt/oracle/product/11.2.0.3

                    PATH=$ORACLE_HOME/bin:$PATH

                    ORACLE_SID=STANDBY

                    ORAENV_ASK=NO

                    . $ORACLE_HOME/bin/oraenv

                    export ORACLE_SID ORAENV_ASK

                    sqlplus / as sysdba @apply_redo.sql

                     

                     

                    The file apply_redo.sql will look something like this:

                     

                    startup nomount pfile=/somepath/admin/initSTANDBY.ora

                    alter database mount standby database;

                    recover standby database until cancel;

                    auto

                    shutdown

                    exit