Difference between revisions of "General:BaseInstallation"

Latest revision as of 09:46, 28 April 2025

Generalties

Warning:
Before anything, don't go further if you don't have read the first section "dbSQWare Concepts " !.

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:

1 SQWareProduction	=> operating shell scripts (often local on the machine hosting the DBMS to be managed)
2 SQWareRepository	=> reference and storage of indicators (database MySQL or MariaDB recommended)
3 SQWareCentral		=> centralization of sources and launch of commands
4 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 :

1    2 vCPU
2    4 GB RAM
3    2 GB swap
4  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 :

1 [root@vmsqwarebox ~]# cat /etc/redhat-release
2 Rocky Linux release 9.3 (Blue Onyx)

Disk configuration :

 1 [root@vmsqwarebox ~]# vgs
 2   VG      #PV #LV #SN Attr   VSize   VFree
 3   data_vg   1   5   0 wz--n- <60.91g  20.83g
 4   root_vg   1   4   0 wz--n-  37.50g <20.48g
 5 
 6 [root@vmsqwarebox ~]# df -h
 7 Filesystem                   Size  Used Avail Use% Mounted on
 8 devtmpfs                     1.9G     0  1.9G   0% /dev
 9 tmpfs                        1.9G     0  1.9G   0% /dev/shm
10 tmpfs                        1.9G  8.7M  1.9G   1% /run
11 tmpfs                        1.9G     0  1.9G   0% /sys/fs/cgroup
12 /dev/mapper/root_vg-root      10G  1.7G  8.4G  17% /
13 /dev/sda2                   1014M  245M  770M  25% /boot
14 /dev/sda1                    599M  5.8M  594M   1% /boot/efi
15 /dev/mapper/root_vg-var      5.0G  379M  4.7G   8% /var
16 /dev/mapper/data_vg-applis   5.0G   69M  5.0G   2% /applis
17 /dev/mapper/data_vg-data      15G  140M   15G   1% /data
18 /dev/mapper/data_vg-home      10G  105M  9.9G   2% /home
19 /dev/mapper/data_vg-backups   10G  105M  9.9G   2% /backups
20 tmpfs                        374M     0  374M   0% /run/user/0
21 
22 [root@vmsqwarebox ~]# free
23               total        used        free      shared  buff/cache   available
24 Mem:        3825740      314080     1493080        8868     2018580     3237608
25 Swap:       2097148           0     2097148

Update the OS to the latest patch version:

1 [root@vmsqwarebox ~]# dnf update

Disabling the firewall and SELinux (optional but help for installation) :

 1 # Disabling the firewall
 2 systemctl disable firewalld
 3 systemctl stop firewalld
 4 
 5 # Deactivate SELINUX
 6 #Check
 7 /usr/sbin/getenforce
 8 Enforcing
 9 
10 vi /etc/sysconfig/selinux
11 SELINUX=enforcing => SELINUX=disabled (reboot)
12 shutdown -r now
13 
14 #Check again
15 /usr/sbin/getenforce
16 Disabled

Prefer a server set to English, it’s easier for error messages (instead of the classic fr_FR.UTF-8):

 1 #If needed you can install or change the locale
 2 dnf install langpacks-en glibc-all-langpacks -y
 3 
 4 #A server set to English, it’s easier for read error messages !
 5 localectl
 6 System Locale: LANG=fr_FR.UTF-8
 7        VC Keymap: fr-oss
 8       X11 Layout: fr
 9      X11 Variant: oss
10 
11 localectl set-locale LANG=en_US.UTF-8
12 
13 localectl
14 System Locale: LANG=en_US.UTF-8
15        VC Keymap: fr-oss
16       X11 Layout: fr
17      X11 Variant: oss
18 
19 #Keyboard (temp)
20 loadkeys en
21 
22 localectl status
23 localectl list-keymaps | grep en
24 
25 localectl set-keymap en
26 localectl set-x11-keymap en
27 
28 localectl status
29    System Locale: LANG=en_US.UTF-8
30        VC Keymap: en
31       X11 Layout: en
32 
33 #You can check if you have the right Time Zone
34 timedatectl
35                Local time: Wed 2024-04-24 07:19:51 EDT
36            Universal time: Wed 2024-04-24 11:19:51 UTC
37                  RTC time: Wed 2024-04-24 11:19:51
38                 Time zone: America/New_York (EDT, -0400)
39 System clock synchronized: yes
40               NTP service: active
41           RTC in local TZ: no
42 
43 #If you need to change it
44 rm -rf /etc/localtime
45 timedatectl set-timezone Europe/Paris
46 
47 timedatectl
48                Local time: Wed 2024-04-24 13:20:44 CEST
49            Universal time: Wed 2024-04-24 11:20:44 UTC
50                  RTC time: Wed 2024-04-24 11:20:44
51                 Time zone: Europe/Paris (CEST, +0200)
52 System clock synchronized: yes
53               NTP service: active
54           RTC in local TZ: no

NTP installation and activation (we use Chrony):

 1 dnf install -y chrony
 2 
 3 #Check config of chronyd (old ntpd)
 4 cat /etc/chrony.conf
 5 
 6 #Here we use a close server pool, ex:
 7 pool 0.europe.pool.ntp.org iburst
 8 
 9 #Start and activation for Chrony
10 systemctl enable --now chronyd
11 
12 #If Firewalld is running, allow NTP service. NTP uses [123/UDP].
13 firewall-cmd --add-service=ntp
14 success
15 firewall-cmd --runtime-to-permanent
16 success
17 
18 #Restart the service
19 systemctl restart chronyd
20 
21 #Check the service state
22 systemctl is-enabled chronyd
23 enabled
24 
25 systemctl status chronyd
26   chronyd.service - NTP client/server
27    Loaded: loaded (/usr/lib/systemd/system/chronyd.service; enabled; vendor preset: enabled)
28 ...

