Tag Archives: scripts

SQL Toolbox for simplified Oracle Unified Audit Data Analysis

On my journey through the area of database security, Oracle Unified Audit has been a constant companion. I not only created audit concepts, but also often had the opportunity to implement them. Besides the configuration, the administration and evaluation of the audit data was always part of it. Occasionally I used scripts for this. However, I often evaluated the audit data ad hoc. There are only a handful of data dictionary views that have to be taken into account. So that was usually enough. Nevertheless, creating a collection of scripts for Unified Audit has been on my bucket list for a long time. At least until today. I finally found the time to put together a small toolbox of scripts, which I would like to show you in this blog post.

The scripts for the database audit are among others part of my GitHub repository oehrlis/oradba and are available to everyone. You are welcome to share them, use them, improve them or just like them.

What is currently covered by my scripts for Oracle Unified Audit?

Configuration of the audit infrastructure, i.e. tablespace, housekeeping jobs, etc.
Information on audit trails and storage usage
Administration of audit policies. This includes the creation, deletion, activation and display of policies.
Assessment of the unified audit trail showing various top events, e.g. policy, user, objects, etc.
Overview of the audit session and analysis of the statements per session

Current list of scripts

The following SQL scripts are currently available for the assessment of Oracle Unified Audit data. Further information on the scripts can be found in the comments of the file headers.

script	Purpose
saua_info.sql	Show information about the audit trails
daua_pol.sql	Disable all audit policies and drop all non-Oracle maintained policies
cdua_init.sql	Initialize Audit environment (create tablespace, reorganize tables, create jobs)
caua_pol.sql	Create custom local audit policies policies
iaua_pol.sql	Initialize / Enable custom local audit policies policies
saua_pol.sql	Show local audit policies policies. A join of the views AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES
saua_teact.sql	Show top unified audit events by action for current DBID
saua_tecli.sql	Show top unified audit events by client_program_name for current DBID
saua_tedbid.sql	Show top unified audit events by DBID
saua_teusr.sql	Show top unified audit events by dbusername for current DBID
saua_teobj.sql	Show top unified audit events by object_name for current DBID
saua_teobjusr.sql	Show top unified audit events by Object Name without Oracle maintained schemas for current DBID
saua_teown.sql	Show top unified audit events by object_schema for current DBID
saua_teosusr.sql	Show top unified audit events by os_username for current DBID
saua_tepol.sql	Show top unified audit events by unified_audit_policies for current DBID
saua_tepoldet.sql	Show top unified audit events by unified_audit_policies, dbusername, action for current DBID
saua_tehost.sql	Show top unified audit events by userhost for current DBID
saua_asdbv.sql	Show audit sessions for audit type Database Vault
saua_asdp.sql	Show audit sessions for audit type Datapump
saua_asfga.sql	Show audit sessions for audit type Fine Grained Audit
saua_asbck.sql	Show audit sessions for audit type RMAN
saua_asstd.sql	Show audit sessions for audit type Standard
saua_as.sql	Show audit sessions for audit any type
saua_asdet.sql	Show entries of a particular audit session with unified_audit_policies
saua_asdetsql.sql	Show entries of a particular audit session with SQL_TEXT
sdua_usage.sql	Show Unified Audit trail storage usage
saua_tabsize.sql	Show Unified Audit trail table and partition size
sdua_enpolstm.sql	Generate statements to enable all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES
sdua_crpolstm.sql	Generate statements to create all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES
sdua_drpolstm.sql	Generate statements to drop all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES
sdua_dipolstm.sql	Generate statements to disable all audit policies as currently set in AUDIT_UNIFIED_ENABLED_POLICIES
sdua_prgstm.sql	Generate Unified Audit trail storage purge statements
sdua_stostm.sql	Generate Unified Audit trail storage usage modification statements

List of SQL Scripts

A few Examples and Use Cases

Overview of Audit Trails saua_info.sql

Some information on the different audit trails and there size. Some data depend on up to date statistics.

Overview of Unified Audit Trail Storage Usage sdua_usage.sql

Summary of various information about the unified audit trail, e.g. number of records, oldest records, size etc. The output is always for the current DBID. If audit records are also available for other DBIDs, they are displayed as foreign DBIDs. These audit data can potentially be deleted. There is also the script saug_tabsize.sql, which displays information about the partitions.

Generate Unified Audit Trail purge statements sdua_prgstm.sql

