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
