1 2 3 Previous Next 44 Replies Latest reply: Sep 22, 2012 10:55 PM by 918424 Go to original post RSS
      • 15. Re: Archive Logs NOT APPLIED but transferred
        918424
        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
          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
            Mahir M. Quluzade
            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:
              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
                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
                  Mahir M. Quluzade
                  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
                    orawarebyte
                    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
                      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
                        orawarebyte
                        Please paste what i asked in my last post.

                        Khurram
                        • 24. Re: Archive Logs NOT APPLIED but transferred
                          918424
                          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:
                            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
                              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
                                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
                                  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
                                    orawarebyte
                                    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