Database Software

Hi everybody.

First time with Data Guard so be patient :-)

Oracle is Oracle Database 11g Enterprise Edition 11.2.0.3.0 64bit Production running on a couple of CentOS 6.2 Linux boxes (test environment).

I've successfully managed to confugure a primary/standby Data Guard setup in the sense that if I insert some records into the primary __AND__ issue the "alter system switch logfile;", by watching the log files I'm able to see the sync stuff:

[primary]
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION
Thread 1 advanced to log sequence 295 (LGWR switch)
Current log# 1 seq# 295 mem# 0: /oracle/origlog/redo01.log
Current log# 1 seq# 295 mem# 1: /oracle/mirrlog/redo01.log
Wed Sep 26 14:23:03 2012
Archived Log entry 485 added for thread 1 sequence 294 ID 0xb91f1325 dest 1:

[standby]
Wed Sep 26 14:22:59 2012
RFS[4]: Assigned to RFS process 18244
RFS[4]: Opened log for thread 1 sequence 292 dbid -1189156315 branch 794247464
Archived Log entry 63 added for thread 1 sequence 292 rlc 794247464 ID 0xb91f1325 dest 2:
Wed Sep 26 14:22:59 2012
RFS[5]: Assigned to RFS process 18246
RFS[5]: Opened log for thread 1 sequence 293 dbid -1189156315 branch 794247464
Archived Log entry 64 added for thread 1 sequence 293 rlc 794247464 ID 0xb91f1325 dest 2:
Wed Sep 26 14:22:59 2012
Media Recovery Log /oracle/oraarch/log1_292_794247464.arc
Media Recovery Log /oracle/oraarch/log1_293_794247464.arc
Media Recovery Waiting for thread 1 sequence 294
Wed Sep 26 14:23:02 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[6]: Assigned to RFS process 18250
RFS[6]: No standby redo logfiles created for thread 1
RFS[6]: Opened log for thread 1 sequence 295 dbid -1189156315 branch 794247464
Wed Sep 26 14:23:04 2012
RFS[7]: Assigned to RFS process 18257
RFS[7]: Opened log for thread 1 sequence 294 dbid -1189156315 branch 794247464
Archived Log entry 65 added for thread 1 sequence 294 rlc 794247464 ID 0xb91f1325 dest 2:
Media Recovery Log /oracle/oraarch/log1_294_794247464.arc
Media Recovery Waiting for thread 1 sequence 295 (in transit)

Also, if I put the standby in READ-ONLY mode and perform a simple query, data is consistent.

I might be happy but I've still some questions for the group:

1) Why does the "No standby redo logfiles created for thread 1" appear? On the STANDBY server:

SQL> select thread#,group#, status from v$standby_log;

THREAD# GROUP# STATUS
---------- ---------- ----------
+ 0 4 UNASSIGNED+
+ 0 5 UNASSIGNED+
+ 0 6 UNASSIGNED+
+ 0 7 UNASSIGNED+

I added them using the "ALTER DATABASE ADD STANDBY LOGFILE..." command but why are they still "UNASSIGNED"?

2) I checked the PRIMARY's ONLINE REDO LOG FILES and they're updated consistently; if I check (ls -l) both the STANDBY's REDO LOG and STANDBY LOGS, they do not get updated. Is it normal?

3) From what I've gathered from the manuals, replication should occur either when the archive_lag_target (mine is set to 1800) expires, the DB ADMIN issues a "alter system switch logfile;" command or when the system does archive an old REDO LOG. Is there a way to have (near) REAL TIME REPLICATION as to have every single rows transferred and applied to the STANDBY as long as it gets interted into the PRIMARY?

4) Again, from what I've learned, the PRIMARY transmits the REDO log to the STANDBY; it should go first into the "STANDBY REDO LOGs" then, once the STANDBY decides to ARCHIVE it, it should be applied to the datafiles. Assuming you're running in "REAL TIME APPLY SERVICE", is it normal for the STANDBY to completely bypass the ONLINE REDO/STANDBY REDO files and write directly to the LOG_ARCHIVE_DEST_1?

Here's the PFILE for the PRIMARY SERVER:

RZSID.__db_cache_size=117440512
RZSID.__java_pool_size=4194304
RZSID.__large_pool_size=4194304
RZSID.__oracle_base='/rnd/apps/servers/oracle'#ORACLE_BASE set from environment
RZSID.__pga_aggregate_target=192937984
RZSID.__sga_target=343932928
RZSID.__shared_io_pool_size=0
RZSID.__shared_pool_size=205520896
RZSID.__streams_pool_size=4194304
*.archive_lag_target=1800
*.audit_file_dest='/rnd/apps/servers/oracle/admin/RZPRIMARY/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/rnd/apps/servers/oracle/oradata/RZPRIMARY/control01.ctl','/rnd/apps/servers/oracle/bkp_control/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/rnd/apps/servers/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=9663676416
*.diagnostic_dest='/rnd/apps/servers/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RZSIDXDB)'
*.memory_target=536870912
*.open_cursors=300
*.processes=150
*.undo_tablespace='UNDOTBS1'
DB_NAME='RZPRIMAR'
DB_UNIQUE_NAME='RZPRIMARY'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(RZPRIMARY,RZBACKUP)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/oracle/oraarch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=RZPRIMARY'
LOG_ARCHIVE_DEST_2=
'SERVICE=RZBACKUP ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=RZBACKUP'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
remote_login_passwordfile='EXCLUSIVE'
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=RZBACKUP
STANDBY_FILE_MANAGEMENT=AUTO
DB_FILE_NAME_CONVERT='RZPRIMARY','RZBACKUP'

Here's the STANDBY one:

RZSID.__db_cache_size=92274688
RZSID.__java_pool_size=4194304
RZSID.__large_pool_size=4194304
RZSID.__oracle_base='/rnd/apps/servers/oracle'#ORACLE_BASE set from environment
RZSID.__pga_aggregate_target=209715200
RZSID.__sga_target=327155712
RZSID.__shared_io_pool_size=0
RZSID.__shared_pool_size=213909504
RZSID.__streams_pool_size=4194304
*.archive_lag_target=1800
*.audit_file_dest='/rnd/apps/servers/oracle/admin/RZBACKUP/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.CONTROL_FILES='/rnd/apps/servers/oracle/oradata/RZBACKUP/control01.ctl','/rnd/apps/servers/oracle/bkp_control/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.DB_FILE_NAME_CONVERT='RZPRIMARY','RZBACKUP'
*.DB_NAME='RZPRIMAR'
*.db_recovery_file_dest='/rnd/apps/servers/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=9663676416
*.DB_UNIQUE_NAME='RZBACKUP'
*.diagnostic_dest='/rnd/apps/servers/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RZSIDXDB)'
*.FAL_SERVER='RZPRIMARY'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(RZPRIMARY,RZBACKUP)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/oraarch/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=RZBACKUP'
*.LOG_ARCHIVE_DEST_2='SERVICE=RZPRIMARY ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=RZPRIMARY'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.memory_target=536870912
*.open_cursors=300
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'

If you need more info, feel free to ask.

Thanks,
Rob