1.su Oracle
and start the listener.
1.lsnrctl start
You will see the interface to start successfully;
1.lsnrctl stop
Stop the Listener command.
1.lsnrctl status
View listener commands.
The security of an Oracle database consists of two parts:
1. Part of the security of the OS
2. Security of the network
3.oracle security of the software itself
The security of the OS depends on the appropriate operating system and the level of administrator, and here we talk only about Oracle security.
I understand the security of Oracle software includes :
1. Change the default listening port number for Oracle
2. Add a password to the listener
3.oracle Controlling the connection of IP
Here are three ways to test your learning
---------Oracle Default listener port changes --------------
1. Change the default listening port number for Oracle
Overall steps to modify the port number
1.1. View the status of the current listener
1.2. Stop listening
1.3. Modify the port number of a listening file
1.4. Modifying initialization parameters Local_listener
1.5. Restart Listener
1.6. Modified, login test with new port
Practice steps:
1.1. View the status of the current listener
C:/Documents and Settings/skate_db>lsnrctl status
Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 1 September-July-2008 12:1
1:19
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (Description= (address= (PROTOCOL=IPC) (Key=extproc1))
The STATUS of LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 10.2.0.1.0-produ
Ction
Start date 1 September-July-2008 11:47:59
Uptime 0 days 0 hours 23 minutes 19 seconds
Trace level off
Security On:password or Local OS authentication
SNMP OFF
Listener parameter File E:/ORACLE/PRODUCT/10.2.0/DB_3/NETWORK/ADMIN/LISTENER.O
Ra
Listener log File E:/oracle/product/10.2.0/db_3/network/log/listener.log
Listener Endpoint Summary ...
(Description= (address= (PROTOCOL=IPC) (PIPENAME=//./PIPE/EXTPROC1IPC)))
(Description= (address= (protocol=tcp) (host=skate) (port=1522)))
Service Summary:
The service "Plsextproc" consists of 1 routines.
Routine "Plsextproc", State UNKNOWN, contains 1 handlers for this service ...
The service "ORCL" consists of 1 routines.
Routine "ORCL", state ready, contains 1 handlers for this service ...
The service "Orclxdb" consists of 1 routines.
Routine "ORCL", state ready, contains 1 handlers for this service ...
The service "ORCL_XPT" consists of 1 routines.
Routine "ORCL", state ready, contains 1 handlers for this service ...
Command execution succeeded
1.2. Stop listening
C:/Documents and Settings/skate_db>lsnrctl stop
Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 1 September-July-2008 13:1
6:15
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (Description= (address= (PROTOCOL=IPC) (Key=extproc1))
Command execution succeeded
1.3. Modify the port number of the listening file for the network connection
Change the port number to 1523
# Listener.ora Network Configuration File:e:/oracle/product/10.2.0/db_3/network/admin/listener.ora
# Generated by Oracle configuration tools.
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = Plsextproc)
(Oracle_home = e:/oracle/product/10.2.0/db_3)
(program = Extproc)
)
)
LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP) (HOST = skate) (PORT = 1523))
)
)
#----ADDED by Tnslsnr December-July -2008 13:26:50---
Passwords_listener = 3650f1eb3c37abd9
#---------------------------------------------
1.4. Modifying initialization parameters Local_listener, for local connections
C:/Documents and Settings/skate_db>sqlplus/as Sysdba
Sql*plus:release 10.2.0.1.0-production on Saturday July 19 13:24:02 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connect to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options
Sql> Show Parameter Local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Local_listener string (address= (protocol=tcp) (host=s
Kate) (port=1522))
Sql> alter system set local_listener= "(Address= (PROTOCOL=TCP) (host=skate) (port=1
523)) ";
The system has changed.
1.5. Restart Listener
C:/Documents and Settings/skate_db>lsnrctl start
Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 1 September-July-2008 13:2
7:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Start Tnslsnr: Please wait ...
Tnslsnr for 32-bit windows:version 10.2.0.1.0-production
System parameter file is E:/oracle/product/10.2.0/db_3/network/admin/listener.ora
Log information written to E:/oracle/product/10.2.0/db_3/network/log/listener.log
Monitoring: (Description= (Address= (PROTOCOL=IPC) (PIPENAME=//./PIPE/EXTPROC1IPC)))
Monitoring: (Description= (Address= (protocol=tcp) (host=skate) (port=1523)))
Connecting to (Description= (address= (PROTOCOL=IPC) (Key=extproc1))
The STATUS of LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 10.2.0.1.0-produ
Ction
Start date 1 September-July-2008 13:27:47
Uptime 0 days 0 hours 0 minutes 3 seconds
Trace level off
Security On:password or Local OS authentication
SNMP OFF
Listener parameter File E:/ORACLE/PRODUCT/10.2.0/DB_3/NETWORK/ADMIN/LISTENER.O
Ra
Listener log File E:/oracle/product/10.2.0/db_3/network/log/listener.log
Listener Endpoint Summary ...
(Description= (address= (PROTOCOL=IPC) (PIPENAME=//./PIPE/EXTPROC1IPC)))
(Description= (address= (protocol=tcp) (host=skate) (port=1523)))
Service Summary:
The service "Plsextproc" consists of 1 routines.
Routine "Plsextproc", State UNKNOWN, contains 1 handlers for this service ...
Command execution succeeded
1.6. Modified, login test with new port
1.6.1. Check if the listening port has changed
C:/Documents and Settings/skate_db>netstat-an | Find "1523"
TCP 0.0.0.0:1523 0.0.0.0:0 LISTENING
TCP 192.168.0.103:1523 192.168.0.103:2389 established
TCP 192.168.0.103:2389 192.168.0.103:1523 established
1.6.2. To view the status of a listener
C:/Documents and Settings/skate_db>lsnrctl status
Lsnrctl for 32-bit windows:version 10.2.0.1.0-production on 1 September-July-2008 13:2
8:56
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (Description= (address= (PROTOCOL=IPC) (Key=extproc1))
The STATUS of LISTENER
------------------------
Alias LISTENER
Version Tnslsnr for 32-bit windows:version 10.2.0.1.0-produ
Ction
Start date 1 September-July-2008 13:27:47
Uptime 0 days 0 hours 1 minutes 10 seconds
Trace level off
Security On:password or Local OS authentication
SNMP OFF
Listener parameter File E:/ORACLE/PRODUCT/10.2.0/DB_3/NETWORK/ADMIN/LISTENER.O
Ra
Listener log File E:/oracle/product/10.2.0/db_3/network/log/listener.log
Listener Endpoint Summary ...
(Description= (address= (PROTOCOL=IPC) (PIPENAME=//./PIPE/EXTPROC1IPC)))
(Description= (address= (protocol=tcp) (host=skate) (port=1523)))
Service Summary:
The service "Plsextproc" consists of 1 routines.
Routine "Plsextproc", State UNKNOWN, contains 1 handlers for this service ...
The service "ORCL" consists of 1 routines.
Routine "ORCL", state ready, contains 1 handlers for this service ...
The service "Orclxdb" consists of 1 routines.
Routine "ORCL", state ready, contains 1 handlers for this service ...
The service "ORCL_XPT" consists of 1 routines.
Routine "ORCL", state ready, contains 1 handlers for this service ...
Command execution succeeded
1.6.3. Log in with the new port number (1523) test
C:/Documents and Settings/skate_db>sqlplus
Sql*plus:release 10.2.0.1.0-production on Saturday July 19 13:32:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Please enter user name: sys/[email protected]: 1523/ORCL as Sysdba
Connect to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production
With the partitioning, OLAP and Data Mining options
Sql>
Sql> select * from V$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod
PL/SQL Release 10.2.0.1.0-production
CORE 10.2.0.1.0 Production
TNS for 32-bit windows:version 10.2.0.1.0-production
Nlsrtl Version 10.2.0.1.0-production
Sql> select Open_mode from V$database;
Open_mode
----------
READ WRITE
Sql>
NOTE: When the Oracle default listening port is modified, using tnsping will cause errors, because at this time, Oracle will
Read TNSNames. Ora this file, and the port number of this file is not changed, just change the corresponding
The port number is OK.
eg
C:/Documents and Settings/skate_db>tnsping ORCL
TNS Ping Utility for 32-bit windows:version 10.2.0.1.0-production on 1 September-July-
2008 17:11:05
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter file:
E:/oracle/product/10.2.0/db_3/network/admin/sqlnet.ora
Used the TNSNames adapter to resolve aliases
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = skate) (P
ORT = 1521)) (Connect_data = (SERVER = dedicated) (service_name = ORCL)))
Tns-12541:tns: No listening program
As can be seen here, Oracle still uses port 1521, we change the port number under Tnsnames.ora, OK.
# Tnsnames.ora Network Configuration File:e:/oracle/product/10.2.0/db_3/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = skate) (PORT = 1523))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)
Extproc_connection_data =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1))
)
(Connect_data =
(SID = Plsextproc)
(PRESENTATION = RO)
)
)
Restart the listener at tnsping
C:/Documents and Settings/skate_db>tnsping ORCL
TNS Ping Utility for 32-bit windows:version 10.2.0.1.0-production on 1 September-July-
2008 17:22:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter file:
E:/oracle/product/10.2.0/db_3/network/admin/sqlnet.ora
Used the TNSNames adapter to resolve aliases
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = skate) (P
ORT = 1523)) (Connect_data = (SERVER = dedicated) (service_name = ORCL)))
OK (50 milliseconds)
---------Oracle Default listener port changes --------------
Xxxxxxxxxxxxxxxxxxxxxxxxxxoracle Control IP Connection xxxxxxxxxxxxxxxxxxxxxxxx
2. Oracle Controls IP connectivity (we can also use database triggers to record user logins, but users who cannot log DBA authority)
Simply set the allowed IP and forbidden IP, before oracle9i the document said to add or modify Protocol.ora files,
What really works in 9i and later is the Sqlnet.ora file, which is actually the best and fastest way to modify Sqlnet.ora.
features of the Sqlnet.ora file:
1. Specify the client domain to append to unqualified names
2. Prioritize naming methods
3. Enable Logging and tracing features
4. Route connections through specific processes
5. Configure parameters for external naming
6. Configure Oracle Advanced Security
7. Use protocol-specific parameters to restrict access to the database
What I'm using here is a 7th function.
Add the following section to the Sqlnet.ora
-----------------------------
Tcp.validnode_checking=yes
#允许访问的IP
Tcp.invited_nodes= (Ip1,ip2 ...)
#禁止访问的IP
Tcp.excluded_nodes= (Ip1,ip2 ...)
Then restart the Listener
eg
# Sqlnet.ora Network Configuration File:e:/oracle/product/10.2.0/db_3/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
# This file was actually generated by NETCA. But if customers choose to
# install ' Software only ', this file wont exist and without the native
# Authentication, they'll is not being able to connect to the database on NT.
Sqlnet. Authentication_services= (NTS)
NAMES. Directory_path= (TNSNames, Ezconnect)
#skate Modify
Tcp.validnode_checking=yes
Tcp.invited_nodes= (192.168.0.103)
Tcp.excluded_nodes= (192.168.0.10)
Issues to be aware of:
1. You need to set the parameter to Yes to activate it.
2, it is recommended to set the allowed IP access, because the IP address may be arbitrarily modified, can not play its own purpose.
3. TCP overrides the Tcp.excluded_nodes setting when the address of the parameter Tcp.invited_nodes and tcp.excluded_nodes settings is the same.
4. You need to restart the listener to take effect.
5, this method is only suitable for the TCP protocol.
6. This configuration is suitable for 9i and above. Versions prior to 9i use file Protocol.ora.
7, directly connected to the database on the server is not affected.
8. This restriction is limited by the listener.
9, this limit is only for IP detection, for the user name detection is not supported.
Original link: http://blog.csdn.net/dz45693/article/details/5962179
Oracle listener Start, stop, view commands