DATA GUARD GUIDE

Transcription

1 Goodus 기술노트 [21 회 ] Author 최범진 Creation Date Last Updated Version 1.0 Copyright(C) 2004 Goodus Inc. All Rights Reserved Version 변경일자 변경자 ( 작성자 ) 주요내용 최범진 문서최초작성 최범진 Upgrade 3

2 Contents 개요 의유용성 의특성 switchover and failover Standby Database 종류 Physical standby database Logical standby database Protect Mode Maximum Protection Maximum Availability Maximum Performance 구축 Standby Database Configuration init.ora 설정 listener.ora 설정 tnsname.ora 설정 Creating physical standby database Standby control file 생성 ( At Primary) Standby DB copy Start and Mount Standby Instance (At Standby DB) 확인 Archive 전송 TEST Data 전송 TEST Mode 변경 Standby Database 상태변경 File Management Maximum Protection/Availability Mode SwitchOver SwitchOver 후 Archive 전송 Failover to standby database 굿어스 Cinema Day 초대 Pro-Active Tuning Service

3 1 1-1 개요 Oracle9i 는기본생산데이타베이스와대기데이타베이스로구성된시스템모음으로구현되며, 이시스템들이서로유연하게연결됨으로써관리가용이한하나의데이터보호솔루션을제공하게된다. 구성에서의각사이트들은같은데이터센터에있을수도있고지리적으로분산되어있을수도있으며, Oracle Network Services를통해서로연결될수도있다. 기본데이타베이스 (Primary Database) 가 transaction에의해수정되면물리적으로존재하는대기데이타베이스 (Standby Database) 는기본데이타베이스 (Primary Database) 에서의변화로인해생성된재실행데이타를전송받으며, 변경사항은복구모드에서실행되는대기데이타베이스 (Standby- Database) 에적용된다. 기본데이타베이스 (Primary Database) 가개방적이고활동적인반면, 대기데이타베이스 (Standby Database) 는복구역할을담당하고리포트액세스를위해서만개방된다. 기본데이터베이스에오류가발생하면생산데이타베이스의작업을대신하기위해대기데이타베이스를사용할수있다. 아래그림은이러한예를보여주고있다. 1-2 의유용성 Standby Database의사용이애플리케이션에영향을주지않으며이를위해애플리케이션코드를변경해야할필요가없기때문에, Standby Database 구성은거의모든데이타베이스에구축되어사용될수있다. Standby Database는 Primary Database의성능에전혀 ( 거의 0%) 영향을미치지않으면서구성될수있다. 또한생산데이타베이스에대한변경사항만이대기데이타베이스로전송되기때문에, 모든데이타베이스파일기록을미러링하는접근방식 - 3 -

4 과비교하여효과적인트랜잭션비율을보장할수있습니다. 대기데이타베이스는복구기능을수행하지않을경우에는읽기전용으로질의될수있으며, 이와동시에생산데이타베이스는계속해서재실행데이타를전송하게됩니다. 대기데이타베이스는전통적으로재난보호솔루션으로인식되어왔으나, 사용자오류나데이타손상같은재난시데이타를보호하기위해서도사용될수있습니다. 사람의실수, 파일시스템손상, 디스크볼륨초가기록으로인해기본데이타베이스가손상되었을경우에, 대기데이타베이스에대한원치않는변경때문에애플리케이션에지연이발생할수도있습니다. 또한대기데이타베이스는기본데이타베이스와대기데이타베이스간의스위치오버 (switchover) 라고도하는역할전환절차를통해, 운영체제및하드웨어업그레이드같은계획된정지로인한다운타임을줄이도록사용될수있습니다. 실제로이러한기능때문에, Oracle의많은고객들이고-가용성솔루션으로서일반적으로대기시스템을사용하고있습니다

5 2 의특성 2-1 switchover and failover - 자동실행이아닌 DBA 가 action 을취해야한다. - Switchover O/S 작업또는서버 PM 작업시사용 (primary standby, standby primary) - Failover Disk fail 등긴급상황에서사용. Standby database 가 main database(primary database) 로 failover 되기때문에기존구성된 Data guard 는사용할수없다. 새로운 data guard 를재구성하여사용하여야한다. 2-2 Standby Database 종류 Physical standby database - block 대 block 기반으로 primary DB 의 redo log 를적용시켜 standby DB 를동기화 - primary database 의 LGWR process 가 standby database redo log 를보내고, standby database 의 RFS process 가 redo log 를 standby redo log 에적용시킨다. archiving 되면 archived redo logs 가되고이것을 MRP process 가 standby database 에적용시킨다

6 2-2-2 Logical standby database - 같은 schema 정의로공유 - Primary database 의 sql 문장을 standby database 에적용 - Logical Standby database 는 primary database 에서수행된 SQL 문장을 LGWR process 가 standby database database 로보내고 RFS process 가받아서 Archived redo logs 에쓴다. LSP(Logical standby process) 가 standby database 에적용시킨다. - Primary database 에서 log switch 가일어나게되면 standby database 의 RFS process 에이를알려주어 log switch 가되도록한다. 2-3 Protect Mode Maximum Protection Primary DB 와 Standby DB 의 redo log 를동기화시킨다. Standby DB 가네트워크이상등의이유로 standby 로의전송이안될경우 primary 를 halt 시킨다. Data 가서로동기화되어 primary DB 에서 commit 을하게되면 standby DB 에서 commit 이완료될때까지 primary DB 에서 commit 완료를하지않는다. 성능에는문제를줄소지가있으나 failover 상황이오더라도데이터손실이없다. Physical standby database 에서만가능하다 Maximum Availability Maximum Protection 과마찬가지로 primary DB 와 standby DB 를동기화시킨다. 단, standby DB 가네트워크문제등의이유로전송이안될지라도 halt 되지는않는다. Data 는 maximum protection 과마찬가지로 primary DB 에서 commit 을하게되면 standby DB 에서 commit 이완료될때까지 primary DB 에서 commit 완료를하지않는다. 만약 standby DB 가 unavailable 상태일경우임시로불일치시킨다. 또다시 standby DB 가 available 하면자동으로동기화시킨다. 성능에는문제를줄소지가있으나 failover 상황이오더라도 data 손실은거의없다

7 Physical standby, logical standby DB 모두가능하다 Maximum Performance Default protection mode 이다. Primary data 에대한 protection 이가장낮다. Primary database 에 transaction 이수행되면이것을 asynchronous 하게 standby DB 에적용한다. 즉 maximum protection, maximum availability 의경우에는 standby DB 에적용 (commit) 될때까지 primary DB 의 transaction 이적용 (commit) 되지않았으나, 기다리지않는다. 즉 standby 유의문제로인해서 primary DB 에성능영향이가지않는다. 단, failover 시에는약간의데이터손실을가져올수있다

8 3 구축 3-1 Standby Database Configuration init.ora 설정 ( At Primary) #****************** # Primary Role #****************** log_archive_start=true log_archive_format=%t_%s.dbf log_archive_dest_1="location=/ora9/oradata/gd9a/archive" log_archive_dest_2='service=sgd9a MANDATORY LGWR SYNC AFFIRM' log_archive_dest_state_1 = ENABLE log_archive_dest_state_2 = ENABLE remote_archive_enable=true #******************* # Standby Role #******************* #log_archive_start=true #log_archive_format=%t_%s.dbf #log_archive_dest_1="location=/ora9/oradata/gd9a/archive" #log_archive_dest_state_1=enable #standby_archive_dest=/ora9/oradata/gd9a/archive #fal_server=gd9a #fal_client=gd9a #remote_archive_enable=true #standby_file_management=auto #******************* # Common #******************* dg_broker_start=true instance_name='gd9a' service_names='gd9a' #lock_name_space=sgd9a log_archive_trace=127 ( At Standby) #****************** # Primary Role #****************** #log_archive_start=true #log_archive_format=%t_%s.dbf #log_archive_dest_1="location=/ora9/oradata/gd9a/archive" #log_archive_dest_2='service=gd9a MANDATORY LGWR SYNC AFFIRM' #log_archive_dest_state_1 = ENABLE #log_archive_dest_state_2 = ENABLE #remote_archive_enable=true #******************* # Standby Role - 8 -

