5 Replies Latest reply: Apr 15, 2013 2:16 AM by Mahir M. Quluzade RSS

    Standby files

    Thunder2777
      Hi Ther e

      while configuring Data Guard, when I edited and Add parameters in Standby parameter file e.g. pfilestldb.ora (stldb is for Standby DB).
      After that when I execute query for log files, it displays following results.

      SQL> select group#,type,member from v$logfile;


      GROUP# TYPE
      ---------- -------
      MEMBER
      --------------------------------------------------------------------------------
      3 ONLINE
      C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

      2 ONLINE
      C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

      1 ONLINE
      C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG


      GROUP# TYPE
      ---------- -------
      MEMBER
      --------------------------------------------------------------------------------
      4 STANDBY
      C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_4_8ORY1L89_.LO
      G

      5 STANDBY
      C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_5_8ORY2JRW_.LO
      G

      6 STANDBY

      GROUP# TYPE
      ---------- -------
      MEMBER
      --------------------------------------------------------------------------------
      C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_6_8ORY2ZRF_.LO
      G


      6 rows selected.

      Although I have not yet Added Standby files as per procedure.

      My pfilestldb.ora is as follow

      STLDB.__db_cache_size=197132288
      STLDB.__java_pool_size=4194304
      STLDB.__large_pool_size=4194304
      STLDB.__shared_pool_size=75497472
      STLDB.__streams_pool_size=0
      *.audit_file_dest='C:\oracle\product\10.2.0\admin\STLDB\adump'
      *.background_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\bdump'
      *.compatible='10.2.0.1.0'
      *.control_files='D:\ORACLE\ORADATA\STLDB\CONTROLFILE\CONTROL01.CTL'
      *.core_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\cdump'
      *.db_block_size=8192
      *.db_domain=''
      *.db_file_multiblock_read_count=16
      *.db_name='ORCL'
      *.db_unique_name='STLDB'
      *.db_recovery_file_dest='D:\oracle\FRA\'
      *.db_recovery_file_dest_size=2147483648
      *.dispatchers='(PROTOCOL=TCP) (SERVICE=STLDBXDB)'
      *.job_queue_processes=10
      *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,STLDB)'
      *.LOG_ARCHIVE_DEST_1=
      'LOCATION=D:\ORACLE\FRA\STLDB\ARCHIVELOG\
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
      DB_UNIQUE_NAME=STLDB'
      *.LOG_ARCHIVE_DEST_2=
      'SERVICE=ORCL LGWR ASYNC
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=ORCL'
      *.lOG_ARCHIVE_DEST_STATE_1='ENABLE'
      *.lOG_ARCHIVE_DEST_STATE_2='ENABLE'
      *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
      *.LOG_ARCHIVE_MAX_PROCESSES=30
      *.FAL_SERVER='ORCL'
      *.FAL_CLIENT='STLDB'
      *.DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\orcl\','D:\ORACLE\ORADATA\STLDB\'
      *.LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ONLINELOG\','D:\ORACLE\FRA\STLDB\ONLINELOG\'
      *.open_cursors=300
      *.pga_aggregate_target=95420416
      *.processes=150
      *.remote_login_passwordfile='EXCLUSIVE'
      *.sga_target=287309824
      *.undo_management='AUTO'
      *.undo_tablespace='UNDOTBS1'
      *.user_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\udump'

      Any advice

      Regards

      Thunder2777
        • 1. Re: Standby files
          mseberg
          Hello;

          Here are my comments
          *.compatible='10.2.0.1.0'   ( OK as long as it matches Primary side )
          *.control_files='D:\ORACLE\ORADATA\STLDB\CONTROLFILE\CONTROL01.CTL'  ( Would still multiplex control files )
          *.core_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\cdump'
          *.db_block_size=8192
          *.db_name='ORCL'  ( OK )
          *.db_unique_name='STLDB' ( OK )
          *.db_recovery_file_dest='D:\oracle\FRA\'   ( Odd this set but not used in LOG_ARCHIVE_DEST_1 below )
          *.db_recovery_file_dest_size=2147483648
          
          *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,STLDB)'  ( OK )
          *.LOG_ARCHIVE_DEST_1= 'LOCATION=D:\ORACLE\FRA\STLDB\ARCHIVELOG\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STLDB' ( would change )
          
          LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STLDB'
          
          
          
          *.LOG_ARCHIVE_DEST_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'
          *.lOG_ARCHIVE_DEST_STATE_1='ENABLE'
          *.lOG_ARCHIVE_DEST_STATE_2='ENABLE' ( Can be set to DEFER in Standby mode )
          *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
          *.LOG_ARCHIVE_MAX_PROCESSES=30    ( would set lower 10 and 12 and only raise if issue )
          *.FAL_SERVER='ORCL'  ( OK )
          *.FAL_CLIENT='STLDB' ( OK )
          *.DB_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\oradata\orcl\','D:\ORACLE\ORADATA\STLDB\'
          *.LOG_FILE_NAME_CONVERT='C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ONLINELOG\','D:\ORACLE\FRA\STLDB\ONLINELOG\'
          *.remote_login_passwordfile='EXCLUSIVE' ( OK )
          And while you can run with SRL, it is not a good idea. Make sure you create them.

          SQL>select * from v$standby_log;

          Best Regards

          mseberg
          • 2. Re: Standby files
            Thunder2777
            Thanks MSBERG

            kindly Check the changes Now as follow

            STLDB.__db_cache_size=922746880
            STLDB.__java_pool_size=16777216
            STLDB.__large_pool_size=16777216
            STLDB.__shared_pool_size=285212672
            STLDB.__streams_pool_size=0
            *.audit_file_dest='E:\oracle\product\10.2.0\admin\STLDB\adump'
            *.background_dump_dest='E:\oracle\product\10.2.0\admin\STLDB\bdump'
            *.compatible='10.2.0.3.0'
            *.control_files='D:\ORACLE\ORADATA\STLDB\CONTROLFILE\CONTROL01.CTL',D:\ORACLE\ORADATA\STLDB\CONTROLFILE\CONTROL02.CTL'
            *.core_dump_dest='E:\oracle\product\10.2.0\admin\STLDB\cdump'
            *.db_block_size=8192
            *.db_domain=''
            *.db_file_multiblock_read_count=16
            *.DB_FILE_NAME_CONVERT='PRMDB','STLDB'
            *.db_name='PRMDB'

            *.db_recovery_file_dest='D:\ORACLE\FRA\STLDB\ARCHIVELOG\'
            *.db_recovery_file_dest_size=2147483648
            *.db_unique_name='STLDB'
            *.DG_BROKER_START=TRUE
            *.dispatchers='(PROTOCOL=TCP) (SERVICE=STLDBXDB)'
            *.FAL_CLIENT='STLDB'
            *.FAL_SERVER='PRMDB'
            *.STANDBY_FILE_MANAGEMENT='AUTO'
            *.job_queue_processes=10
            *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRMDB,STLDB)'

            *.LOG_ARCHIVE_DEST_1='LOCATION=D:\ORACLE\FRA\STLDB\ARCHIVELOG\
            VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
            DB_UNIQUE_NAME=STLDB'

            *.LOG_ARCHIVE_DEST_2='SERVICE=PRMDB LGWR ASYNC
            VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
            DB_UNIQUE_NAME=PRMDB'

            *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
            *.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
            *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
            *.LOG_ARCHIVE_MAX_PROCESSES=10
            *.LOG_FILE_NAME_CONVERT='PRMDB','STLDB'
            *.open_cursors=300
            *.pga_aggregate_target=417333248
            *.processes=150
            *.remote_login_passwordfile='EXCLUSIVE'
            *.sga_target=1253048320
            *.STANDBY_FILE_MANAGEMENT='AUTO'
            *.undo_management='AUTO'
            *.undo_tablespace='UNDOTBS1'
            *.user_dump_dest='E:\oracle\product\10.2.0\admin\STLDB\udump'
            • 3. Re: Standby files
              Mahir M. Quluzade
              SQL> select group#,type,member from v$logfile;


              GROUP# TYPE
              ---------- -------
              MEMBER
              --------------------------------------------------------------------------------
              3 ONLINE
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

              2 ONLINE
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

              1 ONLINE
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG


              GROUP# TYPE
              ---------- -------
              MEMBER
              --------------------------------------------------------------------------------
              4 STANDBY
              C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_4_8ORY1L89_.LO
              G

              5 STANDBY
              C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_5_8ORY2JRW_.LO
              G

              6 STANDBY

              GROUP# TYPE
              ---------- -------
              MEMBER
              --------------------------------------------------------------------------------
              C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_6_8ORY2ZRF_.LO
              G

              Hi,

              If this query result on standby side, then you must rename database files (datafiles, tempfiles., log files)

              Regards
              Mahir M. Quluzade
              • 4. Re: Standby files
                Thunder2777
                Thanks Mahir M. Quluzade

                This issue is Resolved.

                Can you look into my spfilestldb.ora as listed above for Errors.

                Regards
                Thunder2777
                • 5. Re: Standby files
                  Mahir M. Quluzade
                  Hi,

                  Spfile is binary file, contains parameters, you can look content of this file, you must create
                  text based parameter file

                  There haven't errors, only parameters.
                  create pfile='C:\pfile.ora' from spfile;
                  and pste here your pfile.


                  Mahir