Microsoft PowerPoint - Tech-iSeminar_Standby_DataGuard.ppt

Transcription

1 Standby Database 와 Physical Data Guard Getting the most out of MetaLink 이희열, 이은지, 문상준 한국오라클 ( 주 ) 제품지원실 오라클은 Oracle9i부터 Data Protection과 Disaster Recovery 솔루션으로서 Data Guard를제공합니다. 이는기존의 Standby db를계승하고있지만기능에있어서는매우많은혁신과변경이이루어졌습니다. 본세미나는이러한 Data Guard의핵심적인주요변경사항과이를통해어떻게하면 data의손실을최소화할수있는지에대하여설명합니다. 그럼 Standby database 와 Physical Data Guard" 세미나를시작하겠습니다.

2 Contents 1. Data Guard Overview 2. Data Guard Architecture 3. Data Guard New Features 4. Log Transport Services 5. Log Apply Services 6. Role Management Services 7. Miscellaneousness 본세미나는다음과같이구성되었습니다. 먼저 Data Guard 란무엇인가에대해알아보고 Data Guard안에포함된새로운 New feature들에대해서설명합니다. 그리고 Data Guard 의핵심서비스인아래의내용에대하여자세히알아봅니다. - Log Transprt Services - Log Apply Services - Role Management Services 또한마지막으로 Data Guard 를구성하고운영함에있어고려해야할사항들에대해서알아봅니다. 이 Seminar 의모든내용은 Oracle 9i Release 2 를기반으로합니다.

3 Data Gurard Overview Enhances high availability and disaster protection Provides a single uniform management interface Automates the creation and configuration of physical standby databases Supports failover and graceful switchover Safeguards against physical corruption Provides configurable log transport services Provides configurable log apply services Provides monitoring, alert and control mechanisms Oracle 9i Data Guard 는다음과같은요소들을제공합니다. 1. 분산처리구성 (distributed computing configuration) 을통한가용성 (availability) 과데이터보호성 (disaster protection) 을더욱향상시켰습니다. - 는 disaster recovery solution으로서 primary database와 physical standby database를하나로묶어구성하였으며쉽게관리되도록디자인되었습니다. 또한하나의 Data Guard 안에포함된 Primary database 와 standby database들은종종지리적으로분산되도록구성되어지는데이들은 Oracle Net에의하여연결됩니다. 2. 단일관리인터페이스를제공합니다. - Data Guard broker를통해서 Database resource와 application resource를관리하고통제합니다. - Data Guard broker는 graphical user interface( Manager ) 와 command-line interface를제공합니다. 3. 자동으로 Physical standby database를생성하고설정하는기능을제공합니다. - Data Guard broker는 physical standby database를자동으로생성하고설정하는기능을제공합니다 4. Switchover와 Failover 기능을제공합니다 - Switchover는 patch적용과같은전형적인관리작업수행시 database의 downtime 시간을줄이는데큰도움을줍니다.

4 Data Guard Architecture Manager Graphical User Interface or Command-Line Interface Online Redo Logs Primary Database Archived Logs Oracle Data Guard Broker Log Transport Services Standby Site n Standby Site 2 Standby Database Standby Oracle Site 1 Standby Data Guard Standby Database Oracle Broker Redo Standby Data Guard Standby Logs Database Oracle Broker Redo Data Guard Standby Logs Broker Redo Archived Logs Log Logs Archived Apply Services Log Logs Archived Apply Logs Services Log Apply Oracle Net Services Data Guard는하나의 Primary database와최대 9개까지의 Standby database로구성될수있습니다. 그리고 Standby database에는 Physical Standby database와 Logical Standby database 두가지형태가있습니다. Physical Standby database는 primary database의 redo log를 standby database에직접적용하는형태이고, Logical Standby database는 LogMiner Technology를사용하여 redo log의내용을 SQL statement 형태로전환하여이를적용하는방식입니다. 이세미나에서는오직 Physical standby database와함께구성되는 Data Guard만을언급하겠습니다. 위 Architecture를살펴보면 Data Guard는다음과같은구성요소로이루어져있습니다. 1. Data Guard Broker - 이것은 Data Guard를통합관리하는데있어편리성을제공하기위해제공되는구성요소입니다. 그러나이것은 Data Guard를구성하기위한필수요소는아닙니다. 2. Log Transport Services - Log transport services는 Primary database의 Redo log를 Standby database에전송하여 archived redo log file또는 standby redo log file의형태로저장함으로써 standby database가이를사용가능하도록만들어줍니다. 3. Log Apply Services - Log apply services는 Log transport services에의해전송된 archived redo log나, Standby Redo Log를 switching 함으로써발생된 archived redo log을 Media Recovery방법을이용하여 Standby database에적용합니다. 4. Standby Redo Log - Log transport services가 archived redo log보다더작은단위로 online redo log를전송하도록해줍니다.

5 Physical Standby Database A physical standby is physically identical to the primary The log transport and log apply functionality that used to be part of Automated Standby Databases, is now contained in separate layers named Log Transport Services and Log Apply Services Database role reversal is now supported, and is implemented in the Role Management Services layer Data Guard is the term used to collectively refer to all of the components described above Physical standby database 는물리적으로 primary database 와동일합니다. 그래서 primary database의변동사항을 redo log를사용하여 Standby database에적용할때 physical ROWID를사용하는 BLOCK-FOR-BLOCK 방식을사용합니다. 그러므로 Physical standby database는 managed recovery mode 동안에는 READ/WRITE 방식으로 open될수없습니다. 기존의 Standby database(8.0, 8i) 에서도 Log transport 기능과 log apply 기능을내재하고있었습니다. 그러나 부터는이러한기능을 Log Transport Services와 Log Apply Services라는이름의별도의 layer로독립시켜더욱그기능을강화하고향상시켰습니다. 또한기존의 Standby database(8.0, 8i) 는 Primary database와 Standby database가그 Role을서로자유롭게바꿀수없었습니다. 오직 Primary database 복구불능시에 Standby database를 Primary database Role로전환하는 1 가지방법 (Failover) 만을제공하였습니다. 그러나 는이제 Data손실없이 Primary와 Standby의 Role의전환 (Switchover) 을자유롭게해줍니다. 이러한기능을 Role Management Services라부릅니다.

