Início / user-37187

Gandolf989's questions

Martin Hope
Gandolf989
Asked: 2020-10-01 06:22:09 +0800 CST
  • 0

Eu não fiz muito com o MongoDB. Mas ao tentar entender os dados que temos no MongoDB, faz sentido começar examinando o DDL no banco de dados. Qual é a maneira mais simples de extrair o DDL do banco de dados MongoDB. Posso fazer logon diretamente no servidor Linux e executar comandos, além de ter o cliente Robo 3T.

Obrigado

mongodb ddl
Martin Hope
Gandolf989
Asked: 2020-08-05 11:23:33 +0800 CST
  • 0

Eu escrevi uma função de limpeza que roda no Postgres 9.6. A função passa por todas as tabelas em nosso aplicativo, começando da tabela filho inferior e terminando com a tabela pai superior e limpa com base na data ou no cliente. Quando executamos isso em produção e beta, há outros processos acontecendo ao mesmo tempo e o banco de dados está obtendo bloqueios de bloqueio com base em minhas exclusões não confirmadas. Uma solução possível seria fazer um commit a cada mil ou mais linhas. Mas sou novo no Postgres e não consigo fazer com que o Postgres 9.6 faça commits dentro de um loop. É possível que o Postgres 9.6 não faça isso e talvez o Postgres 12 o faça. Estamos migrando para o Postgres 12.

Existe alguma maneira de fazer os commits funcionarem no Postgres 9.6? Incluí um código de teste para que você possa testar os commits por conta própria. Meu código real é muito mais complicado.

Obrigado

\timing

DROP TABLE IF EXISTS _tmp_test_transactions_table;
CREATE TABLE _tmp_test_transactions_table ( pkey INTEGER );

create or replace function _tmp_test_transactions ( p_number_of_rows INTEGER )
returns int language plpgsql
as
$fun$
DECLARE
   v_counter     INTEGER := 0;
   v_final_count INTEGER := 0;
BEGIN
   START TRANSACTION ISOLATION LEVEL READ COMMITTED;

   LOOP
      v_counter := v_counter + 1;
      EXIT WHEN v_counter > p_number_of_rows;
      
      IF MOD( v_counter, 10 ) = 0
      THEN
         COMMIT;
         START TRANSACTION ISOLATION LEVEL READ COMMITTED;
      END IF;
   END LOOP;

   SELECT COUNT(*) 
     INTO v_final_count
     FROM _tmp_test_transactions_table;

   RAISE NOTICE 'Inserted % rows in the _tmp_test_transactions_table table', v_final_count;
   COMMIT;
END
$fun$;

SELECT _tmp_test_transactions( 100 );
psql:bbyrd_test_transactions.sql:36: ERROR:  unsupported transaction command in PL/pgSQL
CONTEXT:  PL/pgSQL function _tmp_test_transactions(integer) line 6 at SQL statement
Time: 0.473 ms
postgresql transaction
Martin Hope
Gandolf989
Asked: 2018-12-15 14:14:16 +0800 CST
  • 1

Estamos usando 11.2.0.4. Como as conexões Oracle não são criptografadas por padrão e acessam dados de identificação pessoal (PII) em nosso aplicativo, somos obrigados a ir a um ouvinte criptografado. Estou tendo problemas para fazer funcionar. Também estamos usando Transparent Data Encryption (TDE). Alguma sugestão sobre o que estou fazendo de errado? Eu tenho a saída da minha conexão, arquivo de log do ouvinte e arquivo de rastreamento abaixo.

@ > connect connect system/pwd@MYAPP
ERROR:
ORA-29080: Message 29080 not found;  product=RDBMS; facility=ORA

<msg time='2018-12-14T11:10:03.640-05:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MYORACLEVM101.corp.com'
 host_addr='10.1.3.209'>
 <txt>14-DEC-2018 11:10:03 * &lt;unknown connect data&gt; * 12561
 </txt>
</msg>
<msg time='2018-12-14T11:10:03.641-05:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MYORACLEVM101.corp.com'
 host_addr='10.1.3.209'>
 <txt>TNS-12561: TNS:unknown error
 </txt>
