About Oracle Database Environment Variables(unix)

To operate Oracle Database correctly, configuring environment variables is essential. This article covers everything from the basics to troubleshooting, including the latest best practice for the PATH variable (adding $ORACLE_HOME/OPatch).

Aiming to resolve all questions for beginners to intermediate readers in a single article.

What are environment variables?
Configuration examples (latest recommended settings)
1. Why add $ORACLE_HOME/OPatch to PATH
Detailed explanation of environment variables
Actual configuration procedures
1. Temporary settings
2. Persistent settings
Troubleshooting
Summary

What are environment variables?

Environment variables are configuration information that the OS provides to applications. In Oracle Database, they are used for:

Identifying database instances and software
Configuring the behavior of commands and tools
Specifying display targets for GUI applications
Setting the character set between client and database

Proper environment variable settings improve operational efficiency and prevent issues before they occur.

Configuration examples (latest recommended settings)

Below is a configuration example that incorporates the latest best practices.

export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export DISPLAY=192.168.56.1:0.0

Why add $ORACLE_HOME/OPatch to PATH

Purpose: To easily run the patching tool (opatch command).
Point: If $ORACLE_HOME/OPatch is not added, you must specify the full path every time you use opatch, which is inefficient.

Detailed explanation of environment variables

1. ORACLE_SID

Role: Specifies the database instance name to connect to.
Example: export ORACLE_SID=ORCL
Note: If multiple instances exist, set the appropriate instance name.

2. ORACLE_BASE

Role: Specifies the base directory for Oracle products.
Example: export ORACLE_BASE=/u01/app/oracle
Merit: Log files and data files are consistently placed under this directory.

3. ORACLE_HOME

Role: Specifies the directory where Oracle software is installed.
Example: export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
Importance: This is the central directory for executables and configuration files. Most tools and commands operate relative to it.

4. PATH

Role: Specifies searchable paths for commands and tools.
Example: export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
Change:
- In addition to $ORACLE_HOME/bin, add $ORACLE_HOME/OPatch.
- Enables efficient use of the opatch tool.
Point: The order in PATH matters. Place Oracle-related paths first so newer tools are prioritized.

5. NLS_LANG

Role: Specifies the locale and character set settings between the client and the database.
Example (US-oriented): export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
US-oriented points:
- On English-language systems in the US, a common setting is:
  - AMERICAN_AMERICA.AL32UTF8
- Unicode (AL32UTF8) is recommended for modern systems.
- To prevent mojibake or data inconsistencies, align the client-side character set with the database’s character set when necessary.
- When connecting from US-based terminals or applications that expect English messages and formatting, using AMERICAN_AMERICA.AL32UTF8 typically yields the expected behavior for prompts and client-side text processing while retaining Unicode support.

Note: Code blocks elsewhere in this article remain as-is per the non-translation rule; this subsection provides US-facing guidance specifically for NLS_LANG.

6. DISPLAY

Role: Specifies the output destination for GUI applications.
Example: export DISPLAY=192.168.56.1:0.0
Note: This setting is unnecessary in environments that do not use a GUI.

Actual configuration procedures

Temporary settings

Make settings effective only for the current session by entering commands directly in the terminal.

export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export DISPLAY=192.168.56.1:0.0

Persistent settings

Write them into ~/.bash_profile or ~/.bashrc so they are applied automatically at login.

vi ~/.bash_profile

Contents to write:

export ORACLE_SID=ORCL
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export DISPLAY=192.168.56.1:0.0

Apply the settings:

source ~/.bash_profile

Troubleshooting

If $ORACLE_HOME/OPatch is not added to PATH

Symptom: Error indicating the opatch command cannot be found.
Workaround:
1. Check whether $ORACLE_HOME/OPatch is included in PATH.
2. If not included, add the setting and try again.

If environment variables are not applied

Symptom: sqlplus or opatch does not work correctly.
Workaround:
1. Verify the settings: echo $PATH
2. Confirm the persistent configuration is written correctly and apply it with source ~/.bash_profile.

If mojibake (garbled characters) occurs

Symptom: Japanese does not display correctly in SQL*Plus.
Workaround:
1. Check NLS_LANG: echo $NLS_LANG
2. Modify to AMERICAN_AMERICA.AL32UTF8 as needed.

Summary

Setting Oracle Database environment variables is a fundamental operation and a key to efficient administration. The latest example—adding $ORACLE_HOME/OPatch to PATH—further streamlines operations. Use this article as a reference to configure environment variables properly and prevent issues in advance.

[Reference]
Oracle Database Database Installation Guide, 19c for Linux