내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.12.0.0 ADG
에러 : Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
19c Active Data Guard 환경에서 브로커 configuration 을 새로 추가할 때 발생하는 메세지
|
1
2
3
4
5
6
7
8
9
10
|
$ dgmgrl sys/oracle@ORAADG
DGMGRL> create configuration 'DR_ORAADG' as primary database is 'ORAADG' connect identifier is 'ORAADG';
Configuration "DR_ORAADG" created with primary database "ORAADG"
create 는 성공
DGMGRL> add database 'ORAADGDR' as connect identifier is 'ORAADGDR';
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
add 는 실패
|
해결 방법 : standby db 파라미터 파일에서 log_archive_dest_2를 제거
pfile 사용중인 경우
db 종료
|
1
|
SQL> shutdown immediate
|
standby db 파라미터 파일 확인
|
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
|
$ cd $ORACLE_HOME/dbs
$ vi initORAADGDR.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADGDR/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADGDR/control01.ctl','/oracle/app/oracle/oradata/ORAADGDR/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGDRXDB)'
*.local_listener='LISTENER_ORAADGDR'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/'
*.db_unique_name='ORAADGDR'
*.db_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.log_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.dg_broker_start=TRUE
*.fal_client='ORAADGDR'
*.fal_server='ORAADG'
*.standby_file_management='auto'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg2 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADGDR'
*.log_archive_dest_2='SERVICE=ORAADG ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
|
log_archive_dest_2 파라미터가 있음
log_archive_dest_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
|
$ cd $ORACLE_HOME/dbs
$ vi initORAADGDR.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADGDR/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADGDR/control01.ctl','/oracle/app/oracle/oradata/ORAADGDR/control02.ctl'
*.db_block_size=8192
*.db_name='ORAADG'
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGDRXDB)'
*.local_listener='LISTENER_ORAADGDR'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/'
*.db_unique_name='ORAADGDR'
*.db_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.log_file_name_convert = '/oracle/app/oracle/oradata/ORAADG/','/oracle/app/oracle/oradata/ORAADGDR/'
*.dg_broker_start=TRUE
*.fal_client='ORAADGDR'
*.fal_server='ORAADG'
*.standby_file_management='auto'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg2 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADGDR'
#*.log_archive_dest_2='SERVICE=ORAADG ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=ORAADG'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
|
주석처리함
db 기동
|
1
|
SQL> startup mount
|
spfile 사용중인 경우
alter 구문으로 변경
|
1
2
3
|
SQL> alter system reset log_archive_dest_2 scope=both;
System altered.
|
configuration add 재시도
정상적으로 실행됨
|
1
2
3
|
$ dgmgrl sys/oracle@ORAADG
DGMGRL> add database 'ORAADGDR' as connect identifier is 'ORAADGDR';
Database "ORAADGDR" added
|
원인 : log_archive_dest_2 파라미터에 값이 들어있어서 발생한 문제
log_archive_dest_2 파라미터를 주석처리하거나 alter system reset 해주면 됨
11gR2 에서 ADG 구성시에는 standby db에 log_archive_dest_2 파라미터가 있어도 잘됬는데
19c에서는 막아놓은듯함
참조 : 1387859.1, 2821530.1
http://www.br8dba.com/tag/reinstate-a-failed-over-data-guard-using-flashback-database/