AI 助理
备案控制台
开发者社区 数据库 文章 正文

这样做，RMAN备份速度可提高6倍！

2023-05-29 131
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 本例是我在实际工作中帮客户做的一个Oracle备份优化案例。

本例是我在实际工作中帮客户做的一个Oracle备份优化案例。

数据库现状

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE  11.2.0.4.0  Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> set pagesize 0;
select tablespace_name ,sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name;
SQL> CCCC10          66.7578125
SYSAUX     .6640625
UNDOTBS1         4.83886719
CCCC3          165.399414
CCCC8          66.7578125
CCCC1          165.399414
CCCC7          66.7578125
USERS          .004882813
CCCC6          165.399414
SYSTEM     .7421875
CCCC2          165.399414
CCCC4          165.399414
CCCC5          165.399414
CCCC9          66.7578125
CCCC11          66.7578125
SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select max(LAST_ANALYZED) from dba_tables;
2020-02-25 22:20:26

数据文件大小


du -sh  /test-nvme/oradata/orcl 
1.4T  /test-nvme/oradata/orcl


修改linux内核参数shmmax和shmall

修改之前共享内存

[oracle@base-test-01 ~]$ ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 114458624  oracle     640        4096       0                       
0x00000000 114491393  oracle     640        4096       0                       
0x00000000 114524163  oracle     640        4096       0                       
0x00000000 1212420    root       644        80         2                       
0x00000000 1245189    root       644        16384      2                       
0x00000000 1277958    root       644        280        2                       
0x00000000 297435143  root       600        524288     2          dest         
0x00000000 114556936  oracle     640        4096       0                       
0x00000000 114589705  oracle     640        4096       0                       
0x00000000 114622474  oracle     640        4096       0                       
0x00000000 114655243  oracle     640        4096       0                       
0x00000000 114688012  oracle     640        4096       0                       
0x00000000 114720781  oracle     640        4096       0                       
0x00000000 114753550  oracle     640        4096       0                       
0x00000000 114786319  oracle     640        4096       0                       
0x00000000 114819088  oracle     640        4096       0                       
0x00000000 114851857  oracle     640        4096       0                       
0x00000000 114884626  oracle     640        4096       0                       
0x00000000 114917395  oracle     640        4096       0                       
0x00000000 114950164  oracle     640        4096       0                       
0x00000000 114982933  oracle     640        4096       0                       
0x00000000 115015702  oracle     640        4096       0                       
0x00000000 115048471  oracle     640        4096       0                       
0x00000000 115081240  oracle     640        4096       0                       
0x00000000 115114009  oracle     640        4096       0                       
0x00000000 115146778  oracle     640        4096       0                       
0x00000000 115179547  oracle     640        4096       0                       
0x00000000 115212316  oracle     640        4096       0                       
0x00000000 115245085  oracle     640        4096       0                       
0x00000000 115277854  oracle     640        4096       0                       
0x00000000 115310623  oracle     640        4096       0                       
0x00000000 115343392  oracle     640        4096       0                       
0x00000000 115376161  oracle     640        4096       0                       
0x00000000 115408930  oracle     640        4096       0                       
0x00000000 297762851  root       600        4194304    2          dest         
0x00000000 297861156  root       600        524288     2          dest         
0x00000000 115441701  oracle     640        4096       0                       
0x00000000 115474470  oracle     640        4096       0                       
0x00000000 115507239  oracle     640        4096       0                       
0x00000000 115540008  oracle     640        4096       0                       
0x42e38fd0 115572777  oracle     640        4096       0                       
0x00000000 127959083  root       600        832920     2          dest


修改之前linux内核参数

$ cat /etc/sysctl.conf
# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295 
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness=1
[oracle@base-test-01 ~]$


修改之后共享内存

[root@base-test-01 oracle]# ipcs -m
------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 504037376  oracle     640        536870912  72                      
0x00000000 504070145  oracle     640        107911053312 72                      
0x42e38fd0 504102915  oracle     640        2097152    72                      
0x00000000 1212420    root       644        80         2                       
0x00000000 1245189    root       644        16384      2                       
0x00000000 1277958    root       644        280        2                       
0x00000000 297435143  root       600        524288     2          dest         
0x00000000 508821513  root       600        832920     2          dest         
0x00000000 297762851  root       600        4194304    2          dest         
0x00000000 297861156  root       600        524288     2          dest 
#  sysresv -l orcl -d on
IPC Resources for ORACLE_SID "orcl" :
Shared Memory:
ID  KEY
504037376 0x00000000
504070145 0x00000000
504102915 0x42e38fd0




修改之后linux内核参数

[root@base-test-01 oracle]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 67108864
kernel.shmmax = 256000000000
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.swappiness = 1


前后对比

