E10000 HPC Customers Sales Tools Market Dev. SAE/Benchmarks Competition About DHPG Home
SAE/Benchmarking Information
SITEMAP
You are here: dhpg.west/SAE/Tuning/oracle/Oracle_FAQ.html

Oracle Admininistration and Tuning

Installation and Setup

0.1My installer session has its pathnames messed up, where are they stored ?
0.2What are the current parameter settings ?
0.3Which Oracle init.ora parameter are typically too small for VLDB.
0.4Here's a fairly complete list of environment variables ora env vars
0.5How do you set up big rbs, OR my public rbs keep going offline

DBA level Problems and Solutions

1.1What do I do when I get the not enough datafiles message ?
1.2Move a user's schema between tablespaces.
1.3How to set up a block dump trace
1.4How do I tell how many Rollback segments are online ?
1.5I got a bad block in a tablespace, how can I recover the database ?
1.6I got a bad block in a rollback segment tablespace, how can I recover the database ?
1.7Somehow the unix file was destroyed, there was nothing in that tablspace, so its no big deal, but when I try to drop the tablespace I only get a File Not Found?

RDBMS Tuning

2.1Do I have enough shared pool ? See also Interpreting /Estat/bstat statistics

Load Unload Import Export Tuning

4.1How do I speed up the loader ?

SQL*NET

5.1How do I Set up to run SQL*NET V1 ?
5.2How do I Set up to run SQL*NET V2 without using netman?
5.3How do I Set up oratab ?
5.4After a system crash, the SQL*NET won't come back up. Whats wrong?

PL/SQL

6.1How do I debug PL/SQL ?
6.2How do I get output to work ?

SQL Tuning

7.1Here's method for fast case-insensitive search ?

Minutae

8.1How do I get the prompt to include the SID ?
8.2How do I get users to get in under their OS id/password ?
8.3How do I show more info about init.ora parameters ?
8.4The installation worked perfectly alright but hardly any applications work.

PRO*C

9.1How do you use the same variable to transfer data through the SQL <> C I/F ?

0.1 My installer session has its pathnames messed up, where are they stored ?

This file has the pathname in it: pfile.spc, for example: PFILE_test73=/ptmp/7.3.1/dbs/inittest73.ora

0.2 What are the current settings ?

Start up svrmgrl (or sqldba for 7.2 or before) and after connecting as internal, type show parameter xxx. Oracle will print out all parameters with the string 'xxx' in them.

0.3 Here a list of init.ora variable that are typically too small for VLDB.

For DSS: sort_area_size = set up to to 4m. And for OLTP: shared_pool transactions = > 1000 processes = > 1000 enqueue_resources = 2000 enqueue_resources = 2000

0.5 How do you set up big rbs, OR my public rbs keep going offline

Use the orarbs tool from /opt/GOODies/bin with the -big option.

1.1 What do I do when I get the not enough datafiles message ?

As with any of the create database parameters, you can't change this on the fly. You need to create a new control file, and restart the database with the new control file. create controlfile set database demo maxinstances 8 maxlogfiles 32 resetlogs datafile '/oracle/dbs/systdemo.dbf' size 50M logfile '/oracle/dbs/log-1' size 50m, '/oracle/dbs/log-2' size 50m, '/oracle/dbs/log-3' size 50m, '/oracle/dbs/log-4' size 50m maxdatafiles 200;

1.2 To move between tablespaces

Move between tablespaces by exporting and them revoke the user's priv. in the "from" tablespace and set the user's default tablespace to the "to" TS.

1.3 block_dump.

alter session set events 'immediate trace name blockdump level NNN';

How do I tell how many Rollback segments are online

Use the GOODies tool orarbs with the -d option. Or

SELECT SUBSTR(DS.SEGMENT_NAME,1,22) R_SEGMENT, SUBSTR(DS.TABLESPACE_NAME,1,20) TABLESPACE, DS.BLOCKS, DS.EXTENTS, DRS.STATUS FROM DBA_SEGMENTS DS,DBA_ROLLBACK_SEGS DRS WHERE DS.SEGMENT_NAME = DRS.SEGMENT_NAME ORDER BY 1;

1.5 I got a bad block in a tablespace, how can I recover the database ?

