This discussion is archived
1 2 3 Previous Next 44 Replies Latest reply: Sep 22, 2012 8:55 PM by 918424 Go to original post RSS
  • 15. Re: Archive Logs NOT APPLIED but transferred
    918424 Newbie
    Currently Being Moderated
    CKPT: there is no listener parameter in both pfiles in both site,

    remote_login_password parameter is set to exclusive

    standby_archive_dest parameter is showing as *?/dbs/arch*
    (I hearing this parameter for the first time).

    Mseberg: I checked the password with v$pwfile_users on both sites. It is showing True on the both sites.

    Abdul: Log_archive_dest_state_2 is in Enabled state.

    Thanks.
  • 16. Re: Archive Logs NOT APPLIED but transferred
    CKPT Guru
    Currently Being Moderated
    915421 wrote:
    CKPT: there is no listener parameter in both pfiles in both site,

    remote_login_password parameter is set to exclusive

    standby_archive_dest parameter is showing as *?/dbs/arch*
    (I hearing this parameter for the first time).

    Mseberg: I checked the password with v$pwfile_users on both sites. It is showing True on the both sites.

    Abdul: Log_archive_dest_state_2 is in Enabled state.

    Thanks.
    Ok, Set the LOCAL_LISTENER on both primary and standby database.
    I guess you are in 10g, So mention STANDBY_ARCHIVE_DEST to /home/oracle/oracle/product/10.2.0/db_1/oradata/newprim/arch/

    And then retry after bouncing standby database once and start MRP. Post if any errors.

    Thanks.
  • 17. Re: Archive Logs NOT APPLIED but transferred
    MahirM.Quluzade Guru
    Currently Being Moderated
    Very interesting.
    Can you check on standby MPR is started ?
     SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
  • 18. Re: Archive Logs NOT APPLIED but transferred
    abdul: Newbie
    Currently Being Moderated
    Hi again,


    can you paste your parameter file here of both side.

    thanks


    THEREAFTER

    paste this query result also:

    [on both side]
    select switchover_status from v$database;
    Edited by: abdul_ora on Sep 21, 2012 4:36 PM
  • 19. Re: Archive Logs NOT APPLIED but transferred
    918424 Newbie
    Currently Being Moderated
    Sorry for the delay..It was due to some interruption.

    CKPT: I have set the standby_archive_dest as you suggested in the primary site Pfile and the local_listener parameters on both pfiles.

    ora-00132: Syntax error or unresolved network name 'Listener' when I set local_listener='LISTENER' or LISTENER
    ora- ...: Invalid specification for system parameter
    ora-...:unable to process system parameters

    guide me please...
  • 20. Re: Archive Logs NOT APPLIED but transferred
    MahirM.Quluzade Guru
    Currently Being Moderated
    hi,

    you must set LOCAL_LISTENER with like
     alter system set local_listener=  '(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IP>)(PORT=1521))' scope=spfile;
    
     If you have an entry in your tnsnames.ora that points to the listener, you can simply use the listener name instead:
     
     alter system set local_listener='<listener_name>' scope=spfile; 
    You must restart database take effect

    Check this link also : http://edstevensdba.wordpress.com/2011/07/30/exploring-the-local_listener-parameter/
    Fig 10



    Regards
    Mahir M. Quluzade
    www.mahir-quluzade.com

    Edited by: Mahir M. Quluzade on Sep 21, 2012 4:43 PM
  • 21. Re: Archive Logs NOT APPLIED but transferred
    316993 Pro
    Currently Being Moderated
    Mahir Input seems to me quite reasonable , check at standby that MRP is functioning or not, paste the following result here.
     select process,status,thread#,sequence#,block#,blocks
       from v$managed_standby
    /
    select sequence# 
      from v$archived_log
    where applied='NO'
    /
    select *
      from v$archive_gap
    Khurram
  • 22. Re: Archive Logs NOT APPLIED but transferred
    918424 Newbie
    Currently Being Moderated
    Mahir: SQL> select process,status from v$managed_standby;

    PROCESS STATUS
    --------- ------------
    ARCH CONNECTED
    ARCH CONNECTED
    MRP0 WAIT_FOR_GAP
    SQL>

    Abdul: Both pfiles are added for your reference below:

    Primary Pfile:


    log_archive_format=%t_%s_%r.dbf

    db_block_size=8192
    db_file_multiblock_read_count=16

    open_cursors=300

    db_domain=""
    db_name=newprim



    background_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newprim/bdump
    core_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newprim/cdump
    user_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newprim/udump

    control_files=("/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim/control01.ctl", "/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim/control02.ctl", "/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim/control03.ctl")
    db_recovery_file_dest=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area
    db_recovery_file_dest_size=2147483648

    ###########################################
    # Job Queues
    ###########################################
    job_queue_processes=10

    ###########################################
    # Miscellaneous
    ###########################################
    compatible=10.2.0.1.0

    ###########################################
    # Processes and Sessions
    ###########################################
    processes=150

    ###########################################
    # SGA Memory
    ###########################################
    sga_target=286261248

    ###########################################
    # Security and Auditing
    ###########################################
    audit_file_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newprim/adump
    remote_login_passwordfile=EXCLUSIVE

    ###########################################
    # Shared Server
    ###########################################
    dispatchers="(PROTOCOL=TCP) (SERVICE=newprimXDB)"

    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################
    pga_aggregate_target=95420416

    undo_management=AUTO
    undo_tablespace=UNDOTBS1
    ######local_listener='LISTENER'


    db_unique_name=newprim
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(newprim,newstand)'

    LOG_ARCHIVE_DEST_1=
    'LOCATION=/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim/arch/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=newprim'

    log_archive_dest_2='SERVICE=newstand LGWR ASYNC VALID_FOR=(online_logfiles,primary_role) DB_UNIQUE_NAME=newstand'

    #########standby_archive_dest=/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim/arch/

    log_archive_dest_state_1=enable

    log_archive_dest_state_2=enable
    log_archive_max_processes=30

    FAL_SERVER=newstand

    FAL_CLIENT=newprim

    DB_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand','/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim'

    LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand','/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim','/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/NEWSTAND/onlinelog','/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/NEWPRIM/onlinelog'

    STANDBY_FILE_MANAGEMENT=AUTO


    Standby Pfile:


    db_block_size=8192

    db_file_multiblock_read_count=16

    open_cursors=300

    background_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newstand/bdump
    core_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newstand/cdump
    user_dump_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newstand/udump

    ####local_listener=LISTENER1

    db_recovery_file_dest=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area

    db_recovery_file_dest_size=2147483648

    ###########################################
    # Job Queues
    ###########################################
    job_queue_processes=10

    ###########################################
    # Miscellaneous
    ###########################################
    compatible=10.2.0.1.0

    ###########################################
    # Processes and Sessions
    ###########################################
    processes=150

    ###########################################
    # SGA Memory
    ###########################################
    sga_target=286261248

    ###########################################
    # Security and Auditing
    ###########################################
    audit_file_dest=/home/oracle/oracle/product/10.2.0/db_1/admin/newstand/adump

    remote_login_passwordfile=EXCLUSIVE

    ###########################################
    # Shared Server
    ###########################################
    dispatchers="(PROTOCOL=TCP) (SERVICE=newstandXDB)"

    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################
    pga_aggregate_target=95420416

    undo_management=AUTO
    undo_tablespace=UNDOTBS1


    DB_NAME=newprim

    DB_UNIQUE_NAME=newstand

    LOG_ARCHIVE_CONFIG='DG_CONFIG=(newprim,newstand)'

    CONTROL_FILES='/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand/standby01.ctl','/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand/standby02.ctl','/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand/standby03.ctl'

    DB_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim','/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand'

    LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/db_1/oradata/newprim','/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand','/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/NEWPRIM/onlinelog','/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/NEWSTAND/onlinelog'

    LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

    LOG_ARCHIVE_DEST_1=
    'LOCATION=/home/oracle/oracle/product/10.2.0/db_1/oradata/newstand/arch/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=newstand'

    LOG_ARCHIVE_DEST_2=
    'SERVICE=newprim LGWR ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=newprim'

    LOG_ARCHIVE_DEST_STATE_1=ENABLE

    LOG_ARCHIVE_DEST_STATE_2=ENABLE

    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

    STANDBY_FILE_MANAGEMENT=AUTO

    FAL_SERVER=newprim

    FAL_CLIENT=newstand
    .


    Primary Site:

    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    TO STANDBY


    Standby Site:
    SQL> select switchover_status from v$database;

    SWITCHOVER_STATUS
    --------------------
    RECOVERY NEEDED


    Thanks.
  • 23. Re: Archive Logs NOT APPLIED but transferred
    316993 Pro
    Currently Being Moderated
    Please paste what i asked in my last post.

    Khurram
  • 24. Re: Archive Logs NOT APPLIED but transferred
    918424 Newbie
    Currently Being Moderated
    Hi Khurram, I have added that below for your reference:

    SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

    PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
    --------- ------------ ---------- ---------- ---------- ----------
    ARCH CONNECTED 0 0 0 0
    ARCH CONNECTED 0 0 0 0
    MRP0 WAIT_FOR_GAP 1 1 0 0
    SQL>


    SQL> select sequence#,APPLIED from v$archived_log where applied='NO';

    SEQUENCE# APP
    ---------- ---
    35 NO
    41 NO
    40 NO
    34 NO
    39 NO
    38 NO
    42 NO
    44 NO
    36 NO
    37 NO
    43 NO
    46 NO
    45 NO
    47 NO
    48 NO
    49 NO
    50 NO
    51 NO
    52 NO
    53 NO
    54 NO
    55 NO
    56 NO
    57 NO
    58 NO
    59 NO
    60 NO
    62 NO
    61 NO
    63 NO
    64 NO
    65 NO
    66 NO
    107 NO
    108 NO
    109 NO
    143 NO
    144 NO
    145 NO
    146 NO
    147 NO
    SQL>


    select * from v$archive_gap; returns no rows...


    Thanks.
  • 25. Re: Archive Logs NOT APPLIED but transferred
    abdul: Newbie
    Currently Being Moderated
    thanks ,

    what i come to know to watched your pfile it been look fine ,all parameter seems transcendent


    but have a look on :

    [standby]
    LOG_ARCHIVE_DEST_STATE_2 should be 'defer'
    so make it.

    run this
    [on standby]
    alter system set log_archive_dest_state_2 = 'defer';
    Edited by: abdul_ora on Sep 21, 2012 5:42 PM
  • 26. Re: Archive Logs NOT APPLIED but transferred
    mseberg Guru
    Currently Being Moderated
    Hello;

    It appears you are not using an spfile on the Standby database. You INIT shows
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    Twice

    I don't think an spfile will allow that, but then I no longer have an oracle 10 system to test it on.

    Data Guard requires an spfile.

    Can you post the exact name and location of the password file on the standby server?


    In a perfect world this :
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    Would be set to DEFER on the standby, but this is not your issue.

    Best Regards

    mseberg
  • 27. Re: Archive Logs NOT APPLIED but transferred
    918424 Newbie
    Currently Being Moderated
    Adbul: I made it in standby site as you suggested and restarted both the databases but logs not applied...

    Mseberg: Your message shown me how funny I am !! Now I have aet my DB on SPFILE !!

    Thanks..

    I personally feel that as I don't have OLAP installed in my software, I have this issue. There is an indication in alert log(shown in previous posts) that there is an error due to absence of OLAP option...
  • 28. Re: Archive Logs NOT APPLIED but transferred
    mseberg Guru
    Currently Being Moderated
    The OLAP option has to do with the OLAPSYS schema. This should not be related to your issue.

    So you have stopped and restarted both databases?

    What are the results?

    Can you post the name and location of the password file on the standby server?

    After the restarts what do the last 100 lines of the alert logs showing ( Primary and standby) ?

    Not using an SPFILE is a configuration issue

    Since not one log has applied yet I would hardly call this a GAP!

    Best Regards

    mseberg

    Edited by: mseberg on Sep 21, 2012 8:02 AM
  • 29. Re: Archive Logs NOT APPLIED but transferred
    316993 Pro
    Currently Being Moderated
    You have archived gap , there is no issue of either OLAP exist or not , it does not make sense.Check those non applied archive log physically at yours standby database by using OS command , there is a hole in yours database which seem to me from missing sequence 34.Check all those non applied archive log physically exist there or not.

    There is no configuration issue.

    Paste as well here
    Select Max(sequence#) 
      from v$archived_log where applied='YES'
    /
    Select Min(sequence#) 
      from v$archived_log where applied='NO'
    It is very odd that yours standby MRP still seeing for sequence 1.

    Khurram

Legend

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