내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
에러 : ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1 - 18446744073709551614)
fra 설정 후 해제 시 발생하는 오류
fra 파라미터 확인(db_recovery_file)
1 2 3 4 5 6 | SQL> show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /home/oracle/fra db_recovery_file_dest_size big integer 2G |
fra 파라미터 변경
1 2 3 4 5 6 | SQL> alter system set db_recovery_file_dest_size=0; alter system set db_recovery_file_dest_size=0 * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1 - 18446744073709551614) |
또는 파라미터 적용후 재기동시 발생하는 오류
1 2 3 4 5 6 7 | SQL> alter system set db_recovery_file_dest_size=0 scope=spfile; System altered. SQL> shutdown immediate SQL> startup ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1 - 18446744073709551614) |
해결 방법 : scope 설정 및 reset 명령 사용
첫번째 명령인 alter system set db_recovery_file_dest_size=0; 에서
에러 메세지가 발생하는 이유는 scope=spfile; 을 입력해주지 않아서임
scope=spfile; 입력 후 재실행
1 2 3 | SQL> alter system set db_recovery_file_dest_size=0 scope=spfile; System altered. |
정상적으로 실행됨
두번째 명령으로 적용 후 재기동시 발생하는 메세지는
db_recovery_file_dest_size 의 값이 1~18446744073709551614 안의 숫자여야하는데 그값을 벗어나서 발생하는 오류임
0이 아닌 rest 명령을 이용해 파라미터를 적용해주어야함
db_recovery_file_dest 과 db_recovery_file_dest_size 모두 reset
1 2 3 4 5 6 7 | SQL> alter system reset db_recovery_file_dest scope=spfile; System altered. SQL> alter system reset db_recovery_file_dest_size scope=spfile; System altered. |
재기동 후 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2254952 bytes Variable Size 1325402008 bytes Database Buffers 805306368 bytes Redo Buffers 4923392 bytes Database mounted. Database opened. SQL> show parameter db_reco NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 |
정상기동됨
파라미터 확인 시
db_recovery_file_dest는 빈칸으로 db_recovery_file_dest_size는 0으로 나옴
db_recovery_file_dest='', db_recovery_file_dest_size=0 적용 후 pfile 생성
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter system set db_recovery_file_dest = '' scope=spfile; System altered. SQL> alter system set db_recovery_file_dest_size = 0 scope=spfile; System altered. SQL> create pfile from spfile; File created. |
pfile 확인
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 | $ cat initORCL11.ora ORCL11.__db_cache_size=805306368 ORCL11.__java_pool_size=16777216 ORCL11.__large_pool_size=16777216 ORCL11.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment ORCL11.__pga_aggregate_target=872415232 ORCL11.__sga_target=1275068416 ORCL11.__shared_io_pool_size=0 ORCL11.__shared_pool_size=419430400 ORCL11.__streams_pool_size=0 *.audit_file_dest='/oracle/app/oracle/admin/ORCL11/adump' *.audit_trail='OS' *.compatible='11.2.0.4.0' *.control_files='/oracle/app/oracle/oradata/ORCL11/control01.ctl','/oracle/app/oracle/oradata/ORCL11/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='ORCL11' *.db_recovery_file_dest='' *.db_recovery_file_dest_size=0 *.diagnostic_dest='/oracle/app/oracle' *.local_listener='' *.log_archive_dest_1='location=/oracle/app/oracle/arch' *.log_archive_dest='' *.log_archive_format='%t%s%r.arc' *.memory_max_target=2147483648 *.memory_target=2147483648 *.open_cursors=300 *.processes=500 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=772 *.undo_tablespace='UNDOTBS1' |
db_recovery_file_dest와 db_recovery_file_dest_size 파라미터가 존재함
reset 명령 후 pfile 생성
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter system reset db_recovery_file_dest scope=spfile; System altered. SQL> alter system reset db_recovery_file_dest_size scope=spfile; System altered. SQL> create pfile from spfile; File created. |
pfile 확인
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 | $ cat initORCL11.ora ORCL11.__db_cache_size=805306368 ORCL11.__java_pool_size=16777216 ORCL11.__large_pool_size=16777216 ORCL11.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment ORCL11.__pga_aggregate_target=872415232 ORCL11.__sga_target=1275068416 ORCL11.__shared_io_pool_size=0 ORCL11.__shared_pool_size=419430400 ORCL11.__streams_pool_size=0 *.audit_file_dest='/oracle/app/oracle/admin/ORCL11/adump' *.audit_trail='OS' *.compatible='11.2.0.4.0' *.control_files='/oracle/app/oracle/oradata/ORCL11/control01.ctl','/oracle/app/oracle/oradata/ORCL11/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='ORCL11' *.diagnostic_dest='/oracle/app/oracle' *.local_listener='' *.log_archive_dest_1='location=/oracle/app/oracle/arch' *.log_archive_dest='' *.log_archive_format='%t%s%r.arc' *.memory_max_target=2147483648 *.memory_target=2147483648 *.open_cursors=300 *.processes=500 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=772 *.undo_tablespace='UNDOTBS1' |
db_recovery_file_dest와 db_recovery_file_dest_size 파라미터가 존재하지 않음
원인 : db_recovery_file_dest_size의 잘못된 값으로 인한 에러
db_recovery_file_dest_size의 잘못된 값으로 인한 에러
alter system reset (파라미터) 명령으로 해결가능함
참조 : https://positivemh.tistory.com/535
https://ocm2017.wordpress.com/2016/06/01/%E9%87%8D%E7%BD%AEdb_recovery_file_dest_size/