Database is not Opening. Errors are coming due to Copy Mismatch of Log file

[former_member524429]

Hello Friends,

I did some mistakes and deleted all log files after closing SAP QAS System.

I was trying to Resizing the Redo Log files, as per as SNOTE 309526.

All control files are consistent. Only due to Log File mismatch, Database is not Opening.

Here, is the error.

*

ERROR at line 1:

ORA-00322: log 14 of thread 1 is not current copy

ORA-00312: online log 14 thread 1: '/oracle/RQ1/mirrlogB/log_g14m2.dbf'

ORA-00322: log 14 of thread 1 is not current copy

ORA-00312: online log 14 thread 1: '/oracle/RQ1/origlogB/log_g14m1.dbf'

In working condition, Previously the file was with this name log_g11m1.dbf......

I had replaced all the files, which i had backedup ,before deleting those Log files. But, still this kind of error is coming.

I tried to "reset log", but it failed.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

I did all the activities after Closing SAP System.

I want your Quick help, as its critical for me.

Regards

Bhavik Shroff

Edited by: Bhavik G. Shroff on Nov 8, 2008 3:49 PM

[stefan_koehler · Accepted solution]

Hello Bhavik,

> I did some mistakes and deleted all log files after closing SAP QAS System.

If you have no member of the current or active online logroup you are really in trouble, but if you have a consistent offline backup of everything (datafiles and controlfiles) or your database was shutdown cleanly at this point, you are a lucky man

> ERROR at line 1:

> ORA-01139: RESETLOGS option only valid after an incomplete database recovery

This error is based on the UNTIL clause in the recovery scenario.

You can only execute an OPEN RESETLOGS after a recovery with the UNTIL clause.

I have tested your scenario on a test database with oracle 10.2.0.4

Just take a look at here:

SQL> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
         1          1         68   52428800          1 NO  CURRENT                3716197 08-NOV-08
         2          1         66   52428800          1 YES INACTIVE               3648097 06-NOV-08
         3          1         67   52428800          1 YES INACTIVE               3689993 07-NOV-08

shell> deleted all members of every online redolog group (the important one in your case is the CURRENT one)

SQL> startup
ORACLE instance started.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/TST/oradata/redolog/redo01.log'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

SQL> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
         1          1          1   52428800          1 YES INACTIVE               3735838 08-NOV-08
         2          1          2   52428800          1 NO  CURRENT                3735839 08-NOV-08
         3          1          0   52428800          1 YES UNUSED                       0

Regards

Stefan

[former_member524429 · Accepted solution]

Hello Stefan,

Thanks for your help.

I tried whatever u suggested.

Still, Database is not stared, as its asking for some Unexpected Offline RedoLog file, which is even generated yet, in SAP System.

I think I did some mistake and missing something silly.

May be The SCN number mismatching is playing this Game.

Actually, I switched Logfiles, before dropping LogFile Group 11. Then i created again the same LogFile Group 11 with the same logfile names in origlogA & mirrlogA. But it was giving the errors as, already the old redo log files were there with the same name. So, I created those files with other new name. It was giving the same error for LogFile group 13. So I took backup of those folders origlogA, mirrlogA, origlogB and mirrlogB, before deleting log*.dbf files.

In mid of these process I executed "alter database switch logfiles" frequently, just to see the switching operations. I think the Deletion Steps and Switching Steps were my mistakes.

I did all these things after Shutting Down SAP System, but i think in this kind of situation, The Point-in-Time Recovery will be required, ultimately. All the Data files are Consistent, Control Files are consistent.

Only, due to These inconsistent Redo Log Files, I will have to choose Point in Time Recovery.

I think there is something little gape is there, which is causing this error. May be change number mismatch with respect to the control files and in the log files.

I am waiting for your precious response.

Thanks & Regards

Bhavik Shroff

[stefan_koehler · Accepted solution]

Hello Bhavik,

to be honest i can not follow your description what you have done.

