This is the second article in a three-part series on inside tips and how-tos for Oracle administrators. The first article offered information on Oracle education and OCP exams and Oracle Internals. This article covers basic database administration techniques. Part three will cover tuning and performance and backup and recovery.

Using PUPBLD

PUPBLD.SQL can be used for more than stopping those irritating "PRODUCT PROFILE TABLE NOT LOADED" messages from occurring when you log on to SQL*PLUS. PUPBLD.SQL creates a product profile table that is read by SQL*PLUS when users log on to the product.

Front-end applications can be designed to prevent users from updating data incorrectly. What happens if the user logs on to the database using their database account and password using SQL*PLUS? The application code is not available to prevent them from making incorrect or unwarranted changes to the database data.

Administrators can prevent this from happening by inserting rows into the product profile table to disable a SQL*PLUS user's ability to execute:

• SQL*PLUS commands - COPY, EDIT , EXECUTE, EXIT, GET, HOST (or your operating system's alias for HOST, such as $ on VMS, and ! on UNIX), QUIT, PASSWORD, RUN, SAVE, SET, SPOOL, START
• SQL statements ALTER, ANALYZE, AUDIT, CONNECT, CREATE, DELETE, DROP, GRANT, INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, SET ROLE, SET TRANSACTION, TRUNCATE, UPDATE
• PL/SQL commands - BEGIN, DECLARE

SQL*PLUS reads restrictions from PRODUCT_USER_PROFILE when a user logs in to SQL*PLUS and maintains those restrictions for the duration of the session. To disable a SQL or SQL*PLUS command for a given user, insert a row containing the user's account in the USERID column, the command name in the ATTRIBUTE column, and DISABLED in the CHAR_VALUE column.

Copying Databases Between Servers

Don't use the EXPORT/IMPORT utility to copy databases between servers running the same operating system. Execute the following steps to speed the transfer:

• Execute the ALTER DATABASE BACKUP CONTROLFILE TO TRACE; statement on the source server
• Bring the database down and copy the trace file, parameter file, all datafiles, controlfiles and redo logs to the new server

Make the following changes to the trace file created in step 1:

• If you are changing the database name, change the first line of the create statement to reflect the new name and change the REUSE keyword to SET
• Change NORESETLOGS to RESETLOGS
• Change directory names if they have changed on the new database server
• Delete all comments and lines that have a # in front of them (#s aren't comments in all Oracle tools)
• Connect internal and run the SQL statement contained in the trace file. It will start up the database in NOMOUNT stage and recreate the control files

ALTER TABLE MOVE Statement

If you are using Oracle8i, don't use the export/import utilities to change initial storage settings for a table, defragment it, or move it to a different tablespace. Oracle8i introduces the ALTER TABLE MOVE statement that allows administrators to move data from one tablespace to another and change initial storage settings while the table is online.

Index COALESCE Vs. ALTER INDEX REBUILD

Space freed in a table because of row deletes becomes immediately available for reuse. This is not the case for the associated index entries. The index entries pointing to the rows are marked as deleted, but the space consumed by the index entries can not be reused until the index structure is compressed.

In volatile OLTP environments, you may have indexes that have only a few active entries per block. The results are inefficient B-Tree index structures and poor performance. The ANALYZE INDEX ... VALIDATE STRUCTURE statement can be used to identify the number of deleted leaf entries in the index structure. Oracle8I offers you two ways to compress an index structure.

The ALTER INDEX ... REBUILD statement is used to reorganize or compact an existing index or to change its storage characteristics. The REBUILD statement uses the existing index as the source for the new index. As a result, the index will consume twice as much space during the rebuild process. All index storage statements are supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).

ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress. The ONLINE keyword specifies that DML operations on the table or partition are allowed during the rebuild process.

The ALTER INDEX…..COALESCE statement instructs Oracle to merge the contents of index blocks where possible to free blocks for reuse. The index structure itself is not affected. Oracle works within the branches to remove the deleted leaf entries and compresses remaining rows into the smallest number of blocks possible.

The following information will help you choose which compress method to use:

• REBUILD can move an index from one tablespace to another. COALESCE can't
• REBUILD takes longer and requires more space. COALESCE is much quicker because it consumes less resources
• REBUILD creates a new B-Tree structure and shrinks the height if possible. COALESCE works within the same branch of the tree, will not move entries to other branches and will not shrink the height
• REBUILD enables you to quickly change storage and tablespace parameters without having to drop the original index. COALESCE can't
• If you shrink the height of an index, it will be faster than if the index was coalesced

Full Database Exports

When executing full database Exports, use CONSISTENT = Y to maintain a consistent view of the database. Oracle will ensure that all tables are consistent to the time the export utility started. Prevent "SNAPSHOT TOO OLD" messages by ensuring sufficient rollback segments are available. The Export will read the rollback segments to maintain a time consistent view of the data.

Large Table Imports

Use COMMIT=Y on Imports to reduce the chances of rollback segment problems when importing large tables. The COMMIT=Y parameter notifies Import to commit after each array insert. By default, Import commits after loading each table.

Like all programs that change data in Oracle, Imports write before images of blocks they are loading to the rollback segment. Specifying COMMIT=Y prevents rollback segments from growing inordinately large and improves the performance of large imports.

Truncating Vs. Deleting

The Oracle load utility and SQL language have TRUNCATE commands that can be used to quickly remove data from Oracle tables. TRUNCATE TABLE…….; is faster than the DELETE SQL statement. In addition, running a load with the TRUNCATE option specified is much faster than running the load utility with the REPLACE option.

