프린트 하기

OS환경 : Oracle Linux 6.8 (64bit)


DB 환경 : Oracle Database 11.2.0.4 RAC


방법 : 오라클 11g RAC 파라미터 변경 방법

1. spfile 이 각 노드에 존재할경우

spfile 사용 확인

1
2
3
4
5
6
7
8
9
1번 노드
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /app/oracle/product/11.2.0/db_1/dbs/spfileracdb1.ora
 
2번 노드
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /app/oracle/product/11.2.0/db_1/dbs/spfileracdb2.ora

공유 영역이 아닌 각각의 디렉토리에 존재함



변경할 파라미터 목록

1
2
3
4
5
6
7
sga_max_size=3g
sga_target=3g     
db_cache_size=500m
shared_pool_size=1g  
streams_pool_size=256m
pga_aggregate_target=1g
open_cursors=500



기존 값 확인

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
58
59
60
61
62
63
64
65
66
67
1번 노드
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 533M
 
2번 노드
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 533M



1번 노드 파라미터 값 변경

1
2
3
4
5
6
7
8
SQL> 
alter system set sga_max_size=3g scope=spfile;
alter system set sga_target=3g scope=spfile;
alter system set db_cache_size=500m scope=spfile;
alter system set shared_pool_size=1g scope=spfile;
alter system set streams_pool_size=256m scope=spfile;
alter system set pga_aggregate_target=1g scope=spfile;
alter system set open_cursors=500 scope=spfile;

(scope=spfile 재기동 후 적용)



1번 노드 재기동

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startupo 
ORACLE instance started.
 
Total System Global Area 3206836224 bytes
Fixed Size            2257520 bytes
Variable Size         1375735184 bytes
Database Buffers     1811939328 bytes
Redo Buffers           16904192 bytes
Database mounted.
Database opened.



변경된 파라미터 값 확인

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
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 512M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 256M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_cursors                 integer     500

정상적으로 변경됨



2번 노드 파라미터 값 변경

1
2
3
4
5
6
7
8
SQL> 
alter system set sga_max_size=3g scope=spfile;
alter system set sga_target=3g scope=spfile;
alter system set db_cache_size=500m scope=spfile;
alter system set shared_pool_size=1g scope=spfile;
alter system set streams_pool_size=256m scope=spfile;
alter system set pga_aggregate_target=1g scope=spfile;
alter system set open_cursors=500 scope=spfile;

(scope=spfile 재기동 후 적용)



2번 노드 재기동

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startupo 
ORACLE instance started.
 
Total System Global Area 3206836224 bytes
Fixed Size            2257520 bytes
Variable Size         1375735184 bytes
Database Buffers     1811939328 bytes
Redo Buffers           16904192 bytes
Database mounted.
Database opened.



변경된 파라미터 값 확인

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
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 512M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 256M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_cursors                 integer     500

정상적으로 변경됨



2. spfile 이 공유 영역에 존재할경우

spfile 사용 확인

1
2
3
4
5
6
7
8
9
1번 노드
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     +ORADATA/racdb/spfileracdb.ora
 
2번 노드
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     +ORADATA/racdb/spfileracdb.ora

공유 영역에 존재함




변경할 파라미터 목록

1
2
3
4
5
6
7
sga_max_size=3g
sga_target=3g     
db_cache_size=500m
shared_pool_size=1g  
streams_pool_size=256m
pga_aggregate_target=1g
open_cursors=500



기존 값 확인

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
58
59
60
61
62
63
64
65
66
67
1번 노드
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 533M
 
2번 노드
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 1616M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 0
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 533M



파라미터 값 변경(sid = '*'; 로 모든노드 적용)

1
2
3
4
5
6
7
8
SQL> 
alter system set sga_max_size=3g scope=spfile sid = '*';
alter system set sga_target=3g scope=spfile sid = '*';
alter system set db_cache_size=500m scope=spfile sid = '*';
alter system set shared_pool_size=1g scope=spfile sid = '*';
alter system set streams_pool_size=256m scope=spfile sid = '*';
alter system set pga_aggregate_target=1g scope=spfile sid = '*';
alter system set open_cursors=500 scope=spfile sid = '*';

(scope=spfile 재기동 후 적용)



show spparameter로 확인

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
SQL>
show spparameter sga_max_size
show spparameter sga_target
show spparameter db_cache_size
show spparameter shared_pool_size
show spparameter streams_pool_size
show spparameter pga_aggregate_target
show spparameter open_cursors
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     sga_max_size               big integer 3G
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     sga_target               big integer 3G
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     db_cache_size               big integer 500M
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     shared_pool_size           big integer 1G
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     streams_pool_size           big integer 256M
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     pga_aggregate_target           big integer 1G 
 
SID     NAME                   TYPE       VALUE
-------- ----------------------------- ----------- ----------------------------
*     open_cursors               integer       500

1번, 2번노드 동일하게 나옴



1번 노드 재기동

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startupo 
ORACLE instance started.
 
Total System Global Area 3206836224 bytes
Fixed Size            2257520 bytes
Variable Size         1375735184 bytes
Database Buffers     1811939328 bytes
Redo Buffers           16904192 bytes
Database mounted.
Database opened.



1번 노드 변경된 파라미터 값 확인

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
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 512M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 256M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_cursors                 integer     500

정상적으로 변경됨



2번 노드 재기동

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startupo 
ORACLE instance started.
 
Total System Global Area 3206836224 bytes
Fixed Size            2257520 bytes
Variable Size         1375735184 bytes
Database Buffers     1811939328 bytes
Redo Buffers           16904192 bytes
Database mounted.
Database opened.



2번 노드 변경된 파라미터 값 확인

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
SQL> 
show parameter sga_max_size
show parameter sga_target
show parameter db_cache_size
show parameter shared_pool_size
show parameter streams_pool_size
show parameter pga_aggregate_target
show parameter open_cursors
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
sga_target                 big integer 3G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                 big integer 512M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size             big integer 256M
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target             big integer 1G
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
open_cursors                 integer     500

정상적으로 변경됨



spfile 이 공유 영역에 미존재할 경우(각각의 노드에 저장되어 있을 경우)

노드 별로 파라미터 값을 변경 해야함

1
SQL> alter system set ~ scope=spfile;



spfile 이 공유 영역에 존재할 경우 모든 노드 파라미터 값을 한번에 변경 할 수 있음

1
SQL> alter system set ~ scope=spfile sid = '*';



참조 : 

https://docs.oracle.com/database/121/RACAD/GUID-59DEC066-5743-4EDE-9A02-E8896005F658.htm#RACAD017

http://rohitguptaoracletips.blogspot.com/2008/05/how-to-change-spfile-parameters-for-rac.html

Doc. 1498307.1

저작자표시

'ORACLE > Rac' 카테고리의 다른 글

Oracle 10g RAC 운영시 rcp, rsh, rlogin 이 필요한지 여부  (0) 2020.07.16
오라클 11g R2 RAC cssd 프로세스 중지 시 노드 재기동  (0) 2020.07.06
오라클 11g R2 RAC 환경 운영중에 /etc/passwd 파일이 삭제된다면?  (0) 2020.06.19
오라클 11g R2 asm rac 환경에서 ocr 백업 확인 및 백업 하기  (0) 2020.01.04
오라클 11g R2 RAC IP 변경 방법(IP Change)  (0) 2018.12.10

티스토리툴바