Generate dbms_audit_mgmt statements based on the current setting / configuration. These statements can be used as a copy template directly or adapted to maintain the audit trail. There is also the script sdua_stostm.sql to create modification statements.

Show local audit policies policies saua_pol.sql

Show current audit policy settings. This script does join the views AUDIT_UNIFIED_POLICIES and AUDIT_UNIFIED_ENABLED_POLICIES. There are also corresponding script to create (caua_pol.sql), drop (daua_pol.sql) and enable/initialize (iaua_pol.sql) the audit policies.

Show Top Audit Events

There are several scripts to show top audit events e.g. by user (saua_teusr.sql), action (saua_teact.sql), policy (saua_tepol.sql), object name (saua_teobj.sql) and more. These script can be used to find the root cause of hig amout of audit data.

Below you see an example for top audit actions.

Audit Session Overview saua_as.sql

Show an overview of audit sessions in the audit trail, where the information is grouped by the session ID. This script also accepts parameters to limit the information based on days or fractions thereof. The following query limits the output to the last 2h. In addition, there are variants of this script that limit the information to the individual audit types, e.g. Datapump (saua_asdp.sql), RMAN (saua_asbck.sql) or Fine Grained Audit (saua_asfga.sql).

Audit Session Details saua_asdet.sql

This script does show all audit records for a specific audit session id. You can see what somebody did during its session. There is also a version of the script (saua_asdetsql.sql) which does show the sql_text. Below we see an example what session ID (scott) has done during its session. This script does also perfectly work for proxy sessions.

Naming Concept

A little confused by the script names? I have tried to somehow bring a bit of order to my scripts in the GitHub repository oehrli/oradba. That is an attempt. But I’m not sure I’ve been successful. Enclosed the information about the different abbreviations and prefixes.

The script names follow the format:

<script_qualifier><privileges_qualifier><topic_qualifier>_<use_case>.sql

Script Qualifier

The script qualifier is used to determine whether a script is used to read information or to configure, e.g. create, modify, activate, etc.

Qualifier	Stands For	Comment
s	Show	Output only on screen
d	Delete	Delete any objects, configuration etc
i	Initialize	Initializes or enable a configuration
c	Create	Create any objects, configuration etc.
u	Update	Update any object
g	Grant	Grants some objects or system privileges

Script Qualifier

Privileges Qualifier

The privilege qualifier is used to determine what privileges are required by a script.

Qualifier	Stands For	Comment
s	SYS	SYS, SYSDBA or Internal
d	DBA	SYSTEM or any other user with DBA role
o	Owner	Object owner
p	Create	Needs some special privileges according to the scripts inline comments
a	Audit	Audit roles like AUDIT_ADMIN or AUDIT_VIEWER

Privileges Qualifier

Topic Qualifier

Topic Qualifier is used to assign the different scripts to a certain topic and thus to be able to sort them better.

Qualifier	Stands For	Comment
ua	Unified Audit	Everything related to Oracle Unified Audit
ta	Traditional Audit	Everything related to Oracle traditional Audit
sec	Security	Oracle security related stuff
enc	Encryption	Oracle Transparent DataEncryption
a	Admin	Database Administration

Topic Qualifier

Conclusion

This collection of SQL scripts around Oracle Unified Audit is certainly not perfect or conclusive. Nevertheless, it is helpful for the configuration and a first analysis of the audit data in the Unified Audit Trail. As already mentioned, you can find the scripts on GitHub under oehrlis/oradba. I would be happy if you share or like them. Feedback and ideas as comments to this blogpost or better directly as a GitHub issue are very welcome.

OUDbase environment scripts for Oracle Unified Directory Part 1

Almost two years ago I started writing environment scripts for my Oracle Unified Directory installations. At the beginning there were only 2-3 scripts, from which at some point a small project on GitHub emerged. A lot has changed since my blog post Environment Scripts for OUD. The current version of OUDbase (v1.5.5) has a number of useful functions that make working with OUD on the command line much easier. This is one reason it is time to write about OUDbase once again. Or better to start a small blog series.

Features at a Glance

At the end of the day, it’s just a script that sets a series of aliases and environment variables. But this script does exactly what it should, it simplifies the work of the administrator. Initially it has been developed for Oracle Unified Directory (OUD), but to a certain degree other Oracle directory server and tools like Oracle Unified Directory Services Manager (OUDSM), Oracle Directory Server Enterprise Edition (ODSEE) and Oracle Internet Directory (OID) are supported as well.

