MySQL - Gerando arquivos CSV incluindo cabeçalho de colunas da tabela

Olá,

No artigo de Janeiro/2019 eu demonstrei como carregar um arquivo CSV para uma tabela no MySQL. Agora irei demonstrar como gerar um arquivo CSV a partir de uma tabela, incluindo também as colunas da tabela como cabeçalho.

mysql> select * from customer;
+------+------+
| id   | name |
+------+------+
|    1 | Joe  |
|    2 | Jack |
|    3 | Zack |
+------+------+
3 rows in set (0.00 sec)

Utilizarei o template abaixo para geração de um arquivo CSV.

SET @table_name = 'nome_da_tabela';
SET @table_schema = 'nome_do_banco_de_dados';
SET SESSION group_concat_max_len = 1000000;
SET @col_names = (
  SELECT GROUP_CONCAT(QUOTE(`column_name`)) AS columns
  FROM information_schema.columns
  WHERE table_schema = @table_schema
  AND table_name = @table_name);
SET @cols = CONCAT('(SELECT ', @col_names, ')');
SET @query = CONCAT('(SELECT * FROM ', @table_schema, '.', @table_name,
  ' INTO OUTFILE \'/tmp/nome_do_arquivo.csv\'
  FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\')');
SET @sql = CONCAT(@cols, ' UNION ALL ', @query);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Segue exemplo abaixo na qual irei gerar um arquivo CSV da tabela customer.

mysql> SET @table_name = 'customer';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @table_schema = 'bd01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION group_concat_max_len = 1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @col_names = (
    ->   SELECT GROUP_CONCAT(QUOTE(`column_name`)) AS columns
    ->   FROM information_schema.columns
    ->   WHERE table_schema = @table_schema
    ->   AND table_name = @table_name);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @cols = CONCAT('(SELECT ', @col_names, ')');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @query = CONCAT('(SELECT * FROM ', @table_schema, '.', @table_name,
    ->   ' INTO OUTFILE \'/tmp/customer.csv\'
    '>   FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\')');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@cols, ' UNION ALL ', @query);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 4 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> system cat /tmp/customer.csv;
"id","name"
"1","Joe"
"2","Jack"
"3","Zack"

Assistente de instalação para o Oracle Instant Client 18c (x64)

Olá,

Disponibilizei para download o assitente de instalação do Oracle Instant Client 18c (x64). Para quem tiver interesse, será necessário baixar o arquivo instantclient18c.zip (48M) e, após descompactá-lo, bastará apenas clicar no arquivo instantclient18c.exe para iniciar a instalação. No mais, segue os links para quem tiver interesse em baixar a versão do Oracle Instant Client 11g e do Oracle Instant Client 10g.

Obtendo o tamanho dos bancos de dados e das collections no MongoDB

Olá,

Neste artigo irei demonstrar de forma prática como obter o tamanho dos bancos de dados e um mostrar um script que obtém o tamanho das collections de cada banco de dados existente na instância, ordenados de forma descendente pelo tamanho.

O script abaixo apresenta o tamanho que cada banco de dados está ocupando no disco.

