{"id":104668,"date":"2025-01-02T22:52:46","date_gmt":"2025-01-02T22:52:46","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=104668"},"modified":"2025-01-03T19:36:08","modified_gmt":"2025-01-03T19:36:08","slug":"oracle-architecture-overview","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-architecture-overview\/","title":{"rendered":"Oracle Architecture Overview"},"content":{"rendered":"\n

I often fall back to the tried-and-true methods to understanding Oracle environments. Yes, sometimes the older ways are best, but understanding where Oracle has been and where it\u2019s going is essential to us building the best products to support this enterprise, robust RDBMS platform.<\/p>\n\n\n\n

I was disappointed by many of the documents out on the Oracle support site, etc., so this is an internal document to help understand how a seasoned Oracle DBA might navigate, learn and understand Oracle.<\/p>\n\n\n\n

In this document I will share an overview of Oracle\u2019s architecture that isn\u2019t complete, but it will give you a basic understanding of how the architecture works, especially if you are coming from other database platforms.<\/p>\n\n\n\n

<\/a>It\u2019s What You Know<\/h2>\n\n\n\n

Offer an Oracle Database Administrator a database host and say, \u201cHere, you figure out what databases are out there and the configuration. We don\u2019t know anything else about this server other than the Oracle password. You\u2019re on your own after that.\u201d There are some traditional methods most DBAs use, if left with just a terminal and a keyboard.<\/p>\n\n\n\n

Let\u2019s dive in on how it might likely go down\u2026<\/p>\n\n\n\n

<\/a>Single Instance Identification<\/h3>\n\n\n\n

Prerequisite:<\/strong> Log into the server via a terminal session\/putty\/emulator<\/p>\n\n\n\n

How do you find out about the environment?<\/p>\n\n\n\n

Windows we can view the task manager and Windows services to identify everything, but Linux is going to be the environment most in question and most common:<\/p>\n\n\n\n

What is the name of the host I\u2019m running on?<\/p>\n\n\n\n

hostname -a<\/pre>\n\n\n\n
What is the IP Address?<\/p>\n\n\n\n
ifconfig -a<\/pre>\n\n\n\n
View the \/etc\/hosts <\/strong>file, which will have the long name and short name of the Linux server, along with the IP address.<\/p>\n\n\n\n
What user are you logged in as?<\/p>\n\n\n\n
whoami<\/pre>\n\n\n\n
What databases are running on the host?<\/p>\n\n\n\n
ps -ef | grep pmon<\/pre>\n\n\n\n
This command, (along with most others on Linux\/Unix) can be run inside of a SQLPlus command line session by adding a \u201c!\u201d before the Linux command:<\/p>\n\n\n\n
!ps -ef | grep smon<\/pre>\n\n\n\n
We\u2019ll dig into this further on how we can capture more information inside the database, but if we have no access outside of the OS, this is a common way we can see what\u2019s going on.<\/p>\n\n\n\n
The SID is the last part of the process monitor (PMON) or System Monitor(SMON) process name:<\/p>\n\n\n\n
oracle   31   1 0 23:23 ?    00:00:00 db_pmon_ORADB1<\/pre>\n\n\n\n
View the oratab<\/a> file for entries. All databases residing on the host or have run on the host are listed. It\u2019s not that all the databases ARE on the machine or running, but they at least existed at one time. This is why we trust the \u201cps -ef\u201d command, as it tells us what is currently RUNNING on the host.<\/p>\n\n\n\n
view \/etc\/oratab<\/pre>\n\n\n\n
Inspect the entry(entries) past the commented-out sections of the file, (those that begin with a # sign):<\/p>\n\n\n\n
ORADB1:\/opt\/oracle\/product\/19.0.0\/dbhome1:N\n\nDWDB1:\/opt\/oracle\/product\/19.0.0\/dbhome2:Y<\/pre>\n\n\n\n
The entries also shows you the ORACLE_HOME<\/code> directory, which is the Oracle software installation that this database is using, including the bin files. The \u201cN\u201d at the end signals on reboot to NOT start the database as part of the autostart<\/code> script, where a \u201cY\u201d signals that it should be started.<\/p>\n\n\n\n
<\/a>It\u2019s Linux, Check Your Environment<\/h2>\n\n\n\n
You now know three things about the environment:<\/p>\n\n\n
\n
    \n
  1. The name of the database instance or instances.<\/li>\n\n\n\n
  2. The ORACLE_HOME<\/code> and from this, the ORACLE_BASE<\/code> (\/opt\/oracle).<\/li>\n\n\n\n
  3. If the database is running, (PMON<\/code> is up, database background processes can be viewed.)<\/li>\n<\/ol>\n<\/div>\n\n\n
    Now it\u2019s time to set up your environment. I\u2019m going to demonstrate the default way to do it and the manual way, too\u2026<\/p>\n\n\n\n
    <\/a>Environment Setup Using ORAENV Script<\/h3>\n\n\n\n
    . oraenv\n\nORACLE_SID = [DB listed in ~\/.bashrc] <enter SID here><\/pre>\n\n\n\n
    Script sets the ORACLE_BASE<\/code>, ORACLE_HOME<\/code> and LD_LIBRARY_PATH<\/code> if entry is in \/etc\/oratab<\/code> file.<\/p>\n\n\n\n
    <\/a>Manual Setup<\/h3>\n\n\n\n
    export ORACLE_SID=ORADB1\n\nexport ORACLE_HOME=\/opt\/oracle\/product\/19.0.0\/dbhome1\n\nexport LD_LIBRARY_PATH=$ORACLE_HOME\/lib:\/usr\/lib:\/usr\/dt\/lib:\/usr\/openwin\/lib:\/usr\/ccs\/lib<\/pre>\n\n\n\n
    Notice that I used the command \u201cexport<\/code>\u201d before each of the environment settings to ensure that I\u2019ve exported the variables for my session.<\/p>\n\n\n\n
    <\/a>Checking Environment Settings<\/h3>\n\n\n\n
    How to check if the environment is set up correctly:<\/p>\n\n\n\n
    Use the ECHO command:<\/p>\n\n\n\n
    echo $ORACLE_SID\n\necho $ORACLE_HOME<\/pre>\n\n\n\n
    These commands should return the values that were entered as part of the script or manual setup.<\/p>\n\n\n\n
    Now that you\u2019ve verified the environment and may have required this to log into SQL Plus or other tool, (vs. verifying environment variables set) you can now log in:<\/p>\n\n\n\n
    sqlplus \/ as sysdba\n\nsqlplus user@service<\/pre>\n\n\n\n
    The above two commands are using the sqlplus<\/a> utility, which is the most common utility any DBA uses. It comes with Oracle and has been around for several decades. The other two most popular ways to log into an Oracle database is SQLcl<\/a> which is new as Oracle 18c and is lighter weight if installed on a desktop. The other is Oracle SQL Developer<\/a>, which is a UI, but has significant additional support packages and some monitoring capabilities for developers and DBAs.<\/p>\n\n\n\n
    The first connection command is a bequeath connection. It uses the ORACLE_SID<\/code> environment variable to fulfill the database name information. It is logging in as SYSDBA<\/code> as the OS user, which must have OS level privileges, (owner<\/code>\/DBA<\/code>) to do so. As I\u2019m ORACLE<\/code> on this container, I\u2019m able to do just that. The WHOAMI<\/code> command earlier verified this for us as we were inspecting the environment.<\/p>\n\n\n\n
    The second login command is for a user and uses a service name. It will not be a bequeath connection but will use a TNS names\/EZ Connect via the Oracle listener and will require this set up on the database. If the listener isn\u2019t running, then this type of connection, which is the most common for Oracle, will fail.<\/p>\n\n\n\n
    <\/a>Main Architectures for Oracle<\/h2>\n\n\n\n
    Oracle has been around for over forty years. The first release was Oracle 2.0, which was released in the 1978. Claims of being the most widely used database begun during Oracle 7.34 days in the 1990\u2019s. Many databases you see today may have started out on this version and have been upgraded, patched and modernized throughout the decades. Due to this and the mission critical nature of these systems, the organizations and DBAs are quite risk averse and tend to not change anything unless it\u2019s absolutely necessary.<\/p>\n\n\n\n
    <\/a>Versioning<\/h2>\n\n\n\n
    Oracle database versions have changed from release numbers to release years, starting with Oracle 18c. Terminal releases are the final release of that iteration and the ones that will be supported long-term. They are the release version targeted by organizations.<\/p>\n\n\n\n
    Release numbers in italics are no longer supported by Oracle or require extended support.<\/p>\n\n\n
    \n