我对 MongoDB 做的不多。但是在尝试理解我们在 MongoDB 中拥有的数据时，从查看数据库中的 DDL 开始是有意义的。将 DDL 从 MongoDB 数据库中拉出的最简单方法是什么。我可以直接登录 Linux 服务器并运行命令，而且我还有 Robo 3T 客户端。
谢谢
我编写了一个在 Postgres 9.6 中运行的清除函数。该函数遍历我们应用程序中的所有表，从最底层的子表开始，到最顶层的父表结束，并根据日期或客户进行清除。当我们在生产和测试版中运行它时，会同时发生其他进程，并且数据库正在根据我未提交的删除获得阻塞锁。一种可能的解决方案是每隔一千左右行进行一次提交。但是我是 Postgres 的新手，我无法让 Postgres 9.6 在循环中进行提交。Postgres 9.6 可能不会这样做，而 Postgres 12 可能会。我们正在迁移到 Postgres 12。
有没有办法让提交在 Postgres 9.6 中工作？我包含了一些测试代码，以便您可以自己测试提交。我的实际代码要复杂得多。
谢谢
\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
我们正在使用 11.2.0.4。由于 Oracle 连接在默认情况下未加密，并且在我们的应用程序中访问个人身份 (PII) 数据，因此我们需要使用加密的侦听器。我无法让它工作。我们还使用透明数据加密 (TDE)。关于我做错了什么有什么建议吗？下面是我的连接、侦听器日志文件和跟踪文件的输出。
@ > 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 * <unknown connect data> * 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.
我创建了钱包：
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
我的 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
我的 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
我的 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