Support of various Oracle directory servers and tools
Support for Oracle directory servers on Docker
Small foot print and minimal requirements
Simple and quick installation
Auto-configure for common environments and Oracle homes
Provide a kind of OFA environment for Oracle directory servers
Flexible environment handling eg. easy switching between different environments
Alias definitions
Platform-independent
More flexible and powerful than… wait, there is not oraenv for Oracle directory servers 🙂
Customization of environment variables and aliases globally or per instance
Miscellaneous templates for cron.d, logrotate.d, systemd service and instance creation

In particular OUDbase provides the following scripts:

oudtab as a central configuration file for instance names, ports and directory types
oudenv.sh script to source and set the environment
oud_backup.sh script to backup specific or all Oracle Unified Directory instances
oud_export.sh script to export specific or all Oracle Unified Directory instances
oud_status.sh script to check the status of an Oracle Unified Directory instance including replication status
oud12c_eus template and scripts to create an Oracle Unified Directory server with Enterprise User Security integration
oud12c_eus_ad_proxy template and scripts to create an Oracle Unified Directory proxy server with Enterprise User Security and MS Active Directory integration
generic template and scripts as base for customisation

Requirements

OUDbase is modest. You just need a bash shell to run it and tar/gzip to install it. This is also one of the reasons why it perfectly fits on OUD Docker images. Although you do not run several directory servers in one Docker container, it is convenient to work on the command line. A little further up I mentioned that OUDBase is platform-independent. At least one operating system, which does not support bash out of the box. Guess which one? Yes, exactly Microsoft Windows. Basically, OUDbase should also run on MS Windows if bash is installed there. However, this has not yet been tested.

Installation

Before you can start the installation of OUDbase, you have to download the latest version from the GitHub repository oehrlis/oudbase. OUDbase is available as TAR file or as shell installation script. The shell script itself is regular Bash script with additional payload. This means that the TAR file is appended directly at the end of the script. Since the embedded TAR is base64 encoded, the installation script can be sent by mail without any problems. If you are interested in how to do this I recommend the How-To Add a Binary Payload to your Shell Scripts written by Mitch Frazier / Linux Journal.

The script does relay on the directory structure optimal flexible architecture (OFA) introduced by Oracle a couple of years ago. Starting from an ORACLE_BASE path, the installation script evaluates the required parameters based on OFA. If you do use a different structure you can give the necessary directory path via parameters. The following code block does show the oudbase_install.sh usage.

oracle@oudad:/u00/app/oracle/ [oud_ad] ./oudbase_install.sh -h
Start of oudbase_install.sh (Version v1.5.5) with -h
processing commandline parameter
Usage, oudbase_install.sh [-hav] [-b ]
[-i ] [-B ]
[-m ] [-f ] [-j ]

-h Usage (this message)
-v enable verbose mode
-a append to profile eg. .bash_profile or .profile
-b ORACLE_BASE Directory. Mandatory argument. This
directory is use as OUD_BASE directory
-o OUD_BASE Directory. (default $ORACLE_BASE).
-d OUD_DATA Directory. (default /u01 if available otherwise $ORACLE_BASE).
This directory has to be specified to distinct persistant data from software
eg. in a docker containers
-A Base directory for OUD admin (default $OUD_DATA/admin)
-B Base directory for OUD backups (default $OUD_DATA/backup)
-i Base directory for OUD instances (default $OUD_DATA/instances)
-m Oracle home directory for OUD binaries (default $ORACLE_BASE/products)
-f Oracle Fusion Middleware home directory. (default $ORACLE_BASE/products)
-j JAVA_HOME directory. (default search for java in $ORACLE_BASE/products)

Logfile : /u01/log/oudbase_install.log

The following table does provide an overview of installation path, environment variables, parameters and there default values.

Parameter	ENV Variable	Default Value	Description
-v	n/a	n/a	Enable verbose mode
-a	n/a	n/a	Append to profile eg. .bash_profile or .profile
-b	$ORACLE_BASE	/u00/app/oracle	Mandatory argument. This directory is use as ORACLE_BASE from which all other directories are evaluated.
-b	$ORACLE_BASE	/u00/app/oracle	Mandatory argument. This directory is use as ORACLE_BASE from which all other directories are evaluated.
-o	$OUD_BASE	$ORACLE_BASE	OUDbase base directory where the scripts, config etc. will be installed. Usually this is the same directory as used for ORACLE_BASE. Due to some legacy requirement this can be separate directory.
-d	$OUD_DATA	/u01 or $ORACLE_BASE	Directory to store the persistant data eg. the OUD instance homes, backup and admin directories etc. It defaults /u01 if available otherwise $ORACLE_BASE. This directory has to be specified to distinct persistant data from software eg. in a docker containers.
-A	$OUD_ADMIN_BASE	$OUD_DATA/admin	Base directory for an instance specific admin directory, similar to the admin directory of Oracle databases.
-B	$OUD_BACKUP_BASE	$OUD_DATA/backup	Base directory for an instance specific directory to store backup’s and LDIF exports.
-i	$OUD_INSTANCE_BASE	$OUD_DATA/instances	Base directory for the OUD instance homes.
-m	$ORACLE_HOME	$ORACLE_BASE/products	Oracle home directory for binaries. The installation script does search below this path for the corresponding binaries.
-f	$ORACLE_FMW_HOME	$ORACLE_BASE/products	Oracle Fusion Middleware home directory when separating the OUD and OUDSM binaries. The installation script does search below this path for the corresponding binaries.
-j	$JAVA_HOME	$ORACLE_BASE/products	Location of the java home. The installation script does search below this path for the corresponding java binaries.

The installation script will guess the required parameter based on OFA. All parameter specified at the command line will be stored for future use in oudenv_core.conf. If something went wrong during installation, you always have the option of adjusting them manually.

Let’s create an installation as an example. We will use /u00/app/oracle as ORACLE_BASE, /u01 as OUD_DATA and /u00/app/oracle/product/fmw12.2.1.3.0 as ORACLE_HOME. Below you find the command and an excerpt of the output. Ok actually everything except the output of the TAR command.

oracle@oudad:/tmp/ [oud_ad] ./oudbase_install.sh -v -b /u00/app/oracle -d /u01 -m /u00/app/oracle/product/fmw12.2.1.3.0
2018-07-16_20:45:46 START: Start of oudbase_install.sh (Version v1.5.5) with -v -b /u00/app/oracle -d /u01 -m /u00/app/oracle/product/fmw12.2.1.3.0
2018-07-16_20:45:46 INFO : processing commandline parameter
2018-07-16_20:45:46 INFO : Define default values
2018-07-16_20:45:46 INFO : Using the following variable for installation
2018-07-16_20:45:46 INFO : ORACLE_BASE = /u00/app/oracle
2018-07-16_20:45:46 INFO : OUD_BASE = /u00/app/oracle/local/oudbase
2018-07-16_20:45:46 INFO : LOG_BASE = /u01/log
2018-07-16_20:45:46 INFO : ETC_CORE = /u00/app/oracle/local/oudbase/etc
2018-07-16_20:45:46 INFO : ETC_BASE = /u01/etc
2018-07-16_20:45:46 INFO : OUD_DATA = /u01
2018-07-16_20:45:46 INFO : OUD_INSTANCE_BASE = /u01/instances
2018-07-16_20:45:46 INFO : OUD_ADMIN_BASE = /u01/admin
2018-07-16_20:45:46 INFO : OUD_BACKUP_BASE = /u01/backup
2018-07-16_20:45:46 INFO : ORACLE_PRODUCT =
2018-07-16_20:45:46 INFO : ORACLE_HOME = /u00/app/oracle/product/fmw12.2.1.3.0
2018-07-16_20:45:46 INFO : ORACLE_FMW_HOME = /u00/app/oracle/product/fmw12.2.1.3.0
2018-07-16_20:45:46 INFO : JAVA_HOME = /usr/java/jdk1.8.0_172
2018-07-16_20:45:46 INFO : SCRIPT_FQN = /tmp/oudbase_install.sh
2018-07-16_20:45:46 INFO : Installing OUD Environment
2018-07-16_20:45:46 INFO : Create required directories in ORACLE_BASE=/u00/app/oracle
2018-07-16_20:45:46 INFO : Create Directory /u01/log
2018-07-16_20:45:46 INFO : Create Directory /u01/etc
2018-07-16_20:45:46 INFO : Create Directory /u00/app/oracle/local
2018-07-16_20:45:46 INFO : Create Directory /u01/admin
2018-07-16_20:45:46 INFO : Create Directory /u01/backup
2018-07-16_20:45:46 INFO : Create Directory /u01/instances
2018-07-16_20:45:46 INFO : Create Directory /u00/app/oracle/local/oudbase
2018-07-16_20:45:46 INFO : Backup existing config files
2018-07-16_20:45:47 INFO : Backup oudtab to oudtab.save
2018-07-16_20:45:47 INFO : Backup oud.<em>DEFAULT</em>.conf to oud.<em>DEFAULT</em>.conf.save
2018-07-16_20:45:47 INFO : Start processing the payload
2018-07-16_20:45:47 INFO : Payload is available as of line 470.
2018-07-16_20:45:47 INFO : Extracting payload into /u00/app/oracle/local
2018-07-16_20:45:47 INFO : Payload is set to base64. Using base64 decode before untar.
...
2018-07-16_20:45:47 INFO : Store customization in core config file /u00/app/oracle/local/oudbase/etc/oudenv_core.conf
2018-07-16_20:45:47 INFO : save customization for OUD_DATA (/u01)
2018-07-16_20:45:47 INFO : save customization for ORACLE_BASE (/u00/app/oracle)
2018-07-16_20:45:47 INFO : save customization for ORACLE_HOME (/u00/app/oracle/product/fmw12.2.1.3.0)
2018-07-16_20:45:47 INFO : Please manual adjust your .bash_profile to load / source your OUD Environment
2018-07-16_20:45:47 INFO : using the following code
#Check OUD_BASE and load if necessary
if [ "${OUD_BASE}" = "" ]; then
if [ -f "${HOME}/.OUD_BASE" ]; then
. "${HOME}/.OUD_BASE"
else
echo "ERROR: Could not load ${HOME}/.OUD_BASE"
fi
fi

