February 12, 2011

simulate one ORA-01161, solving for ORA-01161 and ORA-01189

Recently we hit:
ORA-01161:database name ... in file header does not match given name of ...
when try to recreate controlfile with command "create controlfile SET database...".

I simulate it here again.

Following is a newly installed database which only have 5 datafiles, and Its db name is NIGHT.
SQL> select name,open_mode from v$database;
NAME    OPEN_MODE
------- ----------
NIGHT   READ WRITE

SQL> select name,status from v$datafile;
NAME                                      STATUS
----------------------------------------- -------
/u01/app/oracle/oradata/day/system01.dbf  SYSTEM
/u01/app/oracle/oradata/day/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/day/sysaux01.dbf  ONLINE
/u01/app/oracle/oradata/day/users01.dbf   ONLINE

We can see currently the DB is up and running with its db_name NIGHT.
Now let's shutdown the DB and change db_name in one datafile and see what will happen:
[oracle@jishu1 ~]$ bbed
BBED: Release 2.0.0.0.0 - Limited Production on Sat Feb 12 22:28:08 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************

BBED> dump
File: /u01/app/oracle/oradata/day/users01.dbf (4)
Block: 1 Offsets: 0 to 511 Dba:0x01000001
------------------------------------------------------------------------
0ba20000 01000001 00000000 00000104 ba570000 00000000 0000200a f62845d4
4e494748 54000000 b6000000 00050000 00200000 04000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
..............

Translate the red part Hex code into Character:
SQL> select UTL_RAW.CAST_TO_VARCHAR2('4e49474854000000') from dual;
UTL_RAW.CAST_TO_VARCHAR2
---------------
NIGHT

These 8 bytes in datafile header represent db_name.
Now we change the db_name  from NIGHT to DAY:
SQL> select dump('DAY',16) from dual;
DUMP('DAY',16)
--------------------------
Typ=96 Len=3: 44,41,59

BBED> modify /x 4441590000 offset 32

BBED> dump block 1 offset 0
File: /u01/app/oracle/oradata/day/users01.dbf (4)
Block: 1 Offsets: 0 to 511 Dba:0x01000001
------------------------------------------------------------------------
0ba20000 01000001 00000000 00000104 ba570000 00000000 0000200a f62845d4
44415900 00000000 b6000000 00050000 00200000 04000300 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
..............

Done. Now db_name in users01.dbf is DAY, while in ohter datafiles still NIGHT.
Let's go to startup the database to see what will happen:
SQL> startup
ORACLE instance started.
Database mounted.
Database opened.

None warning or error, it seems ORACLE won't check the db_name value among datafiles header in an normal startup.

Then let's see what will happen when we recreate the controlfile:
SQL> startup nomount force;
ORACLE instance started.

SQL> create controlfile SET database "NIGHT" resetlogs noarchivelog
.................
10 DATAFILE
11 '/u01/app/oracle/oradata/day/system01.dbf',
12 '/u01/app/oracle/oradata/day/undotbs01.dbf',
13 '/u01/app/oracle/oradata/day/sysaux01.dbf',
14 '/u01/app/oracle/oradata/day/users01.dbf';
*
ERROR at line 1:
ORA-01161: database name DAY in file header does not match given name of NIGHT
ORA-01110: data file 4: '/u01/app/oracle/oradata/day/users01.dbf'

See? We hit the error even when we are using SET key word.

Let's Try to set db name to DAY.
Do some needed change in init.ora.
Then:
SQL> startup force nomount;
ORACLE instance started.

SQL> create controlfile SET database "DAY" resetlogs noarchivelog
     ..........;
*
ERROR at line 1:
ORA-01161: database name DAY in file header does not match given name of NIGHT
ORA-01110: data file 4: '/u01/app/oracle/oradata/day/users01.dbf'

OK. We reproduce the ORA-01161 case again.

Is there some reguler methods that we can fix this problem without edit datafille directly?
Yes, there is. I will show you. But before that, one thing you need to be aware:
If you got ORA-01161 in production enviroment, not only db_name is different, SCN and resetlog count must be different either!
Easy to understand right?


So there are 3 issues need us to slove;
1.db_name
2.resetlogs count
3.SCN
For 3.SCN, add hidden parameter "_allow_resetlogs_corruption=TRUE" can solve this. Do it.

Then let's fix the 1.db_name:
SQL> create controlfile SET database "DAY" resetlogs noarchivelog
..............
11 DATAFILE
12 '/u01/app/oracle/oradata/day/system01.dbf',
13 '/u01/app/oracle/oradata/day/undotbs01.dbf',
14 '/u01/app/oracle/oradata/day/sysaux01.dbf'
    ----(not involve users01.dbf);
Control file created.

Pay attention, the database name in datafile header is still unchanged, ORACLE will change it only when
alter database open step. So we need to open the database once:
SQL> alter database open resetlogs;
Database altered.

OK, now db_name in all datafiles is "DAY".  So we go to recreate the controlfile again.
SQL> shutdown immediate;
SQL> startup nomount;
ORACLE instance started.


