Re: Logical Standby Database Oracle 9.2 LGWR redo shipping

parag9i_at_gmail.com wrote:
> Hi,
>
> I am able to setup logical standby db on same server. My settings are
> on primary database "infod" are
>
> LOG_ARCHIVE_START = TRUE
> LOG_ARCHIVE_DEST_1 = 'LOCATION=/apps/oradbf/infod/archive MANDATORY'
> LOG_ARCHIVE_DEST_STATE_1 = ENABLE
> LOG_ARCHIVE_DEST_2 = 'SERVICE=standby LGWR ASYNC'
> LOG_ARCHIVE_DEST_STATE_2 = ENABLE
> LOG_ARCHIVE_FORMAT = infod_log%s_%t.arc
> LOG_PARALLELISM = 1
>
> On Standby db "standby" are
>
> db_name = standby
> log_archive_start = TRUE
> log_archive_dest_1 = 'LOCATION=/apps/oradbf/standby/archive'
> log_archive_dest_state_1 = ENABLE
> log_archive_format = infod_log%s_%t.arc
> log_parallelism = 1
> resource_manager_plan = system_plan
> standby_archive_dest = '/apps/oradbf/standby/primary'
> parallel_max_servers = 9
> instance_name = standby
> lock_name_space = standby
>
> Though I have specified redo changes to be applied on standby thru
> LGWR, it is getting only applied when I do switch logfile (may be only
> arch process getting setup thoguh I have setup LGWR). I also observed
> that following many files created in my primary and standby db
> "infod"/standby in bdump directory are
>
> -rw-r----- 1 oracle dba 813 Aug 29 11:01 infod_dbw0_32150.trc
> -rw-r----- 1 oracle dba 2083 Aug 29 11:25 infod_arc1_59490.trc
> -rw-r----- 1 oracle dba 1305 Aug 29 11:29 infod_lns0_16042.trc
> -rw-r----- 1 oracle dba 2093 Aug 29 11:29 infod_lgwr_60930.trc
> -rw-r----- 1 oracle dba 785 Aug 29 11:30 infod_dbw0_60932.trc
> -rw-r----- 1 oracle dba 1111 Aug 29 11:40 infod_lns0_62112.trc
> -rw-r----- 1 oracle dba 841 Aug 29 11:46 infod_arc1_16044.trc
> -rw-r----- 1 oracle dba 794 Aug 29 11:46 infod_arc0_29270.trc
> -rw-r----- 1 oracle dba 1267 Aug 29 12:36 infod_lns0_73020.trc
> -rw-r----- 1 oracle dba 4936 Aug 29 12:36 infod_lgwr_72932.trc
> -rw-r----- 1 oracle dba 813 Aug 29 12:54 infod_dbw0_72934.trc
> -rw-r----- 1 oracle dba 1037 Aug 29 13:00 infod_lns1_77998.trc
> -rw-r----- 1 oracle dba 1266 Aug 29 13:00 infod_lns0_29292.trc
> -rw-r----- 1 oracle dba 3661 Aug 29 13:27 infod_lgwr_60934.trc
> -rw-r--r-- 1 oracle dba 23570 Aug 29 13:27 alert_infod.log
>
>
> -rw-r----- 1 oracle dba 1373 Aug 29 11:09 standby_dbw0_69678.trc
> -rw-r----- 1 oracle dba 605 Aug 29 11:09 standby_lgwr_68914.trc
> -rw-r----- 1 oracle dba 633 Aug 29 11:12 standby_lgwr_69684.trc
> -rw-r----- 1 oracle dba 633 Aug 29 11:15 standby_lgwr_69274.trc
> -rw-r----- 1 oracle dba 559 Aug 29 11:21 standby_p008_48902.trc
> -rw-r----- 1 oracle dba 559 Aug 29 11:21 standby_p007_28952.trc
> -rw-r----- 1 oracle dba 559 Aug 29 11:21 standby_p006_17538.trc
> -rw-r----- 1 oracle dba 559 Aug 29 11:21 standby_p005_67660.trc
> -rw-r----- 1 oracle dba 559 Aug 29 11:21 standby_p004_19526.trc
> -rw-r----- 1 oracle dba 559 Aug 29 11:21 standby_p003_62620.trc
> -rw-r----- 1 oracle dba 733 Aug 29 11:21 standby_p001_68372.trc
> -rw-r----- 1 oracle dba 4173 Aug 29 11:21 standby_lsp0_62078.trc
> -rw-r----- 1 oracle dba 605 Aug 29 11:29 standby_lgwr_59784.trc
> -rw-r----- 1 oracle dba 605 Aug 29 11:40 standby_lgwr_33178.trc
> -rw-r--r-- 1 oracle dba 35521 Aug 29 13:27 alert_standby.log
>
> Last few lines of alert of standby db are
>
> Mon Aug 29 11:44:34 2005
> LOGSTDBY event: ORA-16205: DDL skipped due to skip setting
> LOGSTDBY stmt: ALTER DATABASE OPEN
> Mon Aug 29 12:36:14 2005
> RFS: Possible network disconnect with primary database
> Mon Aug 29 13:00:02 2005
> LOGSTDBY event: ORA-16205: DDL skipped due to skip setting
> LOGSTDBY stmt: alter database open
> LOGSTDBY event: ORA-16204: DDL successfully applied
> LOGSTDBY stmt: create table t3 as select * from dba_objects
> Mon Aug 29 13:27:07 2005
> LOGSTDBY event: ORA-16204: DDL successfully applied
> LOGSTDBY stmt: drop table t3
> (TESTINFO-oracle)/apps/oracle/admin/standby/bdump
>
> Kindly help me urgently by telling why redo changes are not getting
> applied thru LGWR async mode !!!!
>
> regards & thanks
> pjp
>

Read the following and determine whether you have applied the correct ALTER DATABASE clause(s):

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_15a.htm#2100911

What is your version of Oracle? (Clearly I'm making an assumption about that in the above link, based on your log file format template.) Then carefully examine the relevant V$DATABASE columns, such as DATAGUARD_BROKER, PROTECTION_MODE, and PROTECTION_LEVEL (there are plenty more). Can you be more specific about the problem, version, platform, what you have tried, and what you expected?

There are many informational items missing from your message. What is the larger goal? Are you trying to implement "disaster recovery" or something else? Have you found anything of interest in the trace files?   Having an explicit Resource Manager plan on the standby is not something I have tried before, do you really need it?

Finally, try listing your files from newest to oldest ("ls -alt" in Unix). Ninety-eight times out of one hundred, the most interesting files in any folder will be the newest ones, ordered by date descending. ;-)

-Mark Bole Received on Mon Aug 29 2005 - 20:56:03 CDT