【2017/4/10】认识oracle db体系结构

RDBMS关系型数据库，也是对象型数据库。不过只在关系型数据库的时候占优势。

oracle server=实例+数据库
也就是=（内存结构+后台进程）+物理存储文件

一般情况1个oracle server--》1个实例
集群情况1个oracle server--》多个实例

查询有几个oracle server
ps -ef | grep ora_|cut -d '_' -f 3|sort|uniq

[root@wyzc ~]# ps -ef |grep ora_|cut -d '_' -f3 |sort |uniq
wyzc10g
wyzc11g

12c中
一个CDB包含多个PDB
PDB具备了oracle server的完整功能，共享使用了CDB的undo，control,日志文件，共享了实例名
CDB是一个完整的oracle server

实例=内存结构+后台进程

后台进程是用来访问内存数据与磁盘数据交互用的

当数据库启动时分配实例

数据库=物理文件+构造物理文件的逻辑结构

[root@wyzc ~]# su - oracle
[oracle@wyzc ~]$ 10g
[oracle@wyzc ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 3 15:27:26 2017

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

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
wyzc10g

SQL> select name from v$database;

NAME
---------
WYZC10G

SQL>

一个实例访问了一个数据库,这才算一个完整的oracle server，open(物理+逻辑)状态，才能发挥RDBMS的功能。

此时是mount状态，只能访问物理结构，不能逻辑结构
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 398458880 bytes
Fixed Size 2096568 bytes
Variable Size 134218312 bytes
Database Buffers 255852544 bytes
Redo Buffers 6291456 bytes
Database mounted.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
wyzc10g

SQL> select name from v$database;

NAME
---------
WYZC10G

SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL>
SQL> conn scott/tiger
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Warning: You are no longer connected to ORACLE.
SQL>
SQL> conn / as sysdba
Connected.
SQL>
SQL> alter database open;

Database altered.
SQL> alter user scott account unlock;

User altered.
SQL> conn scott/tiger
Connected.
SQL>
conn / as sysdba
alter database open;
conn scott/tiger
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE

SQL>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如何连接到oracle数据库

若使用客户端调用数据库，会涉及到中间层（asp，java，等）翻译工具。
sqlplus也有相应的翻译工具，或者客户端工具。

翻译工具：
10g、11g、12c
[oracle@wyzc ~]$ ls /u01/oracle/10g/jdbc/lib/
classes12dms.jar classes12.zip ojdbc14dms_g.jar ojdbc14_g.jar
classes12.jar nls_charset12.jar ojdbc14dms.jar ojdbc14.jar
[oracle@wyzc ~]$
/u01/oracle/10g/jdbc/lib/class12.jar
/u01/oracle/10g/jdbc/lib/ojdbc14.jar
这些oracle驱动去调用相关软件

在linux下使用isql访问oracle需要unixodbc驱动
[oracle@wyzc ~]$ ls /u01/oracle/10g/odbc/lib/
env_odbc.mk ins_odbc.mk
[oracle@wyzc ~]$
客户端工具：
sqlplus
sqldevelop
toad
pl/sql develop
ORACLE NET-->监听、本地命名
[oracle@wyzc ~]$ 10g
[oracle@wyzc ~]$ lsnrctl status查看监听状态
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 03-MAR-2017 16:00:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wyzc.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date 03-MAR-2017 12:11:45
Uptime 0 days 3 hr. 48 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/10g/network/admin/listener.ora
Listener Log File /u01/oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wyzc.com)(PORT=1522)))
(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 "wyzc10g" has 2 instance(s).
Instance "wyzc10g", status UNKNOWN, has 1 handler(s) for this service...
Instance "wyzc10g", status READY, has 1 handler(s) for this service...
Service "wyzc10gXDB" has 1 instance(s).
Instance "wyzc10g", status READY, has 1 handler(s) for this service...
Service "wyzc10g_XPT" has 1 instance(s).
Instance "wyzc10g", status READY, has 1 handler(s) for this service...
The command completed successfully

客户端登录scott tiger IP:1522/wyzc10gXDB(服务名)
服务名默认是实例名


11g 装了grid，在grid里查看监听
没装的 ，在database查看监听
[oracle@wyzc ~]$ grid11g
[oracle@wyzc ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-MAR-2017 16:04:06

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wyzc.com)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 03-MAR-2017 13:13:49
Uptime 0 days 2 hr. 50 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/grid/11g/network/admin/listener.ora
Listener Log File /u01/oracle/diag/tnslsnr/wyzc/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wyzc.com)(PORT=1523)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523)))
Services Summary...
Service "wyzc11g" has 1 instance(s).
Instance "wyzc11g", status READY, has 1 handler(s) for this service...
Service "wyzc11gXDB" has 1 instance(s).
Instance "wyzc11g", status READY, has 1 handler(s) for this service...
The command completed successfully


sqlplus scott/[e-mail]:1523/wyzc11gXDB(共享连接)

~~~~~~~~~~~~~~~~~~~~~~
用户进程，在客户端产生的进程
首先sqlplus工具与server进行tcp/ip通信，建立会话并保存在PGA。
IPC通信互联网进程调用，本地连接os认证。
怎么找出用户进程--》sqldevelop打开

10g
sqlplus / as sysdba
desc v$session
SID
PADDR
USERNAME
TADDR---事务
SCHEMA
OSUER------客户端上的用户
PROCESS----进程号
MACHINE----客户端主机名
program----软件


select osuser,process,machine,program from v$session where username='scott';
col osuser for a15
col process for a15
col machine for a15
col program for a15
[oracle@wyzc ~]$ sqlplus scott/tiger

[oracle@wyzc ~]$ sqlplus / as sysdba
SQL> col osuser for a15
SQL> col process for a15
SQL> col machine for a15
SQL> col program for a15
SQL>
SQL> select osuser,process,machine,program from v$session where username='SCOTT';

OSUSER PROCESS MACHINE PROGRAM
--------------- --------------- --------------- ---------------
oracle 63861 wyzc.com [e-mail]
m (TNS V1-V3)


SQL>

10g
sqlplus scott/tiger
select server,process,machine,program from v$session where username='scott';
DEDICATED-->专有连接--1个服务器进程对应1个用户进程
SQL> select server,process,machine,program from v$session where username='SCOTT';

SERVER PROCESS MACHINE PROGRAM
--------- --------------- --------------- ---------------
DEDICATED 63861 wyzc.com [e-mail]
m (TNS V1-V3)


SQL>

select server,process,machine,program,paddr from v$session where username='scott';
select spid from v$process where addr='paddr的值'；
ho ps -ef|grep 4407
local=yes-->本地连接
SQL> select server,process,machine,paddr from v$session where username='SCOTT';

SERVER PROCESS MACHINE PADDR
--------- --------------- --------------- ----------------
DEDICATED 63861 wyzc.com 0000000077AFA5A8

SQL>
SQL> select spid from v$process where addr='0000000077AFA5A8';

SPID
------------
63862

SQL>
SQL> ho ps -ef|grep 63862
oracle 63862 63861 0 16:57 ? 00:00:00 oraclewyzc10g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

若sqlplus scott/[e-mail]:1522/wyzc10g
此时有两个paddr的值，找出local=no


[oracle@wyzc ~]$ sqlplus scott/[e-mail]:1522/wyzc10gXDB

SQL> select server,process,machine,paddr from v$session where username='SCOTT';

SERVER PROCESS MACHINE PADDR
--------- --------------- --------------- ----------------
NONE 70708 wyzc.com 0000000077AF3E10
sqlplus scott/[e-mail]:1522/wyzc10gXDB
NONE-->共享连接--1个服务器进程对应多个用户进程

vi /u01/oracle/10g/network/admin/tnsnames.ora
WYZC10GS
SERVER=shared
SERVICE_NAME=wyzc10gXDB
sqlplus scott/[e-mail]:1523/wyzc10gs


~~~~~~~~~~~~~~
服务器进程
接受用户进程并作处理。每个用户进程对应一个pga
建立连接，建立的会话信息存放在PGA。

paddr的值对应PGA中的ADDR
desc v$process

SQL> select spid from v$process where addr in (select paddr from v$session where username='SCOTT');

SPID
------------
72306

SQL>
SQL> ho ps -ef|grep 72306
oracle 72306 1 0 18:09 ? 00:00:00 oraclewyzc10g (LOCAL=NO)

select spid from v$process where addr in (select paddr from v$session where username='scott');
ho ps -ef |grep 2036进程信息查看

top -p 2306

11g进程的跟踪文件
11g
sqlplus / as sysdba
desc v$process
TRACEFILE

查看pga内存大小 32bit 1.7g 64bit
show parameter pga_a
修改alter system set pga_aggregate_target=5G;

12c
show parameter pga_aggregate_limite限制pga避免过多使用sga


~~~~~~~~~~~~~~~~~~~~~~~~~~
SGA&PGA

show parameter sga_target
select * from v$sgainfo;
granule size-->小于1g以4M为单位，大于1g以16M为单位

desc v$process
background
desc v$dbprocess

select paddr,name from v$bgprocess where paddr<>'00';当前正在使用的后台进程
ho ps -ef|grep ora_|grep wyzc10g

SQL> select paddr,name from v$bgprocess;
PADDR NAME
---------------- -----
00 ASMB
00 GMON
0000000077AF2E20 MMON
0000000077AF3618 MMNL

158 rows selected.

SQL> select paddr,name from v$bgprocess where paddr<>'00';

PADDR NAME
---------------- -----
0000000077AEE668 PMON
0000000077AEEE60 PSP0
0000000077AEF658 MMAN
0000000077AEFE50 DBW0
0000000077AF0648 LGWR
0000000077AF0E40 CKPT
0000000077AF1638 SMON
0000000077AF1E30 RECO
0000000077AF2628 CJQ0
0000000077AF55F8 QMNC
0000000077AF2E20 MMON

PADDR NAME
---------------- -----
0000000077AF3618 MMNL

12 rows selected.

SQL>


查看数据文件
select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

show parameter spfile;

1.查询过程

 用户进程里执行了一条查询语句—》与服务器进程通过tcp、IPC等通信建立了会话信息保存在PGA，此时做hash校验产生一个hashvalue—》在库高速缓存区中对比hashvalue，若存在则走软分析，不存在走硬分析。并决定执行计划，是索引还是全盘。--》数据字典里递归调用，各种对象信息，到底是什么表，什么类型，什么视图，什么函数等。字典未命中，则通过服务器进程将数据字典所在的数据文件内容读入进数据字典高速缓存。若命中，就将行的记录，存在几号文件几号block的相关信息传递给数据库缓冲区—》若在高速缓冲区中没有此block，则通过服务器进程，将此block所在的数据文件内容读入高速缓冲区，然后进行读取。若在高速缓冲区中有此block信息，则直接读取。在高速缓冲区中的block分为读block与写block。读block对应的是undo block，如果不存在的话，如第一次，就需要服务器进程将undo block所在的数据文件内容读入进数据库缓存区。写block对应真实的数据文件的block。

 

2.事务提交、DML/DCL/DDL过程

 一直到数据库缓存区之前过程相似。命令要求修改block之前，会将block复制一份进undo block。修改block时，若没有事务提交，那么新的block与undo block将一致；若有事务提交，则那么新的block与undo block不一致，undo block会标记为事务结束，此时新的block会更改一致性信息，再将即将更改的过程细节记录写进重做日志缓存区，写入的频率是，3s写一次，达到1m写一次，事务提交写一次，日志切换写一次。再通过后台进程LGWR，将日志缓存区内容写入数据库的联机重做日志文件。在数据不一致或者需要恢复数据的时候就会用的此文件。同时触发后台进程CKPT，通知控制文件写新的SCN，数据文件头写新的SCN，此时与联机重做日志文件三者的时间点必须一致。待日志缓存区内容写入数据库的联机重做日志文件后，通知后台进程DBWn，将新的block写入数据文件。若写入的过程中断了，由于控制文件与联机重做日志文件scn一致，所以会同步数据文件，在下次DBWn或者重启数据库后，会继续写入数据文件。

3.补充

 SGA的大小自动管理，只有重做日志缓冲区需要手工指定。

 SMON管理锁对资源的释放、索引的合并等

 PMON监听注册

 DBWn,CKPT,LGWR,SMON,PMON这5个非常重要，其他进程被kill，PMON会自动启起来

 关于联机日志文件，在主库上叫做online redo log，在备库上叫做standby redo log