1、操作系统环境CentOS8.5
[oracle@orcl23c ~]$ cat /etc/redhat-release
CentOS Linux release 8.5.2111
主机名及IP地址
192.168.80.230 orcl23c
安装步骤此处省略
1.1、配置/etc/selinux/config
sed -i “s/SELINUX=enforcing/SELINUX=disabled/g” /etc/selinux/config
cat /etc/selinux/config
1.2、关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
2、下载官方安装包
https://www.oracle.com/database/free/get-started/
RedHat compatible Oracle Linux 8 distribution | |
---|---|
Filename | oracle-database-preinstall-23ai-1.0-2.el8.x86_64.rpm |
Filename | oracle-database-free-23ai-1.0-1.el8.x86_64.rpm |
Notes | **Run dnf install -y oracle-database-preinstall* ** |
Notes | Run dnf install -y oracle-database-free* |
Notes | Run /etc/init.d/oracle-free-23ai configure |
上传下载的安装包到/opt目录
3、创建和配置Oracle数据库服务实例
root用户下执行以下操作
[root@orcl23c ~]# cd /opt
[root@orcl23c opt]# dnf install -y oracle-database-preinstall*
Last metadata expiration check: 1:36:14 ago on Mon 20 May 2024 09:10:18 AM CST.
Dependencies resolved.
==========================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================
Installing:
oracle-database-preinstall-23ai x86_64 1.0-2.el8 @commandline 30 k
Installing dependencies:
compat-openssl10 x86_64 1:1.0.2o-3.el8 appstream 1.1 M
gssproxy x86_64 0.8.0-19.el8 baseos 119 k
ksh x86_64 20120801-254.el8 appstream 926 k
libXv x86_64 1.0.11-7.el8 appstream 20 k
libXxf86dga x86_64 1.1.5-1.el8 appstream 26 k
libdmx x86_64 1.1.4-3.el8 appstream 22 k
libverto-libevent x86_64 0.3.0-5.el8 baseos 16 k
lm_sensors-libs x86_64 3.4.0-23.20180522git70f7e08.el8 baseos 59 k
nfs-utils x86_64 1:2.3.3-46.el8 baseos 500 k
rpcbind x86_64 1.2.5-8.el8 baseos 70 k
sysstat x86_64 11.7.3-6.el8 appstream 425 k
xorg-x11-utils x86_64 7.5-28.el8 appstream 136 k
xorg-x11-xauth x86_64 1:1.0.9-12.el8 appstream 39 k
Transaction Summary
==========================================================================================================================================================================================
Install 14 Packages
Total size: 3.5 M
Total download size: 3.4 M
Installed size: 10 M
Downloading Packages:
(1/13): libXv-1.0.11-7.el8.x86_64.rpm 7.6 kB/s | 20 kB 00:02
(2/13): libXxf86dga-1.1.5-1.el8.x86_64.rpm 39 kB/s | 26 kB 00:00
(3/13): libdmx-1.1.4-3.el8.x86_64.rpm 24 kB/s | 22 kB 00:00
(4/13): ksh-20120801-254.el8.x86_64.rpm 162 kB/s | 926 kB 00:05
(5/13): xorg-x11-utils-7.5-28.el8.x86_64.rpm 168 kB/s | 136 kB 00:00
(6/13): xorg-x11-xauth-1.0.9-12.el8.x86_64.rpm 72 kB/s | 39 kB 00:00
(7/13): gssproxy-0.8.0-19.el8.x86_64.rpm 166 kB/s | 119 kB 00:00
(8/13): libverto-libevent-0.3.0-5.el8.x86_64.rpm 52 kB/s | 16 kB 00:00
(9/13): lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64.rpm 87 kB/s | 59 kB 00:00
(10/13): compat-openssl10-1.0.2o-3.el8.x86_64.rpm 128 kB/s | 1.1 MB 00:09
(11/13): rpcbind-1.2.5-8.el8.x86_64.rpm 126 kB/s | 70 kB 00:00
(12/13): sysstat-11.7.3-6.el8.x86_64.rpm 71 kB/s | 425 kB 00:05
(13/13): nfs-utils-2.3.3-46.el8.x86_64.rpm 254 kB/s | 500 kB 00:01
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 327 kB/s | 3.4 MB 00:10
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: rpcbind-1.2.5-8.el8.x86_64 1/14
Installing : rpcbind-1.2.5-8.el8.x86_64 1/14
Running scriptlet: rpcbind-1.2.5-8.el8.x86_64 1/14
Installing : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 2/14
Running scriptlet: lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 2/14
Installing : sysstat-11.7.3-6.el8.x86_64 3/14
Running scriptlet: sysstat-11.7.3-6.el8.x86_64 3/14
Installing : libverto-libevent-0.3.0-5.el8.x86_64 4/14
Installing : gssproxy-0.8.0-19.el8.x86_64 5/14
Running scriptlet: gssproxy-0.8.0-19.el8.x86_64 5/14
Running scriptlet: nfs-utils-1:2.3.3-46.el8.x86_64 6/14
Installing : nfs-utils-1:2.3.3-46.el8.x86_64 6/14
Running scriptlet: nfs-utils-1:2.3.3-46.el8.x86_64 6/14
Installing : xorg-x11-xauth-1:1.0.9-12.el8.x86_64 7/14
Installing : libdmx-1.1.4-3.el8.x86_64 8/14
Installing : libXxf86dga-1.1.5-1.el8.x86_64 9/14
Installing : libXv-1.0.11-7.el8.x86_64 10/14
Installing : xorg-x11-utils-7.5-28.el8.x86_64 11/14
Installing : ksh-20120801-254.el8.x86_64 12/14
Running scriptlet: ksh-20120801-254.el8.x86_64 12/14
Installing : compat-openssl10-1:1.0.2o-3.el8.x86_64 13/14
Running scriptlet: compat-openssl10-1:1.0.2o-3.el8.x86_64 13/14
Installing : oracle-database-preinstall-23ai-1.0-2.el8.x86_64 14/14
Running scriptlet: oracle-database-preinstall-23ai-1.0-2.el8.x86_64 14/14
[/usr/lib/tmpfiles.d/pesign.conf:1] Line references path below legacy directory /var/run/, updating /var/run/pesign → /run/pesign; please update the tmpfiles.d/ drop-in file accordingly.
Verifying : compat-openssl10-1:1.0.2o-3.el8.x86_64 1/14
Verifying : ksh-20120801-254.el8.x86_64 2/14
Verifying : libXv-1.0.11-7.el8.x86_64 3/14
Verifying : libXxf86dga-1.1.5-1.el8.x86_64 4/14
Verifying : libdmx-1.1.4-3.el8.x86_64 5/14
Verifying : sysstat-11.7.3-6.el8.x86_64 6/14
Verifying : xorg-x11-utils-7.5-28.el8.x86_64 7/14
Verifying : xorg-x11-xauth-1:1.0.9-12.el8.x86_64 8/14
Verifying : gssproxy-0.8.0-19.el8.x86_64 9/14
Verifying : libverto-libevent-0.3.0-5.el8.x86_64 10/14
Verifying : lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64 11/14
Verifying : nfs-utils-1:2.3.3-46.el8.x86_64 12/14
Verifying : rpcbind-1.2.5-8.el8.x86_64 13/14
Verifying : oracle-database-preinstall-23ai-1.0-2.el8.x86_64 14/14
Installed products updated.
Installed:
compat-openssl10-1:1.0.2o-3.el8.x86_64 gssproxy-0.8.0-19.el8.x86_64 ksh-20120801-254.el8.x86_64 libXv-1.0.11-7.el8.x86_64
libXxf86dga-1.1.5-1.el8.x86_64 libdmx-1.1.4-3.el8.x86_64 libverto-libevent-0.3.0-5.el8.x86_64 lm_sensors-libs-3.4.0-23.20180522git70f7e08.el8.x86_64
nfs-utils-1:2.3.3-46.el8.x86_64 oracle-database-preinstall-23ai-1.0-2.el8.x86_64 rpcbind-1.2.5-8.el8.x86_64 sysstat-11.7.3-6.el8.x86_64
xorg-x11-utils-7.5-28.el8.x86_64 xorg-x11-xauth-1:1.0.9-12.el8.x86_64
Complete!
[root@orcl23c opt]# dnf install -y oracle-database-free*
Last metadata expiration check: 1:36:38 ago on Mon 20 May 2024 09:10:18 AM CST.
Dependencies resolved.
==========================================================================================================================================================================================
Package Architecture Version Repository Size
==========================================================================================================================================================================================
Installing:
oracle-database-free-23ai x86_64 1.0-1 @commandline 1.3 G
Transaction Summary
==========================================================================================================================================================================================
Install 1 Package
Total size: 1.3 G
Installed size: 3.6 G
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: oracle-database-free-23ai-1.0-1.x86_64 1/1
Installing : oracle-database-free-23ai-1.0-1.x86_64 1/1
Running scriptlet: oracle-database-free-23ai-1.0-1.x86_64 1/1
[INFO] Executing post installation scripts...
[INFO] Oracle home installed successfully and ready to be configured.
To configure Oracle Database Free, optionally modify the parameters in '/etc/sysconfig/oracle-free-23ai.conf' and then run '/etc/init.d/oracle-free-23ai configure' as root.
Verifying : oracle-database-free-23ai-1.0-1.x86_64 1/1
Installed products updated.
Installed:
oracle-database-free-23ai-1.0-1.x86_64
Complete!
[root@orcl23c opt]# /etc/init.d/oracle-free-23ai 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:
[root@orcl23c opt]#
[root@orcl23c opt]# /etc/init.d/oracle-free-23ai 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:
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: orcl23c/FREEPDB1
Multitenant container database: orcl23c
创建一个名为“FREE”的演示数据库,以及一个名为“FREEPDB1”的可插拔数据库 (PDB)
4、配置sqlplus连接信息提示
vi /opt/oracle/product/23ai/dbhomeFree/sqlplus/admin/glogin.sql
增加如下配置
define _editor=‘vi’
set sqlprompt "_user’@'_connect_identifier> "
5、安装rlwrap
dnf -y install readline readline-devel
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
6、设置oracle用户的环境配置
su - oracle
vi ~/.bash_profile
export LANG=en_US
export ORACLE_SID=FREE
export ORAENV_ASK=NO
export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree
export PATH=$ORACLE_HOME/bin:$PATH
NLS_LANG=AMERICAN_AMERICA.UTF8;export NLS_LANG
alias sqlplus="rlwrap sqlplus"
alias rman="rlwrap rman"
source ~/.bash_profile
7、检查1521端口及smon
[root@orcl23c opt]# netstat -tuln | grep 1521
tcp6 0 0 :::1521 :::* LISTEN
[root@orcl23c opt]# su - oracle
[oracle@orcl23c ~]$ ps -ef | grep smon
oracle 11085 1 0 10:55 ? 00:00:00 db_smon_FREE
oracle 11486 11453 0 10:57 pts/0 00:00:00 grep --color=auto smon
8、检查监听及配置tnsnames.ora
8.1、检查监听
lsnrctl status
LSNRCTL for Linux: Version 23.0.0.0.0 - Production on 20-MAY-2024 11:07:48
Copyright (c) 1991, 2024, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl23c)(PORT=1521)))
STATUS of the LISTENER
----------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 23.0.0.0.0 - Production
Start Date 20-MAY-2024 10:51:25
Uptime 0 days 0 hr. 16 min. 22 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service FREE
Listener Parameter File /opt/oracle/product/23ai/dbhomeFree/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/orcl23c/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl23c)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "18dad49ef3f62c15e063e650a8c0d2d5" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
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 "freepdb1" has 1 instance(s).
Instance "FREE", status READY, has 1 handler(s) for this service...
The command completed successfully
8.2、配置freepdb1 tnsnames.ora
vi /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl23c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl23c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl23c)(PORT = 1521))
[oracle@orcl23c ~]$ tnsping free
TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 20-MAY-2024 11:03:23
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/23ai/dbhomeFree/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl23c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE)))
OK (0 msec)
[oracle@orcl23c ~]$ tnsping freepdb1
TNS Ping Utility for Linux: Version 23.0.0.0.0 - Production on 20-MAY-2024 11:04:08
Copyright (c) 1997, 2024, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/23ai/dbhomeFree/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl23c)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1)))
OK (0 msec)
9、sqlplus登陆检查
[oracle@orcl23c ~]$ sqlplus sys/oracle@FREEPDB1 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Mon May 20 11:04:14 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SYS@FREEPDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 FREEPDB1 READ WRITE NO
SYS@FREEPDB1> select sysdate;
SYSDATE
-------------------
2024-05-20 11:08:08
/*
Oracle 23ai在查询方面进行了简化,尤其是在处理不需要FROM子句的查询时。在以往,当使用DUAL表时,通常需要显式地指定FROM DUAL,但在Oracle 23ai中,这种要求得到了简化。现在,你可以在不写FROM语句的情况下使用SELECT,而Oracle的执行计划依然会采用FAST DUAL来处理这样的查询。
因此,Oracle 23ai并不再强制要求使用DUAL表。这一改变使得SQL查询的编写更加简洁和直观。不过,需要注意的是,尽管不再强制使用DUAL,但在某些情况下,特别是在需要引用一个虚拟表或单行表时,DUAL仍然是一个有用的工具。
*/
SYS@FREEPDB1> SELECT BANNER_FULL FROM v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
#10、java和python连接
-- java
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@192.168.80.230:1521/FREEPDB1"); // jdbc:oracle:thin@[hostname]:[port]/[DB service name]
ods.setUser("[Username]");
ods.setPassword("[Password]");
Connection conn = ods.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT 'Hello World!' FROM dual");
ResultSet rslt = stmt.executeQuery();
while (rslt.next()) {
System.out.println(rslt.getString(1));
}
-- pyhton
import oracledb
conn = oracledb.connect(user="[Username]", password="[Password]", dsn="192.168.80.230:1521/FREEPDB1")
with conn.cursor() as cur:
cur.execute("SELECT 'Hello World!' FROM dual")
res = cur.fetchall()
print(res)
在这里插入代码片