RPM installation that will be needed later !

 1 Many EPEL packages require the CodeReady Builder (CRB) repository.
 2 It is recommended that you run /usr/bin/crb enable to enable the CRB repository.
 3 
 4 dnf install -y open-vm-tools
 5 dnf install -y nmap bind-utils ksh rsync openssh-server openssl-devel openssl
 6 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
 7 
 8 ## for the Oracle 19c client (but not only that!)
 9 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
10 
11 ## for the Python compilation (for SQWarePredict)
12 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 :

1 $ rpm -q gcc
2 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 .

1 $ echo '%_query_all_fmt %%{name}-%%{version}-%%{release} (%%{arch})' >>~/.rpmmacros
2 $ rpm -q gcc
3 gcc-11.4.1-2.1.el9 (x86_64)

Or directly in CLI :

1 $ rpm -q gcc --queryformat '%{name}-%{release}  %{version}  (%{arch})\n'
2 gcc-2.1.el9  11.4.1  (x86_64)

If a RPM is missing you can obtaint this (or something close) :

1 [root@vmsqwarebox /]# rpm -q libXpm-devel
2 package libXpm-devel is not installed

If you need to install a missing RPM :

1 [root@vmsqwarebox /]# rpm -ivh libXpm-devel-3.5.5-3.x86_64.rpm
2 warning: libXpm-devel-3.5.5-3.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 37017186
3 Preparing...                ########################################### [100%]
4    1:libXpm-devel           ########################################### [100%]

The simplest, direct installation with yum (you need a available repo) :

1 [root@vmsqwarebox /]# dnf install libXpm-devel ksh binutils gcc gcc-c++ glibc glibc-common libstdc++ libstdc++-devel make sysstat
2 
3 ##Mail configuration and activation (change the relayhost at least)
4 vi /etc/postfix/main.cf
5 systemctl start postfix
6 systemctl enable postfix
7 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) :

1 groupadd dba
2 useradd -g dba -s /bin/bash -d /home/dbsqware dbsqware
3 useradd -g dba -s /bin/bash -d /home/oracle oracle
4 useradd -g dba -s /bin/bash -d /home/sybase sybase
5 useradd -g dba -s /bin/bash -d /home/mssql mssql
6 useradd -g dba -s /bin/bash -d /home/mysql mysql
7 useradd -g dba -s /bin/bash -d /home/postgres postgres

Set password to users :

1 passwd dbsqware
2 passwd oracle
3 passwd sybase
4 passwd mssql
5 passwd mysql
6 passwd postgres

Outpout example :

 1 [root@vmsqwarebox /]# passwd dbsqware
 2 Changing password for user dbsqware.
 3 New UNIX password:
 4 Retype new UNIX password:
 5 passwd: all authentication tokens updated successfully.
 6 [root@vmsqwarebox /]# passwd oracle
 7 Changing password for user oracle.
 8 New UNIX password:
 9 Retype new UNIX password:
10 passwd: all authentication tokens updated successfully.
11 ...

Change their home directory rights :

1 cd /home
2 chmod 750 dbsqware oracle sybase mssql mysql postgres

Check home directories :

1 [root@vmsqwarebox /]# ls -l /home/
2 total 32
3 drwxr-x--- 3 dbsqware dba 4096 mai 13 21:54 dbsqware
4 drwxr-x--- 3 mssql    dba 4096 mai 13 21:54 mssql
5 drwxr-x--- 3 mysql    dba 4096 mai 13 21:54 mysql
6 drwxr-x--- 3 oracle   dba 4096 mai 13 21:54 oracle
7 drwxr-x--- 3 postgres dba 4096 mai 13 21:54 postgres
8 drwxr-x--- 3 sybase   dba 4096 mai 13 21:54 sybase

At least for the installation set the dbsqware user with 'sudo ALL' :

 1 ## Add sudo
 2 cp -p /etc/sudoers /etc/sudoers.back
 3 chmod 700 /etc/sudoers
 4 vi /etc/sudoers
 5 
 6 root@vmsqwarebox:/root # grep wheel /etc/sudoers
 7 ## Allows people in group wheel to run all commands
 8 # %wheel        ALL=(ALL)       ALL
 9 %wheel  ALL=(ALL)       NOPASSWD: ALL
10 
11 chmod 440 /etc/sudoers
12 
13 id dbsqware
14 uid=1000(dbsqware) gid=1000(dba) groups=1000(dba)
15 
16 usermod -a -G wheel dbsqware
17 id dbsqware
18 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) :

 1 mkdir -p /applis/oracle
 2 chown oracle:dba /applis/oracle
 3 mkdir -p /applis/sybase
 4 chown sybase:dba /applis/sybase
 5 mkdir -p /applis/mssql
 6 chown mssql:dba /applis/mssql
 7 mkdir -p /applis/mysql
 8 chown mysql:dba /applis/mysql
 9 mkdir -p /applis/dbsqware
10 chown dbsqware:dba /applis/dbsqware
11 mkdir -p /applis/postgres
12 chown postgres:dba /applis/postgres

Preparing the /data trees for data storage (if necessary) :

 1 mkdir -p /data/oracle
 2 chown oracle:dba /data/oracle
 3 mkdir -p /data/sybase
 4 chown sybase:dba /data/sybase
 5 mkdir -p /data/mssql
 6 chown mssql:dba /data/mssql
 7 mkdir -p /data/mysql
 8 chown mysql:dba /data/mysql
 9 mkdir -p /data/dbsqware
