Oracle remote connection (PLSQL, SQL developement + Oracle database + client + remote connection settings, plsqldevelopement

Oracle remote connection (PLSQL, SQL developement + Oracle database + client + remote connection settings, plsqldevelopement

Server

Configuration: oracle11g R2 x64

1. Set the listener

A. Launch bar-> Start-> Program-> Oracle-OraDb11g_home1-> Configure and port tools-> Net Manager

B. oracle Net configuration-> Local-> LISTENER, check whether there is a local address in the LISTENER, add if not, host: "localhost", Port: 1521, protocol: TCP/IP

2. Disable all firewalls (including windows firewalls ). If no listening program appears, it is a firewall problem.

3. start the TNS listening service: lsnrctl start (this program lsnrctl.exe is located in the oracle_path/product/11.2.0/dbhome_1/BIN directory

PS: My system users cannot log on, but sys and scott users can log on. I seem to understand that DBA does not allow remote connection? However, the sys user can log on as a DBA.

Client (Windows)

1. download and install the Instant Client for Microsoft Windows (32bit). Address: Download the instant client

2. Add the installation directory of the instant client to the path environment variable.

3. Create the tnsnames. ora configuration file in the following format:

[Plain]View plaincopy

1. # Tnsnames. ora Network Configuration File: C: \ e \ orcldb \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN \ tnsnames. ora
2. # Generated by Oracle configuration tools.
4. WSR =
5. (DESCRIPTION =
6. (ADDRESS_LIST =
7. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.112) (PORT = 1521 ))
8. )
9. (CONNECT_DATA =
10. (SERVICE_NAME = orcl)
11. )
12. )
14. Export lr_connection_data =
15. (DESCRIPTION =
16. (ADDRESS_LIST =
17. (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 ))
18. )
19. (CONNECT_DATA =
20. (SID = CLRExtProc)
21. (PRESENTATION = RO)
22. )
23. )
25. LISTENER_ORCL =
26. (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))
28. ORCL =
29. (DESCRIPTION =
30. (ADDRESS_LIST =
31. (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521 ))
32. )
33. (CONNECT_DATA =
34. (SERVER = DEDICATED)
35. (SERVICE_NAME = orcl)
36. )
37. )

The TNS service name I created is WRS, the address of the Oracle server is 192.168.1.112, the listening port of the Oracle server is 1521, and the connected Oracle database is named orcl, which can be replaced by this format.

4. Add the TNS_ADMIN environment variable to point to the created tnsnames. ora configuration file.

So far, the configuration has been completed. You can use tools such as pl/SQL developer to connect to a remote oracle database. The database is the name of the added tns Service (WSR here)

If you want to use odbc to connect to the database, you also need to add odbc data sources. 64-bit systems use C: \ Windows \ SysWOW64 \ odbcad32.exe, 32-bit systems use C: \ Windows \ System32 \ odbcad32.exe

The following methods fail ........

Oracle Database remote connection can be achieved through multiple methods. This article describes four remote connection methods and precautions, and describes them through examples. Next we will introduce them.

First case:

If the oracle server is installed on the local machine, That is not to mention. The connection is just a matter of user name and password. However, check whether the environment variable % ORACLE_HOME %/network/admin/is set.

Case 2:

The oracle server is not installed or the oracle client is not installed. However, pl SQL development, toad SQL development, SQL navigator, and other database management tools are installed. An oracle server is installed on a virtual machine or another computer, that is, a virtual machine or another computer.

In this case, I use pl SQL development to remotely connect to the ORACLE server database as an example:

1. Search for the following files on the machine where the oracle server is installed:

Oci. dll
Ocijdbc10.dll
Ociw32.dll
Orannzsbb10.dll
Oraocci10.dll
Oraociei10.dll
Sqlnet. ora
Tnsnames. ora
Classes12.jar
Ojdbc14.jar
Copy these files to a folder, such as clientlient, and copy them to the client machine. For example, the placement path is D: clientlient.

2. Configure tnsnames. ora and modify the database connection string.

 

Oracledata = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.58) (PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = oracledata ))

