一、准备工作

1.准备CentOS 7 系统环境

2.下载 Oracle 11g

（1）Oracle官方网站:http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 我的机器安装的是CentOS7 64位系统，因此下载的Oracle版本也是64位的

（2）下载完成后，有两个压缩文件

p13390677112040Linux-x86-641of7.zip
p13390677112040Linux-x86-642of7.zip

3.通过Xftp将压缩文件上传到 /db/

4.安装依赖包，安装依赖包之前，个人建议将yum源修改为aliyun源，下载速度快些，执行如下命令即可：cd /etc

mv yum.repos.d yum.repos.d.bak
mkdir yum.repos.d
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum clean all
yum makecache若提示：正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 失败：未知的名称或服务。则执行：echo 'nameserver 8.8.8.8'>>/etc/resolv.conf

5.修改成功后，安装如下依赖包：

yum -y install binutils \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
expat \
gcc \
gcc-c++ \
glibc \
glibc-common \
glibc-devel \
glibc-headers \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
pdksh \
sysstat \
unixODBC \
unixODBC-devel

6.使用如下命令检查依赖是否安装完整

rpm -q \
binutils \
compat-libstdc++-33 \
elfutils-libelf \
elfutils-libelf-devel \
expat \
gcc \
gcc-c++ \
glibc \
glibc-common \
glibc-devel \
glibc-headers \
libaio \
libaio-devel \
libgcc \
libstdc++ \
libstdc++-devel \
make \
pdksh \
sysstat \
unixODBC \
unixODBC-devel | grep "not installed"

7.发现 pdksh 没有安装

 

 

8.执行 yum -y install pdksh，发现CentOS 7 没有相关安装包可用

9.通过wget命令直接下载pdksh的rpm包，我下载到了/tmp/

wget -O /tmp/pdksh-5.2.14-37.el5_8.1.x86_64.rpm http://vault.centos.org/5.11/os/x86_64/CentOS/pdksh-5.2.14-37.el5_8.1.x86_64.rpm

下载完成后，进入 /tmp/

cd /tmp/
rpm -ivh pdksh-5.2.14-37.el5_8.1.x86_64.rpm

10.再次检查依赖包是否安装完整

11.添加oracle用户组和用户

groupadd oinstall
groupadd dba
groupadd asmdba
groupadd backupdba
groupadd dgdba
groupadd kmdba
groupadd racdba
groupadd oper
useradd -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba,oper -m oracle

12.添加完成后，查看oracle用户

id oracle

#设置oracle密码
echo oracle |passwd --stdin oracle

13.配置hostname（本机IP映射）

vim /etc/hosts
192.168.1.101 oracle

14.配置系统内核参数

[root@oracle ~]$ vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 16451328
kernel.shmmax = 33692319744
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

sysctl -p

 15.限制oracle用户的shell权限

vim /etc/security/limits.conf

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

vim /etc/pam.d/login
session  required   /lib64/security/pam_limits.so
session  required   pam_limits.so

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

16.关闭selinux和防火墙

[root@oracle ~]$ cat /etc/selinux/config

SELINUX=disabled
SELINUXTYPE=targeted

[root@oracle ~]$ systemctl status firewalld

17.创建oracle安装目录

mkdir -p /db/app/oracle/product/11.2.0
mkdir /db/app/oracle/oradata
mkdir /db/app/oracle/inventory
mkdir /db/app/oracle/fast_recovery_area
chown -R oracle:oinstall /db/app/oracle
chmod -R 775 /db/app/oracle

18.配置用户环境

su  -  oracle
vi ~/.bash_profile
umask 022
export ORACLE_HOSTNAME=oracle
export ORACLE_BASE=/db/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/
export ORACLE_SID=ORCL                         #数据库实例名称ORCL
export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/jdk/bin:$PATH
export LC_ALL="en_US"
export LANG="en_US"
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS

source ~/.bash_profile

19.解压安装包

unzip p13390677112040Linux-x86-641of7.zip -d /db/
unzip p13390677112040Linux-x86-642of7.zip -d /db/

20.解压完成后

