Home » Server Options » Data Guard » Data Gaurd (Oracle 10g ver 10.4.0.1.0/Windows Server 2003)
Data Gaurd [message #517778] Thu, 28 July 2011 03:08 Go to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Hello Sir

i am new to oracle dba.Need a help. we configured our live data guard setup, with one primary and one standby server. later i found that archiving from primary to standby is happening very rapidly eg 3-4 KB files in every minute. after deferring the log_archive_dest_state_2 parameter, archiving on primary database happens normally, on every log switch but on enabling the parameter again archiving starts rapidly without even any log switch.

please tell me what can be the cause of it. where i should cheak for the above problem.



i am also sending the some entries from both primary and standby database while the problem was occuring.


Primary
-----------

Sat Jul 23 18:34:56 2011
Thread 1 cannot allocate new log, sequence 3956
Checkpoint not complete
Current log# 1 seq# 3955 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO01.LOG
Current log# 1 seq# 3955 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO01A.LOG
LGWR: Standby redo logfile selected for thread 1 sequence 3956 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3956
Current log# 2 seq# 3956 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO02.LOG
Current log# 2 seq# 3956 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO02A.LOG
Sat Jul 23 18:35:55 2011
LGWR: Standby redo logfile selected for thread 1 sequence 3957 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3957
Current log# 3 seq# 3957 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO03.LOG
Current log# 3 seq# 3957 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO03A.LOG
Sat Jul 23 18:36:56 2011
Thread 1 cannot allocate new log, sequence 3958
Checkpoint not complete
Current log# 3 seq# 3957 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO03.LOG
Current log# 3 seq# 3957 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO03A.LOG
LGWR: Standby redo logfile selected for thread 1 sequence 3958 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3958
Current log# 1 seq# 3958 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO01.LOG
Current log# 1 seq# 3958 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO01A.LOG
Sat Jul 23 18:37:58 2011
Thread 1 cannot allocate new log, sequence 3959
Checkpoint not complete
Current log# 1 seq# 3958 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO01.LOG
Current log# 1 seq# 3958 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO01A.LOG
LGWR: Standby redo logfile selected for thread 1 sequence 3959 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 3959
Current log# 2 seq# 3959 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\DPAMPL\REDO02.LOG
Current log# 2 seq# 3959 mem# 1: G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO02A.LOG
Sat Jul 23 18:38:56 2011
Thread 1 cannot allocate new log, sequence 3960
Checkpoint not complete

Standby
---------
Sat Jul 23 18:35:00 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 4: 'G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO04.LOG'
Sat Jul 23 18:35:04 2011
Media Recovery Log F:\ARC\ARC03955_0706188602.001
Media Recovery Waiting for thread 1 sequence 3956 (in transit)
Sat Jul 23 18:35:55 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 5: 'G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO06.LOG'
Sat Jul 23 18:36:00 2011
Media Recovery Log F:\ARC\ARC03956_0706188602.001
Media Recovery Waiting for thread 1 sequence 3957 (in transit)
Sat Jul 23 18:37:00 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 4: 'G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO04.LOG'
Sat Jul 23 18:37:01 2011
Media Recovery Log F:\ARC\ARC03957_0706188602.001
Media Recovery Waiting for thread 1 sequence 3958 (in transit)
Sat Jul 23 18:38:01 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 5: 'G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO06.LOG'
Sat Jul 23 18:38:07 2011
Media Recovery Log F:\ARC\ARC03958_0706188602.001
Media Recovery Waiting for thread 1 sequence 3959 (in transit)
Sat Jul 23 18:39:00 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 4: 'G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO04.LOG'
Sat Jul 23 18:39:02 2011
Media Recovery Log F:\ARC\ARC03959_0706188602.001
Media Recovery Waiting for thread 1 sequence 3960 (in transit)
Sat Jul 23 18:39:58 2011
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 5: 'G:\ORACLE\PRODUCT\10.2.0\REDOLOG\REDO06.LOG'
Sat Jul 23 18:40:03 2011
Media Recovery Log F:\ARC\ARC03960_0706188602.001
Media Recovery Waiting for thread 1 sequence 3961 (in transit)
gxeon
Member

Posts: 32
Joined: Tue Jan 11, 2011 11:58 am
Top
Re: Data Gaurd [message #517782 is a reply to message #517778] Thu, 28 July 2011 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version? (10.4.0.1.0 does not exist)
What are your archive parameters?
Why didn't you post this in Data Guard forum instead of General one?

Regards
Michel
Re: Data Gaurd [message #517784 is a reply to message #517782] Thu, 28 July 2011 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
"Thread 1 cannot allocate new log, sequence 3958
Checkpoint not complete"
Your logs are too small and/or too few for your workload and/or you dbwr cannot work fast enough to write down the modifications onto the disks.

Regards
Michel
Re: Data Gaurd [message #517881 is a reply to message #517784] Fri, 29 July 2011 02:46 Go to previous messageGo to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Sorry for the typing mistake sir , my version is 10.2.0.1.0.

Re: Data Gaurd [message #517886 is a reply to message #517881] Fri, 29 July 2011 03:23 Go to previous messageGo to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Sir here are all the parameters specified in both primary and standby database.

Primary
========
dpampl.__db_cache_size=1333788672
dpampl.__java_pool_size=16777216
dpampl.__large_pool_size=8388608
dpampl.__shared_pool_size=226492416
dpampl.__streams_pool_size=16777216
*._kgl_large_heap_warning_threshold=8388608
*.audit_file_dest='G:\oracle\product\10.2.0/admin/dpampl/adump'
*.background_dump_dest='G:\oracle\product\10.2.0/admin/dpampl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='e:\oracle\product\10.2.0\oradata\dpampl\control01.ctl','e:\oracle\product\10.2.0\oradata\dpampl\control02.ctl','e:\o racle\product\10.2.0\oradata\dpampl\control03.ctl'
*.core_dump_dest='G:\oracle\product\10.2.0/admin/dpampl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dpampl'
*.db_recovery_file_dest='G:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=37580963840
*.DB_UNIQUE_NAME='DPAMPL'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dpamplXDB)'
*.FAL_CLIENT='DPAMPL'
*.FAL_SERVER='STBYDR'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DPAMPL,STBYDR)'
*.log_archive_dest_1='location=F:\Archivefile VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=DPAMPL'
*.LOG_ARCHIVE_DEST_2='SERVICE=STBYDR LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STBYDR'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.open_cursors=300
*.pga_aggregate_target=629145600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=1073741824
*.sga_target=1073741824
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='G:\oracle\product\10.2.0/admin/dpampl/udump'
*.db_file_name_convert='E:\oracle\product\10.2.0\oradata\dpampl\','E:\oracle\product\10.2.0\oradata\dpampl\',
*.log_file_name_convert='E:\oracle\product\10.2.0\oradata\dpampl\','E:\oracle\product\10.2.0\oradata\dpampl',



Standby
========
*.DB_UNIQUE_NAME='STBYDR'
*.audit_file_dest='G:\oracle\product\10.2.0/admin/dpampl/adump'
*.background_dump_dest='G:\oracle\product\10.2.0/admin/dpampl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='e:\oracle\product\10.2.0\oradata\dpampl\stcontrol01.ctl','e:\oracle\product\10.2.0\oradata\dpampl\stcontrol02.ctl',' e:\oracle\product\10.2.0\oradata\dpampl\stcontrol03.ctl'
*.core_dump_dest='G:\oracle\product\10.2.0/admin/dpampl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dpampl'
*.db_recovery_file_dest='G:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=37580963840
*.DB_UNIQUE_NAME='DPAMPL'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dpamplXDB)'
*.FAL_CLIENT='STBYDR'
*.FAL_SERVER='DPAMPL'
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(DPAMPL,STBYDR)'
*.log_archive_dest_1='location=F:\arc\
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STBYDR'
LOG_ARCHIVE_DEST_2=
'SERVICE=DPAMPL LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=DPAMPL'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.open_cursors=300
*.pga_aggregate_target=629145600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=1073741824
*.sga_target=1073741824
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='G:\oracle\product\10.2.0/admin/dpampl/udump'
*.db_file_name_convert='E:\oracle\product\10.2.0\oradata\dpampl\','E:\oracle\product\10.2.0\oradata\dpampl\',
*.log_file_name_convert='E:\oracle\product\10.2.0\oradata\dpampl\','E:\oracle\product\10.2.0\oradata\dpampl',



Re: Data Gaurd [message #517888 is a reply to message #517886] Fri, 29 July 2011 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you specified "LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)" it is the expected behaviour.

Your main problem is the one I mentioned on primary.

Regards
Michel

Re: Data Gaurd [message #518049 is a reply to message #517888] Mon, 01 August 2011 01:47 Go to previous messageGo to next message
gxeon
Messages: 53
Registered: January 2011
Location: Mumbai
Member
Hello Sir

can you please tell me where exactly i need to make changes in parameters to stop rapid archiving on standby. what exactly be the reason for this ?

GG
Re: Data Gaurd [message #518054 is a reply to message #518049] Mon, 01 August 2011 02:04 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The reason is you choose LGWR to synchronize the archive log.
Use ARCH and it will do it at the same time than the primary.

Regards
Michel

[Updated on: Mon, 01 August 2011 02:05]

Report message to a moderator

Previous Topic: Standby using RMAN
Next Topic: Confirm the Archive log applying
Goto Forum:
  


Current Time: Thu Mar 28 05:13:32 CDT 2024