Index Search Add FAQ Ask Question |
---|
$Date: 21-May-2000 $
$Revision: 2.10 $
$Author: Frank Naudé $
|
database_sid:oracle_home_dir:Y|N
where database_sid is the system id (sid) of one of your oracle instances on this server. oracle_home_dir is the ORACLE_HOME directory associated with this instance. The Y|N flags indicate if the instance should automatically start at boot time (Y) or not (N).
You might think that if you are no longer running SQL*Net V1, you do not need oratab. However, Oracle's dbstart and dbshut scripts still use this file to figure out (using the third field, YorN) which instances are to be start up or shut down. And some other people (including me) have written scripts that cycle through multiple instances using oratab. But if you don't use SQL*Net V1, and don't use these scripts, you don't really need this file anymore.
The following example shows how to use oraenv (noninteractively) from a script:
export ORACLE_SID=ORCL;
export ORAENV_ASK=NO
. oraenv
/etc/oratab
file ends in a capital Y. Eg:
# $ORACLE_SID:$ORACLE_HOME:[N|Y]
#
ORCL:/u01/app/oracle/product/8.0.5:Y
#
The scripts for starting and stopping databases are:
$ORACLE_HOME/bin/dbstart and dbshut. SQL*Net (or Net8) is started with the
lsnrctl command. Add the following entries to your
/etc/rc2.d/S99dbstart
(or equivalent) file:
su - oracle -c "/path/to/$ORACLE_HOME/bin/dbstart" # Start DB's
su - oracle -c "/path/to/$ORACLE_HOME/bin/lsnrctl start" # Start listener
su - oracle -c "/path/tp/$ORACLE_HOME/bin/namesctl start" # Start OraNames (optional)
cd $ORACLE_HOME/orainst ./inspdverNOTE: From oracle 8i the "inspdver" command is not available anymore. If you find an alternative, please let us know.
exp userid/password file=/dev/rmt0 table=emp volsize=1.2G
Note: The volume size is a number ending with a m, k, or b (M, K, or B). The default is bytes.
You can also use ARCHMON which communicates with ARCH. When the end of tape is reached, ARCH stops and signals ARCHMON to prompt the user for a new tape. When ARCHMON knows the tape is mounted, it tells ARCH to resume the archiving operation. It needs regular operator monitoring.
$ archmon @P:TEST
# Make a pipe
mknod expdat.dmp p # or mkfifo pipe
# Start compress sucking on the pipe in background
compress < expdat.dmp > expdat.dmp.Z &
# Wait a second or so before kicking off the export
sleep 5
# Start the export
exp scott/tiger file=expdat.dmp
Or export accross the network directly into the target database:
Host A:
mknod FIFO.dmp p
exp u/p FILE=FIFO.dmp rest_of_parameters...
Host B:
mknod FIFO2.dmp p
rsh host1 dd if=FIFO.dmp > FIFO2.dmp &
imp u/p FILE=FIFO2.dmp rest_of_parameters...
Note:
It is important that you verify that the named pipe is ready on each side before you start the process.
cd /tmp/data
rm exp.dmp
mknod exp.dmp p # mkfifo on certain Unix flavours
split -b2047m
exp scott/tiger file=/tmp/data/exp.dmp record=n tables=tableX
cd /tmp/data
rm exp.dmp
mknod exp.dmp p
cat xaa xab xac xad >/tmp/data/exp.dmp &
imp scott/tiger file=/tmp/data/exp.dmp commit=y tables=tableX
Although running in single-task is faster, it requires more memory since the Oracle executable's text is no longer shared between the front-end and background processes. Thus, if you need to transfer large amounts of data between databases, relink the executale for greater efficiency.
cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk singletask # make -f ins_rdbms.mk expst # make -f ins_rdbms.mk impst # make -f ins_rdbms.mk sqlldrst # mv expst $ORACLE_HOME/bin/ # mv impst $ORACLE_HOME/bin/ # mv sqlldrst $ORACLE_HOME/bin/Now use expst and impst instead of imp or exp.
#!/bin/sh
VALUE=`sqlplus -silent "user/password@instance" <<END
set pagesize 0 feedback off verify off heading off echo off
select max(c1) from t1;
exit;
END`
if [ -z "$VALUE" ]; then
echo "No rows returned from database"
exit 0
else
echo $VALUE
fi
#!/bin/ksh
sqlplus -s >junk1 "usr/psw@instance" <<EOF
column num_rows new_value num_rows format 9999
select count(*) num_rows
from table_name;
exit num_rows
EOF
echo "Number of rows are: $?"
sqlplus -s usr/psw@instance |& # Open a pipe to SQL*Plus
print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- "set sqlprompt ''"
print -p -- "select sysdate from dual;"
read -p SYSDATE
print -p -- "select user from dual;"
read -p USER
print -p -- "select global_name from global_name;"
read -p GLOBAL_NAME
print -p -- exit
echo SYSDATE: $SYSDATE
echo USER: $USER
echo GLOBAL_NAME: $GLOBAL_NAME
:.,.!sqlplus -s /... automagically output from your statement arrives in the vi buffer for cutting and pasting. You might even like to consider piping the output through grep, awk, sed or perl.
$ find . -depth -print | cpio -ocBv >/dev/tapedevice $ dd if=/dev/raw_device of=/dev/tape_device BS=8k $ pax -w -f archive.pax *
Problems can arise when the reverse is true ie. the database is down but the sgadef file exists, as happens when the system crashes. In this case, the memory segment addressed in the file doesn't exist (try ipcs -m to convince yourself), which results in an error.
The 'what' command delves into a file and extracts SCCS version control information from that file, if any exists. For more details, look up 'man what'. Look at this example:
$ cd $ORACLE_HOME/bin $ owhat oracle Oracle patches in /app/oracle/product/7.3.4/bin/oracle: sf.c VLFS patch 08/1/96 sksa.c VLFS patch 08/1/96 sp.c VLFS patch 08/1/96 ssf.c VLFS patch 08/1/96
truss -rall -wall -p <PID> truss -p $ lsnrctl dbsnmp_startNOTE: The "truss" command works on SUN and Sequent. Use "strace" on Linux. If your operating system doesn't support the truss and strace commands, call your system administrator to find the equivalent command on your system.
Monitor your Unix system:
Unix message files record all system problems like disk errors, swap errors, NFS problems, etc. Monitor the following files on your system to detect system problems:
tail -f /var/adm/SYSLOG tail -f /var/adm/messages tail -f /var/log/syslog
#!/bin/ksh ALL_DATABASES=`cat /etc/oratab|grep -v "^#"|grep -v "N$"|cut -f1 -d: -s` for DB in $ALL_DATABASES do unset TWO_TASK export ORACLE_SID=$DB export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s` export PATH=$ORACLE_HOME/bin:$PATH echo "---> Database $ORACLE_SID, using home $ORACLE_HOME" sqlplus -s system/${DB}password @<<-EOF select * from global_name; exit; EOF doneNOTE: The terminating 'EOF' should normally be in column 1. The '-' before the first EOF above causes the shell to strip all leading TAB characters (not spaces, only TABs) from that point until (and including the EOF). This lets you indent the code in between using TABs.
sqlplus /NOLOG @conn.sql # No messing with ps, no password leak, connect from witin conn.sqlor...
echo "Name : \c" read Name echo "Password : \c" stty -echo read Password stty echo echo ${Name}/${Password} | sqlplusor...
USER=scott PASSWORD=tiger { echo ${USER}/${PASSWORD}; cat ${your_script_name_here}; } | sqlplusPS: You can also create OPS$... or IDENTIFIED EXTERNALLY users in the database (to use OS authentication) and connect with a /
This is normal behaviour on Unix machines. If a process has a file open for use, the file will be removed, but the space it occupies will remain in place until all processes referencing it are closed.
Use the "fuser" command before deleting Oracle datafiles. If any processes are touching them, you will not get the space back when you rm the file. If SMON is locking your file, just wait a few minutes until it releases it.
cd $ORACLE_HOME/rdbms/lib make -f ins_rdbms.mk install
# use tar to copy files and directorys with permissions and ownership tar cf - $ORACLE_HOME | rshTo copy the Oracle software to a different directory on the same server:"cd $ORACLE_HOME; tar xf -"
cd /new/oracle/dir/ ( cd $ORACLE_HOME; tar cf - . ) | tar xvf -
NOTE: Remember to relink the Intelligent Agent on the new machine to prevent messages like "Encryption key supplied is not the one used to encrypt file":
cd /new/oracle/dir/ cd network/lib make -f ins_agent.mk install
$ ulimit -a # Display limits for your session under sh or ksh $ limit # Display limits for your session under csh or tcsh $ ulimit -c SIZE_IN_BLOCKS # Limit core size under sh or ksh $ limit coredumpsize SIZE_IN_KB # Limit core size under csh or tcshIf you see a core file lying around, just type "file core" to get some details about it. Example:
$ file core core:ELF-64 core file - PA-RISC 2.0 from 'sqlplus' - received SIGABRTRun the Unix process debugger for details. These details are normally requested by Oracle for in-depth analysis of your problem.
Solaris, HP-UX, etc: $ adb $c $q Sequent: $ debug -c core $ORACLE_HOME/bin/sqlplus debug> stack debug> quitNote: Some Unix operating systems use commands like sdb, xdb, dbx, etc. The same principles apply. Do a man on the command and just do it!
To check the value of these environment variables enter:
env | grep -e ORA -e TNS
LD_LIBRARY_PATH | Path to library files. Eg: /usr/lib:/usr/openwin/lib:$ORACLE_HOME/lib:/usr/games/lib | |
ORACLE_BASE | Full path name to the base directory for all versions of Oracle products. | |
ORACLE_HOME | Full path name to the version of ORACLE you are currently using. ORACLE_HOME is normally found beneath ORACLE_BASE in the directory tree. This variable is used to find executable programs and message files. | |
ORACLE_SID | ORACLE Server system identifier (SID) or instance name. The database name is used as ORACLE_SID. This is required for all ORACLE products and is set by the coraenv script. | |
ORACLE_PATH | Defines the path names to locate files. If ORACLE_PATH is set, the application searches the directories specified by $ORACLE_PATH first, then the current directory. If ORACLE_PATH is not set, then the application uses the current directory. This variable is used by SQL*Plus, Forms and Menu. | |
ORACLE_TERM | Defines a terminal definition. If not set, it defaults to the value of your TERM environment variable. Used by all character mode products. | |
ORAENV_ASK | This will not ask for ORACLE_SID, it will take it as it is specified. This variable is normally set to "NO" before oraenv is invoked from a script. | |
TNS_ADMIN | Defines the path name to the TNS (Transparent Network Substrate) files like TNSNAMES.ORA, SQLNET.ORA etc. | |
ORACLE_TRACLE | Trace Oracle's Unix shell scripts as they execute (using set -x). | |
TWO_TASK | The TWO_TASK environment variable specifies a SQL*Net connect string for
connecting to a remote machine in a client/server configuration.
SQL*Net will check the value of TWO_TASK and automatically add it to your
connect string. For example the following are equivalent:
|
cd | Change the current directory. When you log in, you are in your "home" directory | |
ls | List files in a directory (like the DOS DIR command) | |
pwd | Print working (current) directory | |
cp | Copy (duplicate) a file or directory | |
rm | Remove (delete) a file. Note that rm is permanent, there is no undelete. Be careful "rm -r *" is an easy way to get fired!!! | |
mv | Move or rename a file or directory. Works like cp except the old file is deleted | |
mkdir | Creates a new (sub) directory | |
rmdir | Remove a directory | |
chmod | Change file permissions or access attributes | |
man | UNIX manual pages | |
du | Disk usage | |
script out.log | Write everything printed on your terminal to file out.log. Hit CNTL-D to stop. |
Some more advanced commands for the guru's:
ps -ef | grep inherit | List all running Oracle Listeners | |
ps -ef | grep smon | List all running Oracle Databases | |
ipcs | grep oracle | Semaphores and shared memory segments belonging to oracle Remove with ipcrm, but be carefull!!! | |
ls -l . | awk '{total += $5}; END {print total}' | Prints the total size (in bytes) of all files in the current directory | |
ls -lR . | awk '{total += $5}; END {print total/1024/1024 " Meg"}' | Prints the total size of files in the current directory and subdirectories below | |
ls -lR | awk '{print $5 "\t" $9}' | sort -n | tail | Find the largest files in dir and subdirecotry. |
System Admin commands:
Function: | Sun: | Sequent: | HP-UX: | AIX: | SCO: |
---|---|---|---|---|---|
System Config: | /usr/sbin/psrinfo -v | showcfg -s | ? | ? | ? |
Admin menu: | admintool | menu | ? | smit | sysadmsh |
Mount CD-Rom: | ? | ? | ? | ? | ? |
General | Reference | FAQ | Unix Gurus |
SUN | Vendor | FAQ | |
HP | Vendor | FAQ | |
AIX | Vendor | FAQ | |
Sequent | Vendor | ||
Digital | Vendor | FAQ | |
SCO | Vendor | FAQ | |
NCR | Vendor | ||
SGI | Vendor | FAQ |
General: |
Home |
Index |
Preamble |
Glossary |
OraCorp |
Papers |
Fun |
News |
Events |
Y2000 |
Books |
Links |
Forums
Products: |
SQL |
Plus |
Loader |
PL/SQL |
PreComp |
OPO |
OMO |
OO4OLE |
DBA |
PQO |
PSO |
OCO |
Net |
ODBC |
WebServer |
Des2k |
Dev2k
| Systems: |
MVS |
Unix |
Windows |
WindowsNT |
NetWare |
VMS
| |