Как подключиться к базе данных Oracle 11g удаленно
База данных работает на локальном хосте компьютера A.
Я хочу, чтобы компьютер B подключился к базе данных компьютера A на локальном хосте. Как я могу это сделать?
Вам нужно будет запустить утилиту lsnrctl на сервере A, чтобы запустить прослушиватель. Затем вы должны подключиться с компьютера B, используя следующий синтаксис:
Информация о порте является необязательной, если используется значение по умолчанию 1521.
Я устанавливаю сервер Oracle, и он позволяет без проблем подключаться с локальной машины. Но из другого Maclaptop в моей домашней сети он не может подключиться с помощью Sql Developer или Sql Plus. Проведя некоторые исследования, я понял, что есть еще один дополнительный шаг, который вы должны сделать:
Используйте сетевой менеджер Oracle. Выберите Слушателя. Добавьте IP-адрес (в моем случае это 192.168.1.12) помимо 127.0.0.1 или localhost.
В результате будет добавлена запись в [OracleHome]\product\11.2.0\dbhome_1\network\admin\listener.ora
перезапустите службу слушателя. (примечание: для меня я перезагружаю машину один раз, чтобы она заработала)
Используйте статус lsnrctl для проверки Обратите внимание, что появляется дополнительный Host = 192.168.1.12, и это то, что заставляет работать удаленное соединение.
используйте tnsping для проверки соединения пропингуйте IPv4-адрес, а не localhost или 127.0.0.1
Использовал адаптер EZCONNECT для разрешения псевдонима Попытка связаться (DESCRIPTION = (CONNECT_DATA = (SERVICE_NAME =)) (ADDRESS = (PROTOCOL = TCP) (Host = 192.168.1.12) (PORT = 1521))) ОК (0 мсек)
На компьютере это довольно просто, вам не нужно ничего делать, просто убедитесь, что обе системы находятся в одной сети, если нет доступа к Интернету (для этого вам нужен статический ip). Хорошо, теперь на компьютере b перейдите в меню «Пуск», найдите конфигурацию в папке Oracle, нажмите «Net Configuration Assistant» в этой папке, когда появится всплывающее окно, выберите опцию «Настройка локальной сети».
Во-вторых, для операций с базами данных и подключения от удаленных клиентов в список исключений брандмауэра Windows необходимо добавить следующие исполняемые файлы: (см. Изображение)
Наконец, установите Oracle Instant Client на клиентском компьютере (компьютер B) и запустите:
9 Configuring and Administering Oracle Net Listener
Oracle Net Listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server. This chapter describes how to configure the listener to accept client connections.
This chapter contains the following topics:
In Oracle Database 11 g Release 2 (11.2), the password feature is being deprecated. This does not cause a loss of security because authentication is enforced through local operating system authentication. Refer to Oracle Database Net Services Reference for more information.
Chapter 2, «Identifying and Accessing the Database» for a description of how the listener is used during an initial connection request
Overview of Oracle Net Listener
The release of the listener must be the same as or later than the latest release of all Oracle databases being serviced through the listener.
Service registration offers the following benefits:
Service registration reduces the need for the SID_LIST_ listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.
The SID_LIST_ listener_name parameter is required if you are using Oracle Enterprise Manager to manage the database.
Because the listener always monitors the state of the instances, service registration facilitates automatic failover of a client connect request to a different instance if one instance is down.
When services are configured statically, a listener starts a dedicated server when it receives a client request. If the instance is not up, then the server returns an Oracle not available error message.
Connection load balancing
High-availability for Oracle Real Application Clusters and Oracle Data Guard
Configuring Oracle Net Listener During Installation
Oracle Universal Installer launches Oracle Net Configuration Assistant during installation. Oracle Net Configuration Assistant configures the listening protocol address and service information for Oracle Database.
During an Enterprise Edition or Standard Edition installation on the database server, Oracle Net Configuration Assistant automatically configures a listener with a name of LISTENER that has a TCP/IP listening protocol address for Oracle Database. During a Custom installation, Oracle Net Configuration Assistant prompts for the listener name and protocol address.
A listening IPC protocol address for external procedure calls is automatically configured, regardless of the installation type. Oracle Net Configuration Assistant also automatically configures service information for the external procedures in the listener.ora file.
If you are using the IPC protocol, then you can improve performance by specifying the maximum number of concurrent IPC connection requests to match your expected connection requests.
Example 9-1 Example listener.ora File
Oracle Database Net Services Reference for additional information about identifying listeners by unique names and creating multiple listener entries in the listener.ora file
Customizing Oracle Net Listener Configuration
If the default or installed configuration is not adequate for a particular environment, then you can use Oracle Net Manager to customize the listener.ora configuration.
This section contains the following configuration topics:
Configuring Listening Protocol Addresses
Oracle Enterprise Manager and Oracle Net Manager can be used to configure protocol support for the listener.
The Oracle Net Listener endpoint address configuration accepts both IPv6 addresses and host names that resolve to IPv6 addresses, as explained in «IPv6 Interface and Address Configurations». This technique can create listening endpoints that service IPv6 clients.
Using Oracle Enterprise Manager to Configure Listening Protocol Addresses
To configure protocol addresses for the listener using Oracle Enterprise Manager, do the following:
In the General section of the Database Home page, click the link next to Listener.
The Listener page appears.
The Edit Listener page appears. You may be prompted to log in to the database server.
In the Addresses section, configure protocol support:
The Add Address page appears.
From the Protocol list, select the protocol on which the listener is configured to listen.
For TCP/IP, if the computer has more than one IP address and you want the listener to listen on all available IP addresses, then select TCP/IP or TCP/IP with SSL and enter the host name of the computer in the Host field.
In Port, enter the port number.
When configuring the listener to listen on TCP/IP, enter the default port of 1521. Otherwise, you must configure the LOCAL_LISTENER parameter in the initialization parameter file and the non-default port number must be specified for use by any naming method.
In Host, enter the host address.
Optionally, in the Advanced Parameters section, specify the I/O buffer space limit for send and receive operations of sessions in the Total Send Buffer Size and Total Receive Buffer Size fields.
The protocol address is added to the Addresses section.
Repeat Step 3 for additional protocols.
Oracle Database Net Services Reference for additional information about protocol addresses and TCP/IP privileged ports
Using Oracle Net Manager to Configure Listening Protocol Addresses
To configure protocol addresses for the listener using Oracle Net Manager, do the following:
Start Oracle Net Manager.
Select the listener.
Select the protocol from the Protocol list.
Enter the host name for the listener in the Host field.
Enter the port number in the Port field.
Select Save Network Configuration from the File menu to save the changes.
Handling Large Volumes of Concurrent Connection Requests
If you expect the listener to handle large volumes of concurrent connection requests, then you can specify a listener queue size for its TCP/IP or IPC listening endpoints.
To specify the listener queue size, do the following:
Specify the QUEUESIZE parameter at the end of the protocol address with its value set to the expected number of concurrent requests.
The following example sets the queue size to 20:
The default number of concurrent connection requests is operating system-specific. The defaults for TCP/IP on the Linux operating system and Microsoft Windows follow:
Linux operating system: 128
Microsoft Windows XP Professional SP2: 10
Microsoft Windows 2003 Server Enterprise Edition: 200
Configuring Static Service Information
Table 9-1 describes static service settings in the listener.ora file.
Table 9-1 Static Service Settings in listener.ora
Oracle Net Manager Field
listener.ora File Parameter
Description
The Oracle system identifier (SID) of the instance. You can obtain the SID value from the INSTANCE_NAME parameter in the initialization parameter file.
The database service.
While processing a client connection request, the listener tries to match the value of this parameter with the value of the SERVICE_NAME parameter in the client connect descriptor. If the client connect descriptor uses the SID parameter, then the listener does not attempt to map the values. This parameter is primarily intended for configurations with Oracle8 databases (where dynamic service registration is not supported for dedicated servers). This parameter may also be required for use with Oracle8 i and later database services by some configurations.
Oracle Home Directory
The Oracle home location of the instance. Without this setting, the listener assumes its Oracle home for the instance.
On Linux and UNIX, this setting is optional.
On Microsoft Windows, this setting is ignored. The Oracle home specified by the ORACLE_HOME parameter in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME ID of the Microsoft Windows registry is used.
To statically configure the listener, do the following:
Access the Net Services Administration page in Oracle Enterprise Manager.
Select Listeners from the Administer list, and then select the Oracle home that contains the configuration files.
The Listeners page appears.
The Edit Listener page appears.
The Add Database Service page appears. Enter the required information in the fields.
You can also configure static service information with Oracle Net Manager. See Statically Configure Database Service Information in the online Help for additional information.
The following example shows an excerpt of a listener.ora file statically configured for a database service called sales.us.example.com :
«Configuring Service Registration» for additional information about configuring dynamic service registration Oracle Databases
Chapter 13, «Enabling Advanced Features of Oracle Net Services» for additional information about statically configuring the listener for external procedures and Heterogeneous Services
Oracle Enterprise Manager Advanced Configuration for additional information about Oracle Enterprise Manager
Managing Oracle Net Listener Security
By default, Oracle Net Listener permits only local administration for security reasons. As a policy, the listener can be administered only by the user who started it. This is enforced through local operating system authentication. For example, if user1 starts the listener, then only user1 can administer it. Any other user trying to administer the listener gets an error. The super user is the only exception.
Oracle recommends that you perform listener administration in the default mode (secure by means of local operating system authentication), and access the system remotely using a remote login. Oracle Enterprise Manager can also be used for remote administration.
Configuring and Changing the Oracle Net Listener Password
Local administration of the listener is secure by default through the local operating system. Therefore configuring a password is neither required nor recommended for secure local administration. However, a password can be configured for the listener to provide security for administrative operations, such as starting or stopping the listener, viewing a list of supported services, or saving changes to the Listener Control configuration.
If the PASSWORDS_ listener_name parameter is set to an unencrypted password, then you must manually remove it from the listener.ora file before changing it. If the unencrypted password is not removed, then you are unable to set an encrypted password.
You can use the Listener Control utility ( lsnrctl) or Oracle Enterprise Manager to configure or change the Oracle Net Listener password.
To set or change an encrypted password with Oracle Enterprise Manager, do the following:
Access the Net Services Administration page in Oracle Enterprise Manager.
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
The Listeners page appears.
The Edit Listeners page appears.
Click the Authentication tab.
Restart the listener.
Oracle Database Security Guide for additional information about minimum requirements for passwords
Oracle Database Net Services Reference for additional information about the CHANGE_PASSWORD command
«Configure Password Authentication for the Listener» in the online Help for additional information.
Configuring Service Registration
Service registration allows processes, such as an Oracle database, to identify their available services to the listener, which then acts as a port mapper for those services. The listener uses the dynamic service information about the database and instance received through service registration before using statically configured information in the listener.ora file.
Dynamic service registration is configured in the database initialization file. It does not require any configuration in the listener.ora file. However, listener configuration must be set to listen on the ports named in the database initialization file, and must not have parameters set that prevent automatic registration, such as COST parameters.
This section contains the following configuration topics related to service registration:
Setting Initialization Parameters for Service Registration
To ensure service registration works properly, the initialization parameter file should contain the following parameters:
SERVICE_NAMES for the database service name
INSTANCE_NAME for the instance name
LOCAL_LISTENER for the local listener
REMOTE_LISTENER for the remote listener, if any
Oracle Database Reference for additional information about the SERVICE_NAMES and INSTANCE_NAME parameters
Registering Information with a Local Listener
By default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. If the listener configuration is synchronized with the database configuration, then PMON can register service information with a nondefault local listener or a remote listener on another node. Synchronization occurs when the protocol address of the listener is specified in the listener.ora file and the location of the listener is specified in the initialization parameter file.
To have PMON register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.
For a shared server environment, you can use the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with a nondefault local listener. Because the LOCAL_LISTENER parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same.
LOCAL_LISTENER is a comma-delimited list parameter. If a comma appears in the string, then the entire string must be enclosed in double quotation marks. Set the LOCAL_LISTENER parameter as follows:
For shared server connections, set the LISTENER attribute as follows:
In the preceding command, listener_address is resolved to the listener protocol addresses through a naming method, such as a tnsnames.ora file on the database server.
If you set the parameter to null using the following statement, then the default local address of TCP/IP, port 1521 is assumed:
The LISTENER attribute overrides the LOCAL_LISTENER parameter. As a result, the SQL statement ALTER SYSTEM SET LOCAL_LISTENER does not affect the setting of this attribute.
Example 9-2 Registering a Local Listener in a Dedicated Server Environment
On the host where the local listener resides, configure the listener.ora file with the protocol address of the listener using Oracle Net Manager.
On the database, set the LOCAL_LISTENER parameter in the database initialization parameter file to the alias of the local listener. For example:
If the database is configured for shared server connections, then you could set the LISTENER attribute as follows:
Resolve the listener name alias for the LOCAL_LISTENER setting through a tnsnames.ora file on the database host using a text editor, as follows:
If you are registering a local listener and use Oracle Connection Manager, then do not include (DESCRIPTION = or its closing parenthesis.
A net service name entry can be created for the protocol address without the CONNECT_DATA section of the connect descriptor.
See the Oracle Database SQL Reference for additional information about the ALTER SYSTEM statement.
Registering Information with a Remote Listener
A remote listener is a listener residing on one computer that redirects connections to a database instance on another computer. Remote listeners are typically used in an Oracle Real Application Clusters (Oracle RAC) environment. You can configure registration to remote listeners, such as with Oracle RAC, for dedicated or shared server environments.
In a dedicated server environment, you must enable the PMON background process to register with a remote listener. You do this by configuring the REMOTE_LISTENER parameter, which is a comma-delimited list parameter, in the initialization parameter file. The syntax of REMOTE_LISTENER is as follows:
In the preceding command, listener_address is resolved to the listener protocol addresses through a naming method such as a tnsnames.ora file on the database host. If a comma appears in the listener address, then the entire string must be enclosed in quotation marks.
In a shared server environment, you can use the same registration technique as for a dedicated server environment. Alternatively, you can set the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with any listener. The syntax of the LISTENER attribute is as follows:
The LISTENER attribute overrides the REMOTE_LISTENER initialization parameter. Because the REMOTE_LISTENER initialization parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, you do not need specify both the parameter and the attribute if the listener values are the same.
Figure 9-1 Remote Listener
Description of «Figure 9-1 Remote Listener»
Oracle Database SQL Reference for additional information about the ALTER SYSTEM SET statement
Example 9-3 Registering a Remote Listener in a Dedicated Server Environment
On the host where the remote listener resides, use Oracle Net Manager to configure the listener.ora file with the protocol addresses of the remote listener.
On the database to which you want requests to be redirected, set the REMOTE_LISTENER parameter in the database initialization parameter file to the alias of the remote listener, for example:
For shared server connections, set the DISPATCHER parameter in the initialization file for the database on host sales1-server as follows:
To statically update the REMOTE_LISTENER initialization parameter, use a text editor to de-register the information with the remote listener which it had previously registered information.
Resolve the listener name alias for the REMOTE_LISTENER setting through a tnsnames.ora file on the database host. For example:
Oracle Database Reference to learn about the REMOTE_LISTENER initialization parameter
Oracle Real Application Clusters Administration and Deployment Guide to learn how to configure remote listeners (also called SCAN listeners) in an Oracle RAC environment
Registering Information with All Listeners in a Network
A network may contain multiple local and remote listeners. By default, all listeners are cross-registered with each other. By specifying a set of listeners in the LISTENER_NETWORKS initialization parameter, you can designate a subset of local listeners with a subset of remote listeners. Listeners specified by the LISTENER_NETWORKS parameter should not be specified by the LOCAL_LISTENER and REMOTE_LISTENER parameters.
The syntax of LISTENER_NETWORKS is as follows:
Example 9-4 Using Two Networks on a Subnet
Example 9-5 Configuring Multiple Listeners
A local listener resides on sales1-server and listens on nondefault port 1421.
A remote listener named resides on host sales2-server and listens on port 1521.
Another remote listener resides on host sales3-server and listens on port 1521.
To register information with all listeners in a dedicated server environment, do the following:
On the hosts where the remote listeners reside (in this example, sales2-server and sales3-server ), configure the listener.ora file with the protocol addresses of the remote listener.
On the database to which you want requests to be redirected, set the REMOTE_LISTENER parameter in the database initialization parameter file to the alias of the remote listeners, and the LOCAL_LISTENER parameter to the alias of the local listener.
Set the parameters in the initialization file for the database on host sales1-server as follows:
Resolve the listener name alias for the LOCAL_LISTENER and REMOTE_LISTENER setting through a tnsnames.ora file on the database host.
Oracle Database Reference for additional information about the REMOTE_LISTENER initialization parameter
Configuring a Naming Method
The listener name alias specified for the LOCAL_LISTENER initialization parameter, REMOTE_LISTENER initialization parameter, or LISTENER attribute can be resolved through a tnsnames.ora file. A net service name entry can be created for the protocol address without the CONNECT_DATA section of the connect descriptor. Oracle Enterprise Manager and Oracle Net Manager cannot configure a tnsnames.ora file without the CONNECT_DATA information. To use listener name aliases, Oracle recommends you modify the tnsnames.ora file using a text editor.
For example, if LOCAL_LISTENER is set to listener_sales1 and listener_sales1 uses TCP/IP on port 1421, then the entry in the tnsnames.ora file would be:
Multiple addresses are supported, but connect-time failover and client load balancing features are not supported.
If the listener alias specified in the LOCAL_LISTENER parameter is invalid or not resolved, then the PMON process does not allow the database to start. The following errors occur:
Chapter 13, «Enabling Advanced Features of Oracle Net Services» for additional information about multiple address configuration
Administering the Listener
After the listener is configured, you can administer it with the Listener Control utility, Oracle Enterprise Manager, and the Server Control utility (SRVCTL). This section describes some of the following administrative tasks for the listener. It contains the following topics:
Oracle Database Net Services Reference for a complete list of the Listener Control utility commands
Oracle Enterprise Manager online Help
Starting and Stopping a Listener
To stop or start a listener, use one of the following methods:
You can configure the listener to start automatically whenever the computer it is running or is restarted. See your operating system-specific documentation for details about establishing auto-restart.
Using the Listener Control Utility to Start or Stop a Listener
To start the listener from the command line, enter:
In addition to starting the listener, the Listener Control utility verifies connectivity to the listener.
To stop a listener from the command line, enter:
Using Oracle Enterprise Manager to Start or Stop a Listener
To start or stop a listener from Oracle Enterprise Manager, do the following:
Access the Net Services Administration page in Oracle Enterprise Manager.
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
The Listeners page appears.
The Start/Stop page appears.
Managing a Listener in an Oracle Restart Configuration
The Oracle Restart feature enhances availability for the processes and applications in a single-instance database environment. You can add selected components to the Oracle Restart configuration. The Oracle Restart agents monitor the health of added components by periodically running check operations and restarting the components when necessary.
You can add the listener as a component to the Oracle Restart configuration. The listener is automatically started by Oracle Restart when it fails or is not running. For example, if you restart the database instance after a planned restart of the computer, then Oracle Restart restarts the listener. Server Control (SRVCTL) is a command-line interface that you can use to manage listeners in an Oracle Restart configuration.
To view all configured listeners, use the following command:
Oracle Database Administrator’s Guide to learn how to configure Oracle Restart and for SRVCTL syntax and semantics
Adding or Removing a Listener Using SRVCTL
Adding a listener means to add an entry for the listener to the grid infrastructure, enabling the agent to monitor this component. Similarly, removing a listener means removing an entry for a listener. In both cases you enter the command srvctl at the operating system command line.
Enter srvctl add listener to add the listener.
For example, the following command adds an entry for listener_sales1 to the grid infrastructure:
Enter srvctl remove listener to remove the listener.
For example, the following command removes the entry for listener_sales1 from the grid infrastructure:
Starting or Stopping a Listener Using SRVCTL
Enter srvctl start listener to start a listener.
For example, the first command starts the default listener, and the second command starts listener1 and listener2 :
Enter srvctl stop listener to stop a listener.
For example, the first command stops the default listener, and the second command stops listener1 and listener2 :
Determining the Current Status of a Listener
To show the current status of a listener, use either the STATUS command of the Listener Control utility or Oracle Enterprise Manager. The status output provides basic status information about a listener, a summary of listener configuration settings, the listening protocol addresses, and a summary of services registered with the listener.
Using Listener Control to Show Status
To show the status the listener from the command line, enter:
Example 9-6 shows example output of the STATUS command.
Example 9-6 Listener Control Utility’s STATUS Command Output
The STATUS command generates output with the sections described in Table 9-2.
Table 9-2 Listener Control Utility STATUS Command
Output Section
Description
STATUS of the LISTENER
Status of the listener, which can be one of the following:
Alias of the listener
Version of listener
Start time and up time
Whether the listener can respond to queries from an SNMP-based network management system
listener.ora file being used
Logging and tracing configuration settings
Whether a password is set in listener.ora file
Listening Endpoints Summary
The protocol addresses the listener is configured to listen on
A summary of the services registered with the listener and the service handlers allocated to each service
The registered service
The name of the instance associated with the service along with its status and number of service handlers associated with the service
Status can be one of the following:
A READY status means that the instance can accept connections.
A BLOCKED status means that the instance cannot accept connections.
A READY/SECONDARY status means that this is a secondary instance in an Oracle Real Application Clusters primary/secondary configuration and is ready to accept connections.
An UNKNOWN status means that the instance is registered statically in the listener.ora file rather than dynamically with service registration. Therefore, the status is not known.
Using Oracle Enterprise Manager to Show Status
To show the status of a listener from Oracle Enterprise Manager, do the following:
Access the Net Services Administration page in Oracle Enterprise Manager.
Select Listeners from the Administer list, and then select the Oracle home that contains the location of the configuration files.
The Listeners page appears.
The Listener Control Status page appears.
After viewing the content, click the listener link at the top of the page.
Monitoring Services of a Listener
The SERVICES command of the Listener Control utility provides detailed information about the services and instances registered with a listener and the service handlers allocated to each instance. To show information about the services and instances from the command line, enter:
Example 9-7 shows example output of the SERVICES command.
Example 9-7 Listener Control Utility’s SERVICES Command Output
Client connection requests to hr.us.example.com are handled by one dispatcher named D001 and one dedicated server.
The SERVICES command generates output with the following information as described in Table 9-3.
Table 9-3 Listener Control Utility SERVICES Command
Output Section
Description
The registered service.
The name of the instance associated with the service
The status field indicates if the instance can accept connections.
A READY status means that the instance can accept connections.
A BLOCKED status means that the instance cannot accept connections.
A READY/SECONDARY status means that the is a secondary instance in an Oracle Real Application Clusters primary/secondary configuration and is ready to accept connections.
A RESTRICTED status means that the instance is in restricted mode. The listener blocks all connections to this instance.
An UNKNOWN status means that the instance is registered statically in the listener.ora file rather than dynamically with service registration. Therefore, the status is non known.
This section also identifies the following about the service handler:
established : The number of client connections this service handler has established.
refused : The number of client connections it has refused.
current : The number of client connections it is handling, that is, its current load.
max : The maximum number of connections for the service handler, that is, its maximum load.
state : The state of the handler:
— A READY state means that the service handler can accept new connections.
— A BLOCKED state means that the service handler cannot accept new connections.
Monitoring Listener Log Files
When you notice any of the following conditions, review the listener log file for error information:
Long connection establishment times
Connectivity problems and refusals
Unexpected shutdown of the listener that could indicate a denial-of-service attack
Re: �� ��������� ������ ��������� [new]