SQL> CREATE CONTROLFILE set DATABASE "DAY" RESETLOGS NOARCHIVELOG
...............
11 DATAFILE
12 '/u01/app/oracle/oradata/day/system01.dbf',
13 '/u01/app/oracle/oradata/day/undotbs01.dbf',
14 '/u01/app/oracle/oradata/day/sysaux01.dbf',
15 '/u01/app/oracle/oradata/day/users01.dbf';
*
ERROR at line 1:
ORA-01189: file is from a different RESETLOGS than previous files
ORA-01110: data file 4: '/u01/app/oracle/oradata/day/users01.dbf'

Great, we don't get ORA-01161 anymore, 1.db_name issue is fixed , now only resetlog count issue left. Let's continue.

Still create control file without users01.dbf:
SQL> CREATE CONTROLFILE set DATABASE "DAY" RESETLOGS NOARCHIVELOG
...............
11 DATAFILE
12 '/u01/app/oracle/oradata/day/system01.dbf',
13 '/u01/app/oracle/oradata/day/undotbs01.dbf',
14 '/u01/app/oracle/oradata/day/sysaux01.dbf';
Control file created.

SQL> alter database open resetlogs;
Database altered.

SQL> SELECT FILE#,NAME,STATUS,CHECKPOINT_CHANGE# FROM V$DATAFILE;
FILE#    NAME   STATUS         CHECKPOINT_CHANGE
---- ---------------------------------------------------- ----------
1    /u01/app/oracle/oradata/day/system01.dbf             SYSTEM     188330
2    /u01/app/oracle/oradata/day/undotbs01.dbf            ONLINE     188330
3    /u01/app/oracle/oradata/day/sysaux01.dbf             ONLINE     188330
4    /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00004 RECOVER       0

SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00004' to '/u01/app/oracle/oradata/day/users01.dbf';
Database altered.

SQL> alter database datafile 4 online;
alter database datafile 4 online;
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u01/app/oracle/oradata/day/users01.dbf'

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Database mounted.

SQL> recover until cancel;
Media recovery complete.

SQL> alter database datafile 4 online;
Database altered.

Pay very attention to my commands order, you can't disorder it.
For example, if you execute "alter databas datafile 4 online" before "recover until cacel".
you will got:
ORA-00600: internal error code, arguments: [krhpfh_03-1202], [fno =], [*], [fhcrt =], [*], [cptim =], [0], []
Because newly created redolog know nothing about users01.dbf. The low-cache rba in users01.dbf point to a wrong place.

SQL> alter database open resetlogs;
Database altered.

Congratulations! All work is done now. Please remenber to delete the hidden parameter _ALLOW_RESETLOGS_CORRUPTION from init file later.


Ps:  I got some mails asking me how can they get the error while they never used some tools to modify db_name in datafile header directly.


Below are some possible reasons from me:
1.  data block corruption
2.  oracle's bug
3.  when you first time create controlfile with SET clause, you missed some datafiles.  And db_name in those datafiles remain old one while in other datafiles changed to new one. And finally you found you missed some datafiles, and wanted to re-create controlfile with all datafiles correctly, then you will hit our case.

12 Comments:

ahamad said...

Kevin,

You the man. Very nice post and eliminated one of our most dreaded fear of getting ORA-01161, while creating control files.
Thanks

Ahmed

Anonymous said...

This is a little gem, your "resetlog count issue" part may have saved my job, very valuable! Thanks Kevin.

Anonymous said...

thanks. this worked for me.

Anonymous said...

Kevin,

Thank You for this amazing artical, yesterday was stuck with the same problem.
Just want to say really appreciate you.

Niaz

Unknown said...

Kevin,

Will this work if the file that has a different name in Header is an UNDO datafile ?

When i try to exlude it and do open resetlogs, I hit ORA-600.

Thanks

Kevin Zhang said...

If undo tablespace is the only tablespace that have this problem, then you can use below method to skip this tablespace on the premise that you can allow a little inconsistent of some data:
1.add/change below parameter in pfile:
undo_tablespace='SYSTEM'
undo_management='MANUAL'
*._allow_resetlogs_corruption=true
*._corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$)

2. open resetlogs DB without undo datafile.

3. drop the old undotablespace with contents and datafile.

4. create a new undotablespace, and then revert back the previous change in pfile.

5. bounce the DB

Kevin

Anonymous said...

Thanks mate, really helped me to find a way through

Excellent work !!!

Anonymous said...

Awesome tips...it helped us for missing datafiles during system refresh...

Thanks

Bobek said...

Hi Kevin,

Thank you very much for this blog! You helped us a lot. We were near to restart the database restore (2TB) because of wrong control file creation but finally we found your blog.

Peter

JAMSHER KHAN said...

Awesome Explanation

Anonymous said...

Dude..

You have just saved my day!!!
Awesome fix and explanation.

Thank you very much :D

Anonymous said...

You are a ROCKSTAR!!! Thanks for this it saved my ass. :)

-Clive (Accenture)

Post a Comment