环境准备
软件准备
安装操作系统
操作系统使用virtualbox虚拟机安装的centos8服务器，该步骤省略，教程很多的。
该环境是有网的，所以不设置yum源，如果是没网的内网环境，自行挂载镜像包作为yum源。
服务器使用的是ubuntu 22.04 上 Oracle VM VirtualBox 虚拟机安装的 CentOS 8 ，基于windows和基于linux安装虚拟机没有影响。
服务器的规格是： 2C4G
CentOS 8 采用最小化安装
修改主机名和配置hosts
是要设置的主机名: centos8
是主机的 IP: 10.0.2.15
设置主机名
hostnamectl set-hostname centos8 # 设置 hostname # 查看
关闭防火墙与Selinux
防火墙和selinux按照需求关闭，我这是测试环境，索性关闭
关闭 selinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config setenforce 0 # 输入 getenforce 返回 Permissive 则是临时关闭状态， # 重启后是 Disabled ，完全关闭状态。 getenforce sestatus
关闭防火墙
systemctl stop firewalld.service systemctl disable firewalld.service # 查看是否关闭 systemctl status firewalld.service
安装数据库
下面的安装步骤是在 root 下执行的
执行预安装
执行预安装
dnf -y install oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm
安装的依赖具体内容如下：
bc bind-libs bind-libs-lite bind-license bind-utils binutils checkpolicy compat-openssl10 fstrm glibc-devel glibc-headers gssproxy kernel-headers keyutils ksh libICE libSM libX11 libX11-common libX11-xcb libXau libXcomposite libXext libXi libXinerama libXmu libXrandr libXrender libXt libXtst libXv libXxf86dga libXxf86misc libXxf86vm libdmx libev libmaxminddb libnsl libpkgconf libverto-libev libxcb libxcrypt-devel lm_sensors-libs make net-tools nfs-utils pkgconf pkgconf-m4 pkgconf-pkg-config policycoreutils-python-utils protobuf-c python3-audit python3-bind python3-libsemanage python3-ply python3-policycoreutils python3-pyyaml python3-setools quota quota-nls rpcbind smartmontools sysstat tar unzip xorg-x11-utils xorg-x11-xauth Installing geolite2-city geolite2-country
可以在 /var/log/oracle-database-preinstall-23c 目录下查看相关日志
安装Oracle 23c
使用安装命令：
dnf -y install oracle-database-free-23c-1.0-1.el8.x86_64.rpm
创建和配置Oracle数据库服务实例
运行脚本：
/etc/init.d/oracle-free-23c configure
输出：
[root@centos8 ~]# /etc/init.d/oracle-free-23c configure
Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Passwords do not match. Enter the password:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
**********
Enter SYSTEM user password:
**********
Enter PDBADMIN User Password:
************
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.
Connect to Oracle Database using one of the connect strings:
Pluggable database: centos8/FREEPDB1
Multitenant container database: centos8
-------------------------------------------------------------------------------
设置oracle用户的环境配置
切换用户然后编辑 ~/.bash_profile
su - oracle vi ~/.bash_profile
文件最后添加变量
# oracle settings export ORACLE_SID=FREE export ORAENV_ASK=NO export ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree export PATH=$ORACLE_HOME/bin:$PATH
刷新环境变量
source ~/.bash_profile
简单使用
启动数据库
1、启动监听
lsnrctl start
查看监听状态：
$ lsnrctl status LSNRCTL for Linux: Version 23.0.0.0.0 - Developer-Release on 09-MAY-2023 10:02:25 Copyright (c) 1991, 2023, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.2.15)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 23.0.0.0.0 - Developer-Release Start Date 09-MAY-2023 00:27:46 Uptime 0 days 9 hr. 34 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service FREE Listener Parameter File /opt/oracle/product/23c/dbhomeFree/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/centos8/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.2.15)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "FREE" has 1 instance(s). Instance "FREE", status READY, has 1 handler(s) for this service... Service "FREEXDB" has 1 instance(s). Instance "FREE", status READY, has 1 handler(s) for this service... Service "faf3c4adb9132c47e0550a0027434e0c" has 1 instance(s). Instance "FREE", status READY, has 1 handler(s) for this service... Service "freepdb1" has 1 instance(s). Instance "FREE", status READY, has 1 handler(s) for this service... The command completed successfully
2、启动数据库
使用sqlplus连接数据库后输入startup
$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 9 10:06:07 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL> startup;
查询数据库信息
查看数据库版本信息：
SQL> SELECT BANNER_FULL FROM v$version; BANNER_FULL -------------------------------------------------------------------------------- Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0
查询数据库和实例以及容器数据库信息
SQL> SELECT name, db_unique_name, log_mode, flashback_on, cdb FROM v$database; NAME DB_UNIQUE_NAME LOG_MODE FLASHBACK_ON CDB --------- ------------------------------ ------------ ------------------ --- FREE FREE NOARCHIVELOG NO YES SQL> SELECT instance_name, version, status, database_status FROM v$instance; INSTANCE_NAME VERSION STATUS DATABASE_STATUS ---------------- ----------------- ------------ ----------------- FREE 23.0.0.0.0 OPEN ACTIVE
查看sga信息
SQL> SELECT * FROM v$sgainfo; NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 10042432 No 0 Redo Buffers 4530176 No 0 Buffer Cache Size 436207616 Yes 0 In-Memory Area Size 0 No 0 Shared Pool Size 671088640 Yes 0 Large Pool Size 16777216 Yes 0 Java Pool Size 0 Yes 0 Streams Pool Size 0 Yes 0 Shared IO Pool Size 67108864 Yes 0 Data Transfer Cache Size 0 Yes 0 Granule Size 16777216 No 0 NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Maximum SGA Size 1155423296 No 0 Startup overhead in Shared Pool 235549832 No 0 Free SGA Memory Available 16777216 0 14 rows selected.
创建测试用户和表
1、 查看当前连接
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT
2、查看有哪些可拔插数据库
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
3、 进入可拔插数据库
SQL> alter session set container=FREEPDB1; Session altered. SQL> show con_name CON_NAME ------------------------------ FREEPDB1
4、 查看插拔数据库中有哪些表空间及其数据文件
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 MB FROM DBA_DATA_FILES;
5、创建测试用户
SQL> create user testuser identified by 123456 default tablespace users; User created. SQL> GRANT connect, resource, unlimited tablespace to testuser; Grant succeeded. SQL>
6、使用新建的测试用户连接可拔插数据库
$ sqlplus testuser/123456@10.0.2.15:1521/FREEPDB1 SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 9 11:20:07 2023 Version 23.2.0.0.0 Copyright (c) 1982, 2023, Oracle. All rights reserved. Connected to: Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 SQL>
7、创建测试表
SQL> CREATE TABLE personnel (id NUMBER(2) CONSTRAINT PK_EMP PRIMARY KEY, name VARCHAR2(20), age NUMBER(2)); Table created.
8、增删改查数据
SQL> INSERT INTO personnel(id, name, age) VALUES(1, 'test1', 18);
1 row created.
SQL> INSERT INTO personnel(id, name, age) VALUES(2, 'test2', 19);
1 row created.
SQL> INSERT INTO personnel(id, name, age) VALUES(3, 'test3', 20);
1 row created.
SQL> INSERT INTO personnel(id, name, age) VALUES(4, 'test4', 21);
1 row created.
SQL> SELECT * FROM personnel;
ID NAME AGE
---------- -------------------- ----------
1 test1 18
2 test2 19
3 test3 20
4 test4 21
SQL> DELETE FROM personnel WHERE id = 2;
1 row deleted.
SQL> SELECT * FROM personnel;
ID NAME AGE
---------- -------------------- ----------
1 test1 18
3 test3 20
4 test4 21
SQL> UPDATE personnel SET name = 'testname' WHERE id = 3;
1 row updated.
SQL> SELECT * FROM personnel;
ID NAME AGE
---------- -------------------- ----------
1 test1 18
3 testname 20
4 test4 21
SQL> truncate table personnel;
Table truncated.
SQL> SELECT * FROM personnel;
no rows selected
9、删除表
SQL> drop table personnel; Table dropped.
10、删除用户
删除用户时，该用户必须没有连接，且用dba用户在创建用户的可拔插数据库下删除。
SQL> drop user testuser; User dropped.
停止数据库
sqlplus连接数据库并停止
SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down.
sqlplus优化
使用历史命令
在使用默认sqlplus时，使用方向键上下左右时会报乱码。可使用readline和rlwrap解决。
下载安装readline
dnf -y install readline readline-devel
下载安装rlwrap
wget https://github.com/hanslub42/rlwrap/releases/download/0.46.1/rlwrap-0.46.1.tar.gz tar -zxvf rlwrap-0.46.1.tar.gz cd rlwrap-0.46.1 ./configure make make install
若缺少gcc、make之类的依赖自己安装一下即可。
使用：
rlwrap sqlplus / as sysdba
如果嫌弃这样麻烦，可以使用 alias 做重命名一下。把 alias 添加到.bash_profile 文件中。
su - oracle echo "alias sqlplus='rlwrap sqlplus'" >> .bash_profile source .bash_profile
设置行列宽度
pagesize 默认是14，linesize 默认是80 。 为了 美观，我们一般根据自己的屏幕分辨率来设置。
SQL> show pagesize; pagesize 14 SQL> show linesize; linesize 80 SQL> set pagesize 40; SQL> set linesize 200; SQL> show pagesize; pagesize 40 SQL> show linesize; linesize 200
卸载基于RPM安装的Oracle数据库
1、以安装所有者登录
su - oracle
2、删除关联库
cd $ORACLE_HOME/bin ./dbca
3、删除关联侦听器
cd $ORACLE_HOME/bin ./netca
4、切换到 root，然后删除数据库
dnf -y remove oracle-database-free-23c dnf -y remove oracle-database-preinstall-23c