9 #******************* log_archive_start=true log_archive_format=%t_%s.dbf log_archive_dest_1="location=/ora9/oradata/gd9a/archive" log_archive_dest_state_1=enable standby_archive_dest=/ora9/oradata/gd9a/archive fal_server=gd9a fal_client=sgd9a remote_archive_enable=true standby_file_management=auto #******************* # Common #******************* dg_broker_start=true instance_name='gd9a' service_names='sgd9a' lock_name_space=sgd9a log_archive_trace= listener.ora 설정 ( At Primary) STANDBY_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST = sf-a)(port = 1523)) ) ) ) SID_LIST_STANDBY_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /ora9/product/920) (SID_NAME = GD9A) ) ) ( At Standby) STANDBY_LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST = sf-b)(port = 1523)) ) ) ) - 9 -

10 SID_LIST_STANDBY_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /ora9/product/920) (SID_NAME = GD9A) ) ) tnsname.ora 설정 GD9A = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=sf-a)(PORT=1523)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = GD9A) ) ) SGD9A = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=sf-b)(PORT= 1523)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = GD9A) ) ) 3-2 Creating physical standby database Standby control file 생성 ( At Primary) SQL> alter database create standby controlfile as '/ora9/oradata/gd9a_back/standby.ctl'; SQL> shutdown immediate Standby DB copy datafile, controlfile, redo file,standby controlfile copy(primary standby) v$log, dba_data_files, v$controlfile 등을통해 datafile, controlfile, redo file 위치파악 [sf-a:ora9:/ora9/oradata]$ rcp -r /ora9/oradata/gd9a sf-b:/ora9/oradata/. SQL> startup Start and Mount Standby Instance (At Standby DB) SQL> startup nomount

11 Tue Mar 27 11:47: ARCH: STARTING ARCH PROCESSES COMPLETE Tue Mar 27 11:47: ARC0: Thread not mounted DMON started with pid=13 Tue Mar 27 11:47: ARC1: Archival started ARC1: Thread not mounted Tue Mar 27 11:47: Starting Broker (DMON) SQL> alter database mount standby database; Tue Mar 27 11:48: alter database mount standby database Tue Mar 27 11:48: lmon registered with NM - instance id 1 (internal mem no 0) Tue Mar 27 11:48: Reconfiguration started List of nodes: 0, Global Resource Directory frozen one node partition Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Resources and enqueues cleaned out Resources remastered 0 0 GCS shadows traversed, 0 cancelled, 0 closed 0 GCS resources traversed, 0 cancelled set master node info Submitted all remote-enqueue requests Update rdomain variables Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted 0 GCS shadows traversed, 0 replayed, 0 unopened Submitted all GCS remote-cache requests 0 write requests issued in 0 GCS resources 0 PIs marked suspect, 0 flush PI msgs Tue Mar 27 11:48: Reconfiguration complete Post SMON to start 1st pass IR Tue Mar 27 11:48: Successful mount of redo thread 1, with mount id Tue Mar 27 11:48: Standby Database mounted. Completed: alter database mount standby database Standby redo log 생성 SQL> alter database add standby logfile '/ora9/oradata/gd9a/standbylog01.log' size 10m; SQL> alter database add standby logfile '/ora9/oradata/gd9a/standbylog02.log' size 10m; SQL> alter database add standby logfile '/ora9/oradata/gd9a/standbylog03.log' size 10m; Tue Mar 27 11:50: alter database add standby logfile '/ora9/oradata/gd9a/standbylog01.log' size 10m Completed: alter database add standby logfile '/ora9/oradata/ Tue Mar 27 11:51: alter database add standby logfile '/ora9/oradata/gd9a/standbylog02.log' size 10m Completed: alter database add standby logfile '/ora9/oradata/ Tue Mar 27 11:51: alter database add standby logfile '/ora9/oradata/gd9a/standbylog03.log' size 10m Completed: alter database add standby logfile '/ora9/oradata/

12 logfile 확인 SQL> select group#,status,type from v$logfile; GROUP# STATUS TYPE ONLINE 2 ONLINE 3 ONLINE 4 STANDBY 5 STANDBY 6 STANDBY SQL> select group#,thread#,archived,status from v$log; GROUP# THREAD# ARCHIV STATUS NO CURRENT 2 1 YES INACTIVE 3 1 NO INACTIVE SQL> select group#, thread#, archived, status from v$standby_log; GROUP# THREAD# ARCHIV STATUS YES UNASSIGNED 5 0 YES UNASSIGNED 6 0 YES UNASSIGNED standby database 를 managed recovery mode 로설정 SQL> recover managed standby database disconnect; Tue Mar 27 11:53: ALTER DATABASE RECOVER managed standby database disconnect Attempt to start background Managed Standby Recovery process MRP0 started with pid=15 MRP0: Background Managed Standby Recovery process started Starting datafile 1 recovery in thread 1 sequence 25 Datafile 1: '/ora9/oradata/gd9a/system01.dbf' Starting datafile 2 recovery in thread 1 sequence 25 Datafile 2: '/ora9/oradata/gd9a/undotbs01.dbf' Starting datafile 3 recovery in thread 1 sequence 25 Datafile 3: '/ora9/oradata/gd9a/indx01.dbf' Starting datafile 4 recovery in thread 1 sequence 25 Datafile 4: '/ora9/oradata/gd9a/tools01.dbf' Starting datafile 5 recovery in thread 1 sequence 25 Datafile 5: '/ora9/oradata/gd9a/users01.dbf' Media Recovery Waiting for thread 1 seq# Tue Mar 27 11:53: Completed: ALTER DATABASE RECOVER managed standby database 확인 (At Standby DB) SQL> select database_role, protection_mode from v$database; DATABASE_ROLE PROTECTION_MODE

