Oracle架构和应用

最新推荐文章于 2024-03-12 08:00:00 发布

weixin_41164620

最新推荐文章于 2024-03-12 08:00:00 发布

阅读量571

点赞数

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

本文链接：https://blog.csdn.net/weixin_41164620/article/details/119025385

版权

Oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

3.2.4 关闭数据库，启动到mount状态：

3.3.1 归档日志的查询及日志空间增加：

3.4.1 创建一个删除归档日志的脚本（delete_arch.txt）：

3.4.2 创建批处理任务（delete_archive.bat）

3.4.3 创建一个windows任务定时调用批处理任务（delete_archive.bat）

4.Oracle用户和数据备份

4.1用户创建

4.2数据库备份

1.oracle架构

电脑的基本组成：本地磁盘（L5+L6），内存（L4-L1）高速缓存区，CPU。

在Oracle架构主要分为两大部分： Instance（实例）（高速缓存区加进程）和Database（Oracle 数据库）（本地磁盘）；

创建Instance和DataBase的操作连接如下：注先创建Instance再创建 DataBase

Oracle 11g中创建实例 - 尘心journey - 博客园

Instance 和DataBase的关系可以是一对一关系，在单实例的服务器中，就是一个DataBase对应一个Instance。

Instance 和DataBase的关系也可以是一对多关系。多个Instance对应一个Database。这种多对一关系被称为实际应用群集(Real Application Clusters，RAC)RAC极大提高了数据库的性能、容错与可伸缩性(可能耗费更多的存储空间)并且是oracle网格(grid)概念的必备部分。

具体结构图如下：

1.1 Oracle的SGA

Instance指数据库服务器的内存及相关处理程序，相当于Oracle的心脏。SGA与Instance的性能息息相关，在 oracle 版本10之后SGA的内存有系统进行自动分配，不需要人工调整。

同一个Instance下用户共享一个SGA，每个Instance拥有自己的SGA。

以下是对SGA组成部分分析：

1.data dictionary cache ：用于缓存比如表数据结构等数据，一般Instance启动后为固定值。

2. library cache：会将一次编译的执行的sql语句放入到 library cache中。再执行某个sql时，去库中找如果已经存在，则不需要再次编译，节省时间。

3. blockbuffer cache: Oracle将数据库块写至磁盘之前，另外从磁盘读取数据库块之后，就会把这些数据库块存储在block buffer cache中。这个其实是缓存数据，也是为什么你第二次进行同一个操作的时候比第一次要快的原因，因为他可以直接从这个缓存里面取就可以了！但是如果你第二天来继续进行同一操作应该是比较慢的，因为在那个缓存里面已经没有你要取的数据了！

4. redo buffer cache：在写入在线重做日志前，把这些数据先写到缓冲池中，这样提升了数据增删改的速度，减少磁盘的读写而加快速度。

5.Oracle中有几个服务，服务作用如下：

1.OracleServiceOrcl（对应整个数据库实例，Orcl是服务名字）

2.OracleJobSchedulerORCL（作业调度服务，默认禁用）。

3.OracleConsoleororcl（数据库控制服务，运行Enterpriser manager 时使用。）

4.OracleOraDb11g_home1TNSListener。监听器服务，服务只有在数据库需要远程访问的时候才需要，此服务被默认的设置为开机启动。lsnrctl status 可以查看状态。

6.database对应的状态：如果出现数据连接不上的情况，首先检查database是否正常。

1.1.1 相关表与表字段解析

1. library cache 相关表：字段解释

v$librarycache( library cache)：oracle 基础知识(八)----Library Cache * - kingle-l - 博客园

v$sgastat(对应整个sga状态)：

v$sysstat（对应这个系统状态，可以看到缓存输出数据，和磁盘输出数据等）;

v$buffercache （block buffer cache）：

v$sql（对应执行sql）：oracle系统表v$session、v$sql,v$sqlarea字段中文说明_cyz0815的专栏-CSDN博客_v$sqlarea字段含义

v$sqlarea（对执行的sql汇总后的表数据）:oracle系统表v$session、v$sql,v$sqlarea字段中文说明_cyz0815的专栏-CSDN博客_v$sqlarea字段含义

1.1.2 相关SQL语句

1. select t.* from v$version t ; 查询版本

2.select t.* from v$sga t ; 查询sga情况

3.select t.* from V$librarycache t ;获取到library cache 的情况， PINSHIT 表示命中的总字节。

4. SELECT sum(bytes)/1024/1024 as TSP_IN_M FROM V$SGASTAT T WHERE T.POOL = 'shared pool'; 计算共享池大小

5. 计算共享池的使用率：稳定在75%-90%间
SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'

FROM V$SGASTAT

WHERE NAME = 'free memory' AND POOL = 'shared pool';

6.计算 block buffer cache 命中率一般在90%以上。

select t.* from v$buffer_pool_statistics t ;
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,

1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS

WHERE NAME='DEFAULT';

7.计算系统各个区命中率

SQL> select value from v$sysstat where name ='physical reads';

SQL> select value from v$sysstat where name ='physical reads direct';

SQL> select value from v$sysstat where name ='physical reads direct (lob)';

SQL> select value from v$sysstat where name ='consistent gets';

SQL> select value from v$sysstat where name = 'db block gets';

这里命中率的计算应该是

令 x = physical reads direct + physical reads direct (lob)

命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100

通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区

9.查找磁盘读写最多的SQL:

SQL>SELECT * FROM

(SELECT sql_text,disk_reads "total disk" , executions "total exec",disk_reads/executions "disk/exec" FROM v$sql WHERE executions>0 and is_obsolete='N' ORDER BY 4 desc)

WHERE ROWNUM<11 ;

3.查找工作量最大的SQL(实际上也是按磁盘读写来排序的):

SQL>select substr(to_char(s.pct, '99.00'), 2) || '%' load,s.executions executes,p.sql_text

from(select address,disk_reads,executions,pct,rank() over (order by disk_reads desc) ranking from

(select address,disk_reads,executions,100 * ratio_to_report(disk_reads) over () pct

from sys.v_$sql

where command_type != 47)

where disk_reads > 50 * executions) s,sys.v_$sqltext p

where s.ranking <= 5 and p.address = s.address

order by 1, s.address, p.piece;

4. 用下列SQL工具找出低效SQL:

