oracle19cPDB数据库连不上,提示ORA-01109: database not open

今天尝试创建了一个RAC环境的PDB数据库,在本地执行alter pluggable database pdb01 open 成功了,但是使用上去了plus连接还是连接不上,提示错误如下:

C:\Users\Administrator>sqlplus system/oracle@192.168.1.25:1521/pdb01

SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 11月 19 08:39:09 2023
Version 19.3.0.0.0

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

ERROR:
ORA-01109: database not open

依次检查配置:

1.手工启动pdb数据库

[oracle@mesdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 19 10:25:39 2023
Version 19.15.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB01              MOUNTED
SQL> 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 NO
SQL> 

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]$ exit
logout
[root@mesdb01 ~]# su - grid
Last login: Sun Nov 19 10:18:31 CST 2023
[grid@mesdb01 ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2023 10:30:30

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[grid@mesdb01 ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2023 10:30:34

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Starting /grid/crs_home/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /grid/crs_home/network/admin/listener.ora
Log messages written to /grid/crs_base/diag/tnslsnr/mesdb01/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                19-NOV-2023 10:30:34
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /grid/crs_home/network/admin/listener.ora
Listener Log File         /grid/crs_base/diag/tnslsnr/mesdb01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
The listener supports no services
The command completed successfully
[grid@mesdb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 19-NOV-2023 10:30:38

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                19-NOV-2023 10:30:34
Uptime                    0 days 0 hr. 0 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /grid/crs_home/network/admin/listener.ora
Listener Log File         /grid/crs_base/diag/tnslsnr/mesdb01/listener/alert/log.xml
Listening 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 - oracle
Last login: Sun Nov 19 10:25:31 CST 2023 on pts/0
[oracle@mesdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 19 10:38:50 2023
Version 19.15.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB01              READ WRITE NO
SQL> 

 

节点二:

[root@mesdb02 ~]# su - oracle
Last login: Sun Nov 19 10:18:49 CST 2023
[oracle@mesdb02 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 19 10:36:48 2023
Version 19.15.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED              READ ONLY  NO
     3 PDB01              MOUNTED
SQL> 

 

在两个节点都执行上述操作后OK!

C:\Users\Administrator>sqlplus system/oracle@192.168.1.25:1521/pdb01

SQL*Plus: Release 19.0.0.0.0 - Production on 星期日 11月 19 10:30:42 2023
Version 19.3.0.0.0

Copyright (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 - Production
Version 19.15.0.0.0

SQL>

低级错误!

热门相关:苍穹龙骑   至尊龙帝陆鸣   都市捉妖人   这个大佬有点苟   国破山河在