Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Standby Logfile Member
shailendra11
post Sep 21 2009, 03:46 PM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 21-September 09
Member No.: 41,797



HI ,

I have got a wired situation where my standby database keep giving me below error.

ORA-00313: open failed for members of log group 2 of thread 1
Mon Sep 21 15:22:36 2009
Errors in file /ora01/app/oracle/admin/esbasep1/bdump/esbasep1_arc7_7288.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Mon Sep 21 15:22:36 2009
Errors in file /ora01/app/oracle/admin/esbasep1/bdump/esbasep1_arc7_7288.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Mon Sep 21 15:22:36 2009
Primary database is in MAXIMUM PERFORMANCE mode
Mon Sep 21 15:22:36 2009

Now i did check and found that few logfile members of standby database are invalid.

SQL> select name,open_mode ,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
XXXXXXXX MOUNTED PHYSICAL STANDBY

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
------- ------------------------------------------------------------ ---
14 INVALID ONLINE /ora14/oradata/esbasep1/redo14a.log NO
14 ONLINE /ora08/oradata/esbasep1/redo14b.log NO
13 ONLINE /ora09/oradata/esbasep1/redo13a.log NO
13 ONLINE /ora11/oradata/esbasep1/redo13b.log NO
12 ONLINE /ora01/oradata/esbasep1/redo12a.log NO
12 ONLINE /ora12/oradata/esbasep1/redo12b.log NO
10 ONLINE /ora13/oradata/esbasep1/redo10a.log NO
10 ONLINE /ora05/oradata/esbasep1/redo10b.log NO
11 ONLINE /ora02/oradata/esbasep1/redo11a.log NO
11 ONLINE /ora15/oradata/esbasep1/redo11b.log NO
1 INVALID STANDBY /ora16/oradata/esbasep1/redo1a.log NO

GROUP# STATUS TYPE MEMBER IS_
------- ------------------------------------------------------------ ---
1 STANDBY /ora01/oradata/esbasep1/redo1b.log NO
2 STANDBY /ora01/oradata/esbasep1/redo2a.log NO
2 INVALID STANDBY /ora16/oradata/esbasep1/redo2b.log NO
3 STANDBY /ora01/oradata/esbasep1/redo3a.log NO
3 INVALID STANDBY /ora16/oradata/esbasep1/redo3b.log NO
4 STANDBY /ora01/oradata/esbasep1/redo4a.log NO
4 INVALID STANDBY /ora16/oradata/esbasep1/redo4b.log NO
5 STANDBY /ora16/oradata/esbasep1/redo5a.log NO
5 STANDBY /ora01/oradata/esbasep1/redo5b.log NO
1 STANDBY /ora16/oradata/esbasep1/redo1A.log NO
============================================================

I further checked and found that invalid logfile members doesn't exists at OS level.

SQL> ! ls /ora16/oradata/esbasep1/redo1a.log
/ora16/oradata/esbasep1/redo1a.log: No such file or directory

SQL> ! ls /ora16/oradata/esbasep1/redo2b.log
/ora16/oradata/esbasep1/redo2b.log: No such file or directory

SQL> ! ls /ora16/oradata/esbasep1/redo4b.log
/ora16/oradata/esbasep1/redo4b.log: No such file or directory

SQL> ! ls /ora16/oradata/esbasep1/redo3b.log
/ora16/oradata/esbasep1/redo3b.log: No such file or directory

Now when I am trying to drop these invalid member's getting below error.

SQL> alter database drop standby logfile member '/ora16/oradata/esbasep1/redo1a.log';
alter database drop standby logfile member '/ora16/oradata/esbasep1/redo1a.log'
*
ERROR at line 1:
ORA-00261: log 1 of thread 1 is being archived or modified
ORA-00312: online log 1 thread 1: '/ora16/oradata/esbasep1/redo1a.log'
ORA-00312: online log 1 thread 1: '/ora01/oradata/esbasep1/redo1b.log'
ORA-00312: online log 1 thread 1: '/ora16/oradata/esbasep1/redo1A.log'

SQL> alter database drop standby LOGFILE '/ora16/oradata/esbasep1/redo1a.log';
alter database drop standby LOGFILE '/ora16/oradata/esbasep1/redo1a.log'
*
ERROR at line 1:
ORA-01514: error in log specification: no such log
ORA-01517: log member: '/ora16/oradata/esbasep1/redo1a.log'

