ORACLE8i- P4

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

0
53
lượt xem
4
download

ORACLE8i- P4

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P4: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:
Lưu

Nội dung Text: ORACLE8i- P4

  1. DETERMINING PHYSICAL REQUIREMENTS 109 Initially, redo logs for a database are defined and sized when you execute the CRE- PA R T ATE DATABASE command. Later in the life of a given database, you can also opt to I create new redo logs of differing sizes and even remove old redo logs. Typically, when sizing redo logs, you want log switches to occur every 10 to 20 minutes. Therefore, the best size for the redo logs is based on how often log switches are occurring on an active database. Table 3.1 provides suggested beginning sizes for Oracle Essentials redo logs, in several different databases. TABLE 3.1: SUGGESTED SIZES OF ORACLE REDO LOGS Database Size Suggested Starting Redo Log Sizes for High / Medium / Low Database DML Activity Small (up to 50mMB) 10MB/5MB/1MB Medium (50m to 100 MB) 100MB/50MB/10MB Large (100m to 250 MB ) 1G/500MB/100MB Huge (250MB to 1TB) 5G/1G/500MB MegaDatabase (1TB+) 20G/5G/1G Once your database is up and running, you’ll monitor the alert log and determine how often the database is switching log files. If this is happening more often than once every 15 minutes, it’s probably a good idea to re-create the online redo logs, making them larger. To figure out how much larger they need to be, extrapolate from the current size based on the number of log switches that occur. For example, if you have 500MB redo logs, and log switches occur every 5 minutes, then you should probably re-create them as 1.5GB files. You can re-create the online redo log files while the database is still online. When you do, keep the following rules in mind: • You must always have at least two redo log files available for the database to use. • The current online redo log cannot be dropped. The process is fairly simple: 1. Make sure that the redo logs you wish to re-create are not the current online redo logs, by looking at the STATUS column of the V$LOG data dictionary view. 2. Remove each redo log (each member of each group, one at a time) by using the ALTER DATABASE DROP LOGFILE command. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  2. 110 CHAPTER 3 • CREATING ORACLE DATABASES 3. Once the redo log group is dropped, remove the physical redo log files on the database. Be very careful that you don’t end up removing the wrong redo log files! 4. Re-create that group with the ALTER DATABASE ADD LOGFILE command, using the revised size parameter. Listing 3.1 provides an example of replacing an existing redo log group with a new redo log group on an NT system. Listing 3.1: Replacing a Redo Log Group -- Make sure the redo log to be dropped is not the current log SELECT group#, bytes, status FROM v$log; GROUP# BYTES STATUS ---------- ---------- ---------------- 1 1048576 INACTIVE 2 1048576 CURRENT 3 1048576 INACTIVE -- And where are the current logfile members? SELECT * FROM v$logfile; GROUP# STATUS MEMBER ---------- ------- ---------------------------------------- 1 D:\ORACLE\ORADATA\ORA816\REDO01.LOG 2 D:\ORACLE\ORADATA\ORA816\REDO02.LOG 3 D:\ORACLE\ORADATA\ORA816\REDO03.LOG -- Let’s re-create group 1 (since it’s not active). -- First, drop the existing group. ALTER DATABASE DROP LOGFILE GROUP 1; -- Note, since we are making the log file bigger, we will -- need to drop the original. Host del D:\ORACLE\ORADATA\ORA816\REDO01.LOG -- Now, re-create logfile group 1 as 2MB logfile. Note that -- group 1 originally only had one member, we will add -- a second member to it as well. ALTER DATABASE ADD LOGFILE GROUP 1 (‘d:\oracle\oradata\ora816\redo01a.log’, ‘e:\oracle\oradata\ora816\redo01b.log’) SIZE 2M; NOTE When creating redo logs, you should consider configuration of the incremental checkpoint process. This is covered in detail in Chapter 5. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  3. DETERMINING PHYSICAL REQUIREMENTS 111 Sizing the SYSTEM Tablespace PA R T The size of the SYSTEM tablespace includes a few variables that need to be taken into I account: • The primary consideration is the amount of PL/SQL that will be stored in the database. Oracle Essentials • Account for the Oracle options that you will be implementing (for example, Oracle Spatial). • To a lesser extent, consider the overall size of the database. TI P In calculating the SYSTEM tablespace, be sure to include a generous fudge factor. This is one tablespace you don’t want filling up. See Chapter 6. For the typical bare-bones Oracle database and a normal number of PL/SQL objects, we suggest you begin with the SYSTEM tablespace at 100MB. Each option that you add to Oracle (Spatial, Time Series, SQL J, Java, etc.) calls for an increase as recommended by Oracle in the documentation for that product. SYSTEM tablespaces sized at 250MB are not uncommon, and 500MB and larger is not unheard of. Capacity Planning for Control Files Although you do not size control files directly, you do need to plan for their place- ment. (And you can influence their size somewhat, as discussed in Chapter 13.) Expect to create at least two and preferably three control files, anywhere from 2 to 4MB in size and possibly larger. Make sure that you put each copy of the control file on a different disk, and on different controllers, if possible, for recovery purposes. Determining Memory Requirements Oracle requires memory, and a lot of it. In brief we will discuss configuring sys- tem memory for use by Oracle, and then we will tackle the topic of configuring Oracle memory structures. In this section, we will provide some suggested settings to start out with. Note that these are just suggestions and are in no way meant to be any- thing more than that. You should always monitor your databases to make sure you are getting the most out of your system. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  4. 112 CHAPTER 3 • CREATING ORACLE DATABASES System Memory Before you start an Oracle database, you should ensure that you have enough mem- ory on the system to support it. The Oracle Installation Guides include information on how much total memory you will need for all the products you wish to install. Also, since the Oracle SGA requires shared memory, some systems (particularly Unix) will require that you configure that memory before it can be used. Again, the platform-specific Installation Guides are your source for operating system modifica- tions that must be made before you can run Oracle. Oracle Memory Structures You need to consider the memory requirements of several Oracle structures, including • The SGA • Oracle processes • User processes • Other Oracle memory needs Most of the memory requirements for the Oracle processes and user processes are platform specific. Refer to the Oracle platform-specific documentation for details about process memory requirements beyond those of the SGA. We will review config- uration of the SGA later in this chapter. A note to Unix folks: For Unix platforms you must configure shared memory for use at the OS level. This must be done before you can even start an Oracle instance, because Oracle depends on the use of shared memory with regard to SGA, which is allocated at instance startup. The requirements for each platform are different, so get further instructions from the OS-specific documentation for your version of Oracle. TI P There is a good document that discusses Oracle best practices in a Sun environ- ment (although a fair portion of this document can be expanded to include many different Unix flavors). It is called “Sun/Oracle Best Practices,” authored by Bob Sneed of Sun’s SMI Performance and Availability Engineering Group. You can find it at www.sun.com/ blueprints. The part number is 806-6198-10. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  5. DETERMINING THE DATABASE BLOCK SIZE 113 Determining the Database Block Size PA R T I We’re about to dive into what is sometimes called one of the Oracle Holy Wars: choosing the database block size. It is said that more paper wads have been lobbed over cubicle walls because of this argument than for any other in Oracle technology Oracle Essentials (with the exception, perhaps, of the Mountain Dew vs. coffee arguments, which we steer clear of). Juvenile behavior aside, it is critically important that you carefully choose the correct block size for your database, before you create it. Once the database is created, you can’t change the block size without essentially rebuilding the database, which presents enormous difficulties especially for a mission-critical database. Prior to Oracle 7.3, setting the block size also determined certain database restric- tions, the primary one being a restriction on the number of extents a segment could consist of. These restrictions have been removed since Oracle 7.3. Oracle block sizes can range anywhere from 2K to upwards of 32K. When you are choosing a data block size, you need to consider both its benefits and detriments, as well as the operating system you are using. Factors to Consider For Online Transaction Processing (OLTP) systems, smaller block sizes are usually bet- ter. These systems typically are looking for random data, usually via indexed lookups. Smaller blocks translate into fewer rows per block, so you’ll do fewer overall I/O oper- ations to get the same row than you would with a larger block. For example, consider an index lookup of a single row. In any event, the number of logical I/Os will gener- ally be the same (2–3 for the index, 1 for the block). The total bytes read to complete those I/Os, however, will be significantly less if you are using smaller block sizes. With a 2KB block database, Oracle needs to read only 6–8KB to get the data you need. With an 8KB-block database, Oracle would need to read 32KB to get the same information. Additionally, with smaller row sizes you won’t be filling the SGA with nonessential rows, because fewer rows per block are read into the database. If you are using systems that do sequential reads, such as data warehouses or Exec- utive Information Systems (EIS), then you want to read in as much data in a single I/O as possible. This is because you typically want to read all the rows in the block. Thus larger block sizes are usually preferred in such environments. Warehouse data- bases with 16K and even 32K blocks are not unusual. Random, index-based queries, on the other hand, will suffer greatly with larger block sizes as Oracle moves data in and out of the database buffer cache more frequently and in higher volume. This can also lead to increased contention for data blocks, as well as to database writer (DBWR) performance problems. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  6. 114 CHAPTER 3 • CREATING ORACLE DATABASES As companies discover the power of the data in their systems, more and more data- bases these days are hybrids, a mix of OLTP and warehouse or EIS systems. Thus, it becomes more of a challenge to decide on a database block size. It used to be that DBAs typically used smaller block sizes for their databases. The most commonly used block sizes were 2KB and 4KB. An 8KB block size was rare, and 16KB was very uncommon. For an OLTP database these days, an 8KB block size tends to perform best. For a data warehousing or EIS system, if you are expecting a mix of index lookups and full table scans, try 16KB blocks. If you are expecting mostly full table scans, consider 32KB blocks. When setting the database block size, you also want to make sure that the block size times the DB_FILE_MULTIBLOCK_READ_COUNT is a multiple of the operating system I/O block read size. For example, on most Unix operating systems and NT, one I/O will consist of a 512KB block read from disk. Thus, if you have your Oracle block size set to 8KB, you should set this parameter to 64 (it defaults to 8), which will indi- cate to Oracle that a multiblock read of 64 Oracle blocks should be performed with each I/O. As a result, there is no I/O wastage. Thus, the DB_FILE_MULTIBLOCK_READ_COUNT parameter has the effect of con- trolling how many blocks Oracle will read in a single I/O when doing a full table scan. The effects of setting this parameter are that full table scans will perform more effi- ciently. Higher values for this parameter can also cause the optimizer to consider full table scans (or index fast full scans, which use multiblock I/O as well) over other access paths. As always, it’s a good idea to set up a test and development environment before you create the production system. This is particularly true with the block size, because to reset the block size you must re-create the database—not a pleasant prospect if yours is terabyte-size! There are other issues to consider. The default I/O block size of your operating sys- tem and your file system can have performance impacts. Also, other types of access control mechanisms, such as inodes in Unix, will have impact. In extreme situations, Unix inodes can cause access conflicts. These problems are less prevalent with the advanced technologies and disk caches in use today. TI P Beyond all the logical considerations, a test will give you the best input for choos- ing a block size. Create a database of a given block size and perform a test script on it that will simulate the expected load. Then re-create the database using a different block size and test it again. If you don’t have time to test your system like this, we suggest 16K as a good place to start if you have enough memory to allocate sufficient blocks to the data- base buffer cache. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  7. NAMING CONVENTIONS 115 Table 3.2 summarizes all the things to consider in determining block size. PA R T I TABLE 3.2: BENEFITS AND DETRIMENTS OF VARIOUS BLOCK SIZES Block Size Benefits Detriments Comment Oracle Essentials Small (2–4K) Less contention for blocks, High overhead for Oracle. Probably best for better random accesses. Sequential access is gen- OLTP systems erally slower. Medium (8K) Less block contention than Performance for OLTP Probably best for with larger block sizes; good may be better with small hybrid systems random access performance. blocks. Performance in Sequential access is moder- warehouses and EIS sys- ately good. Overhead to the tems may be better with Oracle server is moderate. large blocks. Large (16K) Improved performance of Random access perfor- Good for data ware- sequential access. Less over- mance is degraded. house with some all overhead cost. Potential for block con- random access tention increases. transactions Humongo Probably the best perfor- Random access perfor- Probably best (32K) mance for sequential access. mance is seriously for pure data Least overhead costs. degraded. Block warehouse or EIS contention can be systems significant. Naming Conventions At this point in the game, it’s a good time to solidify some naming standards. In addi- tion to database names, you’ll want conventions for your physical database file- names. By enforcing solid standards as your database grows and as you add additional databases, you increase the quality and dependability of your system’s information. Of course, OFA already provides some standards, but there are some other best prac- tices to follow. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  8. 116 CHAPTER 3 • CREATING ORACLE DATABASES Deciding the Database Name The name of a database is also known as the database System Identifier or SID. Each and every database name on a server must be unique. Database names are best kept to eight characters or less (in fact, this is still a requirement on a few platforms). Database names can be in upper-, lower-, or mixed case. On some platforms, such as NT, Oracle does not make a case distinction. On other platforms, such as Unix, Oracle does distinguish between an instance called test and one called TEST. (We prefer all lowercase names for instances.) It’s usually smart to make the name of the database represent the database content. For an accounting database, you might start the name with acct, or perhaps fin. We suggest adding a tail to the name based on the type of database. A test system for accounting, for example, might be named accttest. For a production system, it might be acctprod, and a development system would be acctdevl. Database names like Thor, test, prod, or gonzo don’t mean much of anything and can become really confusing in a large database environment. Table 3.3 lists some suggested trailing identifiers for various kinds of databases, with examples for an HR database. TABLE 3.3: DATABASE IDENTIFIERS Database Type Suggested Trailing Identifier Example Production prod or p hrprod or hrp Test test or t hrtest or hrt Development devl or d hrdevl or hrd User Acceptance Testing uact or u hruact or hru Unit Testing utst or n hrust or hrn System Testing stst or s hrstst or hrs Reporting rpt or r hrrpt or hrr Naming Database Objects Besides the name of the database itself, you’ll need to identify naming standards for a variety of physical objects, including • Database datafiles • Control files Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  9. CREATING THE PARAMETER FILE (INIT.ORA) 117 • Redo logs PA R T • Directory naming standards I Table 3.4 provides some suggested naming standards along with examples. TABLE 3.4: OBJECT NAMING STANDARDS Oracle Essentials Object Type Standard Examples in Database Mydb Database datafiles Include the database name, the For a tablespace named data: tablespace name, and a number to mydb_data_01.dbf make the datafile unique for the tablespace. End with a .dbf extension. Control files Include the database name, the mydb_control_01.ctl word control, and a unique iden- tifier for the file. End with a .ctl extension. Redo logs Include the name of the database, For a redo log that is member 2 the word redo, a unique identifier of group 3: mydb_redo_03b.log for the redo log group, and a unique identifier for each member of that group. End with a .log extension. Archived redo logs Include the name of the database, This would be set using the the thread, and the sequence num- LOG_ARCHIVE_FORMAT parameter ber of the archived redo log. as %%ORACLE_SID%%%t%s.log Creating the Parameter File (init.ora) Your database parameter file contains many of the items discussed in this chapter— the database name, the SGA configuration, and much more. NOTE This section does not cover all database configuration issues. Matters such as MTS, parallelism, materialized views, and various recovery settings are discussed in other chapters. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  10. 118 CHAPTER 3 • CREATING ORACLE DATABASES The Oracle database parameter file is typically referred to as init.ora. The full naming standard is init.ora, where sid is the name of the database. Thus, for a database called brosep, the default name of the parameter file would be initbrosep.ora. Location of init.ora The default location for the parameter file is in the ORACLE_HOME\dbs directory (ORACLE_ HOME\database in NT). Typically, however, you will create the parameter file in the ADMIN directory structure using the pfile directory. • If your operating system supports file links, as Unix does, you will want to create a link in the ORACLE_HOME\dbs directory that points to the init.ora in pfile. • If your operating system does not support links to files, then you will want to use the ifile parameter in a shell init.ora in the ORACLE_HOME\dbs directory (ORACLE_HOME\database in NT) directory and point the file to init.ora in the ADMIN directory structure. Thus, the init.ora in the default location only points to the true init.ora in the ADMIN directory. TI P Rather than putting a link or a shell init.ora file in the ORACLE_HOME\dbs direc- tory, you can just use the PFILE= option of the STARTUP command to point Oracle to the correct location of the parameter file. TI P In Oracle8i for NT, you can actually modify a Registry entry and have it look for the parameter file in the ADMIN directory instead of the default location. To do this, change the key HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\Home1 (or the appropriate Oracle Home). Then change the key ora__pfile to point to the correct location of the init.ora file, and save the changes. Oracle will use the init.ora file at the new location the next time you start it. You can, of course, call the init.ora file anything you want. You might want to start your database with an init.ora file other than the default one you have config- ured for that database. (For example, when creating or re-creating a database, you may want to comment out the ROLLBACK_SEGMENTS parameter.) In this case, when you use the STARTUP command to start the database, you will include the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  11. CREATING THE PARAMETER FILE (INIT.ORA) 119 PFILE parameter to define the location and name of the parameter file you want to PA R T use. Here’s an example: I Startup pfile=c:\teststartup\initnorbs.ora Following is an example of a database parameter file. db_name = ora816 Oracle Essentials db_files = 1024 control_files = (“D:\Oracle\oradata\ora816\control01.ctl”, “D:\Oracle\oradata\ora816\control02.ctl”, “D:\Oracle\oradata\ora816\control03.ctl”) open_cursors = 100 max_enabled_roles = 30 db_file_multiblock_read_count = 8 db_block_buffers = 2048 shared_pool_size = 55428800 java_pool_size = 0 log_checkpoint_interval = 10000 log_checkpoint_timeout = 1800 processes = 50 parallel_max_servers = 5 log_buffer = 32768 #audit_trail = true # if you want auditing #timed_statistics = true # if you want timed statistics max_dump_file_size = 10240 # limit trace file size to 5M each ##### For archiving if archiving is enabled ##### log_archive_start = true log_archive_dest_1 = “location=D:\Oracle\oradata\ora816\archive” log_archive_format = %%ORACLE_SID%%T%TS%S.ARC rollback_segments = ( RB01, RB02 ) # Global Naming -- enforce that a dblink has same name as the db it connects to global_names = true # define directories to store trace and alert files background_dump_dest = D:\Oracle\admin\ora816\bdump user_dump_dest = D:\Oracle\admin\ora816\udump db_block_size = 2048 compatible = 8.1.6 sort_area_size = 65536 sort_area_retained_size = 65536 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  12. 120 CHAPTER 3 • CREATING ORACLE DATABASES Setting the Parameters Setting init.ora parameters is kind of a hit-and-miss proposition. No matter what anyone tells you, there is no magic formula for doing it. Rules of thumb often apply, and certainly experience is a good guide. There are typically so many variables that fit into the mix, getting things to work just right becomes largely a matter of tuning. In the end, you need to monitor your database from its infancy, and make sure every- thing is set up to perform correctly. When you find that you need to modify a parameter after the database is up and running, you may be able to do it dynamically for the entire system without needing to restart (or “bounce”) the database. To find out whether a database parameter can be modified on-the-fly, with the database up, query the V$PARAMETER data dictio- nary view. This view includes two columns that indicate whether a listed parameter can be modified for a given session (ISSES_MODIFIABLE) or modified for the entire database (ISSYS_MODIFIABLE). Of course, many parameters cannot be modified dynamically at all. If you find that the V$PARAMETER data dictionary view ISSES_MODIFIABLE col- umn is set to TRUE, then you can modify the parameter for a given session with the ALTER SESSION command. If you find the ISSYS_MODIFIABLE column in V$PARA- METER is set to IMMEDIATE, then the setting can be changed for the entire database with an ALTER SYSTEM command and the change will take effect immediately. If the ISSYS_MODIFIABLE is reported as DEFERRED, then the setting can be changed, and will take effect, but only for new system logins; existing logins will not see the change. Finally, if either column is set to FALSE, then that parameter cannot be changed dynamically. Chapter 5 contains a report for both normal parameters and hidden parameters. It also indicates the dynamic nature of these parameters. To change parameters dynamically, use the ALTER SYSTEM and ALTER SESSION commands. An example is shown in Listing 3.2. Listing 3.2: Changing Parameter Settings Dynamically ALTER SYSTEM SET timed_statistics=TRUE; System altered. ALTER SESSION SET sort_area_size=100000; Session altered. Configuring the SGA You learned in Chapter 1 that the System Global Area (SGA) contains the database buffer cache, the shared pool, and the redo log buffer. Here, we’ll include two Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  13. CREATING THE PARAMETER FILE (INIT.ORA) 121 additional components in our discussion. They are not always directly associated with PA R T the SGA, but you need to know about them: the large pool and the Java pool. I In terms of preparing to create your database, your major concern is sizing—deter- mining the optimum size for the buffers and pools in the SGA. That’s what we are going to talk about next. Oracle Essentials Sizing the Database Buffer Cache The database buffer cache, as described in Chapter 1, is allocated in units of database blocks via the DB_BLOCK_BUFFERS parameter in the init.ora. You can calculate the total size of the database buffer cache by using the formula DB_BLOCK_BUFFERS * DB_BLOCK_SIZE Once you have determined the database block size, then you need to designate how many of those blocks you wish to allocate to the SGA’s buffers and pools. The sample database parameter file provided with Oracle8i contains suggested set- tings for the DB_BLOCK_BUFFERS parameter. These suggestions are probably not the best, however. Various Oracle experts have suggested benchmarks for setting the data- base buffer cache. We like to use from 3 percent to 10 percent of the total database size, depending on a number of factors including memory availability, type of data- base activity, and the expected volume of data changes. Of course, performance is always a concern when allocating memory to the buffer cache. A good figure to start out with is about 80MB for an average database (say, up to 1GB). After initially decid- ing on the size of your SGA, it’s important to monitor closely the performance of your database. You may need less or more space, depending on the types of queries your database is performing. Table 3.5 provides some suggestions based on the various database sizes. TABLE 3.5: SUGGESTED SIZES FOR DATABASE BUFFER CACHE (DB_BLOCK_BUFFERS) Database Size Suggested Cache Size 500MB 50MB 1GB 80–100MB 10GB 200MB 20GB 400MB 100GB 600MB–1GB Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  14. 122 CHAPTER 3 • CREATING ORACLE DATABASES Buffer pool caches larger than about 1.5GB seldom reap any real performance gains (though one of the themes of this book, we hope, is “never say never!”). So keep a close eye on your performance measurements if you allocate a particularly large data- base buffer cache. That memory may be better used elsewhere. WAR N I N G When you allocate memory to the SGA, be cautious that you don’t induce disk paging or swapping on your system! Setting Up the Buffer Pools The database buffer cache, discussed just above, is the only one of Oracle’s three data buffer cache pools for which memory must be allocated. Also out of that memory allocation can come memory for the optional recycle and keep buffer pools. These allocations are controlled with the init.ora parameters BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE. Make sure you allocate enough default buffer pool memory to accommodate the two other buffer pools, if you choose to use them. Optionally, you can also assign a number of LRU latches to each buffer pool that’s configured. Oracle requires a mini- mum of one LRU latch for every 50 buffers assigned to either the recycle or keep buffer pool, and will by default assign this ratio when allocating the two pools. Should latching become a problem, you can assign a larger number of LRU latches when configuring the buffer pool in the parameter file. WARN I NG Be careful you don’t allocate too many LRU latches to the buffer pools, or the database will not start. The total number of available LRU latches is controlled by the parameter DB_BLOCK_LRU_LATCHES, which defaults to 0.5 times the number of CPUs in your system (with a minimum of 1 LRU latch allocated). You may need to increase the DB_BLOCK_LRU_LATCHES parameter in order to use the multiple buffer pool feature of Oracle. Following are examples of init.ora parameters for the buffer pools. -- Assign a recycle buffer pool with default LRU list assignments buffer_pool_recycle=100 -- Assign a keep buffer pool with 50 blocks and 2 LRU latches buffer_pool_keep=(“buffers:100”, “lru_latches:5”) Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  15. CREATING THE PARAMETER FILE (INIT.ORA) 123 PA R T WARN I NG The documentation for some Oracle versions gives incorrect syntax for configuring multiple buffer pools. Note that in the BUFFER_POOL_KEEP setting just above, I the single quotes are required. Oracle Essentials Sizing the Shared Pool The shared pool is sized with a single init.ora parameter, SHARED_POOL_SIZE. The value of this parameter is expressed in bytes, not blocks. Specify the size of the shared pool in consideration of the following expectations: • Overall size of the database • Number of users in the database concurrently • Number of concurrent transactions • Amount of reusable SQL • Number and size of PL/SQL objects and blocks • Number of database objects • Use of reusable SQL statements (see Chapters 15, 16, and 17 for more on this topic) • Use of multithreaded servers (MTS) considering also allocation of the large pool, since some shared_pool structures can be created instead in the large pool. The default size of the shared pool is 32MB, which is enough only for a database that does next to nothing. At a minimum, you should configure the shared pool at about 60MB. Shared pools bigger than a couple of hundred megabytes, in fact, are no longer unusual and may be upwards of 1GB and even larger with certain applications (though this can have performance implications). WAR N I N G Be careful of setting the shared pool too large (greater than about 300MB). This can have a detrimental effect on overall performance of your database (this is dependent on a number of factors). Likewise, a shared pool that is sized too small can have severe performance impacts as well. We generally recommend that a shared pool never be less than 60MB. Always monitor your database carefully after making any changes to memory. See Chapters 15 through 17 for more on this topic. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  16. 124 CHAPTER 3 • CREATING ORACLE DATABASES Sizing the Redo Log Buffer The redo log buffer is sized via the LOG_BUFFER parameter and its default is platform specific. This buffer should be sized initially at about 512K. Be very careful before you change the value of the redo log buffer. Some database performance problems may prompt you to try increasing the size. More often than not, this is a bad idea, because this can have an even more adverse impact on perfor- mance, as we will discuss in Chapter 17. Should performance-tuning issues dictate a change, you may want to increase the redo log buffer, but generally do not make the redo log buffer larger than 1MB at any time. In fact, frequently, the answer to perfor- mance tuning is decreasing the size of the redo log buffer. Sizing the Large Pool The large pool is an optional structure used for MTS session memory, I/O server processes, and RMAN. It is set by the parameter LARGE_POOL_SIZE, with a default size of 0. If we are using RMAN or MTS, we generally start by setting the large pool to about 20MB and then monitor its use afterward. Sizing the Java Pool The Java pool is an optional structure used by the Java memory manager (see Chapter 27). It is set by the parameter JAVA_POOL_SIZE and has a default size of 20MB. If you’re going to use Java inside your Oracle database, you’ll want to set this parameter accordingly. Otherwise, leave it at 0 to reduce memory overhead. WARN I NG A bug in Oracle 8.1.5.0 causes the database to not come up if you set JAVA_POOL_SIZE to 0. You’ll need to set this parameter to about 1MB instead. The bug is fixed in later patch set fixes of Oracle 8.1.5, and in versions after 8.1.5, including 8.1.6 and 8.1.7. Setting Up File Paths Also in the database parameter file are directory locations for Oracle’s use. You’ll define the paths for such things as core dump files, user dump files, and other files generated by the database. Table 3.6 provides a complete list. Many of these parame- ters are discussed in more detail throughout this book. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  17. CREATING THE PARAMETER FILE (INIT.ORA) 125 TABLE 3.6: DIRECTORY PATH SETTINGS IN THE PARAMETER FILE PA R T Parameter Name Purpose Default Value I AUDIT_FILE_DEST Defines the location for database Oracle_home\rdbms\audit audit files. BACKGROUND_ Defines the location for the database OS-specific Oracle Essentials DUMP_DEST alert log and for other background process trace files. CORE_DUMP_DEST Defines the location of database- Oracle_home\dbs related core files. LOG_ARCHIVE_DEST Defines the location(s) to which None and LOG_ARCHIVE_ Oracle will copy archived redo logs. DEST_N The latter parameter is only supported by Oracle8i Enterprise Edition. LOG_ARCHIVE_ This parameter, introduced in Oracle8, None DUPLEX_DEST has been supplanted by LOG_ARCHIVE_ DEST_N in Oracle8i Enterprise Edition. If you are not running Enterprise Edition, you must use this parameter instead. ORACLE_TRACE_ Defines the directory path for the OS-specific COLLECTION_PATH Oracle Trace collection definition, and data collection files associated with Oracle Trace. ORACLE_TRACE_ Defines directory path for Oracle Trace OS-specific FACILITY_PATH facility definition files STANDBY_ Only applies to stand-by databases. OS-specific ARCHIVE_DEST Defines the location of archived redo logs arriving from a primary database that will be processed by the stand-by database. USER_DUMP_DEST Directory location for user-generated OS-specific trace files. UTL_FILE_DIR Directory location for operating system None files generated by the Oracle package UTL_FILE. Repeat this parameter to add as many available paths as needed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  18. 126 CHAPTER 3 • CREATING ORACLE DATABASES Other init.ora Configuration Issues There are several other parameters you need to consider when setting up your database. These control everything from the number of datafiles you can have in your Oracle database to the location of control files, and a number of other database parameters. Number of database files Although the hard limit for the number of database datafiles is actually set by a parameter in the CREATE DATABASE state- ment, the DB_FILES parameter acts as a soft limit. If you try to add a datafile to the database and find you are unable to do so, you may find that this parameter is set artificially low. Locations of control files During database creation, the CONTROL_ FILES parameter defines the number and location of control files. After the cre- ation of the database, this parameter defines the location where the database will look for the control files. Maximum number of open cursors The Oracle database controls the total number of cursors that any given user session can open (see Chapter 16 for more on cursors). The parameter OPEN_CURSORS controls this limit. Often the default database value of 50 is not enough for even normal applications. We rec- ommend that you set this value at 100 or 150 to begin with. Maximum number of roles The parameter MAX_ENABLED_ROLES con- trols the maximum number of roles that a user can enable. Often the default value of 20 is insufficient. We recommend setting this parameter initially to 50. Maximum number of processes PROCESSES defines the total number of operating system processes that can attach to the database. The default value is dependent on the value of the parameter PARALLEL_MAX_SERVERS, and may not be enough for larger databases. For most databases, we allow for at least three processes per expected concurrent user, plus 30 for system overhead. Thus, if you expect to have 10 concurrent users, you’d set processes to 60. Maximum number of open cursors Each SQL statement that is run on your database will require one or more cursors to operate. Since recursive SQL requires its own cursors, it’s very possible that one query may require many cur- sors to be open simultaneously. Oracle controls the maximum number of open cursors in a given session, via the parameter OPEN_CURSORS. This parameter defaults to 50, which is rarely large enough. We suggest initially setting this para- meter to 150 at first. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  19. CREATING THE PARAMETER FILE (INIT.ORA) 127 Size of dump files It may be that you will want to limit the size of trace PA R T files, core dumps, and so on. To allow you to control the size of these files, Oracle I provides the MAX_DUMP_FILE_SIZE parameter. The default is to allow all dump files to be of unlimited length. Thus a single user could take up all the available disk space in your USER_DUMP_DEST directory if this parameter is not set. You can express this value in bytes, like this: Oracle Essentials USER_DUMP_DEST=1000 or in kilobytes or megabytes like this: USER_DUMP_DEST=1k USER_DUMP_DEST=1m Initial setting of ROLLBACK_SEGMENTS parameter We will talk about setting up rollback segments later in this chapter. For now, during database creation, you should not set the ROLLBACK_SEGMENTS parameter at all. Until you have created your database rollback segments, there are no rollback segments to enable! Other parameters The default values of most other database parameters should be sufficient to begin with. You’ll be monitoring your new database closely at first, to make sure that you don’t need to change such parameters as SORT_AREA_SIZE. Another example is the TIMED_STATISTICS parameter. This parameter allows you to derive timings on various database events including wait events and file I/O statistics. This parameter is disabled by default. Turning it on does entail additional overhead on the database, but this overhead seldom affects the overall performance of the database. Performance monitoring and tuning tips are covered in Chapters 15, 16, and 17. ifiles The ifile parameter in a parameter file is like a “goto” command. It instructs the database to open and read the file contained in the command. Once that file has been executed, the original init.ora will be read. These ifiles are often employed in databases that use Oracle Parallel Server to define settings com- mon to all instances. These ifiles can also be used to define specific types of data- base models (such as small, medium, and large database memory models). TI P We are not crazy about using the ifile parameter. It sometimes makes things quite confusing. We recommend, instead, that each init.ora be unique to each data- base and that common parameters be grouped together. Thus, file definitions are grouped together, as are memory settings, network settings, and so on. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
  20. 128 CHAPTER 3 • CREATING ORACLE DATABASES ARCHIVELOG mode parameters The database parameter file also con- tains LOG_ARCHIVE parameters that you’ll set if your database will run in ARCHIVELOG mode. This mode allows you to do hot backups of your database. In general, however, you will probably want to go with NOARCHIVE log mode at first (which is the default setting when a database is first created with the CREATE DATABASE command). No archived redo logs will be created at this early stage, thus reducing the overall I/O load during database creation. If you created the database in ARCHIVELOG mode and then decide to disable it, follow these steps: 1. Mount but don’t start the instance. 2. Issue the command ALTER DATABASE NOARCHIVELOG;. 3. Open the database in NOARCHIVELOG mode by issuing the SQL statement ALTER DATABASE OPEN;. 4. To put the database back into ARCHIVELOG mode, shut it down and mount it again. Issue the commands ALTER DATABASE ARCHIVELOG; and then ALTER DATABASE OPEN;. Creating the Database You’ve planned and calculated. You’ve sized and configured. It’s time to really begin creating the database, and this section walks you through the process. We’ll start with the work that must be done before you can issue the CREATE DATABASE statement (which actually creates the database). We’ll study the CREATE DATABASE statement itself, including how we can change it to better suit individual purposes. Then we’ll go through the steps of creating the database manually, followed by a discussion of the Database Configuration Assistant, Oracle’s Wizard-like tool that helps you create databases. So, let’s move on and get that database created! Preparing to Create the Database We are almost ready to actually create the database. This section gives you an overview of the CREATE DATABASE statement. We will also discuss modifications to SQL.BSQ, which is the file used by the CREATE DATABASE statement when creating the Oracle data dictionary. You can modify this file in order to reduce fragmentation of the SYSTEM tablespace. Finally, you’ll see how to write a SQL script that contains all the required steps for database creation. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
Đồng bộ tài khoản