Oracle - Acessando a view V$SESSION em um ambiente Multitenant

Olá,

Nos ambientes de banco de dados Multitenant a partir do Oracle 12c podemos podemos ter um ou mais bancos de dados plugáveis em uma instância CDB$ROOT. Utilizando o Oracle 18c a seguir, irei mostrar que a view dinâmica de desempenho V$SESSION retorna as sessões de todos os bancos de dados quando executada a partir do banco de dados CDB$ROOT e, quando executada a partir de um banco de dados PDB, irá ser retornado apenas as sessões conectadas apenas neste PDB.

Abaixo é possível perceber que ao conectar no banco de dados CDB$ROOT podemos visualizar todos os datafiles de todos os bancos de dados CDB e PDBs através da view CDB_DATA_FILES. Ao acessar a view V$SESSION também é possível visualizar todas as sessões de todos os bancos de dados.

C:\>sqlplus sys/manager@bdserver:1521/ORCLCDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Qui Mai 2 9:35:50 2019

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

Conectado a:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production


SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
CDB$ROOT

SQL> select file_name,file_id,tablespace_name,bytes,status from CDB_DATA_FILES;

FILE_NAME                                                FILE_ID TABLESPACE_NAME            BYTES STATUS
----------------------------------------------------- ---------- ------------------- ------------ -----------
/opt/oracle/oradata/ORCLCDB/users01.dbf                        7 USERS                    5242880 AVAILABLE
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf                      4 UNDOTBS1               319815680 AVAILABLE
/opt/oracle/oradata/ORCLCDB/system01.dbf                       1 SYSTEM                 891289600 AVAILABLE
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf                       3 SYSAUX                 555745280 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf              9 SYSTEM                 283115520 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf             10 SYSAUX                 387973120 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf            11 UNDOTBS1               104857600 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf              12 USERS                    5242880 AVAILABLE

SQL> select sid,serial#,service_name,username,status,osuser,terminal,program
  2    from v$session
  3   where osuser='eduardo.legatti';

   SID      SERIAL# SERVICE_NAME     USERNAME      STATUS     OSUSER               TERMINAL          PROGRAM
------ ------------ ---------------- ------------- ---------- -------------------- ----------------- ---------------
      38         9924 orclpdb1       SCOTT         INACTIVE   eduardo.legatti      SRVNOT001         sqlplus.exe
     262        16107 ORCLCDB        SYS           INACTIVE   eduardo.legatti      SRVNOT001         sqlplus.exe
     265        28378 orclpdb1       ADAM          INACTIVE   eduardo.legatti      SRVNOT001         sqlplus.exe
     273         7253 orclpdb1       SYS           INACTIVE   eduardo.legatti      SRVNOT001         sqlplus.exe
     275        59715 ORCLCDB        SYS           ACTIVE     eduardo.legatti      SRVNOT001         sqlplus.exe

Ao conectar em um banco de dados PDB específico as views irão mostrar apenas informações do banco de dados PDB específico. 

C:\>sqlplus sys/manager@bdserver:1521/ORCLPDB1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Qui Mai 2 9:48:54 2019

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

Conectado a:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

SQL> select sys_context('USERENV','CON_NAME') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
ORCLPDB1

SQL> select file_name,file_id,tablespace_name,bytes,status from CDB_DATA_FILES;

FILE_NAME                                             FILE_ID TABLESPACE_NAME           BYTES STATUS
--------------------------------------------------- --------- ------------------ ------------ -----------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf           9 SYSTEM                283115520 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf          10 SYSAUX                387973120 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf         11 UNDOTBS1              104857600 AVAILABLE
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf           12 USERS                   5242880 AVAILABLE


SQL> select sid,serial#,service_name,username,status,osuser,terminal,program
  2    from v$session
  3   where osuser='eduardo.legatti';

   SID     SERIAL# SERVICE_NAME    USERNAME     STATUS     OSUSER             TERMINAL      PROGRAM
------ ----------- --------------- ------------ ---------- ------------------ ------------- --------------
    38       9924 orclpdb1         SCOTT        INACTIVE   eduardo.legatti    SRVNOT001     sqlplus.exe
   265      28378 orclpdb1         ADAM         INACTIVE   eduardo.legatti    SRVNOT001     sqlplus.exe
   273       7253 orclpdb1         SYS          ACTIVE     eduardo.legatti    SRVNOT001     sqlplus.exe

Oracle 19c disponível para download nas plataformas Linux x86-64

Olá,

A Oracle disponibilizou para download a versão do Oracle 19c para as plataformas Linux x86-64.

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