Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


quarta-feira, 9 de maio de 2018

Abordando a instalação e configuração do SQL Server no Linux

Por Eduardo Legatti

Olá,



A notícia que mais chamou a atenção no final de 2016 foi o anúncio da Microsoft sobre o lançamento do SQL Server para Linux. De lá pra cá, algumas coisas já mudaram. Por exemplo, a instalação só era permitida apenas em máquinas com no mínimo de 4 GB de RAM livre, o que era ridículo, pois hoje existem vários notebooks com 4 GB de RAM que que são utilizadas com máquinas virtuais para fins de estudos e testes de várias aplicações, inclusive bancos de dados. Atualmente a Microsoft atendeu aos vários pedidos da comunidade e reduziu a quantidade de memória RAM mínima para 2 GB. Portanto, neste artigo irei mostrar como realizar a instalação e configuração básica do SQL Server 2017 em um sistema Linux CentOS 7.4 x86_64.

Confirmando abaixo a versão e distribuição do Linux, irei realizar o download do repositório de instalação do SQL Server a partir do site da Microsoft usando o utilitário wget conforme a seguir.

[root@linux ~]# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

[root@linux ~]# wget https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo -O /etc/yum.repos.d/mssql-server.repo
--2018-05-04 19:34:32--  https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
Resolving packages.microsoft.com (packages.microsoft.com)... 40.76.35.62
Connecting to packages.microsoft.com (packages.microsoft.com)|40.76.35.62|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 232 [application/octet-stream]
Saving to: /etc/yum.repos.d/mssql-server.repo

100%[=============================================================================================>] 232 --.-K/s   in 0s

2018-05-04 19:34:33 (2.60 MB/s) - /etc/yum.repos.d/mssql-server.repo saved [232/232]


[root@linux ~]# ls -lh /etc/yum.repos.d
total 32K
-rw-r--r--. 1 root root 1.7K Aug 30  2017 CentOS-Base.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-CR.repo
-rw-r--r--. 1 root root  649 Aug 30  2017 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root  314 Aug 30  2017 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  630 Aug 30  2017 CentOS-Media.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-Sources.repo
-rw-r--r--. 1 root root 3.8K Aug 30  2017 CentOS-Vault.repo
-rw-r--r--. 1 root root  232 Sep 19  2017 mssql-server.repo

Após a realização do download, irei instalar o pacote mssql-server usando o utilitário yum conforme a seguir.

[root@linux ~]# yum install mssql-server -y
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-mssql-server-2017                                        | 2.9 kB  00:00:00
packages-microsoft-com-mssql-server-2017/primary_db                             |  11 kB  00:00:01
Loading mirror speeds from cached hostfile
 * base: centos.brisanet.com.br
 * extras: centos.brisanet.com.br
 * updates: centos.brisanet.com.br
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.3025.34-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================
 Package                 Arch     Version         Repository                                 Size
==================================================================================================
Installing:
 mssql-server            x86_64   14.0.3025.34-3  packages-microsoft-com-mssql-server-2017   167 M

Transaction Summary
==================================================================================================
Install  1 Package

Total download size: 167 M
Installed size: 167 M
Downloading packages:
Public key for mssql-server-14.0.3025.34-3.x86_64.rpm is not installed
mssql-server-14.0.3025.34-3.x86_64.rpm                                          | 167 MB  00:00:53
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
 Userid     : "Microsoft (Release signing) [gpgsecurity@microsoft.com]"
 Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
 From       : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mssql-server-14.0.3025.34-3.x86_64                               1/1

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
  Verifying  : mssql-server-14.0.3025.34-3.x86_64                               1/1

Installed:
  mssql-server.x86_64 0:14.0.3025.34-3

Complete!

Uma vez instalado o pacote, poderemos proceder com a configuração da instância do SQL Server conforme demonstrado abaixo. Após a execução do comando mssql-conf setup, irei selecionar a opção (1) e fornecer a senha do usuário sa como Aa12345678 para prosseguir com a configuração.

[root@linux ~]# /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) Enterprise Core (PAID)
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 1
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855864&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Enter the SQL Server system administrator password:Aa12345678
Confirm the SQL Server system administrator password:Aa12345678
Configuring SQL Server...

The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
DBSTARTUP (msdb, 4): FCBOpenTime took 1026 ms
DBSTARTUP (msdb, 4): FCBHeaderReadTime took 705 ms
DBSTARTUP (msdb, 4): FileMgrPreRecoveryTime took 1570 ms
DBSTARTUP (msdb, 4): MasterFilesScanTime took 852 ms
DBSTARTUP (msdb, 4): PhysicalCompletionTime took 255 ms
DBSTARTUP (msdb, 4): RecoveryCompletionTime took 169 ms
DBSTARTUP (msdb, 4): UpgradeTime took 951 ms
DBSTARTUP (msdb, 4): StartupInDatabaseTime took 7426 ms
DBSTARTUP (msdb, 4): RemapSysfiles1Time took 108 ms
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

Pronto. Após a instalação e configuração do SQL Server, podemos verificar se o serviço está inicializado.

[root@linux ~]# systemctl status mssql-server
* mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2018-05-04 20:12:17 BST; 2min 0s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 1594 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           |-1594 /opt/mssql/bin/sqlservr
           |-1618 /opt/mssql/bin/sqlservr

May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.65 spid22s     The Service Broker endpoint is in disabled or stopped state.
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.65 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): FCBOpenTime took 1254 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): FCBHeaderReadTime took 632 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): FileMgrPreRecoveryTime took 1581 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): MasterFilesScanTime took 873 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): RecoveryCompletionTime took 118 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): StartupInDatabaseTime took 6707 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.73 spid22s     Service Broker manager has started.
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.76 spid7s      Recovery is complete. This is an informational message only. No user action is required.