13 PHYSICAL STANDBY MAXIMUM PERFORMANCE ( At Primary DB) SQL> select database_role, protection_mode from v$database; DATABASE_ROLE PROTECTION_MODE PRIMARY MAXIMUM PERFORMANCE Archive 전송 TEST ( At Primary DB) SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> select dest_id,status,error from v$archive_dest where dest_id <=2; DEST_ID STATUS ERROR VALID 2 VALID (( At Primary DB) Tue Mar 27 14:28: LGWR: Completed archiving log 2 thread 1 sequence 29 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' LGWR: Transmitting activation ID 6b9d546e LGWR: Beginning to archive log 3 thread 1 sequence 30 Tue Mar 27 14:28: ARC0: Evaluating archive log 2 thread 1 sequence 29 Tue Mar 27 14:28: Thread 1 advanced to log sequence 30 Current log# 3 seq# 30 mem# 0: /ora9/oradata/gd9a/redo03.log Tue Mar 27 14:28: ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed ARC0: Beginning to archive log 2 thread 1 sequence 29 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_29.dbf' ARC0: Completed archiving log 2 thread 1 sequence 29 Tue Mar 27 14:28: LGWR: Completed archiving log 3 thread 1 sequence 30 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' LGWR: Transmitting activation ID 6b9d546e LGWR: Beginning to archive log 1 thread 1 sequence 31 Thread 1 advanced to log sequence 31 Current log# 1 seq# 31 mem# 0: /ora9/oradata/gd9a/redo01.log Tue Mar 27 14:28: ARC1: Evaluating archive log 3 thread 1 sequence 30 Tue Mar 27 14:28: ARC0: Transmitting activation ID 6b9d546e Tue Mar 27 14:28: ARC1: Archive destination LOG_ARCHIVE_DEST_2: Previously completed ARC1: Beginning to archive log 3 thread 1 sequence 30 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_30.dbf' kccrsz: expanded controlfile section 11 from 13 to 27 records requested to grow by 13 record(s); added 1 block(s) of records ARC1: Completed archiving log 3 thread 1 sequence 30 Tue Mar 27 14:28:

14 LGWR: Completed archiving log 1 thread 1 sequence 31 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' LGWR: Transmitting activation ID 6b9d546e LGWR: Beginning to archive log 2 thread 1 sequence 32 Tue Mar 27 14:28: ARC0: Evaluating archive log 1 thread 1 sequence 31 Tue Mar 27 14:28: Thread 1 advanced to log sequence 32 Current log# 2 seq# 32 mem# 0: /ora9/oradata/gd9a/redo02.log Tue Mar 27 14:28: ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed ARC0: Beginning to archive log 1 thread 1 sequence 31 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_31.dbf' ARC0: Completed archiving log 1 thread 1 sequence 31 (At Standby DB) ARC1: Evaluating archive log 5 thread 1 sequence 29 ARC1: Beginning to archive log 5 thread 1 sequence 29 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_29.dbf' Tue Mar 27 14:28: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 14:28: ARC1: Completed archiving log 5 thread 1 sequence 29 ARC1: Evaluating archive log 4 thread 1 sequence 30 ARC1: Beginning to archive log 4 thread 1 sequence 30 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_30.dbf' ARC1: Completed archiving log 4 thread 1 sequence 30 Tue Mar 27 14:28: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 14:28: ARC1: Evaluating archive log 4 thread 1 sequence 31 ARC1: Beginning to archive log 4 thread 1 sequence 31 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_31.dbf' ARC1: Completed archiving log 4 thread 1 sequence 31 Tue Mar 27 14:28: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog01.log' SQL>! ls /ora9/oradata/gd9a/archive 1_24.dbf 1_25.dbf 1_26.dbf 1_27.dbf 1_28.dbf 1_29.dbf 1_30.dbf 1_31.dbf 3-2 Data 전송 TEST ( At Primary DB) SQL> create table twins (a number); SQL> insert into twins values(407); SQL> commit; SQL> alter system switch logfile; (At Standby DB) SQL> recover managed standby database cancel; === standby alert log === Tue Mar 27 14:45: Media Recovery Log /ora9/oradata/gd9a/archive/1_32.dbf

15 Media Recovery Waiting for thread 1 seq# 33 Tue Mar 27 14:46: ALTER DATABASE RECOVER managed standby database cancel Tue Mar 27 14:46: MRP0: Background Media Recovery user canceled with status MRP0: Background Media Recovery process shutdown Tue Mar 27 14:46: Managed Standby Recovery Cancelled Completed: ALTER DATABASE RECOVER managed standby database c === primay alert log === Network asynch I/O wait error 3114 log 3 service 'SGD9A' Tue Mar 27 14:46: Errors in file /ora9/admin/gd9a/bdump/gd9a_lgwr_15429.trc: ORA-03114: not connected to ORACLE SQL> alter database open read only; read only mode 로 standby DB open alter database open read only Tue Mar 27 14:51: SMON: enabling cache recovery Tue Mar 27 14:51: Database Characterset is KO16MSWIN949 replication_dependency_tracking turned off (no async multimaster replication found) Completed: alter database open read only SQL> select * from twins; A Mode 변경 read only managed recovery mode 로전환 SQL> shutdown Tue Mar 27 14:55: Shutting down instance: further logons disabled Shutting down instance (normal) License high water mark = 2 Tue Mar 27 14:56: ALTER DATABASE CLOSE NORMAL Tue Mar 27 14:56: SMON: disabling cache recovery Tue Mar 27 14:56: Completed: ALTER DATABASE CLOSE NORMAL Tue Mar 27 14:56: ALTER DATABASE DISMOUNT Completed: ALTER DATABASE DISMOUNT ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled ARCH 9 state 3 ARCH 8 state 3 Tue Mar 27 14:56: ARCH shutting down ARC0: Archival stopped Tue Mar 27 14:56: ARCH shutting down ARC1: Archival stopped Tue Mar 27 14:56: ARCH: Archiving is disabled

16 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Shutting down Broker processes Tue Mar 27 14:56: Completed: Broker shutdown SQL> startup nomount === standby alert log === ARC0: Thread not mounted ARC1 started with pid=12 ARC1: Archival started ARC1: Thread not mounted Tue Mar 27 15:00: Starting Broker (DMON) Tue Mar 27 15:00: destination database instance is 'started' not 'mounted' === primary alert log === Tue Mar 27 15:00: Errors in file /ora9/admin/gd9a/bdump/gd9a_arc1_15459.trc: ORA-16058: standby database instance is not mounted SQL> alter database mount standby database; Tue Mar 27 15:04: alter database mount standby database Tue Mar 27 15:04: lmon registered with NM - instance id 1 (internal mem no 0) Tue Mar 27 15:04: Reconfiguration started List of nodes: 0, Global Resource Directory frozen one node partition Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Resources and enqueues cleaned out Resources remastered 0 0 GCS shadows traversed, 0 cancelled, 0 closed 0 GCS resources traversed, 0 cancelled set master node info Submitted all remote-enqueue requests Update rdomain variables Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted 0 GCS shadows traversed, 0 replayed, 0 unopened Submitted all GCS remote-cache requests 0 write requests issued in 0 GCS resources 0 PIs marked suspect, 0 flush PI msgs Tue Mar 27 15:04: Reconfiguration complete Post SMON to start 1st pass IR Tue Mar 27 15:04: Successful mount of redo thread 1, with mount id Tue Mar 27 15:04: Standby Database mounted. Completed: alter database mount standby database SQL> recover managed standby database disconnect; Tue Mar 27 15:05: ALTER DATABASE RECOVER managed standby database disconnect

17 Attempt to start background Managed Standby Recovery process MRP0 started with pid=16 MRP0: Background Managed Standby Recovery process started Starting datafile 1 recovery in thread 1 sequence 33 Datafile 1: '/ora9/oradata/gd9a/system01.dbf' Starting datafile 2 recovery in thread 1 sequence 33 Datafile 2: '/ora9/oradata/gd9a/undotbs01.dbf' Starting datafile 3 recovery in thread 1 sequence 33 Datafile 3: '/ora9/oradata/gd9a/indx01.dbf' Starting datafile 4 recovery in thread 1 sequence 33 Datafile 4: '/ora9/oradata/gd9a/tools01.dbf' Starting datafile 5 recovery in thread 1 sequence 33 Datafile 5: '/ora9/oradata/gd9a/users01.dbf' Media Recovery Waiting for thread 1 seq# 33 Tue Mar 27 15:05: Completed: ALTER DATABASE RECOVER managed standby database d Tue Mar 27 15:05: RFS: Possible network disconnect with primary database 3-3 Standby Database 상태변경 1. shut down => managed recovery mode SQL> startup nomount SQL> alter database mount standby database; SQL> recover managed standby database disconnect(from session); 2. shutdown => read only Standby SQL> startup nomount SQL> alter database mount standby database; SQL> alter database open read only;; 3. managed recovery mode => read only Standby SQL> recover managed standby database cancel; SQL>alter database open read only; 4. read only Standby => managed recovery mode( 모든 session 종료 ) SQL> recover managed standby database disconnect(from session); 3-4 File Management standby database 에 MRP process가떠있는지반듯이확인 (At Standby DB) SQL> select process,status from v$managed_standby; PROCESS STATUS ARCH CONNECTED ARCH CONNECTED RFS RECEIVING MRP0 WAIT_FOR_LOG

18 1. add tablespace or datafile (standby_file_management = auto in STANDBY ) ( At Primary DB) SQL> create tablespace twins datafile '/ora9/oradata/gd9a/twins.dbf' size 5m; Tue Mar 27 15:55: create tablespace twins datafile '/ora9/oradata/gd9a/twins.dbf' size 5m Tue Mar 27 15:55: Completed: create tablespace twins datafile '/ora9/oradata/gd SQL> alter system switch logfile; === primary alert log === Tue Mar 27 15:55: LGWR: Completed archiving log 3 thread 1 sequence 33 LGWR: Error 1010 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'SGD9A' LGWR: RFS network connection re-established at host 'SGD9A' LGWR: RFS destination opened for reconnect at host 'SGD9A' Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' LGWR: Transmitting activation ID 6b9d546e LGWR: Beginning to archive log 1 thread 1 sequence 34 Tue Mar 27 15:55: ARC0: Evaluating archive log 3 thread 1 sequence 33 Tue Mar 27 15:55: Thread 1 advanced to log sequence 34 Tue Mar 27 15:55: ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 Tue Mar 27 15:55: Current log# 1 seq# 34 mem# 0: /ora9/oradata/gd9a/redo01.log Tue Mar 27 15:55: ARC0: Beginning to archive log 3 thread 1 sequence 33 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' ARC0: Transmitting activation ID 6b9d546e Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_33.dbf' ARC0: Completed archiving log 3 thread 1 sequence 33 === standby alert log === Tue Mar 27 15:55: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 15:55: ARC1: Evaluating archive log 4 thread 1 sequence 33 ARC1: Beginning to archive log 4 thread 1 sequence 33 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_33.dbf' ARC1: Log corruption near block 6 change time? ARC1: All Archive destinations made inactive due to error 354 Tue Mar 27 15:55: Errors in file /ora9/admin/gd9a/bdump/gd9a_arc1_23459.trc: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 6 change time 03/27/ :41:21 ORA-00312: online log 4 thread 1: '/ora9/oradata/gd9a/standbylog01.log' ARC1: Archiving not possible: error count exceeded ARC1: Failed to archive log 4 thread 1 sequence 33 Clearing standby logfile 4 thread 1 sequence 33 due to error 354 ARCH: Archival stopped, error occurred. Will continue retrying Tue Mar 27 15:55: ORACLE Instance GD9A - Archival Error ARCH: Connecting to console port... Tue Mar 27 15:55: ORA-16038: log 4 sequence# 33 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 4 thread 1: '/ora9/oradata/gd9a/standbylog01.log' ARCH: Connecting to console port... ARCH: Tue Mar 27 15:55:

19 ORA-16038: log 4 sequence# 33 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 4 thread 1: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 15:55: Errors in file /ora9/admin/gd9a/bdump/gd9a_arc1_23459.trc: ORA-16038: log 4 sequence# 33 cannot be archived ORA-00354: corrupt redo log block header ORA-00312: online log 4 thread 1: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 15:55: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 15:56: Media Recovery Log /ora9/oradata/gd9a/archive/1_33.dbf Recovery created file /ora9/oradata/gd9a/twins.dbf Successfully added datafile 6 to media recovery Datafile #6: '/ora9/oradata/gd9a/twins.dbf' Media Recovery Waiting for thread 1 seq# 34 (in transit) Tue Mar 27 15:56: Archiver process freed from errors. No longer stopped (At Standby DB) SQL> select name from v$datafile; NAME /ora9/oradata/gd9a/system01.dbf /ora9/oradata/gd9a/undotbs01.dbf /ora9/oradata/gd9a/indx01.dbf /ora9/oradata/gd9a/tools01.dbf /ora9/oradata/gd9a/users01.dbf /ora9/oradata/gd9a/twins. dbf 2. rename datafile (statndby_file_manangement=manual/auto in STANDBY) ( At Primary DB) SQL> alter tablespace twins offline; SQL>! cp /ora9/oradata/gd9a/twins.dbf /ora9/oradata/gd9a/twins_1.dbf SQL> select file_name from dba_data_files; FILE_NAME /ora9/oradata/gd9a/system01.dbf /ora9/oradata/gd9a/undotbs01.dbf /ora9/oradata/gd9a/indx01.dbf /ora9/oradata/gd9a/tools01.dbf /ora9/oradata/gd9a/users01.dbf /ora9/oradata/gd9a/twins.dbf SQL> alter database rename file '/ora9/oradata/gd9a/twins.dbf' to '/ora9/oradata/gd9a/twins_1.dbf'; SQL> alter tablespace twins online; SQL> select file_name from dba_data_files; FILE_NAME

20 /ora9/oradata/gd9a/system01.dbf /ora9/oradata/gd9a/undotbs01.dbf /ora9/oradata/gd9a/indx01.dbf /ora9/oradata/gd9a/tools01.dbf /ora9/oradata/gd9a/users01.dbf /ora9/oradata/gd9a/twins_1.dbf (At Standby DB) standby 에서 rename 을안해도 redo apply 는정상적으로된다. SQL> select name,sequence#,archived,applied from v$archived_log; (applied => 모두 YES 인지확인 ) NAME SEQUENCE# ARCHIV APPLIE /ora9/oradata/gd9a/archive/1_24.dbf 24 YES NO /ora9/oradata/gd9a/archive/1_25.dbf 25 YES YES /ora9/oradata/gd9a/archive/1_26.dbf 26 YES YES /ora9/oradata/gd9a/archive/1_27.dbf 27 YES YES /ora9/oradata/gd9a/archive/1_28.dbf 28 YES YES /ora9/oradata/gd9a/archive/1_29.dbf 29 YES YES /ora9/oradata/gd9a/archive/1_30.dbf 30 YES YES /ora9/oradata/gd9a/archive/1_31.dbf 31 YES YES /ora9/oradata/gd9a/archive/1_32.dbf 32 YES YES /ora9/oradata/gd9a/archive/1_33.dbf 33 YES YES /ora9/oradata/gd9a/archive/1_34.dbf 34 YES YES /ora9/oradata/gd9a/archive/1_35.dbf 35 YES YES /ora9/oradata/gd9a/archive/1_36.dbf 36 YES YES /ora9/oradata/gd9a/archive/1_37.dbf 37 YES YES SQL> recover managed standby database cancel; SQL> shutdown immediate SQL>! cp /ora9/oradata/gd9a/twins.dbf /ora9/oradata/gd9a/twins_1.dbf SQL> startup nomount SQL> alter database mount standby database; SQL> show parameter standby_file NAME TYPE VALUE standby_file_management string auto SQL> alter system set standby_file_management=manual;

21 SQL> show parameter standby_file NAME TYPE VALUE standby_file_management string MANUAL SQL> alter database rename file '/ora9/oradata/gd9a/twins.dbf' to '/ora9/oradata/gd9a/twins_1.dbf'; SQL> recover managed standby database disconnect; SQL> select name from v$datafile; NAME /ora9/oradata/gd9a/system01.dbf /ora9/oradata/gd9a/undotbs01.dbf /ora9/oradata/gd9a/indx01.dbf /ora9/oradata/gd9a/tools01.dbf /ora9/oradata/gd9a/users01.dbf /ora9/oradata/gd9a/twins_1.dbf SQL> alter system set standby_file_management=auto; 3. drop tablespace (standby_file_management=manual/auto in STANDBY) ( At Primary DB) SQL> drop tablespace twins including contents and datafiles; SQL> alter system switch logfile; === primary alert log === Tue Mar 27 17:03: drop tablespace twins including contents and datafiles Tue Mar 27 17:03: Deleted file /ora9/oradata/gd9a/twins_1.dbf Completed: drop tablespace twins including contents and dataf Tue Mar 27 17:03: LGWR: Completed archiving log 3 thread 1 sequence 39 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' LGWR: Transmitting activation ID 6b9d546e LGWR: Beginning to archive log 1 thread 1 sequence 40 Thread 1 advanced to log sequence 40 Current log# 1 seq# 40 mem# 0: /ora9/oradata/gd9a/redo01.log Tue Mar 27 17:03: ARC0: Evaluating archive log 3 thread 1 sequence 39 ARC0: Archive destination LOG_ARCHIVE_DEST_2: Previously completed ARC0: Beginning to archive log 3 thread 1 sequence 39 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_39.dbf' ARC0: Completed archiving log 3 thread 1 sequence 39 === standby alert log === Tue Mar 27 17:03: ARC1: Evaluating archive log 4 thread 1 sequence 39 ARC1: Beginning to archive log 4 thread 1 sequence 39 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_39.dbf'

22 ARC1: Completed archiving log 4 thread 1 sequence 39 Tue Mar 27 17:03: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog01.log' Tue Mar 27 17:03: Media Recovery Log /ora9/oradata/gd9a/archive/1_39.dbf Recovery deleting file #6:'/ora9/oradata/GD9A/twins_1.dbf' Deleted file /ora9/oradata/gd9a/twins_1.dbf Recovery dropped tablespace 'TWINS' Media Recovery Waiting for thread 1 seq# 40 (in transit) (At Standby DB) SQL> select name from v$datafile; NAME /ora9/oradata/gd9a/system01.dbf /ora9/oradata/gd9a/undotbs01.dbf /ora9/oradata/gd9a/indx01.dbf /ora9/oradata/gd9a/tools01.dbf /ora9/oradata/gd9a/users01.dbf SQL>!ls /ora9/oradata/gd9a/twins_1.dbf /ora9/oradata/gd9a/twins_1.dbf: 해당파일이나디렉토리가없음 3-5 Maximum Protection/Availability Mode default value = maximum performance ( At Primary) SQL> select protection_mode from v$database; PROTECTION_MODE MAXIMUM PERFORMANCE -- standby redo log 생성 (for switchover) SQL> alter database add standby logfile '/ora9/oradata/gd9a/standbylog_11.log' size 10m; SQL> alter database add standby logfile '/ora9/oradata/gd9a/standbylog_22.log' size 10m; SQL> alter database add standby logfile '/ora9/oradata/gd9a/standbylog_33.log' size 10m; SQL> shutdown immediate SQL> startup mount SQL> alter database set standby to maximize protection; SQL> alter database open; Database altered. SQL> select protection_mode from v$database;

23 PROTECTION_MODE MAXIMUM PROTECTION 3-5 SwitchOver ( At Primary) SQL> select switchover_status from v$database; SWITCHOVER_STATUS TO STANDBY SQL> alter database commit to switchover to physical standby with session shutdown wait; === primary alert log === Tue Mar 27 18:19: alter database commit to switchover to physical standby with session shutdown wait Tue Mar 27 18:19: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY LGWR: Performing log switch to complete LGWR archival of current logfile Tue Mar 27 18:19: LGWR: Completed archiving log 2 thread 1 sequence 41 Thread 1 advanced to log sequence 42 Current log# 3 seq# 42 mem# 0: /ora9/oradata/gd9a/redo03.log Tue Mar 27 18:19: ARCH: Evaluating archive log 2 thread 1 sequence 41 ARCH: Archive destination LOG_ARCHIVE_DEST_2: Previously completed Tue Mar 27 18:19: ARC0: Evaluating archive log 2 thread 1 sequence 41 Tue Mar 27 18:19: ARCH: Beginning to archive log 2 thread 1 sequence 41 Tue Mar 27 18:19: ARC0: Unable to archive log 2 thread 1 sequence 41 Log actively being archived by another process Tue Mar 27 18:19: Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_41.dbf' ARCH: Completed archiving log 2 thread 1 sequence 41 Tue Mar 27 18:19: Thread 1 advanced to log sequence 43 Tue Mar 27 18:19: ARCH: Evaluating archive log 3 thread 1 sequence 42 Tue Mar 27 18:19: Current log# 1 seq# 43 mem# 0: /ora9/oradata/gd9a/redo01.log Tue Mar 27 18:19: ARC0: Evaluating archive log 3 thread 1 sequence 42 ARC0: Unable to archive log 3 thread 1 sequence 42 Log actively being archived by another process Tue Mar 27 18:19: ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Beginning to archive log 3 thread 1 sequence 42 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' ARCH: Transmitting activation ID 6b9d546e Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_42.dbf'arch: Completed archiving log 3 thread 1 sequence 42 Tue Mar 27 18:19: SMON: disabling tx recovery SMON: disabling cache recovery Tue Mar 27 18:19: Shutting down archive processes Archiving is disabled ARCH 9 state 3 ARCH 8 state 3 Tue Mar 27 18:19:

24 Errors in file /ora9/admin/gd9a/bdump/gd9a_lgwr_24156.trc: ORA-00449: background process 'LNS0' unexpectedly terminated with error 448 ORA-00448: normal completion of background process Tue Mar 27 18:19: ARCH shutting down ARC0: Archival stopped Tue Mar 27 18:19: ARCH shutting down ARC1: Archival stopped Tue Mar 27 18:19: Thread 1 closed at log sequence 43 Successful close of redo thread 1. Tue Mar 27 18:19: ARCH: noswitch archival of thread 1, sequence 43 ARCH: End-Of-Redo archival of thread 1 sequence 43 ARCH: Evaluating archive log 1 thread 1 sequence 43 ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARCH: Beginning to archive log 1 thread 1 sequence 43 Creating archive destination LOG_ARCHIVE_DEST_2: 'SGD9A' ARCH: Transmitting activation ID 6b9d546e Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_43.dbf' ARCH: Completed archiving log 1 thread 1 sequence 43 ARCH: archiving is disabled due to current logfile archival Clearing standby activation ID (0x6b9d546e) The primary database controlfile was created using the 'MAXLOGFILES 20' clause. There is space for up to 17 standby redo logfiles Use the following SQL commands on the standby database to create standby redo logfiles that match the primary database: ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE ; ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE ; ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE ; ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE ; Archivelog for thread 1 sequence 43 required for standby recovery MRP0 started with pid=11 MRP0: Background Managed Standby Recovery process started Starting datafile 1 recovery in thread 1 sequence 43 Datafile 1: '/ora9/oradata/gd9a/system01.dbf' Starting datafile 2 recovery in thread 1 sequence 43 Datafile 2: '/ora9/oradata/gd9a/undotbs01.dbf' Starting datafile 3 recovery in thread 1 sequence 43 Datafile 3: '/ora9/oradata/gd9a/indx01.dbf' Starting datafile 4 recovery in thread 1 sequence 43 Datafile 4: '/ora9/oradata/gd9a/tools01.dbf' Starting datafile 5 recovery in thread 1 sequence 43 Datafile 5: '/ora9/oradata/gd9a/users01.dbf' Media Recovery Log /ora9/oradata/gd9a/archive/1_43.dbf Identified end-of-redo for thread 1 sequence 43 Identified end-of-redo for thread 1 sequence 43 Media Recovery End-Of-Redo indicator encountered Media Recovery Applied until change MRP0: Media Recovery Complete: End-Of-REDO Resetting standby activation ID (0x6b9d546e) MRP0: Background Media Recovery process shutdown Tue Mar 27 18:19: Switchover: Complete - Database shutdown required Completed: alter database commit to switchover to physical st === stadby alert log === Tue Mar 27 18:19: ARC1: Evaluating archive log 4 thread 1 sequence 41 ARC1: Beginning to archive log 4 thread 1 sequence 41 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_41.dbf' ARC1: Completed archiving log 4 thread 1 sequence 41 Tue Mar 27 18:19: RFS: Possible network disconnect with primary database RFS: End-Of-Redo archival of thread 1 sequence

25 Tue Mar 27 18:19: Media Recovery Log /ora9/oradata/gd9a/archive/1_41.dbf Media Recovery Log /ora9/oradata/gd9a/archive/1_42.dbf Media Recovery Log /ora9/oradata/gd9a/archive/1_43.dbf Identified end-of-redo for thread 1 sequence 43 Identified end-of-redo for thread 1 sequence 43 Media Recovery End-Of-Redo indicator encountered Media Recovery Applied until change MRP0: Media Recovery Complete: End-Of-REDO Resetting standby activation ID (0x6b9d546e) MRP0: Background Media Recovery process shutdown SQL> shutdown immediate === primary alert log === Tue Mar 27 18:29: Shutting down instance: further logons disabled Shutting down instance (immediate) License high water mark = 1 Tue Mar 27 18:29: ALTER DATABASE CLOSE NORMAL ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Shutting down Broker processes Tue Mar 27 18:29: Completed: Broker shutdown (At Standby DB) SQL> select switchover_status from v$database; SWITCHOVER_STATUS NOT ALLOWED default 값 SQL> select switchover_status from v$database; SWITCHOVER_STATUS TO PRIMARY Primary db 에서 alter database commit to switchover to physical standby with session shutdown wait; 실행후바뀐값 SQL> alter database commit to switchover to primary with session shutdown wait; Tue Mar 27 18:36: alter database commit to switchover to primary with session shutdown wait Tue Mar 27 18:36: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY RESETLOGS after incomplete recovery UNTIL CHANGE Resetting resetlogs activation ID 0 (0x0) Switchover: Complete - Database shutdown required Completed: alter database commit to switchover to primary wit SQL> shutdown immediate; Tue Mar 27 18:37: Shutting down instance: further logons disabled Shutting down instance (immediate) License high water mark = 4 Tue Mar 27 18:37: ALTER DATABASE CLOSE NORMAL

26 ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled ARCH 9 state 3 ARCH 8 state 3 Tue Mar 27 18:37: ARCH shutting down ARC0: Archival stopped Tue Mar 27 18:37: ARCH shutting down ARC1: Archival stopped Tue Mar 27 18:37: ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Shutting down Broker processes Tue Mar 27 18:37: Completed: Broker shutdown (New Standby DB) [sf-a:ora9:/ora9/product/920/dbs]$ vi initgd9a.ora #log_archive_dest_2='service=sgd9a MANDATORY LGWR SYNC AFFIRM' fal_server=sgd9a fal_client=gd9a lock_name_space=sgd9a 수정 SQL> startup nomount Tue Mar 27 18:53: Starting ORACLE instance (normal) Tue Mar 27 18:53: Global Enqueue Service Resources = 64, pool = 2 Tue Mar 27 18:53: Global Enqueue Service Enqueues = 128 LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 GES IPC: Receivers 1 Senders 1 GES IPC: Buffers Receive 1000 Send 530 Reserve 300 GES IPC: Msg Size Regular 432 Batch 2048 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = large_pool_size = java_pool_size = 0 lock_name_space = SGD9A control_files = /ora9/oradata/gd9a/control01.ctl, /ora9/oradata/gd9a/control02.ctl db_block_size = 8192 db_cache_size = compatible = remote_archive_enable = true log_archive_start = TRUE log_archive_dest_1 = location=/ora9/oradata/gd9a/archive log_archive_dest_state_1 = enable standby_archive_dest = /ora9/oradata/gd9a/archive log_archive_trace = 127 fal_server = SGD9A fal_client = GD9A log_archive_format = %t_%s.dbf

27 db_file_multiblock_read_count= 16 standby_file_management = auto fast_start_mttr_target = 300 instance_number = 1 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = GD9A service_names = GD9A hash_join_enabled = TRUE background_dump_dest = /ora9/admin/gd9a/bdump user_dump_dest = /ora9/admin/gd9a/udump core_dump_dest = /ora9/admin/gd9a/cdump sort_area_size = db_name = GD9A open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = dg_broker_start = TRUE Tue Mar 27 18:53: cluster interconnect IPC version:oracle UDP/IP IPC Vendor 1 proto 2 Version 1.0 PMON started with pid=2 DIAG started with pid=3 LMON started with pid=4 LMD0 started with pid=5 DBW0 started with pid=6 LGWR started with pid=7 CKPT started with pid=8 SMON started with pid=9 RECO started with pid=10 Tue Mar 27 18:53: ARCH: STARTING ARCH PROCESSES ARC0 started with pid=11 ARC0: Archival started ARC1 started with pid=12 ARC1: Archival started Tue Mar 27 18:53: ARCH: STARTING ARCH PROCESSES COMPLETE Tue Mar 27 18:53: ARC0: Thread not mounted Tue Mar 27 18:53: ARC1: Thread not mounted DMON started with pid=13 Starting Broker (DMON) SQL> alter database mount standby database; alter database mount standby database Tue Mar 27 18:54: lmon registered with NM - instance id 1 (internal mem no 0) Tue Mar 27 18:54: Reconfiguration started List of nodes: 0, Global Resource Directory frozen one node partition Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Resources and enqueues cleaned out Resources remastered 0 0 GCS shadows traversed, 0 cancelled, 0 closed 0 GCS resources traversed, 0 cancelled set master node info Submitted all remote-enqueue requests

28 Update rdomain variables Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted 0 GCS shadows traversed, 0 replayed, 0 unopened Submitted all GCS remote-cache requests 0 write requests issued in 0 GCS resources 0 PIs marked suspect, 0 flush PI msgs Tue Mar 27 18:54: Reconfiguration complete Post SMON to start 1st pass IR Tue Mar 27 18:55: Successful mount of redo thread 1, with mount id Tue Mar 27 18:55: Standby Database mounted. Completed: alter database mount standby database SQL> recover managed standby database disconnect; Tue Mar 27 18:56: ALTER DATABASE RECOVER managed standby database disconnect Attempt to start background Managed Standby Recovery process MRP0 started with pid=15 MRP0: Background Managed Standby Recovery process started Starting datafile 1 recovery in thread 1 sequence 44 Datafile 1: '/ora9/oradata/gd9a/system01.dbf' Starting datafile 2 recovery in thread 1 sequence 44 Datafile 2: '/ora9/oradata/gd9a/undotbs01.dbf' Starting datafile 3 recovery in thread 1 sequence 44 Datafile 3: '/ora9/oradata/gd9a/indx01.dbf' Starting datafile 4 recovery in thread 1 sequence 44 Datafile 4: '/ora9/oradata/gd9a/tools01.dbf' Starting datafile 5 recovery in thread 1 sequence 44 Datafile 5: '/ora9/oradata/gd9a/users01.dbf' Media Recovery Waiting for thread 1 seq# 44 Tue Mar 27 18:56: Completed: ALTER DATABASE RECOVER managed standby database d SQL> select database_role,protection_mode from v$database; DATABASE_ROLE PROTECTION_MODE PHYSICAL STANDBY MAXIMUM PROTECTION (New Primary DB) [sf-b:ora9:/ora9/product/920/dbs]$ vi initgd9a.ora log_archive_dest_2='service=gd9a MANDATORY LGWR SYNC AFFIRM' #fal_server=sgd9a #fal_client=gd9a #lock_name_space=sgd9a 수정 SQL> startup === new primary alert log === Tue Mar 27 18:59: Starting ORACLE instance (normal) Tue Mar 27 18:59: Global Enqueue Service Resources = 64, pool = 2 Tue Mar 27 18:59: Global Enqueue Service Enqueues = 128 LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 GES IPC: Receivers 1 Senders

29 GES IPC: Buffers Receive 1000 Send 530 Reserve 300 GES IPC: Msg Size Regular 432 Batch 2048 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = large_pool_size = java_pool_size = 0 control_files = /ora9/oradata/gd9a/standby.ctl db_block_size = 8192 db_cache_size = compatible = remote_archive_enable = TRUE log_archive_start = TRUE log_archive_dest_1 = location=/ora9/oradata/gd9a/archive log_archive_dest_2 = SERVICE=GD9A MANDATORY LGWR SYNC AFFIRM log_archive_dest_state_1 = ENABLE log_archive_dest_state_2 = ENABLE log_archive_trace = 127 log_archive_format = %t_%s.dbf db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = GD9A service_names = SGD9A hash_join_enabled = TRUE background_dump_dest = /ora9/admin/gd9a/bdump user_dump_dest = /ora9/admin/gd9a/udump core_dump_dest = /ora9/admin/gd9a/cdump sort_area_size = db_name = GD9A open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = dg_broker_start = TRUE Tue Mar 27 18:59: cluster interconnect IPC version:oracle UDP/IP IPC Vendor 1 proto 2 Version 1.0 PMON started with pid=2 DIAG started with pid=3 LMON started with pid=4 LMD0 started with pid=5 DBW0 started with pid=6 LGWR started with pid=7 CKPT started with pid=8 SMON started with pid=9 RECO started with pid=10 Tue Mar 27 18:59: ARCH: STARTING ARCH PROCESSES ARC0 started with pid=11 ARC0: Archival started ARC1 started with pid=12 ARC1: Archival started Tue Mar 27 18:59: ARCH: STARTING ARCH PROCESSES COMPLETE Tue Mar 27 18:59: ARC0: Thread not mounted Tue Mar 27 18:59:

30 ARC1: Thread not mounted DMON started with pid=13 Tue Mar 27 18:59: ALTER DATABASE MOUNT Tue Mar 27 18:59: lmon registered with NM - instance id 1 (internal mem no 0) Tue Mar 27 18:59: Reconfiguration started List of nodes: 0, Global Resource Directory frozen one node partition Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Resources and enqueues cleaned out Resources remastered 0 0 GCS shadows traversed, 0 cancelled, 0 closed 0 GCS resources traversed, 0 cancelled set master node info Submitted all remote-enqueue requests Update rdomain variables Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted 0 GCS shadows traversed, 0 replayed, 0 unopened Submitted all GCS remote-cache requests 0 write requests issued in 0 GCS resources 0 PIs marked suspect, 0 flush PI msgs Tue Mar 27 18:59: Reconfiguration complete Post SMON to start 1st pass IR Tue Mar 27 18:59: Starting Broker (DMON) Tue Mar 27 18:59: Successful mount of redo thread 1, with mount id Tue Mar 27 18:59: Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Mar 27 18:59: ALTER DATABASE OPEN Tue Mar 27 18:59: LGWR: Primary database is in CLUSTER CONSISTENT mode LGWR: Primary database is in MAXIMUM PROTECTION mode LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR LNS0 started with pid=15 Tue Mar 27 18:59: Assigning activation ID (0x6b9f213c) ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** Creating archive destination LOG_ARCHIVE_DEST_2: 'GD9A' LGWR: Transmitting activation ID 6b9f213c LGWR: Standby redo logfile selected to archive thread 1 sequence 45 LGWR: Beginning to archive log 2 thread 1 sequence 45 Thread 1 advanced to log sequence 45 Thread 1 opened at log sequence 45 Current log# 2 seq# 45 mem# 0: /ora9/oradata/gd9a/redo02.log Successful open of redo thread 1. Tue Mar 27 18:59: ARC1: Evaluating archive log 1 thread 1 sequence 44 ARC1: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2 ARC1: Beginning to archive log 1 thread 1 sequence 44 Creating archive destination LOG_ARCHIVE_DEST_2: 'GD9A' ARC1: Transmitting activation ID 6b9f213c Tue Mar 27 18:59: SMON: enabling cache recovery Tue Mar 27 18:59: Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_44.dbf' Tue Mar 27 18:59:

31 Undo Segment 1 Onlined Undo Segment 2 Onlined Undo Segment 3 Onlined Undo Segment 4 Onlined Undo Segment 5 Onlined Undo Segment 6 Onlined Undo Segment 7 Onlined Undo Segment 8 Onlined Undo Segment 9 Onlined Undo Segment 10 Onlined Successfully onlined Undo Tablespace 1. Dictionary check beginning Dictionary check complete Tue Mar 27 18:59: SMON: enabling tx recovery Tue Mar 27 18:59: Database Characterset is KO16MSWIN949 replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN Tue Mar 27 18:59: ARC1: Completed archiving log 1 thread 1 sequence 44 === new standby alert log === Tue Mar 27 18:59: RFS: Successfully opened standby logfile 4: '/ora9/oradata/gd9a/standbylog_11.log' Tue Mar 27 18:59: Media Recovery Log /ora9/oradata/gd9a/archive/1_44.dbf Media Recovery Waiting for thread 1 seq# 45 (in transit) SQL> select database_role,protection_mode from v$database; DATABASE_ROLE PROTECTION_MODE PRIMARY MAXIMUM PROTECTION 3-6 SwitchOver 후 Archive 전송 (New Primary DB) SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> col error format a20 SQL> select dest_id,status,error from v$archive_dest where dest_id <=2; DEST_ID STATUS ERROR VALID 2 VALID (New Standby DB) [sf-a:ora9:/ora9/oradata/gd9a/archive]$ ls -ltr -rw-r ora9 dba 월 27일 18:59 1_44.dbf -rw-r ora9 dba 월 27일 19:24 1_45.dbf -rw-r ora9 dba 월 27일 19:25 1_46.dbf

32 -rw-r ora9 dba 월 27 일 19:25 1_47. 율 3-7 Failover to standby database (New Primary DB) SQL> shutdown abort === new primary alert log === Tue Mar 27 19:29: Shutting down instance (abort) License high water mark = 1 Instance terminated by USER, pid = 1205 === new standby alert log === Tue Mar 27 19:29: RFS: Possible network disconnect with primary database Tue Mar 27 19:29: RFS: Possible network disconnect with primary database Tue Mar 27 19:29: RFS: Possible network disconnect with primary database (New Standby DB) SQL> alter database recover managed standby database finish; Tue Mar 27 19:32: alter database recover managed standby database finish Tue Mar 27 19:32: Terminal Recovery: request posted Tue Mar 27 19:32: TERMINAL RECOVERY changing datafile format version from to Switching logfile format version from to Terminal Recovery: applying standby redo logs. Terminal Recovery: thread 1 seq# 48 redo required Terminal Recovery: /ora9/oradata/gd9a/standbylog_22.log Identified end-of-redo for thread 1 sequence 48 Incomplete recovery applied all redo ever generated. Recovery completed through change MRP0: Media Recovery Complete Switching logfile format version from to Terminal Recovery: successful completion Tue Mar 27 19:32: ARC0: Evaluating archive log 5 thread 1 sequence 48 ARC0: Beginning to archive log 5 thread 1 sequence 48 Creating archive destination LOG_ARCHIVE_DEST_1: '/ora9/oradata/gd9a/archive/1_48.dbf' ARC0: Completed archiving log 5 thread 1 sequence 48 Tue Mar 27 19:32: Resetting standby activation ID (0x6b9f213c) MRP0: Background Media Recovery process shutdown Tue Mar 27 19:32: Terminal Recovery: completion detected Completed: alter database recover managed standby database fi SQL> alter database commit to switchover to primary; Tue Mar 27 19:33: alter database commit to switchover to primary Tue Mar 27 19:33: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY NORESETLOGS after complete recovery through change Resetting resetlogs activation ID 0 (0x0) Online log 6 of thread 0 was previously cleared Changing control file format version from to

33 RESETLOGS changing datafile format version from to Switchover: Complete - Database shutdown required Completed: alter database commit to switchover to primary SQL> shutdown Tue Mar 27 19:34: Shutting down instance: further logons disabled Shutting down instance (normal) License high water mark = 4 Tue Mar 27 19:34: ALTER DATABASE CLOSE NORMAL ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled ARCH 9 state 3 ARCH 8 state 3 Tue Mar 27 19:34: ARCH shutting down Tue Mar 27 19:34: ARCH shutting down Tue Mar 27 19:34: ARC0: Archival stopped Tue Mar 27 19:34: ARC1: Archival stopped Tue Mar 27 19:34: ARCH: Archiving is disabled Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Shutting down Broker processes Tue Mar 27 19:34: Completed: Broker shutdown SQL> startup Tue Mar 27 19:35: Starting ORACLE instance (normal) Tue Mar 27 19:35: Global Enqueue Service Resources = 64, pool = 2 Tue Mar 27 19:35: Global Enqueue Service Enqueues = 128 LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 GES IPC: Receivers 1 Senders 1 GES IPC: Buffers Receive 1000 Send 530 Reserve 300 GES IPC: Msg Size Regular 432 Batch 2048 SCN scheme 3 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: System parameters with non-default values: processes = 150 timed_statistics = TRUE shared_pool_size = large_pool_size = java_pool_size = 0 lock_name_space = SGD9A control_files = /ora9/oradata/gd9a/control01.ctl, /ora9/oradata/gd9a/control02.ctl db_block_size = 8192 db_cache_size = compatible = remote_archive_enable = true log_archive_start = TRUE log_archive_dest_1 = location=/ora9/oradata/gd9a/archive log_archive_dest_state_1 = enable

34 standby_archive_dest = /ora9/oradata/gd9a/archive log_archive_trace = 127 fal_server = SGD9A fal_client = GD9A log_archive_format = %t_%s.dbf db_file_multiblock_read_count= 16 standby_file_management = auto fast_start_mttr_target = 300 instance_number = 1 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_retention = remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = GD9A service_names = GD9A hash_join_enabled = TRUE background_dump_dest = /ora9/admin/gd9a/bdump user_dump_dest = /ora9/admin/gd9a/udump core_dump_dest = /ora9/admin/gd9a/cdump sort_area_size = db_name = GD9A open_cursors = 300 star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = dg_broker_start = TRUE Tue Mar 27 19:35: cluster interconnect IPC version:oracle UDP/IP IPC Vendor 1 proto 2 Version 1.0 PMON started with pid=2 DIAG started with pid=3 LMON started with pid=4 LMD0 started with pid=5 DBW0 started with pid=6 LGWR started with pid=7 CKPT started with pid=8 SMON started with pid=9 RECO started with pid=10 Tue Mar 27 19:35: ARCH: STARTING ARCH PROCESSES ARC0 started with pid=11 ARC0: Archival started ARC1 started with pid=12 ARC1: Archival started Tue Mar 27 19:35: ARCH: STARTING ARCH PROCESSES COMPLETE Tue Mar 27 19:35: ARC0: Thread not mounted Tue Mar 27 19:35: ARC1: Thread not mounted DMON started with pid=13 Tue Mar 27 19:35: ALTER DATABASE MOUNT Tue Mar 27 19:35: WARNING: Setting LOCK_NAME_SPACE on non-standby database can be very dangerous It may even cause database corruption. Use it with caution Tue Mar 27 19:35: lmon registered with NM - instance id 1 (internal mem no 0) Tue Mar 27 19:35: Reconfiguration started List of nodes: 0, Global Resource Directory frozen one node partition Communication channels reestablished Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Resources and enqueues cleaned out

35 Resources remastered 0 0 GCS shadows traversed, 0 cancelled, 0 closed 0 GCS resources traversed, 0 cancelled set master node info Submitted all remote-enqueue requests Update rdomain variables Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted 0 GCS shadows traversed, 0 replayed, 0 unopened Submitted all GCS remote-cache requests 0 write requests issued in 0 GCS resources 0 PIs marked suspect, 0 flush PI msgs Tue Mar 27 19:35: Reconfiguration complete Post SMON to start 1st pass IR Tue Mar 27 19:35: Starting Broker (DMON) Tue Mar 27 19:35: Successful mount of redo thread 1, with mount id Tue Mar 27 19:35: Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Mar 27 19:35: ALTER DATABASE OPEN Tue Mar 27 19:35: LGWR: Primary database is in CLUSTER CONSISTENT mode Assigning activation ID (0x6b9f82d2) Thread 1 opened at log sequence 49 Current log# 1 seq# 49 mem# 0: /ora9/oradata/gd9a/redo01.log Successful open of redo thread 1. Tue Mar 27 19:35: SMON: enabling cache recovery Tue Mar 27 19:35: Undo Segment 1 Onlined Undo Segment 2 Onlined Undo Segment 3 Onlined Undo Segment 4 Onlined Undo Segment 5 Onlined Undo Segment 6 Onlined Undo Segment 7 Onlined Undo Segment 8 Onlined Undo Segment 9 Onlined Undo Segment 10 Onlined Successfully onlined Undo Tablespace 1. Dictionary check beginning Dictionary check complete Tue Mar 27 19:35: SMON: enabling tx recovery Tue Mar 27 19:35: Database Characterset is KO16MSWIN949 replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN SQL> select database_role,protection_mode from v$database; DATABASE_ROLE PROTECTION_MODE PRIMARY MAXIMUM PERFORMANCE

36 참조문서 oracle database9i: administrators student guide - oracle 기술문서 :oracle 9i data guard

37 4. 굿어스 Cinema Day 초대 박찬희씨 ( , 에게연락주세요. 2 차신청기간은 7 월 4 일 ( 수 ) 까지입니다

38 5. Pro-Active Tuning Service 5.1 실제사용자 (End-User) 관점의응답시간튜닝 Pro-active tuning service는사용자관점의모니터링및분석을통하여실제 End-User가느끼는응답시간 (Response Time) 을튜닝합니다. APM(Application Performance Management) 툴을이용하여 End-User의 Request 결과를반환받기까지의모든구간 (Client PC, Internet 구간, FireWall, DNS, Web Server, WAS, DBMS) 을분석하여가장 Delay Time 이많이소요된구간을찾아냅니다. ISP Backb one ISP Client Time Internet Time Firewall Time DNS Time Backend Systems Time 5.2 최상의성능상태로비즈니스고가용성을유지 Pro-Active Tuning Service 매업무단위프로젝트마다참여하여업무적용 (Open) 前문제요소를분석하여튜닝. 단위업무적용 (Open) 후매 3 개월 ( 데이터량갱신주기 ) 마다튜닝포인트를설정, 성능둔화요소를해결. 전사적으로새롭게추가되는업무단위프로젝트의모든 SQL 쿼리를검토및튜닝. 다양한대용량데이터베이스관리 / 튜닝기법을도입하여최적의 DB 상태를 1 년내내상시유지. 전략적튜닝 Factor 를분석, 투자대비효율이높은 Targeting 기법적용. ( 비중도높은 SQL 을튜닝함 )

39 5.3 Knowledge Transfer Pro-Active Tuning Service 는고객의 Business Process 를이해하고시스템을분석한후튜닝하는것으로완료되지않습니다. 실제로고객사환경에서튜닝한내용을그대로실무자들에게전수하여내부임직원의역량을제고시킵니다. 또한, Oracle RDBMS 신버젼의 New Features 를교육함으로써, 이용자 ( 관리자및개발자 ) 가스스로개발업무의효율및생산성을향상시킬수있도록지원합니다. 이외에도 DBMS 관리자를위한관리노하우 ( 고급 Trouble-Shooting, 대용량 DB 처리, 병렬처리등 ) 를전수함으로써, 최상의시스템을최고의기술로유지할수있도록지원합니다. UAS (User Adapted Seminar) 진행사례및내용 (Contents) 개발자를위한 SQL 튜닝실무사례세미나 G 쇼핑몰업체튜닝후실제고객사의튜닝사례를개발자들에게전수하여개발자들이성능을고려한 SQL 을작성할수있도록내부역량을제고시킴. Oracle 10g New Features 세미나 S Global 전자기업 : Oracle 10g 버전으로업그레이드하기전, 신버전의새로운기능과주의사항을전파함으로써, 업그레이드후발생할수있는문제점의사전제거와개발자들이새로운기능을이용함으로써, 개발생산성을향상시킴. K 국가기관 DBMS 관리노하우세미나내부관리자 (DBA,SE) 들을대상으로 DBMS 관리자들이흔히겪을수있는상황에대한 Administration Know-How 와고급 Trouble-Shooting 사례를소개함으로써, 관리기술력을향상시킴. 5.4 Tuning 범위확대 Pro-active tuning service 의제공범위는제한된 Database, 제한된 Server 에국한되지않으며, 고객사전체의 Server+DB 를대상으로그범위를확대함으로써고객사전체 Performance 향상에기여합니다

40 5.5 기대효과 재무적관점 기존 Tuning Service는주로 System Performance 향상에따른업무트레픽감소에초점이맞춰져있었습니다. Pro-actvice 서비스는 Tuning 작업을통한업무처리시간단축뿐만아니라, 업무처리시간단축으로가져올수있는재무적성과를가능하게합니다 서비스관점 단기적성능향상에맞추어진기존 Tuning 서비스는계약된 system 및 Database를서비스대상으로하기때문에전사적인차원의성능향상을기대하기어려웠습니다. Proactive tuning service는계약기간동안주요비즈니스 Factor별로 SLA를정하여 Tuning consulting을수행함으로써서비스자체의안정성을제고할수있습니다

41 5.5.3 사용자관점 Proactive tuning service는계약종료시점작업한 Tuning 산출물을통한기술전수세미나를진행함으로서고객사의사용자가실무에서바로적용가능한기술을전수함과동시에, 계약기간종료후에도 Proactive tuning service를유지할수있는방향을제시합니다 혁신적관점

42 - 42 -