Two supporting processes in the Oracle Database System

We all know that Import and Export are two related utilities in the Oracle database system. The main function of Export is to unload some data in the relevant Oracle database and Import it to the operating system file, the Import utility restores the data detached from the Export to the database. The import process sometimes takes several hours or even days to complete successfully.

Unfortunately, in many cases, the import needs to recover the Oracle database from a fault, and the time requirement is high. Therefore, this performance is not competent. During the two-year period in ORACLE 7 system management, the author summarized several basic parameter changes that can reduce the import time.

I. System-level changes

1. Increase the DB_BLOCK_SIZ parameter whenever possible during database reconstruction. The larger the block size, the less I/O cycles are required. This change is permanent, so the possible effects should be taken into account when modifying it.

2. Create and use a large rollback segment, and disable other rollback segments (offline ). A rollback segment requires about 50% of the maximum table size.

3. Place the Oracle database in NOARCHIVELOG mode before the import is complete. This reduces the time required to create and manage archive logs.

4. Like the rollback segment, create several large redo log files. The larger the file, the better. Close some small redo log files. The larger the redo log file, the less log switching is required. Check the warning log information such as "Thread 1 cannot allocate new log, sequence 17, Checkpoint not complete", which means that the log file needs to be expanded or the number of logs needs to be increased.

5. If possible, distribute the rollback segments, table data, and redo log files on different hard disks. This reduces the possibility of I/O conflicts.

Ii. Changes to the intr. ora Parameter

1. Set the value of LOG_CHECKPOINT_INTERVAL to a value greater than the size of the redo log file. This value is usually defined by the block size of the Operating System (UNIX systems are mostly 512 bytes ). In this way, the checkpoint can be minimized (only executed during log switching ).

2. Increase SORT_AREA_SIZE. This increase depends on the status of other active processes on the machine and the amount of available idle memory. We can try 5-10 times to find the best value. If the switch (swapping) and page (paging) are started, the value is too large.

3. The import option is changed.

1 sets COMMIT = N. This will cause the import to be committed after each object (such as table) ends, rather than the buffer is full. This is why we need a large rollback segment.

2. Use a large BUFFER value. This value depends on the system activity and Oracle database size. Usually a few megabytes is enough, but the value can be larger if there is enough memory. Similarly, we can check the page and switch to determine whether the value is too large. This will reduce the number of times the import process reads data into the file (each time it reads data of a buffer size ).

3 sets INDEXES = N. This will speed up the import of non-index tables. Use the INDEXFILE option to create an index after the data is imported. The SORT_AREA_SIZE value must be large enough when an index is created.

Article by: http://database.csdn.net/c_oracle/tag/2