oracle的共享内存从35个减少到3个，碎片大大减少。

改成异步IO

SQL> SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File';
/test-nvme/oradata/orcl/system01.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/sysaux01.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/undotbs01.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/users01.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc1.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc2.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc3.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc4.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc5.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc6.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc7.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc8.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc9.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc10.dbf     ASYNC_OFF
/test-nvme/oradata/orcl/cccc11.dbf     ASYNC_OFF
SQL>   alter system set filesystemio_options=setall  scope=spfile;
System altered.
SQL> STARTUP FORCE;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.0796E+11 bytes
Fixed Size      2266024 bytes
Variable Size   5.6371E+10 bytes
Database Buffers  5.1540E+10 bytes
Redo Buffers     50450432 bytes
Database mounted.
Database opened.
SQL>  SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File';
/test-nvme/oradata/orcl/system01.dbf     ASYNC_ON
/test-nvme/oradata/orcl/sysaux01.dbf     ASYNC_ON
/test-nvme/oradata/orcl/undotbs01.dbf     ASYNC_ON
/test-nvme/oradata/orcl/users01.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc1.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc2.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc3.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc4.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc5.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc6.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc7.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc8.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc9.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc10.dbf     ASYNC_ON
/test-nvme/oradata/orcl/cccc11.dbf     ASYNC_ON
15 rows selected.
SQL>



修改成hugepage

过程比较复杂，另外写一个文档。


没有优化前备份性能

修改环境变量，将rman中的时间提示最小单位从天改到秒：

export NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK # 或： export NLS_LANG=AMERICAN
rman备份
RMAN>  backup validate database;
Starting backup at 2020-02-26 14:42:44
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/test-nvme/oradata/orcl/cccc1.dbf
input datafile file number=00006 name=/test-nvme/oradata/orcl/cccc2.dbf
input datafile file number=00007 name=/test-nvme/oradata/orcl/cccc3.dbf
input datafile file number=00008 name=/test-nvme/oradata/orcl/cccc4.dbf
input datafile file number=00009 name=/test-nvme/oradata/orcl/cccc5.dbf
input datafile file number=00010 name=/test-nvme/oradata/orcl/cccc6.dbf
input datafile file number=00011 name=/test-nvme/oradata/orcl/cccc7.dbf
input datafile file number=00012 name=/test-nvme/oradata/orcl/cccc8.dbf
input datafile file number=00013 name=/test-nvme/oradata/orcl/cccc9.dbf
input datafile file number=00014 name=/test-nvme/oradata/orcl/cccc10.dbf
input datafile file number=00015 name=/test-nvme/oradata/orcl/cccc11.dbf
input datafile file number=00003 name=/test-nvme/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/test-nvme/oradata/orcl/system01.dbf
input datafile file number=00002 name=/test-nvme/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/test-nvme/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:31:25
.........
RMAN>


从14:42:44开始到15:14:11结束，31分钟27秒，一共1887秒。


从os层查看io情况


$ iostat 60 nvme0n1
Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1        5091.10    650829.00        93.44   39049740       5606
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.84    0.00    2.11    0.75    0.00   96.30
Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1        5483.45    701698.13        11.38   42101888        683
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.93    0.00    2.20    0.72    0.00   96.15
Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1        5589.93    715302.93        12.60   42918176        756
[oracle@base-test-01 ~]$ iostat -x 60 nvme0n1
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 5255.30    0.93 672465.47     9.53   255.88     1.24    0.24    0.24    0.00   0.16  83.82
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.77    0.00    2.11    0.73    0.00   96.39
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 5425.92    1.08 694424.67    11.71   255.92     1.25    0.23    0.23    0.05   0.16  85.25




优化后的备份性能

rman备份


[oracle@base-test-01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 26 18:28:40 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1560763823)
RMAN>  backup validate database;
Starting backup at 2020-02-26 18:28:47
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=9391 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/test-nvme/oradata/orcl/cccc1.dbf
input datafile file number=00006 name=/test-nvme/oradata/orcl/cccc2.dbf
input datafile file number=00007 name=/test-nvme/oradata/orcl/cccc3.dbf
input datafile file number=00008 name=/test-nvme/oradata/orcl/cccc4.dbf
input datafile file number=00009 name=/test-nvme/oradata/orcl/cccc5.dbf
input datafile file number=00010 name=/test-nvme/oradata/orcl/cccc6.dbf
input datafile file number=00011 name=/test-nvme/oradata/orcl/cccc7.dbf
input datafile file number=00012 name=/test-nvme/oradata/orcl/cccc8.dbf
input datafile file number=00013 name=/test-nvme/oradata/orcl/cccc9.dbf
input datafile file number=00014 name=/test-nvme/oradata/orcl/cccc10.dbf
input datafile file number=00015 name=/test-nvme/oradata/orcl/cccc11.dbf
input datafile file number=00003 name=/test-nvme/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/test-nvme/oradata/orcl/system01.dbf
input datafile file number=00002 name=/test-nvme/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/test-nvme/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:05:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              15274        97280           10829000  
  File Name: /test-nvme/oradata/orcl/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              65126           
  Index      0              13578           
  Other      0              3302            