Caso o SQL Server precise ser acessado remotamente e o Firewall esteja habilitado no servidor, é importante liberar a porta 1433 como demonstrado abaixo. No meu caso, o serviço FirewallD não estava em execução e portanto não havia nenhuma regra de firewall ativa.

[root@linux ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent
FirewallD is not running
[root@linux ~]# firewall-cmd --reload
FirewallD is not running

[root@linux ~]# netstat -na | grep 1433
tcp        0      0 0.0.0.0:1433            0.0.0.0:*               LISTEN
tcp6       0      0 :::1433                 :::*                    LISTEN

Vale a penas salientar que a instalação do SQL Server não vem com alguns utilitários clientes "command-line tools" como o sqlcmd. Portanto, precisaremos realizar o download do pacote e realizar a instalação conforme demonstrado abaixo.

[root@linux ~]# wget https://packages.microsoft.com/config/rhel/7/prod.repo -O /etc/yum.repos.d/prod.repo
--2018-05-04 20:16:19--  https://packages.microsoft.com/config/rhel/7/prod.repo
Resolving packages.microsoft.com (packages.microsoft.com)... 40.76.35.62
Connecting to packages.microsoft.com (packages.microsoft.com)|40.76.35.62|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 193 [application/octet-stream]
Saving to: /etc/yum.repos.d/prod.repo

100%[=============================================================================================>] 193 --.-K/s   in 0s

2018-05-04 20:16:20 (2.16 MB/s) - /etc/yum.repos.d/prod.repo saved [193/193]

[root@linux ~]# ls -lh /etc/yum.repos.d
total 36K
-rw-r--r--. 1 root root 1.7K Aug 30  2017 CentOS-Base.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-CR.repo
-rw-r--r--. 1 root root  649 Aug 30  2017 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root  314 Aug 30  2017 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  630 Aug 30  2017 CentOS-Media.repo
-rw-r--r--. 1 root root 1.3K Aug 30  2017 CentOS-Sources.repo
-rw-r--r--. 1 root root 3.8K Aug 30  2017 CentOS-Vault.repo
-rw-r--r--. 1 root root  232 Sep 19  2017 mssql-server.repo
-rw-r--r--. 1 root root  193 Nov 16  2016 prod.repo

Após a realização do download, irei instalar os pacotes mssql-tools e unixODBC-devel aceitando os termos de licença conforme a seguir.

[root@linux ~]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-prod                                                     | 2.9 kB  00:00:00
packages-microsoft-com-prod/primary_db                                          |  89 kB  00:00:01
Loading mirror speeds from cached hostfile
 * base: centos.brisanet.com.br
 * extras: centos.brisanet.com.br
 * updates: centos.brisanet.com.br
Package unixODBC-devel-2.3.1-11.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.1.0.1-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.2.0.0 for package: mssql-tools-17.1.0.1-1.x86_64
--> Processing Dependency: msodbcsql17 >= 17.1.0.1 for package: mssql-tools-17.1.0.1-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.1.0.1-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===================================================================================================
 Package                      Arch     Version     Repository                                  Size
===================================================================================================
Installing:
 mssql-tools                  x86_64   17.1.0.1-1  packages-microsoft-com-prod                253 k
Installing for dependencies:
 msodbcsql17                  x86_64   17.1.0.1-1  packages-microsoft-com-prod                4.1 M

Transaction Summary
====================================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 4.4 M
Installed size: 4.4 M
Downloading packages:
(1/2): mssql-tools-17.1.0.1-1.x86_64.rpm                                        | 253 kB  00:00:01
(2/2): msodbcsql17-17.1.0.1-1.x86_64.rpm                                        | 4.1 MB  00:00:06
----------------------------------------------------------------------------------------------------
Total                                                                    678 kB/s | 4.4 MB  00:00:06
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
The license terms for this product can be downloaded from
https://aka.ms/odbc170eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
  Installing : msodbcsql17-17.1.0.1-1.x86_64                                    1/2
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
  Installing : mssql-tools-17.1.0.1-1.x86_64                                    2/2
  Verifying  : msodbcsql17-17.1.0.1-1.x86_64                                    1/2
  Verifying  : mssql-tools-17.1.0.1-1.x86_64                                    2/2

Installed:
  mssql-tools.x86_64 0:17.1.0.1-1

Dependency Installed:
  msodbcsql17.x86_64 0:17.1.0.1-1

Complete!

Pronto. Para poder executar os utilitários de linha de comandos de qualquer diretório, precisaremos adicionar o caminho abaixo na variável PATH.

[root@linux ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
[root@linux ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
[root@linux ~]# source ~/.bashrc

[root@linux ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/opt/mssql-tools/bin

Agora poderemos testar a conexão com a instância do SQL Server.

[root@linux ~]# sqlcmd -S localhost -U SA -PAa12345678
1> select @@version;
2> GO

-------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64)
        Apr  9 2018 18:00:41
        Copyright (C) 2017 Microsoft Corporation
        Enterprise Evaluation Edition (64-bit) on Linux (CentOS Linux 7 (Core))

(1 rows affected)

1> select database_id,name,file_id,name,physical_name from sys.master_files;
2> GO
database_id name          file_id     name       physical_name
----------- ------------- ----------- ---------- ----------------------------------
          1 master                  1 master     /var/opt/mssql/data/master.mdf
          1 mastlog                 2 mastlog    /var/opt/mssql/data/mastlog.ldf
          2 tempdev                 1 tempdev    /var/opt/mssql/data/tempdb.mdf
          2 templog                 2 templog    /var/opt/mssql/data/templog.ldf
          3 modeldev                1 modeldev   /var/opt/mssql/data/model.mdf
          3 modellog                2 modellog   /var/opt/mssql/data/modellog.ldf
          4 MSDBData                1 MSDBData   /var/opt/mssql/data/MSDBData.mdf
          4 MSDBLog                 2 MSDBLog    /var/opt/mssql/data/MSDBLog.ldf

(8 rows affected)

Para realizar o shutdown da instância do SQL Server, basta apenas executar o comando stop.

[root@linux ~]# systemctl stop mssql-server

Por fim, para se certificar de que o serviço foi finalizado, poderemos executar novamente o comando status.

[root@linux ~]# systemctl status mssql-server
* mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Fri 2018-05-04 20:40:31 BST; 4s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 1594 (code=exited, status=0/SUCCESS)

May 04 20:12:53 linux.local.net sqlservr[1594]: DBSTARTUP (msdb, 4): StartupInDatabaseTime took 6707 ms
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.73 spid22s     Service Broker manager has started.
May 04 20:12:53 linux.local.net sqlservr[1594]: 2018-05-04 20:12:53.76 spid7s      Recovery is complete. This is an informational message only. No user action is required.
May 04 20:17:49 linux.local.net sqlservr[1594]: 2018-05-04 20:17:49.69 spid51      Using 'dbghelp.dll' version '4.0.5'
May 04 20:40:29 linux.local.net systemd[1]: Stopping Microsoft SQL Server Database Engine...
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.60 spid7s      Always On: The availability replica manager is going offline because SQL Server is shutting down. This is an informational message only...ion is required.
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.61 spid7s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.74 spid22s     Service Broker manager has shut down.
May 04 20:40:29 linux.local.net sqlservr[1594]: 2018-05-04 20:40:29.94 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
May 04 20:40:31 linux.local.net systemd[1]: Stopped Microsoft SQL Server Database Engine.
Hint: Some lines were ellipsized, use -l to show in full.

segunda-feira, 16 de abril de 2018

Abordando a instalação e configuração do MySQL no Linux

Por Eduardo Legatti

Olá,



Neste artigo irei abordar a instalação e configuração do MySQL (5.7.18 x86-64) em um Linux Centos 7.3 (64 bits). O primeiro passo é realizar o download dos pacotes de instalação para o servidor. Para isso, irei usar o utilitário wget, conforme a seguir.

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 335516 (328K) [application/x-redhat-package-manager]
Salvando em: “mysql-community-common-5.7.18-1.el7.x86_64.rpm”

100%[==============================================================================================>] 335.516     1,21M/s   em 0,3s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 2103888 (2,0M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-libs-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 2.103.888   3,40M/s   em 0,6s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 23039984 (22M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-client-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 23.039.984  2,86M/s   em 7,3s

[root@linux1 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.18-1.el7.x86_64.rpm --no-check-certificate
Conectando-se a cdn.mysql.com|104.105.143.228|:443... conectado.
A requisição HTTP foi enviada, aguardando resposta... 200 OK
Tamanho: 156322552 (149M) [application/x-redhat-package-manager]
Salvando em: “mysql-community-server-5.7.18-1.el7.x86_64.rpm”

100%[===============================================================================================>] 156.322.552  967K/s   em 3m 4s

[root@linux1 ~]# ls -lh *.rpm
-rw-r--r-- 1 root root 328K Mar 20 06:40 mysql-community-common-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 2,1M Mar 20 06:40 mysql-community-libs-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root  22M Mar 20 06:40 mysql-community-client-5.7.18-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 150M Mar 20 06:41 mysql-community-server-5.7.18-1.el7.x86_64.rpm

Após a realização do download dos pacotes de instalação, irei realizar a instalação dos mesmos usando o utilitário rpm. Vale a pena salientar que a ordem de instalação dos pacotes a seguir é importante.

[root@linux1 ~]# rpm -ivh mysql-community-common-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-common-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-common ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-libs-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-libs-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-libs   ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-client-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-client-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-client ########################################### [100%]

[root@linux1 ~]# rpm -ivh mysql-community-server-5.7.18-1.el7.x86_64.rpm
aviso: mysql-community-server-5.7.18-1.el7.x86_64.rpm: Cabeçalho V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparando...               ########################################### [100%]
   1:mysql-community-server ########################################### [100%]

Pronto. Podemos checar pelo comando abaixo que os pacotes do MySQL foram instalados com sucesso.

[root@linux1 ~]# rpm -qa | grep mysql
mysql-community-server-5.7.18-1.el7.x86_64
mysql-community-common-5.7.18-1.el7.x86_64
mysql-community-libs-5.7.18-1.el7.x86_64
mysql-community-client-5.7.18-1.el7.x86_64

Se quisermos prosseguir com a desinstalação do MySQL, basta utilizarmos os comandos abaixo.

[root@linux1 ~]# rpm -e mysql-community-server-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-common-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-libs-5.7.18-1.el7.x86_64 --nodeps
[root@linux1 ~]# rpm -e mysql-community-client-5.7.18-1.el7.x86_64 –nodeps
[root@linux1 ~]# rm -rf /var/lib/mysql/

Uma vez que o MySQL já se encontra está instalado, poderemos iniciar o seu serviço e começar a realizar a configuração inicial, conforme demonstrado abaixo.

[root@linux1 ~]# systemctl start mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-02 13:45:55 BST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??3109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.

Após a inicialização do serviço da instância do MySQL, poderemos conectar no mesmo após obter uma senha temporária.

[root@linux1]# grep 'temporary password' /var/log/mysqld.log
2018-04-16T12:30 [Note] A temporary password is generated for root@localhost: 8V*vubKtFQp(

[root@linux1 ~]# mysql -uroot –p'8V*vubKtFQp('
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 Server version: 5.7.18

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Pronto. Após conectarmos na instância com o usuário root, somos forçados a alterar a senha por questões de segurança

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Agora irei realizar o procedimento de alteração da senha que deve conter pelo menos uma letra maiúscula, um caractere especial, além de números, como também alterar a política de segurança de senhas de MEDIUM para LOW.

mysql> alter user root@localhost identified by 'Asdjer3jk4kjd4@#';
Query OK, 0 rows affected (0,00 sec)

mysql> show variables like '%password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+
1 row in set (0,07 sec)

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0,03 sec)

Após diminuir o nível de segurança da senha, é possível perceber que ainda não é possível utilizar uma senha mais simples. Apesar de não precisar de letras maiúsculas, e caracteres especiais, a mesma precisa ter pelo menos um tamanho de 8 caracteres conforme demonstrado abaixo.

mysql> alter user root@localhost identified by 'admin';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> alter user root@localhost identified by '12345678';
Query OK, 0 rows affected (0,01 sec)

Para desativar esta obrigatoriedade, bastará desinstalar o plugin validate_password.

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0,01 sec)

mysql> UNINSTALL PLUGIN validate_password;
Query OK, 0 rows affected (0,07 sec)

Uma vez desinstalado, poderemos usar uma senha mais simples.

mysql> alter user root@localhost identified by 'admin';
Query OK, 0 rows affected (0,00 sec)

Após a alteração da senha, poderemos instalar novamente o plugin validate_password, caso seja necessário.

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0,03 sec)

Para realizarmos qualquer operação no MySQL, fazemos uso do utilitário mysql.

[root@linux1]# whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

Para iniciar, parar ou checar o status da instância do MySQL, poderemos fazer uso do comando systemctl. No Linux 6.x e anteriores, fazíamos uso do comando service.

[root@linux1 ~]# systemctl start mysqld
[root@linux1 ~]# systemctl status mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-02 13:45:55 BST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (mysqld)
   CGroup: /system.slice/mysqld.service
           ??3109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.

[root@linux1 ~]# systemctl stop mysqld
[root@linux1 ~]# systemctl status mysqld
* mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Thu 2017-05-02 13:47:00 BST; 904ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3105 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3084 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3109 (code=exited, status=0/SUCCESS)

Abr 16 13:45:50 linux1 systemd[1]: Starting MySQL Server...
Abr 16 13:45:55 linux1 systemd[1]: Started MySQL Server.
Abr 16 13:46:59 linux1 systemd[1]: Stopping MySQL Server...
Abr 16 13:47:00 linux1 systemd[1]: Stopped MySQL Server.

Em relação a possibilidade de conexão remota à instância do MySQL, podemos checar que qualquer computar poderá ter acesso ao mesmo. Caso queiramos que a conectividade fuique restrita ao servidor, bastara apenas setar o parâmetro bind_address para 127.0.0.1.

mysql> show variables like '%bind%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+

[root@linux1 ~]# netstat -na | grep 3306
tcp        0      0 :::3306       :::*          LISTEN

Para finalizar, segue abaixo umas informações do arquivo de configuração da instância do MySQL. O mesmo pode ser encotrado em /etc/my.cnf, mas dependendo da distribuição do Linux, ele poderá estar em outro diretório.

[root@linux1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

segunda-feira, 26 de março de 2018

Oracle Blog agora é Database Blog

Por Eduardo Legatti

Olá,

Após a enquete realizada entre os meses de Agosto a Dezembro/2017, abordarei artigos técnicos não somente de Oracle, mas também MySQL, SQL Server e MongoDB.


quarta-feira, 14 de março de 2018

Retomando o processo de Export/Import Datapump no Oracle

Por Eduardo Legatti

Olá,

Quando realizamos tarefas de exportação ou importação através do Oracle Datapump, temos a opção de utilizar o modo interativo. Por exemplo, podemos abandonar o utilitário e deixar a parte "server" executando. Segue abaixo uma simulação usando o utilitário Import Datapump (impdp).

$ impdp system/manager dumpfile=BD01.dmp schemas=SCOTT nologfile=y

Import: Release 11.2.0.3.0 - Production on Seg Mar 12 15:06:41 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "SYSTEM"."SYS_IMPORT_SCHEMA_01" carregada/descarregada com sucesso
Iniciando "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** dumpfile=BD01.dmp schemas=SCOTT nologfile=y
Processando o tipo de objeto SCHEMA_EXPORT/USER
^C

Acima, após iniciar a importação, executei [CTRL +C] e digitei a palavra help. Dentre os comandos abaixo que podemos utilizar, irei fazer o teste saindo do utilitário impdp e depois retomando o modo interativo do mesmo.

Import> help
----------------------------------------------------------------------------
Os seguintes comandos sao validos quando estao no modo interativo.
Observacão: sao permitidas abreviaturas.

CONTINUE_CLIENT
Retorna ao modo de log. O job ser reiniciado se estiver inativo.

EXIT_CLIENT
Encerra a sessao do cliente e deixa o job sendo executado.

HELP
Resume comandos interativos.

KILL_JOB
Desassocia e deleta o job.

PARALLEL
Altera o nmero de workers ativos para o job atual.

START_JOB
Inicia ou retoma o job atual.
As palavras-chave validas sao: SKIP_CURRENT.

STATUS
A frequencia (segundos) com que o status do job ser monitorado, em que
O default [0] mostrar o novo status quando disponivel.

STOP_JOB
Faz shutdown de forma ordenada da execuão do job e sai do cliente.
As palavras-chave validas sao: IMMEDIATE.

Import> exit_client

Após sair do utilitário impdp através do comando exit_client, irei verificar no banco de dados o nome da sessão que iremos utilizar para retomar ao modo não interativo.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Sessão alterada.

SQL> SELECT created, object_name
  2      FROM dba_objects
  3     WHERE owner = 'SYSTEM' AND object_name LIKE '%IMPORT%'
  4  ORDER BY 1;

CREATED             OBJECT_NAME
------------------- --------------------
12/03/2018 15:07:27 SYS_IMPORT_SCHEMA_01

SQL> select owner_name,job_name,operation from dba_datapump_jobs;
 
OWNER_NAME      JOB_NAME                       OPERATION
---------------- ------------------------------ --------------
SYSTEM           SYS_IMPORT_SCHEMA_01           IMPORT

Sabemos que o nome do job que está realizando a importação se chama SYS_IMPORT_SCHEMA_01 e será o nome deste job que iremos utilizar para retomar o processo de importação, como demonstrado abaixo.

$ impdp system/manager attach=SYS_IMPORT_SCHEMA_01

Import: Release 11.2.0.3.0 - Production on Seg Mar 12 15:10:37 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_01
  Proprietrio: SYSTEM
  Operaão: IMPORT
  Privs. do Criador: TRUE
  GUID: 673C01A018EC5943E055000000000001
  Horrio de Incio: Segunda-Feira, 12 Marco, 2018 15:07:27
  Modo: SCHEMA
  Instncia: BLN18
  Paralelismo Mx: 1
  EXPORT Parmetros do Job:
     CLIENT_COMMAND        system/******** dumpfile=BD01.dmp schemas=SCOTT compression=all nologfile=y
     COMPRESSION           ALL
  IMPORT Parmetros do Job:
  Nome do Parmetro      Valor do Parmetro:
     CLIENT_COMMAND        system/******** dumpfile=BD01.dmp schemas=SCOTT nologfile=y
  Estado: EXECUTING
  Bytes Processados: 0
  Paralelismo Atual: 1
  Contagem de Erros do Job: 0
  Arquivo de Dump: /tmp/BD01.dmp

Worker 1 Status:
  Nome do Processo: DW00
  Estado: EXECUTING
  Esquema de Objeto: SCOTT
  Nome do Objeto: SCOTT
  Tipo de Objeto: SCHEMA_EXPORT/USER
  Paralelismo do Worker: 1

É possível perceber pelo resultado acima que é mostrado tanto o comando que que está executando a importação quanto o comando que foi utilizado para realizar a exportação. Para verificar em que estágio a importação está, poderemos utilizar o comando status conforme seguir.

Import> status

Job: SYS_IMPORT_SCHEMA_01
  Operaão: IMPORT
  Modo: SCHEMA
  Estado: EXECUTING
  Bytes Processados: 0
  Paralelismo Atual: 1
  Contagem de Erros do Job: 0
  Arquivo de Dump: /tmp/BD01.dmp

Worker 1 Status:
  Nome do Processo: DW00
  Estado: EXECUTING
  Esquema de Objeto: SCOTT
  Nome do Objeto: FK_T2_T1
  Tipo de Objeto: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  Objetos Concludos: 110
  Paralelismo do Worker: 1

Por fim, se quisermos continuar com a saída padrão da importação podemos utilizar comando abaixo.

Import> continue_client

terça-feira, 20 de fevereiro de 2018

Verificando a versão, features e options instaladas no banco de dados Oracle

Por Eduardo Legatti

Olá,

Quando instalamos o Oracle e configuramos uma instância do banco de dados Oracle, podemos verificar posteriormente a sua versão, quais "options" e "features" do banco de dados foram instaladas e usadas. Segue abaixo 4 views do dicionário de dados que geralmente utilizo para verificar tais informações em um banco de dados Oracle.

  • V$VERSION: Lista a versão e edição do banco de dados Oracle.
  • V$OPTION: Lista as options e features instaladas no banco de dados. Geralmente as options são licenciadas separadamente e as features costumam já vir no produto que foi instalado (Standard Edition, Enterprise Edition).
  • DBA_REGISTRY: Lista os componentes que foram instalados no banco de dados.
  • DBA_FEATURE_USAGE_STATISTICS: Lista as features e as estatísticas de uso. Essas estatísticas são geradas aptravés de uma procedure não documentada do Oracle.
Segue abaixo um exemplo de saída das views acima executadas em um banco de dados Oracle Standard Edition e um Oracle Enterprise Edition.

-- --------------------------
-- Oracle Standard Edtion  --
-- --------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select * from v$option order by 2,1;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Active Data Guard                                                FALSE
Advanced Compression                                             FALSE
Advanced replication                                             FALSE
Application Role                                                 FALSE
Automatic Storage Management                                     FALSE
Backup Encryption                                                FALSE
Basic Compression                                                FALSE
Bit-mapped indexes                                               FALSE
Block Change Tracking                                            FALSE
Block Media Recovery                                             FALSE
Change Data Capture                                              FALSE
Data Mining                                                      FALSE
Data Redaction                                                   FALSE
Database resource manager                                        FALSE
Deferred Segment Creation                                        FALSE
Duplexed backups                                                 FALSE
Enterprise User Security                                         FALSE
Export transportable tablespaces                                 FALSE
Fast-Start Fault Recovery                                        FALSE
File Mapping                                                     FALSE
Fine-grained access control                                      FALSE
Fine-grained Auditing                                            FALSE
Flashback Database                                               FALSE
Flashback Table                                                  FALSE
Join index                                                       FALSE
Managed Standby                                                  FALSE
Materialized view rewrite                                        FALSE
OLAP                                                             FALSE
Online Index Build                                               FALSE
Online Redefinition                                              FALSE
Oracle Data Guard                                                FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Parallel backup and recovery                                     FALSE
Parallel execution                                               FALSE
Partitioning                                                     FALSE
Point-in-time tablespace recovery                                FALSE
Real Application Clusters                                        FALSE
Real Application Testing                                         FALSE
Result Cache                                                     FALSE
SecureFiles Encryption                                           FALSE
Server Flash Cache                                               FALSE
Spatial                                                          FALSE
SQL Plan Management                                              FALSE
Streams Capture                                                  FALSE
Transparent Data Encryption                                      FALSE
Trial Recovery                                                   FALSE
Unused Block Compression                                         FALSE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
DICOM                                                            TRUE
Flashback Data Archive                                           TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Objects                                                          TRUE
OLAP Window Functions                                            TRUE
Parallel load                                                    TRUE
Plan Stability                                                   TRUE
Proxy authentication/authorization                               TRUE
Sample Scan                                                      TRUE
Transparent Application Failover                                 TRUE
XStream                                                          TRUE

65 linhas selecionadas.

SQL> select name,version,detected_usages,description
  2    from DBA_FEATURE_USAGE_STATISTICS
  3   where rownum<=50 order by 1;

NAME                                               VERSION           DETECTED_USAGES DESCRIPTION
-------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------
Automatic Memory Tuning                            11.2.0.4.0                      0 Automatic Memory Tuning is enabled.
Automatic SGA Tuning                               11.2.0.4.0                     25 Automatic SGA Tuning is enabled.
Backup ZLIB Compression                            11.2.0.4.0                      0 ZLIB compressed backups are being used.
Character Semantics                                11.2.0.4.0                      0 Character length semantics is used in Oracle Database
Character Set                                      11.2.0.4.0                     25 Character set is used in Oracle Database
Client Identifier                                  11.2.0.4.0                      0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions                    11.2.0.4.0                      0 Clusterwide Global Transactions is being used.
Crossedition Triggers                              11.2.0.4.0                      0 Crossedition triggers is being used.
CSSCAN                                             11.2.0.4.0                      0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard                                         11.2.0.4.0                      0 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining                                        11.2.0.4.0                      0 There exist Oracle Data Mining models in the database.
Database Migration Assistant for Unicode           11.2.0.4.0                      0 Database Migration Assistant for Unicode has been used.
Deferred Segment Creation                          11.2.0.4.0                     25 Deferred Segment Creation is being used
Dynamic SGA                                        11.2.0.4.0                      0 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views                                   11.2.0.4.0                      0 Editioning views is being used.
Editions                                           11.2.0.4.0                      0 Editions is being used.
EM Database Control                                11.2.0.4.0                      0 EM Database Control Home Page has been visited at least once.
EM Grid Control                                    11.2.0.4.0                      0 EM Grid Control Database Home Page has been visited at least once.
EM Performance Page                                11.2.0.4.0                      0 EM Performance Page has been visited at least once.
Encrypted Tablespaces                              11.2.0.4.0                      0 Encrypted Tablespaces is enabled.
File Mapping                                       11.2.0.4.0                      0 File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.
Flashback Data Archive                             11.2.0.4.0                      0 Flashback Data Archive, a historical repository of changes to data contained in a table, is used
Flashback Database                                 11.2.0.4.0                      0 Flashback Database, a rewind button for the database, is enabled
Internode Parallel Execution                       11.2.0.4.0                      0 Internode Parallel Execution is being used.
Label Security                                     11.2.0.4.0                      0 Oracle Label Security, that enables label-based access control Oracle applications, is being used.
Locally Managed Tablespaces (system)               11.2.0.4.0                     25 There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user)                 11.2.0.4.0                     25 There exists user tablespaces that are locally managed in the database.
Messaging Gateway                                  11.2.0.4.0                      0 Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.
MTTR Advisor                                       11.2.0.4.0                      0 Mean Time to Recover Advisor is enabled.
Multiple Block Sizes                               11.2.0.4.0                      0 Multiple Block Sizes are being used with this database.
OLAP - Analytic Workspaces                         11.2.0.4.0                      0 OLAP - the analytic workspaces stored in the database.
OLAP - Cubes                                       11.2.0.4.0                      0 OLAP - number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.
Oracle Database Vault                              11.2.0.4.0                      0 Oracle Database Vault is being used
Oracle Managed Files                               11.2.0.4.0                      0 Database files are being managed by Oracle.
Oracle Secure Backup                               11.2.0.4.0                      0 Oracle Secure Backup is used for backups to tertiary storage.
Oracle Text                                        11.2.0.4.0                      0 Oracle Text is being used - there is at least one oracle text index
Parallel SQL DDL Execution                         11.2.0.4.0                      0 Parallel SQL DDL Execution is being used.
Parallel SQL DML Execution                         11.2.0.4.0                      0 Parallel SQL DML Execution is being used.
Parallel SQL Query Execution                       11.2.0.4.0                      0 Parallel SQL Query Execution is being used.
Partitioning (system)                              11.2.0.4.0                     25 Oracle Partitioning option is being used - there is at least one partitioned object created.
Partitioning (user)                                11.2.0.4.0                      0 Oracle Partitioning option is being used - there is at least one user partitioned object created.
PL/SQL Native Compilation                          11.2.0.4.0                      0 PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.
Quality of Service Management                      11.2.0.4.0                      0 Quality of Service Management has been used.
Real Application Clusters (RAC)                    11.2.0.4.0                      0 Real Application Clusters (RAC) is configured.
Recovery Area                                      11.2.0.4.0                      0 The recovery area is configured.
Recovery Manager (RMAN)                            11.2.0.4.0                     24 Recovery Manager (RMAN) is being used to backup the database.
RMAN - Disk Backup                                 11.2.0.4.0                     24 Recovery Manager (RMAN) is being used to backup the database to disk.
RMAN - Tape Backup                                 11.2.0.4.0                      0 Recovery Manager (RMAN) is being used to backup the database to tape.
SQL Monitoring and Tuning pages                    11.2.0.4.0                      0 EM SQL Monitoring and Tuning pages has been visited at least once.
Very Large Memory                                  11.2.0.4.0                      0 Very Large Memory is enabled.

50 linhas selecionadas.

SQL> select comp_id,comp_name,version,status,modified,schema,procedure
  2    from dba_registry
  3   order by 1;

COMP_ID                        COMP_NAME                                          VERSION                        STATUS      MODIFIED             SCHEMA                         PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG                        Oracle Database Catalog Views                      11.2.0.4.0                     VALID       20-JAN-2017 19:29:19 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC                        Oracle Database Packages and Types                 11.2.0.4.0                     VALID       20-JAN-2017 19:29:19 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC
CONTEXT                        Oracle Text                                        11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 CTXSYS                         VALIDATE_CONTEXT
EM                             Oracle Enterprise Manager                          11.2.0.4.0                     VALID       12-JAN-2017 10:24:21 SYSMAN
OWM                            Oracle Workspace Manager                           11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 WMSYS                          VALIDATE_OWM
XDB                            Oracle XML Database                                11.2.0.4.0                     VALID       20-JAN-2017 19:29:20 XDB                            DBMS_REGXDB.VALIDATEXDB

6 linhas selecionadas.


-- ----------------------------
-- Oracle Enterprise Edtion  --
-- ----------------------------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from v$option order by 2,1;

PARAMETER                                                        VALUE
---------------------------------------------------------------- ---------------
Automatic Storage Management                                     FALSE
Data Mining                                                      FALSE
OLAP                                                             FALSE
Oracle Database Vault                                            FALSE
Oracle Label Security                                            FALSE
Real Application Clusters                                        FALSE
Real Application Testing                                         FALSE
Active Data Guard                                                TRUE
Advanced Compression                                             TRUE
Advanced replication                                             TRUE
Application Role                                                 TRUE
Backup Encryption                                                TRUE
Basic Compression                                                TRUE
Bit-mapped indexes                                               TRUE
Block Change Tracking                                            TRUE
Block Media Recovery                                             TRUE
Change Data Capture                                              TRUE
Coalesce Index                                                   TRUE
Connection multiplexing                                          TRUE
Connection pooling                                               TRUE
Database queuing                                                 TRUE
Database resource manager                                        TRUE
Deferred Segment Creation                                        TRUE
DICOM                                                            TRUE
Duplexed backups                                                 TRUE
Enterprise User Security                                         TRUE
Export transportable tablespaces                                 TRUE
Fast-Start Fault Recovery                                        TRUE
File Mapping                                                     TRUE
Fine-grained access control                                      TRUE
Fine-grained Auditing                                            TRUE
Flashback Data Archive                                           TRUE
Flashback Database                                               TRUE
Flashback Table                                                  TRUE
Incremental backup and recovery                                  TRUE
Instead-of triggers                                              TRUE
Java                                                             TRUE
Join index                                                       TRUE
Managed Standby                                                  TRUE
Materialized view rewrite                                        TRUE
Objects                                                          TRUE
OLAP Window Functions                                            TRUE
Online Index Build                                               TRUE
Online Redefinition                                              TRUE
Oracle Data Guard                                                TRUE
Parallel backup and recovery                                     TRUE
Parallel execution                                               TRUE
Parallel load                                                    TRUE
Partitioning                                                     TRUE
Plan Stability                                                   TRUE
Point-in-time tablespace recovery                                TRUE
Proxy authentication/authorization                               TRUE
Result Cache                                                     TRUE
Sample Scan                                                      TRUE
SecureFiles Encryption                                           TRUE
Server Flash Cache                                               TRUE
Spatial                                                          TRUE
SQL Plan Management                                              TRUE
Streams Capture                                                  TRUE
Transparent Application Failover                                 TRUE
Transparent Data Encryption                                      TRUE
Trial Recovery                                                   TRUE
Unused Block Compression                                         TRUE
XStream                                                          TRUE

64 linhas selecionadas.

SQL> select name,version,detected_usages,description
  2    from DBA_FEATURE_USAGE_STATISTICS
  3   where rownum<=50 order by 1;

NAME                                                                             VERSION           DETECTED_USAGES DESCRIPTION
-------------------------------------------------------------------------------- ----------------- --------------- ----------------------------------------------------------------------------------------------------------------------
Active Data Guard - Real-Time Query on Physical Standby                          11.2.0.3.0                      0 Active Data Guard real-time query is enabled on a physical standby
ADDM                                                                             11.2.0.3.0                     11 ADDM has been used.
Advanced Replication                                                             11.2.0.3.0                      0 Advanced Replication has been enabled.
ASO native encryption and checksumming                                           11.2.0.3.0                      0 ASO network native encryption and checksumming is being used.
Audit Options                                                                    11.2.0.3.0                    281 Audit options in use.
Automatic Maintenance - Optimizer Statistics Gathering                           11.2.0.3.0                    247 Automatic initiation of Optimizer Statistics Collection
Automatic Maintenance - Space Advisor                                            11.2.0.3.0                     52 Automatic initiation of Space Advisor
Automatic Maintenance - SQL Tuning Advisor                                       11.2.0.3.0                    210 Automatic initiation of SQL Tuning Advisor
Automatic Segment Space Management (system)                                      11.2.0.3.0                    281 Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user)                                        11.2.0.3.0                    281 Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory                                                   11.2.0.3.0                    281 Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Management                                                     11.2.0.3.0                      0 Automatic Storage Management has been enabled
Automatic Undo Management                                                        11.2.0.3.0                    281 Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository                                                    11.2.0.3.0                      0 A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
AWR Baseline                                                                     11.2.0.3.0                      0 At least one AWR Baseline has been created by the user
AWR Baseline Template                                                            11.2.0.3.0                      0 At least one AWR Baseline Template has been created by the user
AWR Report                                                                       11.2.0.3.0                      9 At least one Workload Repository Report has been created by the user
Backup Encryption                                                                11.2.0.3.0                      0 Encrypted backups are being used.
Backup Rollforward                                                               11.2.0.3.0                      0 Backup Rollforward strategy is being used to backup the database.
Baseline Adaptive Thresholds                                                     11.2.0.3.0                      0 Adaptive Thresholds have been configured.
Baseline Static Computations                                                     11.2.0.3.0                      0 Static baseline statistics have been computed.
Change-Aware Incremental Backup                                                  11.2.0.3.0                    120 Track blocks that have changed in the database.
Character Semantics                                                              11.2.0.3.0                      0 Character length semantics is used in Oracle Database
Character Set                                                                    11.2.0.3.0                    281 Character set is used in Oracle Database
Client Identifier                                                                11.2.0.3.0                      0 Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions                                                  11.2.0.3.0                      0 Clusterwide Global Transactions is being used.
Crossedition Triggers                                                            11.2.0.3.0                      0 Crossedition triggers is being used.
CSSCAN                                                                           11.2.0.3.0                      0 Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Data Guard                                                                       11.2.0.3.0                    278 Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining                                                                      11.2.0.3.0                      0 There exist Oracle Data Mining models in the database.
Data Recovery Advisor                                                            11.2.0.3.0                      0 Data Recovery Advisor (DRA) is being used to repair the database.
Database Migration Assistant for Unicode                                         11.2.0.3.0                      0 Database Migration Assistant for Unicode has been used.
Direct NFS                                                                       11.2.0.3.0                      0 Direct NFS is being used to connect to an NFS server
Dynamic SGA                                                                      11.2.0.3.0                      2 The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
Editioning Views                                                                 11.2.0.3.0                      0 Editioning views is being used.
Editions                                                                         11.2.0.3.0                      0 Editions is being used.
EM Database Control                                                              11.2.0.3.0                      0 EM Database Control Home Page has been visited at least once.
Instance Caging                                                                  11.2.0.3.0                      0 Instance Caging is being used to limit the CPU usage by the database instance.
Locator                                                                          11.2.0.3.0                      0 There is at least one usage of the Oracle Locator index metadata table.
Resource Manager                                                                 11.2.0.3.0                      0 Oracle Database Resource Manager is being used to manage database resources.
Server Flash Cache                                                               11.2.0.3.0                      0 Server Flash Cache is being used with this database.
Server Parameter File                                                            11.2.0.3.0                    281 The server parameter file (SPFILE) was used to startup the database.
Shared Server                                                                    11.2.0.3.0                      0 The database is configured as Shared Server, where one server process can service multiple client programs.
Spatial                                                                          11.2.0.3.0                      0 There is at least one usage of the Oracle Spatial index metadata table.
SQL Access Advisor                                                               11.2.0.3.0                      0 SQL Access Advisor has been used.
SQL Performance Analyzer                                                         11.2.0.3.0                      0 SQL Performance Analyzer has been used.
SQL Tuning Advisor                                                               11.2.0.3.0                     16 SQL Tuning Advisor has been used.
SQL Workload Manager                                                             11.2.0.3.0                      0 SQL Workload Manager has been used.
Tune MView                                                                       11.2.0.3.0                      0 Tune MView has been used.
Undo Advisor                                                                     11.2.0.3.0                      0 Undo Advisor has been used.

50 linhas selecionadas.

SQL> select comp_id,comp_name,version,status,modified,schema,procedure
  2    from dba_registry
  3   order by 1;

COMP_ID                        COMP_NAME                                          VERSION                        STATUS      MODIFIED             SCHEMA                         PROCEDURE
------------------------------ -------------------------------------------------- ------------------------------ ----------- -------------------- ------------------------------ ----------------------------------
CATALOG                        Oracle Database Catalog Views                      11.2.0.3.0                     VALID       03-OUT-2012 22:47:46 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC                        Oracle Database Packages and Types                 11.2.0.3.0                     VALID       03-OUT-2012 22:47:46 SYS                            DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM                            Oracle Workspace Manager                           11.2.0.3.0                     VALID       03-OUT-2012 22:48:14 WMSYS                          VALIDATE_OWM

Postagens populares