내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
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 프로세스 중지 시 노드 재기동 (5) | 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 |