2GB or Not 2GB - File Limits in Oracle
Translation: Kamus (Seraphim)
Correction: bloomit
Email: kamus@itpub.net
Date: 2004-1
When the introduction is often heard, when the backup is restored, the file exceeds 2G size when the SQL * Loader imports the data, and the result is errors.
I have graduated from my textbook, what binary, sixteen, data structure, operating system, etc. have not learned, so there is only one blurred understanding of this issue. Today, I will go shopping on Metalink, and I suddenly discovered this article. The reason is, decided to look at it, after reading, I feel a lot for this issue, so I will be translated into Chinese by the way, I hope to help everyone.
All other Notes and BUGs mentioned herein have also been made as much as possible (not translated, if there is time to make it slowly), but some bugs mentioned in the article are also the limit of the MetAlink account or bugs. The unobstructure does not see detailed description, and it is no longer possible for such bugs.
Original source: http://metalink.racle.com
DOC ID: NOTE: 62427.1 Original Creation Date:
1998-9-2
Original text last update date:
2003-8-6
Introduction
This article describes the "2GB" problem, explains why 2GB will be a figures full of magic, and if you want to use more than 2GB size files in Oracle applications, then this article also tells some things you should know. .
This paper is based on UNIX operating system because most 2GB problems occur on UNIX. Of course, some information about other non-UNIX operating systems are also mentioned, and each operating system is listed in this article. limit.
This topic includes the following points:
l Why is 2GB a special number?
l How to use more than 2GB (2GB ) file?
l Export (export) and 2GB
l SQL * LoAder and 2GB
l Oracle and other 2GB issues
l "Large file" on different operating systems
Why is 2GB a special number?
Many CPUs and APIs currently in use have used 32-bit (BIT) word length, which is the impact on many operations.
The standard API of the file operation under many occasions uses a symbol 32-bit word (32-bit Signed Word) in the current location of the file size and file. A symbol 32 bit is indicated by the highest bit, so only 31 points are left to store real values, and the maximum positive value stored in 31 bits in the 16-based bits is 0x7fffffff, that is, 10 envelopes. 2147483647, this is a value close to 2GB.
2GB or more files are generally called "big files", when you use 2147483647 or even more than the 2147483647, you can encounter some problems. In order to solve these problems, the latest operating system has redefined a series of system functions that fully utilize 64-bit addressing methods to operate file size and offset. The latest Oracle distribution has also been used in these new interfaces, but if you decide to use the "big file", you still have a lot of problems need to be considered.
Another special number is 4GB. That is, the hexadecimal digital 0xffffffffffff (decimal 4294967295) as unaigned value is (decimal 4294967295), which is a value that is slightly less than 4GB. Plus the value will make the low 4-bit byte become 0x00000000, and the '1' carry is generated. This carry will be lost in the 32-bit operation. So 4GB is also a special number that may have problems. This issue also mentioned in this article. What does this mean for using Oracle?
32bit problems affect Oracle in many ways, in order to use "big files", you need to meet the following conditions:
1. A operating system or naked device that supports 2GB files (Raw Devices)
2. A operating system with an API supporting access 2GB file
3. A Oracle version using these APIs
Most of the platforms have supported large files today, and these APIs have generally been used for these files, Oracle7.3, and later, but according to different platforms, different operating systems and different Oracle versions are still. There are a lot of different situations. In some cases, the default is to support "big files", but some other occasions may have to play some patches.
When you write this article, there are some tools in Oracle that are not updated to use these new APIs, such as well-known export and sql * loader, but still emphasize that due to platform and operating systems, Still not the same.
Why use 2GB files?
In this section, we tried to summarize the use of large files and devices for Oracle data files ("" Large "files / devices) and disadvantages.
Using the advantages of greater than 2GB files:
l On most platforms, Oracle7 supports up to 1022 data files. If the file is less than 2G, then it limits the size of the database that can only be less than 2044GB. Of course, this is no longer a problem for Oracle8 that supports more data files (Oracle8 supports up to 1022 data files per table space).
l In reality, the maximum database size of Oracle7 is smaller than 2044GB, because the general data files are stored in separate tablespaces, and many data files may be far less than 2GB. Using big files can make the database beyond the limit of 2044GB.
l Using big files means that only small files are required for smaller databases.
l Need less file processing resources.
Use the shortcomings of greater than 2GB files:
l The unit of recovery is larger. A 2GB file backup and restore, depending on the difference between the backup media and disk speed, it takes 15 minutes to 1 hour, then an 8GB file is 4 times the time.
l Backup and recovery parallel operation will receive an impact.
l Touch some platform unique restrictions, such as the operation of asynchronous I / O over 2GB, may become linear operations in more than 2GB of asynchronous I / O.
l Processing 2GB or more files may require patches or some special configurations. There will be greater risk relative to small documents. For example, in some AIX issued, more than 2GB, asynchronous I / O use linear operations.
Use more than 2GB files:
l Confirm with the operating system provider, whether the big file is supported, how to configure it.
L is confirmed with the operating system provider, how much is the real maximum file limit?
l Ask Oracle Technical Support, determine if you need a patch or what limit is required for your existing platform, operating system version, Oracle version, what is it necessary? l Remember, if you really think about the how to make some patchs for the operating system or Oracle, then check the problems mentioned above.
l Confirm that the limitations of the operating system have been set correctly for all users who want to use large files.
l Confirm that all backup scripts can handle big files.
l Attention to the use of more than 2GB of data files, there is still a limit for maximum file size. This limit relies on your system platform and Oracle initialization parameter db_block_size. Limitations on most platforms (including UNIX, NT, VMS) file size are so large in 4194302 * db_block_size.
[NOTE: 112011.1] Document describes the problems existing in the size of the file, especially when more than 2GB.
The main points that generally need to pay attention:
Be careful to set the automatic extension of the file. Wise the method is to limit the maximum size of the automatic extended data file below 2GB without using the "big file". Also pay attention to [BUG: 568232] will define a maxSize value that exceeds the Oracle processing limit, which will trigger an internal error after resize (error is displayed as ORA-600 [3292])
On many platforms, the heads of the Oracle data file contain additional data blocks, so creating a 2GB data file actually requires more disk space than 2GB. The additional data block size of the data file header on the UNIX platform is usually equal to the size of DB_BLOCK_SIZE, but there may be more space on the bare device.
2GB-related Oracle error
The following errors may occur when 2GB is reached, and these errors have no specific order.
ORA-01119 Error In Creating DataFile XXXX
ORA-27044 Unable to Write Header Block of File
SVR4 Error: 22: Invalid Argument
ORA-19502 Write Error On File 'FileName', BlockNo X (Blocksize = NN)
ORA-27070 SKGFDISP: Async Read / Write Failed
ORA-02237 INVALID FILE SIZE
KCF: Write / Open Error DBA = xxxxxx block = xxxx online = xxxx file = xxxxxxxix file limit exceed.
Unix Error 27, EFBIG
