General:BaseInstallation

Generalties

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: <syntaxhighlight lang="sh" line> 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 : <syntaxhighlight lang="sh" line>

  2 vCPU
  4 GB RAM
  2 GB swap
100 GB disk

Verification and 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 : <syntaxhighlight lang="sh" line> [root@vmsqwarebox ~]# cat /etc/redhat-release Rocky Linux release 9.3 (Blue Onyx)

Disk configuration : <syntaxhighlight lang="sh" line> [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: <syntaxhighlight lang="sh" line> [root@vmsqwarebox ~]# dnf update

Disabling the firewall and SELinux (optional but help for installation) : <syntaxhighlight lang="sh" line>

1. Disabling the firewall

systemctl disable firewalld systemctl stop firewalld

1. Deactivate SELINUX
2. Check

/usr/sbin/getenforce Enforcing

vi /etc/sysconfig/selinux SELINUX=enforcing => SELINUX=disabled (reboot) shutdown -r now

1. 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): <syntaxhighlight lang="sh" line>

1. If needed you can install or change the locale

dnf install langpacks-en glibc-all-langpacks -y

1. 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

1. 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

1. 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

1. 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): <syntaxhighlight lang="sh" line> dnf install -y chrony

1. Check config of chronyd (old ntpd)

cat /etc/chrony.conf

1. Here we use a close server pool, ex:

pool 0.europe.pool.ntp.org iburst

1. Start and activation for Chrony

systemctl enable --now chronyd

1. If Firewalld is running, allow NTP service. NTP uses [123/UDP].

firewall-cmd --add-service=ntp success firewall-cmd --runtime-to-permanent success

1. Restart the service

systemctl restart chronyd

1. 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 ! <syntaxhighlight lang="sh" line> 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

1. 1. 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

1. 1. 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 : <syntaxhighlight lang="sh" line> $ 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 . <syntaxhighlight lang="sh" line> $ 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 : <syntaxhighlight lang="sh" line> $ 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) : <syntaxhighlight lang="sh" line> [root@vmsqwarebox /]# rpm -q libXpm-devel package libXpm-devel is not installed

If you need to install a missing RPM : <syntaxhighlight lang="sh" line> [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) : <syntaxhighlight lang="sh" line> [root@vmsqwarebox /]# dnf install libXpm-devel ksh binutils gcc gcc-c++ glibc glibc-common libstdc++ libstdc++-devel make sysstat

1. 1. 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) : <syntaxhighlight lang="sh" line> 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 : <syntaxhighlight lang="sh" line> passwd dbsqware passwd oracle passwd sybase passwd mssql passwd mysql passwd postgres

Outpout example : <syntaxhighlight lang="sh" line> [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 : <syntaxhighlight lang="sh" line> cd /home chmod 750 dbsqware oracle sybase mssql mysql postgres

Check home directories  : <syntaxhighlight lang="sh" line> [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' : <syntaxhighlight lang="sh" line>

1. 1. Add sudo

cp -p /etc/sudoers /etc/sudoers.back chmod 700 /etc/sudoers vi /etc/sudoers

root@vmsqwarebox:/root # grep wheel /etc/sudoers

1. 1. Allows people in group wheel to run all commands
2.  %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) : <syntaxhighlight lang="sh" line> 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) : <syntaxhighlight lang="sh" line> 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

1. Specific trees for AWR and, or Statspack

mkdir -p /data/oracle/awr chown oracle:dba /data/oracle/awr

