This discussion is archived
12 Replies Latest reply: Jun 22, 2009 12:37 PM by 445843 RSS

Logical standby in noarchive mode

637598 Newbie
Currently Being Moderated
Hello,

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,

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

    Thanks...
  • 3. Re: Logical standby in noarchive mode
    525507 Journeyer
    Currently Being Moderated
    Logically speaking it is not possible to create logical standby database in no archivelog mode.
  • 4. Re: Logical standby in noarchive mode
    528670 Newbie
    Currently Being Moderated
    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,
    Alex
  • 5. Re: Logical standby in noarchive mode
    Girish Sharma Guru
    Currently Being Moderated
    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 )

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

    Thanks,

    Boris...
  • 7. Re: Logical standby in noarchive mode
    591309 Explorer
    Currently Being Moderated
    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
    637598 Newbie
    Currently Being Moderated
    It is a DB just for reporting purposes not used as switchover and failovers target....
  • 9. Re: Logical standby in noarchive mode
    Girish Sharma Guru
    Currently Being Moderated
    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.

    Regards
    Girish Sharma
  • 10. Re: Logical standby in noarchive mode
    445843 Newbie
    Currently Being Moderated
    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
    
    SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
    
    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.
    
    SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
    
    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;
    
      COUNT(*)
    ----------
         24444
  • 11. Re: Logical standby in noarchive mode
    UweHesse Expert
    Currently Being Moderated
    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
    Uwe
  • 12. Re: Logical standby in noarchive mode
    445843 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points