</msg>
2018-12-14 17:11:54.058558 : nstoSetupTimeout:ATO enabled for ctx=0x0x17848c0, val=60000(millisecs)
2018-12-14 17:11:54.059097 : nstoUpdateActive:Active timeout is 0 (see nstotyp)
2018-12-14 17:11:54.059407 : nsopen:opening transport...
2018-12-14 17:11:54.059718 : nttcnp:getting sockname
2018-12-14 17:11:54.060053 : nttcnp:getting peername
2018-12-14 17:11:54.060355 : nttcnr:waiting to accept a connection.
2018-12-14 17:11:54.060645 : nttcnr:getting sockname
2018-12-14 17:11:54.060965 : nttcnr:connected on ipaddr 10.1.3.209
2018-12-14 17:11:54.061271 : nttvlser:valid node check on incoming node 10.1.3.209
2018-12-14 17:11:54.061570 : nttvlser:Accepted Entry: 10.1.3.209
2018-12-14 17:11:54.061885 : nttcon:set TCP_NODELAY on 14
2018-12-14 17:11:54.062184 : ntzAllocate:allocating 304 bytes of memory.
2018-12-14 17:11:54.062511 : nsopen:transport is open
2018-12-14 17:11:54.062818 : ntzcontrol:Command = 1125
2018-12-14 17:11:54.063107 : ntzcontrol:negotiated cipher retrieval failed with error 29031
2018-12-14 17:11:54.063459 : nsnainit:inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
2018-12-14 17:11:54.063765 : nsopen:global context check-in (to slot 4) complete
2018-12-14 17:11:54.064066 : nsanswer:deferring connect attempt; at stage 3
2018-12-14 17:11:54.064403 : ntzcontrol:Command = 1123
2018-12-14 17:11:54.064800 : ntzdosecneg:SSL handshake returned "in progress" status
2018-12-14 17:11:54.065124 : ntzcontrol:Command = 1124
2018-12-14 17:11:54.065439 : nsevdansw:exit
2018-12-14 17:11:54.066212 : ntzcontrol:Command = 1123
2018-12-14 17:11:54.068626 : ntzdosecneg:SSL handshake done
2018-12-14 17:11:54.068925 : nsevdansw:exit
2018-12-14 17:11:54.069517 : nscon:doing connect handshake...
2018-12-14 17:11:54.069861 : ntznzosread:read in 238 bytes
2018-12-14 17:11:54.070152 : ntznzosread:no data remaining to be read from SSL buffer.
2018-12-14 17:11:54.070450 : nscon:got NSPTCN packet
2018-12-14 17:11:54.070746 : nsevdansw:exit
2018-12-14 17:11:54.071044 : ntzcontrol:Command = 3
2018-12-14 17:11:54.071367 : ntzcontrol:Command = 7
2018-12-14 17:11:54.071664 : ntzcontrol:unknown command 7 - calling underlying protocol adapter
2018-12-14 17:11:54.071961 : nscon:sending NSPTRD packet
2018-12-14 17:11:54.072299 : nstimarmed:no timer allocated
2018-12-14 17:11:54.072591 : nstoClearTimeout:ATO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.072874 : nstoClearTimeout:STO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.073156 : nstoClearTimeout:RTO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.073450 : nstoClearTimeout:PITO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.073733 : nstoUpdateActive:Active timeout is -1 (see nstotyp)
2018-12-14 17:11:54.074015 : ntzcontrol:Command = 14
2018-12-14 17:11:54.074307 : ntzcontrol:Command = 15
2018-12-14 17:11:54.074615 : nsclose:closing transport
2018-12-14 17:11:54.074929 : nsclose:global context check-out (from slot 4) complete
2018-12-14 17:11:54.075237 : nsgldissolve:Deallocating cxd 0x1784220.
2018-12-14 17:11:54.075793 : nstoSetupTimeout:ATO enabled for ctx=0x0x17848c0, val=60000(millisecs)
2018-12-14 17:11:54.076090 : nstoUpdateActive:Active timeout is 0 (see nstotyp)
2018-12-14 17:11:54.076394 : nsopen:opening transport...
2018-12-14 17:11:54.076709 : nsopen:transport is open
2018-12-14 17:11:54.077031 : nsnainit:inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
2018-12-14 17:11:54.077348 : nsopen:global context check-in (to slot 4) complete
2018-12-14 17:11:54.077647 : nsanswer:deferring connect attempt; at stage 5
2018-12-14 17:11:54.077951 : nscon:doing connect handshake...
2018-12-14 17:11:54.078255 : nscon:got NSPTCN packet
2018-12-14 17:11:54.078547 : nsevdansw:exit
2018-12-14 17:11:54.078865 : nscon:sending NSPTAC packet
2018-12-14 17:11:54.079158 : nscon:connect handshake is complete
2018-12-14 17:11:54.079463 : nscon:nsctxinf[0]=0xd, [1]=0xc
2018-12-14 17:11:54.079823 : nsevdansw:exit
2018-12-14 17:11:54.080151 : nsrdr:got NSPTMK packet
2018-12-14 17:11:54.080460 : nsglauthorized:Authenticated user: 504
2018-12-14 17:11:54.080749 : nstoClearTimeout:ATO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.081033 : nstoUpdateActive:Active timeout is -1 (see nstotyp)
2018-12-14 17:11:54.081326 : nstoControlATO:ATO disabled for ctx=0x0x17848c0
2018-12-14 17:11:54.081644 : nsgcsss:ons_subscriber_status=1
2018-12-14 17:11:54.083110 : nsdo:632 bytes to NS buffer
2018-12-14 17:11:54.083437 : nsdo:466 bytes to NS buffer
2018-12-14 17:11:54.083735 : nstimarmed:no timer allocated
2018-12-14 17:11:54.084031 : nsclose:closing transport
2018-12-14 17:11:54.084342 : nsclose:global context check-out (from slot 4) complete
2018-12-14 17:11:54.084648 : nsgldissolve:Deallocating cxd 0x1784220.