6 Physical Standby Database New Features 1) Database protection modes Oracle9I R1(9.0.1) Guaranteed protection Instant protection Rapid protection Delayed protection Oracle9I R2(9.2) Maximum Protection Maximum Availability Maximum Performance 2) Database switchover 3) Archive gaps are automatically detected and transmitted 4) Adding new datafiles(standby_file_management) Oracle9i Physical Standby database의 New Feature들을살펴보도록하겠습니다. 1. Database protection modes - Primary database와 Standby db사이의 Data Loss를어느정도까지허용할것인가에따라몇가지 mode로 Data Guard를설정할수있습니다. protection mode는 Oracle9i R1(9.0.1.x) 과 Oracle9i R2(9.2.0.x) 사이에큰차이가있습니다. 위표에서보시는바와같이 R1에서는 Guaranteed, Instant, Rapid, Delayed 4가지 Mode로구분하였으나 R2에서는크게 Maximum Protection, Maximum Availability, Maximum Performance 3 가지 Mode로구분합니다. 이는좀더간단하고편리하게그리고명확하게관리할수있도록하기위해서이와같이 data protection mode를변경한것입니다. 굳이둘사이를 Mapping해본다면 Guaranteed는 Maximum Protection과 Instant 는 Maximum Availability과, 그리고 Rapid와 Delayed는 Maximum Performance과동일하다고볼수있습니다. 2. Database Switchover - Primary database는 Standby database중하나와그 Role을바꿀수있습니다. 즉 Primary db는 Standby db가되고해당선택된 Standby db는 Primary db가되는것입니다. 이때 Data의손실은전혀발생하지않습니다. 필요하다면처음상태로다시되돌릴수도있습니다. 3. Archive gaps are automatically detected and transmitted - Archive gap들은자동으로탐지되고전송됩니다. 이기능은 Log transport services와 Log apply services 양쪽모두에구현되어있습니다. FAL_CLIENT 와 FAL_SERVER parameter를설정함으로써이기능을활성화시킬수있습니다. 4. Adding new datafiles - DBA가 STANDBY_FILE_MANAGEMENT 와 DB_FILE_NAME_CONVERT initialization parameter를설정해놓기만한다면 Primary db에새로운 datafile을추가했을때 Standby db에서도자동으로새로운 Datafile이추가됩니다. 기존 Standby db(8.0, 8i) 는 Primary db에새로운 datafile을추가하였을때반드시이에상응하는 datafile인 standby db에도 manual하게생성시켜주어야만했습니다.

7 Physical Standby Database New Features continued 5) Background managed recovery mode 6) Parallel recovery 7) Standby redo logs are introduced 8) Archiver process(arcn) is available on standby db 9) Cascading standby database( 9iR2) 5. Background managed recovery mode - Physical Standby database는 Managed recovery를수행하기위해서 background process(mrp) 를제공합니다. 이 Process는 managed recovery 명령을수행한 foreground process에게 control을넘겨주어다른작업을수행할수있도록합니다. 6. Parallel recovery - Log apply services에서 managed recovery시 parallel로수행하여좀더빠르게 recovery를수행할수있도록해줍니다. Parallel recovery를수행하기위해서는다음과같은명령을수행합니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 8; 7. Standby redo logs are introduced - Primary database에서전송된 redo data는 standby redo log또는 archived redo log 형태로 standby db쪽에저장될수있습니다. 8. Archiver process(arcn) is available on standby database - Standby database에서 standby redo log를 archiving하기위해서 archiver process(arcn ) 가필요합니다. ARCn은해당 LOG_ARCHIVE_DEST_n 에지정된위치에 archive redo log를생성시키며이위치는 local directory일수도있고 remote standby database의 directory일수도있습니다. 9. Cascading standby database( 9iR2) - cascading standby database는 primary database가아닌다른standby database에서 redo information을받는 standby database입니다. Primary system에서의부하를감소시키기위해서 cascading standby database를구현할수있습니다. ============ redo ============ redo ============ PRIMARY db Standby db A Standby db B ============ ============ ============ cascading standby db

8 Log Transport Services In Oracle9i, the log transport services have become a separate component The new log transport services Offer different log transport mechanisms Handle and report log transport errors Retrieve lost logs after system failure The main benefits of the new services are greater flexibility and resilience to data loss Log transport services는 primary database의 data가 standby database에서사용가능하도록자동으로 primary database의 online redo log를 standby database에복사합니다. Log transport services는 redo log가전송될 destination과각 destination에대해반드시 archiving이필요한지에대한정의를하는데있어융통성을제공합니다. 이서비스는또한 I/O failure handling과 transmission restart 기능을제공합니다. Archiver process(arcn) 또는 Log writer process(lgwr) 가 online redo log를각 Destination에전송하며총 10개의 destination까지설정할수있습니다. Destination은 initialization parameter(log_archive_dest_n) 를통해설정되며적어도 1개의 destination은반드시 local directory에설정되어야합니다. Remote destination은 network services configuration file(tnsnames.ora) 안에정의된 Oracle Net service name 을사용하여설정됩니다. Remote destination이 standby database라면 standby database는반드시 mount 상태나 Read-only mode로 Open되어있어야합니다. Standby database에대한 Network connection이끊기면 network connection을재설정하여 log archiving을계속하도록해줍니다. Oracle9i에서는많은 network failure를자동으로처리해줍니다. Note: Standby database 상의 Remote File Services(RFS) process는 primary database의 LGWR 또는 ARCn로부터 Redo log 전송받아 local disk상에기록합니다.

9 Transactions Log Transport Services Architecture LGWR (Synchronous/Asynchronous) Oracle Net RFS Affirm/ NoAffirm MRP/ LSP Physical/Logical Standby Database Primary Database ARCH (Synchronous) Online Redo Logs FAL Standby Redo Logs ARCH Backup / Reports Transform Redo to SQL for SQL Apply Archived Redo Logs Archived Redo Logs Log Transport Services를수행하는구성요소들은다음과같습니다. 1. Log Writer Process(LGWR) - LGWR은 Primary database의 transaction redo log를 Local online redo logfile에 Write 합니다. 또한편 Oracle Net을통하여 online redo의내용을 Standby database에전송하기도합니다. 2. Archiver Process(ARCn) - Primary database의 ARCn은 Local online redo log를 archiving하는역할을합니다. 또한편 Oracle Net을통하여 Remote에있는 Standby database에 Archived redo log를전송하기도합니다. 3. Remote File Services Process(RFS) - RFS는 standby database에서동작하는 process로서 Primary database의 LGWR또는 ARCn에서전송한 Redo log를받아 Local disk에저장하는역할을수행합니다. 이때 RFS는 LGWR로부터받은 redo log는 Standby redo log에 ARCn으로부터받는 redo log는 Archived redo log의형태로저장하게됩니다. 4. Standby Redo Log - Standby redo log는 Primary database의 LGWR로부터전송된 redo log만을저장하기위한저장소이다. LGWR은 ARCH와는달리 primary database에서발생한 Transaction단위 (commit) 의 redo log들을전송하기때문에 Standby database에서는이러한 redo log들을모아저장하기위한저장소가필요했고이를위해고안된것이 Standby Redo log이다. Note : Physical standby database가 managed recovery mode일경우 Log transport services는 Dedicated server process를사용하여해당 physical standby db에접속해야합니다.

10 Data Protection Modes Maximum Protection Maximum Availability Maximum Performance 는다음 3 가지 protection mode 중하나로운영됩니다. 즉 maximum protection mode, maximum availability mode 그리고 maximum performance mode 입니다. 이 3가지는각각 data protection과 data availability, 그리고 primary database의 performance 사이의서로다른균형점을제공합니다.

11 Data Protection Modes Maximum Protection The highest level of data protection No data loss No data divergence The highest potential impact on the performance and availability of the primary database. Maximum Protection mode는가장높은레벨의 data protection을제공합니다. Primary database transaction은 redo data가 standby database중적어도하나에쓰여질때까지 commit되지않습니다. 만약에 Primary database가 standby database중어느하나에도 redo data를 write하지못하면 primary database를강제로 shutdown함으로서 standby database에반영되지못한 data가발생하지않도록합니다. 이 mode는데이터손실을조금이라도용납하지않습니다. 대신 primary database의 performance와 availability에지대한영향을미칠수있습니다.

