ORA-00305: log 1 of thread 1 inconsistent; belongs to another database

[Former Member]

Dear All,

I have 2 systems such u201CSystem u201CAu201D and System u201CBu201Du201D,

System A it was working fine but the database got crashed, before that I copied the u201CSAPData, saptrace, sapreorg, sapcheck, saparch, oraarchu201D on my external hard disk through windows command,

Now, I installed System B with the same previous configuration u201Chost name, SID, System number and sap directories pathsu201D, and after successfully installed I try replaced the new directories with directories of System u201CAu201D,

The problem now is cant start the oracle database I got the following error,

ORA-00305: log 1 of thread 1 inconsistent; belongs to another database

ORA-00312: online log 1 thread 1: 'C:\ORACLE\CRD\MIRRLOGA\LOG_G11M2.DBF'

ORA-00305: log 1 of thread 1 inconsistent; belongs to another database

ORA-00312: online log 1 thread 1: 'C:\ORACLE\CRD\ORIGLOGA\LOG_G11M1.DBF'

Unfortunately, I didnu2019t have a backup for the mirrlog, please advice.

Regards.

[volker_borowski2]

Dear All,
> I have 2 systems such u201CSystem u201CAu201D and System u201CBu201Du201D,
> System A it was working fine but the database got crashed, before that I copied the u201CSAPData, saptrace, sapreorg, sapcheck, saparch, oraarchu201D on my external hard disk through windows command,
> Now, I installed System B with the same previous configuration u201Chost name, SID, System number and sap directories pathsu201D, and after successfully installed I try replaced the new directories with directories of System u201CAu201D,
> The problem now is cant start the oracle database I got the following error,
> ORA-00305: log 1 of thread 1 inconsistent; belongs to another database
> ORA-00312: online log 1 thread 1: 'C:\ORACLE\CRD\MIRRLOGA\LOG_G11M2.DBF'
> ORA-00305: log 1 of thread 1 inconsistent; belongs to another database
> ORA-00312: online log 1 thread 1: 'C:\ORACLE\CRD\ORIGLOGA\LOG_G11M1.DBF'
> Unfortunately, I didnu2019t have a backup for the mirrlog, please advice. 
> 
> Regards.

Hi,

so your system A is the one you are trying to recreate ?

And your approach is, to install a new system B and overwrite several files of the new installed system B

with various files previously copied from system A, while system A was up and running?

And you do not have a real database backup of system A at all?

This approach will never work!

Is the "crashed" system A still availible. Or did you destroy it when you installed system B?