Eu criei a carteira:

 orapki wallet create  -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}" -auto_login
 orapki wallet add     -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}" -dn "CN=`hostname`,OU=EM,O=Organization,L=City,ST=State,C=US" -self_signed -keysize 2048 -sign_alg sha256 -validity 730
 orapki wallet display -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}"
 orapki wallet export  -wallet "${WALLET_DIRECTORY}" -pwd "${LSNRPWD}" -dn "CN=`hostname`,OU=EM,O=Organization,L=City,ST=State,C=US" -cert ${WALLET_DIRECTORY}/`hostname`-${CURR_TIME}-certificate.crt

meu arquivo listener.ora:

MYAPP_encrypted_listener_11gR2 =
( DESCRIPTION =
 ( address_list =
   ( address = (protocol = tcps)(host = MYORACLEVM101.corp.com)(port = 1520))
 ))

SID_LIST_MYAPP_encrypted_listener_11gR2 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = MYAPP)
      (ORACLE_HOME = /home/oracle/app/product/11.2.0.4)
      (SID_NAME = MYAPP)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DB12C)
      (ORACLE_HOME = /home/oracle/app/product/12.2.0.1)
      (SID_NAME = DB12C)
    )
  )

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/admin/MYAPP/wallet)
    )
  )

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/wallet/11g_encrypted_listener)
    )
  )

# ADR_BASE_LISTENER = /home/oracle/app
INBOUND_CONNECT_TIMEOUT_LISTENER = 180
# ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON

ACCEPT_SHA1_CERTS=TRUE
ACCEPT_MD5_CERTS=TRUE
# ADD_SSLV3_TO_DEFAULT=TRUE
SSL_VERSION=1.0
DIAG_ADR_ENABLED_MYAPP_encrypted_listener_11gR2=on
TRACE_LEVEL_MYAPP_encrypted_listener_11gR2=ADMIN
TRACE_TIMESTAMP_MYAPP_encrypted_listener_11gR2=true
LOG_DIRECTORY_MYAPP_encrypted_listener_11gR2=/home/oracle/app

#This parameter should be false as listener is not going to authenticate the clients. It is the server process that authenticates the clients.
SSL_CLIENT_AUTHENTICATION=FALSE

Meu arquivo SQLNET.ora:

TCP.VALIDNODE_CHECKING=NO
ADMIN_RESTRICTIONS_LISTENER = ON
REMOTE_OS_AUTHENT = FALSE
ACCEPT_SHA1_CERTS = TRUE
ACCEPT_MD5_CERTS = TRUE
# ADD_SSLV3_TO_DEFAULT = TRUE
SSL_VERSION = 1.0

SQLNET.AUTHENTICATION_SERVICES = (BEQ, TCPS)
# sqlnet.authentication_required = FALSE
# sqlnet.fallback_authentication = TRUE

NAMES.DIRECTORY_PATH = (TNSNAMES)
SSL_CLIENT_AUTHENTICATION = FALSE

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/admin/MYAPP/wallet)
    )
  )

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/oracle/admin/wallet/11g_encrypted_listener)
    )
  )

ADR_BASE = /home/oracle/app

# TNSPING.TRACE_LEVEL = ADMIN
# TNSPING.TRACE_DIRECTORY = /home/oracle/app/product/12.2.0.1/network/admin/new_listener/trace_dir

Meu arquivo TNSNAMES.ora:

MYAPP_ENCRYPTED =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = MYORACLEVM101.corp.com)(PORT = 1520))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYAPP)
    )
  )

orapki wallet help
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

wallet:
create [-wallet [wallet]] [[-pwd <pwd>] [-auto_login|-auto_login_local]] | [-auto_login_only]
display [-wallet [wallet]] <-summary> [-pwd <pwd>]
change_pwd [-wallet [wallet]] [-oldpwd <oldpwd>] [-newpwd <newpwd>]
add [-wallet [wallet]] <[-keysize [512|1024|2048|4096]] [-dn [dn]]>
     <-self_signed [-validity [days]] | [-valid_from [mm/dd/yyyy] -valid_until [mm/dd/yyyy]]
                   [-serial_file <file_loc>] | [-serial_num <serial_num>]> <-addext_ski>
     <[-cert [filename]] [-trusted_cert|-user_cert]> [-pwd <pwd>] | [-auto_login_only] [-sign_alg <md5|sha1|sha256|sha384|sha512>]
remove [-wallet [wallet]] [-dn [dn]] [-trusted_cert_all|-trusted_cert|-user_cert|-cert_req]
     [-pwd <pwd>] | [-auto_login_only]
export [-wallet [wallet]] [-dn [dn]] [-cert [filename] | -request [filename]] [-pwd <pwd>]
export_trust_chain [-wallet [wallet]] [-certchain [filename]] [-dn [user_cert_dn]] [-pwd <pwd>]
upload [-wallet [wallet]] [-ldap [host:port]] [-user [user]] [-userpwd [userpwd]] [-pwd <pwd>]
download [-wallet [wallet]] [-ldap [host:nonsslport]] [-user [user]] [-userpwd [userpwd]] [-pwd <pwd>]
jks_to_pkcs12 [-wallet [wallet]] [-pwd <pwd>] [-keystore [keystore]] [-jkspwd [jkspwd]]
     <-aliases [alias:alias..]>
pkcs12_to_jks [-wallet [wallet]] [-pwd <pwd>] [-jksKeyStoreLoc <jksKSloc> -jksKeyStorepwd <jksKS_pwd>]
     [-jksTrustStoreLoc <loc> -jksTrustStorepwd <pwd>]
p11_add [-wallet [wallet]] [-p11_lib <pkcs11Lib>] [-p11_tokenlabel <tokenLabel>]
     [-p11_tokenpw <tokenPassphrase>] [-p11_certlabel <certlabel>] [-pwd <pwd>]
p11_verify [-wallet [wallet]] [-pwd <pwd>]
help
oracle oracle-11g-r2