Index   Search   Add FAQ   Ask Question  

Oracle for UNIX FAQ

$Date: 21-May-2000 $
$Revision: 2.10 $
$Author: Frank Naudé $

In an open world without fences, why does one need Gates.

Topics

  • What is ORATAB and what is it good for?
  • How does one prepare the Oracle environment?
  • How do I get Oracle to automatically start when my server boots up?
  • How does one list all installed products?
  • Can I export directly to a tape drive?
  • Can I archive directly to tape?
  • I don't have enough space to export my database! Any suggestions?
  • How can I overcome the Unix 2 Gig file limit?
  • How can I export/import my database faster?
  • How can I SELECT a value from a table into a Unix variable?
  • How can I SELECT information into my vi-file?
  • How can I automate my on-line backup's?
  • What is the purpose of the sgadef.dbf file?
  • Can I see what patches are applied to Oracle?
  • How does one monitor and trace Unix processes?
  • How does one write a script to do work on all DB's?
  • Can one prevent passwords from appearing in the process table?
  • Why doesn't Oracle release file space when you drop a tablespace?
  • How does one relink the Oracle Server executables?
  • What should I do with those core files?
  • Can one copy Oracle software from machine to machine?
  • What Environment Variables needs to be set?
  • What Unix commands can be used when administrating my database?
  • Where can I get more info about Unix and Unix vendors?

  • Back to Oracle FAQ Index

    What is ORATAB and what is it good for?

    The oratab file is normally located in /etc or /var/opt/oracle. Origianally it was used for SQL*Net V1. It contains comments in unix-style leading pound signs (#), and data lines consisting of entries in this format:
            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.

  • Back to top of file

  • How does one prepare the Oracle environment?

    An oraenv script is provided with every Oracle installation. Run it as part of your current environment to set the necessary environment variables like ORACLE_HOME, ORACLE_SID, etc. Never set the Oracle Home directory explicitely in a script.

    The following example shows how to use oraenv (noninteractively) from a script:

    NOTE: the ". " in front of "oraenv" is needed to run the script as part of the current shell.

  • Back to top of file

  • How do I get Oracle to automatically start when my server boots up?

    Make sure the entry for your database SID in the /etc/oratab file ends in a capital Y. Eg:

    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:

  • Back to top of file

  • How does one list all installed products?

    One can display all installed products with version information by executing the following commands:
            cd $ORACLE_HOME/orainst
            ./inspdver
    
    NOTE: From oracle 8i the "inspdver" command is not available anymore. If you find an alternative, please let us know.

  • Back to top of file

  • Can I export directly to a tape drive?

    Yes, just use the tape device instead of the file name. Eg:

    Note: The volume size is a number ending with a m, k, or b (M, K, or B). The default is bytes.

  • Back to top of file

  • Can I archive directly to tape?

    Yes, set log_archive_dest = /dev/rmt0:100M in your init.ora file.

    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
    
  • Back to top of file

  • I don't have enough space to export my database! Any suggestions?

    Well, if you can't afford to buy extra disk space you can run export and compress simultaneously. This will prevent the need to get enough space for both the export file AND the compressed export file. Eg:

    Or export accross the network directly into the target database:

    Note:

    It is important that you verify that the named pipe is ready on each side before you start the process.

  • Back to top of file

  • How can I overcome the Unix 2 Gig file limit?

    This example uses the Unix split command to create multiple files, each smaller than the Unix (and imp/exp) 2 Gigabyte file size limit. This method would typically be used for import, export and SQL*Loader operations.

  • Back to top of file

  • How can I export/import my database faster?

    The IMP/EXP programs run in two task mode to protect the SGA from potential corruption by user programs. If you relink these programs in single task mode you can gain much improvement in speed (up to 30%). Although Oracle won't support this they supposedly use this method themselves.

    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.

  • Back to top of file

  • How can I SELECT a value from a table into a Unix variable?

    You can select a value from a database column directly into a Unix shell variable. Look at the following shell script examples: Second example, using the SQL*Plus EXIT status code: Yet another example, only this time we will read multiple values from SQL*Plus into shell variables.
  • Back to top of file

  • How can I SELECT information into my vi-file?

    When using vi to edit SQL*Plus or Pro*C code, sometimes one need to insert a table definition or data values into the file. You can simply open a new line, put 'desc EMP' (or a SELECT statement) into it and type:
            :.,.!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.

  • Back to top of file

  • How can I automate my on-line backup's?

    Write a CRON job to:

    1. Get the tablespace names from Oracle:
        SELECT TABLESPACE_NAME FROM SYS.DBA_TABLESPACES;

    2. Put the tablespaces in backup mode:
        ALTER TABLESPACE ... BEGIN BACKUP;

    3. Get the database file names from Oracle:
        SELECT NAME FROM SYS.V_$DATAFILE;
        SELECT NAME FROM SYS.V_$CONTROLFILE;
        SELECT MEMBER FROM SYS.V_$LOGFILE;

    4. Backup the files using pax, tar, ufsdump, dd, cpio or whatever file copy command you fancy. Note that if your database is on a raw partition, you can only use dd. Some examples:
              $ find . -depth -print | cpio -ocBv >/dev/tapedevice
              $ dd if=/dev/raw_device of=/dev/tape_device BS=8k
              $ pax -w -f archive.pax *
    5. End backup mode for all the tablespaces.
        ALTER TABLESPACE ... END BACKUP;

  • Back to top of file

  • What is the purpose of the sgadef.dbf file?

    The sgadef.dbf file can be found in the ${ORACLE_HOME}/dbs directory. Whatever you do, DO NOT REMOVE IT!!! It tells the oracle shadow process the address of the SGA when the process first connects to the database. If the process cannot find this file, it assumes the database is down.

    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.

  • Back to top of file

  • Can I see what patches are applied to Oracle?

    With the Unix "what" command one can see what patches are applied to an executable. The Oracle implementation of what is called owhat.

    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
    
  • Back to top of file

  • How does one monitor and trace Unix processes?

    To trace what a Unix process is doing enter:
            truss -rall -wall -p <PID>
            truss -p $ lsnrctl dbsnmp_start
    
    NOTE: 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
    
  • Back to top of file

  • How does one write a script to do work on all DB's?

    All databases on your machine should be listed in the /etc/oratab file. You can use this information to do processing on all databases on your machine. Look at this example:
            #!/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
            done
    
    NOTE: 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.

  • Back to top of file

  • Can one prevent passwords from appearing in the process table?

    One can prevent Oracle passwords from showing in the Unix process table (ps -ef) when running an Oracle utility (like sqlplus). Look at the following solutions:
            sqlplus /NOLOG @conn.sql
            # No messing with ps, no password leak, connect from witin conn.sql
    
    or...
            echo "Name : \c"
            read Name
            echo "Password : \c"
            stty -echo
            read Password
            stty echo
            echo ${Name}/${Password} | sqlplus
    
    or...
            USER=scott
            PASSWORD=tiger
            { echo ${USER}/${PASSWORD}; cat ${your_script_name_here}; } | sqlplus
    
    PS: You can also create OPS$... or IDENTIFIED EXTERNALLY users in the database (to use OS authentication) and connect with a /

  • Back to top of file

  • Why doesn't Oracle release file space when you drop a tablespace?

    Sometimes when you drop a tablespace within Oracle, then remove (rm) the underlying data files, Oracle will not release filesystem space until you shut it down. This can be confirmed with the "df -k" command.

    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.

  • Back to top of file

  • How does one relink the Oracle Server executables?

    Enter the following Unix commands:
            cd $ORACLE_HOME/rdbms/lib
            make -f ins_rdbms.mk install
    
  • Back to top of file

  • Can one copy the Oracle software from machine to machine?

    Yes, you can copy or FTP the Oracle Software between similar machines. Look at the following example:
            # use tar to copy files and directorys with permissions and ownership
            tar cf - $ORACLE_HOME | rsh  "cd $ORACLE_HOME; tar xf -"
    
    To copy the Oracle software to a different directory on the same server:
            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
    
    

  • Back to top of file

  • What should I do with those core files?

    Make sure the complete CORE file was written out. System administrators usually limit core size to avoid dangerous core files filling file systems. Core file size can be limited with the "ulimit" or "limit" commands. Look at these examples:
            $ 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 tcsh
    
    If 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 SIGABRT
    
    Run 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> quit
    
    Note: 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!

  • Back to top of file

  • What Environment Variables needs to be set?

    Here are a few basic ORACLE environment variables. For more environment variables, please refer to the reference guides.

    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:

      sqlplus scott/tiger@db1
    and

      export TWO_TASK=db1; sqlplus scott/tiger

  • Back to top of file

  • What Unix commands can be used when administrating my database?

    The Unix command set is so rich that I can never list them all, however, if you are a beginner, you may find the following commands useful. To get a detailed description of any command, just type man command-name.

    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 -vshowcfg -s? ? ?
    Admin menu: admintool menu ? smit sysadmsh
    Mount CD-Rom: ? ? ? ? ?

  • Back to top of file

  • Where can I get more info about Unix and Unix vendors?

  • Back to top of file

  • 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