[2025-05-29] RAC to Single ADG 환경에서 Standby에서 아직 적용되지 않은 아카이브 로그가 Primary에서 유실 시 From Service를 이용한 복구 방법 (recover database from service) (12cR2)
[실습
환경]
<Primary>
--소스
OS : Oracle Linux Server 7.9 (Linux rdb01d
5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle Database 12c Enterprise Edition
Release 12.2.0.1.0 - 64bit Production
RAC1 : rdb01d(192.168.0.31) :
DRDB1
RAC2 : rdb01d(192.168.0.32) : DRDB2
db_unique_name :
DRDB
<Standby>
--타켓
OS : Oracle Linux Server 7.9 (Linux rdb01d
5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle Database 12c Enterprise Edition
Release 12.2.0.1.0 - 64bit Production
RAC1 : tdb01p(192.168.0.41) :
DRDB
db_unique_name : DRDB_STB
1. 타켓의 아카이브
수신 및 적용 상태 확인
[2025-06-08:17:48:15][tdb01p]<SYS@DRDB>
COL CREATOR FOR A10
COL APPLIED FOR A10
COL RESETLOGS_TIME
FOR A40
COL FIRST_TIME FOR A40
COL NEXT_TIME FOR A40
SELECT
B.THREAD#
, B.SEQUENCE#
, B.RESETLOGS_TIME
,
B.FIRST_TIME
,
B.NEXT_TIME
, B.CREATOR
, B.APPLIED
,
B.ARCHIVED
FROM
(
SELECT A.THREAD#, MAX(A.SEQUENCE#) AS
SEQUENCE#, MAX(A.RESETLOGS_ID) AS RESETLOGS_ID
FROM
V$ARCHIVED_LOG A
WHERE A.RESETLOGS_TIME = (SELECT
MAX(K.RESETLOGS_TIME) FROM V$ARCHIVED_LOG K)
GROUP BY
A.THREAD#
ORDER BY A.THREAD#
) A
, V$ARCHIVED_LOG B
WHERE
A.RESETLOGS_ID = B.RESETLOGS_ID
AND A.THREAD# = B.THREAD#
AND A.SEQUENCE# = B.SEQUENCE#
;
THREAD# SEQUENCE#
RESETLOGS_TIME
FIRST_TIME
NEXT_TIME
CREATOR APPLIED ARCHIVED
----------
---------- ----------------------------------------
----------------------------------------
---------------------------------------- ---------- ----------
---------
1 76
2025-05-06:10:24:18
2025-06-08:21:46:52
2025-06-08:21:48:31
ARCH
YES
YES
2 74
2025-05-06:10:24:18
2025-06-08:21:46:54
2025-06-08:21:48:30
ARCH
YES YES
-- standby db를 내림 (PRIMARY의
아카이브를 수신받지 못하도록 일단 내림!)
[2025-06-08:17:48:15][tdb01p]<SYS@DRDB> shutdown immediate;
[+ASM:grid@tdb01p][/home/grid]$ asmcmd ls -sl
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread*1*seq*76*
Type Redund Striped
Time
Sys Block_Size Blocks Bytes Space
Name
ARCHIVELOG UNPROT COARSE JUN 08 21:00:00
Y
512 108 55296 4194304
thread_1_seq_76.418.1203284911
[+ASM:grid@tdb01p][/home/grid]$ asmcmd ls -sl
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread*2*seq*74*
Type Redund Striped
Time
Sys Block_Size Blocks Bytes Space
Name
ARCHIVELOG UNPROT COARSE JUN 08 21:00:00
Y
512 102 52224 4194304
thread_2_seq_74.413.1203284909
2. 소스에서
아카이브 로그를 갱신함
--RAC1번 노드
[2025-06-08:22:46:39][rdb01d]<SYS@DRDB1> archive log list;
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
+FRA1
Oldest online log sequence 75
Next log
sequence to archive 77
Current log
sequence
77
--> THREAD 1은 76번까지 완료
--RAC2번
노드
[2025-06-08:22:47:12][rdb02d]<SYS@DRDB2> archive log list
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
+FRA1
Oldest online log sequence 73
Next log
sequence to archive 75
Current log
sequence
75
--> THREAD 2은 74번까지 완료
[2025-06-08:17:22:12][rdb01d]<SYS@DRDB1> ALTER SYSTEM ARCHIVE LOG CURRENT;
[2025-06-08:22:46:39][rdb01d]<SYS@DRDB1> archive log list;
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
+FRA1
Oldest online log sequence 76
Next log
sequence to archive 78
Current log
sequence
78
--> thread 1기준으로는
77이 생기고
[2025-06-08:18:13:33][rdb02d]<SYS@DRDB2> archive log list;
Database log
mode
Archive Mode
Automatic
archival
Enabled
Archive
destination
+FRA1
Oldest online log sequence 74
Next log
sequence to archive 76
Current log
sequence
76
--> thread 2 기준으로는 75가 생김 (RAC1번에서만 ALTER
SYSTEM ARCHIVE LOG CURRENT를 했는데 2번도 아카이브로그가 생긴것임)
[+ASM1:grid@rdb01d][/home/grid]$ asmcmd ls -sl
+FRA1/DRDB/ARCHIVELOG/2025_06_08/
Type Redund Striped
Time
Sys Block_Size Blocks Bytes
Space Name
ARCHIVELOG UNPROT COARSE JUN 08
21:00:00 Y
512 108 55296 4194304
thread_1_seq_76.340.1203284911
ARCHIVELOG UNPROT
COARSE JUN 08 22:00:00
Y 512
14186 7263232 8388608 thread_1_seq_77.593.1203288451
-->
생성
ARCHIVELOG UNPROT COARSE JUN 08
21:00:00 Y
512 140 71680 4194304
thread_2_seq_73.594.1203284815
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 102 52224 4194304
thread_2_seq_74.313.1203284911
ARCHIVELOG UNPROT
COARSE JUN 08 22:00:00
Y 512
11805 6044160 8388608 thread_2_seq_75.596.1203288453
-->
생성
3. 타켓에서
아카이브 파일 상황을 확인
[+ASM:grid@tdb01p][/home/grid]$ asmcmd ls -sl
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y 512
26061 13343232 16777216
thread_1_seq_74.417.1203284709
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 126 64512
4194304 thread_1_seq_75.412.1203284811
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 108 55296
4194304 thread_1_seq_76.418.1203284911
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 472 241664
4194304 thread_2_seq_71.283.1203276645
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y 512
23988 12281856 12582912
thread_2_seq_72.365.1203284709
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 140 71680
4194304 thread_2_seq_73.433.1203284813
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 102 52224
4194304 thread_2_seq_74.413.1203284909
--> 타켓이 내려간 상태기 때문에 아직 아카이브가 수신 안됐음 (thread 1의 77 및 thread 2의
75가 없음)
4. 소스에서
아카이브 로그를 강제로 유실 시킴
[+ASM1:grid@rdb01d][/home/grid]$ asmcmd ls -sl
+FRA1/DRDB/ARCHIVELOG/2025_06_08/
Type Redund Striped
Time
Sys Block_Size Blocks Bytes
Space Name
ARCHIVELOG UNPROT COARSE JUN 08
21:00:00 Y
512 108 55296 4194304
thread_1_seq_76.340.1203284911
ARCHIVELOG UNPROT
COARSE JUN 08 22:00:00
Y 512
14186 7263232 8388608
thread_1_seq_77.593.1203288451
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 140 71680 4194304
thread_2_seq_73.594.1203284815
ARCHIVELOG UNPROT
COARSE JUN 08 21:00:00
Y
512 102 52224 4194304
thread_2_seq_74.313.1203284911
ARCHIVELOG UNPROT
COARSE JUN 08 22:00:00
Y 512
11805 6044160 8388608
thread_2_seq_75.596.1203288453
-->thread_1_seq_77.593.1203288451 과
-->thread_2_seq_75.596.1203288453 은 아직 타켓으로 전송 못시킨 상황임!
[+ASM1:grid@rdb01d][/home/grid]$ asmcmd rm -f
+FRA1/DRDB/ARCHIVELOG/2025_06_08/thread_1_seq_77.593.1203288451
[+ASM1:grid@rdb01d][/home/grid]$
asmcmd rm -f
+FRA1/DRDB/ARCHIVELOG/2025_06_08/thread_2_seq_75.596.1203288453
--> 삭제함 이제 타켓은 실시간 동기화가 안될 것임
5. 타켓을 다시
startup read only한 후 동기화 상태 확인
[2025-06-08:22:32:19][tdb01p]<SYS@DRDB> startup
ORACLE instance
started.
Total
System Global Area 805306368 bytes
Fixed
Size
8625856 bytes
Variable
Size
352321856 bytes
Database
Buffers 436207616
bytes
Redo
Buffers
8151040 bytes
Database mounted.
Database opened.
[2025-06-08:22:32:19][tdb01p]<SYS@DRDB> alter database recover managed standby database using current
logfile disconnect from session;
alter database recover managed standby
database using current logfile disconnect from session
*
ERROR at line
1:
ORA-01153: an incompatible media recovery is active
--> 이미 동기화 중이라는 것임(자동으로 동기화 시작함, DG_BROKER_START=TRUE)
--타켓에서 아래의 sql문으로
확인하면 삭제한 아카이브 로그 파일이 수신 안되는 것을 확인할 수 있음
SELECT A.*
FROM
V$ARCHIVED_LOG A
WHERE A.RESETLOGS_TIME = (SELECT
MAX(K.RESETLOGS_TIME) FROM V$ARCHIVED_LOG K)
ORDER BY A.THREAD#,
A.SEQUENCE#
;
[DRDB:oracle@tdb01p][/u01/app/oracle/product/12c/db_1/network/admin]$
dgmgrl sysdg/Oracle123$@TNS_DRDB_STB
DGMGRL for Linux: Release 12.2.0.1.0 -
Production on Sun Jun 8 18:33:20 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to
DGMGRL, type "help" for information.
Connected to "DRDB_STB"
Connected as
SYSDG.
DGMGRL> show database "drdb_stb";
Database - drdb_stb
Role:
PHYSICAL STANDBY
Intended State:
APPLY-ON
Transport Lag: 21 minutes 24
seconds (computed 1 second ago)
Apply
Lag: 21 minutes 25 seconds
(computed 1 second ago)
Average Apply Rate: 0 Byte/s
Real
Time Query: ON
Instance(s):
DRDB
Database Warning(s):
ORA-16853: apply lag has
exceeded specified threshold
ORA-16855: transport lag has
exceeded specified threshold
Database
Status:
WARNING
[2025-06-08:17:48:15][tdb01p]<SYS@DRDB>
col SOURCE_DB_UNIQUE_NAME for a30
col NAME for a30
col
TIME_COMPUTED for a30
col VALUE for a20
select SOURCE_DB_UNIQUE_NAME,
NAME, VALUE, TIME_COMPUTED from v$dataguard_stats;
SOURCE_DB_UNIQUE_NAME
NAME
VALUE
TIME_COMPUTED
------------------------------ ------------------------------
--------------------
------------------------------
DRDB
transport
lag
+00 00:21:45 06/08/2025
22:56:17
DRDB
apply
lag
+00 00:21:46 06/08/2025
22:56:17
DRDB
apply finish
time
(NULL)
06/08/2025
22:56:17
(NULL)
estimated startup time
44
06/08/2025 22:56:17
6. 타켓(STANDBY)에서 RECOVER database from service를 이용하여 복구 시작
[DRDB:oracle@tdb01p][/u01/app/oracle/product/12c/db_1/network/admin]$ cat
tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12c/db_1/network/admin/tnsnames.ora
# Generated by
Oracle configuration tools.
LISTENER_PTDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = tdb01p)(PORT
= 1521))
PTDB
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL =
TCP)(HOST = tdb01p)(PORT = 1521))
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
PTDB)
)
)
##########
For ADG Start ##########
## For Primary
TNS_DRDB =
(DESCRIPTION
=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.31)(PORT =
1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.0.32)(PORT =
1521))
(LOAD_BALANCE =
OFF)
(FAILOVER =
ON)
)
(CONNECT_DATA
=
(SERVER =
DEDICATED)
(SERVICE_NAME =
DRDB)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD
= BASIC))
)
)
-->TNS_DRDB --> 이게 바로 소스의 tns info임
## For
Standby
TNS_DRDB_STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.41)(PORT =
1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =
DRDB_STB)
)
)
########## For ADG END
##########
[2025-06-08:22:56:16][tdb01p]<SYS@DRDB> shutdown immediate;
Database closed.
Database
dismounted.
ORACLE instance shut down.
[2025-06-08:22:56:16][tdb01p]<SYS@DRDB> startup mount;
ORACLE instance
started.
Total
System Global Area 805306368 bytes
Fixed
Size
8625856 bytes
Variable
Size
348127552 bytes
Database
Buffers 440401920
bytes
Redo
Buffers
8151040 bytes
Database mounted.
[2025-06-08:22:56:16][tdb01p]<SYS@DRDB>
COL NAME FOR A80
SELECT 'CATALOG DATAFILECOPY ' || ''''|| NAME
|| '''' ||';' AS NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
CATALOG
DATAFILECOPY '+DATA1/DRDB_STB/DATAFILE/system.332.1203276363';
CATALOG
DATAFILECOPY '+DATA1/DRDB_STB/DATAFILE/tuner_data1.333.1203276399';
CATALOG
DATAFILECOPY '+DATA1/DRDB_STB/DATAFILE/sysaux.334.1203276415';
CATALOG
DATAFILECOPY '+DATA1/DRDB_STB/DATAFILE/undotbs1.335.1203276461';
CATALOG
DATAFILECOPY '+DATA1/DRDB_STB/DATAFILE/undotbs2.336.1203276465';
CATALOG
DATAFILECOPY '+DATA1/DRDB_STB/DATAFILE/users.337.1203276465';
CATALOG
DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/tuner_idx1.338.1203276467';
7 rows selected.
--> 추후 사용하기 위해서 타켓의 데이터 파일 정보를 메모장 같은 곳에 기록해둠
COL NAME
FOR A80
SELECT FILE#, NAME FROM V$TEMPFILE;
FILE# NAME
----------
--------------------------------------------------------------------------------
1
+DATA1/DRDB_STB/TEMPFILE/temp.339.1203276641
2 +DATA1/DRDB_STB/TEMPFILE/tuner_temp.340.1203276643
COL
MEMBER FOR A80
SELECT MEMBER FROM V$LOGFILE ORDER BY GROUP#;
MEMBER
--------------------------------------------------------------------------------
+FRA1/DRDB_STB/ONLINELOG/group_1.459.1199740711
+DATA1/DRDB_STB/ONLINELOG/group_1.314.1199740713
+FRA1/DRDB_STB/ONLINELOG/group_2.460.1199740717
+DATA1/DRDB_STB/ONLINELOG/group_2.315.1199740717
+FRA1/DRDB_STB/ONLINELOG/group_3.461.1199740721
+DATA1/DRDB_STB/ONLINELOG/group_3.316.1199740721
+FRA1/DRDB_STB/ONLINELOG/group_4.462.1199740723
+DATA1/DRDB_STB/ONLINELOG/group_4.303.1199740725
+DATA1/DRDB_STB/ONLINELOG/group_5.310.1199741191
+FRA1/DRDB_STB/ONLINELOG/group_5.449.1199741191
+DATA1/DRDB_STB/ONLINELOG/group_6.300.1199741193
+FRA1/DRDB_STB/ONLINELOG/group_6.451.1199741195
+DATA1/DRDB_STB/ONLINELOG/group_7.290.1199741195
+FRA1/DRDB_STB/ONLINELOG/group_7.450.1199741197
+DATA1/DRDB_STB/ONLINELOG/group_8.295.1199741197
+FRA1/DRDB_STB/ONLINELOG/group_8.453.1199741199
+DATA1/DRDB_STB/ONLINELOG/group_9.304.1199741201
+FRA1/DRDB_STB/ONLINELOG/group_9.455.1199741201
+DATA1/DRDB_STB/ONLINELOG/group_10.292.1199741203
+FRA1/DRDB_STB/ONLINELOG/group_10.452.1199741203
[+ASM:grid@tdb01p][/home/grid]$ asmcmd ls -sl
+DATA1/DRDB_STB/DATAFILE
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
DATAFILE
UNPROT COARSE JUN 08 22:00:00
Y 8192 189441
1551900672 1560281088 SYSAUX.334.1203276415
DATAFILE
UNPROT COARSE JUN 08 22:00:00
Y 8192
108801 891297792 897581056
SYSTEM.332.1203276363
DATAFILE UNPROT COARSE JUN 08
22:00:00 Y
8192 196609 1610620928 1619001344
TUNER_DATA1.333.1203276399
DATAFILE UNPROT COARSE JUN
08 22:00:00 Y
8192 131073 1073750016 1082130432
TUNER_IDX1.338.1203276467
DATAFILE UNPROT COARSE JUN
08 22:00:00 Y
8192 23681 193994752 197132288
UNDOTBS1.335.1203276461
DATAFILE UNPROT COARSE JUN 08
22:00:00 Y
8192 9601 78651392
79691776 UNDOTBS2.336.1203276465
DATAFILE UNPROT
COARSE JUN 08 22:00:00
Y
8192 641
5251072 8388608
USERS.337.1203276465
[2025-06-08:23:00:52][tdb01p]<SYS@DRDB> select file#, checkpoint_change#, creation_change# from
v$datafile_header;
FILE# CHECKPOINT_CHANGE#
CREATION_CHANGE#
---------- ------------------
----------------
1
13905524
7
2
13905524
2398280
3
13905524
4665
4
13905524
1406609
5
13905524
1418153
7
13905524
29999
8
13905524
2398323
--> SHUTDOWN IMMEDIATE 후 STARTUP MOUNT한 상태라 모든 데이터파일헤더의 SCN이 같음
[2025-06-08:23:00:52][tdb01p]<SYS@DRDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
CANCEL;
--> 타켓의 동기화를 취소(동기화를 중지 시키는 것임)
[DRDB:oracle@tdb01p][/home/oracle]$ alias
rt
alias rt='rman target /'
[DRDB:oracle@tdb01p][/home/oracle]$ rt
Recovery Manager: Release 12.2.0.1.0 -
Production on Sun Jun 8 23:00:21 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: DRDB (DBID=1008397436, not open)
--소스에 존재하는 DB에서 증분백업본을 만들어서 그걸 전송시켜서 타켓을 RECOVER하는 것임
RMAN> recover database from service TNS_DRDB noredo using compressed backupset;
Starting
recover at 25/06/08
using channel ORA_DISK_1
channel ORA_DISK_1: starting
incremental datafile backup set restore
channel ORA_DISK_1: using compressed
network backup set from service TNS_DRDB
destination for restore of datafile
00001: +DATA1/DRDB_STB/DATAFILE/system.332.1203276363
channel ORA_DISK_1:
restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting
incremental datafile backup set restore
channel ORA_DISK_1: using compressed
network backup set from service TNS_DRDB
destination for restore of datafile
00002: +DATA1/DRDB_STB/DATAFILE/tuner_data1.333.1203276399
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1:
starting incremental datafile backup set restore
channel ORA_DISK_1: using
compressed network backup set from service TNS_DRDB
destination for restore
of datafile 00003: +DATA1/DRDB_STB/DATAFILE/sysaux.334.1203276415
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1:
starting incremental datafile backup set restore
channel ORA_DISK_1: using
compressed network backup set from service TNS_DRDB
destination for restore
of datafile 00004: +DATA1/DRDB_STB/DATAFILE/undotbs1.335.1203276461
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1:
starting incremental datafile backup set restore
channel ORA_DISK_1: using
compressed network backup set from service TNS_DRDB
destination for restore
of datafile 00005: +DATA1/DRDB_STB/DATAFILE/undotbs2.336.1203276465
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1:
starting incremental datafile backup set restore
channel ORA_DISK_1: using
compressed network backup set from service TNS_DRDB
destination for restore
of datafile 00007: +DATA1/DRDB_STB/DATAFILE/users.337.1203276465
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1:
starting incremental datafile backup set restore
channel ORA_DISK_1: using
compressed network backup set from service TNS_DRDB
destination for restore
of datafile 00008: +DATA1/DRDB_STB/DATAFILE/tuner_idx1.338.1203276467
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished recover at 25/06/08
[+ASM:grid@tdb01p][/home/grid]$ asmcmd ls -sl
+DATA1/DRDB_STB/DATAFILE
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
DATAFILE
UNPROT COARSE JUN 08 23:00:00
Y 8192 189441
1551900672 1560281088 SYSAUX.334.1203276415
DATAFILE
UNPROT COARSE JUN 08 23:00:00
Y 8192
108801 891297792 897581056
SYSTEM.332.1203276363
DATAFILE UNPROT COARSE JUN 08
23:00:00 Y
8192 196609 1610620928 1619001344
TUNER_DATA1.333.1203276399
DATAFILE UNPROT COARSE JUN
08 23:00:00 Y
8192 131073 1073750016 1082130432
TUNER_IDX1.338.1203276467
DATAFILE UNPROT COARSE JUN
08 23:00:00 Y
8192 23681 193994752 197132288
UNDOTBS1.335.1203276461
DATAFILE UNPROT COARSE JUN 08
23:00:00 Y
8192 9601 78651392
79691776 UNDOTBS2.336.1203276465
DATAFILE UNPROT
COARSE JUN 08 23:00:00
Y
8192 641
5251072 8388608
USERS.337.1203276465
SELECT
NAME
, CURRENT_SCN
,
CHECKPOINT_CHANGE#
FROM V$DATABASE
;
NAME
CURRENT_SCN CHECKPOINT_CHANGE#
--------------------------- -----------
------------------
DRDB
13905523
13868343
[2025-06-08:23:00:52][tdb01p]<SYS@DRDB> select file#, checkpoint_change#, creation_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
CREATION_CHANGE#
---------- ------------------
----------------
1
13911028
7
2
13911052
2398280
3
13911362
4665
4
13911407
1406609
5
13911418
1418153
7
13911422
29999
8
13911429
2398323
--> 타켓의 데이터파일헤더의 SCN이 각기 다르기 때문에 OPEN을 할수 없는 상황임
(소스에 증분 백업본으로 타켓의 각 데이터 파일을 저 위치까지 RECOVER한 것임!)
RMAN> shutdown immediate;
database
dismounted
Oracle instance shut down
RMAN> startup nomount;
Oracle instance started
Total System Global Area 805306368 bytes
Fixed
Size
8625856 bytes
Variable
Size
352321856 bytes
Database
Buffers
436207616 bytes
Redo
Buffers
8151040 bytes
--소스(PRIMARY)에서 STARNDBY CONTROLFILE을 가져와서 타켓(STANDBY)에 RESOTRE함
(결국 해당 타켓 DB를 오픈 시킬려면 PRIMARY에서
최신의 STANDBY용 CONTROLFILE을 가져와야 하는 것임!)
RMAN> restore standby controlfile
from service TNS_DRDB;
Starting
restore at 25/06/08
using target database control file instead of recovery
catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device
type=DISK
channel
ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using
network backup set from service TNS_DRDB
channel ORA_DISK_1: restoring
control file
channel ORA_DISK_1: restore complete, elapsed time:
00:00:02
output file
name=+DATA1/DRDB_STB/CONTROLFILE/current.272.1200267603
output file
name=+FRA1/DRDB_STB/CONTROLFILE/current.275.1200267603
Finished restore at
25/06/08
RMAN> alter database mount;
Statement
processed
released channel: ORA_DISK_1
RMAN> report schema;
Starting
implicit crosscheck backup at 25/06/08
allocated channel:
ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
Crosschecked 10
objects
Finished implicit crosscheck backup at 25/06/08
Starting
implicit crosscheck copy at 25/06/08
using channel ORA_DISK_1
Finished
implicit crosscheck copy at 25/06/08
searching
for all files in the recovery area
cataloging files...
cataloging
done
List of
Cataloged Files
=======================
File Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_72.365.1203284709
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_74.417.1203284709
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_75.412.1203284811
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_73.433.1203284813
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_74.413.1203284909
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_76.418.1203284911
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_76.466.1203288877
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_78.443.1203288879
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_77.434.1203289093
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_79.435.1203289093
File
Name:
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_71.283.1203276645
--> 현재 타켓에는 THREAD 1의 77과 TRHEAD2의 75가 존재안함
RMAN-06139:
warning: control file is not current for REPORT SCHEMA
Report of database
schema for database with db_unique_name DRDB_STB
List
of Permanent Datafiles
===========================
File Size(MB)
Tablespace RB segs
Datafile Name
---- -------- -------------------- -------
------------------------
1
0
SYSTEM
***
+DATA1/MUST_RENAME_THIS_DATAFILE_1.4294967295.4294967295
2
0
TUNER_DATA1
***
+DATA1/MUST_RENAME_THIS_DATAFILE_2.4294967295.4294967295
3
0
SYSAUX
***
+DATA1/MUST_RENAME_THIS_DATAFILE_3.4294967295.4294967295
4
0
UNDOTBS1
***
+DATA1/MUST_RENAME_THIS_DATAFILE_4.4294967295.4294967295
5
0
UNDOTBS2
***
+DATA1/MUST_RENAME_THIS_DATAFILE_5.4294967295.4294967295
7
0
USERS
***
+DATA1/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295
8
0
TUNER_IDX1
***
+DATA1/MUST_RENAME_THIS_DATAFILE_8.4294967295.4294967295
--> 소스(PRIMARY)의 STANDBY CONTROLFILE을 RESOTRE했기 때문에 DATAFILE을 인식하지 못함
List of Temporary
Files
=======================
File Size(MB)
Tablespace
Maxsize(MB) Tempfile Name
---- -------- -------------------- -----------
--------------------
1 20
TEMP
32767
+DATA1/MUST_RENAME_THIS_TEMPFILE_1.4294967295.4294967295
2
128
TUNER_TEMP
32767
+DATA1/MUST_RENAME_THIS_TEMPFILE_2.4294967295.4294967295
run
{
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/system.332.1203276363';
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/tuner_data1.333.1203276399';
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/sysaux.334.1203276415';
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/undotbs1.335.1203276461';
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/undotbs2.336.1203276465';
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/users.337.1203276465';
CATALOG DATAFILECOPY
'+DATA1/DRDB_STB/DATAFILE/tuner_idx1.338.1203276467';
}
--> DATAFILECOPY 명령으로 RMAN이 STANDBY의 DATAFILE을 인식할 수 있도록 조치함
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/system.332.1203276363 RECID=7
STAMP=1203289657
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/tuner_data1.333.1203276399 RECID=8
STAMP=1203289657
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/sysaux.334.1203276415 RECID=9
STAMP=1203289658
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/undotbs1.335.1203276461 RECID=10
STAMP=1203289658
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/undotbs2.336.1203276465 RECID=11
STAMP=1203289658
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/users.337.1203276465 RECID=12
STAMP=1203289658
cataloged
datafile copy
datafile copy file
name=+DATA1/DRDB_STB/DATAFILE/tuner_idx1.338.1203276467 RECID=13
STAMP=1203289658
RMAN> switch database to copy;
datafile 1
switched to datafile copy
"+DATA1/DRDB_STB/DATAFILE/system.332.1203276363"
datafile 2 switched to
datafile copy "+DATA1/DRDB_STB/DATAFILE/tuner_data1.333.1203276399"
datafile
3 switched to datafile copy
"+DATA1/DRDB_STB/DATAFILE/sysaux.334.1203276415"
datafile 4 switched to
datafile copy "+DATA1/DRDB_STB/DATAFILE/undotbs1.335.1203276461"
datafile 5
switched to datafile copy
"+DATA1/DRDB_STB/DATAFILE/undotbs2.336.1203276465"
datafile 7 switched to
datafile copy "+DATA1/DRDB_STB/DATAFILE/users.337.1203276465"
datafile 8
switched to datafile copy
"+DATA1/DRDB_STB/DATAFILE/tuner_idx1.338.1203276467"
--> 타켓(STANDBY)의 컨트롤 파일의 내용을 바꿔줌
RMAN> RECOVER DATABASE;
Starting
recover at 25/06/08
using channel ORA_DISK_1
starting media recovery
archived
log for thread 1 with sequence 80 is already on disk as file
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_80.410.1203289635
archived
log for thread 2 with sequence 78 is already on disk as file
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_78.452.1203289629
archived
log for thread 2 with sequence 79 is already on disk as file
+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_79.454.1203289629
archived
log file
name=+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_1_seq_80.410.1203289635
thread=1 sequence=80
archived log file
name=+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_78.452.1203289629
thread=2 sequence=78
archived log file
name=+FRA1/DRDB_STB/ARCHIVELOG/2025_06_08/thread_2_seq_79.454.1203289629
thread=2 sequence=79
media recovery complete, elapsed time:
00:00:01
Finished recover at 25/06/08
--> THREAD 1의 80과 THREAD 2의 78,79는 RECOVER DATABASE FROM SERVICE 이후 Data Guard Redo 전송 메커니즘이나 수동 전송으로 Standby FRA에 존재하게 된 상태.
(없으면 소스에서 가져와야하는 것임!)
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> alter database open;
--> 아카이브 로그로 컨트롤 파일의 SCN과 DATAFILE의 SCN이 완전히 일치하는 상황까지 RECOVER했으니 OPEN이 가능한 상황인 것임
--> 오픈 시 TEMPFILE을 다시 재생성됨
Database altered.
Elapsed: 00:00:06.34
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> shutdown immediate;
Database closed.
Database
dismounted.
ORACLE instance shut down.
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> startup mount
ORACLE instance
started.
Total
System Global Area 805306368 bytes
Fixed
Size
8625856 bytes
Variable
Size
352321856 bytes
Database
Buffers 436207616
bytes
Redo
Buffers
8151040 bytes
Database mounted.
--> 마운트까지 간 후 REDO LOG를 다시 생성해줌
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB>
col member for a80
col status for a20
select a.group#,
a.member, b.bytes/1024/1024 as mb, b.status from v$logfile a, v$log b where
a.member like '%MUST_RENAME_THIS_LOGFILE%' and a.group# =
b.group#;
GROUP#
MEMBER
MB
----------
--------------------------------------------------------------------------------
----------
2
+FRA1/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295
200
2
+DATA1/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295
200
1
+FRA1/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295
200
1
+DATA1/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295
200
3
+FRA1/MUST_RENAME_THIS_LOGFILE_3.4294967295.4294967295
200
3
+DATA1/MUST_RENAME_THIS_LOGFILE_3.4294967295.4294967295
200
4
+FRA1/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295
200
4
+DATA1/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295
200
11
+DATA1/MUST_RENAME_THIS_LOGFILE_11.4294967295.4294967295
200
11
+FRA1/MUST_RENAME_THIS_LOGFILE_11.4294967295.4294967295
200
12
+DATA1/MUST_RENAME_THIS_LOGFILE_12.4294967295.4294967295
200
12
+FRA1/MUST_RENAME_THIS_LOGFILE_12.4294967295.4294967295
200
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=manual
scope=both;
ALTER
DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER
DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER
DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP
12;
ALTER
DATABASE ADD LOGFILE THREAD 1 group 1('+DATA1','+FRA1') SIZE
200M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 2('+DATA1','+FRA1')
SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 11('+DATA1','+FRA1')
SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 group
3('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2
group 4('+DATA1','+FRA1') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD
2 group 12('+DATA1','+FRA1') SIZE 200M;
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB>
col member for a80
col status for a20
select a.group#,
a.member, b.bytes/1024/1024 as mb, b.status from v$logfile a, v$log b where
a.group# = b.group#;
GROUP#
MEMBER
MB STATUS
----------
--------------------------------------------------------------------------------
----------
--------------------
1
+FRA1/DRDB_STB/ONLINELOG/group_1.389.1203290233
200 UNUSED
1
+DATA1/DRDB_STB/ONLINELOG/group_1.278.1203290235
200 UNUSED
2
+FRA1/DRDB_STB/ONLINELOG/group_2.377.1203290447
200 UNUSED
2
+DATA1/DRDB_STB/ONLINELOG/group_2.311.1203290447
200 UNUSED
3
+DATA1/DRDB_STB/ONLINELOG/group_3.296.1203290237
200 UNUSED
3
+FRA1/DRDB_STB/ONLINELOG/group_3.378.1203290235
200 UNUSED
4
+FRA1/DRDB_STB/ONLINELOG/group_4.414.1203290451
200 UNUSED
4
+DATA1/DRDB_STB/ONLINELOG/group_4.295.1203290451
200 UNUSED
11
+FRA1/DRDB_STB/ONLINELOG/group_11.375.1203290449
200 UNUSED
11
+DATA1/DRDB_STB/ONLINELOG/group_11.285.1203290449
200 UNUSED
12
+FRA1/DRDB_STB/ONLINELOG/group_12.388.1203290453
200 UNUSED
12
+DATA1/DRDB_STB/ONLINELOG/group_12.318.1203290453
200 UNUSED
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=auto
scope=both;
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> alter database open read only;
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT FROM SESSION;
--최종적으로 동기화 잘되는지 확인
--소스
[2025-06-08:22:46:39][rdb01d]<SYS@DRDB1>
--DROP
TABLE tuner.TB_ADG_TEST_20250608_1 PURGE;
CREATE TABLE
tuner.TB_ADG_TEST_20250608_1
(
ADG_TEST_NO
NUMBER(15)
)
;
INSERT
INTO TUNER.TB_ADG_TEST_20250608_1 VALUES (1);
COMMIT;
INSERT INTO
TUNER.TB_ADG_TEST_20250608_1
SELECT (SELECT NVL(MAX(ADG_TEST_NO), 0) FROM
TUNER.TB_ADG_TEST_20250608_1)+LEVEL FROM DUAL CONNECT BY LEVEL <=
10
;
commit;
SELECT COUNT(*) FROM TUNER.TB_ADG_TEST_20250608_1;
COUNT(*)
---------
11
--타켓
[2025-06-08:23:08:23][tdb01p]<SYS@DRDB> SELECT COUNT(*) FROM
TUNER.TB_ADG_TEST_20250608_1;
COUNT(*)
----------
11
1 row selected.