12 Data Protection Modes Maximum availability The next highest level of data protection. A primary database transaction will not commit until the redo data needed to recover that transaction is written to at least one standby database The primary database will not shut down if it is unable to write the redo data to at least one such standby database. Instead, the protection mode will be temporarily lowered to maximum performance mode This mode guarantees no data loss unless the primary database fails while it is in maximum performance mode. The highest level of data protection that is possible without affecting the availability of the primary database. Maximum Availability mode는 Maximum Protection mode 다음으로가장높은레벨의 data protection mode 입니다. 이 Mode 역시 primary database에서발생된 transaction redo data가 standby database중적어도하나에 Write될때까지 Commit되지않습니다. 그러나이 mode는 maximum protection mode와는달리 primary database에서발생된 redo data가 Standby database중어느하나에도 write되지못하였을경우 primary database가 shutdown 되지않습니다. 대신에 fault가모두수정되어정상적인상태가될때까지일시적으로 protection mode가 Maximum Performance mode로낮춰집니다. Maximum availability mode는일시적으로 maximum performance mode로전환된동안에 primary database가 fail되지만않는다면 no-data-loss를보장합니다. 이 mode는 primary database의 availability에영향을주지않으면서가장높은 data protection을구현할수있습니다.

13 Data Protection Modes Maximum performance The default protection mode. A primary database transaction will not wait to commit until the redo data needed to recover that transaction is written to a standby database. some data might be lost if the primary database fails and the redo data needed to recover committed transactions is not available at any standby database. The highest level of data protection that is possible without affecting the performance or availability of the primary database. Maximum Performance mode는 Data Guard의 default protection mode 입니다. 이 mode는 redo data가 standby database중어느하나에 write될까지 commit을기다리지않습니다. 그래서 Primary database가 fail된동시에 standby database중어느것도 redo data를가지고있지않게되면데이터가손실될수있습니다. Maximum Performance mode는 Primary database의 availability와 performance에영향을주지않고운영가능한 data protection mode입니다.

14 Log Transport Services Capabilities Permission Destination Transmission Reception Failure Resolution 이제 Log Transport Services 를좀더세분화하여알아보도록하겠습니다. Log Transport Services 는다음 5 가지의 Category 로분류됩니다. 1. Permission 2. Destination 3. Transmission 4. Reception 5. Failure Resolution

15 Log Transport Services Capabilities: Permission Setting Permission to Archive Redo Logs REMOTE_ARCHIVE_ENABLE TRUE : both FALSE : both SEND : primary RECEIVE : standby Remote destination 에 online redo log를 archiving하기위한 Permission 설정은 REMOTE_ARCHIVE_ENABLE parameter를사용하여기술될수있다. 일반적으로 Data Guard환경에서 primary database와 standby database 모두 True로설정한다. 그러나각각독립적으로 remote archive redo log의 sending과 receiving을관리하려면 SEND와 RECEIVE option을사용할수있다. 1. REMOTE_ARCHIVE_ENABLE=TRUE 는 primary database와 standby database 양쪽모두에설정되며 Primary database에서 redo log를 standby database에 sending하는것을허용하고 Standby database에서는 Primary database로부터의 archiving을위해서 redo log를 receving하는것을허용합니다. 2. REMOTE_ARCHIVE_ENABLE=FALSE 는 primary database와 standby database 양쪽모두에설정되며 redo log의 sending과 receiving 모두를허용하지않습니다. 3. REMOTE_ARCHIVE_ENABLE=SEND는 Primary database에설정되어 Primary database가 redo log를 standby database로 sending하는것을허용합니다. 4. REMOTE_ARCHIVE_ENABLE=RECEIVE 는 Standby database에설정되어 Standby database가 redo log를 primary database로부터 receiving하는것을허용합니다.

16 Log Transport Services Capabilities: Destination Local A local file system location. Remote Physical standby database Logical standby database Archive log repository Cross-instance archival database Log Transport Services의 Destination은 Local host 상에위치할수도있고 Remote host 상에위치할수도있습니다. Local destination - Local file system 상에있는 Destination 입니다. LOG_ARCHIVE_DEST_1= LOCATION=/arc_dest Remote destination - Oracle Net service name을사용하여접근할수있는 remote host 상에있는 Destination입니다 LOG_ARCHIVE_DEST_2 = SERVICE=standby1 - Remote destination은다음과같은몇가지형태들이있습니다. 1) Physical standby database - 재난을대비한 primary database와동일한복사본 database 2) Logical standby database - Archived redo log를받아 SQL transaction으로변환뒤이를적용하는방식의 standby database. 3) Archive log repository - 단순히 archived redo log만을저장하기위한 off-site destination 입니다. 오직 controlfile로만구성되어있습니다. 4) Cross-instance archival database - RAC 환경에서 primary database의다른한 node를 Destination으로사용하는것입니다.

17 Log Transport Services Capabilities: Transmission The Process that Transmits Redo Data Attribute Example Default {ARCH LGWR } LOG_ARCHIVE_DEST_3= SERVICE=stby1 LGWR ARCH Network Transmission Mode SYNC network transmission method ASYNC network transmission method Attribute Example Default {SYNC[=NOPARALLEL PARA LLEL] ASYNC [=blocks] } LOG_ARCHIVE_DEST_3= SERVICE=stdby1 LGWR SYNC SYNC 다음과같은요소를통하여 Primary database에서 standby database로전송되는 redo log의형태와전송방식을선택할수있습니다. 1. The process that will perform the log transmission operation - Redo log를전송하기위해서 Log Writer process(lgwr) 나 Archiver process(arcn) 을사용할수있습니다. 2. The method of transmitting redo logs over the network - Log Writer process(lgwr) 를사용할때는 synchronous 또는 asynchronous network transmission 방식을사용할수있습니다. LGWR는 online redo logs 의형태로 red log를 standby database에전송합니다. -Archiverprocess(ARCn) 를사용할때는오직 synchronous transmission 방식만을사용할수있습니다. ARCn는 redo log를 archived redo logs 형태로전송합니다.

18 Log Transport Services Capabilities: Reception Reception 은 primary database에서전송된 redo log가 Standby database 내의어느곳에저장될것인가를결정하게해줍니다. 위그림에서보시는바와같이 Primary database에서전송된 redo log를 Standby database내의 RFS process가받아서 Standby redo log또는 archived redo log 형태로 standby database 내에저장하게됩니다. Standby redo log는 Primary database의 LGWR이전송한 redo log를저장하기위해서사용됩니다. standby redo log는별도의 redo log file group으로서 physical standby database role일경우에만사용됩니다.

