General:BaseInstallation
Contents
- 1 Generalties
- 2 Installation of central infrastructure
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:
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'
