Oracle运维（数据库、监听、重启）_oracle重启监听

一、概述

二、数据库重启

# su到oracle用户下
[root@localhost ~]# su - oracle

#重启数据库
[oracle@localhost ~]$ sqlplus /nolog
SQL> conn /as sysdba
# 关闭数据库
SQL> shutdown immediate
# 数据库启动
SQL> startup
SQL> exit
1
2
3
4
5
6
7
8
9
10
11

shutdown有四个参数，四个参数的含义如下：

1. Normal 需要等待所有的用户断开连接

2. Immediate 等待用户完成当前的语句

3. Transactional 等待用户完成当前的事务

4. Abort 不做任何等待，直接关闭数据库

• normal需要在所有连接用户断开后才执行关闭数据库任务，所以有的时候看起来好象命令没有运行一样！在执行这个命令后不允许新的连接

• immediate在用户执行完正在执行的语句后就断开用户连接，并不允许新用户连接。

• transactional 在拥护执行完当前事物后断开连接，并不允许新的用户连接数据库。

• abort 执行强行断开连接并直接关闭数据库。

前三种方式不回丢失用户数据。第四种在不的已的情况下，不建议采用！

经常遇到的问题：

• 权限问题，解决方法，切换到oracle用户

• 没有关闭监听器 ，解决方法：关闭监听器

• 有oracle实例没有关闭，解决办法：关闭oracle实例

• 环境变量设置不全，解决办法：修改环境变量

三、监听

1、常用命令

前提：登录用户有dba权限，能够进行查看

# 查看监听状态
[oracle@scorl root]$ lsnrctl status
# 启用监听
[oracle@localhost ~]$ lsnrctl start
# 停掉lsnrctl
[oracle@localhost ~]$ lsnrctl stop

# 退出oracle用户
[oracle@localhost ~]$ exit
logout
[root@localhost ~]#
1
2
3
4
5
6
7
8
9
10
11

2、案例

# 查看监听状态
[oracle@scorl root]$ lsnrctl status
 
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-JUL-2019 14:45:23
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=scorl.dyedu.cn)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

# 启动监听
[oracle@scorl root]$ lsnrctl start
 
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-JUL-2019 14:45:32
 
Copyright (c) 1991, 2011, Oracle.  All rights reserved.
 
Starting /Database/oraapp/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
 
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /Database/oraapp/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /Database/oraapp/oracle/diag/tnslsnr/scorl/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=scorl.dyedu.cn)(PORT=1521)))
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                18-JUL-2019 14:45:32
Uptime                    0 days 0 hr. 0 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /Database/oraapp/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /Database/oraapp/oracle/diag/tnslsnr/scorl/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=scorl.dyedu.cn)(PORT=1521)))
Services Summary...
Service "scorl.dyedu.cn" has 1 instance(s).
  Instance "scorl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

# 停掉lsnrctl
 [oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-JAN-2018 08:00:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
 The command completed successfully

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

这个命令会列出Oracle Net Listener的进程

 [oracle@localhost ~]$ netstat -antp | grep tnslsnr
 (Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
 tcp 0 0 192.168.241.155:1521 0.0.0.0:* LISTEN 6407/tnslsnr 
 [oracle@localhost ~]$
1
2
3
4
5

四、使用plsql连接

用plsql连接到数据库，tnsnames.ora增加配置

TEST_DB155 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.241.155)(PORT = 1521))
 )
 (CONNECT_DATA =
 (SERVICE_NAME = ORCL)
 )
 )
1
2
3
4
5
6
7
8
9

五、更换IP

有一次Linux的ip变了以后，出现了这个

[root@localhost ~]# su - oracle
 [oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-SEP-2018 03:38:08

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

Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
 System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
 Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
 Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
 Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.241.155)(PORT=1521)))
 TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
 TNS-00515: Connect failed because target host or object does not exist
 Linux Error: 99: Cannot assign requested address

Listener failed to start. See the error message(s) above...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

进去改了下ip就好了

[oracle@localhost ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora