반응형
OS환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.12.0.0 ADG
OS 및 사전 설정, ADG 구성은 아래 게시물 참조
Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 1(https://positivemh.tistory.com/819)
Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 2(https://positivemh.tistory.com/820)
방법 : Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3
1번 노드(Active) hostname : oel19adg1, db_name : ORAADG, db_unique_name : ORAADG
2번 노드(Standby) hostname : oel19adg2, db_name : ORAADG, db_unique_name : ORAADGDR
*본문에 2번노드 표시가 없으면 모두 1번노드에서 작업
설치파일은 아래 3가지 파일을 이용함(RAC 구성 할때 받아놓은 패치파일 중 grid 패치파일만 빼고 사용함)
DB : LINUX.X64_193000_db_home.zip
OPatch : p6880880_190000_Linux-x86-64.zip(12.2.0.1.27)
RU : p32900083_190000_Linux-x86-64.zip
Patch 32900083 - Combo of OJVM Component Release Update 19.12.0.0.210720 + Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720
ㄴ Patch 32895426 - Database Grid Infrastructure Jul 2021 Release Update 19.12.0.0.210720
ㄴ Patch 32876380 - Oracle JavaVM Component Release Update 19.12.0.0.210720
구성은 Part1, Part2에서 모두 완료함
이번 게시글에선 adg의 real time redo apply 테스트와 switchover, failover, reinstate를 테스트함
real time redo apply 테스트
현재 recovery mode 확인(1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
select dest_id, status, database_mode, recovery_mode, db_unique_name, gap_status
from v$archive_dest_status where dest_id <=2;
DEST_ID STATUS DATABASE_MODE RECOVERY_MODE DB_UNIQUE_NAME GAP_STATUS
---------- --------- --------------- ---------------------------------- -------------------- ------------------------
1 VALID OPEN IDLE ORAADG
2 VALID OPEN_READ-ONLY MANAGED REAL TIME APPLY WITH QUERY ORAADGDR NO GAP
|
현재 2번 노드가 REAL TIME APPLY 모드임
테이블 생성 및 데이터 삽입, 커밋(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> create table imsidg (no number);
Table created.
SQL> insert into imsidg values(1);
1 row created.
SQL> insert into imsidg values(2);
1 row created.
SQL> insert into imsidg values(3);
1 row created.
SQL> commit;
Commit complete.
|
현재 사용중인 백그라운드 프로세스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
1번 노드
SQL> select client_process,process,status,sequence# from v$managed_standby;
CLIENT_P PROCESS STATUS SEQUENCE#
-------- --------- ------------ ----------
N/A DGRD ALLOCATED 0
ARCH ARCH CLOSING 21
N/A DGRD ALLOCATED 0
ARCH ARCH CLOSING 21
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
LNS LNS OPENING 22
N/A DGRD ALLOCATED 0
LNS LNS WRITING 22
9 rows selected.
2번 노드
CLIENT_P PROCESS STATUS SEQUENCE#
-------- --------- ------------ ----------
ARCH ARCH CLOSING 21
N/A DGRD ALLOCATED 0
N/A DGRD ALLOCATED 0
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
ARCH ARCH CONNECTED 0
N/A MRP0 APPLYING_LOG 22
Archival RFS IDLE 0
LGWR RFS IDLE 22
UNKNOWN RFS IDLE 0
10 rows selected.
|
1번 노드는 LNS 프로세스가 내려쓰는 중임을 확인 가능함
2번 노드는 MRP 프로세스는 계속 APPLYING LOG 상태임 (로그를 적용하는 상태)
테이블 데이터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
1번 노드
SQL> select * from imsidg;
NO
----------
1
2
3
2번 노드
SQL> select * from imsidg;
NO
----------
1
2
3
|
동일한 값 확인 가능함
테이블 삭제 시도(2번 노드)
|
1
2
3
4
5
6
|
SQL> drop table imsidg purge;
drop table imsidg purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
|
2번 노드는 read only 모드이기 때문에 삭제는 불가능함
테이블 삭제(1번 노드)
|
1
2
3
|
SQL> drop table imsidg purge;
Table dropped.
|
정상적으로 삭제됨
switchover 테스트
*switchover 와 failover 란?
S/W나 H/W의 작업에 의해 계획된 전환은 Switchover라 하며,
운영 중에 Primary의 예기치 않은 장애에 대한 전환은 Failover 라고함
switchover 테스트(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADGDR
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADGDR"
Connecting ...
Connected to "ORAADGDR"
Connected as SYSDBA.
New primary database "ORAADGDR" is opening...
Operation requires start up of instance "ORAADG" on database "ORAADG"
Starting instance "ORAADG"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADG"
Database mounted.
Database opened.
Connected to "ORAADG"
Switchover succeeded, new primary is "oraadgdr"
|
정상적으로 switchover됨
configuration 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ dgmgrl sys/oracle
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 13 seconds ago)
|
정상적으로 switchover됨
switchover시 1번 노드 alert log
switchover시 1번 노드 브로커 log
switchover시 2번 노드 alert log
switchover시 2번 노드 브로커 log
failover 테스트
*switchover 와 failover 란?
S/W나 H/W의 작업에 의해 계획된 전환은 Switchover라 하며,
운영 중에 Primary의 예기치 않은 장애에 대한 전환은 Failover 라고함
failover 테스트를 위해 다시 switchover를 해서 ORAADG가 primary, ORAADGDR이 standby로 변경됨
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADG"
Connecting ...
Connected to "ORAADG"
Connected as SYSDBA.
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 54 seconds ago)
|
1번 노드 shutdown abort 실행(1번 노드)
|
1
2
|
SQL> shutdown abort
ORACLE instance shut down.
|
브로커 configuration 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
1번 노드
$ dgmgrl sys/oracle
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 30 17:19:26 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to an idle instance.
Connected as SYSDBA.
DGMGRL> show configuration
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
Configuration details cannot be determined by DGMGRL
2번 노드 dgmgrl로 접속
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Mar 30 17:19:31 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "ORAADGDR"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
Error: ORA-1034: ORACLE not available
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 0 seconds ago)
|
1번 노드 DB가 비정상 종료 되었기 때문에 configuration 조회 불가능함
2번 노드에서 조회는 가능하지만 현재 1번 노드가 사용불가능 상태임을 알려줌
switchover, failover 테스트(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADGDR
Error:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
Configuration details cannot be determined by DGMGRL
DGMGRL> failover to ORAADGDR
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
Configuration details cannot be determined by DGMGRL
|
현재 1번 노드가 비정상 종료 되었기 때문에 switchover, failover이 불가능함
failover 테스트(2번 노드로 접속)
|
1
2
3
4
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> failover to ORAADGDR
Performing failover NOW, please wait...
Failover succeeded, new primary is "oraadgdr"
|
failover 가 성공적으로 끝남 새로운 Primary db는 ORAADGDR(기존 2번 노드)가 됨
1번 노드 startup 시도(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
|
SQL> startup
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 8899664 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from being opened
|
mount 까지는 올라오지만 open 되지 못함
상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 56 seconds ago)
DGMGRL> show database ORAADGDR
Database - ORAADGDR
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORAADGDR
Database Status:
SUCCESS
DGMGRL> show database ORAADG
Database - ORAADG
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ORAADG
Database Status:
DISABLED - ORA-16661: the standby database needs to be reinstated
|
ORAADG 에 대해 ORA-16661: the standby database needs to be reinstated가 발생함
1번 노드는 failover로 인해 사용불가 상태가됨
재사용을 하려면 flashback database 가 on으로 되어있거나
off 인경우에는 데이터가드 재구성을 해줘야함
failover 한 db 복구(reinstate)(flashback database 가 on으로 설정되어 있어야함)
failover시 db가 open 상태였다면 open 상태에서 reinstate를 실행하면 되고
shutdown 되어있다면 startup mount 로 기동후 진행해야함
startup mount 기동
|
1
2
3
4
5
6
7
8
9
|
SQL> startup mount
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 8899664 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7876608 bytes
Database mounted.
|
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADG READ WRITE PRIMARY FAILED DESTINATION
2번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADGDR READ WRITE PRIMARY NOT ALLOWED
|
1번노드가 FAILED DESTINATION 로 나타남
reinstate 수행(2번 노드)(flashback database 가 on으로 설정되어있어야 가능)
|
1
2
3
4
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> reinstate database 'ORAADG'
Reinstating database "ORAADG", please wait...
Reinstatement of database "ORAADG" succeeded
|
정상적으로 완료됨
설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)
DGMGRL> show database ORAADG
Database - ORAADG
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 229.00 KByte/s
Real Time Query: ON
Instance(s):
ORAADG
Database Status:
SUCCESS
DGMGRL> show database ORAADGDR
Database - ORAADGDR
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORAADGDR
Database Status:
SUCCESS
|
ORAADGDR이 primary가 되고 ORAADG가 standby가 됨
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
2번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADGDR READ WRITE PRIMARY TO STANDBY
|
ORAADGDR이 primary(read write)가 되고 ORAADG가 standby(read only)가 됨
다시 원래 primary와 standby로 변경하기위해 switchover
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$ dgmgrl sys/oracle
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"
|
상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 86 seconds ago)
DGMGRL> show database ORAADG
Database - ORAADG
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORAADG
Database Status:
SUCCESS
DGMGRL> show database ORAADGDR
Database - ORAADGDR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 16.00 KByte/s
Real Time Query: ON
Instance(s):
ORAADGDR
Database Status:
SUCCESS
|
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADG READ WRITE PRIMARY TO STANDBY
2번 노드
SQL> select db_unique_name, open_mode, database_role, switchover_status from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------------------ -------------------- ---------------- --------------------
ORAADGDR READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED
|
다시 ORAADG가 primary(read write)가 되고 ORAADGDR이 standby(read only)가 됨
failover 한 db 복구(adg 재구성)(flashback database 가 off 인 경우 adg 재구성)
기존 브로커 configuration 제거(2번노드)
|
1
2
3
4
5
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> remove configuration
Warning: ORA-16620: one or more members could not be reached for a remove operation
Removed configuration
|
브로커 파라미터 false 설정(1번, 2번 노드)
1번 노드가 이미 shutdown 상태라면 아래 파라미터 파일 수정시 수정해줘도됨
|
1
2
3
|
SQL> alter system set dg_broker_start=false;
System altered.
|
장애 발생한 1번 노드 shutdown(1번 노드)
이미 shutdown 상태라면 다음단계로 넘어가도됨
|
1
|
SQL> shutdown immediate
|
브로커 설정파일 삭제(1번, 2번 노드)
|
1
2
3
4
5
6
7
|
1번 노드
$ cd $ORACLE_HOME/dbs
$ rm -rf dr1ORAADG.dat dr2ORAADG.dat
2번 노드
$ cd $ORACLE_HOME/dbs
$ rm -rf dr1ORAADGDR.dat dr2ORAADGDR.dat
|
파라미터 생성(1번 노드)
|
1
2
3
|
SQL> create pfile from spfile;
File created.
|
파라미터 파일 수정(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
$ cd $ORACLE_HOME/dbs
$ vi initORAADG.ora
*.audit_file_dest='/oracle/app/oracle/admin/ORAADG/adump'
*.audit_trail='none'
*.compatible='19.0.0'
*.control_files='/oracle/app/oracle/oradata/ORAADG/control01.ctl','/oracle/app/oracle/oradata/ORAADG/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_name='ORAADG'
*.db_recovery_file_dest_size=7851m
*.db_recovery_file_dest='/oracle/app/oracle/fast_recovery_area/'
*.db_unique_name='ORAADG'
*.dg_broker_start=FALSE
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORAADGXDB)'
*.fal_client='ORAADG'
*.fal_server=''
*.local_listener='LISTENER_ORAADG'
*.log_archive_config='DG_CONFIG=(ORAADG,ORAADGDR)'
*.log_archive_dest_1='LOCATION=/oracle/app/oracle/arch/adg1 valid_for=(all_logfiles, all_roles) db_unique_name=ORAADG'
#*.log_archive_dest_2=''
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=800m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2400m
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert = '/oracle/app/oracle/oradata/ORAADGDR/','/oracle/app/oracle/oradata/ORAADG/'
*.log_file_name_convert = '/oracle/app/oracle/oradata/ORAADGDR/','/oracle/app/oracle/oradata/ORAADG/'
|
11번째 줄 db_broker_start FALSE 처리
20번째 줄 log_archive_dest_2 주석 처리
34, 35번째 줄 convert 절 추가
기존 spfile 백업(1번 노드)
|
1
|
$ mv spfileORAADG.ora spfileORAADG.ora.bak
|
pfile을 이용해 db nomount 기동(1번 노드)
|
1
2
3
4
5
6
7
8
|
SQL> startup nomount pfile='/oracle/app/oracle/product/19c/dbs/initORAADG.ora'
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 8899664 bytes
Variable Size 536870912 bytes
Database Buffers 1962934272 bytes
Redo Buffers 7876608 bytes
|
rman 원격 접속
|
1
2
3
4
5
6
7
8
9
|
$ rman target sys/oracle@ORAADGDR auxiliary sys/oracle@ORAADG
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 30 17:43:24 2022
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAADG (DBID=2271425693)
connected to auxiliary database: ORAADG (not mounted)
|
duplicate 명령으로 복제
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 30-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=389 device type=DISK
contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format '/oracle/app/oracle/product/19c/dbs/orapwORAADG' ;
}
executing Memory Script
Starting backup at 30-MAR-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
Finished backup at 30-MAR-22
contents of Memory Script:
{
restore clone from service 'ORAADGDR' standby controlfile;
}
executing Memory Script
Starting restore at 30-MAR-22
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oracle/app/oracle/oradata/ORAADG/control01.ctl
output file name=/oracle/app/oracle/oradata/ORAADG/control02.ctl
Finished restore at 30-MAR-22
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/oracle/app/oracle/oradata/ORAADG/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oracle/app/oracle/oradata/ORAADG/system01.dbf";
set newname for datafile 2 to
"/oracle/app/oracle/oradata/ORAADG/sysaux01.dbf";
set newname for datafile 3 to
"/oracle/app/oracle/oradata/ORAADG/undotbs01.dbf";
set newname for datafile 4 to
"/oracle/app/oracle/oradata/ORAADG/users01.dbf";
restore
from nonsparse from service
'ORAADGDR' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/ORAADG/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 30-MAR-22
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oracle/app/oracle/oradata/ORAADG/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oracle/app/oracle/oradata/ORAADG/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oracle/app/oracle/oradata/ORAADG/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ORAADGDR
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/ORAADG/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-MAR-22
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1100713801 file name=/oracle/app/oracle/oradata/ORAADG/users01.dbf
Finished Duplicate Db at 30-MAR-22
|
상태확인(1번 노드)
|
1
2
3
4
5
|
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORAADG MOUNTED
|
duplicate 완료 후에는 2번 노드가 mount 상태가됨
db role 확인(1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
2번 노드
SQL> select database_role, open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PRIMARY READ WRITE
|
파라미터 파일 변경
spfile 생성(1번 노드)
|
1
2
3
|
SQL> create spfile from pfile;
File created.
|
1번 노드 mount 재기동(1번 노드)
|
1
2
|
SQL> shutdown immediate
SQL> startup mount
|
브로커 파라미터 true 설정(1번, 2번 노드)
|
1
2
3
|
SQL> alter system set dg_broker_start=true scope=both;
System altered.
|
configuration 설정(2번노드)
|
1
2
3
4
5
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> create configuration 'DR_ORAADG' as primary database is 'ORAADGDR' connect identifier is 'ORAADGDR';
Configuration "DR_ORAADG" created with primary database "ORAADGDR"
DGMGRL> add database 'ORAADG' as connect identifier is 'ORAADG';
Database "ORAADG" added
|
설정 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
|
설정 활성화
|
1
2
3
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> enable configuration
Enabled.
|
enable 후 바로 조회 했을 때는 에러가 발생했지만 잠시후 시도하니 에러가 사라짐
에러가 사라지지 않을 경우 노드1(standby db) shutdown immediate 후 startup mount로 재기동
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database
Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 34 seconds ago)
잠시후 다시 조회(1분 미만) 또는 standby 재기동 mount 후
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADGDR - Primary database
ORAADG - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 36 seconds ago)
|
정상화됨
로그스위치 확인
1번, 2번 노드 모두 alert log tail 로 확인
|
1
2
3
4
5
|
1번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadg/ORAADG/trace/alert_ORAADG.log
2번 노드
$ tail -f /oracle/app/oracle/diag/rdbms/oraadgdr/ORAADGDR/trace/alert_ORAADGDR.log
|
2번 노드(primary)에서 로그 스위치 실행(2번 노드)
|
1
2
3
|
SQL> alter system switch logfile;
System altered.
|
1번, 2번 노드 모두 alert log tail 로 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
1번 노드
2022-03-30T18:03:25.796271+09:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0
Mem# 0: /oracle/app/oracle/oradata/ORAADG/standby_redo01.log
2번 노드
2022-03-30T18:03:24.530214+09:00
Thread 1 advanced to log sequence 4 (LGWR switch), current SCN: 1524525
Current log# 1 seq# 4 mem# 0: /oracle/app/oracle/oradata/ORAADGDR/redo01.log
2022-03-30T18:03:24.536695+09:00
ARC0 (PID:13847): Archived Log entry 20 added for T-1.S-3 ID 0x87637cbb LAD:1
|
정상적으로 redo가 양쪽에 기록됨
db상태확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
1번 노드
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG MOUNTED PHYSICAL STANDBY NOT ALLOWED ENABLED
2번 노드
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR READ WRITE PRIMARY TO STANDBY ENABLED
|
Oracle Data Guard 구성 완료(Standby db가 mount 상태)
Active Data Guard로 전환(Standby db를 open 상태로 전환)
Managed Recovery Process 일시 중지 후 open 후 MRP 다시 기동(1번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
|
db 상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
1번 노드
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED ENABLED
2번 노드
SQL>
set lines 200 pages 1000
select db_unique_name, open_mode, database_role, switchover_status, dataguard_broker from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR
------------------------------ -------------------- ---------------- -------------------- --------
ORAADGDR READ WRITE PRIMARY TO STANDBY ENABLED
|
active db 상태는 그대로지만 standby db는 open_mode가 read only로 변경됨
에러 방지를 위한 추가 설정
StaticConnectIdentifierd 변경($ORACLE_HOME/network/admin/tnstnames.ora에 있는 tns 별칭으로 변경)(2번 노드)
|
1
2
3
4
5
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> edit database 'ORAADG' set property StaticConnectIdentifier = 'ORAADG';
Property "staticconnectidentifier" updated
DGMGRL> edit database 'ORAADGDR' set property StaticConnectIdentifier = 'ORAADGDR';
Property "staticconnectidentifier" updated
|
정상적으로 다시 구성이 완료됨
flashback database 확인(1번 노드)
|
1
2
3
4
5
|
SQL> select db_unique_name, flashback_on from v$database;
DB_UNIQUE_NAME FLASHBACK_ON
------------------------------ ------------------
ORAADG NO
|
switchover로 정상화
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ dgmgrl sys/oracle@ORAADGDR
DGMGRL> switchover to ORAADG
Performing switchover NOW, please wait...
Operation requires a connection to database "ORAADG"
Connecting ...
Connected to "ORAADG"
Connected as SYSDBA.
New primary database "ORAADG" is opening...
Operation requires start up of instance "ORAADGDR" on database "ORAADGDR"
Starting instance "ORAADGDR"...
Connected to an idle instance.
ORACLE instance started.
Connected to "ORAADGDR"
Database mounted.
Database opened.
Connected to "ORAADGDR"
Switchover succeeded, new primary is "oraadg"
|
상태 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
DGMGRL> show configuration
Configuration - DR_ORAADG
Protection Mode: MaxPerformance
Members:
ORAADG - Primary database
ORAADGDR - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 65 seconds ago)
DGMGRL> show database ORAADG
Database - ORAADG
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORAADG
Database Status:
SUCCESS
DGMGRL> show database ORAADGDR
Database - ORAADGDR
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 51.00 KByte/s
Real Time Query: ON
Instance(s):
ORAADGDR
Database Status:
SUCCESS
|
정상적으로 ORAADG가 primary가 되고 ORAADGDR이 standby가 됨
switch over 후 ORAADG를 primary로 변경 한 상태에서
flashback database 활성화(선택)(fra 설정되어있어야 가능함)(1번 노드)
|
1
2
3
|
SQL> alter database flashback on;
Database altered.
|
flashback database 확인(1번, 2번 노드)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
1번 노드
SQL> select db_unique_name, flashback_on from v$database;
DB_UNIQUE_NAME FLASHBACK_ON
------------------------------ ------------------
ORAADG YES
2번 노드
SQL> select db_unique_name, flashback_on from v$database;
DB_UNIQUE_NAME FLASHBACK_ON
------------------------------ ------------------
ORAADGDR YES
|
failover 후 복구(adg 재구성) 완료
출처 : Oracle Linux 8.4에 Oracle 19c ADG 설치 가이드_Part 3 :: 내맘대로긍정 (tistory.com)
반응형
'오라클 > 참고' 카테고리의 다른 글
| 오라클 19c rman catalog db 이용 백업, 복구 (0) | 2023.01.10 |
|---|---|
| 오라클 19c rman catalog 및 catalog db 생성 (0) | 2023.01.10 |
| 오라클 19c ADG 설치2 (0) | 2023.01.05 |
| 오라클 19c ADG 설치1 (0) | 2023.01.05 |
| 오라클 RAC 설치 간단 정리 (2) | 2023.01.04 |