sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库rman备份与large_pool_size的关系详解

时间:2016-11-28 21:56   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:关于使用rman进行备份时,是否使用large pool,存在一个误区,很多人认为使用rman进行备份时,一定用的是large pool,包括我的同事也这样认为,其实不然。

SQL> archive log list;

DATABASE log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/ora10g/archivelog
Oldest online log SEQUENCE     216
NEXT log SEQUENCE TO archive   218
CURRENT log SEQUENCE           218

SQL> SHOW parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
asm_diskstring                       string
disk_asynch_io                       BOOLEAN     TRUE

SQL> SHOW parameter dbwr_io_slaves

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dbwr_io_slaves                       INTEGER     0

[ora10g@killdb bdump]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jan 3 05:31:43 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ROGER (DBID=2466925865)

RMAN> allocate channel for maintenance type disk;

using target database control file instead of recovery catalog
allocated channel: ORA_MAINT_DISK_1
channel ORA_MAINT_DISK_1: sid=138 devtype=DISK
SQL> SELECT * FROM v$sgastat WHERE pool='large pool';

POOL         NAME                            BYTES
------------ -------------------------- ----------
LARGE pool   PX msg pool                    262144
LARGE pool   free memory                   3932160

SQL> SELECT * FROM v$sgastat WHERE name LIKE '%KSFQ%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  X$KSFQP ANCHOR                     52
shared pool  KSFQ buffer pool                 2376

SQL> ALTER system SET disk_asynch_io=FALSE scope=spfile;

System altered.

SQL> shOracleоutdown immediate;

DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.

SQL> startup

ORA-00000: normal, successful completion

SQL> conn /AS sysdba
Connected TO an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed SIZE                  1272600 bytes
Variable SIZE             104858856 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2920448 bytes
DATABASE mounted.
DATABASE opened.

SQL> SHOW parameter dbwr_io_slaves

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dbwr_io_slaves                       INTEGER     2

SQL> SHOW parameter disk

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
asm_diskstring                       string
disk_asynch_io                       BOOLEAN     FALSE

SQL> SELECT * FROM v$sgastat WHERE name LIKE '%KSFQ%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  X$KSFQP ANCHOR                     52
shared pool  KSFQ buffer pool                 2376

[ora10g@killdb bdump]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Jan 3 05:34:47 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ROGER (DBID=2466925865)

RMAN> allocate channel for maintenance type disk;

using target database control file instead of recovery catalog
allocated channel: ORA_MAINT_DISK_1
channel ORA_MAINT_DISK_1: sid=143 devtype=DISK
SQL> SELECT * FROM v$sgastat WHERE name LIKE '%KSFQ%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  X$KSFQP ANCHOR                     52
shared pool  KSFQ buffer pool                 2376
shared pool  KSFQ Buffer Structures            208
LARGE pool   KSFQ Buffers                  4210688

SQL> SELECT * FROM v$sgastat WHERE pool='large pool';

POOL         NAME                            BYTES
------------ -------------------------- ----------
LARGE pool   PX msg pool                    262144
LARGE pool   free memory                   3751888
LARGE pool   KSFQ Buffers                  4210688
LARGE pool   KGH: NO ACCESS                 163888

可见,当dbwr_io_slaves为非0值,且disk_asynch_io为false时,rman才会使用large pool。
如果是sbt,那么需要将 backup_type_io_salves 设置为true。

补充1:
How to Calculate Rman Memory Allocation In Large Pool [ID 336313.1]

Solution

Contrary to the rman documentation at 9i, the amount of memory required by RMAN to be set aside
in LARGE POOL when asynchronous io is simulated with the use of slaves is NOT 16Mb per channel.

For a disk backup:

Each channel is allocated 4*1Mb (output buffers) plus 16*1Mb (input buffers)

For a tape backup:

Each channel is allocated 4*  plus 16*1Mb (input buffers)

Additionally, when the controlfile is included in the backup the memory allocated jumps by a
further 16 Mb (for the channel doing the controlfile backup) to 32Mb plus the output buffers
(tape or disk).

Bug 4513611 (still with Development) has been raised to clarify the documentation and
confirm if this is expected behaviour.

In the meantime, to cater for all circumstances, large pool should be set as follows (bearing in
mind that the controlfile can only be backed up by one channel) :

For disk channels

LARGE_POOL_SIZE = (No:channels * (16 + 4)) +16 Mb

For tape channels:

