1 2 3 Previous Next 31 Replies Latest reply: Jul 2, 2012 9:05 PM by 849425 RSS

    logfile member shows in database but not on physical disk, not match

    849425
      I just created a standby db through rman duplicat active database from target, and it worked.

      However when I issued a alter syste switch logfile, it not have anything going on on standby side.

      I checked the alert log, when the standby db started, it has following error:

      Errors in file /u01/app/oracle/diag/rdbms/prd/prdS1/trace/prdS1_ora_16397.trc:
      ORA-00313: open failed for members of log group 13 of thread 2
      ORA-00312: online log 13 thread 2: '+PRD_DAT/prd/onlinelog/group_13.390.786640667'
      ORA-17503: ksfdopn:2 Failed to open file +PRD_DAT/prd/onlinelog/group_13.390.786640667
      ORA-15173: entry 'group_13.390.786640667' does not exist in directory 'onlinelog'
      Completed: alter database clear logfile group 13
      alter database clear logfile group 14
      Clearing online log 14 of thread 2 sequence number 0
      Errors in file /u01/app/oracle/diag/rdbms/prd/prdS1/trace/prdS1_ora_16397.trc:
      ORA-00313: open failed for members of log group 14 of thread 2
      ORA-00312: online log 14 thread 2: '+PRD_DAT/prd/onlinelog/group_14.366.786640667'
      ORA-17503: ksfdopn:2 Failed to open file +PRD_DAT/prd/onlinelog/group_14.366.786640667
      ORA-15173: entry 'group_14.366.786640667' does not exist in directory 'onlinelog'
      Errors in file /u01/app/oracle/diag/rdbms/prd/prdS1/trace/prdS1_ora_16397.trc:
      ORA-00313: open failed for members of log group 14 of thread 2
      ORA-00312: online log 14 thread 2: '+PRD_DAT/prd/onlinelog/group_14.366.786640667'
      ORA-17503: ksfdopn:2 Failed to open file +PRD_DAT/prd/onlinelog/group_14.366.786640667
      ORA-15173: entry 'group_14.366.786640667' does not exist in directory 'onlinelog'
      Completed: alter database clear logfile group 14
      RFS connections are allowed
      Sat Jun 30 21:03:50 2012
      alter database flashback on
      Errors in file /u01/app/oracle/diag/rdbms/prd/prdS1/trace/prdS1_ora_18097.trc:
      ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
      ORA-38788: More standby database recovery is needed
      ORA-38706 signalled during: alter database flashback on...

      Sql results of logfile members are this:

      MEMBER
      --------------------------------------------------------------------------------
      +PRD_DAT/prds/onlinelog/group_2.262.787354885
      +PRD_DAT/prds/onlinelog/group_1.290.787354885
      +PRD_DAT/prds/onlinelog/group_5.260.787354889
      +PRD_DAT/prds/onlinelog/group_3.261.787354887
      +PRD_DAT/prds/onlinelog/group_4.264.787354887
      +PRD_DAT/prds/onlinelog/group_6.279.787354889
      +PRD_DAT/prds/onlinelog/group_7.277.787354889
      +PRD_DAT/prds/onlinelog/group_8.280.787354891
      +PRD_DAT/prds/onlinelog/group_9.281.787354891
      +PRD_DAT/prds/onlinelog/group_10.282.787354893
      +PRD_DAT/prds/onlinelog/group_11.287.787354893

      MEMBER
      --------------------------------------------------------------------------------
      +PRD_DAT/prds/onlinelog/group_12.265.787354893
      +PRD_DAT/prds/onlinelog/group_13.266.787354895
      +PRD_DAT/prds/onlinelog/group_14.283.787354895
      +PRD_FRA/prds/onlinelog/group_1.1285.787354885
      +PRD_FRA/prds/onlinelog/group_2.1286.787354885
      +PRD_FRA/prds/onlinelog/group_3.1287.787354887
      +PRD_FRA/prds/onlinelog/group_4.1288.787354887
      +PRD_FRA/prds/onlinelog/group_5.1289.787354889
      +PRD_FRA/prds/onlinelog/group_6.1290.787354889
      +PRD_FRA/prds/onlinelog/group_7.1291.787354891
      +PRD_FRA/prds/onlinelog/group_8.1292.787354891

      MEMBER
      --------------------------------------------------------------------------------
      +PRD_FRA/prds/onlinelog/group_9.1293.787354891
      +PRD_FRA/prds/onlinelog/group_10.1294.787354893
      +PRD_FRA/prds/onlinelog/group_11.1295.787354893
      +PRD_FRA/prds/onlinelog/group_12.1296.787354895
      +PRD_FRA/prds/onlinelog/group_13.1297.787354895
      +PRD_FRA/prds/onlinelog/group_14.1298.787354895

      28 rows selected.

      asmcmd shows those log files:


      Type Redund Striped Time Sys Name
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_1.290.787354885
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_10.282.787354893
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_11.287.787354893
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_12.265.787354893
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_13.266.787354895
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_14.283.787354895
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_2.262.787354885
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_3.261.787354887
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_4.264.787354887
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_5.260.787354889
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_6.279.787354889
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_7.277.787354889
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_8.280.787354891
      ONLINELOG UNPROT COARSE JUN 30 21:00:00 Y group_9.281.787354891
      ASMCMD> exit

      How to fix this issue, the physical standby db seems not working.

      Thanks in advance for help.
        • 1. Re: logfile member shows in database but not on physical disk, not match
          CKPT
          ORA-17503: ksfdopn:2 Failed to open file +PRD_DAT/prd/onlinelog/group_14.366.786640667
          Hi,

          What is the disk group name and what is full location?
          Have you mentioned LOG_FILE_NAME_CONVERT?

          Let's suppose your primary online log location is : +PRD_DAT/prd/onlinelog
          And your standby log file location is : +STDBY_DAT/prd/onlinelog

          Ex: LOG_FILE_NAME_CONVERT='+PRD_DAT/prd/onlinelog','+STBY_DAT/stby/onlinelog'

          if it is OMF, then
          ex: LOG_FILE_NAME_CONVERT='+PRD_DAT','+STDBY_DAT'

          when you start MRP it will clear logs, so please mention what is the value for LOG_FILE_NAME_CONVERT?
          • 2. Re: logfile member shows in database but not on physical disk, not match
            849425
            on both primary/standby,

            SQL> show parameter log_file

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            log_file_name_convert string PRD_DAT, PRD_DAT
            • 3. Re: logfile member shows in database but not on physical disk, not match
              CKPT
              >
              Sql results of logfile members are this:

              MEMBER
              +PRD_DAT/prds/onlinelog/group_2.262.787354885
              +PRD_DAT/prds/onlinelog/group_1.290.787354885
              +PRD_DAT/prds/onlinelog/group_5.260.787354889
              +PRD_DAT/prds/onlinelog/group_3.261.787354887
              +PRD_DAT/prds/onlinelog/group_4.264.787354887
              +PRD_DAT/prds/onlinelog/group_6.279.787354889
              +PRD_DAT/prds/onlinelog/group_7.277.787354889
              +PRD_DAT/prds/onlinelog/group_8.280.787354891
              +PRD_DAT/prds/onlinelog/group_9.281.787354891
              +PRD_DAT/prds/onlinelog/group_10.282.787354893
              +PRD_DAT/prds/onlinelog/group_11.287.787354893

              MEMBER
              +PRD_DAT/prds/onlinelog/group_12.265.787354893
              +PRD_DAT/prds/onlinelog/group_13.266.787354895
              +PRD_DAT/prds/onlinelog/group_14.283.787354895
              +PRD_FRA/prds/onlinelog/group_1.1285.787354885
              +PRD_FRA/prds/onlinelog/group_2.1286.787354885
              +PRD_FRA/prds/onlinelog/group_3.1287.787354887
              +PRD_FRA/prds/onlinelog/group_4.1288.787354887
              +PRD_FRA/prds/onlinelog/group_5.1289.787354889
              +PRD_FRA/prds/onlinelog/group_6.1290.787354889
              +PRD_FRA/prds/onlinelog/group_7.1291.787354891
              +PRD_FRA/prds/onlinelog/group_8.1292.787354891

              MEMBER
              +PRD_FRA/prds/onlinelog/group_9.1293.787354891
              +PRD_FRA/prds/onlinelog/group_10.1294.787354893
              +PRD_FRA/prds/onlinelog/group_11.1295.787354893
              +PRD_FRA/prds/onlinelog/group_12.1296.787354895
              +PRD_FRA/prds/onlinelog/group_13.1297.787354895
              +PRD_FRA/prds/onlinelog/group_14.1298.787354895
              >

              Can you tell me above redo log files are from Primary?

              846422 wrote:
              on both primary/standby,

              SQL> show parameter log_file

              NAME TYPE VALUE
              ------------------------------------ ----------- ------------------------------
              log_file_name_convert string PRD_DAT, PRD_DAT
              I assume abover redo log files are from primary, What are the disk groups on standby?
              is it ASM With OMF?
              SQL> show parameter db_create
              You have to mention as
              LOG_FILE_NAME_CONVERT='+PRD_DAT','+STDBY_DAT','+PRD_FRA','+STDBY_FRA'
              LOG_FILE_NAME_CONVERT='Prim_loc1','Stdby_loc1','Prim_loc2','Stdby_loc2'

              Because in primary you have two diskgroups for Online redo log files.

              Use link to configure http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm

              Once after above all settings, Bounce it and start MRP. watch alert log file initially you may see errors while clearing online redo log files. Later if you seen any more please post here.
              Thanks.
              • 4. Re: logfile member shows in database but not on physical disk, not match
                849425
                on primary :

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                db_create_file_dest string +PRD_DAT
                db_create_online_log_dest_1 string +PRD_DAT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                • 5. Re: logfile member shows in database but not on physical disk, not match
                  CKPT
                  846422 wrote:
                  on primary :

                  NAME TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  db_create_file_dest string +PRD_DAT
                  db_create_online_log_dest_1 string +PRD_DAT
                  You have not mentioned what i have requested. read once again and post complete findings. Because i dont know what is the disk groups on standby. So please change accordingly as per your environment.


                  >
                  You have to mention as
                  LOG_FILE_NAME_CONVERT='+PRD_DAT','+STDBY_DAT','+PRD_FRA','+STDBY_FRA'
                  LOG_FILE_NAME_CONVERT='Prim_loc1','Stdby_loc1','Prim_loc2','Stdby_loc2'

                  Because in primary you have two diskgroups for Online redo log files.
                  Use link to configure http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm
                  Once after above all settings, Bounce it and start MRP. watch alert log file initially you may see errors while clearing online redo log files. Later if you seen any more please post here.
                  • 6. Re: logfile member shows in database but not on physical disk, not match
                    849425
                    on both primary/standby, disk groups are PRD_DAT. PRD_FRA are db recovery area.
                    • 7. Re: logfile member shows in database but not on physical disk, not match
                      CKPT
                      846422 wrote:
                      on both primary/standby, disk groups are PRD_DAT. PRD_FRA are db recovery area.
                      Ok, Configure LOG_FILE_NAME_CONVERT as below
                      LOG_FILE_NAME_CONVERT='+PRD_DAT','+PRD_DAT','+PRD_FRA','+PRD_FRA'

                      Normally if mount point are same, then no need to mention.
                      ORA-15173: entry 'group_14.366.786640667' does not exist in directory 'onlinelog'
                      Its OMF, so "prd" will be changed as pr your DB_UNIQUE_NAME.

                      I recommend you to create directories "onlinelog" manually in both the Diskgroups.
                      Assuming your DB_UNIQUE_NAME on standby as "stby"
                      Ex:- +PRD_DAT/stby/onlinelog
                      +PRD_FRA/stby/onlinelog

                      Take care of it, because am not sure of your DB_UNIQUE_NAME, so change as per above both LOG_FILE_NAME_CONVERT & create directories.
                      • 8. Re: logfile member shows in database but not on physical disk, not match
                        849425
                        db unique name is prds.

                        I will see if I can follow your instruction to fix the issue.
                        • 9. Re: logfile member shows in database but not on physical disk, not match
                          849425
                          This logfile issue is resolved.

                          However when I issued alter system switch logfile on primary, I did not see anything on standby alertlog.

                          Is that means logs are not shipping to standby site?
                          • 10. Re: logfile member shows in database but not on physical disk, not match
                            Seberg
                            You should see a message(s) like these:
                            Sat Jun 30 22:03:31 2012
                            Media Recovery Log /u01/app/oracle/flash_recovery_area/STANDBY/archivelog/2012_06_30/o1_mf_1_779_7yzhhjco_.arc
                            Media Recovery Waiting for thread 1 sequence 780 (in transit)
                            So in the above example it just finished 779 and is waiting for 780.

                            If you don't see that then I would cancel apply, shutdown the standby database. Restart it MOUNT and start apply again.
                            • 11. Re: logfile member shows in database but not on physical disk, not match
                              CKPT
                              This logfile issue is resolved.
                              How can you judge?
                              However when I issued alter system switch logfile on primary, I did not see anything on standby alertlog.

                              Is that means logs are not shipping to standby site?
                              Do as follows

                              1) cancel MRP on standby
                              2) perform couple of log switches on primary
                              3) watch at alert log for any errors in primary alert log file
                              4) start MRP on standby
                              5) query this on primary and post here
                              SQL> select severity,errror_code,message,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') from v$dataguard_status where dest_id=2;
                              • 12. Re: logfile member shows in database but not on physical disk, not match
                                849425
                                There has been no any entry since I put it in recovery mode (here we are utc time, which means 5 hrs ahead of eastern time)

                                Sun Jul 01 19:29:07 2012
                                Managed Standby Recovery starting Real Time Apply
                                Parallel Media Recovery started with 16 slaves
                                Waiting for all non-current ORLs to be archived...
                                All non-current ORLs have been archived.
                                Sun Jul 01 19:29:07 2012
                                Media Recovery Waiting for thread 1 sequence 480
                                Completed: ALTER DATABASE RECOVER managed standby database disconnect using current logfile

                                The above is the end of alert log.
                                • 13. Re: logfile member shows in database but not on physical disk, not match
                                  849425
                                  How do I judge?

                                  I checked alert log, the logfile errors are all gone and I compared os asm logfiles with db logfiles , they match.

                                  However there has been none any entry in alert log on standby side since I brought them to recovery mode.
                                  • 14. Re: logfile member shows in database but not on physical disk, not match
                                    849425
                                    on primary side, alert log shows:

                                    Sun Jul 01 19:23:34 2012
                                    Thread 1 advanced to log sequence 487 (LGWR switch)
                                    Current log# 2 seq# 487 mem# 0: +prd_DAT/prd/onlinelog/group_2.341.785963735
                                    Sun Jul 01 19:23:35 2012
                                    Archived Log entry 598 added for thread 1 sequence 486 ID 0xf6cf2d1 dest 1:
                                    Sun Jul 01 19:30:50 2012
                                    Thread 1 advanced to log sequence 488 (LGWR switch)
                                    Current log# 1 seq# 488 mem# 0: +prd_DAT/prd/onlinelog/group_1.342.785963735
                                    Sun Jul 01 19:30:50 2012
                                    Archived Log entry 599 added for thread 1 sequence 487 ID 0xf6cf2d1 dest 1:
                                    Sun Jul 01 23:22:10 2012
                                    Thread 1 advanced to log sequence 489 (LGWR switch)
                                    Current log# 5 seq# 489 mem# 0: +prd_DAT/prd/onlinelog/group_5.398.786640519
                                    Sun Jul 01 23:22:11 2012
                                    Archived Log entry 600 added for thread 1 sequence 488 ID 0xf6cf2d1 dest 1:
                                    Thread 1 advanced to log sequence 490 (LGWR switch)
                                    Current log# 2 seq# 490 mem# 0: +prd_DAT/prd/onlinelog/group_2.341.785963735
                                    Sun Jul 01 23:22:14 2012
                                    Archived Log entry 602 added for thread 1 sequence 489 ID 0xf6cf2d1 dest 1:
                                    1 2 3 Previous Next