The TRUNCATE option is faster because it doesn't generate any undo information, does not fire DELETE triggers and does not record any information in the snapshot log. Since it does not generate undo, you can not rollback the work to undo the removal of the data.

COPY command in SQLPLUS

Koch and Loney describe the COPY command as being "underutilized and unappreciated" in Oracle8I - The Complete Reference. The COPY command copies data from one table to another in the same or different databases. You are able to specify the columns to be copied, commit points (prevents those pesky rollback problems from occurring) and whether you want to append, create, insert or replace data in the target table.

If you want to safely copy small to medium size tables between databases, try COPY.

Relinking Oracle8i Products

Oracle V.8.0 and previous releases provide two different ways to relink Oracle:

• Using the MAKE command and specifying the MAKE files for a particular product
• Using the Oracle installer and relinking from the administrative task menu

Oracle 8.1.5 continues to provide support for the MAKE command but the Oracle Universal Installer no longer has an option to perform product relinks. Oracle 8.1.5 introduces a more user-friendly tool to relink the Oracle products. The RELINK shell script in $ORACLE_HOME/bin can be used with the following parameters to relink the Oracle executables:

ALL -- everything which has been installed

ORACLE -- Oracle database executable only

NETWORK -- net_client, net_server, cman, cnames

CLIENT -- net_client, otrace, plsql

CLIENT_SHAREDLIB - interMedia ctx, ordimg, ordaud, ordvir, md

PRECOMP -- all precompilers which have been installed

UTILITIES -- utilities

OEMAGENT -- oemagent, odg

Checkpoint Not Complete

Checkpoints ensure that all modified data buffers are written to the database files. One (out of several) reasons Oracle can fire a checkpoint is when it switches from one log group to another. When Oracle fills a log group, a checkpoint is fired and Oracle then begins writing to the next log group.

This continues in a circular fashion; when all log groups are filled Oracle reuses the first group. The process by which DBWR writes modified data buffers to disk in Oracle is not synchronized with the COMMIT of the corresponding transactions. The checkpoint ensures that all the modified data buffers in the cache that are covered by the current log are written to the corresponding data files.

A common rule of thumb is to adjust the redo log's size so that Oracle performs a log switch every 15 to 30 minutes. Log switches that occur more frequently may have a negative impact on performance. Log switches that occur several times a minute have a definite impact on database performance.

Checking messages in the alert log is an easy way to determine how fast Oracle is filling and switching logs. If the following messages are found, you can be sure that performance is being affected:

        Thread 1 advanced to log sequence 248
        Current log# 2 seq# 248 mem# 0:
        /orant/oradata/logs/redolog2a.log
        Thread 1 cannot allocate new log, sequence 249
        Checkpoint not complete

In this case the following condition has occurred: REDO_LOG WRITE TIME < CHECKPOINT WRITE TIME. The "checkpoint not complete" messages are generated because the logs are switching so fast that the checkpoint associated with the log switch isn't terminated. Oracle stops processing until the checkpoint completes successfully. If Oracle is unable to verify that modified data associated with the current log is on disk, it can not ensure database consistency.

Performance can be dramatically improved by increasing the log sizes so that logs switch at the recommended interval of 15 to 30 minutes. Identify the current size of the redo log members from V$LOG, record the number of log switches per hour and increase the size of the log to allow Oracle to switch at the recommended rate of one switch per 15 to 30 minutes.

For example, if the database log size is 1 megabyte and you are switching logs every 1 minute, you will need to increase the log size to 30 megabytes in size to allow it to switch every 30 minutes.

Displaying Time in Milliseconds

• You can use DBMS_UTILITY.GET_TIME function to display the time in 100th of second

      SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

Finding Duplicate Rows

The following query can be executed to list duplicate rows in a table:

    SELECT FROM duplicate_table
    WHERE rowid not in
    (SELECT MIN(rowid)
    FROM duplicate_table
    GROUP BY column1, column2, column3... ;

Where column1, column2, column3 are the columns that make up the key that is supposed to uniquely identify the row.

!, $ and Host Command Differences

You can execute a "!" on UNIX and a "$" on MVS, VMS and Windows to execute operating system commands as child processes of SQL*PLUS. You can also execute the keyword "HOST" to do the same thing. What's the difference? "HOST" will perform variable substitution (& and && symbols), whereas "!" and "$" will not.

Learn Command Line Before Using OEM

Oracle Enterprise Manager may have a nice GUI interface, but it won't help you if you can't execute it. What happens when you get called at home or you don't have access to the GUI? Learn command line first and then use OEM.

Don't Write Scripts

Don't write tuning, administration or monitoring scripts if you don't have to. The Internet has an abundance of web sites containing hundreds of scripts. Websites to try first are Tusc.com or Orafans.com, or search the Web using "Oracle scripts" as the key word. Don't reinvent the wheel. Find a reputable site and save time.

--

Christopher Foot has been involved in database management for over 18 years, serving as a database administrator, database architect, trainer, speaker, and writer. Currently, Chris is employed as a Senior Database Architect at RemoteDBA Experts, a remote database services provider. Chris is the author of over forty articles for a variety of magazines and is a frequent lecturer on the database circuit having given over a dozen speeches to local, national and international Oracle User Groups. His book titled OCP Instructors Guide for DBA Certification, can be found at http://www.dba-oracle.com/bp/bp_book14_OCP.htm.