Assuming you can reload one tablespace at a time, it may be faster to do this method than re-load everything. Bringup up the database in a nomount mode.

svrmgrl connect internal startup nomount alter tablespace offline drop;

1.6 I got a bad block in a rollback segment tablespace, how can I recover the database ?

The method used in 1.5 won't work on rollback tablespaces. You must use an undocumented init.ora variable to bring back the database if you get a corrupted rollback segments. For version 7 or 8 it is: _corrupted_rollback_segments.

This method can save rebuilding the database if you have a power hit or other trouble that corrupts the Oracle database (no backup, no ARCHIVELOG mode).

alert log --------- Tue Jul 28 08:50:38 1998 alter database open Beginning crash recovery of 1 threads Recovery of Online Redo Log: Thread 1 Group 1 Seq 172 Reading mem 0 Mem# 0 errs 0: /dev/vx/rdsk/rsm219/redo1_1.v1 Mem# 1 errs 0: /dev/vx/rdsk/rsm219/redo1_1.v2 ORA-1172 signalled during: alter database open... %oerr ORA 1172 01172, 00000, "recovery of thread %s stuck at block %s of file %s" // *Cause: Crash recovery or instance recovery could not apply a change to a // block because it was not the next change. This can happen if the // block was corrupted and then repaired during recovery. // *Action: Do a RECOVER DATAFILE for the file containing the block. If this // does not resolve the problem then restore the file from a backup // and recover it.

trace file

>Tue Jul 28 08:50:39 1998 >*** SESSION ID:(6.1) 1998.07.28.08.50.39.000 >RECOVERY OF THREAD 1 STUCK AT BLOCK 12802 OF FILE 9 So, here is the procedure I took for the recovery. 1. startup mount 2. select * from v$datafile <- find out what FILE 9 is. I found FILE 9 was one of the ROLLBACK files. 3. drop corrupted files alter database SID datafile 'rollback data file' offline drop; 4. shutdown 5. vi init.ora #rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08,r09,r10) <- comment out rollback sements _corrupted_rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08,r09,r10) rollback_segments = (r0) <- use rollback in system tablespace (I tried _offline_rollback_segments, but that didn't work). 6. startup 7. BINGO! database is up. Drop rollback segments, drop/recreate rollback tablespace, recreate rollback segments and modify init.ora for new rollback segments.


RDBMS Tuning

2.1 Do I have enough shared pool ?

Try this query to see how much free space you have.

select * from v$sgastat where name = 'free memory';

Each MTS connection is about 1k taken out of the shared pool.

System Level Admin

3.1 Somehow the unix file was destroyed, there was nothing in that tablspace, so its no big deal, but when I try to drop the tablespace I only get a File Not Found?