19 Log Transport Services Capabilities: Reception continued Standby Redo Logs Maximum protection mode and maximum availability mode Remote file server process (RFS) - Receive the Redo data and write it to the standby redo logs Standby redo logs must be archived before the data can be applied to the standby database The size of a standby redo log must exactly match the primary database online redo logs. Number of a standby redo log groups: minimally one more standby redo log group than the primary database Standby Redo Log에대해서좀더자세히알아보도록하겠습니다. Data Guard가 maximum protection mode나 maximum availability mode일경우는반드시 standby redo log를사용합니다. -Primary database의 Log Writer process(lgwr) 가전송한 redo log를 Standby database의 Remote File Service process(rfs) 가받아서 standby redo log에저장합니다. -변경 data들이 standby database에적용되기위해서는먼저 standby redo log를 archiving하여야합니다. 즉 standby redo 안의내용은 archived redo log의형태를거쳐서 standby database에적용되게된다는의미입니다. 그러므로 standby database는반드시 Archiver process가활성화되어있어야합니다. 이를위해서 LOG_ARCHIVE_START=TRUE를항상설정해주시길권장합니다. 또한 Standby database의 ARCn 에의해서생성되는 archived redo log는 LOG_ARCHIVE_DEST_n 에정의되어있는 destination에생성되며 file name은 LOG_ARCHIVE_FORMAT의설정에따릅니다. -Standby redo log의크기는는 primary database의 online redo log 크기와정확히일치해야합니다. - 그러나 standby redo log group의개수는최소한 primary database의 online redo log group보다 1개더많도록설정합니다. 경우에따라서는 1개이상더추가할수도있습니다. - Creating Standby Redo Log Groups SQL> ALTER DATABASE ADD STANDBY LOGFILE 2> ( /oracle/dbs/log1c.rdo, /oracle/dbs/log2c.rdo ) SIZE 500K; SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 2> ( /oracle/dbs/log1c.rdo, /oracle/dbs/log2c.rdo ) SIZE 500K; - Adding Standby Redo Log Members to an Existing Group SQL> ALTER DATABASE ADD STANDBY LOGFILE MEMBER /oracle/dbs/log2b.rdo TO GROUP 2; - Standby Redo log 생성시고려해야할것 -MAXLOGFILES -LOG_FILES -MAXLOGMEMBERS

20 Log Transport Services Capabilities: Reception continued Storage Locations for Archived Redo Logs STANDBY_ARCHIVE_DEST(RFS) LOG_ARCHIVE_FORMAT Storage Locations for Standby Redo Logs LOG_ARCHIVE_DEST_n ( ARCn) LOG_ARCHIVE_FORMAT Writing Redo Data to Disk Log archiving disk write I/O operations: [NO]AFFIRM Sync or Async Primary database에서 ARCn 가 redo를전송하면 Standby database에서 RFS process가이를받아 Archived redo log의형태로 Archive destination 에저장하게됩니다. 이때저장될 archive destination은 STANDBY_ARCHIVE_DEST을통해서설정됩니다. Standby redo log의내용은 ARCn 을통해서 archiving되어지며이때 LOG_ARCHIVE_DEST_n 에설정된 Destination에 Archived redo log가생성됩니다. Log archiving disk write I/O operation은 Synchronous 하게또는 Asynchronous하게수행될수있습니다. 즉 Primary database에서전송한 redo data가 Standby database쪽의 network buffer에까지도달후에이것이 synchronous 방식으로 disk에 write 되거나또는 asynchronous 방식으로 disk에 write 될수있습니다. Primary database의 LOG_ARCHIVE_DEST_n 안에 AFFIRM 또는 NOAFFIRM attribute를사용하여이와같은사항을설정할수있습니다. LOG_ARCHIVE_DEST_2= SERVICE=stby MANDATORY LGWR SYNC AFFIRM

21 Log Transport Services Capabilities: Failure Resolution Retrying the archiving operation to a failed destination after a specified period of time, up to a limited number of times REOPEN attribute MAX_FAILURE attribute Using an alternate or substitute destination ALTERNATE attribute Primary database로부터 Standby database로의 log archiving이실패하는경우 Primary database에서어떠한 Action들이취해져야하는지를기술합니다. Primary database로부터취해질수있는 action들은다음과같습니다. 1) 실패한 Destination에대하여일정주기로 archiving operation을재시도합니다. 이때재시도의최대횟수를지정할수있습니다. - REOPEN : remote destination에대한 archiving이실패후에재시도하기위해기다려야하는최소한의 Interval. Default값은 300초이다. - MAX_FAILURE : 한 Destination에대하여실패할수있는최대 archiving operation의횟수 LOG_ARCHIVE_DEST_1= LOCATION=/arc_dest REOPEN=5 MAX_FAILURE=3 2) 다른 Destination으로대체하여 archiving을하도록합니다. - ALTERNATE : archiving 실패시에 alternate destination에 archiving하도록합니다. LOG_ARCHIVE_DEST_1= LOCATION=/disk1 MANDATORY ALTERNATE=LOG_ARCHIVE_DEST_2 LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_2= LOCATION=/disk2 MANDATORY LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_2=ALTERNATE

22 Log Transport Services Interfaces Database Initialization Parameters LOG_ARCHIVE_DEST_n SQL Interface ALTER SYSTEM[SESSION] SET. Log Transport Services 에대한설정은 LOG_ARCHIVE_DEST_n 을사용하여이루어집니다. 이 Parameter는 Initialization prarameter로설정되어 database restartup시에수행될수도있고 ALTER SYSTEM SET 과같은 SQL command로 dynamic하게설정될수도있습니다. LOG_ARCHIVE_DEST_n 안에기술될수있는많은attribute들은 Oracle9i R2 Data Guard Concept and Administration document, Chapter 12를참조하시기바랍니다. Example Modifying Destination Parameters at the System and Session Level SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6= SERVICE=stby REOPEN=8 ; SQL> ALTER SESSION SET LOG_ARCHIVE_DEST_6= SERVICE=stby2 REOPEN=10 ; Example Adding Destination Attributes Incrementally SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6= SERVICE=stby1 REOPEN=8 ; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_7= SERVICE=stby2 NOREOPEN ; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6= MANDATORY LGWR DELAY ; Example Clearing a Destination Specification SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6= SERVICE=stby1 REOPEN=8 ; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_7= SERVICE=stby2 NOREOPEN ; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_6= ;

23 Configuring Log Transport Services & Protection Modes Maximum Protection Maximum Availability Maximum Performance Redo archival process LGWR LGWR LGWR or ARCH Network transmission mode SYNC SYNC ASYNC when using LGWR process. Not applicable when using ARCH process Disk write option Standby redo logs required? Database type AFFIRM Yes Physical AFFIRM Required for physical standby databases only Physical and logical NOAFFIRM Required for hysical standby databases using the LGWR process Physical and logical 1) Maximum Protection mode LOG_ARCHIVE_DEST_3= SERVICE=stby1 LGWR SYNC AFFIRM LOG_ARCHIVE_DEST_STATE_3=ENABLE SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTEXTION; 2) Maximum Availability mode LOG_ARCHIVE_DEST_3= SERVICE=stby1 LGWR SYNC AFFIRM LOG_ARCHIVE_DEST_STATE_3=ENABLE SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; 3) Maximum Performance mode LOG_ARCHIVE_DEST_3= SERVICE=stby1 LGWR ASYNC NOAFFIRM LOG_ARCHIVE_DEST_STATE_3=ENABLE or LOG_ARCHIVE_DEST_3= SERVICE=stby1 LOG_ARCHIVE_DEST_STATE_3=ENABLE SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