Please post the output of the following 2 SQL statements:

> shell> sqlplus "/ as sysdba"

> SQL> startup mount

> SQL> set linesize 700

> SQL> SELECT * FROM V$LOG;

> SQL> SELECT * FROM V$LOGFILE;

If you need urgent support on this topic - open a sap call for that.

Regards

Stefan

[former_member524429 · Accepted solution]

Hi Stefan,

SQL> set linesize 700

SQL> SELECT * FROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRS

T_CHANGE# FIRST_TIM

-

---

-

---

-

---

-

---

-

---

--- -

---

-

---

-

---

-

---

11 1 22292 104857600 2 NO INVALIDATED

156909413 08-NOV-08

13 1 0 104857600 2 YES UNUSED

0 08-NOV-08

14 1 22291 20971520 2 YES ACTIVE

156909138 08-NOV-08

SQL> SELECT * FROM V$LOGFILE;

GROUP# STATUS TYPE MEMBER

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

-

---

11 ONLINE /oracle/RQ1/origlogA/log_g11_m1.dbf

11 ONLINE /oracle/RQ1/mirrlogA/log_g11_m2.dbf

13 ONLINE /oracle/RQ1/origlogA/log_g13_m1.dbf

13 ONLINE /oracle/RQ1/mirrlogA/log_g13_m2.dbf

14 ONLINE /oracle/RQ1/origlogB/log_g14m1.dbf

14 ONLINE /oracle/RQ1/mirrlogB/log_g14m2.dbf

6 rows selected.

SQL>

Can i change the redolog files back to log_g11m1.dbf, which was the memebr of log group 11, previously ? It was the first one file that i by mistakely deleted after creating log_g11_m1.dbf ?

Regards

Bhavik Shroff

[former_member524429 · Accepted solution]

Hi Stefan,

Also see this.

r3qas:orarq1 64> sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Sun Nov 9 02:10:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning option

JServer Release 9.2.0.6.0 - Production

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/RQ1/sapdata1/system_1/system.data1'

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-00314: log 11 of thread 1, expected sequence# 22292 doesn't match 22289

ORA-00312: online log 11 thread 1: '/oracle/RQ1/mirrlogA/log_g11_m2.dbf'

ORA-00314: log 11 of thread 1, expected sequence# 22292 doesn't match 22286

ORA-00312: online log 11 thread 1: '/oracle/RQ1/origlogA/log_g11_m1.dbf'

SQL>

Reagards

Bhavik Shroff

[former_member524429 · Accepted solution]

Hi,

Starting Database using SAPDBA;

______________________________________________________________________________

Startup local instance RQ1

______________________________________________________________________________

INSTANCE STATUS : mounted

CONNECTED PROGRAMS: 2

a - Startup normal

b - Startup RESTRICT

c - Startup NOMOUNT

d - Startup MOUNT

e - Refresh

q - Return

Please select ==> a

• SAPDBA - Startup instance RQ1 ************************

APDBA: Trying to open instance RQ1

using ALTER DATABASE OPEN ...

QL*Plus: Release 9.2.0.6.0 - Production on Sun Nov 9 02:14:16 2008

opyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

QL> Connected.

QL> ALTER DATABASE OPEN

RROR at line 1:

RA-00322: log 14 of thread 1 is not current copy

RA-00312: online log 14 thread 1: '/oracle/RQ1/mirrlogB/log_g14m2.dbf'

RA-00322: log 14 of thread 1 is not current copy

RA-00312: online log 14 thread 1: '/oracle/RQ1/origlogB/log_g14m1.dbf'

QL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Pro

uction

ith the Partitioning option

Server Release 9.2.0.6.0 - Production

APDBA: Error during start instance 'RQ1'.

Press <return> to continue ...

Regards

Bhavik Shroff

[stefan_koehler · Accepted solution]

Hello Bhavik,

with your given information (the query from above and the posted ORA-errors) - unfortunately i have to say .. you are almost lost, i really advise you to get professional support on your system for that. Let me explain why.