LARGE_POOL_SIZE = (No:channels * (16 + 4()) +16 Mb
补充2:
RMAN: I/O Slaves and Memory Usage [ID 73354.1]

Contents:

1.0 How Does RMAN make use of memory buffers
2.0 Size of Input/Output Buffers
3.0 Why Use I/O Slaves
4.0 Configuring I/O Slaves


1.0 How Does RMAN make use of memory buffers
=============================================

For each backup/restore operation, every server session (ie, RMAN channel)
allocates

a. 4 input buffers for every disk file
b. 4 output buffers for every backup piece

    memory(input) = #buffers * #files * buffersize
               = 4 * #files * buffersize

#files = total number of files concurrently open

To reduce the amount of memory used by RMAN set - MAXOPENFILES = 
EG - Before maxopenfiles
4*100(files)*8192*64
After maxopenfiles = 4
4*4(files)*8192*64

This can be illustrated by the following:

RMAN> run {
    allocate channel c1 type 'SBT_TAPE';
    backup datafile 1,2;
   }



+-----+   file 1
|+-----+  input buffers
||+-----+
|||+-----+
||||     |                               output buffers
+|||     |
  +||     |                                +-----+
   +|     |           +-----------+        |+-----+      +------+
    +-----+           |   server  |        ||+-----+     | o__o |
                      +-----------+        |||+-----+    +------+
                                           ||||     |
+-----+                                   +|||     |
|+-----+                                   +||     |
||+-----+                                   +|     |
|||+-----+                                   +-----+
||||     | file 2
+|||     | input buffers
  +||     |
   +|     |
    +-----+


The server process reads data from the disk file into one of the input buffers.
A given buffer is dedicated to a file whilst a server process is operating on
that file. When one buffer fills up, the server process writes to one of the
other three. The buffers are used in a circular fashion.

The input buffers will contain blocks that do not need to be backed up, as well
as those that do.

A 'memory copy' routine is used to copy the required data from an input to an
output buffer. This is where block corruption is checked (ie, validate header,
compute checksums if enabled).


2.0 Size of Input/Output Buffers
================================

a. input buffers
----------------

NOTE : DB_FILE_DIRECT_IO_COUNT is not available in Oracle9i onwards.
       In Oracle9i, it is replaced by a hidden _DB_FILE_DIRECT_IO_COUNT which
       governs the size of direct I/Os in BYTES (not blocks). The default is
       1Mb butwill be sized down if the max_io_size of the system is smaller.

The input buffer size is:
  buffersize = db_block_size * db_file_direct_io_count

As there are 4 input buffers, the total input buffer memory use per channel is:
memory(input) = #buffers * #files * buffersize
               = 4 * #files * buffersize

For example, if 2 channels are used, and each of these channels backs up 3
files, then for each channel

memory(input) = 4 * 3 * db_block_size * db_file_direct_io_count


b. output buffers
-----------------

For disk channels, the output buffer size is:
  buffersize = db_block_size * db_file_direct_io_count

For SBT_TAPE channels, the output buffer size in Oracle8/8i is o/s dependant. (On Solaris,
this defaults to 64k) On 9i/10g it defaults to 256k for all platforms. The BLKSIZE argument
to 'allocate channel...' can be used to override the default value.

As there are 4 output buffers,
  memory(output) = #buffers * buffersize
                 = 4 * buffersize


c. Allocation of Memory
-----------------------

This memory is allocated from the channel server process PGA, unless i/o slaves
are used. I/O slave memory is allocated from the SGA in order for the memory to
be shared between the I/O slave and the channel server process. In this case,
Oracle recommends the 'large pool' feature is used, i.e. Set the "init.ora"
parameter to:

  LARGE_POOL_SIZE =

where  is the size of the large pool, calculated from the above.

If the I/O slave cannot acquire the required memory from the SGA, then an
ORA-04031 error is asserted (see "alert.log"), and the operation continues
synchronously by allocating memory from the channel server's PGA.


3.0 Why Use I/O Slaves
=======================

For optimal performance during backup/restore operations, the goal should
be to keep the tape streaming i.e. continually moving. Stopping and starting
tapes are expensive operations. Additionally, potential tape stretching will
lower the life span of the tape.

I/O slaves can be used to provide such a performance enhancement by simulating
asynchronous I/O. There are two types of I/O slaves; disk slaves and
tape slaves.

By default, all I/O to tape is synchronous. This means that the channel server
process is blocked from doing any work while waiting for a tape to complete a
write. Tape i/o slaves allow the channel server process to continue to fill and
process buffers whilst the tape write is completing.

It is also important to quickly fill the input buffers with data. On platforms
that do not support asynchronous I/O, the channel server process can be
blocked on a file read, thus preventing it from processing the buffers.
Disk I/O slaves can be used to asynchronously read from files,
thus enabling channel server process to continue to process the buffers.

This is especially important during incremental backups, or backups of 'empty'
files,  where the number of modified buffers is sufficiently low that the tape
is writing faster than the output buffers are being filled.


4.0 Configuring I/O Slaves
==========================

a. Disk Slaves
--------------

For Oracle 8.0, set the "init.ora" parameter

BACKUP_DISK_IO_SLAVES =

where  is the number of disk i/o slaves to start.

Oracle recommends that no more than 4 disk slaves are started. In this case,
extra channels should be considered.

For Oracle 8i/9i/10g, set the "init.ora" parameter

DBWR_IO_SLAVES > 0

This causes 4 disk i/o slaves to be started.

Note that every channel server process doing a backup/restore will be assigned
this number of disk i/o slaves.


b. Tape Slaves
--------------

Set the "init.ora" parameter

BACKUP_TAPE_IO_SLAVES = true

This causes one tape I/O slave to be assigned to each channel server process.

In 8i/9i/10g, if the DUPLEX option is specified, then tape I/O slaves must be enabled.
In this case, for DUPLCEX=, there are  tape slaves per channel. These N slaves
all operate on the same four output buffers. Consequently, a buffer is not freed
up until all  slaves have finished writing to tape.


c. init.ora
-----------

Each I/O slave is an Oracle server process. The "init.ora" parameters'
processes and sessions need to be set accordingly.
###### test rman ######

RMAN> set maxcorrupt for datafile 6 to 3;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03031: this option of set command needs to be used inside a run block

RMAN> run {
   2>   set maxcorrupt for datafile 6 to 3;
   3>   backup database;
   4> }

executing command: SET MAX CORRUPT

Starting backup at 03-JAN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home/ora10g/oradata/roger/roger01.dbf
input datafile fno=00002 name=/home/ora10g/oradata/roger/undotbs01.dbf
input datafile fno=00006 name=/home/ora10g/oradata/roger/roger02.dbf
input datafile fno=00001 name=/home/ora10g/oradata/roger/system01.dbf
input datafile fno=00003 name=/home/ora10g/oradata/roger/sysaux01.dbf
input datafile fno=00007 name=/home/ora10g/oradata/roger/test1.dbf
input datafile fno=00004 name=/home/ora10g/oradata/roger/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JAN-12
channel ORA_DISK_1: finished piece 1 at 03-JAN-12
piece handle=/home/ora10g/product/10.2/dbs/05mvqjo2_1_1 tag=TAG20120103T063234 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-JAN-12
channel ORA_DISK_1: finished piece 1 at 03-JAN-12
piece handle=/home/ora10g/product/10.2/dbs/06mvqjrc_1_1 tag=TAG20120103T063234 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-JAN-12
SQL> SELECT * FROM v$sgastat WHERE name LIKE '%KSFQ%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  X$KSFQP ANCHOR                     52
shared pool  KSFQ buffer pool                 2376

SQL> SELECT * FROM v$sgastat WHERE name LIKE '%KSFQ%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  X$KSFQP ANCHOR                     52
shared pool  KSFQ buffer pool                 2376

SQL> SELECT device_type, TYPE, buffer_size FROM v$backup_async_io;

DEVICE_TYPE       TYPE      BUFFER_SIZE
----------------- --------- -----------
DISK              AGGREGATE           0
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT         1048576
DISK              OUTPUT        1048576

12 ROWS selected.

SQL> /

DEVICE_TYPE       TYPE      BUFFER_SIZE
----------------- --------- -----------
DISK              AGGREGATE           0
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT         1048576

DEVICE_TYPE       TYPE      BUFFER_SIZE
----------------- --------- -----------
DISK              OUTPUT        1048576

12 ROWS selected.

SQL> /

DEVICE_TYPE       TYPE      BUFFER_SIZE
----------------- --------- -----------
DISK              AGGREGATE           0
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT         1048576
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              OUTPUT        1048576

21 ROWS selected.

SQL> /

DEVICE_TYPE       TYPE      BUFFER_SIZE
----------------- --------- -----------
DISK              AGGREGATE           0
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT         1048576
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              INPUT          524288
DISK              OUTPUT        1048576
DISK              AGGREGATE           0
DISK              INPUT         1048576
DISK              OUTPUT        1048576

24 ROWS selected.
上面的 INPUT 表示每次rman备份时为每个datafile分配的BUFFER_SIZE大小。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库rman备份与large_pool_size的关系详解

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1339.html

Oracle研究中心

关键词:

rman备份与large_pool_size的关系

Oracle large_pool_size