Required skills for programmer entry Oracle installation + LAN service + intranet penetration

Tip: This article is suitable for students who have a little Linux foundation to read. The
first part is Oracle installation under Linux. The
second part connects to Oracle under the local area network. The
third part penetrates through the internal network and accesses the desktop Oracle database from the external network.


Article Directory

Oracle installation under Linux

One environment preparation

1.1 Oracle11gR2 (Linux) installation package

  • Official website download: https://www.oracle.com/cn/downloads/
  • Baidu Cloud: Link: https://pan.baidu.com/s/1EiVO0a0obTemHntE47iysg Extraction code: f9ep

1.2 Computer environment

  • System: CentOS 7.5 64 bit
  • Memory: 1G (the official minimum requirement is 1G)
  • Hard disk: 40G (4.29G and 1.7G data files required for enterprise version installation)

1.3 Environmental inspection

[[email protected] /]# uname -m
x86_64
[[email protected] /]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

Two basic configuration

2.1 Modify the host name

 sed -i "s/HOSTNAME=localhost.localdomain/HOSTNAME=oracledb/" /etc/sysconfig/network
 hostname oracledb

2.2 Add a record corresponding to the host name and IP

  • View computer ip address
  • Install vim editor
  • Modify ip mapping
ifconfig
Insert picture description here
vim /etc/hosts
Insert picture description here

2.3 Close Selinux

sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config 
setenforce 0
Insert picture description here

2.4 Create users and groups

Create Oracle installation group oinstall, database administrator group dba, and oracle user

groupadd -g 200 oinstall
groupadd -g 201 dba
useradd -u 440 -g oinstall -G dba oracle #(主组oinstall,其它组:dba)
passwd oracle   #(P)     密码自己设置,需要记住,后面会用
Insert picture description here

2.5 Modify the system kernel

vim /etc/sysctl.conf  # 编辑 sysctl.conf,末尾处添加下面的参数

net.ipv4.ip_local_port_range= 9000 65500
fs.file-max = 6815744
kernel.shmall = 10523004
kernel.shmmax = 6465333657
kernel.shmmni = 4096
kernel.sem = 250 32000 100128
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_max=1048576
fs.aio-max-nr = 1048576

sysctl -p  # 使配置生效

2.6 Modify system resource limits

vim /etc/security/limits.conf # 编辑 limits.conf,末尾处添加下面的参数

oracle  soft  nproc  2047
oracle  hard  nproc  16384
oracle  soft  nofile  1024
oracle  hard  nofile  65536

2.7 Modify user authentication options

vim /etc/pam.d/login
找到这一行:session    required   pam_namespace.so ,
在其下一行添加一条pam_limits.so
session    required     pam_limits.so
Insert picture description here

2.8 Modify user profile

vim /etc/profile    

if [ $USER ="oracle" ]; then

      if [ $SHELL = "/bin/ksh" ];then

          ulimit -p 16384

          ulimit -n 65536

      else

          ulimit -u 16384 -n 65536

      fi

fi
Insert picture description here

2.9 Create an installation directory and assign permissions

mkdir -p /opt/app/oracle/ 
chmod 755 /opt/app/oracle/ 
chown oracle.oinstall -R /opt/app/oracle/

Three Oracle settings

3.1 Oracle environment variables

su - oracle # 切换到 oracle 用户,输入刚才的密码
vim ~/.bash_profile # 编辑
注释掉最后两行,在末尾添加如下参数
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export ORACLE_PID=ora11g
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
#保存,退出
source ~/.bash_profile # 使配置立即生效
env | grep ORA # 查看环境变量是否完成

3.2 Installation dependencies

