今天尝试创建了一个RAC环境的PDB数据库,在本地执行alter pluggable database pdb01 open 成功了,但是使用上去了plus连接还是连接不上,提示错误如下:
C:\Users\Administrator>sqlplus system/oracle@192.168.1.25:1521/pdb01SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 11月 19 08:39:09 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.ERROR:ORA-01109: database not open
依次检查配置:
1.手工启动pdb数据库
[oracle@mesdb01 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 19 10:25:39 2023Version 19.15.0.0.0Copyright (c) 1982, 2022, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.15.0.0.0SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTEDSQL> alter pluggable database pdb01 open;Pluggable database altered.SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NOSQL>
2.在Oracle用户下的tnsnames.ora文件添加了监听:
[oracle@mesdb01 ~]$ cd $ORACLE_HOME/network/admin[oracle@mesdb01 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/product/19.0.0/db_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.MESDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = mesdb-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mesdb) ) )pdb01 = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)(HOST = mesdb-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb01) ) )[oracle@mesdb01 admin]$
3.重启grid用户下的监听
[oracle@mesdb01 admin]$ exitlogout[root@mesdb01 ~]# su - gridLast login: Sun Nov 19 10:18:31 CST 2023[grid@mesdb01 ~]$ lsnrctl stopLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2023 10:30:30Copyright (c) 1991, 2022, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))The command completed successfully[grid@mesdb01 ~]$ lsnrctl startLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2023 10:30:34Copyright (c) 1991, 2022, Oracle. All rights reserved.Starting /grid/crs_home/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 19.0.0.0.0 - ProductionSystem parameter file is /grid/crs_home/network/admin/listener.oraLog messages written to /grid/crs_base/diag/tnslsnr/mesdb01/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 19.0.0.0.0 - ProductionStart Date 19-NOV-2023 10:30:34Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /grid/crs_home/network/admin/listener.oraListener Log File /grid/crs_base/diag/tnslsnr/mesdb01/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))The listener supports no servicesThe command completed successfully[grid@mesdb01 ~]$ lsnrctl statusLSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2023 10:30:38Copyright (c) 1991, 2022, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 19.0.0.0.0 - ProductionStart Date 19-NOV-2023 10:30:34Uptime 0 days 0 hr. 0 min. 4 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /grid/crs_home/network/admin/listener.oraListener Log File /grid/crs_base/diag/tnslsnr/mesdb01/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.23)(PORT=1521)))Services Summary...Service "0a5aa414b738a716e0631601a8c05401" has 1 instance(s). Instance "mesdb1", status READY, has 1 handler(s) for this service...Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s). Instance "mesdb1", status READY, has 1 handler(s) for this service...Service "mesdb" has 1 instance(s). Instance "mesdb1", status READY, has 1 handler(s) for this service...Service "mesdbXDB" has 1 instance(s). Instance "mesdb1", status READY, has 1 handler(s) for this service...Service "pdb01" has 1 instance(s). Instance "mesdb1", status READY, has 1 handler(s) for this service...The command completed successfully[grid@mesdb01 ~]$
发现问题依然存在,后来发现问题所在,下面这条指令需要在两个节点都执行一遍。但是我以为只需要在其中一个节点执行就可以了,所以只有有一个节点启动成功了,但我连接时候是连接到了另一个节点,因此提示失败。
检查如下:
节点一
[root@mesdb01 ~]# su - oracleLast login: Sun Nov 19 10:25:31 CST 2023 on pts/0[oracle@mesdb01 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 19 10:38:50 2023Version 19.15.0.0.0Copyright (c) 1982, 2022, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.15.0.0.0SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NOSQL>
节点二:
[root@mesdb02 ~]# su - oracleLast login: Sun Nov 19 10:18:49 CST 2023[oracle@mesdb02 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 19 10:36:48 2023Version 19.15.0.0.0Copyright (c) 1982, 2022, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.15.0.0.0SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTEDSQL>
在两个节点都执行上述操作后OK!
C:\Users\Administrator>sqlplus system/oracle@192.168.1.25:1521/pdb01SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 11月 19 10:30:42 2023Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.上次成功登录时间: 星期日 11月 19 2023 09:16:00 +08:00连接到:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.15.0.0.0SQL>
低级错误!