#define an oudenv alias
alias oud='. ${OUD_BASE}/bin/oudenv.sh'

#source oud environment
. /u00/app/oracle/local/oudbase/bin/oudenv.sh
2018-07-16_20:45:47 INFO : update your .OUD_BASE file /home/oracle/.OUD_BASE
2018-07-16_20:45:47 END : of oudbase_install.sh

As you can see from the output above, you just have to source .OUD_BASE and ${OUD_BASE}/bin/oudenv.sh to start using OUDbase. The installation script either provides an example of what you need to add to your .bash_profile or adjusts it directly by specifying the parameter -a.

#Check OUD_BASE and load if necessary
if [ "${OUD_BASE}" = "" ]; then
if [ -f "${HOME}/.OUD_BASE" ]; then
. "${HOME}/.OUD_BASE"
else
echo "ERROR: Could not load ${HOME}/.OUD_BASE"
fi
fi

#define an oudenv alias
alias oud='. ${OUD_BASE}/bin/oudenv.sh'

#source oud environment
. /u00/app/oracle/local/oudbase/bin/oudenv.sh

The next time you login, you’ll see the status of you OUD instance. If you do not have an OUDTAB file, OUDbase will create one for you based on existing OUD instances, Oracle homes etc.

If you haven’t yet installed any Oracle software or created an OUD instance OUDbase can not guess your environment. Therefore you have to manually create an OUDTAB file.

WARN : oudtab (/u00/app/oracle/local/oudbase/etc/oudtab) does not exist or is empty. Create a new one.
WARN : No OUD Instance yet available or defined.

Conclusion

The first blog post of the serie on the OUDbase environment scripts should give you first impression. The installation is straight forward and simple. In the next blog post I’ll show how you can configure and customize OUDbase. Beside a couple of use cases, I’ll provide a deeper insight into environment variables, aliases and scripts. So stay tuned. If you can not wait get the latest version of OUDbase from GitHub and start using it. By the way, my Docker build scripts are configured to use OUDbase.

References

Below you find a few references related to the topics discussed in this post:

OUDbase on GitHub (oehrlis/oudbase)
First blog post on OUDbase Environment Scripts for OUD
Oracle documentation on optimal flexible architecture
Linux Journal Add binary payload your shell scripts
My genuine Docker build scripts for Oracle Unified Directory on GitHub (oehrlis/docker)
Oracle Docker build scripts for Oracle Unified Directory on GitHub (oracle/docker-images) yep from me too 🙂

Environment Scripts for OUD

At Trivadis we do have the TVD-BasEnv™ to standardizes and simplifies the handling of environments for Oracle database and application server landscapes. This inspired me to create something similar for Oracle Unified Directory environments. Although current versions of TVD-BasEnv™ already support OUD and OID environment. I’ve had the situation, where I need some small and slimmed down environment scripts for dedicated OUD test servers. TVD-BasEnv™ is rather complex and brings a lot of nice features for Oracle Database environments with ASM, RAC, DataGuard and more stuff which is in general not required on a simple OUD server.