Make a file at the exact pathname of the old one by copying any old oracle tablespace, the smaller the better. You may use dd or cp. Just make sure there is a file there that Oracle can open (you may have to set permissions too. Then the "drop tablespace dead_table;" should work just fine. Then go ahead and do the unix or volume manager commands to clean up the rest of the way.

4.1 How do I speed up the loader ?

Direct loader is the fastest option if it can be used at all.

  • Pre-sort the data and use the 'sorted' option
  • Run in NOARCHIVELOG mode
  • Set the 'rows' parameter to sqlload to something very large
  • Tune for the large volume of redo log, e.g. Large redo logs Large log buffer size
  • Backup after completion.

If you can use direct load whenever there are lots of rows then they will be skip a lot of extra overhead. Direct load means Oracle formatts them directly into Oracle blocks, bypassing all SQL calls: if you are running in NOARCHIVELOG, (or in 7.1 if you are running in ARCHIVELOG but specify the UNRECOVERABLE option) then there will be no redo log (and no archived redo log) caused by the TABLE load.

When the 3,000,000 row are in the table, Oracle will do a sort operation ONLY against those 3,000,000 key values into a temporary segment. It will then merge the sorted 3,000,000 rows with the original index to create a new index. This will probably be a lot quicker than reading the 7 Gb (estimate) of table and sorting the 1.2 Gb (estimate) of raw information that has to go into the index.

Special Notes:
Irrespective of whether the new index is created by the merge, or by re-creation, it will be protected in the redo log, so there is a 2+ Gb redo that will be generated by this process, (ditto archived redo if you run in ARCHIVELOG mode).

If there are any constraints (other than non-null, or primary/unique key) then you cannot use the direct load.

If there are any triggers, then it will probably not be sensible to do direct load, as you will have to work around the fact that the direct loaded rows have not fired their triggers.

Watch out that your Operating System sort collates in the same order as the Oracle sort (which it does not on my HP, the '_' comes out in a different place.)

If you try direct load and your index in supposed to be unique, but your load produces a duplicate value, you are in trouble: possibly an O/S restore would be the optimum fix.

Space: because the old index will be merged with the temporary sort segment to produce a new index, your INDEX tablespace will have to have enough free space available in it to hold the final index. (The merge is NOT done in situ, a whole new index segment is created). This is not as bad as it seems: after all if you recreated the index your temporary tablespace would nominally have to be twice the size of the final index to allow for worst case sorting.

Since the data in the table has NOT been protected by redo, then you should do a backup of the database a.s.a.p.

<-- End of SQL*LOADER section ------------ >

5.1 How do I Set up to run SQL*NET V1 ?

Use tcpctl to control it. Usage: tcpctl [port ] [ start [opts] | stat | version | stop | log | debug | timeout ] start [orasrv options] : start orasrv stat : status of orasrv version : version number of orasrv stop : stop orasrv log : toggle logging mode debug : toggle debugging mode timeout : set or view handshake timeout When using tcpctl to start orasrv, you can specify the following additional [orasrv options]: [mapfile] [I|O] [logon|logoff] [debugon|debugoff|debug=] [dbaon|dbaoff] [opson|opsoff] [opsrooton|opsrootoff] [-O] [port=] [timeout=] [forkon|forkoff] [detachon|detachoff] ====== TWO_TASK environment variable syntax for pipe driver: P:[sid,buffer_size,break_mode if you use the sid here, you have to have a valid entry in /etc/oratab for that sid, no symlink, or maybe it just has to have ORACLE_HOME exactly as it was when the database was created.

5.2 How do I Set up to run SQL*NET V2 ?

Let's consider the general case of separate client and server. It should be easy to collapse this into the case of both on one machine. (Which is sometimes useful for running MTS to save memory.) It doesn't make much difference whether you use MTS or dedicated, but you will have to set this in the config files.

First off, you need to have tnslsnr set to own the port of choice. This may be in /etc/services or NIS, your choice. Here in Beaverton we have one already set in NIS on port 1521:

tnslsnr 1521/tcp # Oracle sqlnet v2

If you made a change to one of these, you have to send a "HUP" signal to the inetd to make it re-read the config.

You need a minimum of two files to set all this up. The client will look up the server in the file tnsnames.ora in the directory $ORACLE_HOME/network/admin or TNS_ADMIN if this is set. Sometimes TNS_ADMIN is set to /etc.

tnsnames.ora .ora listener.ora [ OPTIONAL sqlnet.ora oratab ] On the host.... Set TNS_ADMIN to the directory wherever the config files were saved to or copy them into the default: $ORACLE_HOME/network/admin

On the clients.... copy tnsnames.ora into the default: $ORACLE_HOME/network/admin or copy them into the place where TNS_ADMIN points.

The listener.ora is a file that will specify which instances on the server will talk via sqlnet. In there, an attribute "LISTENER" is defined, that includes the ORACLE_SID and home for each instance...

LISTENER = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = crsb.world) (PROTOCOL = TCP) (Host = orca) (Port = 1521) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = dss) (ORACLE_HOME = /ptmp/oracle/7.1.3/oracle) ) (SID_DESC = (SID_NAME = demo) (ORACLE_HOME = /opt/oracle) ) )

Identifying the Servers: TNSNAMES.ORA

