Analysis of ORA-01200 errors

One reason for triggering ora-01200 errors
 
[Oracle @ oracle ~] $ Oerr ora 1, 01200
01200,000 00, "actual file size of % s is smaller than correct size of % s blocks"
// * Cause: The size of the file as returned by the operating system is smaller
// Than the size of the file as indicated in the file header and
// Control file. Somehow the file has been truncated. Maybe it is
// Result of a half completed copy.
// * Action: Restore a good copy of the data file and do recovery as needed.

The actual number of data files is smaller than the number of data files recorded in the data file header.

Using bbed to construct a ora-01200 Error
 
1. The actual number of data files/oracle/test/jiujian1.dbf occupied is as follows:
 
SQL> select file_name, blocks from dba_data_files where file_id = 11;
 
FILE_NAME BLOCKS
-------------------------------------------------
/Oracle/test/jiujian1.dbf 1048
 
2. Use bbed to modify the number of data files occupied by the Data File Header
BBED> info
File # Name Size (blks)
-------------------
1/oracle/CRM2/system1.dbf 61440
2/oracle/CRM2/zxb. dbf 1280
3/oracle/CRM2/CRM/sysaux01.dbf 37120
4/oracle/CRM2/CRM/users01.dbf 640
5/oracle/CRM2/zxa. dbf 12800
6/oracle/CRM2/CRM/undotbs2.dbf 25600
7/oracle/CRM2/zxc. dbf 1280
8/oracle/CRM2/CRM/zxbig1.dbf 262144
11/oracle/test/jiujian1.dbf 128
 
BBED> set dba 11,1
DBA 0x02c00001 (46137345)
 
BBED> p offset 44
Kcvfh. kcvfhhdr. kccfhfsz
-----------------------
Ub4 kccfhfsz @ 44 0x00000418 (decimal 1048)
 
BBED> set offset 44
OFFSET 44
 
BBED> dump/v count 30
File:/oracle/test/jiujian1.dbf (11)
Block: 1 Offsets: 44 to 73 Dba: 0x02c00001
-------------------------------------------------------
18040000 00200000 0b000300 00000000 l ...............
00000000 00000000 00000000 l ..............
 
<16 bytes per line>
 
BBED> modify/x 1904
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File:/oracle/test/jiujian1.dbf (11)
Block: 1 Offsets: 44 to 73 Dba: 0x02c00001
------------------------------------------------------------------------
19040000 00200000 0b000300 00000000 00000000 00000000 00000000
 
<32 bytes per line>
 
BBED> p offset 44
Kcvfh. kcvfhhdr. kccfhfsz
-----------------------
Ub4 kccfhfsz @ 44 0x00000419 (10 hexadecimal 1049)
 
BBED> sum apply
Check value for File 11, Block 1:
Current = 0x34f4, required = 0x34f4
 
3 Start the database to trigger ora-01200 errors
SQL> startup
ORACLE instance started.
 
Total System Global Area 322961408 bytes
Fixed Size 2020480 bytes
Variable Size 92277632 bytes
Database Buffers 222298112 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/oracle/test/jiujian1.dbf'
ORA-01200: actual file size of 1048 is smaller than correct size of 1049 blocks
 
Three ora-01200 error Solution
 
Method 1 use bbed to change the header offset of the data file to the number of actually occupied parts of the data file.
 
Method 2 use the dd command to fill in the actual data file size as the value in correct size of 1049 blocks
Dd if =/dev/zero of =/oracle/test/jiujian1.dbf bs = 8192 count = 1 seek = 1049

SPFILE error causing database startup failure (ORA-01565)

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting