Oracle 体系结构概述

最新推荐文章于 2024-05-13 09:37:53 发布

DB架构

最新推荐文章于 2024-05-13 09:37:53 发布

阅读量1k

点赞数

分类专栏： DBA 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/127112049

版权

DBA 专栏收录该内容

183 篇文章 12 订阅

订阅专栏

1.定义数据库和实例

实例（instance）和数据库（database）

数据库（database）：物理操作系统文件或磁盘（disk）的集合。
实例（instance）:一组Oracle后台进程/线程以及一个共享内存区，这些内容由同一个计算机上运行的线程/进程所共享。
实例和数据库之间的关系：数据库可以由多个实例装载和打开，而实例可以在任何时间点装载和打开一个数据库。准确的讲，实例在其整个生存期中最多能装载和打开一个数据库。

实例就是一组操作系统进程（或者是一个多线程的进程）以及一些内存。

数据库只是一个文件集合（包括数据文件、临时文件、重做日志文件和控制文件）。

ps(进程状态)命令，可以看到用户运行的所有进程。

ipcs 命令，可用于显示进程间的通信设备，如共享内存，信号量等。目前系统中没有使用任何通信设备。

[oracle@MaxwellDBA dbs]$ ll
total 20
-rw-rw---- 1 oracle oinstall 1544 Sep 27 11:12 hc_ORCLCDB.dat
-rw-r--r-- 1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r----- 1 oracle oinstall   24 Jun 29 05:28 lkORCLCDB
-rw-r----- 1 oracle oinstall 2048 Jun 29 09:49 orapwORCLCDB
-rw-r----- 1 oracle oinstall 3584 Sep 29 16:00 spfileORCLCDB.ora
[oracle@MaxwellDBA dbs]$ 
[oracle@MaxwellDBA dbs]$ 
[oracle@MaxwellDBA dbs]$ ps -aef | grep ora19c
oracle     91690   91536  0 19:05 pts/0    00:00:00 grep --color=auto ora19c
[oracle@MaxwellDBA dbs]$ ipcs -a

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages    
0x00000000 0          root       666        0            0           
0x00000000 2          db2inst1   601        0            0           
0x00000000 4          db2inst1   601        0            0           
0x00000000 5          db2fenc1   700        0            0           
0x9d852d71 6          db2inst1   663        0            0           
0x00000000 7          db2inst1   601        0            0           
0x9d852d72 8          db2inst1   663        0            0           
0x9d852d77 9          db2inst1   665        0            0           

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x0052e2c1 0          postgres   600        56         6                       
0x9d852d74 1          db2inst1   601        34156360   7                       
0x9d852d61 2          db2inst1   601        175570944  6                       
0x00000000 3          db2fenc1   601        268435456  6                       
0x43b375cc 9          oracle     600        12288      57                      

------ Semaphore Arrays --------
key        semid      owner      perms      nsems     
0x9d852d74 0          db2inst1   645        1         
0x00000000 2          db2inst1   661        1         
0x00000000 3          db2inst1   661        1         
0x00000000 4          db2inst1   661        1         
0x00000000 5          db2inst1   661        1         
0x00000000 6          db2inst1   661        1         
0x00000000 7          db2inst1   661        1         
0x00000000 8          db2inst1   661        1         
0x00000000 9          db2inst1   661        1         
0x9d852d73 10         db2inst1   667        1         
0x00000000 11         db2inst1   661        1         
0x00000000 12         db2inst1   661        1         
0x00000000 13         db2inst1   661        1         
0x00000000 14         db2inst1   661        1         
0x00000000 15         db2inst1   661        1         
0x00000000 17         db2fenc1   667        3         
0x9d852e69 18         db2inst1   661        1         
0x00000000 20         db2inst1   661        1         
0x8d2a3534 28         oracle     600        250       
0x8d2a3535 29         oracle     600        250       

[oracle@MaxwellDBA dbs]$

sys@cdb$root:orclcdb> !ipcs -a

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages    
0x00000000 0          root       666        0            0           
0x00000000 2          db2inst1   601        0            0           
0x00000000 4          db2inst1   601        0            0           
0x00000000 5          db2fenc1   700        0            0           
0x9d852d71 6          db2inst1   663        0            0           
0x00000000 7          db2inst1   601        0            0           
0x9d852d72 8          db2inst1   663        0            0           
0x9d852d77 9          db2inst1   665        0            0           

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x0052e2c1 0          postgres   600        56         6                       
0x9d852d74 1          db2inst1   601        34156360   7                       
0x9d852d61 2          db2inst1   601        175570944  6                       
0x00000000 3          db2fenc1   601        268435456  6                       
0x43b375cc 9          oracle     600        12288      58                      