My OUD Base is basically just the oudenv.sh script, some configuration files and a bunch of aliases. The directory structure for the OUD binaries, scripts and configuration files is similar to what we use in TVD-BasEnv™ and based on OFA. It is written in bash and tested on my Oracle Linux VM’s and Raspberry Pi’s with Raspbian Jessy. It should also run on any other bash environment. Um, well OUD and Raspberry Pi? Yes I’ll explain this soon in an other blog post.

Setup the Environment

In general I do use a dedicated OS user for my Oracle installations. To keep it simple and clear I name it oracle. The following commands are run on my Raspberry Pi and therefore as OS user pi. Please adjust it accordingly. Create the user and the corresponding OS groups as pi user with sudo.

pi@oud2go:~ $ sudo adduser oracle
Adding user oracle ...
Adding new group oracle (1001) ...
Adding new user oracle (1001) with group oracle ...
Creating home directory /home/oracle ...
Copying files from /etc/skel ...
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
Changing the user information for oracle
Enter the new value, or press ENTER for the default
	Full Name []: oracle
	Room Number []: 
	Work Phone []: 
	Home Phone []: 
	Other []: 
Is the information correct? [Y/n] y
pi@oud2go:~ $ sudo addgroup oinstall
Adding group oinstall (GID 1002) ...
Done.
pi@oud2go:~ $ sudo addgroup osdba
Adding group osdba (GID 1003) ...
Done.
pi@oud2go:~ $ sudo adduser oracle oinstall
Adding user oracle to group oinstall ...
Adding user oracle to group oinstall
Done.
pi@oud2go:~ $ sudo adduser oracle osdba
Adding user oracle to group osdba ...
Adding user oracle to group osdba
Done.

Create an ORACLE_BASE directory which is used for OUD and provide access to OS user oracle.

pi@pi2go:~ $ sudo mkdir -p /u00/app/oracle
pi@pi2go:~ $ sudo chown -R oracle:oinstall /u00/app/oracle

My OUD Base is available as Bash Install script with an embedded TAR ( oudbase_install.sh) or as plain TAR file ( oudbase_install.tgz). If you use the TAR file a few manuell configuration steps are required.

Install using oudbase_install.sh

This installation is straightforward as you can see in the usage.

2016-10-15_11:41:58  START: Start of oudbase_install.sh (Version 0.1) with 
2016-10-15_11:41:58  INFO : Usage, oudbase_install.sh [-hv] [-b <oracle_base>] 
2016-10-15_11:41:58  INFO :   [-i <oracle_instance_base>] [-m <oracle_home_base>] [-B <oud_backup_base>]
2016-10-15_11:41:58  INFO : 
2016-10-15_11:41:58  INFO :   -h                          Usage (this message)
2016-10-15_11:41:58  INFO :   -v                          enable verbose mode
2016-10-15_11:41:58  INFO :   -b <oracle_base>            ORACLE_BASE Directory. Mandatory argument.
2016-10-15_11:41:58  INFO :   -i <oracle_instance_base>   Base directory for OUD instances (default $ORACLE_BASE/instances)
2016-10-15_11:41:58  INFO :   -m <oracle_home_base>       Base directory for OUD binaries (default $ORACLE_BASE/middleware)
2016-10-15_11:41:58  INFO :   -B <oud_backup_base>        Base directory for OUD backups (default $ORACLE_BASE/backup)
2016-10-15_11:41:58  INFO : 
2016-10-15_11:41:58  INFO : Logfile : /u00/app/oracle/local/log/oudbase_install.log
2016-10-15_11:41:58  ERR  : Exit Code 1. Wrong amount of arguments. See usage for correct one.

We will just provide the ORACLE_BASE and use the default values for all other settings.