mkdir /db/etc/
cp /db/database/response/* /db/etc/

21.配置响应文件

vim /db/etc/db_install.rsp
oracle.install.option=INSTALL_DB_SWONLY
DECLINE_SECURITY_UPDATES=true
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/db/app/oracle/inventory
SELECTED_LANGUAGES=en,zh_CN
ORACLE_HOSTNAME=oracle
ORACLE_HOME=/db/app/oracle/product/11.2.0
ORACLE_BASE=/db/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=true
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oper

二、开始安装oracle

1. 切换oracle用户

su -  oracle
[oracle@oracle /db/database]$ ./runInstaller -silent -ignorePrereq -responseFile /db/etc/db_install.rsp

安装完成，提示Successfully Setup Software

2. 切换root用户

su - root
sh /db/app/oracle/inventory/orainstRoot.sh
sh /db/app/oracle/product/11.2.0/root.sh
---执行结果
[root@oracle /db]$ sh  /db/app/oracle/inventory/orainstRoot.sh
Changing permissions of /db/app/oracle/inventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /db/app/oracle/inventory to oinstall.
The execution of the script is complete.
[root@oracle /db]$ sh  /db/app/oracle/product/11.2.0/root.sh
Check /db/app/oracle/product/11.2.0/install/root_oracle_2022-01-04_12-03-29.log for the output of root script
[root@oracle /db]$

3.配置静默监听

su - oracle
netca /silent /responsefile /db/etc/netca.rsp
---执行结果
[oracle@oracle /db/database/response]$ netca /silent /responsefile /db/etc/netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /db/etc/netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /db/app/oracle/product/11.2.0/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

#查看监听的端口
netstat -tnulp | grep 1521

4.静默创建数据库 TOTALMEMORY 设置为总内存的80%

切换为root用户
su - root
vi /db/etc/dbca.rsp //打开文件后可通过搜索将“=”右边参数值补齐
GDBNAME = "orcl"
SID = "orcl"
SYSPASSWORD = "oracle"
SYSTEMPASSWORD = "oracle"
SYSMANPASSWORD = "oracle"
DBSNMPPASSWORD = "oracle"
DATAFILEDESTINATION =/db/app/oracle/oradata
RECOVERYAREADESTINATION=/db/app/oracle/fast_recovery_area
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET= "AL16UTF16"
TOTALMEMORY = "1638"

5.执行静默建库(切换为oracle用户)

su - oracle
dbca -silent -responseFile /db/etc/dbca.rsp
---执行结果
[oracle@oracle /db/database/response]$ dbca -silent -responseFile /db/etc/dbca.rsp
Copying database files
1% complete
3% complete
11% complete
18% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/db/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.

6.查看oracle实例进程

ps -ef | grep ora_ | grep -v grep
---执行结果
[oracle@oracle /db/database/response]$ ps -ef | grep ora_ | grep -v grep
oracle    12202      1  0 12:33 ?        00:00:00 ora_pmon_orcl11g
oracle    12204      1  0 12:33 ?        00:00:00 ora_psp0_orcl11g
oracle    12207      1  3 12:33 ?        00:00:55 ora_vktm_orcl11g
oracle    12212      1  0 12:33 ?        00:00:00 ora_gen0_orcl11g
oracle    12214      1  0 12:33 ?        00:00:00 ora_diag_orcl11g
oracle    12216      1  0 12:33 ?        00:00:00 ora_dbrm_orcl11g
oracle    12218      1  0 12:33 ?        00:00:01 ora_dia0_orcl11g
oracle    12220      1  0 12:33 ?        00:00:00 ora_mman_orcl11g
oracle    12222      1  0 12:33 ?        00:00:00 ora_dbw0_orcl11g
oracle    12224      1  0 12:33 ?        00:00:00 ora_lgwr_orcl11g
oracle    12226      1  0 12:33 ?        00:00:00 ora_ckpt_orcl11g
oracle    12228      1  0 12:33 ?        00:00:00 ora_smon_orcl11g
oracle    12230      1  0 12:33 ?        00:00:00 ora_reco_orcl11g
oracle    12232      1  0 12:33 ?        00:00:00 ora_mmon_orcl11g
oracle    12234      1  0 12:33 ?        00:00:01 ora_mmnl_orcl11g
oracle    12236      1  0 12:33 ?        00:00:00 ora_d000_orcl11g
oracle    12238      1  0 12:33 ?        00:00:00 ora_s000_orcl11g
oracle    12249      1  0 12:33 ?        00:00:00 ora_qmnc_orcl11g
oracle    12266      1  0 12:33 ?        00:00:00 ora_cjq0_orcl11g
oracle    12275      1  0 12:33 ?        00:00:00 ora_q000_orcl11g
oracle    12277      1  0 12:33 ?        00:00:00 ora_q001_orcl11g
oracle    12786      1  0 12:43 ?        00:00:00 ora_smco_orcl11g
oracle    13316      1  0 12:53 ?        00:00:00 ora_w000_orcl11g

#查看smon系统监控进程，可以判断oracle数据库是否已经启动成功
ps -ef|grep smon  
---执行结果
[oracle@oracle /db/app/oracle/oradata/orcl]$ ps -ef|grep smon
oracle     2300      1  0 13:58 ?        00:00:00 ora_smon_ORCL
oracle    11145   9680  0 16:46 pts/2    00:00:00 grep --color=auto smon

7.查看监听状态

lsnrctl status
---执行结果
[oracle@oracle /db/database/response]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-JAN-2022 13:02:54

Copyright (c) 1991, 2013, 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.4.0 - Production
Start Date                04-JAN-2022 12:28:47
Uptime                    0 days 0 hr. 34 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /db/app/oracle/product/11.2.0/network/admin/listener.ora
Listener Log File         /db/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl11g", status READY, has 1 handler(s) for this service...
Service "orcl11gXDB" has 1 instance(s).
  Instance "orcl11g", status READY, has 1 handler(s) for this service...
The command completed successfully
#停止监听服务
lsnrctl stop
#重启监听服务
lsnrctl start

8.切换为oracle用户，登录sqlplus

su - oracle
sqlplus -V  #查看oracle版本
[oracle@oracle /db/app/oracle/oradata/orcl]$ ps -ef|grep smon
oracle     2300      1  0 13:58 ?        00:00:00 ora_smon_ORCL  #数据库实例名称ORCL
oracle    11145   9680  0 16:46 pts/2    00:00:00 grep --color=auto smon
#设置oracle sid
export ORACLE_SID=ORCL
#默认本地连接数据库，不需要密码
sqlplus / as sysdba
#基于用户验证
sqlplus system/oracle
#基于操作系统验证
sqlplus sys/oracle as sysdba
#远程登陆
sqlplus system/oracle@192.168.1.101:1521/ORCL

三、检查数据库功能

1. 常见错误问题解决

#查看数据库
SQL> select * from v$database;

#查看数据库状态
SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ WRITE  #可读可写

startup #启动
select status from v$instance;  查看实例状态

#错误1：
ls命令查看  /db/app/oracle/admin/orcl/pfile/下有没有init.ora.*****类似的文件，拷贝覆盖报错的文件
cp /db/app/oracle/admin/orcl/pfile/init.ora.042022123252  /db/app/oracle/product/11.2.0/dbs/initORCL.ora

#错误2：
ORA-00845: MEMORY_TARGET not supported on this system  #分配内存不够
#切换为root用户
df  -h
mount -o remount,size=2G /dev/shm/
df -h | grep shm

---执行结果
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size     2253944 bytes
Variable Size  1056967560 bytes
Database Buffers   654311424 bytes
Redo Buffers     6795264 bytes

SQL> select status from v$instance;

STATUS
------------------------
STARTED

SQL>
oracle 启动过程 :
nomount --->加载参数文件，启动实例
mount --->加载控制文件。
open --->加载数据文件。

#错误3：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted

#解决方案：
优雅的关闭
SQL> shutdown immediate

cd /db/app/oracle/product/11.2.0/dbs && fuser -u lkORCL

[root@oracle /db/app/oracle/product/11.2.0/dbs]$ fuser -u lkORCL
/db/app/oracle/product/11.2.0/dbs/lkORCL: 12202(oracle) 12204(oracle) 12212(oracle)
12216(oracle) 12220(oracle) 12222(oracle) 12224(oracle) 12226(oracle) 12228(oracle) 12230(oracle) 12232(oracle) 12234(oracle)
果然该文件没释放，用fuser命令kill掉：
fuser -k lkORCL
[root@oracle /db/app/oracle/product/11.2.0/dbs]$ fuser -k lkORCL
/db/app/oracle/product/11.2.0/dbs/lkORCL: 12202 12204 12212 12216 12220 12222 12224 12226 12228 12230 12232 12234

su  - oracle
SQL> startup
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size     2253944 bytes
Variable Size  1056967560 bytes
Database Buffers   654311424 bytes
Redo Buffers     6795264 bytes
Database mounted.
Database opened.

（1）查看数据库编码
select userenv('language') from dual;
（2）查看数据库版本信息
select * from v$version;
（3）激活scott用户
alter user scott account unlock; //若遇到su - oracle用户下无法解锁该用户，请用远程连接工具通过system用户进行解锁，这里不影响
alter user scott identified by tiger; //若遇到su - oracle用户下无法修改密码，请用远程连接工具通过system用户进行修改，这里不影响
select username from all_users;

2.参考文档 https://www.cnblogs.com/wanderwei/p/11458979.html

三、网络配置

1. listener.ora配置 -->默认动态注册

[root@oracle ~]$ find / -type f  -name  'listener.ora'
/db/app/oracle/product/11.2.0/network/admin/listener.ora
[root@oracle /db/app/oracle/product/11.2.0/network/admin]$ vim  listener.ora
# listener.ora Network Configuration File: /db/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /db/app/oracle

2. listener.ora 配置 -->修改成静态注册

[oracle@oracle /db/app/oracle/product/11.2.0/network/admin]$ vim  listener.ora
# listener.ora Network Configuration File: /db/app/oracle/product/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (SID_NAME = PLSExtProc)
    (ORACLE_HOME =/db/app/oracle/product/11.2.0/)
    (PROGRAM = extproc)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME =/db/app/oracle/product/11.2.0/)
    (SID_NAME = orcl)
  )
)
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /db/app/oracle
--执行结果
[oracle@oracle ~]$ sqlplus system/oracle@192.168.1.101:1521/ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 9 15:32:29 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. tnsnames.ora 配置 --->修改成别名(可以配置多组别名)

[oracle@oracle /db/app/oracle/product/11.2.0/network/admin]$ vim  tnsnames.ora

# tnsnames.ora Network Configuration File: /db/app/oracle/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

haizhi =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
---执行结果
[oracle@oracle]$ sqlplus  system/oracle@haizhi

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 9 13:32:17 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

四、oracle相关操作

1. 用户限制解除

set linesize 500
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

2. 开归档

mkdir -p /u01/arch
sqlplus / as sysdba
archive log list;
show parameter dest;
alter system set log_archive_dest_1='location=/u01/arch/';
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

3. 用户、角色、权限

#创建用户=创建库
SQL>create user haizhi identified by haizhi default tablespace users;
SQL>create user zhang identified by zhang  default tablespace users;
#查询用户默认表空间、临时表空间
SQL>select username, default_tablespace, temporary_tablespace from dba_users;
#修改用户的信息：
#修改密码
SQL>Alter user haizhi  identified by "123456";
#修改用户表空间
SQL>Alter user haizhi  default tablespace users;
#修改用户临时表空间
SQL>Alter user haizhi temporary tablespace xxxxx;
#强制用户修改口令字：
SQL>Alter user haizhi password expire;
#用户加锁
SQL>Alter user haizhi account lock; // 加锁
SQL>select username,account_status,lock_date from dba_users;  //查看哪些用户被锁
---执行结果
HAIZHI
EXPIRED & LOCKED
2022-01-04 16:13:21

SQL>select LOCK_DATE,username from dba_users where username='haizhi';  //查看某个用户被锁
SQL>select * from dba_users where username='haizhi'; //查看某个用户被锁
#用户解锁
Alter user haizhi account unlock; // 解锁
---执行结果
HAIZHI
EXPIRED
#删除用户
SQL>drop user 用户名;  //用户没有建任何实体
SQL>drop user 用户名 CASCADE; // 将用户及其所建 实体全部删除

#查询用户会话信息：
SQL>select username, sid, serial#, machine from v$session;
SQL> set linesize 500
SQL> /

#删除用户会话信息：
SQL>select sid,serial#,username,program from v$session;  
SQL>Alter system kill session '138, 37';  //对应sid,serial#

#查询用户SQL语句：
SQL>select user_name, sql_text from v$open_cursor where user_name not in ('SYS','SYSTEM');
#创建用户及授权管理权限
SQL>Create user user50 identified by user50;
业务用户授权以下两个就行
SQL>grant connect, resource to user50;
注意：需要退出，重新使用user50用户登陆数据库
SQL>Create table t1(id int);
SQL> set linesize 500
SQL> select * from  user_objects;

#创建用户授权dba权限
SQL> Create user user60 identified by user60;
SQL> grant dba to user60;

4. 管理角色

#建一个角色
SQL>create role role1;
#授权给角色
grant create any table,create procedure to role1;
#授予角色给用户
grant role1 to haizhi;
#查看角色所包含的权限
select * from role_sys_privs;
#系统权限只能由DBA用户回收
Revoke connect, resource from user50;
select * from v$parameter

小技巧：解决oracle数据库在(>sql)下打错命令不能退格和删除的问题

vi ~/bash_profile
stty erase ^H
source ~/.bash_profile