今天忽然想到一个问题,数据库使用裸设备,且一个主机上有2个instance,如果划分lv的时候没有注意名称上的区别,那么在使用的时候,是否会出现一个lv已经分配给了某一个instance,由于instance之间是独立了,裸设备也是不经过os层面,不会锁定的,因此可以把这个lv分配给另一个instance呢?
测试一下,发现oracle还是会报错的:
1 2 3 4 5 6 7 8 |
SQL> create tablespace ts_test datafile '/dev/vg_bak03/rdata_1g_240' size 1023M; create tablespace ts_test datafile '/dev/vg_bak03/rdata_1g_240' size 1023M * ERROR at line 1: ORA-01119: error in creating database file '/dev/vg_bak03/rdata_1g_240' ORA-27086: skgfglk: unable to lock file - already in use HP-UX Error: 13: Permission denied Additional information: 8 |
由于os层面不可能锁定lv,估计是在oracle级能够识别出来这个文件是,把数据文件头dump出来发现,确实在数据文件头有相关的标识表示数据文件已经被某一个数据库使用,在数据文件头已经有了db id的信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
DATA FILE #79: (name #89) /dev/vg_bak03/rdata_1g_075 creation size=130944 block size=8192 status=0xe head=89 tail=89 dup=1 tablespace 36, index=37 krfil=79 prev_file=78 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00 Checkpoint cnt:2 scn: 0x0000.0031091f 11/14/2007 13:56:27 Stop scn: 0xffff.ffffffff 11/14/2007 13:56:27 Creation Checkpointed at scn: 0x0000.00310915 11/14/2007 13:56:27 thread:1 rba:(0x34.a0e.10) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Offline scn: 0x0000.00000000 prev_range: 0 Online Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Hot Backup end marker scn: 0x0000.00000000 aux_file is NOT DEFINED FILE HEADER: Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000 Db ID=264392167=0xfc24de7, Db Name='TESTDB' Activation ID=0=0x0 Control Seq=655=0x28f, File size=130944=0x1ff80 File Number=79, Blksiz=8192, File Type=3 DATA Tablespace #36 - DATA_HISTORY rel_fn:79 Creation at scn: 0x0000.00310915 11/14/2007 13:56:27 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 reset logs count:0x25e3b727 scn: 0x0000.00000001 recovered at 01/01/1988 00:00:00 status:0x4 root dba:0x00000000 chkpt cnt: 2 ctl cnt:1 begin-hot-backup file size: 0 Checkpointed at scn: 0x0000.0031091f 11/14/2007 13:56:27 thread:1 rba:(0x34.a17.10) enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 Backup Checkpointed at scn: 0x0000.00000000 thread:0 rba:(0x0.0.0) enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 External cache id: 0x0 0x0 0x0 0x0 Absolute fuzzy scn: 0x0000.00000000 Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00 Terminal Recovery Stamp scn: 0x0000.00000000 01/01/1988 00:00:00 |
在这里,我们看到了:
Db ID=264392167=0xfc24de7, Db Name=’TESTDB’
说明这个数据文件归dbid为264392167所有。
另外还有一个比较有意思的现象:rdata_1g_240是加到数据库中做数据文件的,rdata_1g_237是尚未加入到数据库中的裸设备,由于一次错误的操作,在数据库open的状态下,将rdata_1g_237 dd到了rdata_1g_240,但是这个时候还能在rdata_1g_240的表空间上面建表,insert数据,数据库没有挂,这是怎么回事呢?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
oracle@local_db:/dev/vg_bak03>dd if=/dev/vg_bak03/rdata_1g_237 of=/dev/vg_bak03/rdata_1g_240 bs=4096 262144+0 records in 262144+0 records out oracle@local_db:/dev/vg_bak03> exit SQL> SQL> create table a (aaa number(9)) tablespace xxx; Table created. SQL> insert into a values(100); 1 row created. SQL> commit; Commit complete. SQL> alter system set events = 'immediate trace name flush_cache'; System altered. SQL> SQL> select * from dual; D - X |
其实这也是数据文件头的关系,此时我create表,insert数据,commit,甚至flush buffer cache,但是都不会有任何报错,也就是说,写数据写入到数据文件,还是可以的。
但是一旦做checkpoint,就直接当数据库了,因为做checkpoint,会检查其数据文件头的情况,此时会发现数据文件头已经被破坏。
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select status,error, from v$datafile_header where name like '%240%'; STATUS ERROR NAME ------- ------------------ --------------------------- ONLINE WRONG FILE TYPE /dev/vg_bak03/rdata_1g_240 SQL> alter system checkpoint; alter system checkpoint * ERROR at line 1: ORA-03113: end-of-file on communication channel |
注意上面的error字段为WRONG FILE TYPE,正常的应该为YES。
要解决这个问题,只能startup mount,在对被破坏的数据文件offline drop,再open数据库后,drop tablespace。