10 chown dbsqware:dba /data/dbsqware
11 mkdir -p /data/postgres
12 chown postgres:dba /data/postgres
13 mkdir -p /data/dbsqware/admin/AsynchronousFiles /data/dbsqware/admin/AsynchronousFilesTmp /data/dbsqware/admin/AsynchronousFilesArch
14 chown -R dbsqware:dba /data/dbsqware/admin
15 # Specific trees for AWR and, or Statspack
16 mkdir -p /data/oracle/awr
17 chown oracle:dba /data/oracle/awr
18 # Specific trees for slow query files
19 mkdir -p /data/mysql/SlowQuery
20 chown mysql:dba /data/mysql/SlowQuery

Preparing the /backups trees for backups storage (if necessary) :

 1 mkdir -p /backups/oracle
 2 chown oracle:dba /backups/oracle
 3 mkdir -p /backups/sybase
 4 chown sybase:dba /backups/sybase
 5 mkdir -p /backups/mssql
 6 chown mssql:dba /backups/mssql
 7 mkdir -p /backups/mysql
 8 chown mysql:dba /backups/mysql
 9 mkdir -p /backups/dbsqware
10 chown dbsqware:dba /backups/dbsqware
11 mkdir -p /backups/postgres
12 chown postgres:dba /backups/postgres

Checking File Trees :

 1 [root@vmsqwarebox /]# ls -l /applis
 2 total 40
 3 drwxr-xr-x 2 dbsqware dba 4096 mai 14 04:06 dbsqware
 4 drwxr-xr-x 2 mssql    dba 4096 mai 14 04:06 mssql
 5 drwxr-xr-x 2 mysql    dba 4096 mai 14 04:06 mysql
 6 drwxr-xr-x 2 oracle   dba 4096 mai 14 04:06 oracle
 7 drwxr-xr-x 2 sybase   dba 4096 mai 14 04:06 sybase
 8 drwxr-xr-x 2 postgres dba 4096 mai 14 04:06 postgres
 9 [root@vmsqwarebox /]# ls -l /data
10 total 40
11 drwxr-xr-x 2 dbsqware dba 4096 mai 14 04:06 dbsqware
12 drwxr-xr-x 2 mssql    dba 4096 mai 14 04:06 mssql
13 drwxr-xr-x 2 mysql    dba 4096 mai 14 04:06 mysql
14 drwxr-xr-x 2 oracle   dba 4096 mai 14 04:06 oracle
15 drwxr-xr-x 2 sybase   dba 4096 mai 14 04:06 sybase
16 drwxr-xr-x 2 postgres dba 4096 mai 14 04:06 postgres
17 [root@vmsqwarebox /]# ls -l /backups
18 total 32
19 drwxr-xr-x 2 dbsqware dba 4096 mai 14 04:06 dbsqware
20 drwxr-xr-x 2 mssql    dba 4096 mai 14 04:06 mssql
21 drwxr-xr-x 2 mysql    dba 4096 mai 14 04:06 mysql
22 drwxr-xr-x 2 oracle   dba 4096 mai 14 04:06 oracle
23 drwxr-xr-x 2 sybase   dba 4096 mai 14 04:06 sybase
24 drwxr-xr-x 2 postgres dba 4096 mai 14 04:06 postgres

SSH Keys

Preparing ssh keys for dbsqware.
With the user root :

1 su - dbsqware -c "
2 ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
3 chmod go-w \$HOME
4 cat \$HOME/.ssh/id_rsa.pub >>\$HOME/.ssh/authorized_keys
5 chmod 700 \$HOME/.ssh
6 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 :

1 [root@vmsqwarebox home]# su - dbsqware
2 $ ssh dbsqware@vmsqwarebox
3 The authenticity of host 'vmsqwarebox (85.31.222.135)' can't be established.
4 RSA key fingerprint is bd:e1:d0:d3:c5:2d:94:8f:df:f0:a2:50:42:8d:ad:1d.
5 Are you sure you want to continue connecting (yes/no)? yes
6 Warning: Permanently added ' vmsqwarebox,bbb.xx.aaa.yyy' (RSA) to the list of known hosts.
7 Last login: Mon May 14 04:33:25 2012 from vmsqwarebox
8 $ exit
9 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 :

 1 lvsqw_SshKey_dbsqware=$(cat /home/dbsqware/.ssh/id_rsa.pub)
 2 for lvsqw_User in `echo 'oracle sybase mssql mysql postgres'`
 3 do
 4 su - $lvsqw_User -c "
 5 ssh-keygen -t rsa -N '' -f \$HOME/.ssh/id_rsa
 6 chmod go-w \$HOME
 7 cat \$HOME/.ssh/id_rsa.pub >>\$HOME/.ssh/authorized_keys
 8 echo '$lvsqw_SshKey_dbsqware' >>\$HOME/.ssh/authorized_keys
 9 chmod 700 \$HOME/.ssh
10 chmod 600 \$HOME/.ssh/authorized_keys"
11 done
12 ssh-keygen -t rsa -N '' -f $HOME/.ssh/id_rsa
13 chmod go-w $HOME
14 echo "$lvsqw_SshKey_dbsqware" >>$HOME/.ssh/authorized_keys
15 chmod 700 $HOME/.ssh
16 chmod 600 $HOME/.ssh/authorized_keys

Test if it works (example for the oracle user to repeat for all users) :

 1 [root@vmsqwarebox home]# su - dbsqware
 2 $ id
 3 uid=1000(dbsqware) gid=1000(dba) groupes=1000(dba),10(wheel)
 4 $ ssh oracle@vmsqwarebox
 5 $ id
 6 uid=1001(oracle) gid=1000(dba) groupes=1000(dba)
 7 $ ssh oracle@vmsqwarebox
 8 The authenticity of host 'vmsqwarebox (85.31.222.135)' can't be established.
 9 RSA key fingerprint is bd:e1:d0:d3:c5:2d:94:8f:df:f0:a2:50:42:8d:ad:1d.