1. 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) : <syntaxhighlight lang="sh" line> 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 : <syntaxhighlight lang="sh" line> [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

SSH Keys

Preparing ssh keys for dbsqware.
With the user root : <syntaxhighlight lang="sh" line> 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 : <syntaxhighlight lang="sh" line> [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 : <syntaxhighlight lang="sh" line> 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) : <syntaxhighlight lang="sh" line> [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 : <syntaxhighlight lang="sh" line> cat <<EOFCAT >/tmp/.vimrc syntax off set mouse-=a set noai EOFCAT cat <<EOFCAT >/tmp/.bash_profile

1. .bash_profile

1. Get the aliases and functions

if [ -f ~/.bashrc ]; then . ~/.bashrc fi

1. 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

1. Source global definitions
2. if [ -f /etc/bashrc ] && [ \$(ps -p \$\$|grep -c 'bash') -ne 0 ] ; then

# . /etc/bashrc

1. 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 : <syntaxhighlight lang="sh" line> [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 : <syntaxhighlight lang="sh" line> 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

1. 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 : <syntaxhighlight lang="sh" line> [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/ ...
Verification all modules are installed : <syntaxhighlight lang="sh" line> $ 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:
<syntaxhighlight lang="sh" line> 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

1. change for the right email address !

cat <<EOFCAT >/home/dbsqware/dbSQWare/SQWareProduction/generic/etc_cust/sqwgen_GlobalVar.cfg

1. Put NoNail in gvsqw_GlobalMail if you don't want to send mail on error

export gvsqw_GlobalMail='dba@mydomane.com'

1. 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.
<syntaxhighlight lang="sh" line> dbsqware@vmsqwarebox:/home/dbsqware $ vi .bash_profile

1. 1. 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:
<syntaxhighlight lang="sh" line> dbsqware@vmsqwarebox:/home/dbsqware $ . ~/.bash_profile

    _ _    ____   _____        __
 __| | |__/ ___| / _ \ \      / /_ _ _ __ ___
/ _` | '_ \___ \| | | \ \ /\ / / _` | '__/ _ \

| (_| | |_) |__) | |_| |\ V V / (_| | | | __/

\__,_|_.__/____/ \__\_\ \_/\_/ \__,_|_|  \___|

2. SQWareCentral module of dbSQWare
3. Copyright (C) 2010-2024, dbSQWare (www.dbsqware.com)
4. dbSQWare is distributed in the hope that it will be useful,
5. but WITHOUT ANY WARRANTY; without even the implied warranty of
6. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
7. CGU/CGA for more details.
8. You should have received a copy of the CGU/CGA
9. along with this program. If not, see <https://www.dbsqware.com/licenses/>.
10. For information : contact@dbsqware.com or www.dbsqware.com
11. SqwVersion=2024.03

1. For help type 'show <-h|-help|--help>'

Installation and 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: <syntaxhighlight lang="sh" line> ~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): <syntaxhighlight lang="sh" line> [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): <syntaxhighlight lang="sh" line> 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) : <syntaxhighlight lang="sh" line> 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 : <syntaxhighlight lang="sh" line> dbsqware@vmsqwarebox:/home/dbsqware $ dbspatch

    _ _    ____   _____        __
 __| | |__/ ___| / _ \ \      / /_ _ _ __ ___
/ _` | '_ \___ \| | | \ \ /\ / / _` | '__/ _ \

| (_| | |_) |__) | |_| |\ V V / (_| | | | __/

\__,_|_.__/____/ \__\_\ \_/\_/ \__,_|_|  \___|

2. SQWareCentral module of dbSQWare
3. Copyright (C) 2010-2024, dbSQWare (www.dbsqware.com)
4. dbSQWare is distributed in the hope that it will be useful,
5. but WITHOUT ANY WARRANTY; without even the implied warranty of
6. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
7. CGU/CGA for more details.
8. You should have received a copy of the CGU/CGA
9. along with this program. If not, see <http://www.dbsqware.com/licenses/>.
10. For information : contact@dbsqware.com or www.dbsqware.com
11. SqwVersion=2024.08

1. 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.

<syntaxhighlight lang="sh" line> ... ... ... 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 of SQWareWeb

This chapter covers the installation and configuration of SQWareWeb. We assume that Apache and PHP 8.2 are installed and operational to connect to MariaDB/MySQL (PHP >= 7.4 and 8.x should also work). To deploy Apache and PHP 8.2, you can refer to the appendix "Quick Installation of Apache and PHP 8.2". Please note that this is only an example; refer to the official tool websites for more information: https://httpd.apache.org/ and https://www.php.net/.
This chapter assumes that we are logged in under UNIX with the username dbsqware. We'll see an installation of SQWareWeb on the same machine as SQWareCentral, but this isn't mandatory.

Setup

When we deployed the dbSQWare sources, we saw that we had the /home/dbsqware/dbSQWare/SQWareWeb directory tree. This contains the sources for the site we're going to set up.

Verifying the tree structures: <syntaxhighlight lang="sh" line> $ 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

http configuration

Set up the Apache VirtualHost for the site (if you're only running in http). Again, this is just an example that you can customize (with the root user, adapt if necessary).<syntaxhighlight lang="sh" line> $ 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

https configuration (be careful, you must put an SSL certificate in place)

Set up the Apache VirtualHost for the site (if you want to run only in https). Again, this is just an example that you can customize (with the root user, adapt if necessary).<syntaxhighlight lang="sh" line>

1. 1. 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.

---

1. Country Name (2 letter code) [XX]:Fr
2. State or Province Name (full name) []:France
3. Locality Name (eg, city) [Default City]:Gardanne
4. Organization Name (eg, company) [Default Company Ltd]:dbSQWare - self-signed
5. Organizational Unit Name (eg, section) []:SQWareWeb SSL
6. Common Name (eg, your name or your server's hostname) []:webdba
7. 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

1. 1. Installation de mod_ssl

[root@vmsqwarebox ~]# dnf install mod_ssl

1. 1. activation du module 'mod_ssl' module

[root@vmsqwarebox ~]# systemctl restart httpd

1. 1. verification

[root@vmsqwarebox ~]# httpd -t -D DUMP_MODULES| grep ssl

ssl_module (shared)

1. 1. 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>

1. 1. 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

Personnalization

Set up repository access (SQWareRepository) for SQWareWeb in the following file: <syntaxhighlight lang="sh" line> vi /home/dbsqware/dbSQWare/SQWareWeb/custom/sqwareweb.ini [db] MysqlHost="localhost" MysqlUser="dbsqware_user" MysqlPass="PassMys"

Change this file with your logo : <syntaxhighlight lang="sh" line> /home/dbsqware/dbSQWare/SQWareWeb/custom/images/client.png

You should now be able to view the following web page https://vmsqwarebox (with your logo) :

An admin account is created by default upon installation : <syntaxhighlight lang="sh" line> User = default_admin Pass = YBHim9Ty_PSOvahtSxAU_5SX3QSoNFaC

This display allows you to validate whether the base is configured correctly for the web.

If you encounter an error, you can play with the debug settings in sqwareweb.ini to try to find the problem. : <syntaxhighlight lang="sh" line>

debug=true

debug_before_connectdb=true

debug_connectdb_error=true

Specific settings

For Oracle

If you set up AWR report generation with SQWareProduction (be careful with licenses), you must create a symbolic link at the root of the site to the directory containing the static reports (configured in SQWareProduction for Oracle). <syntaxhighlight lang="sh" line> $ 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

For MySql

If you set up SlowQuery file rotation with SQWareProduction (only available on Unix), you must create a symbolic link at the root of the site to the directory containing the static reports (configured in SQWareProduction for MySql). <syntaxhighlight lang="sh" line> $ 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

SQWareWeb Access Rights

To access the SQWareWeb console, you must configure access rights. There are three levels of access :

• Read (only access to the indicators)
• Admin (access with rights to modify the SQWareRepository settings)
• Admin-Read (access with rights to read-only the SQWareRepository settings)
  

You have two ways to manage rights, through the SQWareWeb admin interface with a user with admin rights or directly through SQL commands in the database.

Through admin console

Go to the admin console, either directly :

<syntaxhighlight lang="sh" line>https://vmsqwarebox/admin/all/access/adminAccessLogins_general.html

Either by clicking on the "admin" icon:



Then "Access" and "Login" …

Through SQL commands

Delete the default admin user : <syntaxhighlight lang="sh" line> delete from tsqw_AdminLoginsPrivs where id_usr_grantee=1; delete from tsqw_AdminLogins where username='default_admin';

Creating a Viewer User (customize your password) : <syntaxhighlight lang="sh" line> 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');

Creating an Admin User (customize your password) : <syntaxhighlight lang="sh" line> 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 for the dbsqware (unix user) on SQWareCentral

See the contents of the following file :
/home/dbsqware/dbSQWare/SQWareCentral/etc/CrontabRef.cfg

Here is the classic cron that we set up on the dbsqware user : <syntaxhighlight lang="sh" line>

1. 2. mm(0-59) hh(0-23) dd(1-31) MM(0-12) DAY(0-sunday, 1-monday, ...) command
   3. Generic cron for SQWareCentral (dbSQWare)
2. 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'

1. 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'

1. 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'

1. 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'

2. 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'

1. 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'

1. 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'

1. 1. 2. Gather Structures (dbSQWare)
   2. Structures Oracle
2. 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'
   1. Structures MySQL
3. 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'
   1. Structures PostgreSQL
4. 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'
   1. Structures MsSql
5. 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'
   1. Structures Sybase
6. 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'
   1. 2. Specific cron for Oracle (dbSQWare)
   2. Check started instances Oracle
7. 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'
   1. Generate Awr reports in distant mode
8. 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'
   1. Monitor Awr reports in distant mode
9. 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'
   1. Generate Statspack reports in distant mode
10. 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'
    1. Monitor Statspack reports in distant mode
11. 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'
    1. 2. Specific cron for MySql (dbSQWare)
    2. Rotate slow query
12. 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'
    1. Async Rotate slow query
13. 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'
    1. Rotate error log
14. 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'
    1. 2. Specific cron for MsSql (dbSQWare)
    2. Rotate errorlog
15. 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'
    1. 2. Specific cron for update GLPI (dbSQWare)
    2. Update GLPI MYS_GLPI_PRD
16. 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'