PRIMARY> var databases = db.adminCommand("listDatabases").databases;
var totalSize = 0;
databases.forEach(function(d) {
PRIMARY> var totalSize = 0;
PRIMARY> databases.forEach(function(d) {
...     print(d.name + " - " + "Total size on disk: " + d.sizeOnDisk + " bytes (" + (d.sizeOnDisk / 1024 ) / 1024 + "MB)");
... });

bd01 - Total size on disk: 3481600 bytes (3.3203125MB)
bd02 - Total size on disk: 66854875136 bytes (63757.77734375MB)
admin - Total size on disk: 131072 bytes (0.125MB)
local - Total size on disk: 325246976 bytes (310.1796875MB)

O mesmo comando pode ser usado como demonstrado a seguir.

PRIMARY> db.adminCommand({listDatabases: 1})
{
        "databases" : [
                {
                        "name" : "bd01",
                        "sizeOnDisk" : 3481600,
                        "empty" : false
                },
                {
                        "name" : "bd02",
                        "sizeOnDisk" : 66860290048,
                        "empty" : false
                },
                {
                        "name" : "admin",
                        "sizeOnDisk" : 131072,
                        "empty" : false
                },
                {
                        "name" : "local",
                        "sizeOnDisk" : 325246976,
                        "empty" : false
                }
        ],
        "totalSize" : 67189149696,
        "ok" : 1
}

Vale a pena salientar que por padrão a engine WireTiger no MongoDB utiliza a compressão de blocos chamada Snappy mas, que também permite a compressão zlib (similar ao gzip). Portanto, podemos ver abaixo que o resultado storageSize (compactado) é significativamente menor do que a soma de dataSize e indexSize (não compactados).

PRIMARY> var sum = 0; db.getMongo().getDBs()["databases"].forEach(function(x) { sum += db.getMongo().getDB(x.name).stats().dataSize }); print("dataSize: " + sum);
dataSize: 323528024366 (301.30 GB)

PRIMARY> var sum = 0; db.getMongo().getDBs()["databases"].forEach(function(x) { sum += db.getMongo().getDB(x.name).stats().indexSize }); print("indexSize: " + sum);
indexSize: 1709084672 (1.59 GB)

PRIMARY> var sum = 0; db.getMongo().getDBs()["databases"].forEach(function(x) { sum += db.getMongo().getDB(x.name).stats().storageSize }); print("storageSize: " + sum);
storageSize: 65474957312 (60.97)

O gráfico abaixo mostra uma comparação sobre cada compressão.

Para finalizar, segue abaixo um script que retorna o tamanho de cada collection por banco de dados, ordenado por tamanho da collection de forma descendente pelo tamanho (do maior para o menor).

PRIMARY> var mgo = db.getMongo()
... function getReadableFileSizeString(fileSizeInBytes) {
...
...     var i = -1;
...     var byteUnits = [' kB', ' MB', ' GB', ' TB', 'PB', 'EB', 'ZB', 'YB'];
...     do {
...         fileSizeInBytes = fileSizeInBytes / 1024;
...         i++;
...     } while (fileSizeInBytes > 1024);
...
...     return Math.max(fileSizeInBytes, 0.1).toFixed(1) + byteUnits[i];
... };
... function getStatsFor(db){
...     var collectionNames = db.getCollectionNames(), stats = [];
...     collectionNames.forEach(function (n) { stats.push(db.getCollection(n).stats()); });
...     stats = stats.sort(function(a, b) { return b['size'] - a['size']; });
...     for (var c in stats) { print(stats[c]['ns'] + ": " + getReadableFileSizeString(stats[c]['size']) + " (" + getReadableFileSizeString(stats[c]['storageSize']) + ")"); }
... }
... function getAllStats(){
...
... // Especifico
... // var dbName = 'nomedobanco';
... // var db = mgo.getDB(dbName); print('\n    '+db+'\n'); getStatsFor(db)
...
... // Geral
... mgo.getDBNames().forEach(function(name){ var db = mgo.getDB(name); print('\n    '+db+'\n'); getStatsFor(db) })
...
... }
... getAllStats()


    bd01

bd01.collection01: 3.7 MB (1.5 MB)
bd01.collection02: 257.9 kB (112.0 kB)
bd01.collection03: 181.6 kB (84.0 kB)
bd01.collection04: 31.8 kB (44.0 kB)
.
.
.

    bd02

bd02.collection01: 87.4 GB (13.0 GB)
bd02.collection02: 67.5 GB (10.3 GB)
bd02.collection03: 18.7 GB (4.0 GB)
bd02.collection04: 15.2 GB (4.6 GB)
bd02.collection05: 13.0 GB (4.5 GB)
bd02.collection06: 11.9 GB (2.9 GB)
bd02.collection07: 10.8 GB (2.8 GB)
bd02.collection08: 7.4 GB (2.2 GB)
bd02.collection09: 4.6 GB (1.2 GB)
bd02.collection10: 4.5 GB (1.4 GB)
bd02.collection11: 3.3 GB (999.5 MB)
bd02.collection12: 3.2 GB (526.8 MB)
bd02.collection13: 3.0 GB (464.3 MB)
bd02.collection14: 2.8 GB (831.5 MB)
bd02.collection15: 372.6 kB (68.0 kB)
bd02.collection16: 348.8 kB (116.0 kB)
bd02.collection17: 348.1 kB (120.0 kB)
.
.
.
    admin

admin.system.users: 1.5 kB (32.0 kB)
admin.system.version: 0.1 kB (16.0 kB)

    local

local.oplog.rs: 1.0 GB (309.9 MB)
local.startup_log: 21.0 kB (36.0 kB)
local.system.replset: 0.7 kB (36.0 kB)
local.replset.minvalid: 0.1 kB (36.0 kB)
local.replset.election: 0.1 kB (36.0 kB)
local.me: 0.1 kB (32.0 kB)

MySQL - LOAD DATA LOCAL INFILE (Carregando arquivos CSV para uma tabela)

Olá,

Neste artigo irei demonstrar de forma prática como importar para uma tabela de um banco de dados MySQL um arquivo CSV através do comando "LOAD DATA LOCAL INFILE". O arquivo que utilizarei para carregar a tabela será o arquivo a seguir.

$ cat customer.csv
"1","a"
"2","b"
"3","c"
"4","v"
"5","e"
"6","f"
"7","g"
"8","h"
"9","i"
"10","j"

Agora irei conectar na instância do MySQL, especificamente no banco de dados "teste" e executar o comando LOAD para carregar o arquivo customer.csv na tabela customer.

mysql> use teste
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+-----------------+
| Tables_in_teste |
+-----------------+
| customer        |
+-----------------+
1 row in set (0.00 sec)

mysql> describe customer;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| codigo | int(11)      | YES  |     | NULL    |       |
| nome   | varchar(100) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> load data local infile '/tmp/customer.csv' into table customer
    -> fields terminated by ','
    -> enclosed by '"'
    -> lines terminated by '\n'
    -> (codigo,nome);
Query OK, 10 rows affected (0.02 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

Pronto. A tabela foi carregada conforme demonstrado pelo resultado do SQL abaixo.

mysql> select * from customer;
+--------+------+
| codigo | nome |
+--------+------+
|      1 | a    |
|      2 | b    |
|      3 | c    |
|      4 | v    |
|      5 | e    |
|      6 | f    |
|      7 | g    |
|      8 | h    |
|      9 | i    |
|     10 | j    |
+--------+------+
10 rows in set (0.00 sec)

Instalando o Oracle 18c (18.3.0.0.0) em um CentOS 7.4 x86_64 com pacote RPM

Olá,

A Oracle disponibilizou através de um pacote RPM a instalação do Oracle 18c para ambiente Linux (Oracle Linux, Red Hat e similares). Seguindo a documentação disponibilizada, irei realizar a instalação do software Oracle e a configuração de um banco de dados em um ambiente Linux CentOS 7.4 x86_64. O primeiro passo é realizar o download do pacote RPM de instalação no site da Oracle. Irei deixar o pacote RPM no diretório /tmp conforme demonstrado a seguir.

[root@linux tmp]# cd /tmp
[root@linux tmp]# ls -lh oracle-database-ee-18c-1.0-1.x86_64.rpm
-rw-r--r--. 1 root root 3.4G Nov 22 12:30 oracle-database-ee-18c-1.0-1.x86_64.rpm

O próximo passo é realizar o download do pacote pre-install que irá realizar todo o trabalho de preparação do ambiente, como instalar as dependências de pacotes e demais configurações.

[root@linux ~]# curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 18244  100 18244    0     0   2470      0  0:00:07  0:00:07 --:--:--  4826

Uma vez baixado o pacote pre-install, poderemos realizar a instalação do mesmo conforme a seguir.

[root@linux ~]# ls -lh oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 18K Nov 26 18:06 oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

[root@linux ~]# yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
Loaded plugins: fastestmirror, langpacks
Examining oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm: oracle-database-preinstall-18c-1.0-1.el7.x86_64
Marking oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-preinstall-18c.x86_64 0:1.0-1.el7 will be installed
--> Processing Dependency: compat-libcap1 for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
base                                                                                                     | 3.6 kB  00:00:00
extras                                                                                                   | 3.4 kB  00:00:00
packages-microsoft-com-mssql-server-2017                                                                 | 2.9 kB  00:00:00
packages-microsoft-com-prod                                                                              | 2.9 kB  00:00:00
updates                                                                                                  | 3.4 kB  00:00:00
(1/4): extras/7/x86_64/primary_db                                                                        | 205 kB  00:00:06
(2/4): packages-microsoft-com-mssql-server-2017/primary_db                                               |  17 kB  00:00:07
(3/4): packages-microsoft-com-prod/primary_db                                                            | 145 kB  00:00:07
(4/4): updates/7/x86_64/primary_db                                                                       | 6.0 MB  00:00:10
Determining fastest mirrors
 * base: mirror.ufam.edu.br
 * extras: mirror.ci.ifes.edu.br
 * updates: mirror.ci.ifes.edu.br
--> Processing Dependency: compat-libstdc++-33 for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Processing Dependency: ksh for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Processing Dependency: libaio-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Processing Dependency: libstdc++-devel for package: oracle-database-preinstall-18c-1.0-1.el7.x86_64
--> Running transaction check
---> Package compat-libcap1.x86_64 0:1.10-7.el7 will be installed
---> Package compat-libstdc++-33.x86_64 0:3.2.3-72.el7 will be installed
---> Package ksh.x86_64 0:20120801-137.el7 will be installed
---> Package libaio-devel.x86_64 0:0.3.109-13.el7 will be installed
---> Package libstdc++-devel.x86_64 0:4.8.5-28.el7_5.1 will be installed
--> Processing Dependency: libstdc++(x86-64) = 4.8.5-28.el7_5.1 for package: libstdc++-devel-4.8.5-28.el7_5.1.x86_64
--> Running transaction check
---> Package libstdc++.i686 0:4.8.5-16.el7_4.2 will be updated
---> Package libstdc++.x86_64 0:4.8.5-16.el7_4.2 will be updated
---> Package libstdc++.i686 0:4.8.5-28.el7_5.1 will be an update
---> Package libstdc++.x86_64 0:4.8.5-28.el7_5.1 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================
 Package                           Arch        Version             Repository                                          Size
===========================================================================================================================
Installing:
 oracle-database-preinstall-18c    x86_64      1.0-1.el7           /oracle-database-preinstall-18c-1.0-1.el7.x86_64    55 k
Installing for dependencies:
 compat-libcap1                    x86_64      1.10-7.el7          base                                                19 k
 compat-libstdc++-33               x86_64      3.2.3-72.el7        base                                               191 k
 ksh                               x86_64      20120801-137.el7    base                                               885 k
 libaio-devel                      x86_64      0.3.109-13.el7      base                                                13 k
 libstdc++-devel                   x86_64      4.8.5-28.el7_5.1    updates                                            1.5 M
Updating for dependencies:
 libstdc++                         i686        4.8.5-28.el7_5.1    updates                                            317 k
 libstdc++                         x86_64      4.8.5-28.el7_5.1    updates                                            303 k

Transaction Summary
===========================================================================================================================
Install  1 Package  (+5 Dependent packages)
Upgrade             ( 2 Dependent packages)

Total size: 3.2 M
Total download size: 3.2 M
Downloading packages:
Not downloading deltainfo for updates, MD is 679 k and rpms are 620 k
(1/7): compat-libcap1-1.10-7.el7.x86_64.rpm                                                              |  19 kB  00:00:06
(2/7): compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm                                                       | 191 kB  00:00:06
(3/7): libstdc++-4.8.5-28.el7_5.1.i686.rpm                                                               | 317 kB  00:00:07
(4/7): ksh-20120801-137.el7.x86_64.rpm                                                                   | 885 kB  00:00:12
(5/7): libaio-devel-0.3.109-13.el7.x86_64.rpm                                                            |  13 kB  00:00:12
(6/7): libstdc++-4.8.5-28.el7_5.1.x86_64.rpm                                                             | 303 kB  00:00:06
(7/7): libstdc++-devel-4.8.5-28.el7_5.1.x86_64.rpm                                                       | 1.5 MB  00:00:10
---------------------------------------------------------------------------------------------------------------------------
Total                                                                                           193 kB/s | 3.2 MB  00:00:16
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : libstdc++-4.8.5-28.el7_5.1.x86_64                                                                       1/10
  Installing : libstdc++-devel-4.8.5-28.el7_5.1.x86_64                                                                 2/10
  Installing : libaio-devel-0.3.109-13.el7.x86_64                                                                      3/10
  Installing : compat-libcap1-1.10-7.el7.x86_64                                                                        4/10
  Installing : compat-libstdc++-33-3.2.3-72.el7.x86_64                                                                 5/10
  Installing : ksh-20120801-137.el7.x86_64                                                                             6/10
  Installing : oracle-database-preinstall-18c-1.0-1.el7.x86_64                                                         7/10
  Updating   : libstdc++-4.8.5-28.el7_5.1.i686                                                                         8/10
  Cleanup    : libstdc++-4.8.5-16.el7_4.2                                                                              9/10
  Cleanup    : libstdc++-4.8.5-16.el7_4.2                                                                             10/10
  Verifying  : libstdc++-4.8.5-28.el7_5.1.i686                                                                         1/10
  Verifying  : ksh-20120801-137.el7.x86_64                                                                             2/10
  Verifying  : libstdc++-devel-4.8.5-28.el7_5.1.x86_64                                                                 3/10
  Verifying  : oracle-database-preinstall-18c-1.0-1.el7.x86_64                                                         4/10
  Verifying  : compat-libstdc++-33-3.2.3-72.el7.x86_64                                                                 5/10
  Verifying  : compat-libcap1-1.10-7.el7.x86_64                                                                        6/10
  Verifying  : libaio-devel-0.3.109-13.el7.x86_64                                                                      7/10
  Verifying  : libstdc++-4.8.5-28.el7_5.1.x86_64                                                                       8/10
  Verifying  : libstdc++-4.8.5-16.el7_4.2.x86_64                                                                       9/10
  Verifying  : libstdc++-4.8.5-16.el7_4.2.i686                                                                         10/10

Installed:
  oracle-database-preinstall-18c.x86_64 0:1.0-1.el7

Dependency Installed:
  compat-libcap1.x86_64 0:1.10-7.el7
  compat-libstdc++-33.x86_64 0:3.2.3-72.el7
  ksh.x86_64 0:20120801-137.el7
  libaio-devel.x86_64 0:0.3.109-13.el7
  libstdc++-devel.x86_64 0:4.8.5-28.el7_5.1

Dependency Updated:
  libstdc++.i686 0:4.8.5-28.el7_5.1
  libstdc++.x86_64 0:4.8.5-28.el7_5.1

Complete!

Depois que o pacote pre-install foi instalado, poderemos removê-lo já que o mesmo não será mais necessário.

[root@linux ~]# rm oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
rm: remove regular file ‘oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm’? y

Podemos perceber que algumas configurações foram realizadas no arquivo sysctl.conf, entre outros, conforme demonstrado a seguir.

[root@linux ~]# cat /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

# oracle-database-preinstall-18c setting for fs.file-max is 6815744
fs.file-max = 6815744

# oracle-database-preinstall-18c setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 128

# oracle-database-preinstall-18c setting for kernel.shmmni is 4096
kernel.shmmni = 4096

# oracle-database-preinstall-18c setting for kernel.shmall is 1073741824 on x86_64
kernel.shmall = 1073741824

# oracle-database-preinstall-18c setting for kernel.shmmax is 4398046511104 on x86_64
kernel.shmmax = 4398046511104

# oracle-database-preinstall-18c setting for kernel.panic_on_oops is 1 per Orabug 19212317
kernel.panic_on_oops = 1

# oracle-database-preinstall-18c setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144

# oracle-database-preinstall-18c setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304

# oracle-database-preinstall-18c setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144

# oracle-database-preinstall-18c setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576

# oracle-database-preinstall-18c setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2

# oracle-database-preinstall-18c setting for net.ipv4.conf.default.rp_filter is 2
net.ipv4.conf.default.rp_filter = 2

# oracle-database-preinstall-18c setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576

# oracle-database-preinstall-18c setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

Abaixo podemos observar também que foi utilizado o arquivo oracle-database-preinstall-18c.conf dentro do diretório limits.d ao invés do já conhecido arquivo /etc/security/limits.conf.

[root@linux ~]# cat /etc/security/limits.d/oracle-database-preinstall-18c.conf

# oracle-database-preinstall-18c setting for nofile soft limit is 1024
oracle   soft   nofile    1024

# oracle-database-preinstall-18c setting for nofile hard limit is 65536
oracle   hard   nofile    65536

# oracle-database-preinstall-18c setting for nproc soft limit is 16384
# refer orabug15971421 for more info.
oracle   soft   nproc    16384

# oracle-database-preinstall-18c setting for nproc hard limit is 16384
oracle   hard   nproc    16384

# oracle-database-preinstall-18c setting for stack soft limit is 10240KB
oracle   soft   stack    10240

# oracle-database-preinstall-18c setting for stack hard limit is 32768KB
oracle   hard   stack    32768

# oracle-database-preinstall-18c setting for memlock hard limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90 % of RAM
oracle   hard   memlock    134217728

# oracle-database-preinstall-18c setting for memlock soft limit is maximum of 128GB on x86_64 or 3GB on x86 OR 90% of RAM
oracle   soft   memlock    134217728

Vale a pena salientar que o pacote pre-install não alterou o SELinux, então irei alterar manualmente o arquivo /etc/selinux/config modificando a diretiva SELINUX para disabled.

[root@linux ~]# cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted

A seguir irei instalar o pacote RPM do software Oracle que está no diretório /tmp.

[root@linux tmp]# cd /tmp
[root@linux tmp]# yum -y localinstall oracle-database-ee-18c-1.0-1.x86_64.rpm

Loaded plugins: fastestmirror, langpacks
Examining oracle-database-ee-18c-1.0-1.x86_64.rpm: oracle-database-ee-18c-1.0-1.x86_64
Marking oracle-database-ee-18c-1.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-database-ee-18c.x86_64 0:1.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================
 Package                     Arch             Version             Repository                                           Size
===========================================================================================================================
Installing:
 oracle-database-ee-18c      x86_64           1.0-1               /oracle-database-ee-18c-1.0-1.x86_64                7.8 G

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

Total size: 7.8 G
Installed size: 7.8 G
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : oracle-database-ee-18c-1.0-1.x86_64                                                                      1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure a sample Oracle Database you can execute the following service configuration script as root: /etc/init.d/oracledb_ORCLCDB-18c configure
  Verifying  : oracle-database-ee-18c-1.0-1.x86_64                                                                      1/1

Installed:
  oracle-database-ee-18c.x86_64 0:1.0-1

Complete!

Pronto. O software Oracle foi instalado. Vale a pena salientar que foi disponibilizado um arquivo de configuração que pode ser utilizado para criar uma instância de banco de dados chamada ORCLCDB. Essas configurações podem ser visualizadas no arquivo /etc/sysconfig/oracledb_ORCLCDB-18c.conf.

[root@linux tmp]# cat /etc/sysconfig/oracledb_ORCLCDB-18c.conf
#This is a configuration file to setup the Oracle Database.
#It is used when running '/etc/init.d/oracledb_ORCLCDB configure'.
#Please use this file to modify the default listener port and the
#Oracle data location.

# LISTENER_PORT: Database listener
LISTENER_PORT=1521

# ORACLE_DATA_LOCATION: Database oradata location
ORACLE_DATA_LOCATION=/opt/oracle/oradata

# EM_EXPRESS_PORT: Oracle EM Express listener
EM_EXPRESS_PORT=5500

Para criar a instância de banco de dados, irei realizar a execução conforme a seguir.

[root@linux tmp]# /etc/init.d/oracledb_ORCLCDB-18c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at: /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

Database configuration completed successfully. The passwords were auto generated, you must change them by connecting to the database using 'sqlplus / as sysdba' as the oracle user.

Pronto. A instância ORCLCDB foi criada com sucesso e já foi inicializada.

[root@linux tmp]# ps aux | grep smon
oracle   12326  0.0  1.2 1667312 35684 ?       Ss   19:18   0:00 ora_smon_ORCLCDB

Para facilitar a conexão com o banco de dados, já que o pacote pre-install não criou as variáveis de ambiente, irei configurar essas variáveis no arquivo .bash_profile conforme a seguir.

[oracle@linux ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/18c/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:$PATH
export ORACLE_TERM=vt100
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_DATE_FORMAT=DD/MM/YYYY

Caso o listener não esteja inicializado, podemos iniciá-lo caso precisemos fazer uma conexão a partir de um computador remoto.

[oracle@linux ~]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 26-NOV-2018 23:35:29

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Starting /opt/oracle/product/18c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/linux/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=linux)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                26-NOV-2018 23:35:30
Uptime                    0 days 0 hr. 0 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/18c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/linux/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

Por fim, podemos iniciar as instância e conectar no banco de dados.

[oracle@linux ~]$ export ORACLE_SID=ORCLCDB
[oracle@linux ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Nov 26 23:36:08 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1207958368 bytes
Fixed Size                  8895328 bytes
Variable Size             855638016 bytes
Database Buffers          335544320 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.

SQL> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> select a.con_id,
  2         a.name,
  3         a.dbid,
  4         b.status,
  5         a.open_mode,
  6         a.total_size
  7   from v$pdbs a, dba_pdbs b
  8  where a.con_id=b.pdb_id;

    CON_ID NAME                           DBID STATUS     OPEN_MODE  TOTAL_SIZE
---------- ------------------------ ---------- ---------- ---------- ----------
         2 PDB$SEED                 3023932009 NORMAL     READ ONLY   840957952
         3 ORCLPDB1                 2687189862 NORMAL     MOUNTED     846200832

SQL> alter pluggable database ORCLPDB1 open read write;

Pluggable database altered.

Caso precise desinstalar o pacote RPM, basta seguir os passos abaixo logado como root.

$ yum -y remove oracle-database-ee-18c

Caso o yum detecte que existe algum componente ativo no ambiente como banco de dados ou listener, o processo de desinstalação será interrompido e, neste caso, a remoção desses componentes deverá ser feito manualmente. Será necessário logar como oracle.

$ cd $ORACLE_HOME/bin 
$ ./dbca

$ cd $ORACLE_HOME/bin 
$ ./netca

Após isso, precisaremos logar novamente como root e executar o comando yum a seguir.

$ yum -y remove oracle-database-ee-18c

Erro RMAN-06214 ao deletar snapshot de controlfile (snapcf_SID.f)

Olá,

Pode acontecer em alguns casos do RMAN detectar que existe um arquivo de backup obsoleto, ou seja, que não é necessário para a janela de recuperação em vigor, mas o mesmo não consegue ser removido ao apresentar o erro RMAN-06214. Geralmente é apresentado o erro RMAN-06207 que identifica que um arquivo de backup não pode ser removido devido ao status do arquivo não estar atualizado. Na maioria das vezes o comando CROSSCHECK resolve o problema, mas quando o arquivo é um snapshot de controlfile, deveremos realizar outra operação conforme demonstrado a seguir.

RMAN> report obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 4 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     3     25/09/2013 15:17:09 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f
Backup Set           14533  03/11/2018 00:17:17
  Backup Piece       14533  03/11/2018 00:17:17 /backup/flash_recovery_area/BD01/autobackup/2018_11_03/o1_mf_s_989972236_fwo7kx28_.bkp
Backup Set           14535  05/11/2018 00:15:54
  Backup Piece       14535  05/11/2018 00:15:54 /backup/flash_recovery_area/BD01/backupset/2018_11_05/o1_mf_ncnn1_LEVEL_1_DIARIO_fwtdptjw_.bkp
Backup Set           14536  05/11/2018 00:15:57
  Backup Piece       14536  05/11/2018 00:15:57 /backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990144957_fwtdpxgs_.bkp
Backup Set           14538  05/11/2018 00:17:04
  Backup Piece       14538  05/11/2018 00:17:04 /backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990145023_fwtdrzv3_.bkp
Backup Set           14540  05/11/2018 00:17:10
  Backup Piece       14540  05/11/2018 00:17:10 /backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990145030_fwtds6gw_.bkp
Backup Set           14541  05/11/2018 00:17:27
  Backup Piece       14541  05/11/2018 00:17:27 /backup/flash_recovery_area/BD01/backupset/2018_11_05/o1_mf_ncnnf_CONTROLFILE_fwtdsptf_.bkp
Backup Set           14577  06/11/2018 02:16:20
  Backup Piece       14577  06/11/2018 02:16:20 /backup/flash_recovery_area/BD01/backupset/2018_11_06/o1_mf_nnsnf_SPFILE_fx559mtp_.bkp


RMAN> delete noprompt obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     3     25/09/2013 15:17:09 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f
Backup Set           14533  03/11/2018 00:17:17
  Backup Piece       14533  03/11/2018 00:17:17 /backup/flash_recovery_area/BD01/autobackup/2018_11_03/o1_mf_s_989972236_fwo7kx28_.bkp
Backup Set           14535  05/11/2018 00:15:54
  Backup Piece       14535  05/11/2018 00:15:54 /backup/flash_recovery_area/BD01/backupset/2018_11_05/o1_mf_ncnn1_LEVEL_1_DIARIO_fwtdptjw_.bkp
Backup Set           14536  05/11/2018 00:15:57
  Backup Piece       14536  05/11/2018 00:15:57 /backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990144957_fwtdpxgs_.bkp
Backup Set           14538  05/11/2018 00:17:04
  Backup Piece       14538  05/11/2018 00:17:04 /backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990145023_fwtdrzv3_.bkp
Backup Set           14540  05/11/2018 00:17:10
  Backup Piece       14540  05/11/2018 00:17:10 /backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990145030_fwtds6gw_.bkp
Backup Set           14541  05/11/2018 00:17:27
  Backup Piece       14541  05/11/2018 00:17:27 /backup/flash_recovery_area/BD01/backupset/2018_11_05/o1_mf_ncnnf_CONTROLFILE_fwtdsptf_.bkp
Backup Set           14577  06/11/2018 02:16:20
  Backup Piece       14577  06/11/2018 02:16:20 /backup/flash_recovery_area/BD01/backupset/2018_11_06/o1_mf_nnsnf_SPFILE_fx559mtp_.bkp
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/autobackup/2018_11_03/o1_mf_s_989972236_fwo7kx28_.bkp RECID=14533 STAMP=989972237
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/backupset/2018_11_05/o1_mf_ncnn1_LEVEL_1_DIARIO_fwtdptjw_.bkp RECID=14535 STAMP=990144954
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990144957_fwtdpxgs_.bkp RECID=14536 STAMP=990144957
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990145023_fwtdrzv3_.bkp RECID=14538 STAMP=990145023
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/autobackup/2018_11_05/o1_mf_s_990145030_fwtds6gw_.bkp RECID=14540 STAMP=990145030
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/backupset/2018_11_05/o1_mf_ncnnf_CONTROLFILE_fwtdsptf_.bkp RECID=14541 STAMP=990145046
deleted backup piece
backup piece handle=/backup/flash_recovery_area/BD01/backupset/2018_11_06/o1_mf_nnsnf_SPFILE_fx559mtp_.bkp RECID=14577 STAMP=990497780
Deleted 7 objects

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f

Após a tentativa de remoção dos arquivos obsoletos, ocorreu o erro RMAN-06207 acompanhado do erro RMAN-06214 indicando que o datafile copy snapcf_BD01.f não pode ser removido devido a um problema no status do arquivo. Para resolver o problema precisaremos atualizar o status do arquivo de forma que o mesmo se torne expirado.

RMAN> crosscheck copy;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=232 device type=DISK
specification does not match any datafile copy in the repository
validation failed for control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f RECID=3 STAMP=827075829
validation succeeded for archived log
archived log file name=/backup/flash_recovery_area/BD01/archivelog/2018_11_06/o1_mf_1_70882_fx4xmpkg_.arc RECID=88108 STAMP=990489943
validation succeeded for archived log
archived log file name=/backup/flash_recovery_area/BD01/archivelog/2018_11_06/o1_mf_1_70883_fx558m8z_.arc RECID=88109 STAMP=990497778
validation succeeded for archived log
archived log file name=/backup/flash_recovery_area/BD01/archivelog/2018_11_06/o1_mf_1_70884_fx558n5j_.arc RECID=88110 STAMP=990497778
validation succeeded for archived log
archived log file name=/backup/flash_recovery_area/BD01/archivelog/2018_11_06/o1_mf_1_70885_fx5h01op_.arc RECID=88111 STAMP=990507749
validation succeeded for archived log
archived log file name=/backup/flash_recovery_area/BD01/archivelog/2018_11_06/o1_mf_1_70886_fx5v4xnd_.arc RECID=88112 STAMP=990520190
validation succeeded for archived log
archived log file name=/backup/flash_recovery_area/BD01/archivelog/2018_11_06/o1_mf_1_70887_fx5v56tr_.arc RECID=88113 STAMP=990520199
Crosschecked 7 objects

Pronto. Agora basta remover o arquivo expirado conforme demonstrado abaixo.

RMAN> delete expired copy;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=232 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any archived log in the repository
List of Control File Copies
===========================

Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
3       X 25/09/2013      7840543439301 25/09/2013
        Name: /u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f
        Tag: TAG20130925T151707

Do you really want to delete the above objects (enter YES or NO)? y
deleted control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f RECID=3 STAMP=827075829
Deleted 1 EXPIRED objects

SQL Server - Delayed Transaction Durability (Asynchronous Commit)

Olá,

No artigo de Abril/2015 foi abordado o uso do COMMIT Assíncrono no Oracle de forma a acelerar instruções de INSERT no banco de dados. Da mesma forma que no Oracle, outros SGBDs também possuem esse recurso:

• Oracle 10g R2 - COMMIT_WRITE

• Oracle 11g/12c - COMMIT_WAIT

• MySQL - innodb_flush_log_at_trx_commit

• SQL Server 2014 - Delayed Transaction Durability

Neste artigo irei abordar este recurso no SQL Server chamado de "Delayed Transaction Durability" introduzido no SQL Server 2014. Por padrão, no SQL Server as transações são consideradas "Full transaction durability", ou seja, elas são síncronas de forma que quando um COMMIT é executado, o SQL Server assegura que a transação é gravada no arquivo transaction log antes da mesma ser finalizada. Ao alterar para o modo "Delayed transaction durability", a mesma se torna assíncrona, ou seja, a transação é escrita em um buffer de memória e descarregada no arquivo transaction log somente quando este buffer ficar cheio ou através da execução do procedimento sp_flush_log. Essa opção reduz a latência das transações de forma que a mesma seja mais rápida para o usuário. 

Segue abaixo um exemplo prático na qual serão inseridos em uma tabela cerca de um milhão de registros. O primeiro exemplo usando o Full transaction durability e o segundo usando o "Delayed Transaction Durability".

[root ~]# sqlcmd -S localhost -U sa -Padmin

1> use bd01
2> GO
Changed database context to 'bd01'.

1>   SET NOCOUNT ON
2>   -- insert de 1.000.000 de registros
3>   DECLARE @i INT = 1, @start DATETIME = GETDATE();
4>   WHILE @i <= 1000000 BEGIN
5>        BEGIN TRANSACTION;
6>        INSERT INTO teste VALUES (GETDATE());
7>        COMMIT;
8>        SET @i = @i + 1;
9>   END
10>   -- tempo de execução em segundos
11>   SELECT DATEDIFF(ss, @start, GETDATE()) TIME;
12>   GO

TIME
-----------
       1508

Após a execução do bloco TSQL acima, a mesma finalizou após 1508 segundos (25 minutos). Analisando o o I/O realizado pode-se perceber um uso excessivo no disco nv3n1, utilizado para armazenar o arquivo transaction log (*.ldf) do banco de dados.

Device:       rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nv0n1           0.00     1.00    0.00    3.00     0.00     0.04    29.33     0.00    0.00    0.00    0.00   0.00   0.00
nv1n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
nv2n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
nv3n1           0.00   659.00    0.00 1977.00     0.00    10.30    10.67     0.92    0.47    0.00    0.47   0.47  96.30
nv4n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

Segue abaixo o exemplo com uso do recurso "Delayed transaction durability" (COMMIT Assíncrono).

1>   ALTER DATABASE bd01 SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT;
2>   GO

1>   SET NOCOUNT ON
2>   -- insert de 1.000.000 de registros
3>   DECLARE @i INT = 1, @start DATETIME = GETDATE();
4>   WHILE @i <= 1000000 BEGIN
5>        BEGIN TRANSACTION;
6>        INSERT INTO teste VALUES (GETDATE());
7>        COMMIT WITH (DELAYED_DURABILITY = ON);
8>        SET @i = @i + 1;
9>   END
10>   -- tempo de execução em segundos
11>   SELECT DATEDIFF(ss, @start, GETDATE()) TIME;
12>   GO

TIME
-----------
         11

Após a execução do bloco TSQL acima, a mesma finalizou após 11 segundos apenas.