......
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              3146            
Finished backup at 2020-02-26 18:34:04

5分钟15秒完成。

从OS层看磁盘的IO性能


Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.02 35998.80    0.45 4607693.07     5.99   255.99    90.92    2.53    2.53    0.41   0.03 100.00
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.74    0.00    1.23    0.00    0.00   97.03
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 35901.82    0.45 4595201.33     5.73   255.98    90.03    2.51    2.51    0.44   0.03 100.00
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.78    0.00    1.24    0.01    0.00   96.98
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 36015.75    0.52 4609726.14     5.54   255.98    88.58    2.46    2.46    0.26   0.03 100.00
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.66    0.00    1.19    0.00    0.00   97.14
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 36069.02    0.58 4616505.08     5.75   255.98    91.41    2.53    2.53    0.23   0.03 100.00



对比分析

项目 优化前 优化后 提高

1.4T全量备份时间 31分钟27秒 5分钟15秒 提高5.99倍

读nvme磁盘的速度 672465.47rkB/s 4607693.07rkB/s 提高6.8倍

iostate中磁盘利用率 80% 100% 提高20%

将同步IO改成异步IO，大大提高IO性能；

修改linux内核参数shmmax和shmall，将共享内存段从35个减少到3个，碎片大大减少；

配置hugepage，Oracle的共享内存管理单位从4K增加到2M，性能大幅提高；


文章标签：
关系型数据库
Linux
内存技术
Oracle
数据库
姚远OracleACE
目录
相关文章
baker_zhuang
|
UED
ThreeJs模拟工厂生产过程三
这篇文章介绍了在Three.js中通过使用mergeGeometries技术来合并大量车间模型，以减少浏览器渲染负担，提高性能，并提供了实现模型合并的具体方法和步骤。
baker_zhuang
266 5
Python编程站
|
Python Windows
python 程序打包成桌面exe程序（上）
python 程序打包成桌面exe程序
Python编程站
473 0
Crossin的编程教室
|
数据采集 小程序 数据挖掘
个人开发者如何申请微信小程序
从午饭后开始下载开发工具、看文档，花了一下午开发完，晚上又折腾了下服务器域名配置的小问题，然后提交审核。要等审核完才能对外发布。
Crossin的编程教室
898 0
IT鹅
|
安全 NoSQL Ubuntu
CVE-2021-4034 Pkexec LPE漏洞复现与原理分析
国外Qualys安全团队在CVE平台披露了Linux系统Polkit中的pkexec组件存在的本地权限提升漏洞（CVE-2021-4034）。Polkit默认安装在各个主要的 Linux 发行版本上（诸如Ubuntu、Debian、Fedora等知名Linux发型版本），pkexec程序对传入参数未过滤，攻击者可以将环境变量bash作为命令执行，从而诱导 pkexec 执行任意代码，利用成功可导致非特权用户获得管理员root权限。
IT鹅
1547 1
CVE-2021-4034 Pkexec LPE漏洞复现与原理分析
-开发达人-
|
消息中间件 运维 监控
ChaosBlade 在工商银行混沌工程体系中的应用实践
ChaosBlade 在工商银行混沌工程体系中的应用实践
-开发达人-
623 0
Crossin的编程教室
|
Python
电子游戏的核心原理
你小时候有没有玩过这样一种玩具：一块硬纸，一面画着一只鸟，一面画着一个笼子。硬纸下粘上一根细棒。用手来回转动细棒，让硬纸的两面快速交替出现，就会看见鸟被关在了笼子里。
Crossin的编程教室
300 0
夜郎king
|
数据可视化 C++
vscode插件离线安装及常见错误
本文简单介绍了vscode如何离线安装插件，以及2种具体的安装方式，最后给出了在插件安装过程中版本不一致时如何解决的方案，希望可以帮助到正在阅读的你​。
夜郎king
3518 0
vscode插件离线安装及常见错误
小哀女王
|
Linux 数据库
CentOS 6.8 上 MySQL-server 数据库安装失败
小哀女王
2383 0
行者武松
|
传感器 IDE 自动驾驶
无人机为何“一夜爆红”？开源飞控在其后助力
行者武松
2105 0
阿里云安全_
|
2天前
|
云安全 人工智能 算法
以“AI对抗AI”，阿里云验证码进入2.0时代
三层立体防护，用大模型打赢人机攻防战
阿里云安全_
1294 3