This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: Jul 2, 2012 7:05 PM by 849425 RSS

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

849425 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Acooper Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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