===== ULS-Client for Oracle Databases ===== Aka the ORACLE_TOOLS, is the [[http://www.gnu.org/licenses/gpl.txt|GPLv3]]-based ULS-client for gathering runtime metrics and status information, logging configuration settings and executing regular administrative jobs on Oracle database instances which are running on Unix-like operating systems. The ORACLE_TOOLS are now splitted up into two packages, see: * [[https://gitlab.com/universal-logging-system/ULS-Client-for-Oracle-Databases|ULS-Client-for-Oracle-Databases]] and * [[https://gitlab.com/universal-logging-system/Oracle-OpTools|Oracle-OpTools]] on [[https://gitlab.com/|gitlab]]. ==== Overview ==== The [[oracle_tools]] consists of several perl scripts and modules, bash scripts and configuration files to: * gather runtime metrics of an Oracle database instance * do regular fully configurable database backups * collect database relevant information as a configuration report * create STATSPACK snapshots and reports (if STATSPACK is available) * provide database start and stop scripts, used in [[:sccl]] packages * grant schema-to-schema access privileges * execute nightly and hourly scripts to accomplish maintenance tasks It requires [[http://www.perl.org/|Perl]] to run and the [[:uls:agents:linux]] for data transmission to the ULS-server. The monitoring script does only SELECTs against the database or call Oracle built-in utilities. No extra schema is needed but for most SELECTs the SYSDBA privileges are necessary. Usage of a more restrictive user (schema) is currently not considered. All results and/or reports are generated as ULS value files in the [[uls:agents:value_file_format]] which are sent to the ULS-server by using the [[:uls:agents:linux]]. This description of the [[oracle_tools]] often uses "" as a placeholder for an example ORACLE_SID, you have to replace it with the actual ORACLE_SID in your environment. ==== Features ==== The [[oracle_tools]] provide: * metrics about sessions, processes and PGA * several system statistics like db block changes, physical reads and writes, sorts, user calls and commits * information about tablespace usage * figures of the SGA, buffer cache and shared pool usage * metrics about fast recovery area, wait events, redo logs ==== Download & Installation ==== The ORACLE_TOOLS are only available as a zip archive and only has to be unzipped in a destination folder, no rpm, no deb. :!: be sure to have the [[uls:agents:linux]] installed. Try: $ whereis flush_test_values flush_test_values: /usr/local/bin/flush_test_values to check. Download the {{:uls:agents:oracle_tools_2013-10-13.zip}} and copy it to a destination folder (). Check the checksums: $ sha1sum oracle_tools*.zip 914f04461a1a734066e7c0c8ba900be92ea59931 oracle_tools_2013-10-13.zip Extract them: $ unzip oracle_tools*.zip Find or create a script to set the proper Oracle environment for your Oracle database instance, e.g. ~oracle/oracle_env_. Should be similar to: ORACLE_BASE=/oracle export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_HOME ORACLE_SID= export ORACLE_SID PATH=$PATH:/usr/local/bin:$ORACLE_HOME/bin export PATH LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib export LD_LIBRARY_PATH Create a link to it: $ ln -s ~oracle/oracle_env_ oracle_env ==== What You Got ==== The scripts of the ORACLE_TOOLS: * watch_oracle: gather runtime performance and status information * orarman: does a RMAN backup of the database or the redo logs * orabackup: does an "old fashioned" backup of the database * ora_dbinfo: collect inventory information about the database * ora_statspack: take snapshots and generate statspack reports * ora_grants: set schema privileges for database users In conjunction with the [[:sccl]]: * sccl_startstop * instance_start, instance_stop For regular execution of scripts: * nightly * hourly Miscellaneous: * *.pm: the needed Perl modules to run the Perl scripts * send_ipprot: script to transfer logged ip connection protocols * test_before_run.pl: test script to check whether a bash script od the ORACLE_TOOLS is continued or not * cron_oracle_tools: pattern for a crontab === What Happens? === The //normal// usage: * A crontab is defined, the scripts "watch_oracle", "hourly" and "nightly" are executed regularly * "watch_oracle" gathers every 10 minutes the performance and current state information and generates a file in the [[uls:agents:value_file_format]] which is transferred to ULS by the [[:uls:agents:linux]] * "nightly" is executed at 22:00h and * generates a statspack report * does a backup of the database (full on sundays, incremental on weekdays) * if there is a "nightly_custom" script it will be executed * "hourly" is executed shortly after the full hour and * takes a statspack snapshot * does a backup of all archived and the current redo logs * if there is an "hourly_custom" script it will be executed The hourly and nightly backups are delayed by a random number of minutes to avoid starting all database backups at the same time in the whole IT environment. ==== Regular Executions ==== A number of scripts is executed in regular time intervals to perform all actions. That is defined in the crontab. Create the crontab entries. Adjust the ORACLE_SID in the crontab file: $ sed -i "s/orcl/$ORACLE_SID/g" cron_oracle_tools Check the contents of the "cron_oracle_tools" if it corresponds to the syntax of your flavor of operating system:!: Become root and copy that file to its destination directory (that may differ depending on your operating system): # cp cron_oracle_tools /etc/cron.d/oracle_tools_ It should look like: # ----- # Monitoring script for Oracle 1,11,21,31,41,51 * * * * oracle /oracle/admin//oracle_tools/watch_oracle # # ----- # Hourly actions (e.g. backup of archived redo logs) 2 * * * * oracle /oracle/admin//oracle_tools/hourly # # ----- # Nightly jobs, MON to SAT (database backup, removal of trace files, etc) 2 22 * * * oracle /oracle/admin//oracle_tools/nightly Check the regular execution of the scripts :TODO: ==== Configuration ==== Configuration of all oracle_tools scripts is done in the "oracle_tools.conf", which should be a customized copy of the "oracle_tools.conf.default". $ cp oracle_tools.conf.default oracle_tools.conf Customize the "oracle_tools.conf" to your needs. See the [[oracle_tools#Detailed Script Descriptions]] for the script-specific customizations of the configuration file. ==== Detailed Script Descriptions ==== The detailed descriptions about the scripts can be found in specific documents: * [[uls:agents:oracle_tools:watch_oracle]] is a monitoring script to determine the database status and gather runtime metrics * [[uls:agents:oracle_tools:ora_dbinfo]] is a script to collect inventory information about the database software and instance * [[uls:agents:oracle_tools:orarman]] executes RMAN backups * [[uls:agents:oracle_tools:orabackup]] performs "old style" database backups by copying the datafiles to a destination directory * [[uls:agents:oracle_tools:ora_statspack]] takes statspack snapshots and generates statspack reports When using the ORACLE_TOOLS together with the [[:sccl]]: * [[uls:agents:oracle_tools:sccl_integration]]