------ Semaphore Arrays --------
key        semid      owner      perms      nsems     
0x9d852d74 0          db2inst1   645        1         
0x00000000 2          db2inst1   661        1         
0x00000000 3          db2inst1   661        1         
0x00000000 4          db2inst1   661        1         
0x00000000 5          db2inst1   661        1         
0x00000000 6          db2inst1   661        1         
0x00000000 7          db2inst1   661        1         
0x00000000 8          db2inst1   661        1         
0x00000000 9          db2inst1   661        1         
0x9d852d73 10         db2inst1   667        1         
0x00000000 11         db2inst1   661        1         
0x00000000 12         db2inst1   661        1         
0x00000000 13         db2inst1   661        1         
0x00000000 14         db2inst1   661        1         
0x00000000 15         db2inst1   661        1         
0x00000000 17         db2fenc1   667        3         
0x9d852e69 18         db2inst1   661        1         
0x00000000 20         db2inst1   661        1         
0x8d2a3534 28         oracle     600        250       
0x8d2a3535 29         oracle     600        250       


sys@cdb$root:orclcdb> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

4 rows selected.

sys@cdb$root:orclcdb> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/redo03.log
/opt/oracle/oradata/ORCLCDB/redo02.log
/opt/oracle/oradata/ORCLCDB/redo01.log

3 rows selected.

sys@cdb$root:orclcdb> select name from v$controlfile;

NAME
----------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/control01.ctl
/opt/oracle/oradata/ORCLCDB/control02.ctl

2 rows selected.

sys@cdb$root:orclcdb>

重申一遍：

实例是一组后台进程和共享内存组成。
数据库是磁盘上存储的数据集合。
实例“一生”只能装载并打开一个数据库。
数据库可以由一个或多个实例（使用RAC）装载和打开。装载一个数据库的实例数量会随时间变化。

2. SGA和后台进程

维护所有进程需要访问的多种内部数据结构。
缓存磁盘上的数据，另外重做数据写至磁盘之前先在这里缓存。
保存已解析的SQL计划。

Unix环境，可以用pstat查看各线程的更多详细信息。

[oracle@MaxwellDBA dbs]$ 
[oracle@MaxwellDBA dbs]$ 
[oracle@MaxwellDBA dbs]$ ps -aef | grep oracle$ORACLE_SID
oracle     88586       1  0 17:11 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     88616       1  0 17:12 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     88841       1  0 17:20 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     89028   89027  0 17:25 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92059   92058  0 19:17 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92409   91536  0 19:30 pts/0    00:00:00 grep --color=auto oracleORCLCDB
[oracle@MaxwellDBA dbs]$ 


sys@cdb$root:orclcdb> 
sys@cdb$root:orclcdb> !ps -aef | grep oracle$ORACLE_SID
oracle     88586       1  0 17:11 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     88616       1  0 17:12 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     88841       1  0 17:20 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     89028   89027  0 17:25 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92059   92058  0 19:17 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92428   92426  0 19:31 pts/6    00:00:00 grep oracleORCLCDB

sys@cdb$root:orclcdb>

3.连接Oracle

Oracle服务器处理请求的两种最常见的方式：

专用服务器（dedicated server）连接
共享服务器（shared server）连接

服务器上的监听器（listener）进程会以不同的方式工作，这些监听器进程负责建立与服务器的物理连接。

专用服务器

[oracle@MaxwellDBA dbs]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 29 19:37:08 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> !ps -aef | grep oracle$ORACLE_SID
oracle     88616       1  0 17:12 ?        00:00:00 oracleORCLCDB (LOCAL=NO)
oracle     89028   89027  0 17:25 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92059   92058  0 19:17 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92587   92586  0 19:37 ?        00:00:00 oracleORCLCDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle     92599   92597  0 19:37 pts/7    00:00:00 grep oracleORCLCDB

sys@cdb$root:orclcdb>

共享服务器（shared server）

TCP/IP连接的基本原理

[oracle@MaxwellDBA dbs]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 29 19:40:57 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

sys@cdb$root:orclcdb> ALTER SESSION SET CONTAINER=ORCLPDB1;

Session altered.

sys@cdb$root:orclcdb> conn SCOTT/TIGER@ORCLPDB1
Connected.
scott@orclpdb1:orclcdb>

[oracle@MaxwellDBA admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )

LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521))



ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )

LISTENER_ORCLPDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = MaxwellDBA)(PORT = 1521))


[oracle@MaxwellDBA admin]$