This discussion is archived
11 Replies Latest reply: Apr 10, 2013 4:14 AM by Sunny kichloo RSS

Data Guard parameter

Thunder2777 Newbie
Currently Being Moderated
HI There

I have recently configure Data guard with Database 10g (10.2.0.4-64 bits) on Windows 2007 server.

My Data Gurad Configuration show Success status with 2 databases on same (or local) location.

My questions are

1-When I query SHOW PARAMETER LOG ARCH

DG_CONFIG(PRMDB)

ONLY 1 (PRIMARY DATABASE IS DISPLAYED ONLY NOT 2 DTATABASES
e.g. DG_CONFIG(PRMDB, STLDB)

2- How to check the log applied interval or time (either transaction by transaction, timing etc)

Kind Regards
Thunder2777
  • 1. Re: Data Guard parameter
    Sunny kichloo Expert
    Currently Being Moderated
    Is any of log_archive_dest_ parameter in your system has Service name of Standby database.


    Execute below query on both the server to check archive differences


    Primary

    select max(sequence#) from v$archived_log;


    Standby

    select max(sequence#) from v$log_history;
  • 2. Re: Data Guard parameter
    mseberg Guru
    Currently Being Moderated
    Hello again;

    Based on your other question I'm concerned your setup has an issue. To rule out the listener and tsnname on both side can you run : ( and confirm results )
    From Primary
    
    connect sys/password@standby as sysdba
    
    From Standby
    
    connect sys/password@primary as sysdba
    Also can you post the INIT files from both the Primary and the Standby side? Between { code } top and bottom without the space for formating.

    Will review ASAP

    Best Regards

    mseberg
  • 3. Re: Data Guard parameter
    Thunder2777 Newbie
    Currently Being Moderated
    THANKS FOR QUICK RESPONSE.

    HERE IS THE OUTPUT WHEN I EXECUTED IT ON PRIMARY AND STANDBY DB



    select max(sequence#) from v$archived_log;

    MAX(SEQUENCE#)
    --------------
    52

    select max(sequence#) from v$log_history;

    MAX(SEQUENCE#)
    --------------
    52
  • 4. Re: Data Guard parameter
    Sunny kichloo Expert
    Currently Being Moderated
    Please share your init files as mentioned by mseberg in previous thread.

    So that we can check your configurations are correct
  • 5. Re: Data Guard parameter
    Thunder2777 Newbie
    Currently Being Moderated
    THANKS FOR REPLY


    BOTH TNSNAMES.ORA AND LISTENER.ORA IN SAME DIRECTORY

    C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN

    TNSNAMES.ORA

    # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    PRMDB =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CV-SERVER)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PRMDB)
    )
    )

    STLDB =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = CV-SERVER)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = STLDB)
    )
    )

    LISTENER.ORA

    # listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    (PROGRAM = extproc)
    )
    (SID_DESC =
    (SID_NAME = STLDB)
    (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
    )
    )

    # PRIMARY DATABASE SPFILE

    SPFILEPRMDB.ORA

    prmdb.__db_cache_size=922746880
    prmdb.__java_pool_size=16777216
    prmdb.__large_pool_size=16777216
    prmdb.__shared_pool_size=285212672
    prmdb.__streams_pool_size=0
    *.archive_lag_target=0
    *.audit_file_dest='C:\oracle\product\10.2.0\admin\PRMDB\adump'
    *.background_dump_dest='C:\oracle\product\10.2.0\admin\PRMDB\bdump'
    *.compatible='10.2.0.3.0'
    *.control_files='C:\oracle\product\10.2.0\oradata\PRMDB\control01.ctl','C:\oracle\product\10.2.0\oradata\PRMDB\control02.ctl','C:\oracle\product\10.2.0\oradata\PRMDB\control03.ctl'
    *.core_dump_dest='C:\oracle\product\10.2.0\admin\PRMDB\cdump'
    *.db_block_size=8192
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='PRMDB'
    *.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
    *.db_recovery_file_dest_size=2147483648
    *.dg_broker_start=TRUE
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=PRMDBXDB)'
    *.job_queue_processes=10
    *.log_archive_config='dg_config=(stldb)'
    *.log_archive_dest_1='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=CV-SERVER)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=stldb_XPT)(INSTANCE_NAME=stldb)(SERVER=dedicated)))"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="stldb" register net_timeout=180 valid_for=(online_logfile,primary_role)'
    *.log_archive_dest_state_1='ENABLE'
    prmdb.log_archive_format='ARC%S_%R.%T'
    *.log_archive_max_processes=2
    *.log_archive_min_succeed_dest=1
    prmdb.log_archive_trace=0
    *.open_cursors=300
    *.pga_aggregate_target=417333248
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=1253048320
    prmdb.standby_archive_dest=''
    *.standby_file_management='MANUAL'
    *.undo_management='AUTO'
    *.undo_tablespace='UNDOTBS1'
    *.user_dump_dest='C:\oracle\product\10.2.0\admin\PRMDB\udump'

    # STANDBY DATABASE INITSTLDB
    INITSTLDB.ORA

    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='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.3.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='PRMDB'

    *.db_unique_name='STLDB'

    *.db_recovery_file_dest='D:\oracle\FRA'

    *.db_recovery_file_dest_size=2147483648

    *.dispatchers='(PROTOCOL=TCP) (SERVICE=STLDBXDB)'

    *.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=STLDB'
    *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
    *.job_queue_processes=10

    *.open_cursors=300

    *.pga_aggregate_target=417333248

    *.processes=150

    *.remote_login_passwordfile='EXCLUSIVE'

    *.sga_target=1253048320

    *.undo_management='MANUAL'

    *.undo_tablespace='UNDOTBS1'

    *.user_dump_dest='C:\oracle\product\10.2.0\admin\STLDB\udump'

    THANKS
  • 6. Re: Data Guard parameter
    mseberg Guru
    Currently Being Moderated
    Hello;

    Reviewing will post as an edit here.

    Data Guard parameter setup is incomplete. You have several issues.

    Primary

    log_archive_dest_2 = missing
    log_archive_dest_state_2 = missing
    standby_file_management = would set to AUTO
    log_archive_config='dg_config=(stldb)' = set wrong expecting two enties not just strldb
    FAL_SERVER= missing
    FAL_CLIENT= missing

    dg_broker_start=TRUE ( would set to FALSE until I get stable working system )

    DB_UNIQUE_NAME = missing


    Check my setup notes here :

    http://www.visi.com/~mseberg/data_guard_on_oracle_10_step_by_step.html

    Example
    log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'
    
    log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY' 
    In the example above log_archive_dest_1 is going to the FRA of the Primary database. While log_archive_dest_2 is going to the Service for the remote, Standby database.


    In this example both LOG_ARCHIVE_DEST_STATE_1 and LOG_ARCHIVE_DEST_STATE_2 are set to enable


    For a simple Data Guard setup check these parameters :

    FAL_SERVER
    FAL_CLIENT
    STANDBY_FILE_MANAGEMENT
    DB_UNIQUE_NAME
    DB_FILE_NAME_CONVERT
    LOG_FILE_NAME_CONVERT
    LOG_ARCHIVE_DEST_1
    LOG_ARCHIVE_DEST_2
    LOG_ARCHIVE_DEST_STATE_1
    LOG_ARCHIVE_DEST_STATE_2
    LOG_ARCHIVE_MAX_PROCESSES


    Some of these you may not need for example DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT if the remote directory structure matches the Primary site.

    The COMPATIBLE parameter need to be set the same on both databases. REMOTE_LOGIN_PASSWORDFILE is generally set to 'EXCLUSIVE'.

    For Oracle 10 review document B14239-05 Chapter 3 Creating a Physical Standby Database


    Best Regards

    mseberg

    Edited by: mseberg on Apr 9, 2013 8:06 AM

    Edited by: mseberg on Apr 9, 2013 8:42 AM

    Edited by: mseberg on Apr 9, 2013 8:53 AM
  • 7. Re: Data Guard parameter
    Thunder2777 Newbie
    Currently Being Moderated
    Thanks MSEBERG

    When i perform the the following changes in PRIMARY DB SPFILE

    *.FAL_SERVER=STANDBY
    *.FAL_CLIENT=PRIMARY
    *.DB_FILE_NAME_CONVERT='STLDB','PRMDB'
    *.LOG_FILE_NAME_CONVERT='STLDB','PRMDB'
    *.log_archive_dest_1='LOCATION= C:\oracle\product\10.2.0\flash_recovery_area\PRMDB\ARCHIVELOG
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRMDB'
    *.log_archive_dest_2='SERVICE=STANDBY LGWR ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STLDB'
    *.LOG_ARCHIVE_DEST_STATE_1=ENABLE
    *.LOG_ARCHIVE_DEST_STATE_2=ENABLE
    *.LOG_ARCHIVE_MAX_PROCESSES=30
    PRIMARY.standby_archive_dest = 'C:\oracle\product\10.2.0\flash_recovery_area\PRMDB\ARCHIVELOG'

    AND PERFORM

    SHUTDOWN IMMEDIATE;
    STARTUP;

    THE RESULT: ORA-01078: failure in processing system parameters
    LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITPRMDB.ORA'

    WHAT TO DO??????
  • 8. Re: Data Guard parameter
    Sunny kichloo Expert
    Currently Being Moderated
    Is your parameter file present at below mentioned locations

    C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE
  • 9. Re: Data Guard parameter
    mseberg Guru
    Currently Being Moderated
    Hello;

    Would check what Sunny said. You can also start NOMOUNT from your PFILE and recreate the SPFILE from the pfile. Make sure you PFILE has no comments in it.

    Recreate example ( Linux )

    http://www.visi.com/~mseberg/general/fast_spfile_from_pfile.html

    h2. Update

    This does not appear to be correct :
    PRIMARY.standby_archive_dest = 'C:\oracle\product\10.2.0\flash_recovery_area\PRMDB\ARCHIVELOG'
    PRIMARY.standby_archive_dest syntax could cause your ORA-01078 LRM-00109. Would remove "PRIMARY."

    Also double check your ORACLE_SID before startup

    Best Regards

    mseberg

    Edited by: mseberg on Apr 10, 2013 7:14 AM
  • 10. Re: Data Guard parameter
    Thunder2777 Newbie
    Currently Being Moderated
    THANKS SUNNY

    C:\oracle\product\10.2.0\db_1\database\SPFILEPRMDB.ORA

    WHERE PRMDB IS THE NAME OF PRIMARY DB.
  • 11. Re: Data Guard parameter
    Sunny kichloo Expert
    Currently Being Moderated
    IF you have spfile why it is taking pfile as mentioned in your error

    LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITPRMDB.ORA


    Are you using normal startup command for starting database or something else??

    Most import have you checked on the name of your spfile.Is there some spaces in the name of your files.

Legend

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