내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4 Active Data Guard
방법 : Oracle Linux 6.8에 Oracle 11g R2 ADG(Active Data Guard) 설치 가이드
OS 설치는 아래 게시물 참조
Oracle Linux 6.8 설치 가이드
https://positivemh.tistory.com/678
OS 설치 후 아래와 같이 hostname 변경해야함
1번 노드(Primary) hostname : adg1, db_name : adg11g, db_unique_name : adg11g
2번 노드(Standby) hostname : adg2, db_name : adg11g, db_unique_name : adg11gsb
OS 설정
OS 기동 후
/etc/sysconfig/network 확인
1 2 3 4 5 6 7 8 9 | 1번 노드 # cat /etc/sysconfig/network NETWORKING=yes HOSTNAME=adg1 2번 노드 # cat /etc/sysconfig/network NETWORKING=yes HOSTNAME=adg2 |
기존에 다른 hostname이라면 hostname 변경 후 재기동
1 | # reboot |
Network 설정
1 2 3 4 | IP/Netmask/GW 192.168.137.50/255.255.255.0/192.168.137.2 --1번 노드 192.168.137.51/255.255.255.0/192.168.137.2 --2번 노드 DNS 168.126.63.1 |
/etc/hosts 설정, 아래 내용 추가 후 저장
1 2 3 4 5 6 7 8 9 | 1번 노드 # vi /etc/hosts 192.168.137.50 adg1 192.168.137.51 adg2 2번 노드 # vi /etc/hosts 192.168.137.51 adg2 192.168.137.50 adg1 |
오라클 설치 전 사전 설정
자동 설정(1, 2번 노드 모두)
1 | # yum install oracle-rdbms-server-11gR2-preinstall -y |
자동 설정 후 수동설정 파일 확인필요(1, 2번 노드 모두)
/etc/sysctl.conf에 아래 내용 추가
1 2 3 4 5 6 7 8 9 10 11 12 13 | # vi /etc/sysctl.conf fs.suid_dumpable = 1 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 |
잘입력되었는지 확인(1, 2번 노드 모두)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # /sbin/sysctl -p net.ipv4.ip_forward = 0 net.ipv4.conf.default.accept_source_route = 0 kernel.sysrq = 0 kernel.core_uses_pid = 1 net.ipv4.tcp_syncookies = 1 kernel.msgmnb = 65536 kernel.msgmax = 65536 fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.shmall = 4294967296 kernel.shmmax = 4398046511104 kernel.panic_on_oops = 1 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 net.ipv4.conf.all.rp_filter = 2 net.ipv4.conf.default.rp_filter = 2 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 |
아래 파일 내용 추가(1, 2번 노드 모두)
1 2 3 4 5 6 7 8 9 | # vi /etc/security/limits.conf oracle soft nofile 1024 oracle hard nofile 65536 oracle soft nproc 16384 oracle hard nproc 16384 oracle soft stack 10240 oracle hard stack 32768 oracle hard memlock 134217728 oracle soft memlock 134217728 |
유저 및 그룹 생성(수동 생성시)(1, 2번 노드 모두)
1 2 | # groupadd dba # useradd -g dba -G dba oracle |
유저 및 그룹 수정(자동 생성시)(1, 2번 노드 모두)
1 | # usermod -g dba -G dba oracle |
패스워드 설정(1, 2번 노드 모두)
1 | # passwd oracle |
selinux disable 설정(1, 2번 노드 모두)
1 2 | # vi /etc/selinux/config SELINUX=disabled |
불필요한 서비스 중지(1, 2번 노드 모두)
1 2 3 4 5 6 7 8 | # chkconfig --level 123456 xinetd off chkconfig --level 123456 sendmail off chkconfig --level 123456 cups off chkconfig --level 123456 cups-config-daemon off chkconfig --level 123456 smartd off chkconfig --level 123456 isdn off chkconfig --level 123456 iptables off |
오라클 설치용 폴더 생성 및 권한 부여(1, 2번 노드 모두)
1 2 3 | # mkdir -p /app/oracle /app/media # chown -R oracle:dba /app # chmod -R 755 /app/ |
/app/media 경로에 설치파일 업로드(1, 2번 노드 모두)
1 2 3 4 5 6 | # ls -al /app/media/ total 2487216 drwxr-xr-x 2 oracle dba 4096 Dec 13 08:53 . drwxrwxr-x. 5 oracle dba 4096 Dec 13 08:53 .. -rw-r--r-- 1 oracle dba 1395582860 Dec 13 08:54 p13390677_112040_Linux-x86-64_1of7.zip -rw-r--r-- 1 oracle dba 1151304589 Dec 13 08:54 p13390677_112040_Linux-x86-64_2of7.zip |
오라클 계정 접속 후 bash_profile 설정(1, 2번 노드 모두)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 1번 노드 $ vi .bash_profile export TMP=/tmp; export TMPDIR=$TMP; export ORACLE_BASE=/app/oracle; export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_SID=adg11g; export PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin:$PATH; export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export DISPLAY=192.168.137.1:0.0; 2번 노드 $ vi .bash_profile export TMP=/tmp; export TMPDIR=$TMP; export ORACLE_BASE=/app/oracle; export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_SID=adgsb; export PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin:$PATH; export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export DISPLAY=192.168.137.1:0.0; |
설정 저장(1, 2번 노드 모두)
1 | $ . ./.bash_profile |
db 소프트웨어(엔진) 설치
설치파일 압축 해제(1, 2번 노드 모두)
1 2 3 | $ cd /app/media $ unzip p13390677_112040_Linux-x86-64_1of7.zip $ unzip p13390677_112040_Linux-x86-64_2of7.zip |
runInstaller 실행(1 노드 쉘까지 모두 완료 후, 2번 노드도 실행)
1 2 3 4 5 6 7 8 | $ cd database/ $ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 120 MB. Actual 11333 MB Passed Checking swap space: must be greater than 150 MB. Actual 9999 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-12-14_06-47-27AM. Please wait ... |
I wish to 체크 해제
Yes 선택
Skip software updates 선택
Install database software only 선택
Single instance database installation 선택
언어 선택
Enterprise Edition 선택
Oracle base, Oracle home 지정
oraInventory 지정
권한 지정
사전 요구사항 체크중
Install 선택
runInstaller 마지막에 나온 쉘 2개 복사
runInstaller 마지막에 나온 쉘 2개 새로운 세션 열어서 root 계정으로 실행
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 | # /app/oraInventory/orainstRoot.sh Changing permissions of /app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /app/oraInventory to dba. The execution of the script is complete. # /app/oracle/product/11.2.0/db_1/root.sh Performing root user operation for Oracle 11g The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /app/oracle/product/11.2.0/db_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: [엔터 입력] Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Finished product-specific root actions. |
쉘 실행 후 OK
Close 선택 후 종료
리스너 생성
netca 실행
1 | $ netca |
Listener configuration 선택
Add 선택
리스너 이름 지정
프로토콜 지정
포트 지정
No 선택
Next 선택
Finish 선택
리스너 종료
1 2 3 4 5 6 7 8 | $ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-DEC-2020 22:52:27 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adg1)(PORT=1521))) The command completed successfully |
db 생성
db 생성(1번 노드)
1 | $ dbca |
Next 선택
Create Database 선택
General Purpose 선택
sid 지정
EM 체크 해제
AMT 체크해제
패스워드 설정
YES 선택
File System 선택
FRA, 아카이브 모두 체크 해제
샘플 스키마 체크해제
메모리 설정 후 Character Set 선택
KO16MSWIN949 선택
Next 선택
Create Database 선택
OK 선택
DB 생성중
생성 완료
DB 확인
1 2 3 4 5 | SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ adg11g OPEN |
정상적으로 생성됨
pfile 생성 후 db 종료(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 | SQL> create pfile from spfile; File created. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit $ cd $ORACLE_HOME/dbs/ $ mv spfileadg11g.ora spfileadg11g.orabak |
데이터 가드 구성 설정
아카이브 경로 생성(1번, 2번 노드)
1 2 3 4 5 | 1번 노드 $ mkdir -p /app/oracle/arch/adg1 2번 노드 $ mkdir -p /app/oracle/arch/adg2 |
adump, oradata 경로 생성(2번 노드)
1 2 | $ mkdir -p /app/oracle/admin/adg11gsb/adump $ mkdir -p /app/oracle/oradata/adg11gsb |
pfile 확인(1번 노드)
1 2 3 4 | $ cd $ORACLE_HOME/dbs $ ls hc_adg11g.dat initadg11g.ora init.ora lkADG11G orapwadg11g spfileadg11g.orabak $ vi initadg11g.ora |
pfile 수정(1번 노드)
기존
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | adg11g.__db_cache_size=687865856 adg11g.__java_pool_size=16777216 adg11g.__large_pool_size=33554432 adg11g.__oracle_base='/app/oracle'#ORACLE_BASE set from environment adg11g.__pga_aggregate_target=671088640 adg11g.__sga_target=989855744 adg11g.__shared_io_pool_size=0 adg11g.__shared_pool_size=234881024 adg11g.__streams_pool_size=0 *.audit_file_dest='/app/oracle/admin/adg11g/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/app/oracle/oradata/adg11g/control01.ctl','/app/oracle/oradata/adg11g/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='adg11g' *.diagnostic_dest='/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=adg11gXDB)' *.memory_target=1652555776 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' |
수정(2번, 13번 16~28번째 줄)
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 | *.audit_file_dest='/app/oracle/admin/adg11g/adump' *.audit_trail='none' *.compatible='11.2.0.4.0' *.control_files='/app/oracle/oradata/adg11g/control01.ctl','/app/oracle/oradata/adg11g/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='adg11g' *.diagnostic_dest='/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=adg11gXDB)' *.memory_target=1652555776 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='shared' *.undo_tablespace='UNDOTBS1' *.db_unique_name='adg11g' *.dg_broker_start=TRUE *.fal_client='adg11g' *.fal_server='adg11gsb' *.standby_file_management='auto' *.instance_name='adg11g' *.log_archive_config='DG_CONFIG=(adg11g,adg11gsb)' *.log_archive_dest_1='LOCATION=/app/oracle/arch/adg11g valid_for=(all_logfiles, all_roles) db_unique_name=adg11g' *.log_archive_dest_2='SERVICE=adg11gsb ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=adg11gsb' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=4 |
리스너 파일 수정(1번 노드)
기존
1 2 3 4 5 6 7 8 9 10 11 | $ cd $ORACLE_HOME/network/admin $ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = adg1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /app/oracle |
수정
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ cd $ORACLE_HOME/network/admin $ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = adg1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = adg11g) (ORACLE_HOME = /app/oracle/product/11.2.0/db_1) (SID_NAME = adg11g) ) ) ADR_BASE_LISTENER = /app/oracle |
tnsnames.ora 파일 생성(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $ cd $ORACLE_HOME/network/admin $ vi tnsnames.ora adg11g = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = adg1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = adg11g) ) ) adg11gsb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = adg2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = adg11gsb) ) ) |
db 기동(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SQL> startup mount ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1006636072 bytes Database Buffers 637534208 bytes Redo Buffers 7094272 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. |
기동 후 force logging 모드 실행(강제 로깅 기능)(1번 노드)
1 2 3 | SQL> alter database force logging; Database altered. |
1번 노드 pfile을 2번 노드로 복사(1번 노드)
1 2 3 4 5 6 7 8 | $ cd $ORACLE_HOME/dbs $ scp initadg11g.ora adg2:/app/oracle/product/11.2.0/db_1/dbs/initadg11gsb.ora The authenticity of host 'adg2 (192.168.137.51)' can't be established. RSA key fingerprint is 80:6c:80:be:1b:5f:b5:9c:cd:fb:02:40:ad:15:e0:5c. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'adg2,192.168.137.51' (RSA) to the list of known hosts. oracle@adg2's password: initadg11gsb.ora 100% 1034 1.0KB/s 00:00 |
pfile 수정(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 | $ cd $ORACLE_HOME/dbs $ vi initadg11gsb.ora *.audit_file_dest='/app/oracle/admin/adg11gsb/adump' *.audit_trail='none' *.compatible='11.2.0.4.0' *.control_files='/app/oracle/oradata/adg11gsb/control01.ctl','/app/oracle/oradata/adg11gsb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='adg11g' *.diagnostic_dest='/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=adg11gsbXDB)' *.memory_target=1652555776 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='shared' *.undo_tablespace='UNDOTBS1' *.db_unique_name='adg11gsb' *.db_file_name_convert = 'adg11g','adg11gsb' *.log_file_name_convert = 'adg11g','adg11gsb' *.dg_broker_start=TRUE *.fal_client='adg11gsb' *.fal_server='adg11g' *.standby_file_management='auto' *.instance_name='adg11g' *.log_archive_config='DG_CONFIG=(adg11g,adg11gsb)' *.log_archive_dest_1='LOCATION=/app/oracle/arch/adg11gsb valid_for=(all_logfiles, all_roles) db_unique_name=adg11gsb' *.log_archive_dest_2='SERVICE=adg11g ASYNC valid_for=(online_logfiles, primary_role) db_unique_name=adg11g' *.log_archive_dest_state_1='ENABLE' *.log_archive_dest_state_2='ENABLE' *.log_archive_format='%t_%s_%r.arc' *.log_archive_max_processes=4 |
리스너 파일 생성(2번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $ cd $ORACLE_HOME/network/admin $ vi listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = adg2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = adg11gsb) (ORACLE_HOME = /app/oracle/product/11.2.0/db_1) (SID_NAME = adg11gsb) ) ) |
tnsnames.ora 파일 생성(2번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $ cd $ORACLE_HOME/network/admin $ vi tnsnames.ora adg11g = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = adg1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = adg11g) ) ) adg11gsb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = adg2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = adg11gsb) ) ) |
nomount로 기동(2번 노드)
1 2 3 4 5 6 7 8 | SQL> startup nomount ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1006636072 bytes Database Buffers 637534208 bytes Redo Buffers 7094272 bytes |
리스너 기동(1번, 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 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 68 69 | 1번 노드 $ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-DEC-2020 02:44:31 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /app/oracle/diag/tnslsnr/adg1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adg1)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adg1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 15-DEC-2020 02:44:31 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /app/oracle/diag/tnslsnr/adg1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adg1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "adg11g" has 1 instance(s). Instance "adg11g", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully 2번 노드 $ lsnrctl start LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 15-DEC-2020 02:43:18 Copyright (c) 1991, 2013, Oracle. All rights reserved. Starting /app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /app/oracle/product/11.2.0/db_1/network/admin/listener.ora Log messages written to /app/oracle/diag/tnslsnr/adg2/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adg2)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adg2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 15-DEC-2020 02:43:18 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /app/oracle/diag/tnslsnr/adg2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adg2)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "adg11gsb" has 1 instance(s). Instance "adg11g", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
패스워드 파일 복제(1번 노드)
1 | $ scp orapwadg11g adg2:/app/oracle/product/11.2.0/db_1/dbs/orapwadg11gsb |
접속 테스트(1번 노드에서 2번 노드로, 2번 노드에서 1번 노드로)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 1번 노드 $ sqlplus sys/oracle@adg11gsb as sysdba SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ adg11gsb STARTED 2번 노드 $ sqlplus sys/oracle@adg11g as sysdba SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ adg11g OPEN |
rman 백업 경로 생성(1번 노드)
1 | $ mkdir -p /app/oracle/rman |
rman 백업(1번 노드)
1 2 3 4 5 | $ rman target / RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/app/oracle/rman/%F'; RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/app/oracle/rman/%d_%U_%T.bk'; RMAN> backup database plus archivelog; |
rman 백업본 2번 노드로 전송(1번 노드)
1 2 | $ cd /app/oracle/ $ scp -r rman/ adg2:/app/oracle/ |
Standby용 controlfile 생성 후 2번 노드로 전송(1번 노드)
1 2 3 4 5 6 7 | SQL> alter database create standby controlfile as '/home/oracle/adg11gsb.ctl'; Database altered. $ cd /home/oracle $ scp adg11gsb.ctl adg2:/home/oracle/ $ scp adg11gsb.ctl adg2:/app/oracle/oradata/adg11gsb/control01.ctl $ scp adg11gsb.ctl adg2:/app/oracle/oradata/adg11gsb/control02.ctl |
컨트롤파일을 scp로 /home/oracle/ 그리고 oradata 경로에 3개 복사해줬는데
/home/oracle에 복사를 하지 않으면 rman duplicate 시 중간에 오류가 발생함
하지만 duplicate 자체가 실패하진 않음
oradata 경로에 복사하지 않는다면 아래 rman auxiliary 로 접속을 하지 못함
(대상지에 rman 정보가 저장된 컨트롤파일이 없기 때문)
Standby DB를 Primary DB에서 복원
(duplicate는 반드시 Standby가 nomount 상태여야 진행됨)
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 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | $ rman target / auxiliary sys/oracle@adg11gsb RMAN> duplicate target database for standby; ---로그--- Starting Duplicate Db at 15-DEC-20 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=20 device type=DISK contents of Memory Script: { restore clone standby controlfile; } executing Memory Script Starting restore at 15-DEC-20 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: restoring control file channel ORA_AUX_DISK_1: copied control file copy input file name=/home/oracle/adg11gsb.ctl output file name=/app/oracle/oradata/adg11gsb/control01.ctl output file name=/app/oracle/oradata/adg11gsb/control02.ctl Finished restore at 15-DEC-20 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/app/oracle/oradata/adg11gsb/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/app/oracle/oradata/adg11gsb/system01.dbf"; set newname for datafile 2 to "/app/oracle/oradata/adg11gsb/sysaux01.dbf"; set newname for datafile 3 to "/app/oracle/oradata/adg11gsb/undotbs01.dbf"; set newname for datafile 4 to "/app/oracle/oradata/adg11gsb/users01.dbf"; restore clone database ; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /app/oracle/oradata/adg11gsb/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 15-DEC-20 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backup set restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set channel ORA_AUX_DISK_1: restoring datafile 00001 to /app/oracle/oradata/adg11gsb/system01.dbf channel ORA_AUX_DISK_1: restoring datafile 00002 to /app/oracle/oradata/adg11gsb/sysaux01.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /app/oracle/oradata/adg11gsb/undotbs01.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /app/oracle/oradata/adg11gsb/users01.dbf channel ORA_AUX_DISK_1: reading from backup piece /app/oracle/rman/ADG11G_0avi3ulp_1_1_20201215.bk channel ORA_AUX_DISK_1: piece handle=/app/oracle/rman/ADG11G_0avi3ulp_1_1_20201215.bk tag=TAG20201215T035121 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 15-DEC-20 contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=1059191807 file name=/app/oracle/oradata/adg11gsb/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=5 STAMP=1059191807 file name=/app/oracle/oradata/adg11gsb/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=6 STAMP=1059191807 file name=/app/oracle/oradata/adg11gsb/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=7 STAMP=1059191807 file name=/app/oracle/oradata/adg11gsb/users01.dbf Finished Duplicate Db at 15-DEC-20 ---로그--- |
db 상태 확인(2번 노드)
1 2 3 4 5 | SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ adg11gsb MOUNTED |
duplicate 완료 후에는 2번 노드가 mount 상태가됨
db role 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | 1번 노드 SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE 2번 노드 SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED |
1번 노드 redo log 사이즈 확인
1 2 3 4 5 6 7 | SQL> select group#, bytes/1024/1024 mb from v$log; GROUP# MB ---------- ---------- 1 50 2 50 3 50 |
리두 로그파일 당 50MB 임
Standby DB에서 Standby log 파일을 등록(2번 노드)
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter database add standby logfile '/app/oracle/oradata/adg11gsb/standby_log01.log' size 50m; Database altered. SQL> alter database add standby logfile '/app/oracle/oradata/adg11gsb/standby_log02.log' size 50m; Database altered. SQL> alter database add standby logfile '/app/oracle/oradata/adg11gsb/standby_log03.log' size 50m; Database altered. |
Primary DB에서 Standby log 파일을 등록(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 | SQL> alter database add standby logfile '/app/oracle/oradata/adg11g/standby_log01.log' size 50m; Database altered. SQL> alter database add standby logfile '/app/oracle/oradata/adg11g/standby_log02.log' size 50m; Database altered. SQL> alter database add standby logfile '/app/oracle/oradata/adg11g/standby_log03.log' size 50m; Database altered. |
Redo log 확인
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 | 1번 노드 SQL> set lines 200 pages 1000 SQL> col member for a80 SQL> select * from v$logfile order by type, group#; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ---------------------------------------------------- --- 1 ONLINE /app/oracle/oradata/adg11g/redo01.log NO 2 ONLINE /app/oracle/oradata/adg11g/redo02.log NO 3 ONLINE /app/oracle/oradata/adg11g/redo03.log NO 4 STANDBY /app/oracle/oradata/adg11g/standby_log01.log NO 5 STANDBY /app/oracle/oradata/adg11g/standby_log02.log NO 6 STANDBY /app/oracle/oradata/adg11g/standby_log03.log NO 6 rows selected. 2번 노드 SQL> set lines 200 pages 1000 SQL> col member for a80 SQL> select * from v$logfile order by type, group#; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------- --- 1 ONLINE /app/oracle/oradata/adg11gsb/redo01.log NO 2 ONLINE /app/oracle/oradata/adg11gsb/redo02.log NO 3 ONLINE /app/oracle/oradata/adg11gsb/redo03.log NO 4 STANDBY /app/oracle/oradata/adg11gsb/standby_log01.log NO 5 STANDBY /app/oracle/oradata/adg11gsb/standby_log02.log NO 6 STANDBY /app/oracle/oradata/adg11gsb/standby_log03.log NO 6 rows selected. |
파라미터 파일 변경
현재 파라미터 파일 확인(1, 2번 노드)
1 2 3 4 5 | SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string |
spfile에 아무런 값이 없으므로 pfile 을 사용중인것
spfile 생성(1, 2번 노드)
1 2 3 4 5 6 7 8 | 1번 노드 SQL> create spfile from pfile; File created. 2번 노드 SQL> create spfile from pfile; File created. |
양쪽 노드 모두 재기동(1, 2번 노드)
1 2 3 4 5 6 7 | 1번 노드 SQL> shutdown immediate SQL> startup 2번 노드 SQL> shutdown immediate SQL> startup mount |
파라미터 파일 확인(1, 2번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 1번 노드 SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /app/oracle/product/11.2.0/db_ 1/dbs/spfileadg11g.ora 2번 노드 SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /app/oracle/product/11.2.0/db_ 1/dbs/spfileadg11gsb.ora |
정상적으로 spfile 사용중임
*아래 내용부터 데이터 가드 구성 후 고쳐쓴 부분이 많아
alert log에 있는 redo log sequence 의 번호는 맞지 않을 수 있음
sequence 의 번호는 무시하고 해당 작업시 어떤 로그가 발생하는지 정도만 확인하면 됨
configuration 설정
데이터가드 브로커 설정(configuration 추가)
1번 노드 configuration 생성(1번 노드)
1 2 3 4 5 | $ dgmgrl sys/oracle DGMGRL> create configuration 'adg11g' as > primary database is 'adg11g' > connect identifier is 'adg11g'; Configuration "adg11g" created with primary database "adg11g" |
2번 노드 추가(1번 노드)
1 2 3 | DGMGRL> add database 'adg11gsb' as > connect identifier is 'adg11gsb'; Database "adg11gsb" added |
configuration 확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 | DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11g - Primary database adg11gsb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: DISABLED |
Configuration Status 가 disable상태임
configuration 활성화 후 확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DGMGRL> enable configuration Enabled. DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11g - Primary database adg11gsb - Physical standby database Warning: ORA-16792: configurable property value is inconsistent with database setting Fast-Start Failover: DISABLED Configuration Status: WARNING |
에러가 발생함
에러 원인 확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | DGMGRL> show instance verbose adg11gsb Instance 'adg11gsb' of database 'adg11gsb' Host Name: adg2 PFILE: Properties: SidName = 'adg11gsb' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=adg11gsb_DGMGRL)(INSTANCE_NAME=adg11g)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/app/oracle/arch/adg11gsb' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.arc' TopWaitEvents = '(monitor)' Instance Warning(s): ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting Instance Status: WARNING |
해당 db(standby db) InconsistentProperties 조회(1번 노드)
1 2 3 4 5 | DGMGRL> show database 'adg11gsb' InconsistentProperties INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE adg11gsb ArchiveLagTarget 0 0 adg11gsb LogArchiveMinSucceedDest 1 1 |
MEMORY_VALUE, BROKER_VALUE 는 같지만 SPFILE_VALUE 값이 나오지 않음
위 값에 맞게 spfile 수정(2번 노드)
1 2 3 4 5 6 7 | SQL> alter system set archive_lag_target=0 scope=both; System altered. SQL> alter system set log_archive_min_succeed_dest=1 scope=both; System altered. |
1번 노드에서 재확인
1 2 3 | DGMGRL> show database 'adg11gsb' InconsistentProperties INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE |
더이상 내용이 나오지 않음(MEMORY_VALUE, SPFILE_VALUE, BROKER_VALUE 가 모두 동일한 값이기 때문)
* InconsistentProperties 속성은 Oracle Database 19c부터 더 이상 사용되지 않으며 향후 릴리스에서 지원되지 않을 수 있음
instance verbose 재확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | DGMGRL> show instance verbose adg11gsb Instance 'adg11gsb' of database 'adg11gsb' Host Name: adg2 PFILE: Properties: SidName = 'adg11gsb' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=adg2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=adg11gsb_DGMGRL)(INSTANCE_NAME=adg11g)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/app/oracle/arch/adg11gsb' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.arc' TopWaitEvents = '(monitor)' Instance Status: SUCCESS |
SUCCESS 로 표시됨
show configuration 재확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ dgmgrl sys/oracle DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11g - Primary database adg11gsb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
SUCCESS 로 표시됨
로그스위치 확인
1번, 2번 노드 모두 alert log tail 로 확인
1 2 3 4 5 | 1번 노드 $ tail -f /app/oracle/diag/rdbms/adg11g/adg11g/trace/alert_adg11g.log 2번 노드 $ tail -f /app/oracle/diag/rdbms/adg11gsb/adg11gsb/trace/alert_adg11gsb.log |
1번 노드에서 로그 스위치 실행(1번 노드)
1 2 3 | SQL> alter system switch logfile; System altered. |
1번, 2번 노드 모두 alert log tail 로 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 1번 노드 $ tail -f /app/oracle/diag/rdbms/adg11g/adg11g/trace/alert_adg11g.log Fri Dec 18 01:02:32 2020 Thread 1 advanced to log sequence 41 (LGWR switch) Current log# 2 seq# 41 mem# 0: /app/oracle/oradata/adg11g/redo02.log Fri Dec 18 01:02:32 2020 Archived Log entry 58 added for thread 1 sequence 40 ID 0xecff1e6e dest 1: Fri Dec 18 01:02:32 2020 LNS: Standby redo logfile selected for thread 1 sequence 41 for destination LOG_ARCHIVE_DEST_2 2번 노드 $ tail -f /app/oracle/diag/rdbms/adg11gsb/adg11gsb/trace/alert_adg11gsb.log Fri Dec 18 01:02:32 2020 RFS[4]: Selected log 5 for thread 1 sequence 41 dbid -318785682 branch 1059169456 Fri Dec 18 01:02:32 2020 Archived Log entry 22 added for thread 1 sequence 40 ID 0xecff1e6e dest 1: |
1번 노드에서 로그 스위치 시 1, 2번 노드 alert에 해당 내용이 남겨짐
Oracle Active Data Guard 구성 완료
추가 내용
추가1. 현재 recovery mode 확인 및 정지(IDLE)
v$archive_dest_status 뷰를 통해 확인가능(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> col error for a30 select dest_id, status, database_mode, recovery_mode, error, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------- ------------------- -------------------------- --------------- -------- 1 LOCAL VALID OPEN IDLE adg11g 2 PHYSICAL VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY adg11gsb NO GAP |
현재 2번 노드는 mount 상태이며 recovery mode가 real time apply mode 임
위 configuration 설정 중 enable configuration 시
alter database recover managed standby database through all switchover disconnect using current logfile;
명령으로 real time apply mode 로 변경됨
이상태에서는 2번 노드를 read only mode로 open 도 가능함
2번 노드 recovery mode IDLE 상태로 변경(2번 노드)
1 2 3 | SQL> alter database recover managed standby database cancel; Database altered. |
현재 recovery mode 확인(변경 후 v$archive_dest_status 뷰가 업데이트 되기 까지 몇초의 딜레이가 있음)(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> select dest_id, type, status, database_mode, recovery_mode, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------------- ------------------------- -------------- ------- 1 LOCAL VALID OPEN IDLE adg11g 2 PHYSICAL VALID MOUNTED-STANDBY IDLE adg11gsb NO GAP |
recovery mode가 IDLE로 변경됨
show configuration 확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $ dgmgrl sys/oracle DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11g - Primary database adg11gsb - Physical standby database Error: ORA-16766: Redo Apply is stopped Fast-Start Failover: DISABLED Configuration Status: ERROR |
Redo apply 가 중단 되었다고 나오며 configuration status 가 error로 나옴
추가2. 백그라운드 프로세스 확인 및 recovey mode 변경
현재 사용중인 백그라운드 프로세스 확인
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 | 1번 노드 SQL> set lines 200 pages 1000 SQL> select client_process,process,status,sequence# from v$managed_standby; CLIENT_P PROCESS STATUS SEQUENCE# -------- --------- ------------ ---------- ARCH ARCH CLOSING 98 ARCH ARCH CONNECTED 0 ARCH ARCH CLOSING 96 ARCH ARCH CLOSING 98 LNS LNS WRITING 99 5 rows selected. 2번 노드 SQL> set lines 200 pages 1000 SQL> select client_process,process,status,sequence# from v$managed_standby; CLIENT_P PROCESS STATUS SEQUENCE# -------- --------- ------------ ---------- ARCH ARCH CONNECTED 0 ARCH ARCH CONNECTED 0 ARCH ARCH CONNECTED 0 ARCH ARCH CLOSING 98 ARCH RFS IDLE 0 UNKNOWN RFS IDLE 0 LGWR RFS IDLE 99 7 rows selected. |
log_archive_max_processes 파라미터가 4으로 설정되어 있기 때문에 ARCH 프로세스가 4개 나옴(1번, 2번 노드)
위에서 로그스위치를 했기 때문에 LNS 프로세스가 동작중임(1번 노드)
다시 recovery mode 를 real time apply mode 로 변경(2번 노드)
1 2 | SQL> recover managed standby database using current logfile disconnect from session; Media recovery complete. |
recovery mode 확인(변경 후 v$archive_dest_status 뷰가 업데이트 되기 까지 몇초의 딜레이가 있음)(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> col error for a30 select dest_id, status, database_mode, recovery_mode, error, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------- ------------------- -------------------------- --------------- -------- 1 LOCAL VALID OPEN IDLE adg11g 2 PHYSICAL VALID MOUNTED-STANDBY MANAGED REAL TIME APPLY adg11gsb NO GAP |
real time apply mode 로 변경됨
현재 사용중인 백그라운드 프로세스 확인
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 | 1번 노드 SQL> set lines 200 pages 1000 SQL> select client_process,process,status,sequence# from v$managed_standby; CLIENT_P PROCESS STATUS SEQUENCE# -------- --------- ------------ ---------- ARCH ARCH CLOSING 98 ARCH ARCH CONNECTED 0 ARCH ARCH CLOSING 96 ARCH ARCH CLOSING 98 LNS LNS WRITING 99 5 rows selected. 2번 노드 SQL> set lines 200 pages 1000 SQL> select client_process,process,status,sequence# from v$managed_standby; CLIENT_P PROCESS STATUS SEQUENCE# -------- --------- ------------ ---------- ARCH ARCH CONNECTED 0 ARCH ARCH CONNECTED 0 ARCH ARCH CONNECTED 0 ARCH ARCH CLOSING 98 ARCH RFS IDLE 0 UNKNOWN RFS IDLE 0 LGWR RFS IDLE 99 N/A MRP0 APPLYING_LOG 99 8 rows selected. |
real time apply mode 이기 때문에 MRP 프로세스가 기동됨
read only mode로 db open(2번 노드)
1 2 3 | SQL> alter database open read only; Database altered. |
현재 recovery mode 확인(변경 후 v$archive_dest_status 뷰가 업데이트 되기 까지 몇초의 딜레이가 있음)(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> select dest_id, type, status, database_mode, recovery_mode, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------------- ------------------------- -------------- ------- 1 LOCAL VALID OPEN IDLE adg11g 2 PHYSICAL VALID OPEN_READ-ONLY IDLE adg11gsb NO GAP |
database mode가 OPEN_READ-ONLY로 변경됨(2번 노드) 하지만 recovery mode는 IDLE 상태로 변함
이때 v$database 뷰로 database mode 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | 1번 노드 SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE 2번 노드 SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY |
이 때 다시한번 아래 명령을 입력하면
recovery mode가 real time apply mode 로 변경되고
2번 노드의 v$database 뷰의 open_mode 가 READ ONLY WITH APPLY 로 변경됨
1 | SQL> recover managed standby database using current logfile disconnect from session; |
하지만 이 명령을 입력하지 않고 있으면 몇초 뒤 자동으로 위 명령을 실행함
현재 recovery mode 확인(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> select dest_id, type, status, database_mode, recovery_mode, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------- ------------------- -------------------------- --------------- -------- 1 LOCAL VALID OPEN IDLE adg11g 2 PHYSICAL VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY adg11gsb NO GAP |
show configuration 확인(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ dgmgrl sys/oracle DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11g - Primary database adg11gsb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
이 때 2번 노드의 alert log 를 보면 아래와 같은 메세지가 발생함(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 | tail -f /app/oracle/diag/rdbms/adg11gsb/adg11gsb/trace/alert_adg11gsb.log Sun Dec 20 21:18:35 2020 SMON: enabling cache recovery Dictionary check beginning Dictionary check complete Database Characterset is KO16MSWIN949 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Physical standby database opened for read only access. Completed: alter database open read only Sun Dec 20 21:19:07 2020 Data Guard: Database open completed; restarting redo-apply ... ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Attempt to start background Managed Standby Recovery process (adg11gsb) Sun Dec 20 21:19:07 2020 MRP0 started with pid=28, OS id=5888 MRP0: Background Managed Standby Recovery process started (adg11gsb) Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Waiting for thread 1 sequence 99 (in transit) Recovery of Online Redo Log: Thread 1 Group 4 Seq 99 Reading mem 0 Mem# 0: /app/oracle/oradata/adg11gsb/standby_log01.log Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE |
3번 째 줄 : 2번 노드 open 명령을 입력한 시점
10번 째 줄 : 2번 노드 open 이 완료된 시점
12번 째 줄 : 2번 노드 open 후 자동으로 redo-apply 를 재시작 하는 부분
(alter database recover managed standby database through all switchover disconnect using current logfile; 명령 실행)
16번 째 줄 : MRP 프로세스 기동됨
25번 째 줄 : redo-apply 가 완료됨
추가3. real time redo apply 테스트
1번 노드에서 table 생성 후 삭제 테스트
테이블 생성 및 데이터 삽입, 커밋(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> create table imsidg (no number); Table created. SQL> insert into imsidg values(1); 1 row created. SQL> insert into imsidg values(2); 1 row created. SQL> insert into imsidg values(3); 1 row created. SQL> commit; Commit complete. |
현재 사용중인 백그라운드 프로세스 확인
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 | 1번 노드 SQL> select client_process,process,status,sequence# from v$managed_standby; CLIENT_P PROCESS STATUS SEQUENCE# -------- --------- ------------ ---------- ARCH ARCH CLOSING 98 ARCH ARCH CONNECTED 0 ARCH ARCH CLOSING 96 ARCH ARCH CLOSING 98 LNS LNS WRITING 99 2번 노드 SQL> select client_process,process,status,sequence# from v$managed_standby; CLIENT_P PROCESS STATUS SEQUENCE# -------- --------- ------------ ---------- ARCH ARCH CONNECTED 0 ARCH ARCH CONNECTED 0 ARCH ARCH CONNECTED 0 ARCH ARCH CLOSING 98 ARCH RFS IDLE 0 UNKNOWN RFS IDLE 0 LGWR RFS IDLE 99 N/A MRP0 APPLYING_LOG 99 8 rows selected. |
1번 노드는 LNS 프로세스가 내려쓰는 중임을 확인 가능함
2번 노드는 MRP 프로세스는 계속 APPLYING LOG 상태임 (로그를 적용하는 상태)
테이블 데이터 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 1번 노드 SQL> select * from imsidg; NO ---------- 1 2 3 2번 노드 SQL> select * from imsidg; NO ---------- 1 2 3 |
동일 한 값 확인 가능함
테이블 삭제(2번 노드)
1 2 3 4 5 | SQL> drop table imsidg purge; drop table imsidg purge * ERROR at line 1: ORA-16000: database open for read-only access |
2번 노드는 read only 모드이기 때문에 삭제는 불가능함
테이블 삭제(1번 노드)
1 2 3 | SQL> drop table imsidg purge; Table dropped. |
정상적으로 삭제됨
추가4. Redo 전송 확인
Redo 전송이 안될 경우 이 쿼리로 문제 원인을 파악 가능(1번 노드)
1 2 3 4 5 6 7 | SQL> set lines 200 pages 1000 SQL> select dest_id,status,error from v$archive_dest where rownum < 3; DEST_ID STATUS ERROR ---------- --------- ----------------------------------------------------------------- 1 VALID 2 VALID |
TNS 오류 발생시 리스너 및 tnsping 을 확인
리스너 1, 2번 내리고 확인한 상태(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 1번 노드 SQL> set lines 200 pages 1000 SQL> select dest_id,status,error from v$archive_dest where rownum < 3; DEST_ID STATUS ERROR ---------- --------- ----------------------------------------------------------------- 1 VALID 2 ERROR ORA-12541: TNS:no listener 2번 노드 SQL> set lines 200 pages 1000 SQL> select dest_id,status,error from v$archive_dest where rownum < 3; DEST_ID STATUS ERROR ---------- --------- ----------------------------------------------------------------- 1 VALID 2 VALID |
1번 노드에서는 TNS 관련 ORA 메세지가 발생하지만
2번 노드에서는 ORA 메세지가 발생하지 않음
Redo 전송을 확인하는 부분에서 문제가 생겼는지 확인하는 쿼리이기 때문임
정상시 Standby DB는 Redo를 받기만 하고 보내진 않기때문에 모두 VALID로 표시됨
추가5. switchover 테스트
*switchover 와 failover 란?
S/W나 H/W의 작업에 의해 계획된 전환은 Switchover라 하며,
운영 중에 Primary의 예기치 않은 장애에 대한 전환은 Failover 라고함
switchover 테스트(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $ dgmgrl sys/oracle DGMGRL> switchover to adg11gsb Performing switchover NOW, please wait... Operation requires a connection to instance "adg11gsb" on database "adg11gsb" Connecting to instance "adg11gsb"... Connected. New primary database "adg11gsb" is opening... Operation requires startup of instance "adg11g" on database "adg11g" Starting instance "adg11g"... Unable to connect to database ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up and mount instance "adg11g" of database "adg11g" |
1번 노드가 제대로 올라오지 못함
수동으로 startup mount(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> startup mount ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1073744936 bytes Database Buffers 570425344 bytes Redo Buffers 7094272 bytes Database mounted. |
configuration 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ dgmgrl sys/oracle DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11gsb - Primary database adg11g - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
2번 노드가 Primary 가 되고 1번 노드가 Standby 가 됨
DB role 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | 1번 노드 SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED 2번 노드 SQL> select database_role, open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE |
2번 노드가 Primary 가 되고 1번 노드가 Standby 가 됨
1번 노드가 제대로 올라오지 못하는 문제 해결
*(switchover 재시도 후 1번 노드 Primary, 2번 노드 Standby 일 때 진행)
StaticConnectIdentifier 파라미터 설정
1 2 3 4 5 | $ dgmgrl sys/oracle DGMGRL> edit database adg11g set property StaticConnectIdentifier = 'adg11g'; Property "staticconnectidentifier" updated DGMGRL> edit database adg11gsb set property StaticConnectIdentifier = 'adg11gsb'; Property "staticconnectidentifier" updated |
다시 switchover 테스트
1 2 3 4 5 6 7 8 9 10 11 | DGMGRL> switchover to adg11gsb Performing switchover NOW, please wait... Operation requires a connection to instance "adg11gsb" on database "adg11gsb" Connecting to instance "adg11gsb"... Connected. New primary database "adg11gsb" is opening... Operation requires startup of instance "adg11g" on database "adg11g" Starting instance "adg11g"... ORACLE instance started. Database mounted. Switchover succeeded, new primary is "adg11gsb" |
정상적으로 완료됨
configuration 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11gsb - Primary database adg11g - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
정상적으로 완료됨
추가6. flashback database 적용
flashback 에 필요한 폴더 생성 및 파라미터 적용(1번, 2번 노드)
1 2 3 4 5 6 7 8 | $ mkdir -p /app/oracle/fra SQL> alter system set db_recovery_file_dest_size=5g scope=spfile; System altered. SQL> alter system set db_recovery_file_dest='/app/oracle/fra' scope=spfile; System altered. |
파라미터 변경 확인(1번, 2번 노드)
1 2 3 4 5 6 | SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /app/oracle/fra db_recovery_file_dest_size big integer 5G |
정상적으로 변경됨
flashback database 활성화(1번, 2번 노드)
1 2 3 4 5 6 7 8 9 10 11 | 1번 노드 SQL> alter database flashback on; Database altered. 2번 노드 SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-01153: an incompatible media recovery is active |
1번 노드는 정상적으로 활성화되었지만
2번 노드는 에러가 발생함(switchover 후 Primary DB일 때 적용 해야함)
2번노드(adg11gsb)로 switchover(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ dgmgrl sys/oracle DGMGRL> switchover to adg11gsb Performing switchover NOW, please wait... Operation requires a connection to instance "adg11gsb" on database "adg11gsb" Connecting to instance "adg11gsb"... Connected. New primary database "adg11gsb" is opening... Operation requires startup of instance "adg11g" on database "adg11g" Starting instance "adg11g"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "adg11gsb" |
정상적으로 완료됨
flashback database 활성화(2번 노드)
1 2 3 | SQL> alter database flashback on; Database altered. |
정상적으로 활성화됨
switchover로 Primary, Standby 원상복구
1 2 3 4 5 6 7 8 9 10 11 12 | DGMGRL> switchover to adg11g Performing switchover NOW, please wait... Operation requires a connection to instance "adg11g" on database "adg11g" Connecting to instance "adg11g"... Connected. New primary database "adg11g" is opening... Operation requires startup of instance "adg11gsb" on database "adg11gsb" Starting instance "adg11gsb"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "adg11g" |
원상복구 완료
추가7. failover 테스트
*switchover 와 failover 란?
S/W나 H/W의 작업에 의해 계획된 전환은 Switchover라 하며,
운영 중에 Primary의 예기치 않은 장애에 대한 전환은 Failover 라고함
1번 노드 shutdown abort 실행(1번 노드)
1 2 | SQL> shutdown abort ORACLE instance shut down. |
데이터가드 브로커 configuration 확인
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 | 1번 노드 $ dgmgrl sys/oracle DGMGRL> show configuration Error: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 Configuration details cannot be determined by DGMGRL 2번 노드 $ dgmgrl sys/oracle DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11g - Primary database adg11gsb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: ORA-01034: ORACLE not available ORA-16625: cannot reach database "adg11g" DGM-17017: unable to determine configuration status |
1번 노드 DB가 비정상 종료 되었기 때문에 configuration 조회 불가능함
2번 노드 조회는 가능하지만 현재 1번노드가 사용불가능 상태임을 알려줌
switchover 테스트(1번 노드)
1 2 3 4 5 6 7 | DGMGRL> switchover to adg11gsb Error: ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 Configuration details cannot be determined by DGMGRL |
현재 1번 노드가 비정상 종료 되었기 때문에 switchover이 불가능함
failover 테스트(1번 노드)
1 2 3 4 5 6 | DGMGRL> failover to adg11gsb ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 Configuration details cannot be determined by DGMGRL |
현재 1번 노드가 비정상 종료 되었기 때문에
1번 노드에서 failover이 불가능함
failover 테스트(1번 노드)
1 2 3 4 | $ dgmgrl sys/oracle@adg11gsb DGMGRL> failover to adg11gsb Performing failover NOW, please wait... Failover succeeded, new primary is "adg11gsb" |
failover 가 성공적으로 끝남 새로운 Primary db는 adg11gsb(2번 노드)가 됨
v$archive_dest_status 뷰 확인(2번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> select dest_id, type, status, database_mode, recovery_mode, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------- --------------- ---------------- ---------------- ----------- 1 LOCAL VALID OPEN IDLE adg11gsb 2 UNKNOWN DEFERRED UNKNOWN IDLE adg11g |
각각 TYPE와 STATUS, DATABASE_MODE를 보면
2번 노드(adg11gsb)는 LOCAL(Primary) 가되고 VALID, OPEN 상태이지만
1번 노드(adg11g)는 UNKNOWN 에 DEFERRED, UNKNOWN 상태임
1번 노드 startup 시도(1번 노드)
1 2 3 4 5 6 7 8 9 10 11 | SQL> startup ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size 2253784 bytes Variable Size 1124076584 bytes Database Buffers 520093696 bytes Redo Buffers 7094272 bytes Database mounted. ORA-16649: possible failover to another database prevents this database from being opened |
mount 까지는 올라오지만 open 되지 못함
데이터가드 브로커 configuration 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 1번 노드 $ dgmgrl sys/oracle DGMGRL> show configuration ORA-16795: the standby database needs to be re-created Configuration details cannot be determined by DGMGRL 2번 노드 $ dgmgrl sys/oracle DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11gsb - Primary database adg11g - Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
1번 노드는 failover로 인해 사용불가 상태가됨
재사용을 하려면 flashback database 가 on으로 되어있거나
off 인경우에는 데이터가드 재구성을 해줘야함
추가8. failover 한 db 복구 테스트
*flashback database가 양쪽 노드 모두 on으로 되어 있을 경우에만 복구가 가능함
reinstate 명령 실행(2번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | DGMGRL> reinstate database 'adg11g' Reinstating database "adg11g", please wait... Operation requires shutdown of instance "adg11g" on database "adg11g" Shutting down instance "adg11g"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "adg11g" on database "adg11g" Starting instance "adg11g"... ORACLE instance started. Database mounted. Continuing to reinstate database "adg11g" ... Reinstatement of database "adg11g" succeeded |
데이터가드 브로커 configuration 확인(2번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 13 | DGMGRL> show configuration Configuration - adg11g Protection Mode: MaxPerformance Databases: adg11gsb - Primary database adg11g - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS |
v$archive_dest_status 뷰 확인(2번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> select dest_id, type, status, database_mode, recovery_mode, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- ---------- --------- --------------- ------------------------- ----------------- ------- 1 LOCAL VALID OPEN IDLE adg11gsb 2 PHYSICAL VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY adg11g NO GAP |
각각 TYPE와 STATUS, DATABASE_MODE를 보면
2번 노드(adg11gsb)는 LOCAL(Primary) 에 VALID, OPEN 상태이고
1번 노드(adg11g)는 PHYSICAL(Standby) 에 VALID, OPEN_READ-ONLY 상태로 변경됨
정상 복구 완료
1번, 2번 노드 Primary, Standby 를 초기 상태로 돌리기 위해 다시한번 switchover 실행(2번 노드)
1 2 3 4 5 6 7 8 9 10 11 12 | DGMGRL> switchover to adg11g Performing switchover NOW, please wait... Operation requires a connection to instance "adg11g" on database "adg11g" Connecting to instance "adg11g"... Connected. New primary database "adg11g" is opening... Operation requires startup of instance "adg11gsb" on database "adg11gsb" Starting instance "adg11gsb"... ORACLE instance started. Database mounted. Database opened. Switchover succeeded, new primary is "adg11g" |
v$archive_dest_status 뷰 확인(1번 노드)
1 2 3 4 5 6 7 8 9 | SQL> set lines 200 pages 1000 SQL> select dest_id, type, status, database_mode, recovery_mode, db_unique_name, gap_status from v$archive_dest_status where dest_id <=2; DEST_ID TYPE STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS ---------- --------- --------- ------------------- -------------------------- --------------- -------- 1 LOCAL VALID OPEN IDLE adg11g 2 PHYSICAL VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY adg11gsb NO GAP |
초기 상태로 변경됨
참조 :
https://dataforum.io/display/ORCL/11g+R2+Data+Guard+Installation
https://docs.oracle.com/cd/E18283_01/server.112/e17022/rcmbackp.htm
http://wiki.gurubee.net/display/CORE/19.+DATA+GUARD+11Ghttps://docs.oracle.com/cd/E19421-01/820-6945/fnrfv/index.html
https://positivemh.tistory.com/236
https://positivemh.tistory.com/676
https://positivemh.tistory.com/677
https://docs.oracle.com/database/121/DGBKR/dbresource.htm#DGBKR160
https://docs.oracle.com/html/E10643_07/rcmsynta023.htm
'ORACLE > Install' 카테고리의 다른 글
| Oracle 11g R2 Single PSU 롤백 가이드 (0) | 2021.03.20 |
|---|---|
| Oracle 11g R2 Single PSU 패치 가이드 (0) | 2021.03.20 |
| Oracle Linux 7.6에 Oracle 21c 클라이언트 설치 가이드 (0) | 2020.12.12 |
| Windows 10에 Oracle 12c R1 클라이언트 설치 가이드 (3) | 2020.08.07 |
| Oracle Linux 7.6에 Oracle 12c R1 설치 가이드 (0) | 2020.07.15 |