oracle@pi2go:~ $ ./oudbase_install.sh -v -b /u00/app/oracle
2016-10-15_11:44:03  START: Start of oudbase_install.sh (Version 0.1) with -v -b /u00/app/oracle
2016-10-15_11:44:03  INFO : processing commandline parameter
2016-10-15_11:44:03  Installing OUD Environment
2016-10-15_11:44:03  Create required directories in ORACLE_BASE=/u00/app/oracle
2016-10-15_11:44:03  Create Directory /u00/app/oracle/etc
2016-10-15_11:44:03  Create Directory /u00/app/oracle/local
2016-10-15_11:44:03  Create Directory /u00/app/oracle/backup
2016-10-15_11:44:03  Create Directory /u00/app/oracle/middleware
2016-10-15_11:44:03  Create Directory /u00/app/oracle/instances
2016-10-15_11:44:03  Extracting file into /u00/app/oracle/local
bin/
bin/oud_export.sh
bin/oud_backup.sh
bin/oudenv.sh
bin/oudbase_install.sh
bin/oud_status.sh
config/
certificates/
doc/
etc/
etc/oudtab
etc/oudenv.conf
etc/oud._DEFAULT_.conf
lib/
log/
log/oud_status.log
log/oud_export.log
log/oud_backup.log
log/oudbase_install.log
templates/
templates/cron.d/
templates/cron.d/oud
templates/.bash_profile
templates/ldif/
templates/ldif/oud_pi_init.ldif
templates/logrotate.d/
templates/logrotate.d/oud
2016-10-15_11:44:03  Please manual adjust your .profile to load / source your OUD Environment
2016-10-15_11:44:03  END  : of oudbase_install.sh

You have to change your bash profile to make sure that the environment is loaded. Just add the following lines.

oracle@pi2go:~ $ vi .profile
# Check OUD_BASE and load if necessary
if [ "${OUD_BASE}" = "" ]
  then
    if [ -f "${HOME}/.OUD_BASE" ]
      then
        . "${HOME}/.OUD_BASE"
      else
        echo "ERROR: Could not load ${HOME}/.OUD_BASE"
    fi
fi

# define an oudenv alias
alias oud='. ${OUD_BASE}/bin/oudenv.sh'

# source oud environment
. ${OUD_BASE}/bin/oudenv.sh

During the next logon you have the OUD Base available

Manual installation using oudbase_install.tgz

Ok, it is not really more complex just un-tar the file in a directory. Normally it is $ORACLE_BASE/local. Other directory probably have to be specified in the config file.

oracle@pi2go:~ $ cd /u00/app/oracle/
oracle@pi2go:~ $ mkdir local
oracle@pi2go:~ $ cd local
oracle@pi2go:~ $ tar zxvf oudbase_install.tgz

You also have to change your bash profile as mentioned above.

Examples

A few example how to use OUD Base to simplify OUD management.

Change environment to OUD instance oud_pi.

oracle@pi2go:~/ [oud_pi] oud_pi
Source environment for OUD Instance oud_pi
--------------------------------------------------------------
 Instance Name   : oud_pi
 Instance Home   : /u00/app/oracle/instances/oud_pi
 Oracle Home     : /u00/app/oracle/middleware/oud_11.1.2.3
 Instance Status : up
 LDAP Port       : 1389
 LDAPS Port      : 1636
 Admin Port      : 4444
 Replication Port: 8989
--------------------------------------------------------------

List available / running OUD instances using oudup or via alias u.

oracle@pi2go:~/ [oud_pi] oudup
TYPE INSTANCE   STATUS PORT HOME
---- ---------- ------ ---- ----------------------------------
OUD  oud_pi     up     4444 /u00/app/oracle/instances/oud_pi

Configuration and Architecture

Config Files

The OUD Base does have the following configuration files.

File	Description
.OUD_BASE	This is a simple file in the user home directory. It includes the pointer to the OUD Base directory. This file is used to initiate $OUD_BASE.
oudtab	oudtab is a simple file which includes all OUD instance and there ports eg. default LDAP port, admin port, SSL port and replication port.
oudenv.conf	This is the main configuration file for environment variables and aliases. It is loaded when an environment is set or changed. Location of oudenv.conf is $ETC_BASE.
oud._DEFAULT_.conf	This configuration file for custom environment variables. Location of oud._DEFAULT_.conf is $ETC_BASE.
oud._INSTANCE_.conf	This configuration file for custom environment variables for a dedicated OUD instance eg. oud_pi Location of oud._oud_pi_.conf is $ETC_BASE.

Directories and its variables

The following directory, environment variables and aliases are defined and used in OUD Base. Most of them are inspired by OFA (Oracle Flexible Architecture) and TVD-BasEnv™.

ENV Variable	Alias	Path	Description
$ORACLE_BASE, $cdob	cdob	/u00/app/oracle	Base directory for the oracle binaries
$OUD_BASE, $cdl	cdl	$ORACLE_BASE/local	OUD Base directory with the scripts, config etc
	cdl.bin	$ORACLE_BASE/bin	Scripts directory in OUD_BASE