24 Log Transport Services Monitoring Gathering Redo Log Archival Information Step 1 Determine the current redo log sequence numbers. Step 2 Determine the most recently archived redo log. Step 3 Determine the most recently archived redo log file at each destination. Step 4 Find out if logs have been received at a particular site. Step 5 Trace the progression of archived redo logs on the standby site. Step 1 Current Redo Sequence를얻기위해 Primary database에서다음 Query 수행 SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG; THREAD# SEQUENCE# ARC STATUS YES ACTIVE NO CURRENT Step 2 Primary database 에서가장최근의 Archived redo log 를찾는다 SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG; MAX(SEQUENCE#) Step 3 Primary database에서다음과같은 Query를수행하여각 Destination 별로가장최근의 Archived redo log를찾습니다. SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# 2> FROM V$ARCHIVE_DEST_STATUS 3> WHERE STATUS <> DEFERRED AND STATUS <> INACTIVE ; DESTINATION STATUS ARCHIVED_THREAD# ARCHIVED_SEQ# /private1/prmy/lad VALID standby1 VALID Note: STATUS의값이 VALID 가아닌경우는해당 Destination에 archiving이실패했다는것을의미

25 Step 4 특정 Destination 에서전송받지못한 log가있는지를찾는다. Local Destination ID를 1 Remote Destination ID를 2라고가정한다 SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL WHERE LOCAL.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#); THREAD# SEQUENCE# Step 5 Standby database 쪽으로의 redo archiving 진행과정을살펴보려면 LOG_ARCH_TRACE parameter 를 Primary 와 Standby database 양쪽에설정해야합니다.

26 Log Apply Services: Standby database mode Managed recovery mode In this mode, log transport services archive logs to the standby site, and log apply services automatically apply these logs Read-only mode If you want to use the standby database for reporting purposes, then open it in read-only mode in a Data Guard environment. Physical Standby database 는 managed recovery mode 와 read-only mode 두가지로운영될수있습니다. Managed recovery mode상에서 Log Transport Services는 redo log를 standby site에 archiving하고 Log Apply Services는자동으로이 log들을 standby database에적용합니다. Reporting 목적으로 Standby database를사용하기원하는경우는 standby database를 Read-Only mode로 Open할수있습니다. Read-only mode로 open되면 Read-only mode가유지되는동안에는 Log Apply Services가 archived redo log를 standby database에적용할수없습니다. Read-only mode로 open되어있는동안에도 Standby database는계속해서 Primary database로부터 Archived redo log를받습니다.

27 Transactions Log Apply Services: Architecture LGWR (Synchronous/Asynchronous) Oracle Net RFS Affirm/ NoAffirm MRP/ LSP Physical/Logical Standby Database Primary Database ARCH (Synchronous) Online Redo Logs FAL Standby Redo Logs ARCH Backup / Reports Transform Redo to SQL for SQL Apply Archived Redo Logs Archived Redo Logs Log Apply Services는자동으로 archived redo log를적용함으로써 Primary database와 Standby database를동기화시킵니다. Log Transport Services를통하여전송된 redo data는 Archived redo log 또는 standby redo log 형태로저장됩니다. 그리고 Log Apply Services는이렇게저장된 redo data를 Standby database에적용합니다. Archived redo log 형태로저장된 redo log는 Managed Recovery Process(MRP) 에의하여 Read되어 Standby database에바로적용됩니다. 그러나 Standby redo log 형태로저장된 redo log는 Archiver process에의하여 archiving된후에 Managed Recovery Process에의해 read되어 standby database에적용됩니다. 그러므로 standby redo log를사용하는경우 standby database는반드시 archiver process를 start시켜야합니다. 이를위해서 LOG_ARCHIVE_START=TRUE를설정하십시요 FAL Client와 FAL Server는 primary database와 standby database사이의 archive gap을자동으로 detect하여해결해줍니다.

28 Log Apply Services: Processes Archiver process(arcn) If standby redo logs are being used, the ARCn process archives the standby redo logs that are to be applied by the managed recovery process. Managed recovery process (MRP) The managed recovery process (MRP) applies information from the archived redo logs or standby redo logs to the standby database. Standby redo log 가사용되면 Standby database 의 ARCn process 가 standby redo log 를 archiving 하는 역할을담당하며이때생성된 archived redo log는 Managed Recovery process(mrp) 에의해 Read되어 Standby database에적용되게됩니다. Managed recovery process(mrp) 는 Primary database에서 Log Transport Services를통하여전송된 Redo data를 Standby database에적용하는역할을합니다. Log Transport Services를통하여전송된 Redo data의형태가 archived redo log 또는 standby redo log 이지만 MRP는오직 archived redo log 형태의 Redo data만을 read하여 standby database에적용합니다. 그러므로 standby redo log 안의내용이 standby database에적용되기위해서는반드시 archiving 작업이선행되어야합니다.

29 Log Apply Services: Starting Managed Recovery ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Log Apply Services는 Foreground session으로서수행될수도있고 background process로수행될수도있습니다. 1) Foreground session 을시작하기위해서는다음과같은명령을수행해야합니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE; Foreground session 을시작하면기본적으로 control 이 command prompt 상으로되돌아오지않습니다. 2) Background process 를시작하기위해서는 DISCONNECT keyword 를사용해야합니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 이명령은분리된서버프로세스를시작하여즉시 control을사용자에게반환합니다. Managed recovery process가 background로 recovery를수행하는동안 RECOVER 명령을수행시켰던 foreground process는다른작업을수행할수있습니다. 이것은 current SQL session을 disconnect시키지않습니다. 3) 만약분리된서버프로세스 (detached server process) 로 log apply services를시작하지않았다면다음과같은명령으로 log apply services를정지시킬수있습니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

30 Log Apply Services: Monitor the recovery process. V$MANAGED_STANDBY SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 2> FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS RFS ATTACHED MRP0 APPLYING_LOG Physical Standby database에서 log apply services와 log transport services를 monitoring하기위해서 V$MANAGED_STANDBY 를 Query합니다. SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 2> FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS RFS ATTACHED MRP0 APPLYING_LOG RFS process가 redo log file sequence number 947에대한 archiving을완료했음을알수있습니다. Archived된 redo log sequence number 946이 Managed recovery operation에의해현재적용되고있음을알수있습니다. Recovery operation은 72 block의 archived redo log중 10번 block을현재recovering하고있습니다. Log apply services가올바르게시작되었는지를검증하기위해서 Standby database에있는 V$MANAGED_STANDBY fixed view를 Query 하십시요. 이 view는 managed recovery mode에있는 Standby database의 recovery process를 monitoring합니다. SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS 2> FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS MRP0 APPLYING_LOG

31 Log Apply Services: Datafile Management PARAMETER FUNCTION DB_FILE_NAME_CONVERT Converts primary database datafile filenames to standby datafile filenames, for example, from tbs_* to standbytbs_*. LOG_FILE_NAME_CONVERT Converts primary database redo log filenames to standby database redo log filenames, for example, from log_* to standbylog_*. STANDBY_FILE_MANAGEMENT When set to auto, this parameter automates the creation and deletion of datafile filenames on the standby site using the same filenames as the primary site. Primary database에서 datafile이생성될경우 physical standby database에서자동으로 new datafile이생성되도록하기위해서는 STANDBY_FILE_MANAGEMENT=AUTO가설정되어야합니다. 만약에 primary와 standby database의 directory 구조가다르면 DB_FILE_NAME_CONVERT parameter를사용하여 convert시킬수있습니다. DB_FILE_NAME_CONVERT= "/disk1/oracle/oradata/payroll/df1", "/disk1/oracle/oradata/payroll/standby/df1", "/disk1/oracle/oradata/payroll", "/disk1/oracle/oradata/payroll/standby/" STANDBY_FILE_MANAGEMENT=AUTO Physical standby database에서 STANDBY_FILE_MANAGEMENT의값이 AUTO로설정되어있으면다음과같은명령문은허용되지않습니다. ALTER DATABASE RENAME ALTER DATABASE ADD/DROP LOGFILE ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER ALTER DATABASE CREATE DATAFILE AS

