集群环境下的wallet和ORA-28365: wallet is not open
今天客户的某库在重启后1个节点执行以下查询报ORA-28365: wallet is not open错误，另一个节点可以正常执行查找数据。
1节点表空间使用wallet解密失败
alert：
2节点可用正常访问数据
在1节点执行
Altersystem set encryption wallet open identified by "password";
还是没有打开wallet
查找gv$encryption_wallet
SQL>select * from gv$encryption_wallet
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- -------------------- ---------------------------------------- ------------------
2 file /oracle/product/11.2.0/wallet OPEN
1 file /oracle/product/11.2.0/admin/ynras1/wallet CLOSED
1节点wallet没有打开，2节wallet点正常
两个节点的smon的环境变量值，ORACLE_HOME是一致的，没有找到ORACLE_BASE
ybsera01:/oracle>ps eauwww 33884060
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
oracle 33884060 0.0 0.0 201620 121772 - A 01:09:30 0:09 ora_smon_ynras11 _=/grid/product/11.2.0/bin/oraagent.bin LANG=en_US LOGIN=oracle __CLSAGENT_INCARNATION=4 _ORA_AGENT_ACTION=TRUE VISUAL=vi PATH= CRS_LIMIT_NPROC=%CRS_LIMIT_NPROC% NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 CRS_LIMIT_OPENFILE=65536 CRS_LIMIT_MEMLOCK=unlimited __CLSAGENT_USER_NAME=oracle ORA_DAEMON_LOGGING_LEVELS=AGENT=1,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=1,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1 LC__FASTMSG=true ENV_FILE=/grid/product/11.2.0/crs/install/s_crsconfig_ybsera01_env.txt EDITOR=vi GIPCD_PASSTHROUGH=false __CRSD_AGENT_NAME=/grid/product/11.2.0/bin/oraagent_oracle EXTSHM=OFF LOCPATH=/usr/lib/nls/loc [email protected] CRS_LIMIT_STACK=2048 __IS_HASD_AGENT= __CRSD_CONNECT_STR=(ADDRESS=(PROTOCOL=IPC)(KEY=CRSD_IPC_SOCKET_11)) ODMDIR=/etc/objrepos ORASYM=/grid/product/11.2.0/bin/oraagent.bin ORA_CRS_HOME=/grid/product/11.2.0 ORA_DAEMON_TRACING_LEVELS=AGENT=0,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=0,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1 __CLSAGENT_LOG_NAME=ora.database.type_oracle ORACLE_HOME=/oracle/product/11.2.0__CLSAGFW_TYPE_NAME=ora.database.type __CRSD_MSG_FRAME_VERSION=2 __CLSAGENT_LOGDIR_NAME=crsd PWD=/ CRS_LIMIT_CORE=unlimited TZ=BEIST-8 CRF_HOME=/grid/product/11.2.0 AIXTHREAD_SCOPE=S RT_GRQ= ORACLE_SID=ynras11 ORA_NET2_DESC=22,25 MPROTECT_TXT=ON ORACLE_SPAWNED_PROCESS=1 NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat LIBPATH=/oracle/product/11.2.0/lib LD_LIBRARY_PATH=
ybsera02:/oracle/app/diag/rdbms/ynras1/ynras12/trace>ps eauwww 22085910
USER PID %CPU %MEM SZ RSS TTY STAT STIME TIME COMMAND
oracle 22085910 0.0 0.0 199968 275112 - A 01:50:47 0:03 ora_smon_ynras12 _=/grid/product/11.2.0/bin/oraagent.bin LANG=en_US LOGIN=root __CLSAGENT_INCARNATION=3 _ORA_AGENT_ACTION=TRUE VISUAL=vi SSH_TTY=/dev/pts/4 PATH= CRS_LIMIT_NPROC=%CRS_LIMIT_NPROC% NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 CRS_LIMIT_OPENFILE=65536 CRS_LIMIT_MEMLOCK=unlimited __CLSAGENT_USER_NAME=oracle ORA_DAEMON_LOGGING_LEVELS=AGENT=1,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=1,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1 COPERR=/opt/teradata/client/13.0/lib LC__FASTMSG=true ENV_FILE=/grid/product/11.2.0/crs/install/s_crsconfig_ybsera02_env.txt EDITOR=vi GIPCD_PASSTHROUGH=false LOGNAME=root COPLIB=/opt/teradata/client/13.0/lib __CRSD_AGENT_NAME=/grid/product/11.2.0/bin/oraagent_oracle MAIL=/usr/spool/mail/root EXTSHM=OFF LOCPATH=/usr/lib/nls/loc PS1=[ybsera02:$LOGIN:$PWD] GRID_HOME=/grid/product/11.2.0 USER=root AUTHSTATE=compat LC_MESSAGES= CRS_LIMIT_STACK=2048 __IS_HASD_AGENT= TD_ICU_DATA=/opt/teradata/client/13.0/tdicu/lib __CRSD_CONNECT_STR=(ADDRESS=(PROTOCOL=IPC)(KEY=CRSD_IPC_SOCKET_11)) SHELL=/usr/bin/ksh ODMDIR=/etc/objrepos ORASYM=/grid/product/11.2.0/bin/oraagent.bin ORA_CRS_HOME=/grid/product/11.2.0 HOME=/ ORA_DAEMON_TRACING_LEVELS=AGENT=0,AGFW=0,CLSFRAME=0,CLSVER=0,CLUCLS=0,COMMCRS=0,COMMNS=0,CRSAPP=0,CRSCCL=0,CRSCEVT=0,CRSCOMM=0,CRSD=0,CRSEVT=0,CRSMAIN=0,CRSOCR=0,CRSPE=0,CRSPLACE=0,CRSRES=0,CRSRPT=0,CRSRTI=0,CRSSE=0,CRSSEC=0,CRSTIMER=0,CRSUI=0,CSSCLNT=0,SuiteTes=0,UiServer=0,OCRAPI=1,OCRCLI=1,OCRSRV=1,OCRMAS=1,OCRMSG=1,OCRCAC=1,OCRRAW=1,OCRUTL=1,OCROSD=1,OCRASM=1 SSH_CLIENT=10.168.3.87 43139 22 SSH_CONNECTION=10.168.3.87 43139 10.173.253.15 22 TERM=vt100 __CLSAGENT_LOG_NAME=ora.database.type_oracle MAILMSG=[YOU HAVE NEW MAIL] ORACLE_HOME=/oracle/product/11.2.0__CLSAGFW_TYPE_NAME=ora.database.type __CRSD_MSG_FRAME_VERSION=2 __CLSAGENT_LOGDIR_NAME=crsd PWD=/ CRS_LIMIT_CORE=unlimited TZ=BEIST-8 CRF_HOME=/grid/product/11.2.0 AIXTHREAD_SCOPE=S RT_GRQ= A__z=! LOGNAME ORACLE_SID=ynras12 ORA_NET2_DESC=22,25 MPROTECT_TXT=ON ORACLE_SPAWNED_PROCESS=1 NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat LIBPATH=/oracle/product/11.2.0/lib LD_LIBRARY_PATH=
昨晚两个实例都重启过
SQL> select inst_id,STARTUP_TIME,status from gv$instance;
INST_ID STARTUP_TIME STATUS
---------- ------------------- ------------
2 2018-04-27 01:50:43 OPEN
1 2018-04-27 01:09:23 OPEN
alert中的启动日志，可以找到ORACLE_BASE的问题
ORACLE_HOME = /oracle/product/11.2.0
System name: AIX
Node name: ybsera01
Release: 1
Version: 6
...
Post SMON to start 1st pass IR
Fix write in gcs resources
Reconfiguration complete
Fri Apr 27 01:09:32 2018
LCK0 started with pid=35, OS id=5701998
Starting background process RSMN
Fri Apr 27 01:09:32 2018
RSMN started with pid=36, OS id=32180142
Fri Apr 27 01:09:32 2018
ORACLE_BASE not set in environment. It is recommended
that ORACLE_BASE be set in the environment
Reusing ORACLE_BASE from an earlier startup = /oracle/app
Fri Apr 27 01:09:33 2018
ALTER SYSTEM SET local_listener=' (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.173.253.16)(PORT=1521))))' SCOPE=MEMORY SID='ynras11';
ALTER DATABASE MOUNT /* db agent *//* {1:53629:335} */
This instance was first to mount
...
Errors in file /oracle/app/diag/rdbms/ynras1/ynras11/trace/ynras11_j004_16058024.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1
Fri Apr 27 01:10:49 2018
XDB installed.
XDB initialized.
Fri Apr 27 01:10:54 2018
kcbztek_get_tbskey: decrypting encrypted key for tablespace 23 without opening the wallet
Errors in file /oracle/app/diag/rdbms/ynras1/ynras11/trace/ynras11_j000_57147462.trc:
ORA-12012: ִҵ 4
ORA-28365: Wallet δ
ORA-06512: YNNGODB.BILL_COUNT", line 21
ORA-06512: ine 1
Fri Apr 27 01:17:45 2018
Starting background process SMCO
Fri Apr 27 01:17:45 2018
SMCO started with pid=256, OS id=21366546
Fri Apr 27 01:26:56 2018
kcbztek_get_tbskey: decrypting encrypted key for tablespace 23 without opening the wallet
Errors in file /oracle/app/diag/rdbms/ynras1/ynras11/trace/ynras11_j001_13631616.trc:
ORA-12012: ִҵ 4
ORA-28365: Wallet δ
ORA-06512: YNNGODB.BILL_COUNT", line 21
ORA-06512: ine 1
查看集群环境的配置
ybsera01:/grid>crsctl stat res ora.ynras1.db -p
...
OFFLINE_CHECK_INTERVAL=0
ONLINE_RELOCATION_TIMEOUT=0
ORACLE_HOME=/oracle/product/11.2.0
ORACLE_HOME_OLD=
...
集群db资源配置里只有ORACLE_HOME，没有ORACLE_BASE
查看历史命令，在启动数据库时使用的是srvctl命令，在grid环境中启动
ybsera01:/grid>more .sh_history
srvctl start database -d ynras1
查看sqlnet.ora的配置，wallet路径配置正常
ybsera01:/oracle/product/11.2.0/network/admin>more sqlnet.ora
NCRYPTION_WALLET_LOCATION=
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /oracle/product/11.2.0/wallet)))
SQLNET.INBOUND_CONNECT_TIMEOUT=120
SQLNET.EXPIRE_TIME=10
搜索到mos中bug文档，我们检查了TNS_ADMIN的配置
MOS:Bug 10113110 : ORA-28382: GLOBAL WALLET OPERATION IN RAC FAILED
检查TNS_ADMIN，正确配置了TNS_ADMIN
ybsera01:/oracle/app/diag/rdbms/ynras1/ynras11/trace>env|grep TNS
TNS_ADMIN=/oracle/product/11.2.0/network/admin
gv$encryption_wallet中的目录不存在
创建gv$encryption_wallet中的目录并拷贝ewallet.p12到该目录下
ybsera01:/oracle/product/11.2.0/wallet>cd /oracle/product/11.2.0/admin/ynras1/
ybsera01:/oracle/product/11.2.0/wallet>mkdir -p /oracle/product/11.2.0/admin/ynras1/wallet
ybsera01:/oracle/product/11.2.0/admin/ynras1/wallet>cd /oracle/product/11.2.0/wallet
ybsera01:/oracle/product/11.2.0/wallet>ls -lrt
total 8
-rw------- 1 oracle asmadmin 1573 Nov 16 2014 ewallet.p12
ybsera01:/oracle/product/11.2.0/wallet>cp ewallet.p12 /oracle/product/11.2.0/admin/ynras1/wallet
ybsera01:/oracle/product/11.2.0/wallet>ls -l /oracle/product/11.2.0/admin/ynras1/wallet
total 8
-rw------- 1 oracle oinstall 1573 Apr 27 10:37 ewallet.p12
ybsera01:/oracle/product/11.2.0/wallet>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 27 10:37:31 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> alter system set encryption wallet open identified by "Ynyd1234+";
System altered.
SQL>select * from gv$encryption_wallet
INST_ID WRL_TYPE WRL_PARAMETER STATUS
---------- -------------------- ------------------------------------------------------------ ------------------
1 file /oracle/product/11.2.0/admin/ynras1/wallet OPEN
2 file /oracle/product/11.2.0/wallet OPEN
--1节点wallet状态open
SQL>
SQL> l
1* select * from ynngodb.bill_info where rownum<2
可以执行了
这次运气还是不错，ewallet.p12 仍然在本地，但是为了防止下次故障没有wallet文件，所以应及时备份
对钱包文件备份
cp ewallet.p12 /tmp/ewallet.p12.bak
综上
在启动数据库时，使用了srvctl命令，且集群中没有配置ORACLE_BASE变量，以致数据库启动时oracle启用了旧的ORACLE_BASE，数据库读取了错误的wallet路径，最终导致了加密表空间打不开。
还有一种可能就是bug，在alert中明确写明了Reusing ORACLE_BASE from an earlier startup = /oracle/app，这是一个正确的配置，应该不会导致wallet路径错误。