Oracle中提高RMAN备份速度
👉 本文共约1152个字，系统预计阅读时间或需5分钟。
数据库现状
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | 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> aaaa10 66.7578125 SYSAUX .6640625 UNDOTBS1 4.83886719 aaaa3 165.399414 aaaa8 66.7578125 aaaa1 165.399414 aaaa7 66.7578125 USERS .004882813 aaaa6 165.399414 SYSTEM .7421875 aaaa2 165.399414 aaaa4 165.399414 aaaa5 165.399414 aaaa9 66.7578125 aaaa11 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 |
数据文件大小
1 2 | du -sh /test-nvme/oradata/orcl 1.4T /test-nvme/oradata/orcl |
修改linux内核参数shmmax和shmall
修改之前共享内存
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | [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内核参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | $ 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 ~]$ |
修改之后共享内存
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [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内核参数
1 2 3 4 5 6 7 8 9 10 11 12 13 | [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个，碎片大大减少。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
改成异步IO
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | 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/aaaa1.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa2.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa3.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa4.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa5.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa6.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa7.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa8.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa9.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa10.dbf ASYNC_OFF /test-nvme/oradata/orcl/aaaa11.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/aaaa1.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa2.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa3.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa4.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa5.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa6.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa7.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa8.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa9.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa10.dbf ASYNC_ON /test-nvme/oradata/orcl/aaaa11.dbf ASYNC_ON 15 rows selected. SQL> |
修改成hugepage
参考：https://www.xmmup.com/oracleshujukupeizhidaye.html
没有优化前备份性能
修改环境变量，将rman中的时间提示最小单位从天改到秒：
export NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK # 或： export NLS_LANG=AMERICAN
rman备份
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | 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/aaaa1.dbf input datafile file number=00006 name=/test-nvme/oradata/orcl/aaaa2.dbf input datafile file number=00007 name=/test-nvme/oradata/orcl/aaaa3.dbf input datafile file number=00008 name=/test-nvme/oradata/orcl/aaaa4.dbf input datafile file number=00009 name=/test-nvme/oradata/orcl/aaaa5.dbf input datafile file number=00010 name=/test-nvme/oradata/orcl/aaaa6.dbf input datafile file number=00011 name=/test-nvme/oradata/orcl/aaaa7.dbf input datafile file number=00012 name=/test-nvme/oradata/orcl/aaaa8.dbf input datafile file number=00013 name=/test-nvme/oradata/orcl/aaaa9.dbf input datafile file number=00014 name=/test-nvme/oradata/orcl/aaaa10.dbf input datafile file number=00015 name=/test-nvme/oradata/orcl/aaaa11.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情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | $ 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备份
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | [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/aaaa1.dbf input datafile file number=00006 name=/test-nvme/oradata/orcl/aaaa2.dbf input datafile file number=00007 name=/test-nvme/oradata/orcl/aaaa3.dbf input datafile file number=00008 name=/test-nvme/oradata/orcl/aaaa4.dbf input datafile file number=00009 name=/test-nvme/oradata/orcl/aaaa5.dbf input datafile file number=00010 name=/test-nvme/oradata/orcl/aaaa6.dbf input datafile file number=00011 name=/test-nvme/oradata/orcl/aaaa7.dbf input datafile file number=00012 name=/test-nvme/oradata/orcl/aaaa8.dbf input datafile file number=00013 name=/test-nvme/oradata/orcl/aaaa9.dbf input datafile file number=00014 name=/test-nvme/oradata/orcl/aaaa10.dbf input datafile file number=00015 name=/test-nvme/oradata/orcl/aaaa11.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性能
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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，性能大幅提高；
扩展
另外一点，若想加速rman的备份速度，那么可以充分使用CPU的并行能力，开启多通道备份，加上是否进行压缩备份，开启压缩，肯定会导致备份时间加长，在备份和恢复时配置并行进程。
并行备份的shell脚本如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | #!/bin/ksh export ORACLE_SID=orcl export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" MYDATE=`date +'%Y%m%d%H%M%S'` rman target / log /rman/backup_orcl_full_$MYDATE.log append <<EOF run { sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"'; sql 'alter session set NLS_LANGUAGE="AMERICAN"'; allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate channel c7 type disk; allocate channel c8 type disk; allocate channel c9 type disk; allocate channel c10 type disk; allocate channel c11 type disk; allocate channel c12 type disk; backup as compressed backupset FILESPERSET 10 database format '/rman/FULL_%d_%U.full' section size 100G; backup as compressed backupset archivelog from time 'sysdate-1' format '/rman/ARC_%d_%U.arc' section size 100G; backup current controlfile format '/rman/control_%U.ctl'; backup spfile format '/rman/spfile_%d_%U.ora'; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; release channel c11; release channel c12; } EOF |
或者也可以直接在rman中配置并行的进程数也可以：
1 | CONFIGURE DEVICE TYPE DISK PARALLELISM 12 BACKUP TYPE TO BACKUPSET; |
