Causes and Solutions of ORA-01200 errors

1. Case Study
When the database is started, an error is returned:
[Oracle @ localhost ~] $ Sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Mon Sep 19 19:31:05 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 202445884 bytes
Fixed Size 451644 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/u01d/sysaux01.dbf'
ORA-01200: actual file size of 38400 is smaller than correct size of 51200 blocks
2. Problem Analysis
SQL> select status from v $ instance;
STATUS
------------
MOUNTED
SQL>
The error indicates that the actual size and correct size of the data file are small.
SQL> col name for a50
SQL> select file #, status, bytes/1024/1024 mb, name from v $ datafile;
FILE # STATUS MB NAME
-----------------------------------------------------------------------------
1 SYSTEM 300/u01/u01d/system01.dbf
2 ONLINE 100/u01/u01d/undotbs01.dbf
3 SYSTEM 400/u01/u01d/sysaux01.dbf
4 ONLINE 25/u01/u01d/users01.dbf
5 ONLINE 25/u01/u01d/indx01.dbf
6. ONLINE 100/u01/u01d/perfstat. dbf
7 ONLINE 10/u01/u01d/timi01.dbf
6 rows selected.
SQL>
It can be seen that the control file records 400 M, while du-sh system01.dbf returns 301 M. Next, read the online help documentation:
[Oracle @ localhost ~] $ 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
// Controlfile. 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.
[Oracle @ localhost ~] $
It can be seen that the actual size of the data file is different from the size recorded in the control file and the header of the data file. Generally, this is caused by database exceptions. During database operation, when the file is restarted, it may be encountered when an exception occurs after the file is resize. Next let's take a look at how to recover to open the database:
3. Determine the solution
First, let's take a look at the dump data file header:
SQL> alter session set events 'immediate trace name FILE_HDRS level 10 ';
Session altered.
SQL> @/u01/admin/mytools/myscripts/gettrcname. SQL
TRACE_FILE_NAME
Bytes --------------------------------------------------------------------------------------------------------------
/U01/admin/denver/udump/denver_ora_4669.trc
SQL>
[Oracle @ localhost ~] $ More/u01/admin/denver/udump/denver_ora_5349.trc
.....
File header:
Software vsn = 153092096 = 0x9200000, Compatibility Vsn = 134217728 = 0x8000000
Db ID = 4004057640 = 0xeea91228, Db Name = 'Denver'
Activation ID = 0 = 0x0
Control Seq = 1908 = 0x774, File size = 51200 = 0xc800 // visible here the size is 51200, while the ORA-01200 reports that the actual size is 38400
File Number = 1, blksiz= 8192, File Type = 3 DATA
Tablespace #0-SYSTEM rel_fn: 1
.....
Solution determination:
1. If there is a backup, backup can be used for recovery.
2. The worst case is to extract the data from the file and reload it to the database.
3. It is best to use some special means to restore the file and run it ONLINE again.
4. Use Metalink and various approaches to find known solutions.
At this point, no backup is found. No way. You only need to use the bbed tool for unconventional recovery.
4. Use the BBED tool to restore
Bbed is short for block browse block edit. It is a tool used to directly view and modify data files. This tool is not provided on windows. on linux, compilation is required:
Make-f ins_rdbms.mk BBED = $ ORACLE_HOME/rdbms/lib/bbed $ ORACLE_HOME/bin/bbed
Then add $ ORACLE_HOME/rdbms/lib to the PATH of the environment variable, and then you can directly run bbed in the command. However, there is a default password blockedit. Before Running bbed, you must write several configuration files by yourself:
There are two files, respectively, file.txt and par. bbd.
[Oracle @ localhost ~] $ Ls
File.txt bbed. par
[Oracle @ localhost ~] $ More bbed. par
Blocksize = 8192
Listfile =/home/oracle/file
Mode = edit
[Oracle @ localhost ~] $ More file.txt
3/free/oracle/oradata/orcl/sysaux01.dbf 1614807040
5. Recovery Process
Next we can use the BBED tool:
[Oracle @ localhost ~] $ Bbed
Password:
BBED: Release 2.0.0.0.0-Limited Production on Tue Sep 20 18:19:33 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
*************!!! For Oracle Internal Use only !!! ***************
BBED>

BBED> modify/x 00960000 offset 44 --- Use find/x c8 curr to find the location where 51200 is located, because the hexadecimal 38400 is 9600, Which is 0096 according to the inverted rule.
File:/u01/u01d/system01.dbf (1)
Block: 1 Offsets: 44 to 555 Dba: 0x00400001
------------------------------------------------------------------------
00960000 00200000 01000300 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 a1014000 07000000 00000000
4bbaa02a 9aabd02a 0e3a0f00 00000000 00000000 00000000 00000000
7a061000 00000000 86a7dd2a 01005162 01000000 8d660000 1000 ffbf 02000000
00000000 6c010000 54abd02a 6b010000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 06005359 5354454d 00000000 00000000 00000000
00000000 00000000 00000000 01000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0a000a00 9cefc52a
5c0a0d00 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> sum apply
Check value for File 1, Block 1:
Current = 0x1459, required = 0x1459
BBED> quit
Try to open the database:
SQL> alter database open;
Database altered.
SQL> col name for a50
SQL> select file #, status, bytes/1024/1024 mb, name from v $ datafile;
FILE # STATUS MB NAME
-----------------------------------------------------------------------------
1 SYSTEM 300/u01/u01d/system01.dbf
2 ONLINE 100/u01/u01d/undotbs01.dbf
3 SYSTEM 300/u01/u01d/sysaux01.dbf --- the control file records 300 MB, and the database has been successfully opened.
4 ONLINE 25/u01/u01d/users01.dbf
5 ONLINE 25/u01/u01d/indx01.dbf
6. ONLINE 100/u01/u01d/perfstat. dbf
7 ONLINE 10/u01/u01d/timi01.dbf
6 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 202445884 bytes
Fixed Size 451644 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
OK. The database is opened successfully.
6. Summary
The specific cause of the ORA-01200 error has been clear, but what is the cause of this database fault, has not been studied. However, it should be certain that the database should immediately perform an export and re-import operation.
 
Author's "Technology Achievement dream"