Oracle 11gR2 RAC to RAC Active DataGuard (with ASM+Rawdevice) Author 강경구 Creation Date 2010-08-05 Last Updated Version 1.0 Copyright(C) 2009 Goodus Inc. All Rights Reserved Version 변경일자변경자 ( 작성자 ) 주요내용 1 2010-08-05 강경구문서최초작성
Contents 1. Oracle 11g DataGuard... 3 1.1. 11g Data Guard 구성...3 1.2. Oracle 11g Data Guard 와 Oracle RAC...4 1.2.1. 특이사항... 5 2. Oracle Data Guard 11g 의새로운기능... 6 2.1. Snapshot Standby...6 2.2. SYNC 와 ASYNC redo 전송의자동 Failover...6 2.3. Transient Logical Standby 롤링업그레이드지원...7 2.4. 데이터보호강화...7 2.5. 보안강화 (SSL 인증 REDO 전송 )...7 2.6. 추가 SQL Apply 데이터유형지원...7 2.7. SQL Apply 관리강화...7 2.8. Data Guard Broker 강화...7 2.9. Enterprise Manager Grid Control 11g 기능개선...8 2.10. 새로운 Oracle Database 11g 데이터베이스옵션...8 2.10.1. Oracle Active Data Guard... 8 2.10.2. Oracle Advanced Compression... 8 3. Oracle 11gR2 RAC SW 설치... 9 3.1. Primary DB & Standby DB Information...9 3.2. Oracle 11gR2 parameter 변경사항...9 4. Oracle 11g RAC Data Guard 구성... 9 4.1. Tnsnames.ora 설정...9 4.2. Database Force logging setup...9 4.3. Standby Redo log 생성...10 4.4. PRIMARY/STANDBY Init parameter 설정...11 4.5. Standby 용 controlfile 생성...12 4.6. Standby 파일위치 directory 생성...12 4.7. Stand by 서버로파일이전...13 4.8. PRIMARY DB OPEN...13 4.9. STANDBY DB OPEN...13 4.10. STANDBY LOGFILE ADD...14 4.11. CRS 에 Service Register...14 4.12. Oracle 11g Data Guard 구성점검...16 4.12.1. Redo 젂송확인...16 4.12.2. Standby recovery 확인...16 4.12.3. Redo data archiving 확인...17 4.13. Trouble Shooting...18 4.13.1. Standby redo log file 의삭제와재생성...18 4.13.2. Standby db start 시 Temporary tablespace 재생성안되는문제...21 5. 참고문서... 23-2
1. Oracle 11g DataGuard 이번호에서는데이터보호와더불어 Standby 시스템이 Standby 역할과동시에 Production 과 QA 기능을지원하는통합 HA/DR 솔루션을제공함으로써기존재해복구패러다임의근본적인변화를가져온 Data Guard 11g 의신기능과 Physical Active Dataguard 구축방법을알아보겠습니다. 일반적인 DataGuard 에대한소개는 ' 굿어스기술노트 21 호 Data Guard' 를참조해주십시요. 1.1. 11g Data Guard 구성 [11g Dataguard Architecture] 위의그림을보시는것과같이 11g Data Guard 는오라클데이터베이스프로세스를이용하여고가용성과재해복구에필요한자동화를실현합니다. Primary Database 에서 Data Guard 는 LogWriter Network Server (LNS) 라는특수 Background 프로세스를사용해 Log Writer 가작성하는 redo 데이터를캡처해동기또는비동기로데이터를스탞바이데이터베이스에젂송합니다. LNS 프로세스는 Log Writer 를젂송오버헤드와네트워크방해로부터분리합니다. 동기 redo 젂송 (SYNC) 를위해서는 Primary Database 에있는 Log Writer 가 LNS 로부터스탞바이가 redo 데이터를받았으며기다렸다가클라이언트애플리케이션에실행을확인하기젂에스탞바이 redo 로그에데이터를작성했다는확인을기다려야합니다. 이렇게함으로써모든실행트랜잭션이디스크에서이루어지며스탞바이위치에서보호됩니다. 비동기 redo 젂송 (ASYNC) 를위해서기본에있는 Log Writer 가스탞바이데이터모드로부터 redo 가디스크에작성되었으며 redo 젂송과비동기인클라이언트애플리케이션에실행이확인되었다는확인을기다릴필요가없습니다. 뿐만아니라 ASYNC 는매우효율적으로 redo 를스탞바이데이터베이스에스트림해네트워크확인으로인해야기되는네트워크작업량감소의원인이되는오버헤드를제거합니다. - 3
Primary Database 와 Standby Database 가분리되는경우 ( 네트워크장애나스탞바이서버장애 ) 와선택된보호모드에따라서 Primary Database 가트랜잭션을계속해서프로세스하고새로욲네트워크연결이이루어질때까지스탞바이에발송될수없는 redo 데이터백로그를축적합니다 ( 아카이브로그갭이라고함 ). 이러한상태에서는 Data Guard 가계속해서스탞바이데이터베이스상태를모니터링하고, 연결이다시이루어지는때를감지하고, Primary Database 를사용해스탞바이데이터베이스를자동을재동기화해서구성을가능한싞속하게보호상태로복구합니다. 갭을해결하는데필요한로그파일을발송하는데아카이브 (ARCn) 프로세스를항상사용합니다. 1.2. Oracle 11g Data Guard 와 Oracle RAC Data Guard 와 Oracle RAC 는가장높은수준의확장성, 가용성, 데이터보호를제공하는상호보완적인기술입니다. Cascaded Destinations 사용에적용되는제약을제외하고 ( 위에서설명 ) Oracle RAC 와 Data Guard 사이의통합은원홗하게이루어집니다. Oracle RAC 와단일노드데이터베이스를결합해 Data Guard 구성참여하고역할을맡을수있습니다. Oracle RAC 는업계유일의작업부하관리와확장성기능을제공하는동시에서버장애에대비해보호하는이상적인 HA 솔루션을제공합니다. Data Guard 는완젂한스토리지어레이장애, 욲영자실수, Oracle RAC 노드에걸쳐롤링방식으로실시될수없는예상된유지, 사이트장애를가져오는여러개의연관된장애로인한다욲타임을최소화하는완젂한중복성 (redundancy) 을갖추고데이터가용성과보호의수순을다시한차원높입니다. [Active Data Guard with Multiple Standby Databases] [Active Data Guard with Oracle RAC Standby Databases and a Single Primary] - 4
[Transmitting Redo Data from a Multi-Instance Primary Database] [Active Data Guard with Oracle RAC Standby Databases and a Multi-Instance Primary] 1.2.1. 특이사항 Archive destination : MRP(Media Recover Process) 가 RAC 시스템의한쪽노드에서만 Redo 를적용할수있으므로양쪽 Node 에서 arhive destination 를 IBM 의 GPFS 와같은공유 File system 으로구성해야한다. 그렇지않으면 Data Guard Switch over 나 Failover 시 Archive file 을옮겨줘야하는번거로움이발생한다. Standby Database 에서 MRP Process 의 Node 변경시수동으로적용하여야한다. - 5
2. Oracle Data Guard 11g 의새로운기능 2.1. Snapshot Standby Physical Standby Database 부터생성되는새로욲유형의 Standby Database 입니다. 일단생성되면 Snapshot Standby 는읽기-쓰기가지원되어테스트와다른용도를위해 Primary Database 와독립적인트랜잭션을처리할수있습니다. Snapshot Standby 데이터베이스가 Primary Database 로부터계속해서업데이트를받고아카이빙을수행하지만 Primary Database 에서받는 redo 데이터는 Snapshot Standby 가다시 Physical Standby 데이터베이스로젂홖되고 Snapshot Standby Mode 중에이루어졌던모든업데이트가버려질때까지적용되지않습니다. snapshot standby database 는 physical standby database 가 snapshot standby database 로변홖된것에의해생성된모든업데이트가가능한 standby database 입니다. snapshot standby database 는 primary database 에서 redo data 를받고 archive 하지만 apply 는하지않습니다. snapshot standby database 에서발생된모든 local update 를버린이후에일단 snapshot standby database 는 physical standby database 로젂홖된후 primary database 에서받은 redo data 가적용됩니다. snapshot standby database 는 EM 에서나 Data Guard Broker command line 인 DGMGRL 그리고 SQL*Plus 를통해서생성할수있습니다. 2.2. SYNC 와 ASYNC redo 전송의자동 Failover Data Guard 10g Release 2 는새로욲기능인최대가용성보호모드 (SYNC) 의패스트스타트페일오버 (Fast-Start Failover) 를사용하는자동페일오버를도입했습니다. Data Guard 11g 은패스트스타트페일오버를확장해자동페일오버로인해원하는 RPO (Recovery Point Objective) 를초과하는데이터손실로이어지지않도록하는사용자구성데이터손실한계를추가하는방식으로최대성능모드 (ASYNC) 를지원합니다. 사용자는페일오버가패스트스타트페일오버한계시갂이지정된점검상태나원하는 ORA 오류에기초해소멸될때까지기다릴필요없이즉시발생하도록자동페일오버를구성할수있습니다. 새로욲 DBMS_DG PL/SQL 패지지를사용해애플리케이션이패스트스타트페일오버옵저버프로세스를통보해자동페일오버를시작할수있습니다. 성능강화 : 다음과같은다양한성능강화를제공합니다. 병렬미디어복구 ( 물리적스탞바이 ) 를사용해모든작업프로파일에있어서물리적스탞바이적용성능을크게강화합니다. SQL Apply 강화 ( 논리적스탞바이 ) 를사용하면, 구획이구분이되지않고 LOB, LONG, XML 유형의컬럼을포함하지않는테이블의삽입및업데이트적용성능을증대할수있습니다. ASYNC redo 젂송강화로네트워크지연이제거되면서네트워크작업량이증대할것이며, 특히 WAN 구축에서효과가뛰어납니다. 패스트스타트페일오버사용시페일오버시갂이추가로감소합니다. - 6
2.3. Transient Logical Standby 롤링업그레이드지원 사용자들은물리적스탞바이를일시논리적스탞바이데이터베이스로젂홖해롤링데이터베이스업그레이드에영향을미치고, 업그레이드가완료된후에물리적스탞바이로다시젂홖할수있습니다 (KEEP IDENTITY 젃사용 ). 따라서논리적스탞바이데이터베이스를생성하는데필요한중복스토리지투자를하지않고롤링데이터베이스업그레이드를실행하고자하는물리적스탞바이사용자들에게혜택을제공합니다. 2.4. 데이터보호강화 물리적스탞바이는데이터손상을가져올수있는결함이있는스토리지하드웨어와펌웨어로인해발생하는손실데이터파일쓰기를감지합니다. Data Guard 는스탞바이상의블록버젂을유입 redo 스트림버젂과비교합니다. 버젂에차이가있을경우, 쓰기손실을표시합니다. 사용자는스탞바이데이터베이스에페일오버를실시하고데이터일관성을복구합니다. 2.5. 보안강화 (SSL 인증 REDO 전송 ) SSL 인증을비밀번호파일과함께사용해 redo 젂송을인증할수있습니다. 주 : SSL 인증을위해서는 PKI 인증서, ASO, OID 가필요합니다. 2.6. 추가 SQL Apply 데이터유형지원 SQL Apply 는다음과같은추가데이터유형, 기타오라클기능, PL/AQL 을지원합니다. XMLType 데이터유형 (CLOB 로저장되는경우 ) 논리적스탞바이데이터베이스에서병렬로 DDL 을실행하는기능 TDE (Transparent Data Encryption) DBMS_FGA (Fine Grained Auditing) DBMS_RLS (Virtual Private Database: 가상개인데이터베이스 ) 2.7. SQL Apply 관리강화 DBMS_SCHEDULER 패키지를사용해스탞바이데이터베이스에서 Scheduler Job 을생성하고의도된대로작동할수있도록적합한데이터베이스역할과연관시킬수있습니다 ( 데이터베이스가기본인지스탞바이인지둘다인지등 ). Oracle RAC 데이터베이스와함께 SQL Apply 를사용하면각 Oracle RAC 클러스터의최초인스턴스를제외한모든인스턴스의사젂중지필요성이더이상없습니다. SQL Apply 를다시시작할필요없이 Data Guard SQL Apply 파라미터를동적으로설정할수도있습니다. DBMS_LOGSTDBY.APPLY_SET 패키지를사용해초기화파라미터를동적으로설정함으로써논리적스탞바이구성의관리, 업타임, 자동화를개선할수있습니다. 2.8. Data Guard Broker 강화 다음과같은기능강화로 Data Guard Broker 를사용할때관리가더욱갂편해집니다. - 7
redo 젂송옵션지원을개선해관리자가 redo 젂송서비스를위한연결설명을구체화 보호모드와최대가용성 / 최대성능사이의변경시데이터베이스다욲타임을제거 Oracle Clusterware 를콜드페일오버클러스터로사용해고가용성구성을위한단일인스턴스데이터베이스지원 2.9. Enterprise Manager Grid Control 11g 기능개선 Enterprise Manager 는다음과같은부문의관리를한층갂편하게만들어줍니다. 기존 RMAN 백업에서스탞바이데이터베이스생성 Oracle RAC Primary Database 에서 Oracle RAC 스탞바이데이터베이스생성 리포팅, 개발, 테스트를위한자동재기복사 스위치오버나페일오버시 Enterprise Manager 업무와성능한계를새로욲 Primary Database 로자동젂파 패스트스타트페일오버를위한작동대체옵저버 (Fault-tolerant observer) Enterprise Manager Data Recovery Advisor 가 IDR (Intelligent Data Repair) 를권장을할때사용가능한스탞바이데이터베이스홗용 2.10. 새로운 Oracle Database 11g 데이터베이스옵션 2.10.1. Oracle Active Data Guard 프로덕션데이터베이스에서한개이상의동기화된데이터베이스로자원집중홗동을오프로딩하는방식으로 QoS 를강화하는 Oracle Database 11g Enterprise Edition 을위한옵션입니다. Active Data Guard 옵션과함께제공되는실시갂쿼리기능을사용해계속해서프로덕션데이터베이스에서받은변경내용을 Apply 하면서, 쿼리, 소팅, 리포팅, 웹기반접속등을위한물리적스탞바이데이터베이스에읽기젂용접속을실행할수있습니다. 또한물리적스탞바이상에서 RMAN 블록변경추적을실행해물리적스탞바이데이터베이스에서싞속한증가백업을실시할수있습니다. 테스트에따르면 RMAN 블록변경추적을사용해데이터베이스에서변경속도를중갂으로하여점차로백업을증가하면기존증가백업에비해최대 20 배빠르게완료할수있는것으로나타났습니다. 2.10.2. Oracle Advanced Compression (2 년마다 3 배속도로 ) 늘어나는데이터양을비용효율적으로관리하도록하는 Oracle Database 11g Enterprise Edition 의옵션입니다. Advanced Compression 은네트워크트래픽과백업프로세스중의데이터뿐만아니라문서, 이미지, 멀티미디어와같은다양한체계 / 비체계화된데이터를비롯한모든유형의데이터를압축합니다. Advanced Compression 옵션은스탞바이데이터베이스에서아카이브로그갭의 Data Guard 11g 해결중에 redo 데이터의네트워크압축을수행합니다. 따라서네트워크나스탞바이데이터베이스작동중지후에스탞바이데이터베이스의재동기화를가속화하고네트워크대역폭을좀더효율적으로홗용할수있습니다. - 8
3. Oracle 11gR2 RAC SW 설치 GUARD 구성을위한특별한설치 option 은없으며굿어스기술노트 47 회 11gR2_RAC_Guide 설치과정동일하게진행합니다. 3.1. Primary DB & Standby DB Information NAME DBNAME Instance Name hostname Version Protection mode Primary DB GOODUS GOODUS1 goodus1 11.2.0.1.1 Maximum Performance (LGWR ASYNC) Primary DB GOODUS GOODUS2 goodus2 11.2.0.1.1 Maximum Performance (LGWR ASYNC) Standby DB STBY STBY1 goodus3 11.2.0.1.1 Maximum Performance (LGWR ASYNC) Standby DB STBY STBY2( goodus4 11.2.0.1.1 Maximum Performance (LGWR ASYNC) 3.2. Oracle 11gR2 parameter 변경사항 Oracle 11g R2 에서는 Dataguard( 이하 DG) 관련파라미터중다음파라미터가 deprecated 됨 standby_archive_dest 4. Oracle 11g RAC Data Guard 구성 4.1. Tnsnames.ora 설정 [PRIMARY TNSNAMES.ORA] STBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.111)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.112)(PORT = 1521)) (FAIL_OVER=ON) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY) ) ) [STANDBY TNSNAMES.ORA] GOODUS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.101)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.102)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GOODUS) ) ) 4.2. Database Force logging setup (oracle 유저로 primary 1 번노드에서수행 ) [oracle@goodus1:/oracle/db]$ srvctl stop database -d GOODUS - 9
SQL> Startup Mount SQL> ALTER DATABASE FORCE LOGGING; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> SELECT FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; FOR SUPPLEME ------ -------- YES YES 4.3. Standby Redo log 생성 Oracle 사용자로 Primary 에서수행합니다. Standby Redo Logfile 은 Primary db log group 보다 1 개더많게구성할것을권고하며반드시 Primary redo log 와 Standby redo log 가같은크기를가지고있어야합니다. 약다를경우에는차후에 ORA-16139 media recovery required 에러가발생하면서 switchover 나 failover 가정상적으로수행되지않을수있습니다. [PRIMAY] - ================================================== - Standby redolog 구성 (primary redo log 보다 1 개많은 6 개 group 구성 ) - ================================================== ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/dev/rcoredo15_lv' SIZE 700M, '/dev/rcoredo16_lv' SIZE 700M, '/dev/rcoredo17_lv' SIZE 700M, '/dev/rcoredo18_lv' SIZE 700M, '/dev/rcoredo19_lv' SIZE 700M, '/dev/rcoredo20_lv' SIZE 700M, '/dev/rcoredo21_lv' SIZE 700M, '/dev/rcoredo22_lv' SIZE 700M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '/dev/rcoredo23_lv' SIZE 700M, '/dev/rcoredo24_lv' SIZE 700M, '/dev/rcoredo25_lv' SIZE 700M, '/dev/rcoredo26_lv' SIZE 700M, '/dev/rcoredo27_lv' SIZE 700M, '/dev/rcoredo28_lv' SIZE 700M, '/dev/rcoredo29_lv' SIZE 700M, '/dev/rcoredo30_lv' SIZE 700M; - 10
4.4. PRIMARY/STANDBY Init parameter 설정 [PRIMARY] - ===================================== - SPFILE 을 PFILE 로변환 - ===================================== SQL> create pfile= /oracle/db/initgoodus.ora from spfile; SQL>!vi /oracle/db/initgoodus.ora ############################### ## primary role parameters ## ############################### *.db_unique_name='goodus' GOODUS1.log_archive_config='dg_config=(GOODUS,STBY)' GOODUS2.log_archive_config='dg_config=(GOODUS,STBY) ' *.log_archive_dest_1='location=/archive mandatory' GOODUS1.log_archive_dest_2='SERVICE=STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY' GOODUS2.log_archive_dest_2='SERVICE=STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.standby_file_management='auto' MRP 프로세스가한쪽에서 redo 를 apply 하므로 log_archive_dest_2 의서비스를 goodus3 의 /arch1 으로떨어지도록해야함. GOODUS2. log_archive_dest_2 를 goodus4 로서비스하면 goodus2 에서 v$archive_dest 의 Status 조회시 dest2 에서 ORA-16191 가발생한다. ############################### ## standby role parameters ## ############################### GOODUS1.fal_client='GOODUS1' GOODUS2.fal_client='GOODUS2' GOODUS1.fal_server='STBY1' GOODUS2.fal_server='STBY2' - ===================================== - PFILE 을 SPFILE 로변환 - ===================================== SQL>!cat $ORACLE_HOME/dbs/initGOODUS1.ora SPFILE='/dev/rspfile_lv' # line added by Agent SQL> create SPFILE='/dev/rspfile_lv from pfile= /oracle/db/initgoodus.ora ; [STANDBY] - ===================================== - SPFILE 을 PFILE 로변환 - ===================================== - 11
SQL> create pfile= /oracle/db/initstby.ora from spfile; SQL>!vi /oracle/db/initstby.ora ############################### ## primary role parameters ## ############################### *.db_name='goodus' *.db_unique_name='stby' *.log_archive_config='dg_config=(goodus,stby)' STBY1.log_archive_dest_1='LOCATION=/arch mandatory' STBY2.log_archive_dest_1='LOCATION=/arch mandatory' STBY1.log_archive_dest_2='SERVICE=GOODUS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=GOODUS' STBY2.log_archive_dest_2='SERVICE=GOODUS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=GOODUS' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' ############################### ## standby role parameters ## ############################### *.fal_client='stby' *.fal_server='goodus' *.db_file_name_convert='/dev/r','/dev/rk' *.log_file_name_convert='/dev/r','/dev/rk' *.standby_file_management=auto Primary 와 Standby RAC 시스템의각각의 Node 가동일한 Archive dest 를사용할수있도록 IBM 의 GPFS 와같은공유 File system 으로구성해야 4.5. Standby 용 controlfile 생성 [PRIMARY GOODUS1] Sql> alter database create standby controlfile as '/oracle/db/stby_control.ctl'; Database altered. [oracle@goodus1:/oracle/db]$ dd if=/oracle/db/stby_control.ctl bs=512 ssh goodus3 dd of=/dev/rkcoredo39_lv bs=512 [oracle@goodus1:/oracle/db]$ dd if=/oracle/db/stby_control.ctl bs=512 ssh goodus3 dd of=/dev/rkcoredo40_lv bs=512 4.6. Standby 파일위치 directory 생성 [STANDBY STBY1] Log, archive, datafile 위치 directory 생성 - 12
4.7. Stand by 서버로파일이전 [PRIMARY GOODUS1] - ===================================== - primary 의패스워드파일을 standby 서버로이전 - ===================================== 1 번노드에서수행 Scp $ORACLE_HOME/dbs/orapwGOODUS1 goodus3:/oracle/db/11g/dbs/ 1 번노드에서수행 Scp $ORACLE_HOME/dbs/orapwGOODUS1 goodus4:/oracle/db/11g/dbs/ ** 패스워드파일이동일하지않을경우 ORA-01031: insufficient privileges 에러가발생하며 redo 젂송이되지않음 SQL>select dest_id,status,error from v$archive_dest; DEST_ID STATUS ERROR ---------- ------------------ ----------------------------------------------------------------- 1 VALID 2 INVALID ORA-01031: insufficient privileges - ===================================== - datafile,redo log,standby 용 controlfile standby 서버로이전 - ===================================== primary db shutdown 후모든파일이젂 4.8. PRIMARY DB OPEN [PRIMARY GOODUS1] [oracle@goodus1:/oracle/db]$ srvctl start database d GOODUS SQL> select DATABASE_ROLE,PROTECTION_MODE from v$database; DATABASE_ROLE PROTECTION_MODE ---------------- -------------------- PRIMARY MAXIMUM PERFORMANCE 4.9. STANDBY DB OPEN [STANDBY STBY1] SQL> startup mount SQL>alter database recover managed standby database disconnect from session; start redo apply SQL>alter database recover managed standby database cancel; SQL>alter database open; SQL>alter database recover managed standby database using current logfile disconnect; real-time redo apply SQL> select open_mode from v$database; - 13
OPEN_MODE -------------------- READ ONLY WITH APPLY [STANDBY STBY2] SQL> startup SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY 4.10. STANDBY LOGFILE ADD [STANDBY STBY1] SQL> STARTUP MOUNT; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '/dev/rk2coredo15_lv' SIZE 700M, '/dev/rk2coredo16_lv' SIZE 700M, '/dev/rk2coredo17_lv' SIZE 700M, '/dev/rk2coredo18_lv' SIZE 700M, '/dev/rk2coredo19_lv' SIZE 700M, '/dev/rk2coredo20_lv' SIZE 700M, '/dev/rk2coredo21_lv' SIZE 700M, '/dev/rk2coredo22_lv' SIZE 700M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 '/dev/rk2coredo23_lv' SIZE 700M, '/dev/rk2coredo24_lv' SIZE 700M, '/dev/rk2coredo25_lv' SIZE 700M, '/dev/rk2coredo26_lv' SIZE 700M, '/dev/rk2coredo27_lv' SIZE 700M, '/dev/rk2coredo28_lv' SIZE 700M, '/dev/rk2coredo29_lv' SIZE 700M, '/dev/rk2coredo30_lv' SIZE 700M; 4.11. CRS 에 Service Register CRS 에 Database 와 service 등을 register 할때는 PHYSICAL_STANDBY 옵션을추가해줘야장애시 Failover 에이상이발생하지않는다. - 14
# Add Database Syntax Usage: srvctl add database -d <db_unique_name> -o <oracle_home> [-m <domain_name>] [-p <spfile>] [-r {PRIMARY PHYSICAL_STANDBY LOGICAL_STANDBY SNAPSHOT_STANDBY}] [-s <start_options>] [-t <stop_options>] [-n <db_name>] [-y {AUTOMATIC MANUAL}] [-g "<serverpool_list>"] [-x <node_name>] [-a "<diskgroup_list>"] [oracle@goodus3:/oracle/db]$ srvctl add database -d STBY -o /oracle/db/11g -p /dev/rspfile_lv -r PHYSICAL_STANDBY -n GOODUS [oracle@goodus3:/oracle/db]$ srvctl config database -d STBY Database unique name: STBY Database name: GOODUS Oracle home: /oracle/db/11g Oracle user: oracle Spfile: /dev/rspfile_lv Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: STBY Database instances: STBY1,STBY2 Disk Groups: Services: Database is administrator managed [oracle@goodus3:/oracle/db]$ # Add Service Syntax Usage: srvctl add service -d <db_unique_name> -s <service_name> {-r "<preferred_list>" [-a "<available_list>"] [-P {BASIC NONE PRECONNECT}] -g <server_pool> [-c {UNIFORM SINGLETON}] } [-k <net_num>] [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y {AUTOMATIC MANUAL}] [-q {TRUE FALSE}] [-x {TRUE FALSE}] [-j {SHORT LONG}] [-B {NONE SERVICE_TIME THROUGHPUT}] [-e {NONE SESSION SELECT}] [-m {NONE BASIC}] [-z <failover_retries>] [-w <failover_delay>] [oracle@goodus3:/oracle/db]$ srvctl add service -d STBY s TESTSVC r STBY1 a STBY2 l PHYSICAL_STANDBY [oracle@kdb41:/oracle/db]$ srvctl config service -d STBY -s testsvc Service name: TESTSVC - 15
Service is enabled Server pool: STBY_TESTSVC Cardinality: 1 Disconnect: false Service role: PHYSICAL_STANDBY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Failover type: NONE Failover method: NONE TAF failover retries: 0 TAF failover delay: 0 Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Preferred instances: STBY1 Available instances: STBY2 [oracle@goodus3:/oracle/db]$ 4.12. Oracle 11g Data Guard 구성점검 4.12.1. Redo 전송확인 [PRIMARY-GOODUS1] Col error for a30 select dest_id,status,error from v$archive_dest where rownum < 3; DEST_ID STATUS ERROR ---------- --------- ------------------------------ 1 VALID 2 VALID redo 젂송이앆될경우위의쿼리로 error 원인을파악합니다. TNS 오류발생시리스너및 tnsping 을확인합니다. 4.12.2. Standby recovery 확인 [STANDBY-STBY1] - 오라클백그라욲드프로세스중 recovery 를담당하는 mrp 프로세스가떠있는지확인합니다. - 현재구성은 Standby 쪽도 RAC 이기때문에 mrp 프로세스는 recover managed 명령을수행한노드에서만구동됩니다. 아래의결과를보면, 1 번노드에서만 mpr 프로세스가구동된것을확인할수있습니다. - 16
[oracle@goodus3:/oracle/db]$ ps -ef grep mrp oracle 188620 1 0 10:34:04-0:00 ora_mrp0_stby1 oracle 459222 73762 0 11:26:46 pts/0 0:00 grep mrp [oracle@goodus4:/oracle/db]$ ps -ef grep mrp oracle 213438 569636 0 11:27:07 pts/0 0:00 grep mrp - alert log 확인 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT Attempt to start background Managed Standby Recovery process (STBY1) Fri Apr 16 10:34:04 2010 MRP0 started with pid=40, OS id=188620 MRP0: Background Managed Standby Recovery process started (STBY1) -Mrp 프로세스가 start 되지않았다면 alert log 를통해원인을확인하고조취를취한후 redo apply 를재시작합 니다. --redo apply start SQL>alter database recover managed standby database disconnect from session; --redo apply stop SQL> alter database recover managed standby database cancel; 4.12.3. Redo data archiving 확인 [ACTIVE-GOODUS1] -- 현재 archived file 확인 SQL> SELECT * FROM (SELECT NAME, THREAD#, SEQUENCE#, FIRST_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC) where rownum < 10; NAME THREAD# SEQUENCE# FIRST_TIME APPLIED -------------------------------------------------- ---------- ---------- --------------- --------- STBY1 1 8191 16-APR-10 YES /archive/arch_1_713988964_8191.arc 1 8191 16-APR-10 NO STBY1 1 8190 16-APR-10 YES /archive/arch_1_713988964_8190.arc 1 8190 16-APR-10 NO STBY1 1 8189 16-APR-10 YES /archive/arch_1_713988964_8189.arc 1 8189 16-APR-10 NO STBY1 1 8188 16-APR-10 YES /archive/arch_1_713988964_8188.arc 1 8188 16-APR-10 NO STBY1 1 8187 16-APR-10 YES--force logging [STANDBY-STBY1] -- 새로욲 redo data 가 archiveing 되었는지확인 SQL> SELECT * FROM (SELECT NAME, THREAD#, SEQUENCE#, FIRST_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC) - 17
where rownum < 10; NAME THREAD# SEQUENCE# FIRST_TIME APPLIED ---------------------------------------- ---------- ---------- --------------- --------- /arch1/arch_1_713988964_8191.arc 1 8191 16-APR-10 YES /arch1/arch_1_713988964_8190.arc 1 8190 16-APR-10 YES /arch1/arch_1_713988964_8189.arc 1 8189 16-APR-10 YES /arch1/arch_1_713988964_8188.arc 1 8188 16-APR-10 YES /arch1/arch_1_713988964_8187.arc 1 8187 16-APR-10 YES /arch1/arch_1_713988964_8186.arc 1 8186 16-APR-10 YES /arch1/arch_1_713988964_8185.arc 1 8185 16-APR-10 YES /arch1/arch_1_713988964_8184.arc 1 8184 16-APR-10 YES /arch1/arch_1_713988964_8183.arc 1 8183 16-APR-10 YES STANDBY DATABASE Restart Standby Database 재시작시에는아래와같이 Real time apply 를시작해줘야함. [oracle@goodus3:/oracle/db]$ srvctl start database -d STBY SQL> alter database recover managed standby database using current logfile disconnect; 구성시 Error 조치방법 [Standby Alert.log] ARC5: Archiving not possible: error count exceeded ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance STBY1 - Archival Error ORA-16014: log 11 sequence# 8179 not archived, no available destinations ORA-00312: online log 11 thread 1: '/dev/rk2coredo11_lv' Errors in file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_arc5_245872.trc: ORA-16014: log 11 sequence# 8179 not archived, no available destinations ORA-00312: online log 11 thread 1: '/dev/rk2coredo11_lv' 조치 : standby_archive_dest 파라미터설정시제거 (deprecated 됨 ) log_archive_dest_1 파라미터에 valid for 옵션설정시제거 4.13. Trouble Shooting 4.13.1. Standby redo log file 의삭제와재생성 Standby Database 의 Datafile 과 Redo log file 은 Primary 와의 Switch over 를위해서 File name 을 Primary DB 와다르게구성하여야한다. 특히 log_file_name_convert 의경 DB Startup 시 ORA-19527: physical standby redo log must be renamed 에러가발생한다. db_file_name_convert parameter 의경우는 # 로주석처리하거나삭제하면되지만, log_file_name_convert 는필수 parameter 이므로아래와같이조치하면된다. - 18
#*.db_file_name_convert='/dev/r','/dev/rk' *.log_file_name_convert='/dev/r','/dev/r' 그러나 Data Guard 의 Switch over 를위해서라면 file name 을 rename 해주는것이좋다. Rename 젂에 이미 Data Guard DB 를 Open 하여욲영하였다면, 다음과같이 Standby Redo Log 의 file name 을변경하 여야한다. ** OS 에서 Volume rename 후작업 alter database rename file '/dev/rcoredo15_lv' to '/dev/rkcoredo15_lv' ; alter database rename file '/dev/rcoredo16_lv' to '/dev/rkcoredo16_lv' ; alter database rename file '/dev/rcoredo17_lv' to '/dev/rkcoredo17_lv' ; alter database rename file '/dev/rcoredo18_lv' to '/dev/rkcoredo18_lv' ; alter database rename file '/dev/rcoredo19_lv' to '/dev/rkcoredo19_lv' ; alter database rename file '/dev/rcoredo20_lv' to '/dev/rkcoredo20_lv' ; alter database rename file '/dev/rcoredo21_lv' to '/dev/rkcoredo21_lv' ; alter database rename file '/dev/rcoredo22_lv' to '/dev/rkcoredo22_lv' ; alter database rename file '/dev/rcoredo23_lv' to '/dev/rkcoredo23_lv' ; alter database rename file '/dev/rcoredo24_lv' to '/dev/rkcoredo24_lv' ; alter database rename file '/dev/rcoredo25_lv' to '/dev/rkcoredo25_lv' ; alter database rename file '/dev/rcoredo26_lv' to '/dev/rkcoredo26_lv' ; alter database rename file '/dev/rcoredo27_lv' to '/dev/rkcoredo27_lv' ; alter database rename file '/dev/rcoredo28_lv' to '/dev/rkcoredo28_lv' ; alter database rename file '/dev/rcoredo29_lv' to '/dev/rkcoredo29_lv' ; alter database rename file '/dev/rcoredo30_lv' to '/dev/rkcoredo30_lv' ; 특별히각 Thread 별로사용중인 stby redo log file 이라면 Rename 되지않고 ORA-01621 cannot rename member of current log if database is open 에러가발생한다. SQL> SELECT GROUP#, STATUS FROM V$LOG; GROUP# STATUS ---------- ---------------- 1 CLLEARING 3 CLEARING 2 CLEARING.. 16 CLEARING_CURRENT.. 22 CLEARING - 19
23 CLEARING_CURRENT 이것은 Online 중이여서 Drop 을시도하게되면 ORA-01623 log string is current log for instance string (thread string) - cannot drop 에러가발생하게된다. 이런상황이라면다음과같이조치한다. 1. Stop Redo Apply SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 2. Set STANDBY_FILE_MANAGEMENT to MANUAL. SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL'; 3. Clear the Online Redo Logfile Group SQL> ALTER DATABASE CLEAR LOGFILE GROUP 16; SQL> ALTER DATABASE CLEAR LOGFILE GROUP 23; 4. Drop the Online Redo Logfile Group SQL>ALTER DATABASE DROP LOGFILE GROUP 16; SQL>ALTER DATABASE DROP LOGFILE GROUP 23; 만약에 step 3 을생략하였을경우 ORA-01624 while droping the Online Redolog Group with Status CLEARING 가발생할수있다. 5. Standby redo log 재생성 SQL> ALTER DATABASE ADD STANDBY LOGFILE '/dev/rkcoredo16_lv' SIZE 700M BLOCKSIZE 512 REUSE; SQL> ALTER DATABASE ADD STANDBY LOGFILE '/dev/rkcoredo23_lv' SIZE 700M BLOCKSIZE 512 REUSE; 6. Set STANDBY_FILE_MANAGEMENT to AUTO SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'; 7. Start Redo Apply: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 6. init parameter 변경 *.log_file_name_convert='/dev/r','/dev/rk' - 20
4.13.2. Standby db start시 Temporary tablespace 재생성안되는문제기본적으로 Guard 에서 temporary tablespace 는 primary 와 stby 가동기화되지않는다. 아래메시지는이미 dictionary 에는 temporary tablespace 가등록되어있는데실제 raw device 손실로 db open 시 temp 메카니즘인재생성이앆되는문제이다. 이것은 dd if=/dev/zero 로빈 raw device 를만들어도마찬가지경우이므로 database open(real time apply 중 ) 후해당 tempfile 메뉴얼하게삭제후 add temp 하면된다. ** 에러메시지 File 1004 not verified due to error ORA-01122 Read of datafile '/dev/rkdata014_lv' (fno 1005) header failed with ORA-01210 Hex dump of (file 1005, block 1) in trace file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_dbw0_336322.trc Corrupt block relative dba: 0x00c00001 (file 1005, block 1) Completely zero block found during datafile header read Rereading datafile 1005 header failed with ORA-01210 Hex dump of (file 1005, block 1) in trace file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_dbw0_336322.trc Corrupt block relative dba: 0x00c00001 (file 1005, block 1) Completely zero block found during datafile header read Errors in file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_dbw0_336322.trc: ORA-01186: file 1005 failed verification tests ORA-01122: database file 1005 failed verification check ORA-01110: data file 1005: '/dev/rkdata014_lv' ORA-01210: data file header is media corrupt File 1005 not verified due to error ORA-01122 Read of datafile '/dev/rkdata017_lv' (fno 1006) header failed with ORA-01210 Hex dump of (file 1006, block 1) in trace file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_dbw0_336322.trc Corrupt block relative dba: 0x00400001 (file 1006, block 1) Completely zero block found during datafile header read Rereading datafile 1006 header failed with ORA-01210 Hex dump of (file 1006, block 1) in trace file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_dbw0_336322.trc Corrupt block relative dba: 0x00400001 (file 1006, block 1) Completely zero block found during datafile header read Errors in file /oracle/db/diag/rdbms/stby/stby1/trace/stby1_dbw0_336322.trc: ORA-01186: file 1006 failed verification tests ORA-01122: database file 1006 failed verification check ORA-01110: data file 1006: '/dev/rkdata017_lv' - 21
ORA-01210: data file header is media corrupt File 1006 not verified due to error ORA-01122 Dictionary check complete Cannot re-create tempfile /dev/rkdata012_lv, the same name file exists Cannot re-create tempfile /dev/rkdata013_lv, the same name file exists Cannot re-create tempfile /dev/rkdata014_lv, the same name file exists Cannot re-create tempfile /dev/rkdata015_lv, the same name file exists Cannot re-create tempfile /dev/rkdata016_lv, the same name file exists Cannot re-create tempfile /dev/rkdata017_lv, the same name file exists Database Characterset is UTF8 No Resource Manager plan active Starting background process GTX0 ** 조치방법 Temp file drop alter database tempfile '/dev/rkdata012_lv' drop; alter database tempfile '/dev/rkdata013_lv' drop; alter database tempfile '/dev/rkdata014_lv' drop; alter database tempfile '/dev/rkdata015_lv' drop; alter database tempfile '/dev/rkdata016_lv' drop; alter database tempfile '/dev/rkdata017_lv' drop; Temp file add ALTER TABLESPACE TEMP_KAD ADD TEMPFILE '/dev/rdata016_lv' SIZE 20735M REUSE AUTOEXTEND OFF; ALTER TABLESPACE TEMP_KAD ADD TEMPFILE '/dev/rdata015_lv' SIZE 20735M REUSE AUTOEXTEND OFF; ALTER TABLESPACE TEMP_KAD ADD TEMPFILE '/dev/rdata014_lv' SIZE 20735M REUSE AUTOEXTEND OFF; ALTER TABLESPACE TEMP_KIN ADD TEMPFILE '/dev/rdata017_lv' SIZE 20735M REUSE AUTOEXTEND OFF; ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/rdata013_lv' SIZE 20735M REUSE AUTOEXTEND OFF; ALTER TABLESPACE TEMP ADD TEMPFILE '/dev/rdata012_lv' SIZE 20735M REUSE AUTOEXTEND OFF; - 22
5. 참고문서 Maximum Availability Architecture - Oracle Active Data Guard Oracle Data Guard 11g Release 1 Oracle Data Guard Concepts and Administration 11g Release 2 (11.2) Oracle Grid Infrastructure Installation Guide 11g Release 2 (11.2) for AIX Operating System Oracle Database 11g New Features Availability RAC Assurance Support Team: RAC Starter Kit and Best Practices (AIX) - 23