Previous | Table of Contents | Next

Page 111

Your objective in creating these tablespaces is to separate operational and application data and functions. The TEMP, RBS, and RBS_02 tablespaces hold objects necessary for the Oracle database to function, while TOOLS, USER_DATA, and USER_INDEX hold user data. See Part VIII, "Performance Tuning," for more information on separating data, I/O, and functionality using tablespaces.

Also, create your production rollback segments. Create at least two rollback segments in the RBS tablespace to support OLTP transactions. These segments should be relatively small, with many extents. Name them RBS##; for example, RBS01, RBS02, and RBS03. Create one rollback segment in the RBS_02 tablespace for bulk data loads. This segment should be large with fewer extents. Call this rollback segment RBS_LOAD##, for example RBS_LOAD01. Bring the OLTP rollback segments online and place their names in the rollback_segments init.ora value list.

When you have finished these tasks, drop the temporary rollback segment you created earlier by issuing the following command:

drop rollback segment rbs_temp;

Securing the Default Accounts

A new database always has two default system accounts—SYS and SYSTEM. The initial password for SYS is CHANGE_ON_INSTALL, and SYSTEM is MANAGER. Change these immediately. It would be frightening to know how many databases can be broken into by logging in as system/manager.

Also, if you have installed the demo tables, consider changing the scott userid's password from the commonly known tiger to something else.

Updating the System Configuration Files

Finally, you need to register your new database in all of the configuration files on your system. If this is the first database on the system, you'll want to set your ORACLE_SID variable to reflect this. On a Windows NT server, you will need to create this value before you can set it. If you are on a UNIX server, you'll want to update oratab to reflect the new database information. Also, put the database in the tnsnames.ora and listener.ora files so they are reachable via SQL*Net.

At this point (assuming all of the previous steps completed successfully), you have a fully functional Oracle database to start your development, testing, or playing.

Exploring the Oracle Database

Anyone who works with Oracle for any length of time will sooner or later face the task of administering, troubleshooting, or working within a database environment that is completely unfamiliar to him or her. It is important to be able to quickly familiarize yourself with the custom configurations and setup of a foreign environment. This will ensure that your time is spent handling the critical task(s) at hand, rather than wasting it on time-consuming and trivial items

Page 112

such as what rollback segments you can use for your data load, what tablespace to store your user data in, and so on.

In the following sections, we'll look at how to extract information about the database from the database. The information in these pages coupled with the information covered in the previous sections will allow you to complete your picture of an unfamiliar database environment (and perhaps even your own).

Looking at the Database

The first order of business is to get a handle on the database and its objects. This includes looking at the datafiles, tablespaces, redo logs, and rollback segments, as well as memory allocations to the various caches and what background processes are running. Database jobs stored in the job queue and the users and security privileges assigned to database users are also of interest.

The v$database and v$thread data views are useful to locate information on the database and instance, respectively. v$database will show you the database name and archiving mode (whether the database is running archivelog or noarchivelog mode). v$thread is specifically a Parallel Server view, but it is useful because it gives the instance name (more of an issue on a Windows NT server than in the UNIX environment). You also can query v$instance for the database open time. In Oracle8, v$instance contains the startup_time field, which displays the instance start time. If you are using Oracle7, the following query will transform the v$instance startup information into a usable format:

SELECT TO_CHAR (TO_DATE (a.value, `J') + b.value/86400, `HH24:MI:SS
DD-MON-RR') start_time
FROM v$instance a, v$instance b
WHERE a.key = `STARTUP TIME - JULIAN' AND b.key = `STARTUP TIME - SECONDS';

The output of this query will look similar to the following:

START_TIME
------------------
22:48:37 06-MAR-98

In Oracle7, this rather elaborate query is necessary because the Julian date portion of the startup time is stored in one column in v$instance, while the time portion of the date is stored in a separate field (in seconds).

NOTE
v$thread contains an OPEN_TIME field that may be different from the time displayed in v$instance. This is because one field displays the time the instance was created, while the other shows the time at which the database was opened and made available for access.n

You can list the active controlfiles by querying v$controlfile. Rollback segment information as stored in dba_rollback_segs, v$rollstat, and v$rollname. Dba_rollback_segs will show information on all rollback segments in the database, while the v$ views will only list those rollback segments that are currently online. The following query will make sense of the online rollback segment information:

Page 113

SELECT a.segment_name, b.bytes, b.extents, a.tablespace_name, c.shrinks,    
       c.extends, c.hwmsize
FROM dba_rollback_segs a, dba_segments b, v$rollstat c
WHERE a.segment_id = c.usn AND a.segment_name = b.segment_name;

This query will produce output similar to the following:

SEGMENT_NAME BYTES EXTENTS TABLESPACE_NAME SHRINKS EXTENDS HWMSIZE
RBS01 102400 2 ROLLBACK_DATA 1 3 100352
RBS02 102400 2 ROLLBACK_DATA 0 0 100352
RBS03 102400 2 ROLLBACK_DATA 1 1 100352
SYSTEM 204800 4 SYSTEM 0 0 202752

Information on the redo logs is stored in the v$log and v$logfile views. The following query will display pertinent information on the redo logs created for the database:

SELECT member, bytes, members, a.status
FROM v$log a, v$logfile b
WHERE a.group# = b.group#
ORDER BY member;

The output of this query will look similar to the following:

MEMBER BYTES MEMBERS STATUS
C:\ORADATA\PPRD\REDO_PPRD_01A.LOG 204800 1 CURRENT
C:\ORADATA\PPRD\REDO_PPRD_02A.LOG 204800 1 INACTIVE

Mapping your datafiles and tablespaces is important. Query dba_data_files for this information. You can query information specific to your tablespaces from dba_tablespaces, and datafile- specific information from v$datafile.

Query v$sgastat for information on instance memory allocations. The following query may be helpful:

SELECT name, bytes
FROM v$sgastat
WHERE name in (`free memory','fixed_sga','db_block_buffers','log_buffer','dictionary cache',
`library cache','sql area');

The results of this query run on a very small database are shown:

NAME BYTES
free memory 2287676
fixed_sga 35208
db_block_buffers 409600
log_buffer 8192
dictionary cache 226376
library cache 247636
sql area 466040

Previous | Table of Contents | Next