12 Replies Latest reply: Jun 22, 2009 2:37 PM by 445843 RSS

    Logical standby in noarchive mode


      does anybody know if it is possible to run a logical standby (10gR2) in noarchivelog mode?

      For my understanding it should be possible, why not... But I can't find a piece of documentation which prove this.

      Thanks in advance,

        • 1. Re: Logical standby in noarchive mode
          The best way to is to create it and see if it is really possible.
          • 2. Re: Logical standby in noarchive mode
            Sure, I would also like to test it, but we need a quick decision.

            • 3. Re: Logical standby in noarchive mode
              Logically speaking it is not possible to create logical standby database in no archivelog mode.
              • 4. Re: Logical standby in noarchive mode
                Hi Boris,

                I doubt if that would be possible to create standby in noarchivelog mode. On another hand, you might be successful in making your already working logical standby running in noarchivelog mode. I suspect Oracle will not allow you to alter database anyway but you can try.

                Best Regards,
                • 5. Re: Logical standby in noarchive mode
                  Girish Sharma
                  It is clear mentioned that
                  " The primary database must run in ARCHIVELOG mode. "
                  see ( http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/standby.htm#i72073 )

                  Girish Sharma
                  • 6. Re: Logical standby in noarchive mode
                    Yes I know.. Thats clear, but what is with the logical standby itself?


                    • 7. Re: Logical standby in noarchive mode
                      You can run logical standby database in noarchive log mode.
                      But what will happen during a switch over?
                      • 8. Re: Logical standby in noarchive mode
                        It is a DB just for reporting purposes not used as switchover and failovers target....
                        • 9. Re: Logical standby in noarchive mode
                          Girish Sharma
                          It is NOT possible. See ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5796841276215 )

                          Can logical standby database be in NOARCHIVELOG mode? Thanks.

                          Followup January 19, 2008 - 10pm US/Eastern:
                          would not even begin to even make a tiny bit of sense.

                          failovers are to be the replacement for production.

                          Girish Sharma
                          • 10. Re: Logical standby in noarchive mode
                            I also was curious if setting up a Logical Standby in noarchivelog mode was possible. It looks like it is...

                            We use this simply as a reporting copy of our data. Our analysts do not change any of this data. They just need it to be fairly current (~3 days). At this point, there isn't any need to generate archivelogs so we want to disable the feature to minimize our maintenance and our storage requirements.

                            STANDBY DB:
                            SQL> select log_mode, open_mode from v$database;
                            LOG_MODE     OPEN_MODE
                            ------------ ----------
                            ARCHIVELOG   READ WRITE
                            Database altered.
                            SQL> shutdown immediate;
                            Database closed.
                            Database dismounted.
                            ORACLE instance shut down.
                            SQL> startup mount;
                            ORACLE instance started.
                            Database mounted.
                            SQL>  alter database noarchivelog;
                            Database altered.
                            SQL> alter database open;
                            Database altered.
                            Database altered.
                            Then I created an object on the production server:
                            SQL> create table testuser.noarchivelog_testing as select * from dba_objects;
                            Table created.
                            SQL> alter system switch logfile;
                            System altered.
                            Mon Jun 22 11:13:26 2009
                            Beginning log switch checkpoint up to RBA [0xe6.2.10], SCN: 1031235046
                            Mon Jun 22 11:13:26 2009
                            Now I see it on the standby:
                            Mon Jun 22 11:13:43 2009
                            RFS LogMiner: RFS id [25920] assigned as thread [1] PING handler
                            RFS[1]: Archived Log: '/oracle/LH1/oraarch/LH1arch_1_229_680639127.arc'
                            Mon Jun 22 11:13:46 2009
                            RFS LogMiner: Registered logfile [/oracle/LH1/oraarch/LH1arch_1_229_680639127.arc] to LogMiner session id [1]
                            Mon Jun 22 11:13:48 2009
                            LOGSTDBY status: ORA-16204: DDL successfully applied
                            Mon Jun 22 11:13:51 2009
                            LOGMINER: End mining logfile: /oracle/LH1/oraarch/LH1arch_1_229_680639127.arc
                            SQL> select count(*) from testuser.noarchivelog_testing;
                            • 11. Re: Logical standby in noarchive mode
                              Indeed, this is possible. I have seen customers running in this configuration and I have tested it myself also. Of course, when you create the logical standby, you have to have it in archivemode. But later on you may turn it off.

                              That may be even reasonable, if you intend to use the logical standby for reporting purpose only and not for HA purpose. However, it seems as if that is only possible with archiver transmission from the primary. Else (with LGWR SYNC or ASYNC) the transmission to the logical standby stops.

                              Kind regards
                              • 12. Re: Logical standby in noarchive mode
                                you're absolutely right, thanks. I hadn't noticed that! I should have, since I pasted the section of the alert log that shows that it's using archivelogs :-)

                                I double checked it on my setup and when I created a second test table it has not propagated yet.

                                I even tried to kick the Primary DB in the butt with "alter system set log_archive_dest_state_2 = enable scope=both;" with no luck.