TECHNOLOGY: Inside OCP
Tuning Oracle9i on Linux
By Aradhana Puri
Questions and answers about managing Oracle9i on Linux
On Linux platforms, using Oracle Cluster File System (OCFS) and asynchronous input/output (I/O) and implementing a large System Global Area (SGA) make it easier to manage Oracle9i on Linux.
This column presents sample questions of the type you may encounter when taking the "Managing Oracle9i on Linux Special Accreditation" exam. This exam tests the skills required to manage an Oracle9i Database on the Linux platform. A candidate must be an Oracle9i DBA OCP before earning the Special Accreditation: Managing Oracle9i on Linux. Note that the format of the sample questions has been adjusted for presentation in this column and that the questions are based on use of Red Hat Enterprise Linux AS 2.1.
Oracle Cluster File System
A cluster file system allows the nodes in a cluster to concurrently access a given file system. Each node accesses the same set of files and data. The cluster file system eliminates the requirement for Oracle database files to be linked to logical drives. OCFS volumes can span one shared disk or multiple shared disks for redundancy and performance enhancements. OCFS is designed to provide an alternative to using raw devices for Oracle Real Application Clusters.
Which two statements are correct about OCFS?
A. You can have a maximum of 355 files on OCFS.
The correct answers are B and D. OCFS supports standard file system commands such as mkdir , mv , cp , rmdir , and tar . OCFS is not a journaled file system. It does not record changes in the file system in a journal file. Answer A is incorrect because there is no limit on the number of files on OCFS. Answer C is incorrect because you can use OCFS partitions for archive logs. In this way, the different instances of the database can share archive logs. This makes media recovery easier, because every node has access to every archived log file. Answer E is incorrect because OCFS supports database files, including data files, online redo log files, archived log files, and control files.
Smith, a DBA at ABC Corp., plans to install a standalone database system and upgrade it to an Oracle Real Application Clusters environment in the near future. He needs to ensure that the upgrade to the Oracle Real Application Clusters environment is smooth. Which file system should Smith choose for the database files in the standalone database system?
The correct answer is A. You can run OCFS in a standalone system, because you use it for database files only. With OCFS, if you plan to upgrade to an Oracle Real Application Clusters environment, adding a new node, recompiling the Oracle database kernel to enable Oracle Real Application Clusters mode, and adding a new online log thread are easier. Answers B, C, and D are incorrect because the reiserfs , ext3 , and ext2 file systems do not help if you are upgrading to Oracle Real Application Clusters.
In synchronous I/O, the process that makes a write request waits for the completion of the request before continuing. Asynchronous I/O allows a process to make multiple write requests without having to wait for the completion of a prior request. No write request is considered complete until the operating system reports a completion message back to the requesting process.
Which two file systems support asynchronous I/O?
A. Network File System (NFS)
The correct answers are B and C. OCFS and the ext3 support asynchronous I/O. Note that for Oracle Database to support asynchronous I/O, the file system where the database files reside must support asynchronous I/O. Answers A and D are incorrect because NFS and GPFS do not support asynchronous I/O.
All your database files reside on the ext3 file system. You are implementing asynchronous I/O. You have relinked the Oracle server executables by issuing the following command:
$ make -f ins_rdbms.mk async_on
Which two initialization parameter settings are required to enable asynchronous I/O?
A. DISK_ASYNCH_IO is set to true .
The correct answers are A and B. You need to verify that the DISK_ASYNCH_IO and FILESYSTEMIO_OPTIONS initialization parameters are set to their default values: true and asynch , respectively. Answers C and D are incorrect because you need not set the FILE_MAPPING and BACKUP_TAPE_IO_SLAVES parameters to enable asynchronous I/O.
You are working in a decision support system (DSS) that resides on the ext3 file system. You have enabled asynchronous I/O for Oracle Database and require better I/O throughput with DSS workloads. You need to increase the value of the _____ Linux tuning parameter to tune asynchronous I/O.
The correct answer is B. To get better I/O throughput with DSS workloads, you must increase the value of the /proc/sys/fs/aio-max-size parameter from the default 131,072 bytes to 1MB or greater. You can set the value of aio-max-size to 1048576 bytes (1MB), by executing the following command as the root user:
# echo 1048576 >/proc/sys/fs/aio-max-size
Alternatively, to make the changes persistent after system restart, add the following to the sysctl.conf file:
fs.aio-max-size = 1048576
Implementing a Large SGA
With 4GB of RAM, the Linux memory map appears as shown in Figure 1(a). The starting point in the memory map for shared libraries and process memory is controlled by the mapped_base parameter. The process memory—the Oracle SGA—can occupy the section from 1.25GB to 2.98GB, allocating about 1.7GB to the SGA.
You can modify the Linux memory map and allocate more memory for the SGA by decreasing the mapped_base parameter value so that the Oracle SGA can occupy the section from 336MB to 2.98GB, allocating about 2.7GB to the SGA, as shown in Figure 1(b). The kernel will now take up about 128MB at the base as well as the uppermost 1GB. Note that decreasing the value of the mapped_base parameter expands the area available for the SGA at the expense of memory for shared libraries and application code.
You need to increase the SGA from 1.7GB to 2.7GB by decreasing the mapped_base parameter value and relocating the SGA. Your Linux environment has 4GB of RAM. Which three actions would you perform to increase the SGA size?
A. Configure bigpages.
The correct answers are C, D, and E. You must perform the following three tasks to increase the SGA size: (1) As the root user, modify the shmmax parameter to accommodate the entire SGA. (2) As the root user, decrease the mapped_base parameter value of the Linux shell that is used to start up and shut down the database. (3) As the oracle user, modify ksms.s and relink the Oracle executable. Answers A and B are incorrect because you need not configure bigpages and increase the value of the fs.overflowuid parameter.
You increased the SGA size by decreasing the mapped_base parameter value and relocating the SGA. Which two statements are correct in this scenario?
A. All the components of the SGA can use the additional memory allocated to the SGA.
The correct answers are A and D. All the components of the SGA, including the shared pool and buffer cache, can make use of the additional memory. You can configure multiple block sizes in a database with the SGA extended, by decreasing the mapped_base parameter value. Answer B is incorrect because with the extended SGA, less virtual memory is available for the program global area (PGA). You may need to retune the SORT_AREA_SIZE initialization parameter. Answer C is incorrect because asynchronous I/O is not disabled automatically when you implement a large SGA.
This column has focused on the various features you can use to manage and tune an Oracle9i Database on a Linux platform. OCFS enables you to manage Oracle9i Real Application Clusters without having to use raw devices. The implementation of asynchronous I/O allows Oracle processes to issue multiple I/O requests to the disk with a single system call. You can modify the Linux memory map to allocate more memory to the SGA.
Aradhana Puri (email@example.com) is a principal Oracle Certification Exam developer at Oracle. She has been with the company since 2000.