now when i tried to re use that log member again getting below error.

SQL> ALTER DATABASE ADD standby LOGFILE MEMBER '/ora16/oradata/esbasep1/redo1a.log' reuse to group 1 ;
ALTER DATABASE ADD standby LOGFILE MEMBER '/ora16/oradata/esbasep1/redo1a.log' reuse to group 1
*
ERROR at line 1:
ORA-01577: cannot add log file '/ora16/oradata/esbasep1/redo1a.log' - file already part of database

Now this is something i am not able to understand how to outcome of this situation? I want to get rid of these invalid log members from database. help is appreciated.
Go to the top of the page
 
+Quote Post
aquiles
post Sep 21 2009, 04:24 PM
Post #2


Newbie
*

Group: Banned
Posts: 8
Joined: 21-September 09
From: Mexico City
Member No.: 41,798



QUOTE (shailendra11 @ Sep 21 2009, 03:47 PM) *
HI ,

I have got a wired situation where my standby database keep giving me below error.

ORA-00313: open failed for members of log group 2 of thread 1
Mon Sep 21 15:22:36 2009
Errors in file /ora01/app/oracle/admin/esbasep1/bdump/esbasep1_arc7_7288.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Mon Sep 21 15:22:36 2009
Errors in file /ora01/app/oracle/admin/esbasep1/bdump/esbasep1_arc7_7288.trc:
ORA-00313: open failed for members of log group 2 of thread 1
Mon Sep 21 15:22:36 2009
Primary database is in MAXIMUM PERFORMANCE mode
Mon Sep 21 15:22:36 2009

Now i did check and found that few logfile members of standby database are invalid.

SQL> select name,open_mode ,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
XXXXXXXX MOUNTED PHYSICAL STANDBY

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
------- ------------------------------------------------------------ ---
14 INVALID ONLINE /ora14/oradata/esbasep1/redo14a.log NO
14 ONLINE /ora08/oradata/esbasep1/redo14b.log NO
13 ONLINE /ora09/oradata/esbasep1/redo13a.log NO
13 ONLINE /ora11/oradata/esbasep1/redo13b.log NO
12 ONLINE /ora01/oradata/esbasep1/redo12a.log NO
12 ONLINE /ora12/oradata/esbasep1/redo12b.log NO
10 ONLINE /ora13/oradata/esbasep1/redo10a.log NO
10 ONLINE /ora05/oradata/esbasep1/redo10b.log NO
11 ONLINE /ora02/oradata/esbasep1/redo11a.log NO
11 ONLINE /ora15/oradata/esbasep1/redo11b.log NO
1 INVALID STANDBY /ora16/oradata/esbasep1/redo1a.log NO

GROUP# STATUS TYPE MEMBER IS_
------- ------------------------------------------------------------ ---
1 STANDBY /ora01/oradata/esbasep1/redo1b.log NO
2 STANDBY /ora01/oradata/esbasep1/redo2a.log NO
2 INVALID STANDBY /ora16/oradata/esbasep1/redo2b.log NO
3 STANDBY /ora01/oradata/esbasep1/redo3a.log NO
3 INVALID STANDBY /ora16/oradata/esbasep1/redo3b.log NO
4 STANDBY /ora01/oradata/esbasep1/redo4a.log NO
4 INVALID STANDBY /ora16/oradata/esbasep1/redo4b.log NO
5 STANDBY /ora16/oradata/esbasep1/redo5a.log NO
5 STANDBY /ora01/oradata/esbasep1/redo5b.log NO
1 STANDBY /ora16/oradata/esbasep1/redo1A.log NO
============================================================

I further checked and found that invalid logfile members doesn't exists at OS level.

SQL> ! ls /ora16/oradata/esbasep1/redo1a.log
/ora16/oradata/esbasep1/redo1a.log: No such file or directory

SQL> ! ls /ora16/oradata/esbasep1/redo2b.log
/ora16/oradata/esbasep1/redo2b.log: No such file or directory

SQL> ! ls /ora16/oradata/esbasep1/redo4b.log
/ora16/oradata/esbasep1/redo4b.log: No such file or directory

SQL> ! ls /ora16/oradata/esbasep1/redo3b.log
/ora16/oradata/esbasep1/redo3b.log: No such file or directory

Now when I am trying to drop these invalid member's getting below error.

