For example, to create a database with a global database name of test. This is typically the name of the organization that owns the database. If the database you are about to create will ever be part of a distributed database system, give special attention to this initialization parameter before database creation.
A flash recovery area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the Oracle-managed current database files datafiles, control files, and online redo logs. It cannot be a raw file system. You must disable those parameters before setting up the flash recovery area.
Oracle recommends using a flash recovery area, because it can simplify backup and recovery operations for your database. Oracle strongly recommends you use at least two control files stored on separate physical disk drives for each database.
Oracle Database can support up to four additional nonstandard block sizes. The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify.
If you do not set a value for this parameter, the default data block size is operating system specific, which is generally adequate. You cannot change the block size after database creation except by re-creating the database. If the database block size is different from the operating system block size, ensure that the database block size is a multiple of the operating system block size.
Therefore, consider specifying a block size larger than your operating system block size if the following conditions exist:. Oracle Database is on a large computer system with a large amount of memory and fast disk drives.
For example, databases controlled by mainframe computers with vast hardware resources typically use a data block size of 4K or greater. The operating system that runs Oracle Database uses a small operating system block size. For best performance in this case, a database block should consist of multiple operating system blocks. These nonstandard block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K.
Platform-specific restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms. To use nonstandard block sizes, you must configure subcaches within the buffer cache area of the SGA memory for all of the nonstandard block sizes that you intend to use. The initialization parameters used for configuring these subcaches are described in the next section, "Managing the System Global Area SGA ".
The ability to specify multiple block sizes for your database is especially useful if you are transporting tablespaces between databases. You can, for example, transport a tablespace that uses a 4K block size from an OLTP environment to a data warehouse environment that uses a standard block size of 8K.
This section discusses the initialization parameters that affect the amount of memory allocated to the System Global Area SGA. The size of the SGA is dynamic, and can grow or shrink by dynamically altering these parameters.
Oracle Database Concepts for a conceptual discussion of automatic shared memory management. The SGA comprises a number of memory components , which are pools of memory used to satisfy a particular class of memory allocation requests. All SGA components allocate and deallocate space in units of granules. The memory for dynamic components in the SGA is allocated in the unit of granules. Granule size is determined by total SGA size.
Some platform dependencies may arise. Consult your operating system specific documentation for more details. The same granule size is used for all dynamic components in the SGA. If you specify a size for a component that is not a multiple of granule size, Oracle Database rounds the specified size up to the nearest multiple. This parameter in effect replaces the parameters that control the memory allocated for a specific set of individual components, which are now automatically and dynamically resized tuned as needed.
Alternatively, you can set one or more of the automatically sized SGA components to a non-zero value, which is then used as the minimum setting for that component during SGA tuning. This is discussed in detail later in this section.
For more complete automatic tuning, set the values of the automatically sized SGA components listed in Table to zero. To control the minimum size of one or more automatically sized SGA components, set those component sizes to the desired value. See the next section for details.
Set the values of the other automatically sized SGA components to zero. You can exercise some control over the size of the automatically sized SGA components by specifying minimum values for the parameters corresponding to these components.
Doing so can be useful if you know that an application cannot function properly without a minimum amount of memory in specific components. You specify the minimum amount of SGA space for a component by setting a value for its corresponding initialization parameter. Here is an example configuration:. In this example, the shared pool and the default buffer pool will not be sized smaller than the specified values 32 M and M, respectively.
The remaining M minus is available for use by all the manually and automatically sized components. The parameter values determine the minimum amount of SGA space allocated. You can also see the current actual values of the SGA components in the Oracle Enterprise Manager memory configuration page.
Manually limiting the minimum size of one or more automatically sized components reduces the total amount of memory available for dynamic adjustment.
This reduction in turn limits the ability of the system to adapt to workload changes. Therefore, this practice is not recommended except in exceptional cases.
The default automatic management behavior maximizes both system performance and the use of available resources. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time.
Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit. Any manually configured components remain unaffected. The exact value depends on environmental factors such as the number of CPUs on the system. Consider the following combination of parameters:. Therefore the rules governing resize for all component parameters are the same as in earlier releases.
However, when automatic shared memory management is enabled, the manually specified sizes of automatically sized components serve as a lower bound for the size of the components. You can modify this limit dynamically by changing the values of the corresponding parameters. If the specified lower limit for the size of a given SGA component is less than its current size, there is no immediate change in the size of that component. The new setting only limits the automatic tuning algorithm to that reduced minimum size in the future.
For example, consider the following configuration:. This resizing occurs at the expense of one or more automatically tuned components. The new setting only limits the reduction of the large pool size to M in the future. Parameters for manually sized components can be dynamically altered as well. However, rather than setting a minimum size, the value of the parameter specifies the precise size of the corresponding component.
When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. When you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components. This section provides guidelines on setting the parameters that control the size of each SGA components.
The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use them to specify the sizes of caches for the various block sizes used by the database.
These initialization parameters are all dynamic. The size of a buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes.
However, a large cache may take up too much memory and induce memory paging or swapping. Each parameter specifies the size of the buffer cache for the corresponding block size.
For example:. In this example, the parameters specify that the standard block size of the database is 4K. The size of the cache of standard block size buffers is 12M.
Additionally, 2K and 8K caches will be configured with sizes of 8M and 4M respectively. Oracle Database selects an appropriate default value.
The internal SGA overhead refers to memory that is allocated by Oracle during startup, based on the values of several other initialization parameters. This memory is used to maintain state for different server components in the SGA. In manual SGA mode, this parameter must be set so that it includes the internal SGA overhead in addition to the desired value of shared pool size.
The Oracle Database 10 g migration utilities recommend a new value for this parameter based on the value of internal SGA overhead in the pre-upgrade environment and based on the old value of this parameter. When you use automatic shared memory management in Oracle Database 10 g , the shared pool is automatically tuned, and an ORA error would not be generated by Oracle. The large pool is an optional component of the SGA. The value of this parameter must be a minimum of one for each background process plus one for each user process.
The number of background processes will vary according the database features that you are using. For example, if you are using Advanced Queuing or the file mapping feature, you will have additional background processes. If you are using Automatic Storage Management, then add three additional processes. Every Oracle Database must have a method of maintaining information that is used to undo changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed.
Collectively these records are called undo data. This section provides instructions for setting up an environment for automatic undo management using an undo tablespace. Set this parameter to AUTO to enable automatic undo management mode. When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace for storage of undo data. This parameter is especially useful for assigning a particular undo tablespace to an instance in an Oracle Real Application Clusters environment.
If no undo tablespace is available, then the instance starts without an undo tablespace. You should avoid running in this mode. Such a database is said to be at the 9. You can advance the compatibility level of your database. Oracle Database Backup and Recovery Advanced User's Guide for information about point-in-time recovery of your database.
If you use named user licensing, Oracle Database can help you enforce this form of licensing. You can set a limit on the number of users created in the database. Once this limit is reached, you cannot create more users. If database creation fails, you can look at the alert log to determine the reason for the failure and to determine corrective action.
The alert log is discussed in "Monitoring the Operation of Your Database". After correcting the error that caused the failure of the database creation, try re-creating the database. Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files.
It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted. If you used the Database Configuration Assistant to create your database, you can use that tool to delete drop your database and remove the files. Initialization parameters for the Oracle Database have traditionally been stored in a text initialization parameter file.
For better manageability, you can choose to maintain initialization parameters in a binary server parameter file that is persistent across database startup and shutdown. This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters.
The following topics are contained in this section. A server parameter file can be thought of as a repository for initialization parameters that is maintained on the machine running the Oracle Database server.
It is, by design, a server-side initialization parameter file. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup. It can also be created directly by the Database Configuration Assistant. The server parameter file is a binary file that cannot be edited using a text editor.
Oracle Database provides other interfaces for viewing and modifying parameter settings in a server parameter file. If no server parameter file is found, the instance searches for a text initialization parameter file. Instructions for starting an instance using a server parameter file are contained in "Starting Up a Database". If you are currently using a text initialization parameter file, use the following steps to migrate to a server parameter file.
If the initialization parameter file is located on a client machine, transfer the file for example, FTP from the client machine to the server machine.
This statement reads the initialization parameter file to create a server parameter file. However, if the instance has been started using a server parameter file, an error is raised if you attempt to re-create the same server parameter file that is currently being used by the instance. In this example no SPFILE name is specified, so the file is created with the platform-specific default name and location shown in Table Another example, which follows, illustrates creating a server parameter file and supplying a name.
When you create a server parameter file from an initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file.
All other comments are ignored. The server parameter file is always created on the machine running the database server. If a server parameter file of the same name already exists on the server, it is overwritten with the new information.
Oracle recommends that you allow the database server to default the name and location of the server parameter file. The supported character sets and default value of this parameter depend on your operating system. Oracle Database Globalization Support Guide for more information about choosing a character set. A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion 2 32 blocks.
The maximum size of the single data file or temp file is terabytes TB for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks. A smallfile tablespace is a traditional Oracle tablespace, which can contain data files or temp files, each of which can contain up to approximately 4 million 2 22 blocks.
Oracle Database Administrator's Guide for more information about bigfile tablespaces. Specify one or more files to be used as redo log files. If you omit the LOGFILE clause, then Oracle Database creates an Oracle-managed log file member in the default destination, which is one of the following locations in order of precedence :.
In all these cases, the parameter settings must correctly specify operating system filenames or creation form Oracle ASM filenames, as appropriate. If no values are set for any of these parameters, then the database creates a log file in the default location for the operating system on which the database is running. This log file is not an Oracle Managed File. Specify the number that identifies the redo log file group.
A database must have at least two redo log file groups. If you omit this parameter, then Oracle Database generates its value automatically. Specify the maximum number of redo log file groups that can ever be created for the database. Oracle Database uses this value to determine how much space to allocate in the control file for the names of redo log files. The default, minimum, and maximum values depend on your operating system.
Specify the maximum number of members, or copies, for a redo log file group. The maximum and default values depend on your operating system. Specify the maximum number of archived redo log files for automatic media recovery of Oracle RAC. The database uses this value to determine how much space to allocate in the control file for the names of archived redo log files. The minimum value is 0. The maximum value is limited only by the maximum size of the control file.
This clause prepares for the possibility of media recovery. This clause does not allow for the possibility of media recovery.
Oracle Database will log all changes in the database except for changes in temporary tablespaces and temporary segments. Refer to Oracle Database Administrator's Guide for information on when to use this setting. You can specify it in two modes:. Oracle ASM applies attributes to the data file, as specified in the system default template for a data file as shown in the table in " Managing Disk Group Templates ".
You can also create and specify your own template. For example, if there is a disk group named data , then you can create a tablespace tblspace in that disk group with the following SQL statement:. The following example illustrates the usage of Oracle ASM with defaults. This example enables Oracle ASM to create and manage the tablespace data file for you, using Oracle supplied defaults that are adequate for most situations.
Each online log should have one log member in multiple disk groups. The file names for log file members are automatically generated. All rights reserved. This chapter from the Oracle 9i Fundamentals I Exam Cram book covers the creation of a database, with sample questions to help you prepare. This chapter is from the book.
Overview Pearson Education, Inc. Collection and Use of Information To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including: Questions and Inquiries For inquiries and questions, we collect the inquiry or question, together with name, contact details email address, phone number and mailing address and any other additional information voluntarily submitted to us through a Contact Us form or an email.
Surveys Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites.
Contests and Drawings Occasionally, we may sponsor a contest or drawing. Newsletters If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information informit. Service Announcements On rare occasions it is necessary to send out a strictly service related announcement. Customer Service We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.
Other Collection and Use of Information Application and System Logs Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Web Analytics Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site.
Cookies and Related Technologies This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Security Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.
Children This site is not directed to children under the age of Marketing Pearson may send or direct marketing communications to users, provided that Pearson will not use personal information collected or processed as a K school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations. Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing. Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.
Sale of Personal Information Pearson does not rent or sell personal information in exchange for any payment of money. Supplemental Privacy Statement for California Residents California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice.
Sharing and Disclosure Pearson may disclose personal information, as follows: As required by law. Links This web site contains links to other sites. Requests and Contact Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.
0コメント