Need to rely on:binutils-2.23.52.0.1-12.el7.x86_64 compat-libcap1-1.10-3.el7.x86_64 compat-libstdc++-33-3.2.3-71.el7.i686 compat-libstdc++-33-3.2.3-71.el7.x86_64 gcc-4.8.2-3.el7.x86_64 gcc-c++-4.8.2-3.el7.x86_64 glibc-2.17-36.el7.i686 glibc-2.17-36.el7.x86_64 glibc-devel-2.17-36.el7.i686 glibc-devel-2.17-36.el7.x86_64 ksh libaio-0.3.109-9.el7.i686 libaio-0.3.109-9.el7.x86_64 libaio-devel-0.3.109-9.el7.i686 libaio-devel-0.3.109-9.el7.x86_64 libgcc-4.8.2-3.el7.i686 libgcc-4.8.2-3.el7.x86_64 libstdc++-4.8.2-3.el7.i686 libstdc++-4.8.2-3.el7.x86_64 libstdc++-devel-4.8.2-3.el7.i686 libstdc++-devel-4.8.2-3.el7.x86_64 libXi-1.7.2-1.el7.i686 libXi-1.7.2-1.el7.x86_64 libXtst-1.2.2-1.el7.i686 libXtst-1.2.2-1.el7.x86_64 make-3.82-19.el7.x86_64 sysstat-10.1.5-1.el7.x86_64 unixODBC-2.3.1-6.el7.x86_64 or later unixODBC-2.3.1-6.el7.i686 or later unixODBC-devel-2.3.1-6.el7.x86_64 or later unixODBC-devel-2.3.1-6.el7.i686 or later

Switch root user to install

#安装
yum -y install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*i686 compat-libstdc++-33*.devel compat-libstdc++-33 compat-libstdc++-33*.devel gcc gcc-c++ glibc glibc*.i686 glibc-devel glibc-devel*.i686 ksh libaio libaio*.i686 libaio-devel libaio-devel*.devel libgcc libgcc*.i686 libstdc++ libstdc++*.i686 libstdc++-devel libstdc++-devel*.devel libXi libXi*.i686 libXtst libXtst*.i686 make sysstat unixODBC unixODBC*.i686 unixODBC-devel unixODBC-devel*.i686
#检查是否全部安装
rpm -q binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXi libXtst make sysstat unixODBC unixODBC-devel

3.3 install oracle

  • Upload the installation package to/opt
  • Unzip to the same directory
yum -y install unzip # 安装unzip,如已安装请忽略
unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip

3.4 Modify parameters

cd /opt/database/response # 进入 response 目录

cp db_install.rsp db_install_copy.rsp # 将 db_install.rsp 备份一份,以免修改出错

vim db_install.rsp # 编辑

#需要修改的地方,仔细比对,耐心点
oracle.install.option=INSTALL_DB_SWONLY

ORACLE_HOSTNAME=oracledb

UNIX_GROUP_NAME=oinstall

INVENTORY_LOCATION=/opt/app/oracle/oraInventory

SELECTED_LANGUAGES=en,zh_CN

ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1

ORACLE_BASE=/opt/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.DBA_GROUP=dba

oracle.install.db.OPER_GROUP=oinstall

oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

oracle.install.db.config.starterdb.globalDBName=ora11g

oracle.install.db.config.starterdb.SID=ora11g

oracle.install.db.config.starterdb.memoryLimit=1500

oracle.install.db.config.starterdb.password.ALL=oracle

oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE

DECLINE_SECURITY_UPDATES=true    //一定要设为 true

#保存并退出

less /opt/database/response/db_install.rsp |grep -v "#"|grep -v "^$"

# 执行命令
xhost +

3.5 Start installation

su - oracle # 切换到 oracle 用户

cd /opt/database/ #进入/opt/database目录
# 安装命令
./runInstaller -silent -force -responseFile /opt/database/response/db_install.rsp
#当出现 Successfully Setup Software. 证明已经安装成功,然后根据提示以 root 用户执行脚本
sh /opt/app/oracle/oraInventory/orainstRoot.sh

sh /opt/app/oracle/product/11.2.0/db_1/root.sh
Insert picture description here

3.6 Configure monitoring

su - oracle # 切换到 oracle 用户

$ORACLE_HOME/bin/netca /silent /responseFile /opt/database/response/netca.rsp # 配置监听

lsnrctl status # 查看舰艇状态

3.7 Silent dbca build database

su - root # 切换到 root 用户vim /opt/database/response/dbca.rsp

GDBNAME = "orcl" # 78 行

SID="orcl" # 149行

CHARACTERSET="AL32UTF8" # 415行

NATIONALCHARACTERSET="UTF8" # 425行

