oracle错误记录

oracle常见的错误记录。

The listener supports no services

报错信息

在启动oracle监听的时候,提示The listener supports no services。如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[oracle@root bin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-OCT-2016 18:49:30
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /home/app/oracle/product/11.2.0.4/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /home/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Log messages written to /home/app/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-OCT-2016 18:50:30
Uptime 0 days 0 hr. 2 min. 7 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File /home/app/diag/tnslsnr/dbserver/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))
The listener supports no services
The command completed successfully

客户端信息

这时候,远程连接oracle就会报错ora-12514:TNS: 无法解析指定的连接标识符.

问题原因

根据lnstctl 提示信息,说明:数据实例没有注册到监听器上。

解决办法一

在listener.ora里面添加监听的实例名,属于静态注册监听实例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[oracle@dbserver admin]$ vi listener.ora
# listener.ora Network Configuration File: /home/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /home/app
INBOUND_CONNECT_TIMEOUT_listenername=0
# 添加实例名
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(SID_NAME = orcl)
)
)

解决办法二

通过sqlplus登录后,动态注册监听实例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 14 11:27:06 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL> alter system register;
System altered.
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL>

重启监听程序

1
2
3
4
5
6
7
8
[oracle@dbserver bin]$ $ORACLE_HOME/bin/lnsrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-OCT-2016 11:17:58
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

在通过远程连接oracle就可以正常连接上了。

ORA-28001: 口令已经失效

Oracle11G创建用户时缺省密码过期限制是180天(即6个月), 如果超过180天用户密码未做修改则该用户无法登录

查询默认的用户密码限制

1
2
3
4
--密码过期时间
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'
``
### 修改限制为UNLIMITED

–修改用户密码有效期为无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
```

文章目录
  1. 1. The listener supports no services
    1. 1.1. 报错信息
    2. 1.2. 客户端信息
    3. 1.3. 问题原因
    4. 1.4. 解决办法一
    5. 1.5. 解决办法二
    6. 1.6. 重启监听程序
  2. 2. ORA-28001: 口令已经失效
    1. 2.1. 查询默认的用户密码限制