SQL>select executions,disk_reads,buffer_gets,round((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio,round(disk_reads/executions,2) reads_per_run,sql_text

From v$sqlarea

Where executions>0 and buffer_gets >0 and (buffer_gets-disk_reads)/buffer_gets<0.8

Order by 4 desc;

5、根据sid查看对应连接正在运行的sql

SQL>select /*+ push_subq

*/command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions,users_executing,loads,first_load_time,in

validations,parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,’>’||address sql_address,’N’status

From v$sqlarea

Where address=(select sql_address from v$session where sid=&sid);

6.查询执行次数最多的sql语句

select t.FIRST_LOAD_TIME,t.LAST_LOAD_TIME,t.* from (select t.EXECUTIONS as excutnum ,t.* from v$sqlarea t order by t.EXECUTIONS desc)t where rownum <10 ;

1.2 Oracle 的DataBase

物理结构：oracle的物理结构指的其实就是一些存在硬盘上的各种文件。其最终目的就是存储和获取相关数据。这些存在硬盘上的文件组成了oracle数据库的物理结构

Oracle中主要包括三种文件：

1.数据文件

2.控制文件

3.重做日志文件（参见3Oracle热归档模式）

参数文件和密码文件：

参数文件：定义了数据库实例的特性，他是正文文件

密码文件：授予用户启动和关闭数据库的实例。主要有二进制数据组成

1.2.1表空间

Oracle的表空间属于Oracle中的存储结构，是一种用于存储数据库对象(如：数据文件)的逻辑空间，是Oracle中信息存储的最大逻辑单元，其下还包含有段、区、数据块等逻辑数据类型。表空间是在数据库中开辟的一个空间，用于存放数据库的对象，一个数据库可以由多个表空间组成。可以通过表空间来实现对Oracle的调优

这里介绍表空间。

在oracle的底层的数据存储递增为： byte-block（多个字节构成）-extend（多个block构成，控制段增长多少）-segement（多个extend构成）-tablespace。

以下是表空间相关表：

1.dba_tablespaces

2.dba_data_files

3.dba_segments

查询block的大小： select value from v$parameter where name='db_block_size'

创建表空间和修改如下：

Oracle 表空间的概念 - 90500042陈 - 博客园

1.2.2 表删除对tablespace的影响

在 oracle中，你在drop表的时候，实际上表没有彻底删除，表结构和数据会放在回收站中。

当你通过dba_segement 查看到这样的格式的表，就是回收站的表。

查询回收站中表： select * from recyclebin where type='TABLE';
删除回收站表：PURGE RECYCLEBIN;

删除指定回收站表：PURGE TABLE TABLE_NAME

参考具体分析：oracle删除表，让整个表从数据库中彻底消失_huangbaokang的博客-CSDN博客_oracle 删除表

2.Oracle 数据库优化

2.1数据库层面

2.1.1索引优化

索引列进行按顺序排列，通过索引可以快速的找到数据速度。

常见的索引有B-TREE索引、位图索引、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等。

1.数据影响

同时添加索引对以下情况（会影响导入速度）：

修改列是索引列，导入数据，删除数据

这些情况下，影响比列与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：

索引对于Insert性能降低56%

索引对于Update性能降低47%

索引对于Delete性能降低29%

但是合理使用索引，极大的提高数据的查询速度，所以优化索引列是数据库层面优化的必备选项。

2..查询数据库中执行较长的sql语句

我们前面提过v$sql表和v$sqlarea表

查询执行时间大于10S的sql语句：

目前我了解到的 elapsed_time 的单位是 1/1000000 秒。

select t.* , round(t.ELAPSED_TIME / t.EXECUTIONS ,2)/1000 from v$sqlarea t where t.EXECUTIONS >0 order by t.ELAPSED_TIME / t.EXECUTIONS desc;

3.索引创建原理

有些索引创建了，但是在sql查询的时候会发现还是走了全表扫描没有走索引。因为在有些情况下，会自动放弃索引使用。

以下为索引放弃情况：

1.只用<> 或者 not in 或者 is not null

2.经过普通运算或函数运算后的索引字段不能使用索引 select t.* from table where t.column +1>10;

3.使用前导的模糊查询 select t.* from table where t.column like '%dd';

4.使用 is null

我们一般在什么字段上建索引？

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1、字段出现在查询条件中，并且查询条件可以使用索引；

2、语句执行频率高，一天会有几千次以上；

3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

单条记录长度≈字段平均内容长度之和+字段数*2

如何知道SQL是否使用了正确的索引？

简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划。

4.创建复合索引

创建复合缩印的语句：

create index indexName on tableName（column1，column2）；

首先的 column1在前面，会以column1为主排序，在以column2 排序具体的结构图如下：

在这里插入图片描述

所以如果检索的情况下将column2放在前面，会造成索引失效的。

3.Oracle的热归档模式

在基本的生产运行环境下，归档模式是被推崇的模式。

非归档模式下，一般有3个log文件，它是循环写入的，比如当前 redo01写满了，会切换到reo02上，此时如果redo02是满的，会清空redo02，重新写入新的数据。

归档模式下，所有的事务重做日志都将保存.这意味着对数据库进行的所有事务都留有一个备份,尽管重做日志以循环方式工作,但在一个重做日志被覆盖前均将为其建立一个副本.在重做日志文件复制完成之前,ORACLE数据库将停止一切新的操作。

同时两种模式还体现在数据备份上：

非归档模式，非归档模式只能做冷备份（数据备份时，停止备份服务）,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复。在特殊的意外情况下，可能会丢失完全备份和系统出错之间的操作数据。

归档模式，归档模式可以做热备份,并且可以做增量备份,可以做部分恢复.

3.1 模式查询

sql： select t.* from V$database t; 查询出数据库是否用归档模式。

sql:查询日志状态和日志文件

select * from v$log;
select * from v$logfile;

sql：查询出归档模式下，归档文件的已使用率和log对应的sequence ，通过sequence可以进行数据还原。

select * from v$recovery_area_usage ;
select * from v$log_history ;

3.2开启归档模式

3.2.1 打开sqlplus工具：

sqlplus工具路径：F:\Oracle\product\11.2.0\dbhome_1\BIN中的sqlplus.exe

双击打开即可。（或者cmd到）

3.2.2 登录到Oracle数据库：

第一种方式：

1.sqlplus /nolgo; 登入sqlplus

2. conn user/password@127.0.0.1/orcl 或者 conn /as sysdba（用管理员权限登录）

输入conn /as sysdba回车后提示需要输入口令，此时不必输入口令直接回车即可。

3.2.3 查看当前oracle的模式：

select log_mode from v$database;（此处的分号不可省略）

如果当前模式为NOARCHIVELOG，就说明没有开启归档模式

3.2.4 关闭数据库，启动到mount状态：

shutdown immediate;

startup mount;

3.2.5 修改数据库为归档模式

alter database archivelog;（此处的分号不可以省略，如果不加分号会提示“2”，并不会执行数据库修改）

（如果是归档模式改为非归档，则执行： alter database noarchivelog;）

3.2.6 启动数据库：

alter database open;（此处的分号不可以省略，如果不加分号会提示“2”，并不会执行数据库修改）

3.2.7 再次验证：

执行select log_mode from v$database;

可查看到oracle已经修改为归档模式了

3.3 归档日志的查看及删除：

3.3.1 归档日志的查询及日志空间增加：

归档日志的大小是有限制的，不能让归档文件无限增加，需要定时进行删除等操作。

SQLPlus执行conn /as sysdba去查看，也可以只直接在PLSQL中查询：

执行:

select * from v$flash_recovery_area_usage;

ARCHIVED LOG行的percent_space_used 表示归档日志占用空间的百分比：

如果 ARCHIVED LOG 超过90% oracle随时有宕机的危险。

该操作需要在SQLPlus中进行：

alter system set db_recovery_file_dest_size=20G;

3.3.2 删除归档日志释放磁盘空间

删除归档日志需要进入RMAN工具才可操作，RMAN 是Oracle数据库软件自带的备份恢复工具，一种是类似于DOS，通过键盘操作的命令行方式。

3.3.3 进入RMAN工具

CMD输入RMAN，如果进不去，提示不是内部或外部命令，则进入数据库bin目录再执行RMAN，如下图

　　2.进入RMAN连接数据的两种方法：

（1）可以按照第一点CMD进去先启动RMAN，然后再通过CONNECT命令来连接目标数据库

（2）先指定要连接的目标数据库的实例名“SET ORACLE_ID=实例名”。如果本地库只有一个实例并已经设置了ORACLE_SID环境变量，则不需要再指定ORACLE_SID。RMAN会自动连接到默认实例。如图，再执行RMAN TARGET/

　　3.需要注意，Linux/UNIX 环境下设置操作系统环境变量应使用export命令，另外ORACLE_SID必须为大写。例如：

3.3.4 删除归档日志

先查看归档日志状态：

RMAN>list archivelog all;

　　2.手工删除归档日志文件（删除7天前的所有归档日志）

RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

说明：
SYSDATA-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志，如果使用了闪回功能，也会删除闪回的数据。
同样道理，也可以删除从7天前到现在的全部日志，不过这个命令要考虑清楚，做完这个删除，最好马上进行全备份数据库。

　　3.退出rman

RMAN> exit

3.4 Windows定时删除归档日志

3.4.1 创建一个删除归档日志的脚本（delete_arch.txt）：

connect target /

run{

crosscheck archivelog all;

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

delete expired archivelog all;

}

注：如果数据库为单实例数据库直接用“connect target /”连接数据库，如果数据为多实例数据库，需指定特定实例及用户：connect target sys/oracle@hrst即脚本格式为：

connect target sys/oracle@hrst

run{

crosscheck archivelog all;

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

delete expired archivelog all;

}

其中DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'; //删除七天前的归档日志

3.4.2 创建批处理任务（delete_archive.bat）

rman cmdfile=C:\Users\Tanr\Desktop\定期删除归档日志\delete_arch.txt

当由于电脑系统问题，必须要在Oracle的bin目录才能执行RMAN时，可以这种写法：

cd ..\

cd F:\Oracle\product\11.2.0\dbhome_1\BIN

rman cmdfile=C:\Users\Tanr\Desktop\定期删除归档日志\delete_arch.txt

3.4.3 创建一个windows任务定时调用批处理任务（delete_archive.bat）

开始 => 所有程序 => 附件 => 系统工具 => 任务计划

新建个任务计划了，然后根据要求配置下即可。

一般情况计划任务可设置一周执行一次，没有必要设置每天都执行。

4.Oracle用户和数据备份

在1.oracle架构中，已经写过如何创建oracle的数据库和实例。这里接着说，创建实例好了之后，关于用户的创建和数据备份。

4.1用户创建

以下是创建用户的语句：

先cmd打开控制台（user代表用户名，password代表密码）

如果一开始不知道有创建权限的用户和密码：sqlplus / as sysdba

如果知道，直接使用登录： sqlplus user/password

创建语句如下： create user identified by password

赋权语句： grant privilege（具体权限） to user

删除语句（包括用户名下表）： drop user cascade

用户相关权限查询，可以通过一下表查询。具体数据可以参看：Oracle 用户，角色，权限等 - 一泽涟漪 - 博客园

select t.* from dba_users t ;
select t.* from role_role_privs t ;
select t.* from user_role_privs t ;

4.2数据库备份

首先确保你先使用的用户是具有数据导入导出的权限的。

weixin_41164620

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle架构和应用

1.oracle架构图电脑的基本组成：本地磁盘（L5+L6），内存（L4及以上），CPU。同理在oracle架构上，也对应分成三大模块：oracle数据库（本地磁盘），SGA：share global area（内存缓存区），Oracle服务进程。具体结构图如下：...
复制链接

扫一扫