The information from the query

SQL> SELECT * FROM V$LOG;
GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
    11          1      22292  104857600          2 NO  INVALIDATED      156909413     08-NOV-08
    13          1          0  104857600          2 YES UNUSED           0             08-NOV-08
    14          1      22291   20971520          2 YES ACTIVE           156909138     08-NOV-08

SQL> SELECT * FROM V$LOGFILE;
GROUP# STATUS  TYPE    MEMBER
11         ONLINE  /oracle/RQ1/origlogA/log_g11_m1.dbf
11         ONLINE  /oracle/RQ1/mirrlogA/log_g11_m2.dbf
13         ONLINE  /oracle/RQ1/origlogA/log_g13_m1.dbf
13         ONLINE  /oracle/RQ1/mirrlogA/log_g13_m2.dbf
14         ONLINE  /oracle/RQ1/origlogB/log_g14m1.dbf
14         ONLINE  /oracle/RQ1/mirrlogB/log_g14m2.dbf

and the ORA-00322 error:

ORA-00322: log 14 of thread 1 is not current copy
ORA-00312: online log 14 thread 1: '/oracle/RQ1/mirrlogB/log_g14m2.dbf'
ORA-00322: log 14 of thread 1 is not current copy

As you can see your online redo log group 14 has the status ACTIVE, that means that this online redolog files are needed for a crash recovery (not all needed data was already flushed to the data files), but the files from the online redo log group 14 are lost (deleted accidentally). But you have really luck that this online redologfile is already archived (see query ARC = YES) so you must have a archivelog file with the sequence number 22291.

The problem in your case is that the recover is performed through the online redolog files (normally done by a complete recovery), but the online redolog files are already deleted. To fix this issue you have to restore an older backup and make a recover trough the archive log files (including the sequence 22291 which was already archived) and not through the online redolog groups.

At this point this is the only way to get a running database again (but maybe with data lost).

The other serious problem is the redolog group 11, it seems like this group was the CURRENT redolog (take a look at the sequence and the SCNs) and you will lose this data.

I can imagine that the database was not shutdown clearly at the moment of the redolog file deletion - the most indications are pointing to this.

Sorry that i have no better news for you.. please get professional support who can execute the necessary steps for you.

Regards

Stefan

[former_member524429 · Accepted solution]

Hi Stefan,

I am going to Restore + Recover Database.

I did All those activities after SHUTTING DOWN SAP QAS System. So, i think there will be not more data generated , which are having Offline Redo Log files

Let see, what will happen ?

Regards

Bhavik Shroff

[stefan_koehler · Accepted solution]

Hello Bhavik,

> I did All those activities after SHUTTING DOWN SAP QAS System.

But this is not the important part if SAP was down or not.. the important one is that your database was also shutdown cleanly. The situation above seems like this was not the case.

> Let see, what will happen ?

Of course

Regards

Stefan

[former_member524429 · Accepted solution]

Hi Stefan,

Now, My Database is now recovered. Its running now. Also, SAP QAS System is up and running nice.

I tried lot of alternatives to resolve that Problem without applying Restore + Recovery Options.

But, Ultimately I needed to do Database Point-in-Time Recovery with the help of ArchiveLog Sequence Number.

It succeeded.

But, I am thinking that Whether this kind of situation requires Database Recovery , due to Online Redo Log files related errors.

I think the better way to Resize the Online Redo log File will be ...

1. If possible Take Online Consistent Backup of Database.

2. Switching Log files which are in use, otherwise u will not be able to drop the Log File Group.

3. If necessary, Drop the Log File Group, if you want to reuse it.

or

3. Create the (New) Group with its associated Redo Log File Members, including Mirrored Members with new Size.

If you want to delete those Old Files then , Please take the Whole Backup of origlogA, origlogB, mirrlogA, mirrlogB and all control files.

Thanks a lot Stefan for your cooperations.

Regards

Bhavik Shroff