Project resource requirements
VMVare15 resource link: https://pan.baidu.com/s/1cVzeKjqu0Cmp4qMRDbFx8Q
Extraction code: tdn4
CentOS7 image file link: https://pan.baidu.com/s/14QyfiXYYObdynswJlOoI1g
Extraction code: 6cxp
oracle11 Linux version link: https://pan.baidu.com/s/16hWmzGudLjUEpkRzHAOGZw
Extraction code: y34w
MobaXterm link: https://pan.baidu.com/s/1vvxW4tHXhtQxjRdcpwDbDA
Extraction code: kle7
Oracle11 Linux silent installation
Linux environment installation address
Oracle installation tutorial address
Oracle server client connection
Configuration environment
according to the above Linux environment installation address as well as Oracle installation tutorial address To install two virtual machines, build Oracle environment on the two virtual machines, and install Oracle database.
the ip and host names of the two virtual machines established by the author are:
192.168.233.107 oracle01
192.168.233.108 oracle02
the author takes oracle01 as the server and oracle02 as the client as an example.
installation of two virtual machine devices vim:
[root@oracle01 ~]# yum install vim / / install vim
switching virtual machines:
[root@oracle02 ~]# yum install vim / / install vim
in the remote connection virtual machine, you can replace XShell5 with MobaXterm (both can be connected remotely, and the author uses MobaXterm).
Modify hosts file
[root@oracle01 ~]# vim /etc/hosts / / change as follows: 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.233.107 oracle01 192.168.233.108 oracle02
switching virtual machines:
[root@oracle02 ~]# vim /etc/hosts / / change as follows: 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.233.107 oracle01 192.168.233.108 oracle02
Check whether listening is on
if you want to establish a connection between two databases, first, set the listening service of the server and the service request of the client.
[root@oracle01 ~]# su oracle / / change to oracle user [oracle@oracle01 root]$ lsnrctl status //Check whether listening is started LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-OCT-2021 01:02:03 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 08-OCT-2021 21:20:01 Uptime 0 days 3 hr. 42 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /opt/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... Service "orclXDB" has 1 instance(s). Instance "orcl", status READY, has 1 handler(s) for this service... The command completed successfully
successful indicates that the monitoring is running normally; (the code shown by the author is the listening status of static registration, and how to change dynamic registration to static registration will be explained later)
switch the virtual machine, and do the same
Dynamic registration and static registration
registration is to register the database as a service to the listener. The client does not need to know the database name and instance name. It only needs to know the service name provided by the database to apply for connection to the database. The service name may or may not be the same as the instance name.
when the instance status is UNKNOWN, it indicates that the service is statically registered. At this time, the listener is used to indicate that it does not know any information about the instance. It checks whether the instance exists only when the client sends a connection request.
the dynamically registered database is indicated by the status READY or status BLOCKED (for a standby database) in the status information. Whenever the database is closed, the dynamically registered database will dynamically log out of the listener, and the related information will disappear from the status list. In this way, the listener always knows the status of the database whether it is running or closed. This information will be used for fallback and load balancing of connection requests.
Specific explanations can be seen: https://blog.csdn.net/xiaoxiong1212/article/details/6632521
Change dynamic registration to static registration
[oracle@oracle01 root]$ cd /opt/app/oracle/product/11.2.0/db_1/network/admin
[oracle@oracle01 admin]$ ll //View all files in the current folder
Total consumption 24
-rw-r--r--. 1 oracle oinstall 369 9 July 21-17:55 listener2109215 5546 PM.bak
-rw-r--r-- 1 oracle oinstall 553 10 August 16:18 listener.ora//Listening profile
drwxr-xr-x. 2 oracle oinstall 64 9 July 21-17:47 samples
-rw-r--r--. 1 oracle oinstall 187 5 July 2007 shrept.lst
-rw-r--r--. 1 oracle oinstall 219 9 July 21-17:55 sqlnet2109215 5546 PM.bak
-rw-r--r--. 1 oracle oinstall 219 9 July 21-17:55 sqlnet.ora
-rw-r----- 1 oracle oinstall 504 10 August 16:24 tnsnames.ora//tns protocol connection important files
[oracle@oracle01 admin]$ vim listener.ora //Modify the configuration as follows
# listener.ora Network Configuration File: /opt/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)
(GLOBAL_HOME = orcl)//Service name for external display
)
)
ADR_BASE_LISTENER = /opt/app/oracle
Configure tnsnames.ora
ORACLE describes the connection information through the connection descriptor in the tnsnames.ora file.
//Switch to oracle user on oracle 02 virtual machine
[oracle@oracle02 admin]$ cd /opt/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@oracle02 admin]$ ll
Total consumption 16
-rw-r--r-- 1 oracle oinstall 547 10 August 16:00 listener.ora
drwxr-xr-x. 2 oracle oinstall 64 10 April 15:32 samples
-rw-r--r--. 1 oracle oinstall 187 5 July 2007 shrept.lst
-rw-r--r--. 1 oracle oinstall 219 10 April 17:01 sqlnet.ora
-rw-r----- 1 oracle oinstall 494 10 August 16:24 tnsnames.ora
[oracle@oracle02 admin]$ vim tnsnames.ora //Modify the configuration as follows:
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle02)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL01 =
(DESCREPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Restart the virtual machine
after configuring the listener.ora file of oracle01 and the tnsnames.ora file of oracle02, restart the two virtual machines to make the configuration effective
New user
because the user name and password are required for connection, and the user provided with oracle database does not have permission to access, you can create a new user and authorize the connection permission and resource permission as the user to test the connection.
sqlplus /nolog SQL> connect / as sysdba SQL> create user user1 identified by 123456;//Create user user1 with password 123456 SQL> grant connect, resource to user1;//Grant connect and resource permissions to user1
Remote connection
oracle user switching to oracle 02 virtual machine
//sqlplus user name / password @ virtual machine host: port / service name sqlplus user1/123456@oracle01:1521/orcl SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 01:50:16 2021 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>
as shown above, the connection is successful.
Possible problems
- There is no lsnrctl command and no sqlplus command
Possible causes:
~/.bash_ The profile file was not configured successfully and needs to be reconfigured,
And source ~/.bash_profile - ora-12154 problem
Possible causes:
(1) The listening service is not started. Check whether the listening service is started through the lsnrctl status command
(2) The connected database instance is not registered in the listener
(3) The IP of the listener is configured as localhost
(4) The instance names in tnsnames.ora and listener.ora are inconsistent
(5) There is a problem with the client configuration. Check the tnsnames.ora file of the client