10 Are you sure you want to continue connecting (yes/no)? yes
11 Warning: Permanently added 'vmsqwarebox,bbb.xx.aaa.yyy' (RSA) to the list of known hosts.
12 Last login: Mon May 14 05:07:47 2012 from vmsqwarebox
13 $ id
14 uid=1001(oracle) gid=1000(dba) groupes=1000(dba)
15 $ exit
16 Connection to vmsqwarebox closed.
17 $ exit
18 Connection to vmsqwarebox closed.

Environment files

Setting up a .bash_profile and .bashrc for all users :

 1 cat <<EOFCAT >/tmp/.vimrc
 2 syntax off
 3 set mouse-=a
 4 set noai
 5 EOFCAT
 6 cat <<EOFCAT >/tmp/.bash_profile
 7 # .bash_profile
 8 
 9 # Get the aliases and functions
10 if [ -f ~/.bashrc ]; then
11 	. ~/.bashrc
12 fi
13 
14 # User specific environment and startup programs
15 export PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/ccs/bin:/usr/local/sbin:/usr/openwin/bin
16 EOFCAT
17 cat <<EOFCAT >/tmp/.bashrc
18 # Source global definitions
19 # if [ -f /etc/bashrc ] && [ \$(ps -p \$\$|grep -c 'bash') -ne 0 ] ; then
20 	# . /etc/bashrc
21 # fi
22 unalias ls 2>/dev/null
23 alias ll='ls -al'
24 alias lt='ls -alrt'
25 set -o vi
26 EOFCAT
27 chmod 777 /tmp/.bash_profile /tmp/.bashrc /tmp/.vimrc
28 
29 for lvsqw_User in `echo 'dbsqware oracle sybase mssql mysql postgres root'`
30 do
31 su - $lvsqw_User -c "
32 cp /tmp/.vimrc \$HOME/.
33 cp /tmp/.bash_profile \$HOME/.
34 cp /tmp/.bashrc \$HOME/.
35 cd
36 ln -s .bash_profile .profile
37 chmod 640 \$HOME/.vimrc
38 chmod 640 \$HOME/.bash_profile
39 chmod 640 \$HOME/.bashrc"
40 done
41 rm -f /tmp/.bash_profile /tmp/.bashrc
42 
43 cat <<EOCAT >> $HOME/.bashrc
44 export PS1="\`id| cut -d')' -f1| cut -d'(' -f2\`@\$(hostname|cut -d '.' -f1):"'\$PWD # '
45 EOCAT
46 
47 for lvsqw_User in `echo 'oracle sybase mssql mysql postgres'`
48 do
49 su - $lvsqw_User -c "
50 ln -s /home/dbsqware/dbSQWare/SQWareProduction \$HOME/SQWareProduction"
51 done

Checking that all files have been created :

 1 [root@vmsqwarebox ~]# ls -al /home/*
 2 /home/dbsqware:
 3 total 56
 4 drwxr-x--- 3 dbsqware dba  4096 May 14 05:30 .
 5 drwxr-xr-x 7 root     root 4096 May 14 03:57 ..
 6 -rw-r----- 1 dbsqware dba    48 May 14 05:30 .bashrc
 7 -rw-r----- 1 dbsqware dba    13 May 14 05:30 .bash_profile
 8 -rw------- 1 dbsqware dba   746 May 14 05:26 .sh_history
 9 drwx------ 2 dbsqware dba  4096 May 14 04:37 .ssh
10 -rw------- 1 dbsqware dba   922 May 14 05:23 .viminfo
11 
12 /home/mssql:
13 total 40
14 drwxr-x--- 3 mssql dba  4096 May 14 05:30 .
15 drwxr-xr-x 7 root  root 4096 May 14 03:57 ..
16 -rw-r----- 1 mssql dba    48 May 14 05:30 .bashrc
17 -rw-r----- 1 mssql dba    13 May 14 05:30 .bash_profile
18 drwx------ 2 mssql dba  4096 May 14 05:16 .ssh
19 
20 /home/mysql:
21 total 40
22 drwxr-x--- 3 mysql dba  4096 May 14 05:30 .
23 drwxr-xr-x 7 root  root 4096 May 14 03:57 ..
24 -rw-r----- 1 mysql dba    48 May 14 05:30 .bashrc
25 -rw-r----- 1 mysql dba    13 May 14 05:30 .bash_profile
26 drwx------ 2 mysql dba  4096 May 14 05:16 .ssh
27 
28 /home/oracle:
29 total 48
30 drwxr-x--- 3 oracle dba  4096 May 14 05:30 .
31 drwxr-xr-x 7 root   root 4096 May 14 03:57 ..
32 -rw-r----- 1 oracle dba    48 May 14 05:30 .bashrc
33 -rw-r----- 1 oracle dba    13 May 14 05:30 .bash_profile
34 -rw------- 1 oracle dba    76 May 14 05:12 .sh_history
35 drwx------ 2 oracle dba  4096 May 14 05:16 .ssh
36 
37 /home/sybase:
38 total 40
39 drwxr-x--- 3 sybase dba  4096 May 14 05:30 .
40 drwxr-xr-x 7 root   root 4096 May 14 03:57 ..
41 -rw-r----- 1 sybase dba    48 May 14 05:30 .bashrc
42 -rw-r----- 1 sybase dba    13 May 14 05:30 .bash_profile
43 drwx------ 2 sybase dba  4096 May 14 05:16 .ssh
44 
45 /home/postgres:
46 total 40
47 drwxr-x--- 3 sybase dba  4096 May 14 05:30 .
48 drwxr-xr-x 7 root   root 4096 May 14 03:57 ..
49 -rw-r----- 1 sybase dba    48 May 14 05:30 .bashrc
50 -rw-r----- 1 sybase dba    13 May 14 05:30 .bash_profile
51 drwx------ 2 sybase dba  4096 May 14 05:16 .ssh

