Java Outside the Database

Java code commonly resides outside the database. Sometimes the code resides in a Web application server, other times it is standalone. However the need to access data inside a database is still present. Since Oracle 7.3.4, Java can make SQL calls to a database. The two ways to do this are by using JDBC or SQLJ. Java Database Connectivity (JDBC) is an open method that allows Java programs to issue SQL statements to any database, not just Oracle. Conceptually this is similar to ODBC (Open...

Editing a Dump dmp File

Oracle documentation and support personnel state that .dmp files are not editable and any attempt to open these files will corrupt them beyond repair. Furthermore, Oracle Support will not support you if you attempt to edit a .dmp file. Having said that, I will show you a way I have found to edit .dmp files to change storage parameters. Be warned, however, that this procedure is totally unsupported and that I am not responsible if you try it and it corrupts your files beyond repair. If you...

Monitoring Network Usage

One tool commonly used by DBAs is tnsping. This is not a Unix network performance tool it is actually an Oracle utility used to test database connectivity. However, it does provide timing statistics. You can use it as shown in Figure 13.3. licrosoft Windows 2000 Uersioi < C> Copyright 1985-1999 Micros- TNS Ping Utility for 32-bit Uii 31 20 23 26 < c> Copyright 199 Oracle Corporation. fill rights res( Attempting to contact < ADDRESS OK < 160 pisec> As you can see here, it is an...

Monitoring Rollback Segment Usage

There are two main areas you want to watch for regarding rollback segments contention and size. Contention is more common in OLTP environments. It occurs when several transactions are fighting for access to a rollback segment. Specifically, each rollback segment has a header containing a transaction table. This transaction table contains information about what is being written to the rollback segment. There are several extents in a rollback segment, but there is only one header. Therefore, this...

Using Undo Tablespaces

Oracle 9i also attempts to reduce maintenance duties involving rollback segments. If you want, you can still use traditional tablespaces, whereby you create rollback segments and manage the extents. However, 9i introduces a new type of undo tablespace that replaces traditional rollback segments. In fact, Oracle documentation states that the traditional method of creating rollback segments is being depreciated and strongly recommends using undo tablespaces. Undo tablespaces perform the same core...

Storage Hierarchy

Managing objects is easier said than done. A single production schema can have 10,000 objects, including tables, indexes, sequences, synonyms, and PL SQL packages, procedures, and functions. Obviously the DBA doesn't manage each of these individually, but the DBA is obligated to make sure each of these is in a reasonable tablespace and has enough space to grow if needed. Before you go any further, you should understand the storage hierarchy of Oracle objects, shown in Figure 6.5. The storage...

Redo Log Buffer

The redo log buffer holds changed data until it can be written to the online redo log files. This buffer is cleared frequently so it does not need to be very large. However, it does need to be large enough so that server processes writing changes to it can find space quickly. The redo log buffer is sized by LOG_BUFFER. The performance is measured by the ratio of the number of redo log space requests to the number of redo entries. This represents how often a server process had to wait for space...

Hot Backup Script

The following is a small piece of code that enables you to initiate hot backups. Use this as a sample for your script. I used scripts to generate this dynamically, but it can be hard coded as well. I also could have made use of Unix environment variables for the copy and compress steps, but I wanted to keep it simple. This script first spools to create a log. Next it puts a tablespace in hot backup mode. It uses cp to copy the file to a backup location. The - p option is probably not necessary....

How Unix Manages Memory

Understanding how memory is managed within Unix is very important to being a DBA, particularly when you're performance tuning and troubleshooting. Knowing how the kernel will react when you create an SGA of several hundred megabytes is extremely relevant, especially when a novice SA insists it is Oracle making the machine swap. In situations like these, your knowledge of how Unix works is the best protection when people start complaining about performance. Unix works on the principle of virtual...

Shared Pool

The shared pool provides caching in memory to improve performance for the same reasons as does the database buffer cache. The shared pool, however, caches data dictionary information and parsed SQL statements rather than data blocks. Like the database buffer cache, proper sizing and management of the shared pool is critical for good database performance. The shared pool is composed of two caches the data dictionary cache and the library cache. Database control data regarding the status of the...

Miscellaneous Features and Changes

There are many more new features and changes that deserve mentioning. Use this list to identify features you should explore as you find the time. Oracle Parallel Server (OPS) is now called Real Application Clusters (RAC). RAC is the system of having a physical database on a shared disk array that's accessed simultaneously by multiple instances on separate nodes. This provides improved fault tolerance and can be used to partition the application. This feature has been available since Oracle 7,...

Setting Up and Monitoring cron Jobs

First, create your shell scripts as normal. Be sure to fully define the environment 6 variables within your scripts. If you don't set your environment variables, they will not be set automatically your script will fail. Scripts can call other scripts but make sure you > declare the full path and filename in the scripts. SQL*Plus scripts can also be executed from within shell scripts. 2. Next, create a directory where your crontab (file with your jobs) will be stored. The user oracle will have...

Recovering from a Combination of Lost Files

The chapter has covered the basics of losing individual data, online redo logs, and control files. Individually, the recovery for each file is fairly simple as long as you have good backups and multiplex your control and online redo log files. Unfortunately, however, disk crashes seldom take just one file with them. Normally, you will lose several files at once and this complicates the recovery. This section looks at a sample recovery of a control file, an online redo log file, a SYSTEM...

Creating a Server Parameter File SPFILE

In previous versions of Oracle the primary source of database parameters was the text-based init.ora. From that file it was common to read config.ora and initdef.ora files for additional parameters, but all of these files were text based. This changes in Oracle 9i. A binary server parameter file (SPFILE) can now be created from the text-based init .ora file and used instead, if desired. To create an SPFILE you must have a preexisting init .ora file to use as a starting point. Next, log in to...

Migration Steps Using ODMA

The following example uses ODMA to migrate (or upgrade, using Oracle terminology) an Oracle 8.1.6.1 database to Oracle 9.0.1. In reality, the migration process for 8i to 9i is more like an upgrade (basically running scripts) than from Oracle 7.3 to 9i. You'll see where the two paths diverge once you're inside the tool. The migration process can be broken into three steps preparing the database, using ODMA to migrate the database, and performing post-migration checks. Before you migrate your...

Server Processes

A server process acts as a go-between for the user process and the Oracle memory structures. When a user process has a request for work, that work is done by a server process. Reading data from disk and placing it in memory also is done by the server process. For example, when a user process requests data, it is the server process that scans the database buffer cache to find it and, if the data is not in the cache, the server process reads the data block from memory into the buffer cache. The...

Info

Prepare the rollback segment inside the SYSTEM tablespace. You don't want this to blow out in the middle of your migration nor do you want it to shrink. Give it the maximum limit of 505 extents, turn off OPTIMAL, and set the PCTINCREASE to 50 . SQL> alter rollback segment system 2 storage (maxextents 505 optimal null next 1M) SQL> alter tablespace system default storage (pctincrease 50) Tablespace altered. 4. Make sure that the default and temporary tablespace for SYS and SYSTEM is SYSTEM....

C

Server), 471-472 library cache, 46-47, 303-304 cache hit ratio, 302 capacity planning, 67-68 disk drives, 69 memory, 69 operating system, 68-69 server costs, 70 system maintenance, 70-71 technology and vendors, 71 cat command, 526 catalog.sql script, 382 catproc.sql script, 382 catrep.sql script, 382 CBT (Computer Based Training), 514 cd command, 526 cdump (core dump) files, 74 central processing units. See CPUs certification. See also continuing education Database Administrator, 516 Database...

Monitoring the CPU

The previous chapter mentioned that only one process can actually be running on a CPU at any given time. However, by using time-slicing, Unix can service multiple processes nearly simultaneously to give the illusion of one CPU per user. By using SMP machines in which you have multiple processors executing simultaneously, the capability to service more processes without bottlenecks improves. There are two ways to look at CPU usage by using utilities to examine activity on the CPUs or by looking...

Available Certification Tracks

Until just recently, there were three categories of OCP Database Operator, Database Administrator, and Application Developer. Database Operator is the simplest certification. It has one test focused on basic database principles and OEM. Some people consider it the equivalent of a Junior DBA test. Database Administrator is the main certification most people attempt. It has five tests covering the following areas Introduction to SQL and PL SQL By no mere accident, these correspond exactly to the...

Examine the Database Instance

The next step is to login to the database using SQL*Plus and look around. Use the default system manager account for this. Production on Sun Feb 18 17 59 13 2001 (c) Copyright 1999 Oracle Corporation. All rights reserved. Enter password Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production JServer Release 8.1.6.0.0 - Production SQL> show user USER is SYSTEM SQL> There are two security issues here. First, Oracle always creates the SYSTEM account with manager as the password and the SYS...

Hot Backups and Recoveries

Hot backups (aka online backups) are copies of data files taken while the database is up and running. Only databases in ARCHIVELOG mode are eligible for hot backups. Furthermore, the backup is not simply an OS level copy of the entire database at once. Each tablespace is placed in hot backup mode, which freezes the data file headers so the SCN does not increase. Then, each data file in the tablespace is copied to the backup destination. Once those files are successfully copied, that tablespace...

Customize the Scripts

As the oracle user, go to the ORACLE_BASE admin subdirectory. If you used the DBCA, it should have made your subdirectories for you. Otherwise, make a subdirectory named after the SID you plan to create. Then, type these commands u01 app oracle admin mkdir rh1dev1 cd rh1dev1 mkdir create This create directory is where you edit your scripts. Copy the scripts created by DBCA to this location u01 app oracle admin rh1dev1 create cp . ls rhidevisqlplus.sh rhidevirun.sh rhideviiMedia.sh sqlrhidevi.sh...

Multiplex Control Files

Multiplexing control files means maintaining several identical control files for the same database. Oracle will read from the first control file created, but will write to all the control files just as if there were only one file. The database will still crash if any control file is lost, but recovery is greatly simplified. Typically, control files are multiplexed during database creation. The database-creation assistant and most scripts will automatically create the database with three control...

Export and Import with Compress and Pipe

Historically, Unix files were limited to a maximum file size of 2G (gigabytes). This limit posed problems when you attempted to export and the resulting .dmp would exceed 2G. On most modern Unix systems this limit no longer exists and most versions of Oracle no longer enforce this limit. However, the need to deal with large files still exists. 0 It is not uncommon to export a schema and have the .dmp file range from several hundred > megabytes to several gigabytes. Even if the Unix operating...

Recovering from a Lost Redo Log Member

Losing an online redo log file is not a major problem if it is multiplexed. Theoretically, as long as the member is multiplexed, your database should not go down, although Oracle will issue error messages in the alert log. If, however, you lose an entire group, expect the database to crash. This section assumes that you did wisely multiplex your redo log groups and have at least two members each. First, before a crisis occurs, let's look at the groups and members. As you can see in the...

Unix Processes

Any program really executes as one or more processes. Within the context of a program, a process executes to perform a task. For example, the LGWR background process writes from the online redo log buffer to the online redo log files. That task is part of the Oracle program. The same principle holds true whether the program is Oracle, a simple shell script, or a Unix command such as grep. At any given time, there are usually hundreds of concurrent processes running on the system. Some are...

Database Writer Process DBWn

The database writer (DBWn, also called DBWR) writes modified (dirty) blocks from the database buffer to disk. DBWR does this to clear out old dirty blocks to make room for new blocks in the cache. The DBA should note that this includes both committed and uncommitted data. Oracle will be able to keep track of which data is not yet committed, so it will move out those blocks when necessary. DBWR does not write unmodified (clean) blocks to disk because there is no reason to do so. Multiple DBWRs...

Emerging Technologies

After you have been trained and are working as a DBA, it is up to you to learn new technologies. In recent years, the roles of the Internet, LDAP, Java, and Web servers have become more important to the DBA. There is a big push to Web-enable many preexisting systems, which of course has an impact on the database. As Java supplements and replaces PL SQL, you must understand how, why, and where it is used. Oracle is continually phasing these technologies into each new database release. The...

Using Oracle Managed Files

Oracle 9i offers a new feature that attempts to free you from the details of managing database files. Oracle-managed files allow you to create data files and tablespaces normally, but Oracle manages these aspects Location Each file is placed in a default directory location. This parameter can be modified with ALTER SYSTEM. Name Each file has a unique name within the database. It still follows OFA conventions and data files include the tablespace name, but each file is given a system-generated...

Avoiding File Contention

Historically, spreading high-contention files across multiple filesystems has been a common way to tune a database. This process can improve performance if an I O bottleneck exists. Ideally, competing files such as data and indexes will be identified and separated before the database is created. You learned about this principle and which files to separate in Chapter 3. Once a database has been created, you should check to see which files are being frequently written to and read. Because you can...

Raid

Redundant Array of Inexpensive Independent Disks (RAID) is a method of creating filesys- X tems across multiple physical disks. Basically, you have one logical filesystem, but it is com- O ER posed of several underlying disks. The benefits of using RAID are two-fold performance and fault tolerance. Performance is improved with RAID because data is striped across multiple disks. Unix will issue a read or write to the device driver, but the actual read or write will take place over several disks,...

Understanding the Role and Future of Java Inside Oracle

Anyone involved with computers or the IT industry knows that the Internet is the key to staying competitive. Every computer vendor certainly knows this. As a result there has been a mad rush for several years to Web-enable virtually every aspect of computing. Oracle has embraced this idea and has gone to great lengths to make its database accessible and supportive of the Internet. Oracle created several new products and restructured others to support this initiative. These products include...

Monitor Space on Filesystems

The DBA is better qualified than anyone to monitor the disk usage of his her filesystems. Although the SA should have scripts that monitor and issue warnings if a filesystem begins running out of space, this is really the DBA's responsibility. If a routine backup or export fails because it ran out of disk space, it is the DBA who is accountable, not the SA. On filesystems where you are dumping large export, log, or backup files, identify how much space you have and how much is used each night....

Cleaning Up Shared Memory and Semaphores

Although it happens infrequently, there are times when an instance crashes, but shared memory and semaphores for that instance still exist. In reality, the server and background processes have terminated and the files are no longer accessible, but memory is still allocated. This is a problem because before you can restart the instance, these shared memory areas and semaphores must be cleared out of the system. This is not just because they are a waste of resources the new SGA will not allocate...

Using Dynamic Memory Parameters and Multiple Block Sizes

One of the most exciting new features of Oracle 9i is the capability to adjust the SGA without bouncing the database. Obviously this has large implications for systems with high uptime requirements. The process of defining the size of the database buffer cache has also changed. This section covers these changes. Have you ever had a production database with lots of active users, only to find that you need to increase a parameter like the shared pool to meet an unexpected demand It's a hassle to...

Automatic Database Startup

When a Unix or Linux server shuts down (planned or otherwise), any databases running on it are also shut down. Once the server is restarted, any databases will remain shutdown by default. This can pose a problem if the databases need to be restarted. Unless there is a DBA on call 7x24 just in case there are problems, the solution is to have the database automatically restart on machine startup. Unix and Linux servers have a set of predefined stages they go through as they shut down or start up....

Files on u01

Underneath the first mount point, create an app subdirectory. This is where the application runs. Next, on u01 app, create an oracle subdirectory. Beneath the u01 app oracle directory, an important split takes place. The subdirectories product, local, and admin are created, as shown Under product, each version of the Oracle database is installed. For example, if you were running Oracle 7.3.4 and Oracle 8.1.6 on the same machine, you would have u01 app oracle product 7.3.4 11 8.1.6 This is an...

Using Log Miner

The online redo log files and any resulting archive log files provide a wealth of information because they are basically transaction logs. Any changes that occur within the database go to these files. Although this represents potentially useful information, the DBA historically had no way to view these files. All that changed with the advent of the LogMiner utility. The contents of the online redo logs and archived redo log files can now be viewed by a DBA. If, for example, you know that...

Multiplex Online Redo Logs

Multiplexing online redo log files is conceptually very similar to multiplexing control files. Oracle automatically maintains one or more copies of a file. The key difference is that the loss of a multiplexed redo log file will generate an error message, but the database instance will still survive. Additionally, no data will be lost. Given these benefits, it is a very good idea to multiplex each redo log group on a different filesystem. You normally multiplex redo log files after the database...

DML Locking

Because Oracle practices row-level locking, the most common problems occur when two users are trying to update the same row at the same time. As long as the first user commits or rolls back rapidly, this is usually not a problem. Problems occur when one user updates a row, but does not commit or roll back the change immediately. Any other user attempting to update the same row will issue their statement, but no response will come back from Oracle. It will look to them like the screen is...

Non Technical Responsibilities

Fulfilling non-technical responsibilities is a key part of being a successful DBA. These are the skills not normally taught in class or detailed in database manuals, but they are what determine who really adds value to an organization. As an Oracle DBA, you are expected to know everything about Oracle, all of its products, the future direction of the technology, and the business practices of the database vendors. This is an impossible task, so it is acceptable just to be highly knowledgeable...

Database Configuration Assistant

The easiest way to get a basic set of database-creation scripts is to have the Database Creation Assistant (DBCA) generate them for you. The DBCA asks you a set of questions about the needs of your database and then offers to either create the database for you or to generate a set of scripts to create it. This chapter uses the DBCA to generate the set of scripts to create a basic hybrid database. You will then use these scripts as your template to create your real database. The set of scripts...

System Monitor Process SMON

The System Monitor process is a required Oracle process that is normally sleeping. It wakes up periodically and performs its tasks automatically without any interaction from the DBA. SMON automatically performs the following activities Instance recovery after a crash. This is the roll forward and roll back stage in which transactions are resolved to the last completed checkpoint. Coalesces (merges) free space on disk within dictionary managed tablespaces if the PCTFREE parameter is greater than...

Java Pool

The Java pool is a relatively new memory structure first introduced in Oracle 8i. It is used to store shared Java objects. The more stored Java procedures and EJBs you use, the larger this parameter should be. This pool is used especially during loading of classes, but compiling objects will also use it. The parameter JAVA_POOL_SIZE sizes the Java pool in bytes. The default size is 20M, with 50M common for systems using more Java. However, a JAVA_POOL_SIZE of 100M or more is needed if init...

Server Installation

Configuring the server tier for OEM is also optional however advanced features provided by OMS are dependent on this. Also, some OEM tools require a repository, Intelligent Agents, and Data Gatherer even if you choose not to use OMS. Most Unix DBAs create OEM repositories and start Intelligent Agents even if they choose not to use OMS. To create an OEM repository, you need to start the OEM Configuration Assistant, as shown in Figure 7.13. This is found on NT Windows 2000 installations under...

Hybrid Systems

Hybrid systems are those with a mix of OLTP and DSS needs. Although most applications are OLTP in nature, most systems are really hybrids. For example, virtually any business that takes orders from customers is OLTP. However, how long can those businesses exist without financial or sales reports Although the OLTP application is where the company actually makes the money, there are back-end support functions that need DSS (batch) processing. Review the needs of OLTP versus DSS. Do they look like...

Compile Invalid Objects

The next step of verification involves getting object counts and recompiling invalid objects. Login as SYSTEM into SQL*Plus and issue the following command SQL> select object_type, count(*) from dba_objects 2 where status 'INVALID' group by object_type To get the names and types of each invalid object, type the following col object_name format a30 col owner format a10 SQL> select owner, object_name, object_type from dba_objects 2 where status 'INVALID' order by object_type, object_name In...

S

CPU usage statistics, 367 disk I O monitoring, 361 7, 22-23 scalability of Web architecture, 467-468 sched scheduler process, 324 Schema Manager, 196 schemas backing up, 216-217 migrating, 217-219 Oracle 9i server sample schemas, 506-508 Schema Manager, 196 Numbers , 34 scope of DBA database administrator responsibilities, 6 screen, clearing, 129, 526 script command, 529 scripts. See also commands B ESTAT, 296-298 catalog.sql, 382 catproc.sql, 382 catrep.sql, 382 compare_users.sql, 541...