Environment: RHEL 6.9、 Oracle 11.2.0.4
When we running some rman backup/restore jobs, we found the actual PGA size could greatly exceed the predefined limit value about PGA:
- Why the following each rman channel process memory allocated were almost all more than the _pga_max_size?
- Why the following total rman channel processes' memory allocated could be far more than the global pga_target?
SQL>
SQL>
SQL> SELECT a.ksppinm AS parameter, c.ksppstvl AS VALUE, a.ksppdesc AS description, b.ksppstdf AS "Default?"
2 FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm IN
3 ('pga_aggregate_target','_pga_max_size','_smm_max_size','_smm_px_max_size') ORDER BY a.ksppinm;
PARAMETER VALUE DESCRIPTION Default?
--------------------- ------------------ -------------------------------------------------- ----------
_pga_max_size 209715200 Maximum size of the PGA memory for one process TRUE
_smm_max_size 102400 maximum work area size in auto mode (serial) TRUE
_smm_px_max_size 524288 maximum work area size in auto mode (global) TRUE
pga_aggregate_target 1073741824 Target size for the aggregate PGA memory consumed FALSE
by the instance
SQL>
SQL> select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", max_size/1024/1024 "MAX_SIZE",
2 user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$memory_dynamic_components where rownum < 100;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE TYPE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- -------------
shared pool 656 528 656 0 GROW
large pool 416 416 416 0 STATIC
java pool 64 64 64 0 STATIC
streams pool 0 0 0 0 STATIC
SGA Target 3072 3072 3072 0 STATIC
DEFAULT buffer cache 1888 1888 1904 0 SHRINK
KEEP buffer cache 0 0 0 0 STATIC
RECYCLE buffer cache 0 0 0 0 STATIC
DEFAULT 2K buffer cache 0 0 0 0 STATIC
DEFAULT 4K buffer cache 0 0 0 0 STATIC
DEFAULT 8K buffer cache 0 0 0 0 STATIC
DEFAULT 16K buffer cache 0 0 0 0 STATIC
DEFAULT 32K buffer cache 0 0 0 0 STATIC
Shared IO Pool 0 0 0 0 STATIC
PGA Target 1024 1024 1024 1024 STATIC
ASM Buffer Cache 0 0 0 0 STATIC
16 rows selected.
SQL>
SQL>
SQL> select sid, spid, s.program, event, pga_used_mem,pga_max_mem from v$process p, v$session s
2 where addr = s.paddr and s.program like '%rman%' order by pga_used_mem desc;
SID SPID PROGRAM EVENT PGA_USED_MEM PGA_MAX_MEM
------- ------------------------ ------------------------------------------------ --------------------------------- ------------ -----------
1123 120921 rman@JCHXRST (TNS V1-V3) Backup: MML read backup piece 542165387 562245814
1057 120917 rman@JCHXRST (TNS V1-V3) Backup: MML read backup piece 542127691 562245814
1024 120915 rman@JCHXRST (TNS V1-V3) Backup: MML read backup piece 541392107 562753046
892 120907 rman@JCHXRST (TNS V1-V3) SQL*Net message from client 106517430 113455286
1090 120919 rman@JCHXRST (TNS V1-V3) Backup: MML restore backup piece 21407539 24588470
958 120914 rman@JCHXRST (TNS V1-V3) SQL*Net message from client 980323 1913014
6 rows selected.
SQL>
SQL>
Updated:
It's just for the RMAN restore job !
And we just use the customized RMAN script with the default RMAN configuration:
RMAN> show all;
RMAN configuration parameters for database with db_unique_name SGPMDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+SGPMDB_ARC/snapcf_sgpmdb.f';
RMAN>
rman target / <<EOF
run {
allocate channel ch00 type 'sbt_tape';
allocate channel ch01 type 'sbt_tape';
allocate channel ch02 type 'sbt_tape';
allocate channel ch03 type 'sbt_tape';
SEND 'NB_ORA_SERV=jchxbak,NB_ORA_CLIENT=scyxdb3';
set until time "to_date('2020-08-22 15:12:58','YYYY-MM-DD HH24:MI:SS')";
SET NEWNAME FOR DATABASE to '+data/%U';
restore database ;
switch datafile all;
switch tempfile all;
release channel ch00;
release channel ch01;
release channel ch02;
release channel ch03;
}
exit;
Best Answer
== Partial answer as question require more context ==
== As i'm new here, i can't put that in the comments, so if reviewers could right the things ==
As it's Oracle 11, encryption cause can be casted out.
For point 1 & 2 could you precise if you're doing inconsistent backups (archive log, redo log, etc.) or consistent backups (cold backup, etc.) ?
As it concerns the PGA, that could be the result of your RMAN config (RMAN uses PLSQL package for backup and recover), i suggest to tune your buffers sizes and / or multiplexing level.
Update
Seeing that you've the default config, that means that your restore target disks (Tape backups / restores are more prone to target PGA). With disks, PGA load could come from Synchronous I/O with a high
DBWR_IO_SLAVES
. I suggest that you check these in your database config. IfDBWR_IO_SLAVES
is zero and you have asynchronous I/O, i suggest to reduce buffers size.