SQL> alter database drop standby logfile member '/ora16/oradata/esbasep1/redo1a.log';
alter database drop standby logfile member '/ora16/oradata/esbasep1/redo1a.log'
*
ERROR at line 1:
ORA-00261: log 1 of thread 1 is being archived or modified
ORA-00312: online log 1 thread 1: '/ora16/oradata/esbasep1/redo1a.log'
ORA-00312: online log 1 thread 1: '/ora01/oradata/esbasep1/redo1b.log'
ORA-00312: online log 1 thread 1: '/ora16/oradata/esbasep1/redo1A.log'

SQL> alter database drop standby LOGFILE '/ora16/oradata/esbasep1/redo1a.log';
alter database drop standby LOGFILE '/ora16/oradata/esbasep1/redo1a.log'
*
ERROR at line 1:
ORA-01514: error in log specification: no such log
ORA-01517: log member: '/ora16/oradata/esbasep1/redo1a.log'

now when i tried to re use that log member again getting below error.

SQL> ALTER DATABASE ADD standby LOGFILE MEMBER '/ora16/oradata/esbasep1/redo1a.log' reuse to group 1 ;
ALTER DATABASE ADD standby LOGFILE MEMBER '/ora16/oradata/esbasep1/redo1a.log' reuse to group 1
*
ERROR at line 1:
ORA-01577: cannot add log file '/ora16/oradata/esbasep1/redo1a.log' - file already part of database

Now this is something i am not able to understand how to outcome of this situation? I want to get rid of these invalid log members from database. help is appreciated.


HI.

may i have more information

Oracle version?
what type of replication?
what type of nodes?
etc..

Go to the top of the page
 
+Quote Post
shailendra11
post Sep 21 2009, 04:39 PM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 21-September 09
Member No.: 41,797



QUOTE (aquiles @ Sep 21 2009, 04:25 PM) *
HI.

may i have more information

Oracle version?
what type of replication?
what type of nodes?
etc..


Database : 10.2.0.4.0
OS : SunOS ndcebit003 5.9 Generic_122300-18 sun4u sparc SUNW,Sun-Fire-V890
It's non cluster database. ( NO RAC )
Go to the top of the page
 
+Quote Post
JGarmany
post Sep 21 2009, 05:42 PM
Post #4


Advanced Member
***

Group: Members
Posts: 359
Joined: 18-March 04
Member No.: 57



Your standby is a copy of the primary.

Copy the primary log files to the standby in the appropriate directory.

The online redo logs are not used in standby mode.

Only standby redo logs (if they exist) are actually used when in standby mode, but you will need the logs when you switch over.

You are not going to be able to drop or create the logs, copy then from the source database.

Please read this for details:

http://www.dba-oracle.com/t_standby_redo_log_recovery.htm
Go to the top of the page
 
+Quote Post
shailendra11
post Sep 21 2009, 06:55 PM
Post #5


Newbie
*

Group: Members
Posts: 4
Joined: 21-September 09
Member No.: 41,797



QUOTE (JGarmany @ Sep 21 2009, 05:43 PM) *
Your standby is a copy of the primary. Copy the primary log files to the standby in the appropriate directory. The online redo logs are not used in standby mode. Only standby redo logs (if they exist) are actually used when in standby mode, but you will need the logs when you switch over. You are not going to be able to drop or create the logs, copy then from the source database.

www.dba-oracle.com/t_standby_redo_log_recovery.htm


here as you can see only one redo member from online log group is showing as Invaid rest are from standby log group. So you meant to say i just copy all these redo group members from my primary and it should work ?
Go to the top of the page
 
+Quote Post
JGarmany
post Sep 22 2009, 04:24 PM
Post #6


Advanced Member
***

Group: Members
Posts: 359
Joined: 18-March 04
Member No.: 57



QUOTE (shailendra11 @ Sep 21 2009, 11:56 PM) *
here as you can see only one redo member from online log group is showing as Invaid rest are from standby log group. So you meant to say i just copy all these redo group members from my primary and it should work ?


When I create a standby I create the standby redo logs on the prod database, then copy all file, datafiles, control file (these get overwriten with the standby control file) online redo logs and standby redo logs to the standby.

The online redo logs are not used on the standby while in standby mode, they are cleared during the standby activation. The standby redo logs are not used on prod until it is converted into a standby.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th October 2014 - 07:16 PM