Added the source part of the SQWareProduction environment in the .profile of all users :

 1 for lvsqw_Sgbd  in `echo 'oracle sybase mssql mysql postgres'`
 2 do
 3 lvsqw_tmp=$(echo "$lvsqw_Sgbd"|sed 's,\([a-z][a-z][a-z]\)[a-z]*,\1,')
 4 lvsqw_tmp1=$(echo "$lvsqw_tmp"|sed 's,\([a-z]\)[a-z]*,\1,'|tr '[a-z]' '[A-Z]')
 5 lvsqw_tmp2=$(echo "$lvsqw_tmp"|sed 's,[a-z]\([a-z][a-z]\)[a-z]*,\1,')
 6 TrigrammeSgbd="${lvsqw_tmp1}$lvsqw_tmp2"
 7 if [ "$TrigrammeSgbd" = "Mss" ]
 8 then
 9 TrigrammeSgbd="Msq"
10 lvsqw_tmp="msq"
11 elif [ "$TrigrammeSgbd" = "Pos" ]
12 then
13 TrigrammeSgbd="Pg"
14 lvsqw_tmp="pg"
15 fi
16 echo "DBMS processing: '$lvsqw_Sgbd', '$TrigrammeSgbd'"
17 cat <<EOFCAT >> /home/$lvsqw_Sgbd/.bash_profile
18 
19 # env dbSQWare for $lvsqw_Sgbd, must stay at end of .bash_profile
20 export gvsqw_Env='PRD'
21 export gvsqw_${TrigrammeSgbd}Bin=\$HOME/SQWareProduction/$lvsqw_Sgbd/bin
22 lvsqw_IsTerminal=\$(tty 2>&1 >/dev/null;echo \$?)
23 if [ "\$lvsqw_IsTerminal" = "0" ] && [ -r \$gvsqw_${TrigrammeSgbd}Bin/../etc/.profile_confort ]
24 then
25 . \$gvsqw_${TrigrammeSgbd}Bin/../etc/.profile_confort
26 fi
27 EOFCAT
28 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 :

 1 [root@vmsqwarebox ~]# su - dbsqware
 2 $ cd
 3 $ tar zxvf /tmp/dbSQWare_full_latest.tgz
 4 dbSQWare/
 5 dbSQWare/SQWareProduction/
 6 dbSQWare/SQWareProduction/ingres/
 7 dbSQWare/SQWareProduction/ingres/tools/
 8 ...
 9 dbSQWare/SQWareProduction/oracle/
10 dbSQWare/SQWareProduction/oracle/tools/
11 dbSQWare/SQWareProduction/oracle/tools/sqwora_RmanConfigure.ksh
12 ...
13 dbSQWare/SQWareCentral/
14 dbSQWare/SQWareCentral/lib/
15 dbSQWare/SQWareCentral/lib/sqwctl_MenuAction.lib
16 ...
17 dbSQWare/SQWareWeb/custom_ref/
18 dbSQWare/SQWareWeb/custom_ref/ingres/
19 ...
20 <br>
21 Verification all modules are installed :
22 <syntaxhighlight lang="sh" line>
23 $ ls -l ~dbsqware/dbSQWare
24 total 12
25 lrwxrwxrwx  1 dbsqware dba   24 Jan  2  2022 generic -> SQWareProduction/generic
26 drwxr-x---  3 dbsqware dba   56 Feb 23 14:51 SQWareAdmin
27 drwxr-x--- 16 dbsqware dba 4096 Feb 23 14:51 SQWareCentral
28 drwxr-x---  4 dbsqware dba   33 Feb 23 14:51 SQWarePredict
29 drwxr-x--- 15 dbsqware dba  192 Feb 23 14:51 SQWareProduction
30 drwxr-x--- 17 dbsqware dba 4096 Feb 23 14:51 SQWareRepository
31 drwxr-x--- 11 dbsqware dba  185 Feb 23 14:51 SQWareWeb
32 -rwxr-x---  1 dbsqware dba    9 Feb 23 14:51 SQW_VERSION

dbsqware user profile configuration for SQWareCentral

Setting up default configurations:

 1 cat <<EOFCAT >/home/dbsqware/dbSQWare/SQWareCentral/etc_cust/sqwctl_GlobalVar.cfg
 2 export gvsqw_AsynchronousFilesDir="/data/dbsqware/admin/AsynchronousFiles"
 3 export gvsqw_AsynchronousFilesDirTmp="/data/dbsqware/admin/AsynchronousFilesTmp"
 4 export gvsqw_AsynchronousFilesDirArch="/data/dbsqware/admin/AsynchronousFilesArch"
 5 EOFCAT
 6 chmod 750 /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/sqwctl_GlobalVar.cfg
 7 
 8 #change for the right email address !
 9 cat <<EOFCAT >/home/dbsqware/dbSQWare/SQWareProduction/generic/etc_cust/sqwgen_GlobalVar.cfg
10 #Put NoNail in gvsqw_GlobalMail if you don't want to send mail on error
11 export gvsqw_GlobalMail='dba@mydomane.com'
12 
13 #for SQWareWeb
14 export gvsqw_WebDbaURL="https://$(hostname)"
15 export gvsqw_WebDbaUser='dbsqware@$(hostname)'
16 
17 EOFCAT
18 chmod 750 /home/dbsqware/dbSQWare/SQWareProduction/generic/etc_cust/sqwgen_GlobalVar.cfg
19 
20 ln -s /data/dbsqware/admin/AsynchronousFilesArch /home/dbsqware/dbSQWare/SQWareWeb/sqw_logdir
21 ln -s /data/mysql/SlowQuery /home/dbsqware/dbSQWare/SQWareWeb/sqw_mysql_slow
22 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.

