This discussion is archived
5 Replies Latest reply: Apr 15, 2013 12:16 AM by MahirM.Quluzade RSS

Standby files

Thunder2777 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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

Legend

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