1

Topic: Whether it is possible in 11gR2 to increase sga without restart ?

After dock like it is possible, if correctly I remember.
But in practice only through spfile and restart turn out... I do not understand!

2

Re: Whether it is possible in 11gR2 to increase sga without restart ?

SGA_MAX_SIZE
It is installed or is explicit, or on set separate _SIZE, or as SGA_TARGET
It is impossible to change upwards

3

Re: Whether it is possible in 11gR2 to increase sga without restart ?

Theoretically in 11g is still MEMORY_TARGET (MEMORY_MAX_SIZE)
There all by the same principle - only can be taken away (THEORETICALLY) more SGA, at the expense of PGA_AGGREGATE_TARGET - simply virtual adjustment

4

Re: Whether it is possible in 11gR2 to increase sga without restart ?

Ljubomudrov wrote:

SGA_MAX_SIZE
It is installed or is explicit, or on set separate _SIZE, or as SGA_TARGET
It is impossible to change upwards

At us on SGA_MAX_SIZE = 10Gb
SGA_TARGET = 8Gb
to lift SGA_TARGET on 10 Gb too (through = both, i.e. without rerestart). Does not go! :-(

5

Re: Whether it is possible in 11gR2 to increase sga without restart ?

and should after dock:

wrote:

ALTER SYSTEM SET SGA_TARGET=value [SCOPE = {SPFILE|MEMORY|BOTH}]

6

Re: Whether it is possible in 11gR2 to increase sga without restart ?

And that means "does not go!"?

7

Re: Whether it is possible in 11gR2 to increase sga without restart ?

DBAshnik wrote:

it is passed...
At us on SGA_MAX_SIZE = 10Gb
SGA_TARGET = 8Gb
to lift SGA_TARGET on 10 Gb too (through = both, i.e. without rerestart). Does not go! :-(

What OS?
+ There should be some adjustments of system
More shortly, particulars are necessary

8

Re: Whether it is possible in 11gR2 to increase sga without restart ?

DBAshnik wrote:

and should after dock:
it is passed...

Should not - Memory Architecture

wrote:

The System Global Area (SGA) and the set of database processes constitute an Oracle Database instance. Oracle Database automatically allocates memory for an SGA when you start an instance , and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

9

Re: Whether it is possible in 11gR2 to increase sga without restart ?

flexgen;
Dynamic Modification of SGA_TARGET

10

Re: Whether it is possible in 11gR2 to increase sga without restart ?

flexgen
Take a little more advanced manual, than Conceps, for example, Administrator's Guide

11

Re: Whether it is possible in 11gR2 to increase sga without restart ?

On some UNIX wrote:

flexgen;
Dynamic Modification of SGA_TARGET

Yes like and on Windows fulfilled

12

Re: Whether it is possible in 11gR2 to increase sga without restart ?

Ljubomudrov wrote:

And that means "does not go!"?

Well attempt  "alter system set sga_target" means lasts hours and is not completed!

13

Re: Whether it is possible in 11gR2 to increase sga without restart ?

Vadim Lejnin wrote:

What OS? + there should be some adjustments of system

AIX Unix. Which?

Vadim Lejnin wrote:

is shorter, particulars

are necessary
Which?

14

Re: Whether it is possible in 11gR2 to increase sga without restart ?

Vadim Lejnin wrote:

What OS?

To be exact:

wrote:

oslevel-s | awk - F - ' {printf "AIX %.1f - Technology Level %d - Service Pack %d\n", $1/1000, $2, $3}'
AIX 7.1 - Technology Level 3 - Service Pack 7

15

Re: Whether it is possible in 11gR2 to increase sga without restart ?

DBAshnik wrote:

it is passed...
Well attempt  "alter system set sga_target" means lasts hours and is not completed!

The answer from Orakl-sapporta on a problem (+ it , etc.) was that:

wrote:

I could not identify any problem.
It appears that you must first shrink the individual SGA memory components prior to altering the sga_target or set the new value with the scope=spfile and restart the database.
Cannot dynamically shrink all memory components by setting sga_target.

16

Re: Whether it is possible in 11gR2 to increase sga without restart ?

DBAshnik wrote:

Cannot dynamically shrink all memory components by setting sga_target.

so you want to reduce or increase? To reduce so simply really it does not turn out

17

Re: Whether it is possible in 11gR2 to increase sga without restart ?

xtender wrote:

so you want to reduce or increase? To reduce so simply really it does not turn out

Like talk went about magnification with 8  to 10 . I, by the way, walked on links that here gave, like all is correct, but in my specific case the magnification sga happens only at reboot . To what it is connected - I do not know, it is possible the same situation as well as at a topic-starter.

18

Re: Whether it is possible in 11gR2 to increase sga without restart ?

If the HARDWARE at first wanted to reduce any of components, operation did not transit also it it interrupted most likely to increase it does not turn out any more, probably, that at first tries to fulfill, , reduction operation.
And reduction by the worked DB sometimes it is impossible to wait. ( Hot change of the size shared pool'. Works-whether? )
Worst of all that the Oracle itself quite often fulfills storage reallocation, and in different modes (IMMEDIATE, DEFERRED) and, probably, is expected, when operation before it starts to be fulfilled MANUAL ends DEFERRED
Well and besides, at once it is selected SGA_MAX_SIZE, and then in it all can be reallocated it (well, not everything, it is finite) inside it. Besides in the same solar oil depending on difference SGA_MAX_SIZE / SGA_TARGET shared storage can be selected with the different method, subject to a swap or not ( Sun ZFS and Oracle 10.2 ) [spoiler]

 hercules.oracle> strings $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora | grep sga
tst. __ sga_target=805306368
*.sga_max_size = [color=red] [b] 1073741824 [/b] [/color]
*.sga_target = [b] 805306368 [/b]
hercules.oracle> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 12 9:02:52 AM 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area [b] 1069252608 [/b] bytes
Fixed Size 2166160 bytes
Variable Size 771756656 bytes
Database Buffers 285212672 bytes
Redo Buffers 10117120 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2166160 No
Redo Buffers 10117120 No
Buffer Cache Size [b] 285212672 [/b] Yes
Shared Pool Size 482344960 Yes
Large Pool Size 12582912 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 4194304 Yes
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 1069252608 No
Startup overhead in Shared Pool 145854976 No
Free SGA Memory Available [b] 268435456 [/b]
12 rows selected.
SQL> sho parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1G
sga_target big integer 768M
SQL> [color=blue] [b] alter system set sga_target=1g scope=memory; [/b] [/color]
System altered.
SQL> sho sga
Total System Global Area [b] 1069252608 [/b] bytes
Fixed Size 2166160 bytes
Variable Size 503321200 bytes
Database Buffers 553648128 bytes
Redo Buffers 10117120 bytes
SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2166160 No
Redo Buffers 10117120 No
Buffer Cache Size [b] 553648128 [/b] Yes
Shared Pool Size 482344960 Yes
Large Pool Size 12582912 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 4194304 Yes
Shared IO Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 1069252608 No
Startup overhead in Shared Pool 145854976 No
Free SGA Memory Available [b] 0 [/b]
12 rows selected.
SQL> column component format a24
SQL> select oper_type, component, oper_mode, initial_size/power (2, 20) "FROM", final_size/power (2, 20) "TO"
2 from v$sga_resize_ops order by start_time;
OPER_TYPE COMPONENT OPER_MODE FROM TO
------------- ------------------------ --------- ---------- ----------
STATIC shared pool 0 460
GROW DEFAULT buffer cache MANUAL 272 528
STATIC java pool 0 4
STATIC streams pool 0 4
STATIC DEFAULT buffer cache 0 272
STATIC KEEP buffer cache 0 0
STATIC RECYCLE buffer cache 0 0
STATIC DEFAULT 2K buffer cache 0 0
STATIC DEFAULT 4K buffer cache 0 0
STATIC DEFAULT 8K buffer cache 0 0
STATIC DEFAULT 16K buffer cache 0 0
STATIC DEFAULT 32K buffer cache 0 0
STATIC ASM Buffer Cache 0 0
INITIALIZING DEFAULT buffer cache 272 272
STATIC large pool 0 12
15 rows selected.
SQL> alter system reset sga_max_size scope=spfile;
System altered.

[/spoiler]

19

Re: Whether it is possible in 11gR2 to increase sga without restart ?

Not, anybody tried to reduce nothing at us...
And here it could be truth:

wrote:

Worst of all that the Oracle itself quite often fulfills storage reallocation, and in different modes (IMMEDIATE, DEFERRED) and, probably, is expected, when operation before it starts to be fulfilled MANUAL ends DEFERRED

but after all it now a horse-radish you will check up, whether not so? ;-)

20

Re: Whether it is possible in 11gR2 to increase sga without restart ?

DBAshnik wrote:

but after all it now a horse-radish you will check up, whether not so? ;-)

V$SGA_RESIZE_OPS?