# ORACLE8i- P4

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

0
53
lượt xem
4

## ORACLE8i- P4

Mô tả tài liệu

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ủ đề:

Bình luận(0)

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