junho 2019 – Rodrigo Silva e Silva

ERROR 3185 (HY000): Can’t find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.

Publicado em 29 de junho de 2019 by rodrigosilvaesilva

Erro ao tentar criar a tabela criptografada

mysql> CREATE TABLE faturamento (empresa varchar(50), fatura int)
		ENGINE=InnoDB ENCRYPTION='Y';
		
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server 
log if a keyring plugin is loaded and initialized successfully.

mysql> CREATE TABLE faturamento (empresa varchar(50), fatura int)

ENGINE=InnoDB ENCRYPTION='Y';

ERROR 3185 (HY000): Can't find master key from keyring, please check in the server

log if a keyring plugin is loaded and initialized successfully.

Verificando plugin instalado

mysql> show variables like '%keyring%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| keyring_operations | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%keyring%';

+--------------------+-------+

| Variable_name | Value |

+--------------------+-------+

| keyring_operations | ON |

+--------------------+-------+

1 row in set (0.00 sec)

Instalando o plugin ‘keyring_file.so’

mysql> install plugin keyring_file soname 'keyring_file.so';
Query OK, 0 rows affected (0.01 sec)

1 2	mysql> install plugin keyring_file soname 'keyring_file.so'; Query OK, 0 rows affected (0.01 sec)

Verificando plugin instalado

mysql> show variables like '%keyring%';
+--------------------+--------------------------------+
| Variable_name      | Value                          |
+--------------------+--------------------------------+
| keyring_file_data  | /var/lib/mysql-keyring/keyring |
| keyring_operations | ON                             |
+--------------------+--------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like '%keyring%';

+--------------------+--------------------------------+

| Variable_name | Value |

+--------------------+--------------------------------+

| keyring_file_data | /var/lib/mysql-keyring/keyring |

| keyring_operations | ON |

+--------------------+--------------------------------+

2 rows in set (0.00 sec)

Criando tabela criptografada

mysql> CREATE TABLE faturamento (empresa varchar(50), fatura int)
    -> ENGINE=InnoDB ENCRYPTION='Y';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE faturamento (empresa varchar(50), fatura int)

-> ENGINE=InnoDB ENCRYPTION='Y';

Query OK, 0 rows affected (0.01 sec)

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Publicado em 29 de junho de 2019 by rodrigosilvaesilva

ERROR 1290 ao exportar para .csv

mysql> SELECT * FROM alunos
		INTO OUTFILE '/tmp/alunos.csv'
		FIELDS ENCLOSED BY '"'
		TERMINATED BY ';'
		ESCAPED BY '"'
		LINES TERMINATED BY '\r\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> SELECT * FROM alunos

INTO OUTFILE '/tmp/alunos.csv'

FIELDS ENCLOSED BY '"'

TERMINATED BY ';'

ESCAPED BY '"'

LINES TERMINATED BY '\r\n';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Edite o arquivo /etc/my.cnf Adicione a linha

secure-file-priv = ""

1	secure-file-priv = ""

Exportando novamente

mysql> SELECT * FROM alunos
    -> INTO OUTFILE '/tmp/alunos.csv'
    -> FIELDS ENCLOSED BY '"'
    -> TERMINATED BY ';'
    -> ESCAPED BY '"'
    -> LINES TERMINATED BY '\r\n';
Query OK, 8000 rows affected (0.00 sec

mysql> SELECT * FROM alunos

-> INTO OUTFILE '/tmp/alunos.csv'

-> FIELDS ENCLOSED BY '"'

-> TERMINATED BY ';'

-> ESCAPED BY '"'

-> LINES TERMINATED BY '\r\n';

Query OK, 8000 rows affected (0.00 sec

Resolvido!

ORA-28374: typed master key not found in wallet

Publicado em 21 de junho de 2019 by rodrigosilvaesilva

Oracle Database 12c Standard Edition ERROR ORA-28374: typed master key not found in wallet ao criar tablespace

CREATE TABLESPACE TESTE DATAFILE  '/u02/app/oracle/oradata/TESTE/ERP/teste.dbf' size 1024M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet

CREATE TABLESPACE TESTE DATAFILE '/u02/app/oracle/oradata/TESTE/ERP/teste.dbf' size 1024M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

ERROR at line 1:

ORA-28374: typed master key not found in wallet

Analisando o status da wallet do CDB$ROOT

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

column WRL_PARAMETER format a40;
column STATUS format a10;
column WALLET_TYPE format a10;

SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;

WRL_PARAMETER                            STATUS     WALLET_TYP
---------------------------------------- ---------- ----------
/u01/app/oracle/admin/EXM/tde_wallet/    OPEN       AUTOLOGIN

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

column WRL_PARAMETER format a40;

column STATUS format a10;

column WALLET_TYPE format a10;

SQL> SELECT wrl_parameter, status, wallet_type FROM v$encryption_wallet;

WRL_PARAMETER STATUS WALLET_TYP

---------------------------------------- ---------- ----------

/u01/app/oracle/admin/EXM/tde_wallet/ OPEN AUTOLOGIN

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE close;

keystore altered.

SET KEYSTORE

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY "j-sIc2#dPKFzoLI6PZ-4" CONTAINER=ALL;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open IDENTIFIED BY "j-sIc2#dPKFzoLI6PZ-4" CONTAINER=ALL;

keystore altered.

Acessando o PDB01

SQL> ALTER SESSION SET CONTAINER = PDB01;

Session altered.

SQL> ALTER SESSION SET CONTAINER = PDB01;

Session altered.

SET KEY

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "j-sIc2#dPKFzoLI6PZ-4" with backup;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "j-sIc2#dPKFzoLI6PZ-4" with backup;

keystore altered.

Create Tablespace

SQL> CREATE TABLESPACE TESTE DATAFILE  '/u02/app/oracle/oradata/EXM/SANKHYA/teste.dbf' size 10M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

Tablespace created.

SQL> CREATE TABLESPACE TESTE DATAFILE '/u02/app/oracle/oradata/EXM/SANKHYA/teste.dbf' size 10M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

Tablespace created.

Resolvido!

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

Publicado em 21 de junho de 2019 by rodrigosilvaesilva

Erro ao tentar conectar do Cliente 11g Servidor TS no Oracle Database 11g Release 11.2.0.4.0 em Real Application Clusters and Automatic Storage Management ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

oracle@bdora-11g ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 19 23:24:37 2019

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

Verificando o nome do serviço 

SQL> select value from v$parameter where name='service_names';

VALUE
--------------------------------------------------------------------------------
BD11G_iad18f.privatesubnet.natvcn.oraclevcn.com

oracle@bdora-11g ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 19 23:24:37 2019

Connected to:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

Verificando o nome do serviço

SQL> select value from v$parameter where name='service_names';

VALUE

--------------------------------------------------------------------------------

BD11G_iad18f.privatesubnet.natvcn.oraclevcn.com

Ajustando tsnames Editar o /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BD11G_IAD18F =
  (DESCRIPTION =
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BD11G_iad18f.privatesubnet.natvcn.oraclevcn.com)
    )
  )

BD11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bdora-11g.privatesubnet.natvcn.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BD11G_iad18f.privatesubnet.natvcn.oraclevcn.com )
    )
  )

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

BD11G_IAD18F =

(DESCRIPTION =

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BD11G_iad18f.privatesubnet.natvcn.oraclevcn.com)

)

BD11G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = bdora-11g.privatesubnet.natvcn.oraclevcn.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BD11G_iad18f.privatesubnet.natvcn.oraclevcn.com )

)

Resolvido!