The connect string is what is described in the tnsnames.ora file e.g. mudsharkdemo. This will be what you use to specify the server when you you a sqlplus like this: sqlplus scott/tiger@mudsharkdemo with the following tnsnames.ora mudsharkdemo = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(CONNECT_DATA=(SID=demo))) or sqlplus scott/tiger@oracle_server_service_name oracle_server_service_name = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = crsb.world) (PROTOCOL = TCP) (Host = orca) (Port = 1521) ) ) (CONNECT_DATA = (SID = demo) ) ) or with a dedicated server: BAANd= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=monk) (PORT=1525) ) (CONNECT_DATA= (SID=BAAN) (SERVER=DEDICATED) ) ) And this one was run with a MTS BAAN= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=monk) (PORT=1525) ) (CONNECT_DATA= (SID=BAAN) ) )


Start the listener: % lsnrctl start

Check the listener: % lsnrctl status Given this tnsnames.ora, use the connection: sqlplus scott/tiger@oracle_server_service_name

You may get some good information about connections by using the "services" option of lsnrctl. It gives the number of attempts and failures to connect. % lsnrctl services

5.3 How do I Set up oratab ?

use /etc/oratab or the default /var/opt/oracle/oratab It should look like this: # ORACLE_SID : ORACLE_HOME : Start: Y or N TOMM:/export/oracle:N JERY:/exprot/oracle:N test:/export/oracle:N

You can have a local copy of the tnsnames.ora file in your working directory which means you can name the alias' anything you want (at least on WinNT I can). e.g. scott/tiger@i scott/tiger@my_72_db scott/tiger@the_one_around_the_corner I used to also put v1 alias' in my tnsnames.ora file to make using v1 easier. e.g. my_72_db_v1 = t:hostname_or_ipaddr:ORCL

5.4 After a system crash, the SQL*NET won't come back up. Whats wrong?

There are named pipes save in /var/tmp remove them and try starting over.

PL/SQL

6.1 How do I debug PL/SQL ?