su - oracle # 切换到 oracle 用户

$ORACLE_HOME/bin/dbca -silent -responseFile /opt/database/response/dbca.rsp # 建库

#执行完后会先清屏,清屏之后没有提示,直接输入oracle用户的密码,回车,再输入一次,再回车。
Insert picture description here

3.8 Start the database

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 16 14:26:57 2015Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to an idle instance.SQL> startSP2-1506: START, @ or @@ command has no argumentsSQL> startupORACLE instance started. Total System Global Area 3290345472 bytesFixed Size                  2217832 bytesVariable Size            1795164312 bytesDatabase Buffers         1476395008 bytesRedo Buffers               16568320 bytesDatabase mounted.Database opened.SQL>

# 使用 show parameter;或者 select table_name from dba_tables 看看是否正常

3.9 Configure to automatically start monitoring and start Oracle

su - root # 切换到 root 用户

vim /etc/oratab # 编辑

*:/home/oracle/oracle92:N

这一行中 * 改为数据库的SID orcl, 第三段的N改为Y

# 保存并退出

vim /etc/rc.local # 编辑

在文件末尾增加如下内容

su - oracle -c 'dbstart' su - oracle -c 'lsnrctl start'

#保存并退出

chmod +x /etc/rc.local # 配置文件权限

3.10 Open ports

查看端口是否开放:firewall-cmd --query-port=1521/tcp

永久开放1521端口号:firewall-cmd --permanent --zone=public --add-port=1521/tcp

重启防火墙: systemctl restart firewalld.service

查看防火墙状态

systemctl status firewalld.service

Four questions

4.1 The service is not successfully monitored

Insert picture description here
  • Solution
# oracle用户下
cd/opt/app/oracle/product/11.2.0/db_1/network/admin

vim listener.ora

#增加配置
 (SID_LIST =

              (SID_DESC =

              (GLOBAL_DBNAME = orcl)

              (ORACLE_HOME = /opt/app/oracle/product/11.2.0/db_1)

              (SID_NAME = orcl)

      )

)
# 重启监听
lsnrctl reload

# 查看监听
lsnrctl status
Insert picture description here

4.2 The computer is shut down, and the oracle service is shut down

source ~/.bash_profile # 使配置立即生效
[[email protected] ~]$ sqlplus / as sysdba
#执行sql语句
startup

Connect to Oracle under the LAN

A preparation

  • Oracle environment is installed
  • The computers to be connected are in the same local area network

Two connection

2.1 Linux computer ip view

ifconfig
Insert picture description here

2.2 Own computer cmd

Insert picture description here
Insert picture description here

connection succeeded

2.3 Navicat visualization tool connection

Insert picture description here
Insert picture description here

Internal network penetration, external network access to desktop Oracle database

1. Environmental requirements

  • An Alibaba Cloud server centos7.6 x86
  • A networked linux version oracle computer centos7.5 x86

Two basic introduction

Insert picture description here
  • Download tool: frps download address: https://github.com/fatedier/frp/releases/tag/v0.33.0
  • Note that the version is consistent
  • Create a folder on the Alibaba Cloud server and computer respectively, and upload the installation package
Insert picture description here
Alibaba Cloud server configuration
#解压
tar zxvf frp_0.33.0_linux_amd64.tar.gz

cd frp_0.33.0_linux_amd64

# 执行命令
nohup ./frps -c frps.ini &

#查看日志
tail -f nohup.out
Insert picture description here
Linux computer configuration
#解压
tar zxvf frp_0.33.0_linux_amd64.tar.gz

cd frp_0.33.0_linux_amd64

#编辑 vim
vim frpc.ini

#修改
[common]
server_addr = 自己的服务器地址
server_port = 7000

[ssh]
type = tcp
local_ip =自己电脑的ip地址
local_port =1521
remote_port = 6000

#保存
#注意阿里云服务器需要开放:7000,6000端口

# 执行命令
nohup ./frps -c frps.ini &

#查看日志
tail -f nohup.out
External computer test cmd
telnet 阿里云地址 6000
Navicat visualization tool connection
Insert picture description here
Insert picture description here
At this point, we can access the database on the company computer at home