General:BaseInstallation
WIP
Contents
- 1 Generalty
- 2 Installation of central infrastructure
Generalty
Limits of this section
This section does not claim to cover all possible cases of installation and configuration of dbSQWare.
It should not be forgotten that this is a very flexible tool that is capable of adapting to a good number of architectures.
This section is for DBAs who are familiar with Linux/Unix. This is an installation guide for someone who is already comfortable with these parts.
Before you say "it doesn't work", read the error message carefully and think about what you need at the core level for the scripts to work. For example, if you can't do a sqlplus command line to connect to your Oracle instance, there's no reason why the scripts should be able to do it (there's no magic)
!
If one of the chapters does not work, do not go any further, you must first correct it before continuing !
We advise you to call upon a tool specialist to define with you the best installation architecture suited to your fleet and your work organization https://www.dbsqware.com. Training courses are also available from partners to explore the different parts and the different possible configurations in more depth.
This section is based on a Rocky Linux installation of the central point on a machine or virtual machine (2 vCPU and 4 GB RAM) with 100 GB disk (Allows you to keep 2 years of history for around a hundred instances). You can obviously install it on another type of Linux/Unix, you will then have to adapt the proposed system commands if necessary.
It only covers the installation of the central base, for other settings refer to the appropriate sections.
Reminder of the structure of the tool
dbSQWare is composed of four add-ons:
SQWareProduction => operating shell scripts (often local on the machine hosting the DBMS to be managed) SQWareRepository => reference and storage of indicators (database MySQL or MariaDB recommended) SQWareCentral => centralization of sources and launch of commands SQWareWeb => graphical restitution interface
Installation of central infrastructure
As a reminder, this document only presents one way to install the tool. There are multiple possible configurations.
This section presents a “compact” installation, in other words the installation of the SQWareCentral, SQWareRepository and SQWareWeb modules on the same machine, we will call it vmsqwarebox.
This machine will also host the SQWareProduction sources.
For the exploitation of MsSql, we will also install SQWareProduction MsSql (and FreeTds) on this same machine.
You can obviously install it on another type of Linux/Unix, in which case you will need to adapt the proposed system commands.
The installation can also be performed in "distributed" mode, i.e., each module on its own machine; in this case, you will need to adapt the configurations.
We will cover the installation for Oracle (Oracle), Sybase (Sybase), Sql-Server (MSSQL), MySQL (MySQL), and PostgreSQL (Postgres).
Only use the commands for the DBMS(s) you are interested in and/or adapt the commands for other DBMS.
VM Features
Rocky Linux release 9.x 64 bits.
Sizing for the virtual machine :
2 vCPU 4 GB RAM 2 GB swap 100 GB disk
Verification / implementation of prerequisites
Most of the commands in this chapter are run with the unix root user, either because it is necessary for "system" installations, or because we are using scripts that loop through all users.
Adjust the commands if you want to do the installation step by step, logging in with the correct user each time.
It is essential to follow the commands in order and to check each time that the expected result is present before moving on to the next step, otherwise, it risks blocking and you will have difficulty finding where.
Basically, all commands have been tested, so you can use the good old copy/paste method in most cases.
System checks
Checking the OS version :
[root@vmsqwarebox ~]# cat /etc/redhat-release Rocky Linux release 9.3 (Blue Onyx)
Disk configuration :
[root@vmsqwarebox ~]# vgs
VG #PV #LV #SN Attr VSize VFree
data_vg 1 5 0 wz--n- <60.91g 20.83g
root_vg 1 4 0 wz--n- 37.50g <20.48g
[root@vmsqwarebox ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.9G 0 1.9G 0% /dev
tmpfs 1.9G 0 1.9G 0% /dev/shm
tmpfs 1.9G 8.7M 1.9G 1% /run
tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup
/dev/mapper/root_vg-root 10G 1.7G 8.4G 17% /
/dev/sda2 1014M 245M 770M 25% /boot
/dev/sda1 599M 5.8M 594M 1% /boot/efi
/dev/mapper/root_vg-var 5.0G 379M 4.7G 8% /var
/dev/mapper/data_vg-applis 5.0G 69M 5.0G 2% /applis
/dev/mapper/data_vg-data 15G 140M 15G 1% /data
/dev/mapper/data_vg-home 10G 105M 9.9G 2% /home
/dev/mapper/data_vg-backups 10G 105M 9.9G 2% /backups
tmpfs 374M 0 374M 0% /run/user/0
[root@vmsqwarebox ~]# free
total used free shared buff/cache available
Mem: 3825740 314080 1493080 8868 2018580 3237608
Swap: 2097148 0 2097148
Update the OS to the latest patch version:
[root@vmsqwarebox ~]# dnf update
Disabling the firewall and SELinux (optional but help for installation) :
# Disabling the firewall systemctl disable firewalld systemctl stop firewalld # Deactivate SELINUX #Check /usr/sbin/getenforce Enforcing vi /etc/sysconfig/selinux SELINUX=enforcing => SELINUX=disabled (reboot) shutdown -r now #Check again /usr/sbin/getenforce Disabled
Prefer a server set to English, it’s easier for error messages (instead of the classic fr_FR.UTF-8):
#If needed you can install or change the locale
dnf install langpacks-en glibc-all-langpacks -y
#A server set to English, it’s easier for read error messages !
localectl
System Locale: LANG=fr_FR.UTF-8
VC Keymap: fr-oss
X11 Layout: fr
X11 Variant: oss
localectl set-locale LANG=en_US.UTF-8
localectl
System Locale: LANG=en_US.UTF-8
VC Keymap: fr-oss
X11 Layout: fr
X11 Variant: oss
#Keyboard (temp)
loadkeys en
localectl status
localectl list-keymaps | grep en
localectl set-keymap en
localectl set-x11-keymap en
localectl status
System Locale: LANG=en_US.UTF-8
VC Keymap: en
X11 Layout: en
#You can check if you have the right Time Zone
timedatectl
Local time: Wed 2024-04-24 07:19:51 EDT
Universal time: Wed 2024-04-24 11:19:51 UTC
RTC time: Wed 2024-04-24 11:19:51
Time zone: America/New_York (EDT, -0400)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no
#If you need to change it
rm -rf /etc/localtime
timedatectl set-timezone Europe/Paris
timedatectl
Local time: Wed 2024-04-24 13:20:44 CEST
Universal time: Wed 2024-04-24 11:20:44 UTC
RTC time: Wed 2024-04-24 11:20:44
Time zone: Europe/Paris (CEST, +0200)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no
NTP installation and activation (we use Chrony):
dnf install -y chrony #Check config of chronyd (old ntpd) cat /etc/chrony.conf #Here we use a close server pool, ex: pool 0.europe.pool.ntp.org iburst #Start and activation for Chrony systemctl enable --now chronyd #If Firewalld is running, allow NTP service. NTP uses [123/UDP]. firewall-cmd --add-service=ntp success firewall-cmd --runtime-to-permanent success #Restart the service systemctl restart chronyd #Check the service state systemctl is-enabled chronyd enabled systemctl status chronyd chronyd.service - NTP client/server Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled) ...
RPM installation that will be needed later !
Many EPEL packages require the CodeReady Builder (CRB) repository. It is recommended that you run /usr/bin/crb enable to enable the CRB repository. dnf install -y open-vm-tools dnf install -y nmap bind-utils ksh rsync openssh-server openssl-devel openssl dnf install -y epel-release binutils net-tools telnet control-center gcc gcc-c++ glibc glibc-common libstdc++ libstdc++-devel make sysstat s-nail postfix cyrus-sasl cyrus-sasl-lib cyrus-sasl-plain cyrus-sasl-scram cyrus-sasl-md5 cyrus-sasl-ntlm ## for the Oracle 19c client (but not only that!) dnf install -y zip unzip git wget mlocate nano binutils.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc++.i686 libstdc++.x86_64 libstdc++-devel.i686 libstdc++-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 ## for the Python compilation (for SQWarePredict) dnf install -y zlib-devel bzip2 bzip2-devel readline-devel sqlite sqlite-devel openssl-devel xz xz-devel libffi-devel findutils ncurses-devel
A number of RPMs are required for the Oracle installation, so it is worth checking for them and installing them if necessary.
Example of a verification command :
$ rpm -q gcc gcc-11.4.1-2.1.el9.x86_64
It may be useful to know the architecture of the installed RPMs (x86_64 and i386 for example). To do this, simply change the default display format by editing the file ~/.rpmmacros .
$ echo '%_query_all_fmt %%{name}-%%{version}-%%{release} (%%{arch})' >>~/.rpmmacros
$ rpm -q gcc
gcc-11.4.1-2.1.el9 (x86_64)
Or directly in CLI :
$ rpm -q gcc --queryformat '%{name}-%{release} %{version} (%{arch})\n'
gcc-2.1.el9 11.4.1 (x86_64)
If a RPM is missing you can obtaint this (or something close) :
[root@vmsqwarebox /]# rpm -q libXpm-devel package libXpm-devel is not installed
If you need to install a missing RPM :
[root@vmsqwarebox /]# rpm -ivh libXpm-devel-3.5.5-3.x86_64.rpm warning: libXpm-devel-3.5.5-3.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 37017186 Preparing... ########################################### [100%] 1:libXpm-devel ########################################### [100%]
The simplest, direct installation with yum (you need a available repo) :
[root@vmsqwarebox /]# dnf install libXpm-devel ksh binutils gcc gcc-c++ glibc glibc-common libstdc++ libstdc++-devel make sysstat ##Mail configuration and activation (change the relayhost at least) vi /etc/postfix/main.cf systemctl start postfix systemctl enable postfix systemctl status postfix
Unix users management
Creation of users and groups with bash as default shell (you can made some personalization like the uid and gid) :
groupadd dba useradd -g dba -s /bin/bash -d /home/dbsqware dbsqware useradd -g dba -s /bin/bash -d /home/oracle oracle useradd -g dba -s /bin/bash -d /home/sybase sybase useradd -g dba -s /bin/bash -d /home/mssql mssql useradd -g dba -s /bin/bash -d /home/mysql mysql useradd -g dba -s /bin/bash -d /home/postgres postgres
Set password to users :
passwd dbsqware passwd oracle passwd sybase passwd mssql passwd mysql passwd postgres
Outpout example :
[root@vmsqwarebox /]# passwd dbsqware Changing password for user dbsqware. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. [root@vmsqwarebox /]# passwd oracle Changing password for user oracle. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully. ...
Change their home directory rights :
cd /home chmod 750 dbsqware oracle sybase mssql mysql postgres
Check home directories :
[root@vmsqwarebox /]# ls -l /home/ total 32 drwxr-x--- 3 dbsqware dba 4096 mai 13 21:54 dbsqware drwxr-x--- 3 mssql dba 4096 mai 13 21:54 mssql drwxr-x--- 3 mysql dba 4096 mai 13 21:54 mysql drwxr-x--- 3 oracle dba 4096 mai 13 21:54 oracle drwxr-x--- 3 postgres dba 4096 mai 13 21:54 postgres drwxr-x--- 3 sybase dba 4096 mai 13 21:54 sybase
At least for the installation set the dbsqware user with 'sudo ALL' :
## Add sudo cp -p /etc/sudoers /etc/sudoers.back chmod 700 /etc/sudoers vi /etc/sudoers root@vmsqwarebox:/root # grep wheel /etc/sudoers ## Allows people in group wheel to run all commands # %wheel ALL=(ALL) ALL %wheel ALL=(ALL) NOPASSWD: ALL chmod 440 /etc/sudoers id dbsqware uid=1000(dbsqware) gid=1000(dba) groups=1000(dba) usermod -a -G wheel dbsqware id dbsqware uid=1000(dbsqware) gid=1000(dba) groups=1000(dba),10(wheel)
Setting up the trees
Preparing the /applis trees for installing the application binaries (if necessary) :
mkdir -p /applis/oracle chown oracle:dba /applis/oracle mkdir -p /applis/sybase chown sybase:dba /applis/sybase mkdir -p /applis/mssql chown mssql:dba /applis/mssql mkdir -p /applis/mysql chown mysql:dba /applis/mysql mkdir -p /applis/dbsqware chown dbsqware:dba /applis/dbsqware mkdir -p /applis/postgres chown postgres:dba /applis/postgres
Preparing the /data trees for data storage (if necessary) :
mkdir -p /data/oracle chown oracle:dba /data/oracle mkdir -p /data/sybase chown sybase:dba /data/sybase mkdir -p /data/mssql chown mssql:dba /data/mssql mkdir -p /data/mysql chown mysql:dba /data/mysql mkdir -p /data/dbsqware chown dbsqware:dba /data/dbsqware mkdir -p /data/postgres chown postgres:dba /data/postgres mkdir -p /data/dbsqware/admin/AsynchronousFiles /data/dbsqware/admin/AsynchronousFilesTmp /data/dbsqware/admin/AsynchronousFilesArch chown -R dbsqware:dba /data/dbsqware/admin # Specific trees for AWR and, or Statspack mkdir -p /data/oracle/awr chown oracle:dba /data/oracle/awr # Specific trees for slow query files mkdir -p /data/mysql/SlowQuery chown mysql:dba /data/mysql/SlowQuery
Preparing the /backups trees for backups storage (if necessary) :
mkdir -p /backups/oracle chown oracle:dba /backups/oracle mkdir -p /backups/sybase chown sybase:dba /backups/sybase mkdir -p /backups/mssql chown mssql:dba /backups/mssql mkdir -p /backups/mysql chown mysql:dba /backups/mysql mkdir -p /backups/dbsqware chown dbsqware:dba /backups/dbsqware mkdir -p /backups/postgres chown postgres:dba /backups/postgres
Checking File Trees :
[root@vmsqwarebox /]# ls -l /applis total 40 drwxr-xr-x 2 dbsqware dba 4096 mai 14 04:06 dbsqware drwxr-xr-x 2 mssql dba 4096 mai 14 04:06 mssql drwxr-xr-x 2 mysql dba 4096 mai 14 04:06 mysql drwxr-xr-x 2 oracle dba 4096 mai 14 04:06 oracle drwxr-xr-x 2 sybase dba 4096 mai 14 04:06 sybase drwxr-xr-x 2 postgres dba 4096 mai 14 04:06 postgres [root@vmsqwarebox /]# ls -l /data total 40 drwxr-xr-x 2 dbsqware dba 4096 mai 14 04:06 dbsqware drwxr-xr-x 2 mssql dba 4096 mai 14 04:06 mssql drwxr-xr-x 2 mysql dba 4096 mai 14 04:06 mysql drwxr-xr-x 2 oracle dba 4096 mai 14 04:06 oracle drwxr-xr-x 2 sybase dba 4096 mai 14 04:06 sybase drwxr-xr-x 2 postgres dba 4096 mai 14 04:06 postgres [root@vmsqwarebox /]# ls -l /backups total 32 drwxr-xr-x 2 dbsqware dba 4096 mai 14 04:06 dbsqware drwxr-xr-x 2 mssql dba 4096 mai 14 04:06 mssql drwxr-xr-x 2 mysql dba 4096 mai 14 04:06 mysql drwxr-xr-x 2 oracle dba 4096 mai 14 04:06 oracle drwxr-xr-x 2 sybase dba 4096 mai 14 04:06 sybase drwxr-xr-x 2 postgres dba 4096 mai 14 04:06 postgres
Clés ssh
Preparing ssh keys for dbsqware.
With the user root :
su - dbsqware -c " ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa chmod go-w \$HOME cat \$HOME/.ssh/id_rsa.pub >>\$HOME/.ssh/authorized_keys chmod 700 \$HOME/.ssh chmod 600 \$HOME/.ssh/authorized_keys"
If everything worked correctly, the dbsqware user should be able to connect to itself via ssh (this is for more flexibility in certain configuration cases).
Test if it works :
[root@vmsqwarebox home]# su - dbsqware $ ssh dbsqware@vmsqwarebox The authenticity of host 'vmsqwarebox (85.31.222.135)' can't be established. RSA key fingerprint is bd:e1:d0:d3:c5:2d:94:8f:df:f0:a2:50:42:8d:ad:1d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added ' vmsqwarebox,bbb.xx.aaa.yyy' (RSA) to the list of known hosts. Last login: Mon May 14 04:33:25 2012 from vmsqwarebox $ exit Connection to vmsqwarebox closed.
For each of the unix users you created previously, you must repeat the same operation but also authorize the dbsqware user to connect to them (to launch the scripts automatically).
In the same way as before, either you do it on each user manually, or you use the following script snippet as root :
lvsqw_SshKey_dbsqware=$(cat /home/dbsqware/.ssh/id_rsa.pub) for lvsqw_User in `echo 'oracle sybase mssql mysql postgres'` do su - $lvsqw_User -c " ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa chmod go-w \$HOME cat \$HOME/.ssh/id_rsa.pub >>\$HOME/.ssh/authorized_keys echo '$lvsqw_SshKey_dbsqware' >>\$HOME/.ssh/authorized_keys chmod 700 \$HOME/.ssh chmod 600 \$HOME/.ssh/authorized_keys" done ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa chmod go-w $HOME echo "$lvsqw_SshKey_dbsqware" >>$HOME/.ssh/authorized_keys chmod 700 $HOME/.ssh chmod 600 $HOME/.ssh/authorized_keys
Test if it works (example for the oracle user to repeat for all users) :
[root@vmsqwarebox home]# su - dbsqware $ id uid=1000(dbsqware) gid=1000(dba) groupes=1000(dba),10(wheel) $ ssh oracle@vmsqwarebox $ id uid=1001(oracle) gid=1000(dba) groupes=1000(dba) $ ssh oracle@vmsqwarebox The authenticity of host 'vmsqwarebox (85.31.222.135)' can't be established. RSA key fingerprint is bd:e1:d0:d3:c5:2d:94:8f:df:f0:a2:50:42:8d:ad:1d. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'vmsqwarebox,bbb.xx.aaa.yyy' (RSA) to the list of known hosts. Last login: Mon May 14 05:07:47 2012 from vmsqwarebox $ id uid=1001(oracle) gid=1000(dba) groupes=1000(dba) $ exit Connection to vmsqwarebox closed. $ exit Connection to vmsqwarebox closed.
Environment files
Setting up a .bash_profile and .bashrc for all users :
cat <<EOFCAT >/tmp/.vimrc
syntax off
set mouse-=a
set noai
EOFCAT
cat <<EOFCAT >/tmp/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/ccs/bin:/usr/local/sbin:/usr/openwin/bin
EOFCAT
cat <<EOFCAT >/tmp/.bashrc
# Source global definitions
# if [ -f /etc/bashrc ] && [ \$(ps -p \$\$|grep -c 'bash') -ne 0 ] ; then
# . /etc/bashrc
# fi
unalias ls 2>/dev/null
alias ll='ls -al'
alias lt='ls -alrt'
set -o vi
EOFCAT
chmod 777 /tmp/.bash_profile /tmp/.bashrc /tmp/.vimrc
for lvsqw_User in `echo 'dbsqware oracle sybase mssql mysql postgres root'`
do
su - $lvsqw_User -c "
cp /tmp/.vimrc \$HOME/.
cp /tmp/.bash_profile \$HOME/.
cp /tmp/.bashrc \$HOME/.
cd
ln -s .bash_profile .profile
chmod 640 \$HOME/.vimrc
chmod 640 \$HOME/.bash_profile
chmod 640 \$HOME/.bashrc"
done
rm -f /tmp/.bash_profile /tmp/.bashrc
cat <<EOCAT >> $HOME/.bashrc
export PS1="\`id| cut -d')' -f1| cut -d'(' -f2\`@\$(hostname|cut -d '.' -f1):"'\$PWD # '
EOCAT
for lvsqw_User in `echo 'oracle sybase mssql mysql postgres'`
do
su - $lvsqw_User -c "
ln -s /home/dbsqware/dbSQWare/SQWareProduction \$HOME/SQWareProduction"
done
Checking that all files have been created :
[root@vmsqwarebox ~]# ls -al /home/* /home/dbsqware: total 56 drwxr-x--- 3 dbsqware dba 4096 May 14 05:30 . drwxr-xr-x 7 root root 4096 May 14 03:57 .. -rw-r----- 1 dbsqware dba 48 May 14 05:30 .bashrc -rw-r----- 1 dbsqware dba 13 May 14 05:30 .bash_profile -rw------- 1 dbsqware dba 746 May 14 05:26 .sh_history drwx------ 2 dbsqware dba 4096 May 14 04:37 .ssh -rw------- 1 dbsqware dba 922 May 14 05:23 .viminfo /home/mssql: total 40 drwxr-x--- 3 mssql dba 4096 May 14 05:30 . drwxr-xr-x 7 root root 4096 May 14 03:57 .. -rw-r----- 1 mssql dba 48 May 14 05:30 .bashrc -rw-r----- 1 mssql dba 13 May 14 05:30 .bash_profile drwx------ 2 mssql dba 4096 May 14 05:16 .ssh /home/mysql: total 40 drwxr-x--- 3 mysql dba 4096 May 14 05:30 . drwxr-xr-x 7 root root 4096 May 14 03:57 .. -rw-r----- 1 mysql dba 48 May 14 05:30 .bashrc -rw-r----- 1 mysql dba 13 May 14 05:30 .bash_profile drwx------ 2 mysql dba 4096 May 14 05:16 .ssh /home/oracle: total 48 drwxr-x--- 3 oracle dba 4096 May 14 05:30 . drwxr-xr-x 7 root root 4096 May 14 03:57 .. -rw-r----- 1 oracle dba 48 May 14 05:30 .bashrc -rw-r----- 1 oracle dba 13 May 14 05:30 .bash_profile -rw------- 1 oracle dba 76 May 14 05:12 .sh_history drwx------ 2 oracle dba 4096 May 14 05:16 .ssh /home/sybase: total 40 drwxr-x--- 3 sybase dba 4096 May 14 05:30 . drwxr-xr-x 7 root root 4096 May 14 03:57 .. -rw-r----- 1 sybase dba 48 May 14 05:30 .bashrc -rw-r----- 1 sybase dba 13 May 14 05:30 .bash_profile drwx------ 2 sybase dba 4096 May 14 05:16 .ssh /home/postgres: total 40 drwxr-x--- 3 sybase dba 4096 May 14 05:30 . drwxr-xr-x 7 root root 4096 May 14 03:57 .. -rw-r----- 1 sybase dba 48 May 14 05:30 .bashrc -rw-r----- 1 sybase dba 13 May 14 05:30 .bash_profile drwx------ 2 sybase dba 4096 May 14 05:16 .ssh
Added the source part of the SQWareProduction environment in the .profile of all users :
for lvsqw_Sgbd in `echo 'oracle sybase mssql mysql postgres'`
do
lvsqw_tmp=$(echo "$lvsqw_Sgbd"|sed 's,\([a-z][a-z][a-z]\)[a-z]*,\1,')
lvsqw_tmp1=$(echo "$lvsqw_tmp"|sed 's,\([a-z]\)[a-z]*,\1,'|tr '[a-z]' '[A-Z]')
lvsqw_tmp2=$(echo "$lvsqw_tmp"|sed 's,[a-z]\([a-z][a-z]\)[a-z]*,\1,')
TrigrammeSgbd="${lvsqw_tmp1}$lvsqw_tmp2"
if [ "$TrigrammeSgbd" = "Mss" ]
then
TrigrammeSgbd="Msq"
lvsqw_tmp="msq"
elif [ "$TrigrammeSgbd" = "Pos" ]
then
TrigrammeSgbd="Pg"
lvsqw_tmp="pg"
fi
echo "DBMS processing: '$lvsqw_Sgbd', '$TrigrammeSgbd'"
cat <<EOFCAT >> /home/$lvsqw_Sgbd/.bash_profile
# env dbSQWare for $lvsqw_Sgbd, must stay at end of .bash_profile
export gvsqw_Env='PRD'
export gvsqw_${TrigrammeSgbd}Bin=\$HOME/SQWareProduction/$lvsqw_Sgbd/bin
lvsqw_IsTerminal=\$(tty 2>&1 >/dev/null;echo \$?)
if [ "\$lvsqw_IsTerminal" = "0" ] && [ -r \$gvsqw_${TrigrammeSgbd}Bin/../etc/.profile_confort ]
then
. \$gvsqw_${TrigrammeSgbd}Bin/../etc/.profile_confort
fi
EOFCAT
done
Installation sources on vmsqwarebox
Most of the commands in this chapter are executed with the UNIX user dbsqware.
It is essential to follow the commands in order and to check each time that the expected result is present before moving on to the next step; otherwise, it may block and you will have difficulty finding where it is. Also, check that all the previous steps have been executed without errors.
In this section, we will deploy all the dbSQWare scripts.
There will be:
- SQWareCentral, which will be used in particular for the automatic execution of scripts across the entire database.
- SQWareProduction, which is actually the source repository for this module. You will need to configure it to meet your database standards before deploying it.
- SQWareRepository, which contains the SQL scripts allowing you to create the repository. *SQWareWeb is the graphical rendering component that runs on Apache and PHP. It can be deployed on the same machine as SQWareCentral or moved to another machine, as you prefer.
Extracting the archive
Collect the sources and place them in /tmp :
[root@vmsqwarebox ~]# su - dbsqware $ cd $ tar zxvf /tmp/dbSQWare_full_latest.tgz dbSQWare/ dbSQWare/SQWareProduction/ dbSQWare/SQWareProduction/ingres/ dbSQWare/SQWareProduction/ingres/tools/ ... dbSQWare/SQWareProduction/oracle/ dbSQWare/SQWareProduction/oracle/tools/ dbSQWare/SQWareProduction/oracle/tools/sqwora_RmanConfigure.ksh ... dbSQWare/SQWareCentral/ dbSQWare/SQWareCentral/lib/ dbSQWare/SQWareCentral/lib/sqwctl_MenuAction.lib ... dbSQWare/SQWareWeb/custom_ref/ dbSQWare/SQWareWeb/custom_ref/ingres/ ... <br> Verification all modules are installed : <pre> $ ls -l ~dbsqware/dbSQWare total 12 lrwxrwxrwx 1 dbsqware dba 24 Jan 2 2022 generic -> SQWareProduction/generic drwxr-x--- 3 dbsqware dba 56 Feb 23 14:51 SQWareAdmin drwxr-x--- 16 dbsqware dba 4096 Feb 23 14:51 SQWareCentral drwxr-x--- 4 dbsqware dba 33 Feb 23 14:51 SQWarePredict drwxr-x--- 15 dbsqware dba 192 Feb 23 14:51 SQWareProduction drwxr-x--- 17 dbsqware dba 4096 Feb 23 14:51 SQWareRepository drwxr-x--- 11 dbsqware dba 185 Feb 23 14:51 SQWareWeb -rwxr-x--- 1 dbsqware dba 9 Feb 23 14:51 SQW_VERSION
dbsqware user profile configuration for SQWareCentral
Setting up default configurations:
cat <<EOFCAT >/home/dbsqware/dbSQWare/SQWareCentral/etc_cust/sqwctl_GlobalVar.cfg export gvsqw_AsynchronousFilesDir="/data/dbsqware/admin/AsynchronousFiles" export gvsqw_AsynchronousFilesDirTmp="/data/dbsqware/admin/AsynchronousFilesTmp" export gvsqw_AsynchronousFilesDirArch="/data/dbsqware/admin/AsynchronousFilesArch" EOFCAT chmod 750 /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/sqwctl_GlobalVar.cfg #change for the right email address ! cat <<EOFCAT >/home/dbsqware/dbSQWare/SQWareProduction/generic/etc_cust/sqwgen_GlobalVar.cfg #Put NoNail in gvsqw_GlobalMail if you don't want to send mail on error export gvsqw_GlobalMail='dba@mydomane.com' #for SQWareWeb export gvsqw_WebDbaURL="https://$(hostname)" export gvsqw_WebDbaUser='dbsqware@$(hostname)' EOFCAT chmod 750 /home/dbsqware/dbSQWare/SQWareProduction/generic/etc_cust/sqwgen_GlobalVar.cfg ln -s /data/dbsqware/admin/AsynchronousFilesArch /home/dbsqware/dbSQWare/SQWareWeb/sqw_logdir ln -s /data/mysql/SlowQuery /home/dbsqware/dbSQWare/SQWareWeb/sqw_mysql_slow ln -s /data/oracle/awr /home/dbsqware/dbSQWare/SQWareWeb/sqw_oracle_awr
Add the following lines to the .bash_profile of the unix user dbsqware so that it sources the SQWareCentral environment.
dbsqware@vmsqwarebox:/home/dbsqware $ vi .bash_profile ## SQWareCentral export gvsqw_Env='PRD' export gvsqw_SQWareCentralPath=$HOME/dbSQWare/SQWareCentral export gvsqw_SQWareProductionPath=$HOME/dbSQWare/SQWareProduction . $gvsqw_SQWareCentralPath/etc/.profile
To check if everything is OK, source the user's .bash_profile and you should have a result like this:
dbsqware@vmsqwarebox:/home/dbsqware $ . ~/.bash_profile
_ _ ____ _____ __
__| | |__/ ___| / _ \ \ / /_ _ _ __ ___
/ _` | '_ \___ \| | | \ \ /\ / / _` | '__/ _ \
| (_| | |_) |__) | |_| |\ V V / (_| | | | __/
\__,_|_.__/____/ \__\_\ \_/\_/ \__,_|_| \___|
###################################################################
# SQWareCentral module of dbSQWare
# Copyright (C) 2010-2024, dbSQWare (www.dbsqware.com)
# dbSQWare is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# CGU/CGA for more details.
# You should have received a copy of the CGU/CGA
# along with this program. If not, see <https://www.dbsqware.com/licenses/>.
# For information : contact@dbsqware.com or www.dbsqware.com
###################################################################
# SqwVersion=2024.03
# For help type 'show <-h|-help|--help>'
Installation/configuration of SQWareRepository
This chapter describes how to create the repository. This is a MariaDB database >=10.1... (MariaDB 10.11 recommended and on the provided SqWarebox).
The volume will depend on the number of instances processed, and certain other settings such as the history depth or the number of log lines uploaded to the database. In most cases, for around a hundred managed instances and the default dbSQWare settings, the volume should not exceed 15 GB under full load (data is purely indicative).
Installations will be performed using the SQL scripts located in:
~dbsqware/dbSQWare/SQWareRepository/$TypeSgbd/sql/*.sql
MariaDB database installation
This chapter assumes you already have a MySQL/MariaDB client installed under the mysql username. This client will also be used by the SQWareCentral module to connect to SQWareRepository and generate lists of instances to process, for example. For example, you can see the appendix "Quick Installation of a MariaDB Instance" to create the instance that will host the repository on the central point. Please note that this is only an example; it can be created anywhere.
This chapter assumes you are logged in to UNIX with the mysql username.
Creating the Database and Users (Example):
[root@vmsqwarebox ~]# su - mysql $ mysql -uroot -pPassMys Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 10.6.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost) [(none)]> create database dbsqware_db; Query OK, 1 row affected (0.00 sec) (root@localhost) [(none)]> GRANT ALL PRIVILEGES ON dbsqware_db.* TO 'dbsqware_user'@'localhost' IDENTIFIED BY 'PassMys'; Query OK, 0 rows affected (0.01 sec) (root@localhost) [(none)]> exit Bye mysql@vmsqwarebox:/home/mysql () $ exit logout [root@vmsqwarebox ~]# exit logout
Creating objects
Object creation will be done using the Unix username dbsqware. To do this, you must first create the configuration file for accessing MariaDB/MySQL.
Here's what the file should contain (adapt it with your credentials):
dbsqware@vmsqwarebox:/home/dbsqware $ vi /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf [client] host = localhost port = 3306 database = dbsqware_db user = dbsqware_user password = PassMys chmod 640 /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf
Connexion test to the repository (with the file) :
dbsqware@vmsqwarebox:/home/dbsqware $ mysql --defaults-file='/home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf' Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 11.4.2-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [dbsqware_db]> show databases; +--------------------+ | Database | +--------------------+ | dbsqware_db | | information_schema | +--------------------+ 2 rows in set (0.001 sec) MariaDB [dbsqware_db]> exit Bye
Installing the repository :
dbsqware@vmsqwarebox:/home/dbsqware $ dbspatch
_ _ ____ _____ __
__| | |__/ ___| / _ \ \ / /_ _ _ __ ___
/ _` | '_ \___ \| | | \ \ /\ / / _` | '__/ _ \
| (_| | |_) |__) | |_| |\ V V / (_| | | | __/
\__,_|_.__/____/ \__\_\ \_/\_/ \__,_|_| \___|
###################################################################
# SQWareCentral module of dbSQWare
# Copyright (C) 2010-2024, dbSQWare (www.dbsqware.com)
# dbSQWare is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# CGU/CGA for more details.
# You should have received a copy of the CGU/CGA
# along with this program. If not, see <http://www.dbsqware.com/licenses/>.
# For information : contact@dbsqware.com or www.dbsqware.com
###################################################################
# SqwVersion=2024.08
# For help type 'show <-h|-help|--help>'
Create tree for custom dir ...
create directory /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom
create directory /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/images
Copy ref files to custom if not exists ...
copy /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom_ref/authentication_custom.php on /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/authentication_custom.php
copy /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom_ref/GlobalHead.html on /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/GlobalHead.html
copy /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom_ref/images/client.png on /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/images/client.png
copy /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom_ref/sqwareweb.ini on /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/sqwareweb.ini
Sourcing sqwctl_Global.lib v2024.08 SQWareCentral (dbSQWare) ...
eval global vars:
gvsqw_RsyncDefaultUser="$gvsqw_UserExec@RsyncHost" => gvsqw_RsyncDefaultUser=dbsqware@RsyncHost
Checks before install:
SQWareRepository source to install: /home/dbsqware/dbSQWare/SQWareRepository
Connect string to repository:
MySQL_Command="mysql --defaults-file='/home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf'"
[client]
host = localhost
port = 3306
database = dbsqware_db
user = dbsqware_user
10.11.11-MariaDB-log
Major version of Repository, 1011, 10.11.11
Checks for install mode (full or patch):
You will make a full install.
Do you want to full install SQWareRepository [y-Y-o-O-n-N]?
Here type Y, the repository installation will take some time.
... ... ... END_TRT Code: 0 --> Update_06_MenuY.sql executed successfully. END_TRT Code: 0 --> Update_07_MenuY.sql executed successfully. END_TRT Code: 0 --> Update_08_MenuY.sql executed successfully. END_TRT Code: 0 --> Update_09_MenuY.sql executed successfully. Job Install SQWareRepository proceeded successfully Begining : 2024-04-15 12:13:31 End : 2024-04-15 12:15:04 Duration : 00:01:33
Installation/configuration de SQWareWeb
Ce chapitre présente l’installation et la configuration de SQWareWeb.
Nous partons du principe que apache et PHP 8.2 sont installés et opérationnels pour se connecter à MariaDB/MySQL (PHP >= 7.4 et 8.x fonctionnent également en principe).
Pour déployer apache et PHP 8.2, vous pouvez regarder l'annexe « Installation rapide de apache et PHP 8.2 ». Attention, il ne s’agit que d’un exemple, reportez-vous aux sites officiels des outils pour plus d’information https://httpd.apache.org/ et https://www.php.net/.
On part du principe que nous sommes connectés sous unix avec le user dbsqware pour ce chapitre. Nous allons voir une installation de SQWareWeb sur la même machine que SQWareCentral, mais ceci n’est pas une obligation.
Mise en place
Nous avons vu lorsque nous avons déployé les sources de dbSQWare que nous possédions l’arborescence /home/dbsqware/dbSQWare/SQWareWeb. Celle-ci contient les sources du site que nous allons mettre en place.
Vérification des arborescences :
$ cd /home/dbsqware/dbSQWare/SQWareWeb $ ls -l total 24 drwxr-x--- 2 dbsqware dba 70 Nov 29 19:46 css drwxr-xr-x 3 dbsqware dba 64 Dec 8 14:50 custom drwxr-x--- 3 dbsqware dba 64 Nov 29 19:46 custom_ref drwxr-x--- 6 dbsqware dba 60 Nov 29 19:46 exploit drwxr-x--- 19 dbsqware dba 322 Nov 29 19:46 htdocs drwxr-x--- 2 dbsqware dba 8192 Nov 29 19:46 images drwxr-x--- 14 dbsqware dba 177 Nov 29 19:46 jobs_help drwxr-x--- 5 dbsqware dba 239 Nov 29 19:46 js drwxr-x--- 3 dbsqware dba 4096 Nov 29 19:46 lib -rwxr-x--- 1 dbsqware dba 22 Nov 29 19:46 phpinfo.php lrwxrwxrwx 1 dbsqware dba 42 Dec 8 14:54 sqw_logdir -> /data/dbsqware/admin/AsynchronousFilesArch lrwxrwxrwx 1 dbsqware dba 21 Dec 8 14:54 sqw_mysql_slow -> /data/mysql/SlowQuery lrwxrwxrwx 1 dbsqware dba 16 Dec 8 14:54 sqw_oracle_awr -> /data/oracle/awr drwxr-x--- 2 dbsqware dba 214 Nov 29 19:46 templates -rwxr-x--- 1 dbsqware dba 1274 Nov 29 19:46 test.php
Conf http
Mettez en place le VirtualHost apache pour le site (si vous ne tournez qu'en http). Encore une fois, il ne s’agit ici que d’un exemple que vous pouvez customiser (avec le user root, adaptez si besoin).
$ vi /etc/httpd/conf.d/httpd-vhosts.conf
<Directory "/home/dbsqware/dbSQWare/SQWareWeb">
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>
<Directory "/data/oracle/awr">
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>
<VirtualHost *:80>
DocumentRoot /home/dbsqware/dbSQWare/SQWareWeb
ServerName vmsqwarebox
ServerAlias vmsqwarebox vmsqwareboxRocky9 webdba
</VirtualHost>
[root@vmsqwarebox ~]# systemctl restart httpd
Conf https (attention, vous devez mettre un certificat ssl en place)
Mettez en place le VirtualHost apache pour le site (si vous souhaitez ne tournez qu'en https). Encore une fois, il ne s’agit ici que d’un exemple que vous pouvez customiser (avec le user root, adaptez si besoin).
## Generate SSL Certificate : To generate a new self-signed certificate with 365 days expiry, run:
[root@vmsqwarebox ~]# openssl req -newkey rsa:2048 -nodes -keyout /etc/pki/tls/private/dbsqware_selfsigned.key -x509 -days 3650 -out /etc/pki/tls/certs/dbsqware_selfsigned.crt
Generating a RSA private key
........................................................................................................................................+++++
...............................+++++
writing new private key to '/etc/pki/tls/private/dbsqware_selfsigned.key'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
# Country Name (2 letter code) [XX]:Fr
# State or Province Name (full name) []:France
# Locality Name (eg, city) [Default City]:Gardanne
# Organization Name (eg, company) [Default Company Ltd]:dbSQWare - self-signed
# Organizational Unit Name (eg, section) []:SQWareWeb SSL
# Common Name (eg, your name or your server's hostname) []:webdba
# Email Address []:myadress@mydomain
[root@vmsqwarebox ~]# ls -l /etc/pki/tls/private/dbsqware_selfsigned.key /etc/pki/tls/certs/dbsqware_selfsigned.crt
-rw-r--r--. 1 root root 1497 Apr 19 15:32 /etc/pki/tls/certs/dbsqware_selfsigned.crt
-rw-------. 1 root root 1704 Apr 19 15:31 /etc/pki/tls/private/dbsqware_selfsigned.key
## Installation de mod_ssl
[root@vmsqwarebox ~]# dnf install mod_ssl
## activation du module 'mod_ssl' module
[root@vmsqwarebox ~]# systemctl restart httpd
## verification
[root@vmsqwarebox ~]# httpd -t -D DUMP_MODULES| grep ssl
ssl_module (shared)
## To Redirect All HTTP Traffic To HTTPS
[root@vmsqwarebox ~]# cat /etc/httpd/conf.d/redirect_http.conf
<VirtualHost _default_:80>
ServerName vmsqwarebox
ServerAlias vmsqwarebox vmsqwareboxRocky9 webdba
Redirect permanent / https://webdba/
</VirtualHost>
## vhost https
[root@vmsqwarebox ~]# vi /etc/httpd/conf.d/httpd-vhosts.conf
<Directory "/home/dbsqware/dbSQWare/SQWareWeb">
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>
<Directory "/data/oracle/awr">
Options Indexes FollowSymLinks
AllowOverride All
Require all granted
</Directory>
<VirtualHost *:443>
DocumentRoot /home/dbsqware/dbSQWare/SQWareWeb
ServerName vmsqwarebox
ServerAlias vmsqwarebox VMsqwareboxRockyLinux9 webdba
SSLEngine on
SSLCertificateFile /etc/pki/tls/certs/dbsqware_selfsigned.crt
SSLCertificateKeyFile /etc/pki/tls/private/dbsqware_selfsigned.key
</VirtualHost>
[root@vmsqwarebox ~]# systemctl reload httpd
Personnalisation
Paramétrez l’accès au référentiel (SQWareRepository) pour SQWareWeb dans le fichier suivant :
vi /home/dbsqware/dbSQWare/SQWareWeb/custom/sqwareweb.ini [db] MysqlHost="localhost" MysqlUser="dbsqware_user" MysqlPass="PassMys"
Remplacez le fichier suivant par votre logo :
/home/dbsqware/dbSQWare/SQWareWeb/custom/images/client.png
Vous devez maintenant pouvoir afficher la page web suivante https://vmsqwarebox (avec votre logo) :
Un compte d’admin est créé par défaut à l’installation :
User = default_admin Pass = YBHim9Ty_PSOvahtSxAU_5SX3QSoNFaC
Fichier:SQWareWeb.png
Cet affichage vous permet de valider si le socle est paramétré correctement pour le web.
En cas d’erreur, vous pouvez jouer avec les paramètres de « debug » du sqwareweb.ini afin de tenter de trouver le problème :
;debug=true ;debug_before_connectdb=true ;debug_connectdb_error=true
Paramétrages spécifiques
Pour Oracle
Si vous mettez en place la génération des rapports AWR avec SQWareProduction (attention aux licences), vous devez créer un lien symbolique à la racine du site vers le répertoire contenant les rapports statiques (paramétré dans SQWareProduction pour Oracle).
$ cd /home/dbsqware/dbSQWare/SQWareWeb $ ln -s /data/oracle/awr sqw_oracle_awr $ ls -l sqw_oracle_awr lrwxrwxrwx 1 dbsqware dba 16 jun 12 19:01 sqw_oracle_awr -> /data/oracle/awr
Pour MySql
Si vous mettez en place la rotation des fichiers SlowQuery avec SQWareProduction (uniquement disponible sous unix), vous devez créer un lien symbolique à la racine du site vers le répertoire contenant les rapports statiques (paramétré dans SQWareProduction pour MySql).
$ cd /home/dbsqware/dbSQWare/SQWareWeb $ ln -s /data/mysql/SlowQuery sqw_mysql_slow $ ls -l sqw_mysql_slow lrwxrwxrwx 1 dbsqware dba 21 jun 12 19:05 sqw_mysql_slow -> /data/mysql/SlowQuery
Droits d’accès à SQWareWeb
Pour accéder à la console SQWareWeb, vous devez paramétrer les droits d’accès.
Il existe trois niveaux d’accès :
- En lecture (accès uniquement aux indicateurs)
- En admin (accès avec droits de modification du paramétrage SQWareRepository)
- En lecture/admin (accès en lecture seule au paramétrage SQWareRepository)
Vous avez deux façons de gérer les droits, par l’interface d’admin de SQWareWeb avec un user possédant le droit admin ou directement par ordres sql en database.
Par la console d’admin
Allez dans la console d’admin, soit directement :
https://vmsqwarebox/admin/all/access/adminAccessLogins_general.html
Soit en cliquant sur l'icône « admin » :
Fichier:AdminConsole.png
Puis « Access » et « Login » …
Directement en sql
Suppression du user d’admin par défaut :
delete from tsqw_AdminLoginsPrivs where id_usr_grantee=1; delete from tsqw_AdminLogins where username='default_admin';
Création d’un user de visu :
insert into tsqw_AdminLogins (username,lastname,firstname,passwd, cre_date, upd_date, pwd_date, dbaname, authentication_type, is_forweb)
values ('viewer', 'Viewer', 'Viewer', SHA1('View!Sec@'), now(), now(), now(), 'init', 'internal','1');
Création d’un user d’admin :
insert into tsqw_AdminLogins (username,lastname,firstname,passwd, cre_date, upd_date, pwd_date, dbaname, authentication_type, is_forweb)
values ('myadmin', 'Adm', 'My', SHA1('P@dm!n'), now(), now(), now(), 'init', 'internal','1')
;
insert into tsqw_AdminLoginsPrivs (id_usr_grantee,id_grp_granted,dbaname,comments,upd_date)
select id_usr_grantee, id_grp_granted, dbaname, comments, upd_date
from
(select id_usr as id_usr_grantee from tsqw_AdminLogins where username='myadmin') gadm,
(select id_grp as id_grp_granted from tsqw_AdminGroups where groupname='DBA') gdba,
(select 'myadmin' as dbaname, 'Init dbSQWare' as comments, now() as upd_date from dual) com
;
Crontab du user dbsqware unix pour SQWareCentral
Voir le contenu du fichier suivant :
/home/dbsqware/dbSQWare/SQWareCentral/etc/CrontabRef.cfg
Voici la cron classique que l’on met en place sur le user dbsqware :
########################### ## mm(0-59) hh(0-23) dd(1-31) MM(0-12) DAY(0-sunday, 1-monday, ...) command ## Generic cron for SQWareCentral (dbSQWare) ############ # Generate all ref files (RefFileInstances,RefFileDb,RefFileCMDB,...) 00 02,08 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_GenRef_AllFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_GenRef_AllFiles.log' # Check indicators 00 07 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_CheckIndicators.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_CheckIndicators.log' # Gather indicators 00 03 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_GatherIndicators.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_GatherIndicators.log' # Retry gather indicators if not exists 00 09,12,17 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_GatherIndicators.ksh -Repind -NoMail 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_GatherIndicators_Repind.log' ############ # Retrieve async files from a dir (configure sqwctl_LoopRetrieveAsynchronousFilesGlobal.cfg in cetc) 20 * * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopRetrieveAsynchronousFilesGlobal.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopRetrieveAsynchronousFilesGlobal.log' # Retrieve async files from all instances 15 * * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopRetrieveAsynchronousFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopRetrieveAsynchronousFiles.log' 45 07,08,09,10,11,12,13,14,15,16,17,18 * * * ksh -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopRetrieveAsynchronousFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopRetrieveAsynchronousFiles.log' # Exec async files from all instances 30 * * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopExecAsynchronousFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopExecAsynchronousFiles.log' 10,50 07,08,09,10,11,12,13,14,15,16,17,18 * * * ksh -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopExecAsynchronousFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopExecAsynchronousFiles.log' ######################################################################################################################## ### Gather Structures (dbSQWare) ############ ## Structures Oracle #00 05 * * * bash -c '. ~/.profile 2>&1 >/dev/null;export gvsqw_JobName="Structure";$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T oracle -A GatherStructureOn -F GenLstInstanceOn_SpecJob -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/GatherStructureOracle.log' ## Structures MySQL #15 05 * * * bash -c '. ~/.profile 2>&1 >/dev/null;export gvsqw_JobName="Structure";$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T mysql -A GatherStructureOn -F GenLstInstanceOn_SpecJob -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/GatherStructureMySQL.log' ## Structures PostgreSQL #30 05 * * * bash -c '. ~/.profile 2>&1 >/dev/null;export gvsqw_JobName="Structure";$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T postgres -A GatherStructureOn -F GenLstInstanceOn_SpecJob -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/GatherStructurePostgreSQL.log' ## Structures MsSql #45 05 * * * bash -c '. ~/.profile 2>&1 >/dev/null;export gvsqw_JobName="Structure";$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T mssql -A GatherStructureOn -F GenLstInstanceOn_SpecJob -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/GatherStructureMsSql.log' ## Structures Sybase #00 06 * * * bash -c '. ~/.profile 2>&1 >/dev/null;export gvsqw_JobName="Structure";$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T sybase -A GatherStructureOn -F GenLstInstanceOn_SpecJob -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/GatherStructureSybase.log' ######################################################################################################################## ### Specific cron for Oracle (dbSQWare) ############ ## Check started instances Oracle #00 06 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T oracle -A sqwora_StartedInstancesOn.ksh -F GenLstUniqueOn -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/StartedInstancesOracle.log' ## Generate Awr reports in distant mode #10 09 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T oracle -A sqwora_GenerateAwrDist.ksh -F sqwora_GenLstInstanceAwrAll.sql -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/ind_ora_awr_dist.log' ## Monitor Awr reports in distant mode #05 * * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T oracle -A sqwora_MonitorHourlyAwrDist.ksh -F sqwora_GenLstInstanceAwrAll.sql -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/ind_ora_awr_monitor_dist.log' ## Generate Statspack reports in distant mode #10 09 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T oracle -A sqwora_GenerateStatspackOld.ksh -F Liste9iStatspack.sql -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/ind_Statspack_awr_old.log' ## Monitor Statspack reports in distant mode #05 * * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T oracle -A sqwora_MonitorStatspackOld.ksh -F Liste9iStatspack.sql -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/ind_ora_Statspack_monitor_old.log' ######################################################################################################################## ### Specific cron for MySql (dbSQWare) ############ ## Rotate slow query #00 07 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T mysql -A sqwmys_SlowQuery.ksh -F GenLstInstanceOn -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/mysql_slowquery.log' ## Async Rotate slow query #00 07 * * * bash -c '. ~/.profile 2>&1 >/dev/null;export gvsqw_JobName="AsyncRotateLog";$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T mysql -A sqwmys_SlowQueryAsync.ksh -F GenLstInstanceOn_SpecJob -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/mysql_Async_slowquery.log' ## Rotate error log #00 08 * * 6 bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T mysql -A sqwmys_PurgeAlert.ksh -F GenLstInstanceOn -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/mysql_errorlog.log' ######################################################################################################################## ### Specific cron for MsSql (dbSQWare) ############ ## Rotate errorlog #01 23 * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_TrtAuto.ksh -T mssql -A sqwmsq_RotateLog.ksh -F GenLstInstanceOn -Exec 2>&1 >$HOME/admin/SQWareCentral/logs/MssqlRotateLog.log' ######################################################################################################################## ### Specific cron for update GLPI (dbSQWare) ############ ## Update GLPI MYS_GLPI_PRD #10 10 * * * bash -c '. ~/.profile 2>&1 >/dev/null;/home/dbsqware/dbSQWare/SQWareProduction/mysql/tools/sqwmys_UpdateGLPI.ksh -I MYS_GLPI_PRD -D glpi -F /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/RefFileCMDB.csv -Dist 2>&1 >$HOME/admin/SQWareCentral/logs/MajGLPI_PRD_PRD'