1 dbsqware@vmsqwarebox:/home/dbsqware $ vi .bash_profile
2 ## SQWareCentral
3 export gvsqw_Env='PRD'
4 export gvsqw_SQWareCentralPath=$HOME/dbSQWare/SQWareCentral
5 export gvsqw_SQWareProductionPath=$HOME/dbSQWare/SQWareProduction
6 . $gvsqw_SQWareCentralPath/etc/.profile

To check if everything is OK, source the user's .bash_profile and you should have a result like this:

 1 dbsqware@vmsqwarebox:/home/dbsqware $ . ~/.bash_profile
 2      _ _    ____   _____        __
 3   __| | |__/ ___| / _ \ \      / /_ _ _ __ ___
 4  / _` | '_ \___ \| | | \ \ /\ / / _` | '__/ _ \
 5 | (_| | |_) |__) | |_| |\ V  V / (_| | | |  __/
 6  \__,_|_.__/____/ \__\_\ \_/\_/ \__,_|_|  \___|
 7 
 8 ###################################################################
 9 # SQWareCentral module of dbSQWare
10 # Copyright (C) 2010-2024, dbSQWare (www.dbsqware.com)
11 # dbSQWare is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14 # CGU/CGA for more details.
15 # You should have received a copy of the CGU/CGA
16 # along with this program.  If not, see <https://www.dbsqware.com/licenses/>.
17 # For information : contact@dbsqware.com or www.dbsqware.com
18 ###################################################################
19 # SqwVersion=2024.03
20 
21 # 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:

1 ~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):

 1 [root@vmsqwarebox ~]# su - mysql
 2 $ mysql -uroot -pPassMys
 3 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 4 Your MariaDB connection id is 5
 5 Server version: 10.6.12-MariaDB-log MariaDB Server
 6 
 7 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 8 
 9 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
10 
11  (root@localhost) [(none)]> create database dbsqware_db;
12 Query OK, 1 row affected (0.00 sec)
13 
14  (root@localhost) [(none)]> GRANT ALL PRIVILEGES ON dbsqware_db.* TO 'dbsqware_user'@'localhost' IDENTIFIED BY 'PassMys';
15 Query OK, 0 rows affected (0.01 sec)
16 
17  (root@localhost) [(none)]> exit
18 Bye
19 mysql@vmsqwarebox:/home/mysql () $ exit
20 logout
21 [root@vmsqwarebox ~]# exit
22 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):

1 dbsqware@vmsqwarebox:/home/dbsqware $ vi /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf
2 [client]
3 host = localhost
4 port = 3306
5 database = dbsqware_db
6 user = dbsqware_user
7 password = PassMys
8 
9 chmod 640 /home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf

Connexion test to the repository (with the file) :

 1 dbsqware@vmsqwarebox:/home/dbsqware $ mysql --defaults-file='/home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf'
 2 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 3 Your MariaDB connection id is 4
 4 Server version: 11.4.2-MariaDB-log MariaDB Server
 5 
 6 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 7 
 8 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 9 
10 MariaDB [dbsqware_db]> show databases;
11 +--------------------+
12 | Database           |
13 +--------------------+
14 | dbsqware_db        |
15 | information_schema |
16 +--------------------+
17 2 rows in set (0.001 sec)
18 
19 MariaDB [dbsqware_db]> exit
20 Bye

Installing the repository :

 1 dbsqware@vmsqwarebox:/home/dbsqware $ dbspatch
 2 
 3 
 4      _ _    ____   _____        __
 5   __| | |__/ ___| / _ \ \      / /_ _ _ __ ___
 6  / _` | '_ \___ \| | | \ \ /\ / / _` | '__/ _ \
 7 | (_| | |_) |__) | |_| |\ V  V / (_| | | |  __/
 8  \__,_|_.__/____/ \__\_\ \_/\_/ \__,_|_|  \___|
 9 
10 ###################################################################
11 # SQWareCentral module of dbSQWare
12 # Copyright (C) 2010-2024, dbSQWare (www.dbsqware.com)
13 # dbSQWare is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16 # CGU/CGA for more details.
17 # You should have received a copy of the CGU/CGA
18 # along with this program.  If not, see <http://www.dbsqware.com/licenses/>.
19 # For information : contact@dbsqware.com or www.dbsqware.com
20 ###################################################################
21 # SqwVersion=2024.08
22 
23 # For help type 'show <-h|-help|--help>'
24 
25 
26 Create tree for custom dir ...
27 create directory /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom
28 create directory /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/images
29 
30 Copy ref files to custom if not exists ...
31 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
32 copy /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom_ref/GlobalHead.html on /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/GlobalHead.html
33 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
34 copy /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom_ref/sqwareweb.ini on /home/dbsqware/dbSQWare/SQWareCentral/../SQWareWeb/exploit/bin/../../custom/sqwareweb.ini
35 Sourcing sqwctl_Global.lib v2024.08 SQWareCentral (dbSQWare) ...
36 
37 
38 eval global vars:
39 gvsqw_RsyncDefaultUser="$gvsqw_UserExec@RsyncHost" => gvsqw_RsyncDefaultUser=dbsqware@RsyncHost
40 
41 Checks before install:
42 SQWareRepository source to install: /home/dbsqware/dbSQWare/SQWareRepository
43 Connect string to repository:
44 MySQL_Command="mysql --defaults-file='/home/dbsqware/dbSQWare/SQWareCentral/etc_cust/.my_SQWareRepository.cnf'"
45 [client]
46 host = localhost
47 port = 3306
48 database = dbsqware_db
49 user = dbsqware_user
50 10.11.11-MariaDB-log
51 Major version of Repository, 1011, 10.11.11
52 
53 Checks for install mode (full or patch):
54 You will make a full install.
55 
56 Do you want to full install SQWareRepository [y-Y-o-O-n-N]?

Here type Y, the repository installation will take some time.

 1 ...
 2 ...
 3 ...
 4 END_TRT Code:   0 --> Update_06_MenuY.sql executed successfully.
 5 END_TRT Code:   0 --> Update_07_MenuY.sql executed successfully.
 6 END_TRT Code:   0 --> Update_08_MenuY.sql executed successfully.
 7 END_TRT Code:   0 --> Update_09_MenuY.sql executed successfully.
 8 
 9 Job Install SQWareRepository proceeded successfully
10 
11 Begining     : 2024-04-15 12:13:31
12 End          : 2024-04-15 12:15:04
13 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:

 1 $ cd /home/dbsqware/dbSQWare/SQWareWeb
 2 $ ls -l
 3 total 24
 4 drwxr-x---  2 dbsqware dba   70 Nov 29 19:46 css
 5 drwxr-xr-x  3 dbsqware dba   64 Dec  8 14:50 custom
 6 drwxr-x---  3 dbsqware dba   64 Nov 29 19:46 custom_ref
 7 drwxr-x---  6 dbsqware dba   60 Nov 29 19:46 exploit
 8 drwxr-x--- 19 dbsqware dba  322 Nov 29 19:46 htdocs
 9 drwxr-x---  2 dbsqware dba 8192 Nov 29 19:46 images
10 drwxr-x--- 14 dbsqware dba  177 Nov 29 19:46 jobs_help
11 drwxr-x---  5 dbsqware dba  239 Nov 29 19:46 js
12 drwxr-x---  3 dbsqware dba 4096 Nov 29 19:46 lib
13 -rwxr-x---  1 dbsqware dba   22 Nov 29 19:46 phpinfo.php
14 lrwxrwxrwx  1 dbsqware dba   42 Dec  8 14:54 sqw_logdir -> /data/dbsqware/admin/AsynchronousFilesArch
15 lrwxrwxrwx  1 dbsqware dba   21 Dec  8 14:54 sqw_mysql_slow -> /data/mysql/SlowQuery
16 lrwxrwxrwx  1 dbsqware dba   16 Dec  8 14:54 sqw_oracle_awr -> /data/oracle/awr
17 drwxr-x---  2 dbsqware dba  214 Nov 29 19:46 templates
18 -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).

 1 $ vi /etc/httpd/conf.d/httpd-vhosts.conf
 2 <Directory "/home/dbsqware/dbSQWare/SQWareWeb">
 3     Options Indexes FollowSymLinks
 4     AllowOverride All
 5     Require all granted
 6 </Directory>
 7 <Directory "/data/oracle/awr">
 8     Options Indexes FollowSymLinks
 9     AllowOverride All
10     Require all granted
11 </Directory>
12 <VirtualHost *:80>
13     DocumentRoot /home/dbsqware/dbSQWare/SQWareWeb
14     ServerName vmsqwarebox
15     ServerAlias vmsqwarebox vmsqwareboxRocky9 webdba
16 </VirtualHost>
17 
18 [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).

 1 ## Generate SSL Certificate : To generate a new self-signed certificate with 365 days expiry, run:
 2 [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
 3 
 4 Generating a RSA private key
 5 ........................................................................................................................................+++++
 6 ...............................+++++
 7 writing new private key to '/etc/pki/tls/private/dbsqware_selfsigned.key'
 8 -----
 9 You are about to be asked to enter information that will be incorporated
10 into your certificate request.
11 What you are about to enter is what is called a Distinguished Name or a DN.
12 There are quite a few fields but you can leave some blank
13 For some fields there will be a default value,
14 If you enter '.', the field will be left blank.
15 -----
16 # Country Name (2 letter code) [XX]:Fr
17 # State or Province Name (full name) []:France
18 # Locality Name (eg, city) [Default City]:Gardanne
19 # Organization Name (eg, company) [Default Company Ltd]:dbSQWare - self-signed
20 # Organizational Unit Name (eg, section) []:SQWareWeb SSL
21 # Common Name (eg, your name or your server's hostname) []:webdba
22 # Email Address []:myadress@mydomain
23 
24 [root@vmsqwarebox ~]# ls -l /etc/pki/tls/private/dbsqware_selfsigned.key /etc/pki/tls/certs/dbsqware_selfsigned.crt
25 -rw-r--r--. 1 root root 1497 Apr 19 15:32 /etc/pki/tls/certs/dbsqware_selfsigned.crt
26 -rw-------. 1 root root 1704 Apr 19 15:31 /etc/pki/tls/private/dbsqware_selfsigned.key
27 
28 ## Installation de mod_ssl
29 [root@vmsqwarebox ~]# dnf install mod_ssl
30 
31 ## activation du module 'mod_ssl' module
32 [root@vmsqwarebox ~]# systemctl restart httpd
33 
34 ## verification
35 [root@vmsqwarebox ~]# httpd -t -D DUMP_MODULES| grep ssl
36  ssl_module (shared)
37 
38 ## To Redirect All HTTP Traffic To HTTPS
39 [root@vmsqwarebox ~]# cat /etc/httpd/conf.d/redirect_http.conf
40 <VirtualHost _default_:80>
41     ServerName vmsqwarebox
42     ServerAlias vmsqwarebox vmsqwareboxRocky9 webdba
43 	Redirect permanent / https://webdba/
44 </VirtualHost>
45 
46 ## vhost https
47 [root@vmsqwarebox ~]# vi /etc/httpd/conf.d/httpd-vhosts.conf
48 <Directory "/home/dbsqware/dbSQWare/SQWareWeb">
49 	Options Indexes FollowSymLinks
50 	AllowOverride All
51 	Require all granted
52 </Directory>
53 <Directory "/data/oracle/awr">
54 	Options Indexes FollowSymLinks
55 	AllowOverride All
56 	Require all granted
57 </Directory>
58 <VirtualHost *:443>
59 	DocumentRoot /home/dbsqware/dbSQWare/SQWareWeb
60 	ServerName vmsqwarebox
61 	ServerAlias vmsqwarebox VMsqwareboxRockyLinux9 webdba
62 	SSLEngine on
63 	SSLCertificateFile /etc/pki/tls/certs/dbsqware_selfsigned.crt
64 	SSLCertificateKeyFile /etc/pki/tls/private/dbsqware_selfsigned.key
65 </VirtualHost>
66 
67 [root@vmsqwarebox ~]# systemctl reload httpd

Personnalization

Set up repository access (SQWareRepository) for SQWareWeb in the following file:

1 vi /home/dbsqware/dbSQWare/SQWareWeb/custom/sqwareweb.ini
2 [db]
3 MysqlHost="localhost"
4 MysqlUser="dbsqware_user"
5 MysqlPass="PassMys"

Change this file with your logo :

1 /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 :

1 User = default_admin
2 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. :

1 ;debug=true
2 ;debug_before_connectdb=true
3 ;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).

