30.Oracle体系结构

1.了解Oracle，测试环境

1.1开启监听

[oracle@yuanzj.com:/home/oracle]$ 10g 
[oracle@yuanzj.com:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 28-FEB-2023 16:55:18

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yuanzj.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                28-FEB-2023 16:47:08
Uptime                    0 days 0 hr. 8 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/ora10g/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/ora10g/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yuanzj.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl10g" has 1 instance(s).
  Instance "orcl10g", status READY, has 1 handler(s) for this service...
Service "orcl10gXDB" has 1 instance(s).
  Instance "orcl10g", status READY, has 1 handler(s) for this service...
Service "orcl10g_XPT" has 1 instance(s).
  Instance "orcl10g", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@yuanzj.com:/home/oracle]$ 11g 
[oracle@yuanzj.com:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-FEB-2023 16:55:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yuanzj.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                28-FEB-2023 16:48:02
Uptime                    0 days 0 hr. 7 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/ora11g/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/ora11g/diag/tnslsnr/yuanzj/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yuanzj.com)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "orcl11g" 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
[oracle@yuanzj.com:/home/oracle]$ 12c 
[oracle@yuanzj.com:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 28-FEB-2023 16:55:25

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yuanzj.com)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                28-FEB-2023 16:48:47
Uptime                    0 days 0 hr. 6 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/ora12c/product/12.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/ora12c/diag/tnslsnr/yuanzj/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yuanzj.com)(PORT=1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=yuanzj.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/ora12c/product/12.2.0/db_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "f48ee2c05e6d47cce0532802a8c0a02a" has 1 instance(s).
  Instance "orcl", status READY, 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...
Service "orclpdb01" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

1.2.Oracle10g，Oracle11g，Oracle12c版本数据库name

--Oracle10g
[oracle@yuanzj.com:/home/oracle]$ 10g 
[oracle@yuanzj.com:/home/oracle]$ sas

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 28 16:52:22 2023

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

sys@ORCL10G 2023-02-28 16:52:22> select name from v$database;

NAME
--------------------------------------------------------------------------------
ORCL10G

Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 16:52:24> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
--Oracle11g
[oracle@yuanzj.com:/home/oracle]$ 11g 
[oracle@yuanzj.com:/home/oracle]$ sas

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 28 16:53:26 2023

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sys@ORCL11G 2023-02-28 16:53:26> select name from v$database;

NAME
--------------------------------------------------------------------------------
ORCL11G

Elapsed: 00:00:00.00
sys@ORCL11G 2023-02-28 16:53:28> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
--Oracle12c
[oracle@yuanzj.com:/home/oracle]$ 12c 
[oracle@yuanzj.com:/home/oracle]$ sas

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 28 16:53:35 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys@ORCL 2023-02-28 16:53:35> select name from v$database;

NAME
--------------------------------------------------------------------------------
ORCL

Elapsed: 00:00:00.01
sys@ORCL 2023-02-28 16:53:36> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2.如何连接到Oracle数据库

注意：不同的应用程序连接数据库，需要不同的驱动：

#java：需要jdbc驱动
[oracle@yuanzj.com:/home/oracle]$ ls -lh $ORACLE_HOME/jdbc/lib
total 23M
-rw-r--r-- 1 oracle oinstall 6.5M Dec 10  2021 ojdbc8dms_g.jar
-rw-r--r-- 1 oracle oinstall 5.4M Dec 10  2021 ojdbc8dms.jar
-rw-r--r-- 1 oracle oinstall 6.5M Dec 10  2021 ojdbc8_g.jar
-rw-r--r-- 1 oracle oinstall 3.9M Dec 10  2021 ojdbc8.jar
-rw-r--r-- 1 oracle oinstall  29K Dec 13  2016 simplefan.jar
#asp：需要odbc驱动
[oracle@yuanzj.com:/home/oracle]$ ls -lh $ORACLE_HOME/odbc/lib
total 1.6M
-rw-r--r-- 1 oracle oinstall  74K Jan 26  2017 env_odbc.mk
-rw-r--r-- 1 oracle oinstall  957 Jan 26  2017 ins_odbc.mk
-rw-r--r-- 1 oracle oinstall 1.1M Jan 26  2017 sqoci.a
-rw-r--r-- 1 oracle oinstall 331K Jan 26  2017 sqora.a
-rw-r--r-- 1 oracle oinstall  98K Jan 26  2017 utility.a
#连接Oracle10g数据库
[oracle@yuanzj.com:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 28 17:08:00 2023

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

sys@ORCL10G 2023-02-28 17:08:00> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Feb 28 17:08:09 2023

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

scott@ORCL10G 2023-02-28 17:08:09> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
#连接Oracle11g
[oracle@yuanzj.com:/home/oracle]$ 11g 
[oracle@yuanzj.com:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 28 17:08:51 2023

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

sys@ORCL11G 2023-02-28 17:08:51> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
[oracle@yuanzj.com:/home/oracle]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 28 17:08:54 2023

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, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

scott@ORCL11G 2023-02-28 17:08:54> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
#连接Oracle12c
[oracle@yuanzj.com:/home/oracle]$ 12c 
[oracle@yuanzj.com:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 28 17:09:37 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys@ORCL 2023-02-28 17:09:37> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@yuanzj.com:/home/oracle]$ sqlplus sys/Oracle_4U@orclpdb01 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 28 17:10:02 2023

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sys@ORCLPDB01 2023-02-28 17:10:02> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@yuanzj.com:/home/oracle]$ sqlplus scott/tiger@orclpdb01

SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 28 17:10:24 2023

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

Last Successful login time: Mon Feb 13 2023 13:19:06 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

scott@ORCLPDB01 2023-02-28 17:10:24> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

3.用户进程

连接：用户进程和实例之间的通信

会话：用户通过用户进程与实例建立的特定连接

sys@ORCL10G 2023-02-28 18:48:53> select server,OSUSER,PROCESS,MACHINE,paddr from v$session where username = 'SCOTT';

SERVER	  OSUSER			 PROCESS      MACHINE							       PADDR
--------- ------------------------------ ------------ ---------------------------------------------------------------- ----------------
DEDICATED oracle			 22785	      yuanzj.com						       00000000A5309F70

Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 18:51:45> select spid from v$process where addr = '00000000A5309F70';

SPID
------------
22786

Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 18:52:16> ho ps -ef|grep 22786
oracle    22786  22785  0 18:45 ?        00:00:00 oracleorcl10g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    23922  22820  0 18:52 pts/3    00:00:00 /bin/bash -c ps -ef|grep 22786
oracle    23924  23922  0 18:52 pts/3    00:00:00 grep 22786

DEDICATED：专有连接，LOCAL=YES是本地连接，用户连接到数据库之后，就不需要监听程序了。

4.服务器进程

sys@ORCL10G 2023-02-28 19:06:38> select spid, username from v$process where addr in (select paddr from v$session where username = 'SCOTT');

SPID	     USERNAME
------------ ---------------
22786	     oracle

Elapsed: 00:00:00.00
sys@ORCL10G 2023-02-28 19:08:46> ho top -p 22786
top - 19:09:12 up  2:22,  2 users,  load average: 0.02, 0.02, 0.00
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni, 99.7%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   5975824k total,  3776692k used,  2199132k free,    58444k buffers
Swap:  8388604k total,        0k used,  8388604k free,  2903808k cached

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                       
 22786 oracle    20   0 1269m  21m  18m S  0.0  0.4   0:00.00 oracle
 
--Oracle10g 
sys@ORCL10G 2023-02-28 19:13:19> show parameter pga;

NAME_COL_PLUS_SHOW_PARAM							 TYPE                       VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pga_aggregate_target								 big integer                 371M
--Oracle11g
sys@ORCL11G 2023-02-28 19:13:59> show parameter pga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target		     big integer 0
--Oracle12c
sys@ORCL 2023-02-28 19:14:49> show parameter pga;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit		     big integer 2G
pga_aggregate_target		     big integer 0

--pga_aggregate_limit：Oracle12c新增参数，限制pga最大值

--修改pga参数
alter system set pga_aggregate_target = 5G scope=spfile

5.Oracle系统全局区和用户全局区

--1.pga接收数据，计算hash value值，传递给sga中的库高速缓冲区
--2.sga中库高速缓冲区查找hash value，如果hash value存在，做软解析，如果不存在做硬解析，询问数据字典告诉缓冲区是全表扫描还是索引，对象的类型和位置信息；
--3.在数据字典高速换缓冲区查找数据，走字典命中，如果没有命中，通过服务器进程将数据文件中的数据库读入到数据字典缓冲区中缓存，需要一次磁盘IO；
--4.假设执行DQL语句，数据字典报告文件号和block块号，查看数据库缓冲区，如果有，返回数据，如果没有通过服务器进程读取数据文件到数据缓冲区，返回行数据，
--  数据缓冲区有两种block，第一种是undo block，第二种是write block。

sys@ORCL 2023-02-28 19:14:52> select * from v$sgainfo;

NAME										      BYTES RES     CON_ID
-------------------------------------------------------------------------------- ---------- --- ----------
Fixed SGA Size									    8793304 No		 0
Redo Buffers									    7983104 No		 0
Buffer Cache Size								  654311424 Yes 	 0
In-Memory Area Size									  0 No		 0
Shared Pool Size								  234881024 Yes 	 0
Large Pool Size 								   33554432 Yes 	 0
Java Pool Size									   16777216 Yes 	 0
Streams Pool Size									  0 Yes 	 0
Shared IO Pool Size								   50331648 Yes 	 0
Data Transfer Cache Size								  0 Yes 	 0
Granule Size									   16777216 No		 0
Maximum SGA Size								 1610612736 No		 0
Startup overhead in Shared Pool 						  175924648 No		 0
Free SGA Memory Available							  654311424		 0

6.后台进程

--1.CKTP进程：维护数据库一致性进程，即维护数据文件，控制文件，日志文件时间点一致。用户进程发出事务提交，DCL，DDL，都会出发CKPT进程；
--2.CKPT触发之后，会通知更新scn号，通知DBWn进程把数据高速缓冲区被修改的数据，写入到磁盘。
--3.DBWn进程：执行之前，LGWR把内存中需要修改数据库高速缓冲区中block全部记录到重做日志缓冲区，然后DBWn进程才执行，把数据写入到磁盘文件中；
--4.SMON进程：分配资源协调规划，使用和释放，索引使用，锁释放；
--5.PMON进程：进程监视进程，监听注册；
--6.RECO进程：恢复进程。

--查看后台进程
sys@ORCL 2023-02-28 20:32:44> select paddr,name from v$bgprocess where paddr <> '00';

PADDR		 NAME
---------------- --------------------------------------------------------------------------------
000000006DC07E68 PMON
000000006DC08EF8 CLMN
000000006DC09F88 PSP0
000000006DC0B018 VKTM
000000006DC0C0A8 GEN0
000000006DC0D138 MMAN
000000006DC0E1C8 PXMN
000000006DC0F258 GEN1
000000006DC102E8 SCMN
000000006DC11378 DIAG
000000006DC12408 OFSD
000000006DC13498 SCMN
000000006DC14528 DBRM
000000006DC155B8 VKRM
000000006DC16648 SVCB
000000006DC176D8 PMAN
000000006DC18768 DIA0
000000006DC197F8 DBW0
000000006DC1A888 LGWR
000000006DC1B918 CKPT
000000006DC1C9A8 SMON
000000006DC1DA38 SMCO
000000006DC1EAC8 RECO
000000006DC1FB58 W005
000000006DC20BE8 LREG
000000006DC22D08 FENC
000000006DC22D08 MMNL
000000006DC23D98 MMON
000000006DC26F48 TMON
000000006DC27FD8 W000
000000006DC29068 TT00
000000006DC2A0F8 TT01
000000006DC2B188 TT02
000000006DC2C218 AQPC
000000006DC2D2A8 CJQ0
000000006DC32578 W001
000000006DC33608 W003
000000006DC34698 QM02
000000006DC3A9F8 Q003
000000006DC3BA88 Q004

--数据文件信息
sys@ORCL 2023-02-28 20:32:56> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/system01.dbf
/oradata/orcl/sysaux01.dbf
/oradata/orcl/undotbs01.dbf
/oradata/orcl/pdbseed/system01.dbf
/oradata/orcl/pdbseed/sysaux01.dbf
/oradata/orcl/users01.dbf
/oradata/orcl/pdbseed/undotbs01.dbf
/oradata/orcl/orclpdb01/system01.dbf
/oradata/orcl/orclpdb01/sysaux01.dbf
/oradata/orcl/orclpdb01/undotbs01.dbf
/oradata/orcl/orclpdb01/users01.dbf

11 rows selected.

Elapsed: 00:00:00.01
sys@ORCL 2023-02-28 20:34:30> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/orcl/redo03.log
/oradata/orcl/redo02.log
/oradata/orcl/redo01.log

Elapsed: 00:00:00.01
sys@ORCL 2023-02-28 20:34:44> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/orcl/control01.ctl
/u01/app/ora12c/fast_recovery_area/orcl/control02.ctl

Elapsed: 00:00:00.00

7.系统全局区的其他组件