10 Replies Latest reply: Nov 14, 2012 5:41 AM by Osama_Mustafa RSS

    log_archive_ setting in for streams

    AsimDeo
      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