Shulou(Shulou.com)06/01 Report--

This article is to share with you about the reasons why the database encountered ORA-01200 errors. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Case phenomenon

In the database startup, an error is reported:

[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, 2005, 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:'/ u01max u01dUniple 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 report says that the actual size of the data file is smaller than the correct size.

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 >

You can see that the control file records 400m, while the du-sh system01.dbf result is 301m. Next, check the online help documentation:

[oracle@localhost ~] $oerr ora 01200

01200, 00000, "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 the

/ / controlfile. Somehow the file has been truncated. Maybe it is the

/ / 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. It is generally caused by database exceptions, which may be encountered during the operation of the database, when restarting, and when an exception occurs after the file resize. Let's see how to restore to open the database:

3. Determine the solution

First, dump the data file header and take a look at:

SQL > alter session set events' immediate trace name FILE_HDRS level 10'

Session altered.

SQL > @ / u01/admin/mytools/myscripts/gettrcname.sql

TRACE_FILE_NAME

-

/ 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 / / see that the size here is 51200, while ORA-01200 reports that the actual size is 38400

File Number=1, Blksiz=8192, File Type=3 DATA

Tablespace # 0-SYSTEM rel_fn:1

.

Determination of the solution:

1. If there is a backup, you can use backup to restore.

two。 The worst-case scenario is to extract the data from the file and reload it into the database.

3. The best-case scenario is to restore the file and run ONLINE again by some special means.

4. Find known solutions through Metalink and various channels.

At this point, it is found that there is no backup at all. There is no choice but to use the bbed tool for unconventional recovery.

4. Restore with BBED tool

Bbed is an acronym for block browse block edit, which is used to view and modify data files directly. This tool is not available on windows, but on linux, it needs to be compiled:

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 you can bbed directly in the command. But there is a default password, blockedit. You need to write a few configuration files yourself before running bbed:

It is assumed that there are two files, 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, you 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, 2005, Oracle. All rights reserved.

*! For Oracle Internal Use only!! *

BBED >

BBED > modify / x 00960000 offset 44-use find / x c8 curr to find out where 51200 is, because the hexadecimal number of 38400 is 9600, which is 0096 according to the inversion rule.

File: / u01/u01d/system01.dbf (1)

Block: 1 Offsets: 44 to 555 Dba:0x00400001

00960000 00200000 01000300 00000000 00000000 00000000

00000000 00000000 00000000 a1014000 07000000 00000000

4bbaa02a 9aabd02a 0e3a0f00 00000000 00000000 00000000 00000400

7a061000 00000000 86a7dd2a 01005162 01000000 8d660000 1000ffbf 02000000

00000000 6c010000 54abd02a 6b010000 00000000 00000000 00000000

00000000 00000000 00000000

00000000 00000000 00000000 06005359 5354454d 00000000 00000000 00000000

00000000 00000000 00000000 01000000 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

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-300m is recorded in the control file, and the database has been opened successfully

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, successfully opened the database.

Thank you for reading! This is the end of the article on "what are the reasons for ORA-01200 errors in the database?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.