1 $ cd /home/dbsqware/dbSQWare/SQWareWeb
2 $ ln -s /data/oracle/awr sqw_oracle_awr
3 $ ls -l sqw_oracle_awr
4 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).

1 $ cd /home/dbsqware/dbSQWare/SQWareWeb
2 $ ln -s /data/mysql/SlowQuery sqw_mysql_slow
3 $ ls -l sqw_mysql_slow
4 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 :

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

1 delete from tsqw_AdminLoginsPrivs where id_usr_grantee=1;
2 delete from tsqw_AdminLogins where username='default_admin';

Creating a Viewer User (customize your password) :

1 insert into tsqw_AdminLogins (username,lastname,firstname,passwd, cre_date, upd_date, pwd_date, dbaname, authentication_type, is_forweb)
2 values ('viewer', 'Viewer', 'Viewer', SHA1('View!Sec@'), now(), now(), now(), 'init', 'internal','1');

Creating an Admin User (customize your password) :

 1 insert into tsqw_AdminLogins (username,lastname,firstname,passwd, cre_date, upd_date, pwd_date, dbaname, authentication_type, is_forweb)
 2 values ('myadmin', 'Adm', 'My', SHA1('P@dm!n'), now(), now(), now(), 'init', 'internal','1')
 3 ;
 4 insert into tsqw_AdminLoginsPrivs (id_usr_grantee,id_grp_granted,dbaname,comments,upd_date)
 5 select id_usr_grantee, id_grp_granted, dbaname, comments, upd_date
 6 from
 7 	(select id_usr as id_usr_grantee from tsqw_AdminLogins where username='myadmin') gadm,
 8 	(select id_grp as id_grp_granted from tsqw_AdminGroups where groupname='DBA') gdba,
 9 	(select 'myadmin' as dbaname, 'Init dbSQWare' as comments, now() as upd_date from dual) com
10 ;

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 :

 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)
 4 ############
 5 # Generate all ref files (RefFileInstances,RefFileDb,RefFileCMDB,...)
 6 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'
 7 # Check indicators
 8 00 07       * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_CheckIndicators.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_CheckIndicators.log'
 9 # Gather indicators
10 00 03       * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_GatherIndicators.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_GatherIndicators.log'
11 # Retry gather indicators if not exists
12 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'
13 ############
14 # Retrieve async files from a dir (configure sqwctl_LoopRetrieveAsynchronousFilesGlobal.cfg in cetc)
15 20 *        * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopRetrieveAsynchronousFilesGlobal.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopRetrieveAsynchronousFilesGlobal.log'
16 # Retrieve async files from all instances
17 15 *        * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopRetrieveAsynchronousFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopRetrieveAsynchronousFiles.log'
18 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'
19 # Exec async files from all instances
20 30 *        * * * bash -c '. ~/.profile 2>&1 >/dev/null;$gvsqw_SQWareCentralPath/bin/sqwctl_LoopExecAsynchronousFiles.ksh 2>&1 >$HOME/admin/SQWareCentral/logs/sqwctl_LoopExecAsynchronousFiles.log'
21 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'
22 ########################################################################################################################
23 ### Gather Structures (dbSQWare)
24 ############
25 ## Structures Oracle
26 #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'
27 ## Structures MySQL
28 #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'
29 ## Structures PostgreSQL
30 #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'
31 ## Structures MsSql
32 #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'
33 ## Structures Sybase
34 #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'
35 ########################################################################################################################
36 ### Specific cron for Oracle (dbSQWare)
37 ############
38 ## Check started instances Oracle
39 #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'
40 ## Generate Awr reports in distant mode
41 #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'
42 ## Monitor Awr reports in distant mode
43 #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'
44 ## Generate Statspack reports in distant mode
45 #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'
46 ## Monitor Statspack reports in distant mode
47 #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'
48 ########################################################################################################################
49 ### Specific cron for MySql (dbSQWare)
50 ############
51 ## Rotate slow query
52 #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'
53 ## Async Rotate slow query
54 #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'
55 ## Rotate error log
56 #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'
57 ########################################################################################################################
58 ### Specific cron for MsSql (dbSQWare)
59 ############
60 ## Rotate errorlog
61 #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'
62 ########################################################################################################################
63 ### Specific cron for update GLPI (dbSQWare)
64 ############
65 ## Update GLPI MYS_GLPI_PRD
66 #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'