32 Data Guard Gap Detection and Resolution What Is an Archive Gap? Methods of Gap Resolution: Automatic Gap Resolution FAL (Fetch Archive Log) Gap Resolution Archive Gap 이란 Standby database 가 primary database 에서생성된 archived redo log 를전송받지 못했을경우발생하게되는 missing archived redo log 들을의미합니다. Data Guard는 Automatic 과 FAL(Fetch Archive Log), 두가지의 Gap Resolution 을제공합니다. Automatic 방식은별다른설정 (configuration) 이필요없습니다. 그러나 FAL의경우는 init.ora 안에 FAL_CLIENT와 FAL_SERVER를설정을해주어야만합니다. 이제이두가지 Gap resolution 에대해서자세히알아보도록하겠습니다.

33 Automatic Gap Resolution Implemented during log transport processing The sequence number of the log being archived is compared to the last sequence received by the RFS process on the standby The ARCH process on the primary sends the missing logs to the standby and notifies the LGWR that the gaps have been resolved The ARCH process on the primary database polls all standby databases every minute ( Enhanced in oracle9i R2) Automatic Gap Resolution은 Log transport processing 동안구현되어집니다.(9.0.1 and 9.2.0) Primary database의 LGWR 또는 ARCH가 redo data를 standby database에전송하고이를 Standby database의 RFS가받게되는데받는동안 RFS는현재받고있는 log의 sequence번호와이미받은마지막 log sequence 번호를비교합니다. 만약에 RFS가현재받고있는 log sequence 번호가 last log sequence +1 보다크다는것을발견하면 Primary에게 missing archive log를전송해줄것을요청 (piggyback) 합니다. Missing archive log를요청하는standby destination은이미primay database의log_archive_dest_n 에정의되어있기때문에primary database의arch process가standby에log를전송하고gap이해결되었다는것을 LGWR에게알립니다. Data Guard 9.2부터 Automatic Gap Resolution이좀더향상되었습니다. 위에이미기술된것외에 primary database상의 ARCH process는모든 standby database 상에 archive gap이있는지를 check하기위해서매분마다 polling합니다. 만약 Gap이발견되면 ARCH process가 missing archived redo log를전송합니다. Gap이해결되면해당 site가최신으로 update되었음을 LGWR에게알립니다.

34 FAL Gap Resolution MRP sees the update to the controlfile, it attempts to recover that file If the MRP process finds that the archived log is missing or is corrupt, FAL is called to resolve the gap or obtain a new copy Since MRP has no direct communications link with the primary, it must use the FAL_SERVER and FAL_CLIENT to resolve the gap Both of these parameters must be set in the standby init.ora. FAL_SERVER FAL_CLIENT Standby database의 RFS가 archived log를받으면 archived log file name과 location을 standby controlfile 안에 update합니다. MRP는 controlfile 상의 update를보고 recovery를시도합니다. 만약에 MRP가 archived log가 missing이거나 corrupt되었다는것을발견하면 Gap 을해결하거나 new copy를얻기위해서 FAL이호출됩니다. MRP는 primary와 direct communication link를가지고있지않기때문에반드시 FAL_SERVER 와 FAL_CLIENT parameter를 init.ora안에설정해야합니다. 이두 parameter 모두 standby의 init.ora 안에설정되어야합니다. 이두 parameter에대한정의는다음과같습니다. - FAL_SERVER : primary database listener를가리키고있는 OracleNet service name으로서 standby tnsnames.ora 안에존재한다. 또한 comma로구분하여여러개의 OracleNet service name을기술할수있다. - FAL_CLIENT : standby database listener를가리키고있는 OracleNet service name으로서 primary tnsnames.ora 안에존재한다. MRP 가 gap을발견하면 Primary database를 call하기위해서 FAL_SERVER에정의되어있는 Value를사용한다. 일단Primary와의communication이확립되면MRP process는 FAL_CLIENT value를primary ARCH process에게전달한다. Primary ARCH process는해당 remote archive destination을찾아서 Missing archived redo log를전송한다. 만약 FAL_SERVER에나열된 first destination에서 gap을해결할수없다면다음 destination이시도된다 현재까지 FAL Gap Resolution은오직 Physical Standby database에서만유효하다.

35 Manually Resolving a Gap Query the V$ARCHIVE_GAP view After you identify the gap, query the V$ARCHIVED_LOG on the primary database to locate the archived redo logs on your primary database: Copy the logs returned by the query to your physical standby database and register using the ALTER DATABASE REGISTER LOGFILE command Restart the MRP process. 앞에서언급한바와같은두가지 Gap resolution에의해대부분은자동으로 Gap을해결하게됩니다. 그러나부득이하게 manual하게 gap을해결해야하는경우는다음과같은절차를수행합니다. 1) V$ARCHIVE_GAP view 를 query 합니다. SQL> SELECT * FROM V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# 위 Query 결과는 Thread 1에대해서 Sequence 443에서 446까지 missing archive file들존재하고있음을의미하고있습니다. 2) Gap을확인한후에는 missing archive file들이 primary database의어디에위치하고있는지를찾기위해서다음과같은 SQL문을수행합니다. SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND 2> SEQUENCE# BETWEEN 443 AND 446; NAME /u01/oradata/arch/arch_1_443.arc /u01/oradata/arch/arch_1_444.arc /u01/oradata/arch/arch_1_445.arc

36 3) 위 Query에나타난 archive file들을모두 primary에서 standby로 copy한후에 ALTER DATABASE REGISTER LOGFILE 명령을사용하여등록합니다. SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_443.arc'; SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_444.arc'; SQL> ALTER DATABASE REGISTER LOGFILE '/u01/oradata/stby/arch/arch_1_445.arc'; 4) Missing archive file들이모두 standby controlfile안에등록되면 MRP process를다시시작합니다.

37 Role Management Services Database Roles and Role Transitions Database Switchover Database Failover Database는상호배제적인 PRIMARY 또는 STANDBY Role중하나로운영됩니다. Data Guard는 SQL 명령을통하여동적으로이 Role들을변경하는것을허용합니다. Oracle Data Guard 는다음두가지 Role transition operation 을지원합니다. - Switchover primary database와 standby database중하나사이에 role을서로교환하는 operation -Failover primary database의 failure로인해 standby database를 primary role로변경하는 operation.