Here, oracledata is the service name to be connected; HOST = 192.168.0.58 is the Server IP address; PORT = 1521 is the PORT number.

3. Add the first environment variable named TNS_ADMIN with the value tnsnames. path of the ora file (for example, D: Connector lient, especially after reinstallation or other operations, if you forget the TNS_ADMIN variable, the connection identifier cannot be parsed when you log on to plsql ), this is to find the tnsnames mentioned above. ora. This step is the most important.

Add the second environment variable (optional): "NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK". (AMERICAN_AMERICA.US7ASCII is of the ASCII encoding type. For other types, go to the server and check it online) (This step is correct for the time being. If the encoding is incorrect, garbled characters are generated ).

 

4. download and install the pl SQL Developer configuration application:

Open pl SQL Developer, click Cancel on the logon page, and choose tools> preferences> connection:

 

Oracle Home = D: oracleclient OCI library = D: oracleclientoci. dll

5. If you enable plsql again, you can log on to the database by entering the username and password with the oracledata option.

Case 3:

ORACLE server is not installed on this machine, but the oracle client is installed. pl SQL development, toad SQL development, SQL navigator, and other database management tools are also installed. An oracle server is installed on a virtual machine or another computer, that is, a virtual machine or another computer.

In this case, I use pl SQL development to remotely connect to the oracle server database as an example:

1. Open the net manager in the oracle client and configure the name and IP address of the database to be remotely connected. If the net manager does not have the name of the database to be remotely connected, create a new database.

2. The other steps are the same as 2-5 in the second case.

Case 4:

Oracle server is not installed on the local machine, pl SQL development, toad SQL development, SQL navigator, and other database management tools are not installed, but the oracle client is installed. An ORACLE server is installed on a virtual machine or another computer, that is, a virtual machine or another computer.

In this case, I use sqlplus in the oracle client to remotely connect to the oracle server database as an example:

1. Open the net manager in the oracle client and configure the name and IP address of the database to be remotely connected. If the net manager does not have the name of the database to be remotely connected, create a new database.

2. Same as step 2 in the second case.

3. In the same case as in step 3.

4. Enable sqlplus:

(1) If you use sys to log on, log on to the database as sysdba with the username: sys password: xxxxxx host string.

(2) If you use another user to log on, the user name is xxx password: xxxxxx host string: name of the database to be connected. log on to the database.

Note:

1. The server and client firewalls must be disabled;

2. We often encounter that the *** service cannot be started, so we need to enable the Net Configuration Assistant to fix the issue or create a *** service.

3. What should I do if I forget my Database Password? Follow these steps to change the password:

Start --> Run --> cmd

Input: sqlplus/nolog press ENTER

Enter connect/as sysdba and press Enter.

Unlock user: alter user system account unlock press ENTER

Change Password: alter user system identified by manager

4. How can I determine whether the database is running in archive mode or non-archive mode?

Go to dbastudio, and choose history> database> archive.

5. If the oracle server is installed on both the local host and other hosts, you must modify the environment variables if the local host is connected to another host.

To remotely connect to the oracle database on the server using PLsql, you need to do the following work on the server or client:

Step 1: first install the oracle client and PLSQL software on the client, that is, you can select to install the client during the oracle installation process.
Step 2: Install the oracle server (administrator-level) on the server.
Step 3: configure the local network service name:
Open Oracle Net Configuration Assistant of Oracle (under Configuration and porting tools)

Select Local Network Service name configuration, and next step

Next, set the service name. Current Oracle instance (database name)

Next, select the Protocol
Next, set host name and port

Test the connection

Step 2 install PL_ SQL _Developer
Directly pass the pls-setup.exeinstallation and enter the serial number in sn.txt. Click Next. The installation is successful.
Configure PL_ SQL
Click PL_ SQL after installation, without entering the user name and password. Go to Edit-> Preferrences in order and configure as follows:

Log on to pl SQL again, and enter the user name and password.



What services are required to connect to a remote database using the oracle client and plsql dev?

No images?
You only need to start the services required by the oracle client without additional services.