End your pl/sql code with: end; / show errors; and it will give error msgs with line nrs (do a set serveroutput on at the sqlplus prompt first, if that's not your default)

6.2 How do I get output to work ?

SET SERVEROUTPUT ON You then should begin seeing the output messages. Note: if you are sending a large amount of messages using dbms_output then you would have to increase the SIZE parameter on SET SERVEROUTPUT. See help SET SERVEROUTPUT for more info.

7.1 Here's method for fast case-insensitive search

I'm sure there must be a neat solution to this problem which an expert in Oracle can point out. In Sybase or its PC implementation, MS SQL Server, one can define all character strings to be stored case sensitive but searched case insensitive, as an option at installation. Most of our fields are character strings we wish to store/search in this fashion, and I could not conceive of operating our system without such automatic case handling capability.

There is a workaround invented in the Applications Division (of which I was a member); it's not quite as neat as one might like, but it works well enough for interactive use. Create a statement with 2^N ORed LIKE clauses for checking the first N characters of the string -- one case for each combination of upper or lower case for those characters -- and AND those clauses with an UPPER vs. UPPER check. Usually, for decent indexing the first character or two is enough (I believe we used two).

For example, suppose that one is looking up Fred as an ENAME in the EMP table (what else? :). The following statement works pretty well:

SELECT ename, empno FROM emp WHERE (ename LIKE HFR% ename LIKE HFr% ename LIKE HfR% ename LIKE Hfr% AND UPPER(ename) = HFRED

Some variations on this technique are of course possible. For instance, one would likely want to replace the quoted strings above with variables, and store the appropriate values in those variables during cursor open -- thus avoiding reparse etc. And one might want to use a LIKE HFRED% instead of the = HFRED the NLS features of the RDBMS, you may even want to expand those strings into expressions that do the appropriate calculations -- something like

SELECT ename, empno FROM emp WHERE (ename LIKE ( UPPER( SUBSTR(:ename,1,1) ) || UPPER( SUBSTR(:ename,2,1)) || H% ename LIKE ( UPPER( SUBSTR(:ename,1,1) ) || LOWER( SUBSTR(:ename,2,1)) || H% ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) || UPPER( SUBSTR(:ename,2,1)) || H% ename LIKE ( LOWER( SUBSTR(:ename,1,1) ) || LOWER( SUBSTR(:ename,2,1)) || H% AND UPPER(ename) LIKE UPPER(:ename) || H%

Since those operations require no I/O, they are pretty cheap, even if done by the RDBMS.

Oracle Application Object Library (OAOL) has used these techniques in implementing QuickPick (in fact, the invention led to the name!); and I have been told that SQL*Forms 4.0 has adopted this technique as an option -- you can check the 'case-insensitive query' attribute on fields to use it in query-by-example.

8.1 How do I get the prompt to include the SID ?

Put a file in SQLPATH named login.sql: host echo "set sqlp SQL@\c" > /tmp/setsqlp_rcl.sql host echo "`uname -n`-$ORACLE_SID> " >> /tmp/setsqlp_rcl.sql host echo "host rm /tmp/setsqlp_rcl.sql " >> /tmp/setsqlp_rcl.sql @/tmp/setsqlp_rcl

8.2 How do I get users to get in under their OS id/password ?

When creating the accounts, make the username OPS$username.

Like this:

create user ops$doofus identified externally; grant connect, resource to ops$doofus;

When UNIX user doofus types sqlplus, all he has to do is press RETURN twice, and he's in as Oracle user OPS$DOOFUS. When another user wants to connect as OPS$DOOFUS, they will need to know the password blahblahblah. Only user doofus can get into Oracle as OPS$DOOFUS without entering a password. Oracle lets the Operating System (OPS?) do the user authentication.

8.3 How do I show more info about init.ora parameters ?

REM Show more about the parameter settings connect sys/change_on_install column KSPPINM format a40 column KSPPivl format a10 set pagesize 40 select KSPPINM, KSPPITY, KSPPIVL, KSPPIDF from x$ksppi order by ksppinm; REM REM For Oracle 7.2.1 it will give you..... REM REM REM KSPPINM KSPPITY KSPPIVL KSPPIDF REM ---------------------------------------- ---------- ---------- --------- REM _allow_resetlogs_corruption 1 FALSE TRUE REM _controlfile_enqueue_timeout 3 900 TRUE REM _corrupt_blocks_on_stuck_recovery 3 0 TRUE REM _corrupted_rollback_segments 2 TRUE REM _db_block_cache_clone 1 FALSE TRUE REM _db_block_cache_protect 1 FALSE TRUE For 7.3.3 I get SQL> describe x$ksppi Name Null? Type ------------------------------- -------- ---- ADDR RAW(4) INDX NUMBER KSPPINM VARCHAR2(64) KSPPITY NUMBER KSPPDESC VARCHAR2(64) KSPPIFLG NUMBER So I use this query instead: REM Just describe the parameter select KSPPINM , KSPPITY, KSPPDESC from x$ksppi where KSPPINM = 'spin_count'; Note that you do not see the actual value of the parameter with these queries. To find that see the part about 'show parameter'

8.4 The installation worked perfectly alright but hardly any applications work.

The ones which do work are sqldba and sqlplus.

Had the same problem, but everythings all right now...

You need to create the following directory:

/usr/lib/X11/nls

It MUST be in that path exactly, and you CANNOT get away with putting the nls directory in your LD_LIBRARY_PATH.

PRO*C

9.1 How do you use the same variable to transfer data through the SQL <> C I/F ?

I always hated using the varchar2 datatype within a C program. It got ugly setting the length field all of the time and converting back and forth between C strings and varchar2's. There is a section somewhere in the Pro*C manual about datatype equivalencing. The code looks something like this:

char data[6]; exec sql var data is string(6); From then on, you can use the variable "data" in normal string operations and use it in SQL statements. Oracle knows the data is NULL terminated if you're using it for inserts/updates and it NULL terminates the data if you select into the variable. Warning: the size specified for data (inside the []) must be EXACTLY the same as the size after the word "string". That's why I like to put both statements on one line. If I change the size of data, I have a better chance of remembering to change the string size if it's on the same line. Also note that I used a 6 in my example for use with you varchar2 field that is 5 chars long.

Back To Top

E10000 | HPC | Customers | Sales Tools | Market Dev | SAE/Benchmarks | About DHPG | Search | Sitemap | Home
View DHPG.WEST
CALENDAR
  For dhpg contact information click here..
Questions, Comments or Content Suggestions?
Email webmeister@west
Copyright 1994-2000 Sun Microsystems, Inc., 901 San Antonio Road, Palo Alto, CA 94303 USA.
All rights reserved.