38 Role Management Services Failover Operation 1. Previously, this was the only way to move the primary processing role to the standby 2. Failover was often only performed as a result of an unplanned outage of the primary 3. The primary had to be discarded and could not be used as the new standby 4. The system was at risk during the time taken to create a new standby Failover 에대해서알아보도록하겠습니다 1. Oracle9i 이전버전에서는 standby database 의 role 을 primary 로바꿀수있는유일한방법이었습니다. 2. Primary database 의예측치못한사용불능의결과로서종종수행되었습니다. 3. Failover 수행후에는기존의 Primary database 는필요없으므로반드시삭제해야합니다. 4. 새로운 standby database 를구성하는동안 primary database 는잠시 risk 상황에머물게됩니다.

39 Role Management Services Switchover Operation In Oracle9i, primary and standby database can continue to alternate roles This is a planned operation unlike failover No need to recreate a new standby every time the switch is performed Switchover is possible if all these conditions are met: The primary performs a graceful shutdown The archive logs are available The primary s online logs are available Switchover 에대해서알아보도록하겠습니다. 1) 에서는 primary와 standby database가서로 role을바꿀수있습니다. 2) Switchover는 planned operation을위해사용되어질수있습니다. 3) Switching이발생할때마다새로운 standby database를생성할필요가없습니다. 4) 다음과같은조건을만족할때 switchover를수행할수있습니다. - Primary가 graceful shutdown을하였을경우 - Archive log가모두사용가능한경우 - primary database의 online redo log가모두사용가능한경우

40 Role Management Services: Switchover Steps(1) On the current primary database Step 1 Step 2 Step 3 Verify that it is possible to perform a switchover operation. Initiate the switchover operation on the primary database. Shut down and restart the former primary instance. Switchover operation 을수행하는과정은다음과같습니다. 1단계 ) Switchover operation이수행가능한상태인지를살펴본다. Primary database에서 V$DATABASE fixed view의 SWITCHOVER_STATUS의값이 TO STANDBY 이면이 primary database은 standby database로 switchover가능함을의미합니다. 2단계 ) primary database에서 switchover operation 명령문을수행합니다. Primary database를 primary role에서 standby role로변경하기위해서아래명령문을실행합니다. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY; 위명령문의수행이완료되면 primary database는 standby database로전환됩니다. 그리고 switchover operation전에 current control file이 SQL session trace file 형태로 backup 됩니다. 3단계 ) Standby role로변경된 primary database를 shutdown 하고 restart 합니다. SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP NOMOUNT; SQL> ALTER DATABASE MOUNT STANDBY DATABASE; Switchover 과정중이시점에서는모든 database 가 physical standby database 가됩니다.

41 Role Management Services: Switchover Steps (2) On the target physical standby database Step 4 Verify the switchover status in the V$DATABASE view. Step 5 Switch the physical standby database role to the primary role. Step 6 Shut down and restart the new primary database. Switchover operation 수행과정에서 Standby database 에서수행되는과정을살펴봅니다. 단계 4) V$DATABASE에서 standby database의 switchover status를살펴봅니다. Primary database가 standby database로 switchover 되면이에대한변경이 Target standby database에게통보되어지게됩니다. 그리고이때 standby database의 SWITCHOVER_STATUS의값은 SWITCHOVER PENDING 으로바뀝니다. SWITCHOVER PENDING 은 standby database가 standby role에서 primary role로이제막 switching되기시작했다는의미입니다. 단계 5) Physical standby database role을 primary role로전환합니다. Physical standby database를 standby role에서 primary role로전환하기위해서는 manged recovery mode로 mount되어있거나 read-only mode로 open되어있어야합니다. 다음은이를수행하는명령문입니다. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 위명령문수행시에 online redo log가존재하지않는다면자동으로 online redo log를생성시켜줍니다. 그러나 oracle은이보다는미리 manual 하게 online redo log를추가시켜주실것을권장합니다. 6단계 ) Target standby database를적절한 initialization parameter들과함께 restartup 합니다. SQL> SHUTDOWN; SQL> STARTUP; Physical standby database는이제 primary database role로전환됩니다.

42 Role Management Services: Switchover Steps(3) On the new physical standby database Step 7 Start managed recovery operations and log apply services. On the new primary database Step 8 Begin sending redo data to the standby databases. 7단계 ) 이제새로운 physical standby database에서는 managed recovery operation과 log apply services를시작합니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 8 단계 ) 이제새로운 primary database 에서는 standby database 로 redo data 를전송하기시작합니다. SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

43 Role Management Services : Failover Steps(1) Step 1 Identify and resolve any archived redo log gaps. Step 2 Copy any other missing archived redo logs. Step 3 Repeat steps 1 and 2. Step 4 Initiate the failover operation on the target physical standby database. Step 5 Convert the physical standby database to the primary role. Primary database의장애로인해 primary database를더이상사용할수없게된경우에는 Failover를통해서비스를계속할수있습니다. 다음은 Failover를수행하는과정입니다단계1) Archived redo log gap을찾아이를해결합니다. 다음 SQL문을통하여 archived redo log gap을확인합니다. SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; THREAD# LOW_SEQUENCE# HIGH_SEQUENCE# 위 Query 결과를보면 Gap은 archive log 90, 91, 92로이루어짐을알수있습니다. 가능한한이모든 archived redo log file을 target standby database에복사하고이를등록 (register) 해야합니다. 이모든것은각 Thread 단위로수행되어야합니다. SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec1 ;

44 2단계 ) 다른 missing archived redo log가있는지를점검하고가능한한이를 target standby database에복사하여등록합니다. SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) 2> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG; THREAD LAST 현재사용가능한모든database에서target standby database보다더큰sequence 번호를가진 archived redo log가있다면이를모두 target standby database에복사하여등록합니다. SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE filespec1 ; 3 단계 ) Gap 이없어질때까지가능한한 1 단계와 2 단계를반복적으로수행한다. 4단계 ) Target physical standby database 상에서 failover operation을수행합니다. target standby database가 standby redo log로구성되어있고아직 partial archived redo log 를 manual하게등록하지않았다면다음과같은명령문을수행합니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; standby redo log 안의 paritial archived redo log 를 skip 한다면아래와같은명령문을사용합니다. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH 2> SKIP STANDBY LOGFILE; 5 단계 ) Physical standby database 를 primary role 로변경합니다. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; 이명령문수행후에는이 database는더이상 standby database로서사용할수없습니다. original primary database의 redo log도더이상적용될수없습니다. standby redo log는 archived되어나머지 standby database로 copy되어야합니다. copy된후에는 register를거쳐 recovery가수행되어야합니다. original primary database 제거되어야합니다. 그리고 new primary database의 backup을사용하여 physical standby database를새로만들어야합니다.

45 Role Management Services : Failover Steps(2) On the primary database and all remaining standby databases Step 6 Prepare to receive redo logs from the new primary database. On the new primary database Step 7 Shut down and restart the new primary database. Step 8 Optionally, back up the new primary database. 6 단계 ) 다른 standby database 는 new primary database 로부터 redo log 를받기위해준비합니다. Archived standby redo log가다른모든 standby database에적용되면이제이 standby database들은 new primay database로부터 redo log를받아들일준비를합니다. 7 단계 ) New primary database 를 restartup 합니다. failover operation을완료하기위해서 new primary database를 read/write mode로 restartup 합니다. 이때적절한 SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP; 8 단계 ) New primary database 를 backup 합니다.

