DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: archive logs not get copied on standby database in a dataguard configuration.

Hybrid View

  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Question archive logs not get copied on standby database in a dataguard configuration.

    Hi All,

    I am trying for first time, to create physical standby database using data guard feature of oracle on windows box.
    As I am fresher in dba field, to keep it simple I am doing it on same box.

    Issue:
    1) Archive logs are getting created on primary database in archive folder specified “LOG_ARCHIVE_DEST_1" parameter in pfile of primary database.
    But on standby database I am not able to see archive logs and they are not getting copied there.
    2) What things need to be done so that archive logs will get copied on standby database?
    3) Do I initializing the both database using wrong "redo transport, redo apply and role management parameters"?
    How should I initialize following parameters in data guard configuration?

    LOG_ARCHIVE_DEST_1
    LOG_ARCHIVE_DEST_2
    FAL_SERVER=
    FAL_CLIENT=
    db_file_name_convert=
    log_file_name_convert
    LOG_ARCHIVE_FORMAT

    regards,
    knp_11

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    otn.oracle.com -> datagurad manual

    its all in there

  3. #3
    Join Date
    Feb 2009
    Posts
    91
    As a beginner, my suggestion to you would be to skip using dataguard
    for now until you get comfortable with the concept of the primary and
    standbny database. I say this because, I am first start to expirment with
    a standby db and understand it.

    Here are my instructions for creating a primary and standby DB on the same
    machine if you want to follow them:

    1) create a DB anyway you like (manually, clone, ...) and call it pri

    2) Add the following to your tnsnames.ora and listener.ora file

    note: ip_address must be replaceed with a valid ip address

    tnsnames.ora

    PRI =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ip_address)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = pri)
    )
    )

    STDBY =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ip_address)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = stdby)
    )
    )

    listener.ora

    LISTENER =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(Host = ip_address)(Port = 1521))
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME= /rdbms/oracle/10g20)
    (SID_NAME = pri)
    )
    (SID_DESC =
    (ORACLE_HOME= /rdbms/oracle/10g20)
    (SID_NAME = stdby)
    )
    )

    TRACE_LEVEL_listener = OFF
    STARTUP_WAIT_TIME_listener = 0
    CONNECT_TIMEOUT_listener = 10

    Add the following entries to your initpri.ora file

    DB_NAME=PRI
    DB_UNIQUE_NAME=PRI
    CONTROL_FILES='/u1/pri/control1.ctl', '/u2/pri/control2.ctl'
    LOG_ARCHIVE_DEST_1=
    'LOCATION=/u1/oraarch/pri/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=pri'
    LOG_ARCHIVE_DEST_2=
    'SERVICE=stdby ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=stdby'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
    LOG_ARCHIVE_MAX_PROCESSES=30
    FAL_SERVER=stdby
    FAL_CLIENT=pri
    DB_FILE_NAME_CONVERT='stdby','pri'
    LOG_FILE_NAME_CONVERT='stdby','pri'
    STANDBY_FILE_MANAGEMENT=AUTO

    initpri.ora file (here is complete initpri.ora file if you want to cut and paste)

    $ cat initpri.ora
    *.audit_file_dest='/t4data/oracle_data/pri/audit'
    *.audit_trail='false'
    *.background_dump_dest='/t4data/oracle_data/pri/bdump'
    *.compatible='10.2.0.1.0'
    *.control_files='/t4data/oracle_data/pri/control01.ctl','/t4data/oracle_data/pri
    /control02.ctl','/t4data/oracle_data/pri/control03.ctl'
    *.core_dump_dest='/t4data/oracle_data/pri/cdump'
    *.db_block_size=4096
    *.db_cache_size=100m
    *.db_domain='world.com'
    *.db_name='pri'
    *.DB_UNIQUE_NAME='pri'
    *.instance_name='pri'
    *.java_pool_size=31457280
    *.job_queue_processes=20
    *.large_pool_size=8388608
    *.log_archive_format='arch%s%r%t.arc'
    *.max_dump_file_size='10000'
    *.nls_sort='binary'
    *.open_cursors=600
    *.pga_aggregate_target=100m
    *.processes=300
    *.remote_login_passwordfile='EXCLUSIVE'
    *.service_names='pri'
    *.shared_pool_size=153431142
    *.undo_management='AUTO'
    *.undo_retention=10800
    *.undo_tablespace='UNDOTBS1'
    *.user_dump_dest='/t4data/oracle_data/pri/udump'
    *.workarea_size_policy='auto'
    *.log_archive_dest_1='LOCATION=/t4data/oracle_data/pri/arch/ VALID_FOR=(ALL_LOGF
    ILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
    *.log_archive_dest_2='SERVICE=stdby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROL
    E) DB_UNIQUE_NAME=stdby'
    *.log_archive_dest_state_1=ENABLE
    *.log_archive_dest_state_2=ENABLE
    *.fal_server=stdby
    *.fal_client=pri
    *.db_file_name_convert='/t4data/oracle_data/stdby','/t4data/oracle_data/pri'
    *.log_file_name_convert='/t4data/oracle_data/stdby','/t4data/oracle_data/pri'
    *.standby_file_management=AUTO


    cp initpri.ora to initstdby.ora and modify

    DB_NAME=pri
    DB_UNIQUE_NAME=stdby
    CONTROL_FILES='/u1/stdby/control1.ctl', '/u2/stdby/control2.ctl'
    DB_FILE_NAME_CONVERT='pri','stdby'
    LOG_FILE_NAME_CONVERT='pri','stdby'
    LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
    LOG_ARCHIVE_DEST_1=
    'LOCATION=/u1/oraarch/stdby/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=stdby'
    LOG_ARCHIVE_DEST_2=
    'SERVICE=pri ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=pri'
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    STANDBY_FILE_MANAGEMENT=AUTO
    FAL_SERVER=pri
    FAL_CLIENT=stdby

    Here is a complete initstdby.ora file if you want to cut and paste.

    $ cat initstdby.ora
    *.background_dump_dest='/t4data/oracle_data/stdby/bdump'
    *.compatible='10.2.0.1.0'
    *.control_files='/t4data/oracle_data/stdby/control01.ctl','/t4data/oracle_data/s
    tdby/control02.ctl','/t4data/oracle_data/stdby/control03.ctl'
    *.core_dump_dest='/t4data/oracle_data/stdby/cdump'
    *.db_block_size=4096
    *.db_cache_size=100m
    *.db_domain='world.com'
    *.db_name='pri'
    *.DB_UNIQUE_NAME='stdby'
    *.instance_name='pri'
    *.java_pool_size=31457280
    *.job_queue_processes=20
    *.large_pool_size=8388608
    *.log_archive_format='arch%s%r%t.arc'
    *.max_dump_file_size='10000'
    *.nls_sort='binary'
    *.open_cursors=600
    *.pga_aggregate_target=100m
    *.processes=300
    *.remote_login_passwordfile='EXCLUSIVE'
    *.service_names='pri'
    *.shared_pool_size=153431142
    *.undo_management='AUTO'
    *.undo_retention=10800
    *.undo_tablespace='UNDOTBS1'
    *.user_dump_dest='/t4data/oracle_data/stdby/udump'
    *.workarea_size_policy='auto'
    *.LOG_ARCHIVE_DEST_1='LOCATION=/t4data/oracle_data/stdby/arch/ VALID_FOR=(ALL_LO
    GFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
    *.LOG_ARCHIVE_DEST_2='SERVICE=pri ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=pri'
    *.log_archive_dest_state_1=ENABLE
    *.log_archive_dest_state_2=ENABLE
    *.fal_server=pri
    *.fal_client=stdby
    *.db_file_name_convert='pri','stdby'
    *.log_file_name_convert='pri','stdby'
    *.standby_file_management=AUTO


    Take a cold backup of Primary database
    Create control file from primary for standby as
    sql> startup mount
    sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/pri.ctl';
    sql> ALTER DATABASE OPEN;

    In my sample I have 3 control files so you need to copy
    cp /tmp/pri.ctl ...control01.ctl
    cp /tmp/pri.ctl ...control02.ctl
    cp /tmp/pri.ctl ...control03.ctl

    Set environment variable for standby database in terms of ORACLE_HOME. ORACLE_SID, PATH etc and run sqlplus as

    $ sqlplus “/ as sysdba”
    sql> startup mount;

    Standby at this stage will be in mount mode and now take it to managed recovery mode as

    Sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

    use orapwd to create password files for both primary and standby db


    Here is a good document to follow. Let me know if you need anymore help

  4. #4
    Join Date
    Apr 2009
    Posts
    2

    archive logs not get copied on standby database in a dataguard configuration. Reply

    Hi Paul
    Thanks for such detailed information.Actually I was trying on one system since last 15 days but coulden't get through so moved to two box configuration on linux
    1)I have installed "PRIM" database on primary and on standby installed without starter database.
    2)I configured pfile for primary ie. added parameters needed for dataguard configuration also created net service for it and created listner for it
    3)I started the database using modified pfile(which contain parameters for dataguard)
    4)created spfile and mounted database using spfile
    5)I enabled log archive mode and force logging on primary and logs are getting generated at the location specifed in LOG_ARCHIVE_DEST_1 in primary pfile.
    6)On standby :I created pfile for standby and then transfered it to standby then modified it for datagurd configuration and standby database was mounted using that pfile then created spfile and mounted database using that spfile.
    7)I also cretated listner and net service on standby and my standby server is running fine
    8)But when I "alter database recover managed standby database disconnect from session;" on standby i found no logs getting generated on standby
    9)I think somehow log transport service is not able to transport the archived logs to standby server

    These are some link I refered
    http://www.orafaq.com/node/2030
    http://www.singlequery.com/2008/06/s...rd-enviroment/

    I have few questions for you.
    1)Do we need to create listners for primary and standby on primary server and on standby server also
    or
    On primary server, listner for primary only
    On standby server, listner for standby only

    2)I have taken cold backup and also tried with RAMAN backup from primary to secondary does it make difference?

    3)Can you send me any link which can help me

    4)How can I troubleshoot this issue as when I was doing it on single box same thing was happening and now on two box configurastion also it is there
    I think I am not initilizing datagurad parameters in the pfiles properly

    regards,
    NandyK

  5. #5
    Join Date
    Feb 2009
    Posts
    91
    I have not got up to the point of making this work on 2 servers but I
    would strongly suggest trying to keep things simple and use one machine
    first.

    As for the listeners and tnsnames.ora file since both DB's need to communicate with each other I would suggest making them work
    on both machines.

    Also, remember you need to put your prim db in a mode where it forces
    logging.

    Hope this helps

  6. #6
    Join Date
    Feb 2009
    Posts
    91
    This is a very good link note the queries that can help and also
    you may want to look at v$log

    http://download.oracle.com/docs/cd/B...htm#SBYDB00426

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width