This discussion is archived
10 Replies Latest reply: Nov 14, 2012 3:41 AM by Osama_Mustafa RSS

log_archive_ setting in for streams

AsimDeo Newbie
Currently Being Moderated
Hi All,

Here we have 3 env. Production,DR and Reporting physically on separate machines.
Production and DR are working fine...configured via dataguard.
Reporting instance is synchronized via streams.

At production "archivelog all delete all input" is set in RMAN script.
Befor we added Reporting instance archivelogs were removing from Production after backup is completed.
But since we added Reporing instance, Redo logs are shipped from Production to Reporting (and changes applied) but archivlogs are not deleted after backup is completed at Production.

Below are the parameter details across different environmnets

OS=Windows server 2003 64bit, DB=10.2.0.5.0 64bit

*1=Production*
SQL> show parameter log_arch
NAME                              VALUE
-------------------  ------------------------------------------------------------------------------------------
db_name                 CIBSPROD 
DB_UNIQUE_NAME       CIBSPROD 
log_archive_config       DG_CONFIG=(CIBSPROD,CIBSSTDB,CIBSREP)
log_archive_dest_1     LOCATION=E:\DBFILES\ArchiveLog\CIBSPROD\PrimaryRole VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIBSPROD
log_archive_dest_2     SERVICE=CIBSSTDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CIBSSTDB
log_archive_dest_3     service=CIBSREP arch async noregister valid_for=(online_logfile,all_roles) db_unique_name=CIBSREP
*1=DR (configured via Datagurad*
SQL> show parameter log_arch
NAME                              VALUE
-------------------  ------------------------------------------------------------------------------------------
db_name                    CIBSPROD 
DB_UNIQUE_NAME       CIBSSTDB 
log_archive_config       DG_CONFIG=(CIBSPROD,CIBSSTDB)
log_archive_dest_1     LOCATION=E:\DBFILES\ArchiveLog\CIBSSTDB\StandbyRole VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIBSSTDB
log_archive_dest_2     SERVICE=CIBSPROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CIBSPROD
*1=Report(configured via streams, with noarchivelog at downstream database)*
NAME                              VALUE
-------------------  ------------------------------------------------------------------------------------------
db_name                CIBSREP 
DB_UNIQUE_NAME         CIBSREP 
log_archive_config      receive,dg_config=(CIBSPROD,CIBSSTDB)
log_archive_start          FALSE
log_archive_dest_1         LOCATION=E:\DBFILES\ArchiveLog\CIBSREP\PrimaryRole VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CIBSREP
log_archive_dest_2         LOCATION=E:\DBFILES\ArchiveLog\CIBSREP\StandbyRole VALID_FOR=(STANDBY_LOGFILE, PRIMARY_ROLE) DB_UNIQUE_NAME=CIBSREP

BSSTDB)
Regards

Legend

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