46 Miscellaneousness 1. Managing Primary Database Events That Affect the Standby Database 2. Recovering After the NOLOGGING Clause Is Specified 3. Standby Database Real Application Clusters Support

47 Managing Primary Database Events That Affect the Standby Database Change Made on Primary Database Add a datafile or create a tablespace Drop or delete a tablespace or datafile Rename a datafile Add or drop online redo logs Alter the primary database control file (using the SQL ALTER DATABASE CREATE CONTROLFILE statement) Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause Change initialization parameter

48 Change Made on Primary Database Add a datafile or create a tablespace Drop or delete a tablespace or datafile Rename a datafile Add or drop online redo logs Alter the primary database control file (using the SQL ALTER DATABASE CREATE CONTROLFILE statement) Perform a DML or DDL operation using the NOLOGGING or UNRECOVERABLE clause Change initialization parameter Action Required on Standby Database If you did not set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, you must copy the new datafile to the standby database. Delete the corresponding datafile after the archived redo log was applied. Rename the datafile on the standby database. Synchronize changes on the standby database. Re-create the standby control file or re-create the standby database, depending on the alteration made. Send the datafile containing the unlogged changes to the standby database. Dynamically change the standby parameter or shut down the standby database and update the initialization parameter file. 위내용은 Standby database에영향을미치는 Primary database Event들과이각 event들에대해서취해주어야할 Action들을도식화한것입니다.

49 Recovering After the NOLOGGING Clause Is Specified Step 1 Determine which datafiles should be copied. Step 2 On the primary site, back up the datafile that you need to copy to the standby site Step 3 On the standby database, restart managed recovery. Primary database에서 NOLOGGING option이사용되었을경우 NOLOGGING 사용시발생한 redo log가 standby database에적용되었을때 Standby database의일부파일들이사용할수없게되고 UNRECOVERABLE 로표시되게됩니다. Failover를시도하거나 read-only 로 open하였을때 UNRECOVERABLE 로 marked된 block들을 Access하게되면다음과비슷한 Error를만나게됩니다. ORA-01578: ORACLE data block corrupted (file # 1, block # 2521) ORA-01110: data file 1: '/oracle/dbs/stdby/tbs_1.dbf' ORA-26040: Data block was loaded using the NOLOGGING option Primary database에서 NOLOGGING option을사용하여작업하였을경우 Standby database에서이를 recovery하기위해서는해당 datafile을 copy해야만합니다. Recovery 과정은다음과같습니다.

50 1 단계 ) Primary database 로부터어떠한 datafile 이 copy 되어야하는지를결정합니다. a. Primary database에서아래의 SQL문을실행합니다. SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE; NAME UNRECOVERABLE /oracle/dbs/tbs_1.dbf 5216 /oracle/dbs/tbs_2.dbf 0 /oracle/dbs/tbs_3.dbf 0 /oracle/dbs/tbs_4.dbf 0 b. Standby database에서아래의 SQL문을실행합니다. SQL> SELECT NAME, UNRECOVERABLE_CHANGE# FROM V$DATAFILE; NAME UNRECOVERABLE /oracle/dbs/tbs_1.dbf 5186 /oracle/dbs/tbs_2.dbf 0 /oracle/dbs/tbs_3.dbf 0 /oracle/dbs/tbs_4.dbf 0 c. UNRECOVERABLE_CHANGE# 의값을비교하여 primary database의값이더큰 datafile들을 copy하면된다. 2 단계 ) Primary database 에서해당 datafile 을 backup 하여 standby database 로 copy 한다 SQL> ALTER TABLESPACE system BEGIN BACKUP; SQL> EXIT; % cp tbs_1.dbf /backup SQL> ALTER TABLESPACE system END BACKUP; 3 단계 ) Standby database 에서 managed recovery 를재수행한다. managed recovery 수행시 ORA-00308: cannot open archived log Error를만날수있는데이것은 archive log gap으로인해발생한것입니다. Manual 하게 archive gap을해결하면이문제는자동으로해결됩니다.

51 Standby Database Real Application Clusters Support Configuring Standby Databases in a RAC Instance Combinations Single-Instance Primary Database Multi-Instance Primary Database Single-Instance Standby Database Yes Yes Multi-Instance Standby Database Yes( for read-only queries) Yes Real Application Cluser(RAC) 로구성되어있는 Primary database 에대해 standby database 를구성하고자 할때 Single-instance standby database 와 multiple-instance standby database 모두구성될수있습니다.

52 Multi-Instance Primary Database with a Single-Instance Standby DB 위의경우는 2개의 primary database instance를가진 RAC가 single-instance standby database에 Redo log를 archiving하는경우입니다. 이경우 primary database의 instnace 1이 log 1,2,3,4,5를전송하고반면에 instance 2는 Log 32, 33, 34,35,36 을전송합니다. Standby database 가 managed recovery mode 이면양 primary instance 에서전송된 archived redo log 들에 대해서적용할올바른순서를자동으로결정합니다.

53 Multi-Instance Primary Database with a Multi-Instance Standby DB RAC 상에있는 Standby database 는다음두가지형태의 instance 를가지게됩니다. - Receiving instance : Primary database로부터 archived log를받을수있는 instance - Recovery instance : managed recovery operation을수행하는 node의 instance 이 node에의하여모든 archived log들 access 가능하여야합니다. Receiving instance에서 Recovery instance로 standby database archived log를전송하기위해 Cross-instance archival operation을사용합니다. Standby database cross-instance archival operation은 primary database archived log의임시저장소로서 Standby redo log를사용합니다.

54 Standby Database Real Application Clusters Support To set up a standby database in a RAC Create the standby redo logs. On the recovery instance where the managed recovery process (MRP) is to operate, define the archived log destination to archive locally, because cross-instance archiving is not necessary. On the receiving instance, define the archived log destination to archive to the node where the MRP is to operate. Start the ARCn process on all standby database instances. Start the MRP on the recovery instance. RAC 환경상에서 standby database 를구성하고자한다면다음과같은과정을수행해야합니다. 1. Standby redo log를생성합니다. standby redo log는 RAC의모든 instance에서 access가능한 disk device 상에위치해야합니다. 2. Recovery instance 상에서는 archived log destination을 Local directory로지정합니다 Recovery instance는 managed recovery process가 redo log는적용하는 instance입니다. 이경우는 cross-instance archiving이필요없습니다. LOG_ARCHIVE_DEST_1= LOCATION=/home/oracle 3. Receiving instance에서는 archived log destination을 MRP가동작하는 node로지정합니다. 이때에 cross-instance archiving이필요합니다. LOG_ARCHIVE_DEST_1= SERVICE=stanby1 4. 모든 standby database instance 상에서 ARCn 를시작합니다. 5. Recovery instance 에서 MRP 를시작합니다.

55 Standby Database Real Application Clusters Support To set up a primary database in a RAC On all instances, designate the LGWR process to perform the archival operation. Designate the standby database as the receiving node. RAC 환경상에서 primary database 의설정은다음과같습니다. 1. Primary database의모든 instance에서 LGWR이 archival operation을수행하도록설정합니다. LGWR을설정해야 standby database에서 standby redo log에 redo를저장하게되고그럼으로서 cross-instance archiving을수행할수있게된다. 2. Standby database 를 receiving node 로설정합니다.