This content has been marked as final. Show 12 replies
The best way to is to create it and see if it is really possible.
Sure, I would also like to test it, but we need a quick decision.
Logically speaking it is not possible to create logical standby database in no archivelog mode.
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.
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 )
Yes I know.. Thats clear, but what is with the logical standby itself?
You can run logical standby database in noarchive log mode.
But what will happen during a switch over?
It is a DB just for reporting purposes not used as switchover and failovers target....
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.
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.
Then I created an object on the production server:
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.
Now I see it on the standby:
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
Mon Jun 22 11:13:43 2009 RFS LogMiner: RFS id  assigned as thread  PING handler RFS: 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  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
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.
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.