【Oracle设置bct备份，效率有多高】实验
|
oracle block change tracking 简称oracle bct，该功能在数据库rman增量备份过程中，确实能减少增量备份的时间，提高备份的效率，在做增量备份之前，我们需要做level 0级别的备份，在做下一次level 1级别备份前，会将2次备份之间的数据差异通过位图的形式，以日志的方式记录在bct日志中，这样在做增量备份时，不需要对每个daafile文件进行比对，减少了比对时间，提高了备份效率。 下面我们看下数据库的增量备份演进； 1、现实及历史 Oracle bct是在10g时提出的，当数据库进行增量备份时，记录每个块的变化情况记录到bct文件，这样在下一次增量备份时，不需要进行数据库所有文件的扫描，直接对变更的数据库进行备份，缩短了备份执行的时间及系统软硬件资源的消耗。 2、bct备份原理及基础 bct是基于bitmap file方式记录每个数据文件块的变化，启用了bct服务后，不需要其他维护操作，将上次level0级备份以来所变化的块进行记录，记录到bct文件中，文件中包含了8次bitmaps信息，所以增量备份时，需要记得这个前提，当超过8次时，bitmap信息将会覆盖，那么bct将不会对增量备份进行优化，将重新扫描所有的数据文件。同时bct文件的大小和数据库的大小及开启的线程redo的数据有关系，tracking file的大小会根据数据库大小的变化而变化，和更新频率没有关系。单实例的情况下大约为1/30000数据库的大小，如果是rac环境，需要考虑thread数量。 初始值为10m，每个datafile，在tracking file里最少分配320k的空间，所以如果有大量的小datafile，change tracking file也会相对较大。 3、bct策略需要注意事项 ①开启bct功能，如果直接开启该功能，其文件存放位置和参数db_create_file_dest相关，需设置该参数。 ②直接开始bct并指定具体位置；alter database enable block change tracking using file '/home/oracle/track.f'; SQL> select * from v$block_change_tracking; STATUS FILENAME BYTES -------- ------------------------------------------------------------ ---------- ENABLED /home/oracle/track.f 11599872 4、bct在还原库测试 ①备份数据库（Oracle 11.2.0.4） level0级别备份命令： run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; backup as compressed backupset incremental level 0 database tag='level0' format '/home/oracle/backup/full_db_level0_%U_%T.bak' ; sql 'alter system archive log current'; backup as compressed backupset archivelog all tag='arc_bak' format='/home/oracle/backup/archivelog_%U_%T' skip inaccessible filesperset 6 not backed up 1 times delete input; backup current controlfile format '/home/oracle/backup/ctl_level0_%U_%T.bak'; backup spfile format '/home/oracle/backup/spfile_level0_%U_%T.bak'; release channel c1; release channel c2; release channel c3; release channel c4; } 开启bct，插入数据scott.it数据 SQL> select * from it; ID ---------- 1 2 100 SQL> commit; 进行level1级别备份命令： run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; backup as compressed backupset incremental level 1 database tag='level1' format '/home/oracle/backup/full_db_level1_%U_%T.bak' ; sql 'alter system archive log current'; backup as compressed backupset archivelog all tag='arc_bak' format='/home/oracle/backup/archivelog_%U_%T' skip inaccessible filesperset 6 not backed up 1 times delete input; backup current controlfile format '/home/oracle/backup/ctl_level1_%U_%T.bak'; backup spfile format '/home/oracle/backup/spfile_level1_%U_%T.bak'; release channel c1; release channel c2; release channel c3; release channel c4; } [oracle@db1 backup]$ ls -lrth total 313M -rw-r----- 1 oracle oinstall 1.1M Aug 22 13:18 full_db_level0_0q15q71j_1_1_20220822.bak -rw-r----- 1 oracle oinstall 22M Aug 22 13:19 full_db_level0_0p15q71j_1_1_20220822.bak -rw-r----- 1 oracle oinstall 96K Aug 22 13:19 full_db_level0_0r15q729_1_1_20220822.bak -rw-r----- 1 oracle oinstall 74M Aug 22 13:19 full_db_level0_0o15q71j_1_1_20220822.bak -rw-r----- 1 oracle oinstall 195M Aug 22 13:19 full_db_level0_0n15q71j_1_1_20220822.bak -rw-r----- 1 oracle oinstall 13K Aug 22 13:19 archivelog_0s15q72p_1_1_20220822 -rw-r----- 1 oracle oinstall 2.5K Aug 22 13:19 archivelog_0t15q72p_1_1_20220822 -rw-r----- 1 oracle oinstall 9.4M Aug 22 13:19 ctl_level0_0u15q72q_1_1_20220822.bak -rw-r----- 1 oracle oinstall 96K Aug 22 13:19 spfile_level0_0v15q72s_1_1_20220822.bak -rw-r----- 1 oracle oinstall 608K Aug 22 13:30 full_db_level1_1015q7ms_1_1_20220822.bak -rw-r----- 1 oracle oinstall 128K Aug 22 13:30 full_db_level1_1215q7ms_1_1_20220822.bak -rw-r----- 1 oracle oinstall 664K Aug 22 13:30 full_db_level1_1115q7ms_1_1_20220822.bak -rw-r----- 1 oracle oinstall 96K Aug 22 13:30 full_db_level1_1415q7mt_1_1_20220822.bak -rw-r----- 1 oracle oinstall 1.1M Aug 22 13:30 full_db_level1_1315q7ms_1_1_20220822.bak -rw-r----- 1 oracle oinstall 525K Aug 22 13:30 archivelog_1515q7mv_1_1_20220822 -rw-r----- 1 oracle oinstall 2.5K Aug 22 13:30 archivelog_1615q7mv_1_1_20220822 -rw-r----- 1 oracle oinstall 9.4M Aug 22 13:30 ctl_level1_1715q7n0_1_1_20220822.bak -rw-r----- 1 oracle oinstall 96K Aug 22 13:30 spfile_level1_1815q7n2_1_1_20220822.bak [oracle@db1 backup]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on 星期一 8月 22 13:32:10 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1640816139) RMAN> list backup summary; using target database control file instead of recovery catalog List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 23 B 0 A DISK 22-8月 -22 1 1 YES LEVEL0 24 B 0 A DISK 22-8月 -22 1 1 YES LEVEL0 25 B 0 A DISK 22-8月 -22 1 1 YES LEVEL0 26 B 0 A DISK 22-8月 -22 1 1 YES LEVEL0 27 B 0 A DISK 22-8月 -22 1 1 YES LEVEL0 28 B A A DISK 22-8月 -22 1 1 YES ARC_BAK 29 B A A DISK 22-8月 -22 1 1 YES ARC_BAK 30 B F A DISK 22-8月 -22 1 1 NO TAG20220822T131922 31 B F A DISK 22-8月 -22 1 1 NO TAG20220822T131924 32 B 1 A DISK 22-8月 -22 1 1 YES LEVEL1 33 B 1 A DISK 22-8月 -22 1 1 YES LEVEL1 34 B 1 A DISK 22-8月 -22 1 1 YES LEVEL1 35 B 1 A DISK 22-8月 -22 1 1 YES LEVEL1 36 B 1 A DISK 22-8月 -22 1 1 YES LEVEL1 37 B A A DISK 22-8月 -22 1 1 YES ARC_BAK 38 B A A DISK 22-8月 -22 1 1 YES ARC_BAK 39 B F A DISK 22-8月 -22 1 1 NO TAG20220822T133008 40 B F A DISK 22-8月 -22 1 1 NO TAG20220822T133010 ②恢复数据库 直接使用oracle数据库dummy方式启动到nomount状态，通过已有的备份恢复spfile文件 [oracle@db1 dbs]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on 星期一 8月 22 13:46:26 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 285213576 bytes Database Buffers 775946240 bytes Redo Buffers 5517312 bytes RMAN> restore spfile from '/home/oracle/backup/spfile_level1_1815q7n2_1_1_20220822.bak'; Starting restore at 22-8月 -22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=429 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/spfile_level1_1815q7n2_1_1_20220822.bak channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 22-8月 -22 SQL> startup nomount; ORACLE instance started. Total System Global Area 1185853440 bytes Fixed Size 2252664 bytes Variable Size 956301448 bytes Database Buffers 218103808 bytes Redo Buffers 9195520 bytes SQL> quit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@db1 ~]$ rman target / Recovery Manager: Release 11.2.0.4.0 - Production on 星期一 8月 22 13:50:35 2022 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (not mounted) RMAN> restore controlfile from '/home/oracle/backup/ctl_level1_1715q7n0_1_1_20220822.bak'; Starting restore at 22-8月 -22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=572 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/oradata/orcl/control01.ctl output file name=/oradata/orcl/control02.ctl Finished restore at 22-8月 -22 RMAN> alter database mount; 恢复数据库：restore & recover database； run{ restore database; recover database; alter database open resetlogs; } 检查数据情况： [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on 星期一 8月 22 13:55:59 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SQL> conn scott/oracle Connected. SQL> select * from it; ID ---------- 1 2 100 数据恢复完成，数据无丢失，此时bct状态为启用状态，数据库已经恢复过，其需要初始化才能发挥其备份优化的功能。SQL> select * from v$block_change_tracking; STATUS FILENAME BYTES -------- ---------------------------------------- ---------- ENABLED /home/oracle/track.log 11599872 5、bct对数据库性能的影响 ①OLTP类型业务，启用bct对数据库的性能的影响几乎为0. ②OLAP类型业务，对于数仓类型的业务，大量的数据录入，造成bct进程来不及进行写入到track.f日志中，造成拥塞，这时会反应到dbtime占用上，如果发现这种情况，可以反应出bct的开启对数据库性能的影响。 总结：bct功能使用在增量备份过程中，提高增量备份数据库的效率。一般推荐数据库的块变化量小于20%时应该激活BCT。同时bct在恢复库的过程中，如果track.f文件丢失，开启数据库会报错，只要在mount状态下将bct功能关闭，在开启open状态即可。 Yicheng16 -- The End -- （编辑：泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |