One day I got a ticket, a sqlload tkt: import data into one table from an CSV file The CSV file is:
/x368/backup/app01/home/ctort/cdi_city_cort_final.csv
And the target table's structure is：
SYS @ ctort > desc ctortALAPP01.t_bpc_cleaning
Name Type
-----------------------------
COUNTRY VARCHAR2(200)
ACTUALCOUNTRY VARCHAR2(200)
STATE VARCHAR2(200)
ACTUALSTATE VARCHAR2(200)
CITY VARCHAR2(200)
ACTUALCITY VARCHAR2(200)
below are some rows in the cdi_city_cort_final.csv file：
US,UNITED STATES,MI,MICHIGAN,ADRIAN,ADRIAN
US,UNITED STATES,NC,NC,ADVANCE,ADVNCE
US,UNITED STATES,MN,MN,AFTON,AFTON
US,UNITED STATES,MA,MA,AGAWAM,AGAWAM
The columns and fileds are one-to-one correspondence, it seems an simple work, and the sqlload is done without any issue.
User confirmed after a quick check , all rows were there.
But the next day, user came to me again, they encounter some problem.
I check the table as what they told me, but all data seems OK, no issue:
SYS @ ctort > select CITY,ACTUALCITY from ctortALAPP01.t_bpc_cleaning where country ='US' and state = 'KS' and rownum<2;
CITY ACTUALCITY
--------------------------
MCCRACKEN MCCRACKEN
User blablabla said much, but i still don't understand what the issue is. So the user share the screen to me.
Now i understand.
This table:ctortALAPP01.t_bpc_cleaning
When user connect through sql developer，and randam select one row from the table：
SYS @ ctort > select CITY,ACTUALCITY from ctortALAPP01.t_bpc_cleaning where country ='US' and state = 'KS' and rownum<2;
CITY ACTUALCITY
--------------------------
MCCRACKEN MCCRACKEN
then user try to copy and paste the rows content to another wordpad file,
when he copy the column CITY's content MCCRACKEN，what he paste still shows excatly MCCRACKEN，It is OK.
But when he copy the column ACTUALCITY's MCCRACKEN，after paste into txt file, it shows "MCCRACKEN ".
Yes, you don't misread , it shows "" symbol and mytiply space.
WHY?
The user is confused with this so he came to me again.
Now let's check into it.
It is easy to guess it is because some invisible character exist in the fileds ACTUALCITY.
Let's find out what it is:
SYS @ ctort > select CITY,ACTUALCITY from ctortALAPP01.t_bpc_cleaning where country ='US' and state = 'KS' and rownum<2;
CITY ACTUALCITY
--------------------------
MCCRACKEN MCCRACKEN
SYS @ ctort > select dump(CITY,16), dump(ACTUALCITY,16) from ctortALAPP01.t_bpc_cleaning where country ='US' and state = 'KS' and rownum<5;
DUMP(CITY,16)
-----------------------
DUMP(ACTUALCITY,16)
-----------------------
Typ=1 Len=9: 4d,43,43,52,41,43,4b,45,4e
Typ=1 Len=10: 4d,43,43,52,41,43,4b,45,4e,d
We can see clear now, there is one more "d" at the end of ACTUALCITY column.
This character can't show in the sqlplus, but once user copy and paste, we can see it.
So, where is this d come from？
Go to check my sqlload command again, it is nothing wrong.
Then the problem must come from the CSV file which offered by the user. Let's go to check it:
cihcisddb735[oracle]_ctort> cat /x368/backup/app01/home/ctort/cdi_city_cort_final.csv|more
COUNTRY,ACTUALCOUNTRY,STATE,ACTUALSTATE,CITY ,ACTUALCITY
DE,GERMANY,,,AACHEN,AACHEN
DK,DENMARK,,,AALBORG,AALBORG
.................
Obviously we can't find any clue from cat command.
Let's check the file into HEX：
vi cdi_city_cort_final.csv
and then input:
：%！xxd
HEX code is below:
0000000: 434f 554e 5452 592c 4143 5455 414c 434f COUNTRY,ACTUALCO
0000010: 554e 5452 592c 5354 4154 452c 4143 5455 UNTRY,STATE,ACTU
0000020: 414c 5354 4154 452c 4349 5459 202c 4143 ALSTATE,CITY ,AC
0000030: 5455 414c 4349 5459 0d0a 4445 2c47 4552 TUALCITY..DE,GER
0000040: 4d41 4e59 2c2c 2c41 4143 4845 4e2c 4141 MANY,,,AACHEN,AA
0000050: 4348 454e 0d0a 444b 2c44 454e 4d41 524b CHEN..DK,DENMARK
................
Go through it, very soon, we concentrate on the 0d0a which exist at end of each row.
Why it is 0d0a？
In linux the wrap symbol should only be 0a.
Only when in windows the wrap row symbol is 0d0a.
And the extra 0d caused above problem to us.
Now we already can guess that this CSV file is come from windows not linux.
Go to check with user：
Kevin : 2:41:48 AM is the cdi_city_cort_final.csv file, generate from windows?
Mural : 2:42:04 AM yes
OK,till now the whole thing comes to light.
Now that we found out the reason, there are mutiply methods to fix this.
We can copy the CSV file to windows enviroment and sqlload again from windows client.
But what if the CSV file is big or we don't want to reload again?
Don't worry, we can directly remove the last Hex code "0d" from the filed with oracle provided function.
You may ask:
We can remove last word "p" from string "testp" by:
rtrim("p" from "testp")
But How to trim an invisible character? We even can't see it! We even can't type it out though keybord!
Don't worry, because we already know the ASCII code for the invisible character is 0d, we can remove it in below way:
rtrim(Input_String,UTL_RAW.CAST_TO_VARCHAR2('0d'))
UTL_RAW.CAST_TO_VARCHAR2 is used to convert HEX code back to character. It is the opposite of dump function.
More......
GE, AGFA. experienced in 9i/10g/11g/12c, AdvanceReplication, Stream, GoldenGate, RAC, DataGuard, ODA, EXADATA, GridControl. In my career I have always been assigned to handle most complicated and critical oracle issues without solution online, and I hope to record some of them via this blog.
October 26, 2011
one small case about Invisible Character
April 15, 2011
Low HWM ,High HWM and Direct Path Write
An ASSM segment has two High Water Marks. One is Low High Water Mark, another is High High Water Mark.
Blocks under Low HWM are definitely all formated, other wise if there exist unformatted block exist under Low HWM, then you will get ORA-08103: object no longer exists.
Blocks between Low HWM and High HWM are either can be formatted and unformtted.
Blocks above High HWM are definitely un formatted.
When oracle perform a scan on the segment, it will read blocks one by one from it till the LHWM. Then Oracle will use the bitmap to only read the formatted block between LHWM and HHWM.
And the HHWM is the endline, oracle never read the blocks above HHWM.
When we perform a Direct Path Write, oracle will first sync the LHWM and HHWM (means format all unformated blocks between LHWM and HHWM), then oracle will insert above the LHWM---mean while it is already same with HHWM.
Let's see:
Target table TESTHW Segment header:
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 18 #blocks: 384
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0140018c(High HWM) ext#: 17 blk#: 3 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 259
mapblk 0x00000000 offset: 17
Disk Lock:: Locked by xid: 0x0010.02b.00000009
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0140018c(Low HWM) ext#: 17 blk#: 3 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 259
mapblk 0x00000000 offset: 17
Level 1 BMB for High HWM block: 0x01400189
Level 1 BMB for Low HWM block: 0x01400189
--------------------------------------------
After truncate the segment:
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0140001c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by xid: 0x000d.028.0000000a
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0140001c ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01400019
Level 1 BMB for Low HWM block: 0x01400019
Insert some rows, now the LHWM not equal HHWM:
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 18 #blocks: 384 --segment owns 18 extents
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01400189 ext#: 16 blk#: 128 ext size: 128 --HHWM on the 17 extent
#blocks in seg. hdr's freelists: 0
#blocks below: 244
mapblk 0x00000000 offset: 16
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01400099 ext#: 15 blk#: 8 ext size: 8 --LHWM on the 17 extent
#blocks in seg. hdr's freelists: 0
#blocks below: 118
mapblk 0x00000000 offset: 15
Level 1 BMB for High HWM block: 0x0140010a
Level 1 BMB for Low HWM block: 0x01400089
Direct Path Load(/*+Append */) one row, pay attention, only one row:
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 18 #blocks: 384
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0140018c ext#: 17 blk#: 3 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 259
mapblk 0x00000000 offset: 17
Disk Lock:: Locked by xid: 0x000d.029.0000000a
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0140018c ext#: 17 blk#: 3 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 259
mapblk 0x00000000 offset: 17
Level 1 BMB for High HWM block: 0x01400189
Level 1 BMB for Low HWM block: 0x01400189
We can see it just behave as what i describle at the beginning.
But why HHWM move from 0x01400189 to 0x0140018c not to 0x0140018b?
This is because 0x0140018a is metadata block, so the rowdata is insert into the following block 0x0140018b, so the HHWM is 0x0140018c now.
More......
March 26, 2011
ORA-00600: internal error code, arguments: [ktspfmthwm], [28339], [28350], [], [], [], [], []
This week, come across a new ORA-00600. Search on metalink and google, none recored return.
So paste it here.
Fri Mar 25 02:10:48 2011
Errors in file /s611/dba01/oracle/dump01/cubes/udump/cubes_ora_23620.trc:
ORA-00600: internal error code, arguments: [ktspfmthwm], [28339], [28350], [], [], [], [], []
First argument is ktspfmthwm, kt means kernel transaction, sp means space(check), fmt means format. hwm means "High Water Mark".
cubes_ora_23620.trc：
Current SQL statement for this session:
INSERT /*+ SYS_DL_CURSOR */ INTO SSS_LOADER_STG.FND_DOCUMENTS_TL
("DOCUMENT_ID","CREATION_DATE","CREATED_BY","LAST_UPDATE_DATE","LAST_UPDATED_BY","LAST_UPDATE_LOGIN","LANGUAGE","DESCRIPTION","SHORT_TEXT","FILE_NAME","MEDIA_ID","REQUEST_ID","PROGRAM_APPLICATION_ID","PROGRAM_ID","PROGRAM_UPDATE_DATE","DOC_ATTRIBUTE_CATEGORY","DOC_ATTRIBUTE1","DOC_ATTRIBUTE2","DOC_ATTRIBUTE3","DOC_ATTRIBUTE4","DOC_ATTRIBUTE5","DOC_ATTRIBUTE6","DOC_ATTRIBUTE7","DOC_ATTRIBUTE8","DOC_ATTRIBUTE9","DOC_ATTRIBUTE10","DOC_ATTRIBUTE11","DOC_ATTRIBUTE12","DOC_ATTRIBUTE13","DOC_ATTRIBUTE14","DOC_ATTRIBUTE15","APP_SOURCE_VERSION","SOURCE_LANG"
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)；
Call Stack:
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
..........omit .........
kgeasnmierr()+163 call kgerinv() 0068A38A0 ? 007A4F420 ?
7FFF014969A0 ? 7FFF014968E0 ?
000000000 ? 000000000 ?
ktspfmthwm()+737 call kgeasnmierr() 0068A38A0 ? 007A4F420 ?
7FFF014969A0 ? 7FFF014968E0 ?
000000000 ? 000006EB3 ?
ktsmg_seg()+3500 call ktspfmthwm() 2B18AB3A4A08 ? 000000003 ?
000000000 ? 7FFF014968E0 ?
000000000 ? 000006EB3 ?
kdblfpl()+1544 call ktsmg_seg() 2B18AB3A4A08 ? 7FFF014983E0 ?
000000003 ? 000000000 ?
000000000 ? 800000000 ?
..........omit .........
The Hint /*+ SYS_DL_CURSOR */ is very similiar with /*+APPEND */，when use sqlldr to perform a direct path load, you could see it.
From ktspfmthwm()+737，we know the first argument ktspfmthwm is oracle's function name. And the second parameter 28339 is one of the function's parameter 000006EB3。
Now that the ORA600 looks somehow related with High Water Mark, let's make a dump to check.
Segment header dump：
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 28350 #blocks: 124812
last map 0x9401d550 #maps: 28 offset: 5452
Highwater:: 0xa181c085 ext#: 28339 blk#: 4 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 124768
mapblk 0x9401d550 offset: 297
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0xa181c085 ext#: 28339 blk#: 4 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 124768
mapblk 0x9401d550 offset: 297
Level 1 BMB for High HWM block: 0x9fc1d6c1
Level 1 BMB for Low HWM block: 0x9fc1d6c1
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 16384 fbsz: 0
L2 Array start offset: 0x00002994
First Level 3 BMB: 0x9f800217
L2 Hint for inserts: 0x944000b6
Last Level 1 BMB: 0x9fc1d6dd
Last Level II BMB: 0x9401d93e
Last Level III BMB: 0x9401d93f
Map Header:: next 0x9f8003b0 #extents: 647 obj#: 403905 flag: 0x10000000
Inc # 0
Now we know exact meaning of this ORA600, the second argument 28339 is High High Water Mark's Position.(Maybe low high water mark)
The High High Water Mark is on the 28339+1=28340th extent.
The third argument 28350 means this segment has 28350 extents.
Under low high water mark, all block are formated. Between low high and high high water mark, blocks could be either formated or not. Above high high water mark, all blocks are unformated.
Pay attention to "Last Level III BMB: 0x9401d93f", It is a level three bit map block
Check it through, none wired place found, so we dump the first level bit map block where exist the high high water mark and low high water mark：
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 3 parent dba: 0x9401d926 poffset: 2
unformatted: 0 total: 12 first useful block: 1
owning instance : 1
instance ownership changed at 03/25/2011 02:10:47
Last successful Search 03/25/2011 02:10:47
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 2
Extent Map Block Offset: 4294967295
First free datablock : 10
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 403905
HWM Flag: HWM Set
Highwater:: 0xa181c085 ext#: 28339 blk#: 4 ext size: 4
#blocks in seg. hdr's freelists: 0
#blocks below: 124768
mapblk 0x9401d550 offset: 297
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x9fc1d6c1 Length: 4 Offset: 0
0xa001e7e5 Length: 4 Offset: 4
0xa181c081 Length: 4 Offset: 8
0:Metadata 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:FULL 7:FULL
8:FULL 9:FULL 10:75-100% free 11:75-100% free
--------------------------------------------------------
End dump data blocks tsn: 55 file#: 639 minblk 120513 maxblk 120513
..........omit .........
More......
March 1, 2011
Add Column Internal
Yesterday, got one ticket, add column to a table. In order to make sure it wouldn't effect the running application based on that table, I make a "Add Column" experiment to test its influence.
Conclusion is:
alter table xxx add column nullable is a very light operation. Oracle only update the dictionary table with out making a full table scan. So even a huge big table can finish immediately.
On the contrary,
alter table xxx add column not null, Oracle will scan the segment and check and update all the blocks. It may cost very long time if the table is big. And because it will hold exclusive TM lock, all other operation on that table will be blocked until the "Add Column" command finished.
Following is my experiment :
SQL> conn bbed/oracle
Connected.
SQL> create table xxx (c1 int,c2 char(2));
Table created.
SQL> insert into xxx values(1,'a');
1 row created.
SQL> commit;
Commit complete.
SQL> select dump(rowid,16) from xxx;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,0,27,68,1,40,0,f,0,0
#From the rowid we know this row is in file 5 block 15.
SQL> alter system flush buffer_cache;
System altered.
#Check the row data in block. "alter system dump datafile 5 block 15" can't show you the real information.
BBED> set file 5 block 15
FILE# 5
BLOCK# 15
BBED> map
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Dba:0x0140000f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8059] @120
ub1 rowdata[9] @8179
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8179 0x2c
BBED> set offset 8179
OFFSET 8179
BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8179 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c010202 c1020261 20030641 a7
<32 bytes per line>
#Now we add a nullable column.
SQL> alter table xxx add (c3 varchar2(20));
Table altered.
SQL> alter system flush buffer_cache;
System altered.
#Check the row data in block again.
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8179 0x2c
BBED> set offset 8179
OFFSET 8179
BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8179 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c010202 c1020261 20030641 a7
<32 bytes per line>
#We can see the row data is not changed.
#Let's continue to add a not null column.
SQL> alter table xxx add (c4 varchar2(20) default 'dba' not null);
Table altered.
SQL> alter system flush buffer_cache;
System altered.
#Check the row data in block again.
BBED> map
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Dba:0x0140000f
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @100
struct kdbt[1], 4 bytes @114
sb2 kdbr[1] @118
ub1 freespace[8045] @120
ub1 rowdata[23] @8165
ub4 tailchk @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8165 0x2c
BBED> set offset 8165
OFFSET 8165
BBED> dump
File: /duckdb/oracle/duck/data/bbed01.dbf (5)
Block: 15 Offsets: 8165 to 8191 Dba:0x0140000f
------------------------------------------------------------------------
2c020402 c1020261 20ff0364 62612c00 0202c102 02612001 06c4a8
<32 bytes per line>
#We can see the oracle had change the row data on the block from "2c00 0202c102 026120" to "2c020402 c1020261 20ff0364 6261".
Now we know, if we want to add a column in production database, we had better choose nullable column.
Some people may got a question. If oracle add nullable column without modify block, then what will happen when oracle want to read data from block?
Oracle will do in this way: when read row data from block and only find tow columns, meanwhile oracle know the table should have 4 columns from col$, then oracle will add value "null" to following tow columns.
More......
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.
More......