$ETC_BASE, $etc	etc, cdl.etc	$ORACLE_BASE/etc	OUD Base configuration directory
$LOG_BASE, $log	log, cdl.log	$ORACLE_BASE/log	OUD Base log directory
		$ORACLE_BASE/doc	OUD Base documentation directory
		$ORACLE_BASE/config	Local directory for configuration files, LDIF etc to build an OUD instance
		$ORACLE_BASE/certificates	Local directory for certificates
$ORACLE_HOME, $cdh	cdh	$ORACLE_BASE/middleware/oud_11.1.2.3	Oracle Unified Directory binaries eg. 11.1.2.3
$JAVA_HOME		/usr/lib/jvm/jre-1.7.0-oracle-1.7.0.101-1jpp.1.el7.x86_64	Java used for OUD
$OUD_INSTANCE_BASE, $cdib	cdib	$ORACLE_BASE/instances	Base directory for the instance homes
	oud_pi		Alias to set environment for OUD instance oud_pi
$OUD_INSTANCE_HOME, $cdih	cdih	$ORACLE_BASE/instances/oud_pi	OUD Instance Home directory for Instance oud_pi
$cdic	cdic	$OUD_INSTANCE_HOME/OUD/config	Config directory for OUD instance oud_pi
$cdil	cdil	$OUD_INSTANCE_HOME/OUD/logs	Log directory for OUD instance oud_pi

Variables

Variable besides the ones mentioned above.

Variable	Description
$OUD_INSTANCE	Name of the current OUD instance
$OUD_INST_LIST	List of OUD instances taken from $OUDTAB
$PWD_FILE	Password file for the OUD instance eg. ${ETC_BASE}/$OUD_INSTANCE_pwd.txt or ${ETC_BASE}/pwd.txt
$PORT	OUD instance port taken from oudtab file
$PORT_ADMIN	OUD instance admin port taken from oudtab file
$PORT_REP	OUD instance replication port taken from oudtab file
$PORT_SSL	OUD instance SSL port taken from oudtab file
$OUDTAB	oudtab config file eg. ${ETC_BASE}/oudtab

Aliases

Alias	Description
dsc	dsconfig including hostname, $PORT_ADMIN and $PWD_FILE
dsrs	dsreplication status
oud_pi	OUD Base does generate an alias for each OUD instance based on its name. This allows to easily change the environment from one to an other OUD instance.
oud INSTANCE	Use oud INSTANCE name to change the environment to a particular OUD instance
taa	tea will do a tail -f on the OUD instance access log
tae	tea will do a tail -f on the OUD instance error log
tas	tea will do a tail -f on the OUD instance server.out log
tarep	tea will do a tail -f on the OUD instance replication log
task	task does run a manage-tasks with hostname, port etc parameter
u	u runs oudup to display the current OUD Instances
vio	vio opens the oudtab file eg. ${ETC_BASE}/oudtab

Conclusion

Although there is the possibility to use property files for OUD I’m still happy, that I have a bunch of aliases to set or change a few directories. Eg. jump to the log directory, view config files etc. Feel free to use the OUD Base as it is on your OUD environments at your own risk. It simplifies a few settings in particular if you have multiple OUD instance on one system. You may change, modify the scripts as you like. I can not guarantee, that the scripts do not have any errors or bugs. Please test before you start using them on a production environment.

Files and References

Below you find a few references related to Raspberry Pi, USB OTG or Oracle Unified Directory:

OUD base environment installation script. It’s a bash script including a TAR. oudbase_install.sh
OUD base environment as TAR archive without installation script. oudbase_install.tgz
The genuine Trivadis base environment scripts TVD-BasEnv™

OraDBA

Just another Site about Oracle, Database Security, Linux, Mac OS X and more

Tag Archives: scripts

SQL Toolbox for simplified Oracle Unified Audit Data Analysis

Current list of scripts

A few Examples and Use Cases

Overview of Audit Trails saua_info.sql

Overview of Unified Audit Trail Storage Usage sdua_usage.sql

Generate Unified Audit Trail purge statements sdua_prgstm.sql

Show local audit policies policies saua_pol.sql

Show Top Audit Events

Audit Session Overview saua_as.sql

Audit Session Details saua_asdet.sql

Naming Concept

Script Qualifier

Privileges Qualifier

Topic Qualifier

Conclusion

OUDbase environment scripts for Oracle Unified Directory Part 1

Features at a Glance

Requirements

Installation

Conclusion

References

Environment Scripts for OUD

Setup the Environment

Install using oudbase_install.sh

Manual installation using oudbase_install.tgz

Examples

Configuration and Architecture

Config Files

Directories and its variables

Variables

Aliases

Conclusion

Files and References