May be it can be repaired, if only non/less important parts are defective (or an archiver stuck get's cleaned).

Volker

[Former Member]

Dear Mark,

I execute the command and I got the following,

SQL> @E:\oracle\CRD\saptrace\usertrace\ctl.sql

SP2-0734: unknown command beginning "Dump file ..." - rest of line ignored.

SP2-0734: unknown command beginning "Thu May 05..." - rest of line ignored.

SP2-0734: unknown command beginning "ORACLE V10..." - rest of line ignored.

SP2-0734: unknown command beginning "vsnsql=14 ..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "Oracle Dat..." - rest of line ignored.

SP2-0734: unknown command beginning "Redo threa..." - rest of line ignored.

SP2-0734: unknown command beginning "Oracle pro..." - rest of line ignored.

SP2-0734: unknown command beginning "Windows th..." - rest of line ignored.

SP2-0734: unknown command beginning "*** ACTION..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "*** MODULE..." - rest of line ignored.

SP2-0734: unknown command beginning "*** SERVIC..." - rest of line ignored.

SP2-0734: unknown command beginning "*** SESSIO..." - rest of line ignored.

SP2-0734: unknown command beginning "V10 STYLE ..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "Compatibil..." - rest of line ignored.

SP2-0734: unknown command beginning "Db ID=2081..." - rest of line ignored.

SP2-0734: unknown command beginning "Activation..." - rest of line ignored.

SP2-0734: unknown command beginning "Control Se..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "File Numbe..." - rest of line ignored.

SP2-0734: unknown command beginning "Tablespace..." - rest of line ignored.

SP2-0734: unknown command beginning "Creation ..." - rest of line ignored.

SP2-0734: unknown command beginning "Backup tak..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

SP2-0734: unknown command beginning "reset logs..." - rest of line ignored.

SP2-0734: unknown command beginning "prev reset..." - rest of line ignored.

SP2-0734: unknown command beginning "recovered ..." - rest of line ignored.

SP2-0734: unknown command beginning "status:0x2..." - rest of line ignored.

SP2-0044: For a list of known commands enter HELP

and to leave enter EXIT.

217

Regards,

[Former Member]

Hello Ahmed,

SAP note 549828 has got a description how to recreate the controlfiles,

see the explanations under "Solution": In your case OLDSID and NEWSID

are identical. If the description doesn't work for you, please ask a DBA

who has got some experience with the topic. It is quite difficult to explain

each and every step via the forum.

Regards,

Mark

[Former Member]

Hi All,

There was a problem with Control file, and I update all control files with the new one coming from new-installation, now the error is changed,

SQL> startup

ORACLE instance started.

Total System Global Area 2332033024 bytes

Fixed Size 2067464 bytes

Variable Size 1174406136 bytes

Database Buffers 1140850688 bytes

Redo Buffers 14708736 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: 'E:\ORACLE\CRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

ORA-01202: wrong incarnation of this file - wrong creation time

Regards.

[Former Member]

Hi Ahmed,

After you created new control files, the system resets the SCN and have a new incarnation number. This information stored in the control files. So, you are not able to open the database consistently.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun Gedik,

I agreed with you, but right now there is no way to restore my system?

regards,

[Former Member]

Hi Ahmed,

If someone can say that this system can be restored safely, I can't say anything. But in my opinion, there's no way to restore the system.

Best regards,

Orkun Gedik

[Former Member]

Hello Ahmed,

I am confused. Initially you copied the datafiles and controlfiles from system A,

and the database didn't open because you didn't also copy the online redeologs.

The redologs were still from system B.

Now you are trying to use the controlfiles from system B together with the

datafiles of system A? This cannot work.

BUT you can try to build new controlfiles, provided that you can gather all required

information.

sqlplus "/ as sysdba"
alter database backup controlfile to trace;

Search for the latest tracefile in USER_DUMP_DEST and modify it accordingly (e.g. use

only the RESETLOGS clause).

sqlplus "/ as sysdba"
shutdown abort;
startup nomount;
@"execute that file which you just created"

Good luck,

Mark

[Former Member]

Hi Mark,

Even he create the new control file (as far as I understand that already he created those files, already), there's no way to restore the database safely. So, he will face with the same result.

Best regards,

Orkun Gedik

Edited by: Orkun Gedik on May 5, 2011 3:41 PM

[Former Member]

Hello Mark,

Really I appreciated your support and really I hope to resolve the problem via your recommendations,

Regarding your previous reply, I already applied the fist part, but I canu2019t understand the following part please more clarification,

Search for the latest tracefile in USER_DUMP_DEST and modify it accordingly (e.g. use

only the RESETLOGS clause).

sqlplus "/ as sysdba"

shutdown abort;

startup nomount;

@"execute that file which you just created"

Regards.

[Former Member]

Hello Ahmed,

go to the directory \ORACLE\CRD\SAPTRACE\USERTRACE

and look for the latest tracefile. It should look similar to this:

Dump file /oracle/CRD/saptrace/usertrace/CRD_ora_7083.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/CRD/102_64
System name:    HP-UX
Node name:      isidb20
Release:        B.11.31
Version:        U
Machine:        ia64
Instance name: CRD
Redo thread mounted by this instance: 1
Oracle process number: 46
[,,,]

The create controlfile command can be made from the part between

--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRD" RESETLOGS  ARCHIVELOG
[...]
CHARACTER SET UTF8
;

Make sure that all filenames are correct. Shutdown your database and execute this command,

it will recreate your controlfiles. Afterwards try an:

ALTER DATABASE OPEN RESETLOGS;

Regards,

Mark

[Former Member]

Dear Mark,

Thanks for the details,

I did the following,

I went to directory u201CE:\oracle\CRD\saptrace\usertraceu201D and this is the last file updated u201Ccrd_ora_6344.trcu201D

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.0 Service Pack 1

CPU : 8 - type 8664, 4 Physical Cores

Process Affinity : 0x0000000000000000

Memory (Avail/Total): Ph:6645M/14324M, Ph+PgF:7722M/35701M

Instance name: crd

Redo thread mounted by this instance: 0 <none>

Oracle process number: 0

Windows thread id: 6344, image: ORACLE.EXE (SHAD)

Dynamic strand is set to TRUE

Running with 2 shared and 0 private strand(s). Zero-copy redo is FALSE

Then, I went command line and did the following

C:\Users\crdadm>sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 5 17:40:30 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2332033024 bytes

Fixed Size 2067464 bytes

Variable Size 1174406136 bytes

Database Buffers 1140850688 bytes

Redo Buffers 14708736 bytes

Database mounted.

ORA-01122: database file 1 failed verification check

ORA-01110: data file 1: 'E:\ORACLE\CRD\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

ORA-01207: file is more recent than control file - old control file

SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 2332033024 bytes

Fixed Size 2067464 bytes

Variable Size 1174406136 bytes

Database Buffers 1140850688 bytes

Redo Buffers 14708736 bytes

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "CRD" RESETLOGS ARCHIVELOG [...] CHARACTER S

ET UTF8 ;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT CREA

TE CONTROLFILE REUSE DATABASE "CRD" RESETLOGS ARCHIVELOG [...] CHARACTER SET UT

F8

*

ERROR at line 1:

ORA-00933: SQL command not properly ended

Sorry if I caused any trouble for you

[Former Member]

Hi Ahmed,

Is the database online or offline state, while you backing up your database into the external drive?

If it was online backup, you should recover offline redolog files and then execute "alter database open resetlogs;"

If it was offline backup, you don't need to have online redolog files, so you can execute "alter database open resetlogs;"

Before all of those steps, check the control files. It must be consistent with your database. If they are not consistent with the database, then you need to create the controlfiles, again.

PS: Find a Oracle database copy procedure over the inet and apply the steps.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun Gedik,

First of all many thanks for your support,

Actually I didnu2019t have any backup for the old system, only I restored the files from old system I mentioned before u201CSAPData, saptrace, sapreorg, sapcheck, saparch, oraarchu201D,

I tried to apply

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

Please advice.

[Former Member]

Hello Ahmed,

your error message implies that the control files were copied together with the datafiles,

so they are consistent. No recovery of the database is needed. I am not sure about that,

but maybe clearing the logfiles should be the simplest way to initialize them:

ALTER DATABASE CLEAR LOGFILE 'C:\ORACLE\CRD\ORIGLOGA\LOG_G11M1.DBF';
ALTER DATABASE CLEAR LOGFILE 'C:\ORACLE\CRD\MIRRLOGA\LOG_G11M2.DBF';

Regards,

Mark

[Former Member]

Hi Ahmed,

How did you copied your database? Was it offline during the copy operation?

Best regards,

Orkun Gedik

[Former Member]

Hello Mark,

First of all, thanks for your help,

The following error occurred once I try to apply your recommendation,

SQL> ALTER DATABASE CLEAR LOGFILE 'C:\ORACLE\CRD\ORIGLOGA\LOG_G11M1.DBF';

ALTER DATABASE CLEAR LOGFILE 'C:\ORACLE\CRD\ORIGLOGA\LOG_G11M1.DBF'

*

ERROR at line 1:

ORA-01514: error in log specification: no such log

ORA-01517: log member: 'C:\ORACLE\CRD\ORIGLOGA\LOG_G11M1.DBF'

SQL> ALTER DATABASE CLEAR LOGFILE 'C:\ORACLE\CRD\MIRRLOGA\LOG_G11M2.DBF';

ALTER DATABASE CLEAR LOGFILE 'C:\ORACLE\CRD\MIRRLOGA\LOG_G11M2.DBF'

*

ERROR at line 1:

ORA-01514: error in log specification: no such log

ORA-01517: log member: 'C:\ORACLE\CRD\MIRRLOGA\LOG_G11M2.DBF'

Please advice.

[Former Member]

Hi Orkun Gedik,

I didnu2019t have a backup for the system, the system was up and running and we copied the directories via windows command u201C copy & pasteu201D thatu2019s all.

Thanks.

[Former Member]

Hi Ahmed,

Everything is clear now. One more question is; do you have offline redolog files under oraarch?

Best regards,

Orkun Gedik

[Former Member]

HI Orkun Gedik,

Yes, i have them.

Regards,

Ahmed

[Former Member]

Hi Ahmed,

Sorry, I forget to ask that have you to executed "alter database begin backup;" before you copy operation. After the respective copy operation, you need to execute "alter database end backup;". Then, you can recover the offline redolog files, respectively.

So as far as I can understand that your db copy cannot be used.

Best regards,

Orkun Gedik

[Former Member]

Hi Ahmed,

Sorry, I forget to ask that have you to executed "alter database begin backup;" before you copy operation. After the respective copy operation, you need to execute "alter database end backup;". Then, you can recover the offline redolog files, respectively.

So as far as I can understand that your db copy cannot be used.

Best regards,

Orkun Gedik