[제목]
[2025-09-20] 12cR2 2 Node RAC to Single ADG 환경에서 Failover후 Standby 백업본으로 (Old) Primary를 복구 후 Switch Over 작업을 하는 테스트
[테스트 개요]
<목적>
RAC Primary 장애 후 Standby로 Failover →
(old) Primary를 Standby 백업본으로 복구 → Switchover로 역할 원복 검증
<절차>
Standby DB에서 L0 풀백업 및 컨트롤/아카이브
백업
Primary 데이터파일/컨트롤/리두 삭제로 장애 유발
Standby DB activate → 신규 Primary
승격
백업을 (old) Primary 서버에 전송 후 standby controlfile로 복구 및 recover
(old)
Primary를 물리 Standby로 편입 후 실시간 동기화(MRP) 확인
신규 Primary에서 DML 후 (old) Primary에
정상 적용되는지 확인
Switchover 수행 → 역할 원복
원복 후에도 양방향 동기화 검증 완료
<검증
포인트>
v$dataguard_stats 지연 0
v$database ROLE 변경 정상
SRL/리두 로그 상태 정상
DML
데이터 건수 양측 일치
<성공
기준>
Failover 후 신규 Primary 서비스 정상
(old) Primary 물리 Standby 복구 및 동기화
정상
Switchover 후 역할 정상 원복, 데이터 적용 정상
[테스트
환경]
<Primary>
OS : Oracle Linux Server 7.9 (grep
^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal :
5.4.17-2102.201.3.el7uek.x86_64 (uname -r)
Oracle Version : Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (echo -e "set pages
0 lines 200 feedback off heading off\nselect banner from v\$version where banner
like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle
Configuration
DB명 : ORA12R1 (echo -e 'set pages 0 feedback
off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' |
sqlplus -s / as sysdba) '
RAC Node 1
Hostname :
ol7ora12r11 (hostname)
Public IP : 192.168.240.11 (getent
ahostsv4 `hostname` | awk '{print $1; exit}')
Instance Name :
ORA12R11 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol7ora12r12
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name : ORA12R12
(echo -e 'set pages 0 feedback off heading off verify off\nselect '\''Instance
Name : '\''||instance_name from v$instance;' | sqlplus -s / as sysdba)
'
Patch Info
Grid (opatch lspatches) (grid os user)
33610989;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:RELEASE) (33610989)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913) (26839277)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720 (33116894)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
33587128;Database Jan 2022 Release Update : 12.2.0.1.220118
(33587128)
Oracle (opatch lspatches) (oracle os user)
33678030;OCW
JAN 2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
33587128;Database
Jan 2022 Release Update : 12.2.0.1.220118 (33587128)
<Standby>
OS : Oracle Linux Server 7.9 (grep
^PRETTY_NAME= /etc/os-release | cut -d= -f2- | tr -d '"')
OS Kernal :
5.4.17-2102.201.3.el7uek.x86_64 (uname -r)
Oracle Version : Oracle Database
12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (echo -e "set pages
0 lines 200 feedback off heading off\nselect banner from v\$version where banner
like 'Oracle Database%';" | sqlplus -s / as sysdba)
Oracle
Configuration
DB명 : ORA12R1 (echo -e 'set pages 0 feedback
off heading off verify off\nselect '\''DB명 : '\''||name from v$database;' |
sqlplus -s / as sysdba) '
DB Unique Name : ORA12R1_STB
Single
Hostname : ol7ora12s1 (hostname)
Public
IP : 192.168.240.10 (getent ahostsv4 `hostname` | awk '{print $1;
exit}')
Instance Name : ORA12R1 (echo -e 'set pages 0 feedback
off heading off verify off\nselect '\''Instance Name : '\''||instance_name from
v$instance;' | sqlplus -s / as sysdba) '
Patch
Info
Grid (opatch lspatches) (grid os user)
33678030;OCW JAN
2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
33610989;TOMCAT RELEASE
UPDATE 12.2.0.1.0(ID:RELEASE) (33610989)
33116894;ACFS JUL 2021
RELEASE UPDATE 12.2.0.1.210720 (33116894)
26839277;DBWLM RELEASE
UPDATE 12.2.0.1.0(ID:170913) (26839277)
33587128;Database Jan 2022
Release Update : 12.2.0.1.220118 (33587128)
Oracle (opatch lspatches) (oracle os user)
33678030;OCW
JAN 2022 RELEASE UPDATE 12.2.0.1.220118 (33678030)
33587128;Database
Jan 2022 Release Update : 12.2.0.1.220118 (33587128)
[내용]
1. Standby DB Full Backup
1-1. 백업 전 실시간 동기화 상태 확인
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 22:49:00 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
COL
NAME FOR A40
COL
VALUE FOR A20
COL
UNIT FOR A40
COL TIME_COMPUTED FOR
A30
COL DATUM_TIME FOR A30
SELECT
NAME,
VALUE,
UNIT,
TIME_COMPUTED,
DATUM_TIME
FROM
V$DATAGUARD_STATS
ORDER BY NAME;
NAME
VALUE
UNIT
TIME_COMPUTED
DATUM_TIME
---------------------------------------- --------------------
---------------------------------------- ------------------------------
------------------------------
apply finish
time
+00 00:00:00.000 day(2) to second(3)
interval
09/20/2025
22:49:08
(NULL)
apply
lag
+00 00:00:00 day(2) to second(0)
interval
09/20/2025
22:49:08
09/20/2025 22:49:06
estimated startup
time
14
second
09/20/2025
22:49:08
(NULL)
transport
lag
+00 00:00:00 day(2) to second(0)
interval
09/20/2025
22:49:08
09/20/2025 22:49:06
4 rows selected.
Elapsed: 00:00:00.00
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ mkdir -p /home/oracle/ORA12R1_BACKUP
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ echo
$ORACLE_SID
ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ alias
rt
alias rt='rman target /'
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$
rt
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 22:33:28 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA12R1 (DBID=270370777)
RMAN>
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP
FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/ORA12R1_BACKUP/%F';
CONFIGURE
ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
run
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2
DEVICE TYPE DISK;
ALLOCATE CHANNEL c3 DEVICE TYPE
DISK;
ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
crosscheck
backupset;
crosscheck backup;
crosscheck
copy;
crosscheck archivelog all;
BACKUP INCREMENTAL LEVEL 0 AS
COMPRESSED BACKUPSET FORMAT '/home/oracle/ORA12R1_BACKUP/%d_L0_%T_%U.bkp'
DATABASE;
delete noprompt obsolete;
delete noprompt expired
backup;
BACKUP AS COMPRESSED BACKUPSET FORMAT
'/home/oracle/ORA12R1_BACKUP/arch_%d_%T_%U.bkp' ARCHIVELOG ALL delete
input;
BACKUP CURRENT CONTROLFILE FORMAT
'/home/oracle/ORA12R1_BACKUP/STB_CUR_CTL_%U_%T' ;
RELEASE CHANNEL
c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE
CHANNEL c4;
}
allocated
channel: c1
channel c1: SID=20 device type=DISK
allocated
channel: c2
channel c2: SID=1109 device type=DISK
allocated
channel: c3
channel c3: SID=1637 device type=DISK
allocated
channel: c4
channel c4: SID=21 device type=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7a4469mr_1_1.bkp
RECID=178 STAMP=1212360411
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7b4469mr_1_1.bkp
RECID=179 STAMP=1212360411
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7h4469pq_1_1.bkp
RECID=183 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7i4469pq_1_1.bkp
RECID=184 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7g4469pq_1_1.bkp
RECID=185 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7f4469pq_1_1.bkp
RECID=186 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-04 RECID=187
STAMP=1212360531
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7k4469ql_1_1.bkp
RECID=188 STAMP=1212360533
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/STB_CTL_7l4469qm_1_1_20250920 RECID=189
STAMP=1212360535
Crosschecked 3 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-05 RECID=190
STAMP=1212360536
Crosschecked 3 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7a4469mr_1_1.bkp
RECID=178 STAMP=1212360411
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7b4469mr_1_1.bkp
RECID=179 STAMP=1212360411
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7h4469pq_1_1.bkp
RECID=183 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7i4469pq_1_1.bkp
RECID=184 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7g4469pq_1_1.bkp
RECID=185 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7f4469pq_1_1.bkp
RECID=186 STAMP=1212360506
crosschecked backup piece: found to be
'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-04 RECID=187
STAMP=1212360531
Crosschecked 2 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/STB_CTL_7l4469qm_1_1_20250920 RECID=189
STAMP=1212360535
Crosschecked 3 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-05 RECID=190
STAMP=1212360536
Crosschecked 3 objects
crosschecked backup piece: found to be 'AVAILABLE'
backup piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7k4469ql_1_1.bkp
RECID=188 STAMP=1212360533
Crosschecked 2 objects
specification does not match any datafile copy in the
repository
specification does not match any control file copy in the
repository
validation succeeded for archived log
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_1_seq_35.311.1212358053
RECID=37 STAMP=1212358052
Crosschecked 1 objects
validation succeeded for archived log
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_1_seq_35.311.1212358053
RECID=37 STAMP=1212358052
Crosschecked 1 objects
Starting backup at 25/09/20
channel c1: starting compressed
incremental level 0 datafile backup set
channel c1: specifying datafile(s) in
backup set
input datafile file number=00003
name=+DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
channel c1: starting
piece 1 at 25/09/20
channel c2: starting compressed incremental level 0
datafile backup set
channel c2: specifying datafile(s) in backup set
input
datafile file number=00002
name=+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
input datafile
file number=00001
name=+DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
channel c2: starting
piece 1 at 25/09/20
channel c3: starting compressed incremental level 0
datafile backup set
channel c3: specifying datafile(s) in backup set
input
datafile file number=00004
name=+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
input datafile file
number=00005 name=+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
channel
c3: starting piece 1 at 25/09/20
channel c4: starting compressed incremental
level 0 datafile backup set
channel c4: specifying datafile(s) in backup
set
input datafile file number=00008
name=+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
input datafile
file number=00007
name=+DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
channel c4: starting
piece 1 at 25/09/20
channel c3: finished piece 1 at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7p4469v2_1_1.bkp
tag=TAG20250920T225114 comment=NONE
channel c3: backup set complete, elapsed
time: 00:00:02
channel c4: finished piece 1 at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7q4469v2_1_1.bkp
tag=TAG20250920T225114 comment=NONE
channel c4: backup set complete, elapsed
time: 00:00:03
channel c2: finished piece 1 at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7o4469v2_1_1.bkp
tag=TAG20250920T225114 comment=NONE
channel c2: backup set complete, elapsed
time: 00:00:16
channel c1: finished piece 1 at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7n4469v2_1_1.bkp
tag=TAG20250920T225114 comment=NONE
channel c1: backup set complete, elapsed
time: 00:00:26
Finished backup at 25/09/20
Starting
Control File and SPFILE Autobackup at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-06
comment=NONE
Finished Control File and SPFILE Autobackup at
25/09/20
RMAN
retention policy will be applied to the command
RMAN retention policy is set
to redundancy 1
Deleting the following obsolete backups and
copies:
Type
Key Completion Time
Filename/Handle
-------------------- ------ ------------------
--------------------
Backup
Set
186 25/09/20
Backup
Piece 186
25/09/20
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7f4469pq_1_1.bkp
Backup
Set
185 25/09/20
Backup
Piece 185
25/09/20
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7g4469pq_1_1.bkp
Backup
Set
183 25/09/20
Backup
Piece 183
25/09/20
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7h4469pq_1_1.bkp
Backup
Set
184 25/09/20
Backup
Piece 184
25/09/20
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7i4469pq_1_1.bkp
Backup
Set
187 25/09/20
Backup
Piece 187
25/09/20
/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-04
Backup
Set
189 25/09/20
Backup
Piece 189
25/09/20
/home/oracle/ORA12R1_BACKUP/STB_CTL_7l4469qm_1_1_20250920
Backup
Set
190 25/09/20
Backup
Piece 190
25/09/20
/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-05
deleted backup
piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7i4469pq_1_1.bkp
RECID=184 STAMP=1212360506
deleted backup piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-04 RECID=187
STAMP=1212360531
deleted backup piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/STB_CTL_7l4469qm_1_1_20250920 RECID=189
STAMP=1212360535
deleted backup piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7h4469pq_1_1.bkp
RECID=183 STAMP=1212360506
Deleted 1 objects
deleted
backup piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7f4469pq_1_1.bkp
RECID=186 STAMP=1212360506
Deleted 1 objects
deleted
backup piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7g4469pq_1_1.bkp
RECID=185 STAMP=1212360506
Deleted 1 objects
deleted
backup piece
backup piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-05 RECID=190
STAMP=1212360536
Deleted 4 objects
specification does not match any backup in the
repository
Starting
backup at 25/09/20
RMAN-06820: warning: failed to archive current log at
primary database
cannot connect to remote database
channel c1: starting
compressed archived log backup set
channel c1: specifying archived log(s) in
backup set
input archived log thread=1 sequence=35 RECID=37
STAMP=1212358052
channel c1: starting piece 1 at 25/09/20
channel c1:
finished piece 1 at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7s4469vt_1_1.bkp
tag=TAG20250920T225141 comment=NONE
channel c1: backup set complete, elapsed
time: 00:00:01
channel c1: deleting archived log(s)
RMAN-08120: warning:
archived log not deleted, not yet applied by standby
archived log file
name=+FRA1/ORA12R1_STB/ARCHIVELOG/2025_09_20/thread_1_seq_35.311.1212358053
thread=1 sequence=35
Finished backup at 25/09/20
Starting
backup at 25/09/20
channel c1: starting full datafile backup set
channel
c1: specifying datafile(s) in backup set
including current control file in
backup set
channel c1: starting piece 1 at 25/09/20
channel c1: finished
piece 1 at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/STB_CUR_CTL_7t4469vu_1_1_20250920
tag=TAG20250920T225142 comment=NONE
channel c1: backup set complete, elapsed
time: 00:00:01
Finished backup at 25/09/20
Starting
Control File and SPFILE Autobackup at 25/09/20
piece
handle=/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-07
comment=NONE
Finished Control File and SPFILE Autobackup at
25/09/20
released channel: c1
released channel: c2
released channel: c3
released channel: c4
2.
Primary 장애 상황 발생
--1번
노드
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as
sysdba"'
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 22:57:01 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12r11]<SYS@ORA12R11>$ shutdown
abort
ORACLE instance shut down.
--2번
노드
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12R12:oracle@ol7ora12r12][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 22:57:12 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12r12]<SYS@ORA12R12>$ shutdown
abort
ORACLE instance shut down.
--테스트를 위해 asmcmd로 직접 데이터
파일을 제거해야하므로 asm 인스턴스 강제로 재기동
--1번 노드
[+ASM1:grid@ol7ora12r11][/home/grid]$
srvctl stop asm -n ol7ora12r11 -force
[+ASM1:grid@ol7ora12r11][/home/grid]$
srvctl start asm -n ol7ora12r11
--2번
노드
[+ASM2:grid@ol7ora12r12][/home/grid]$ srvctl stop asm -n ol7ora12r12
-force
[+ASM2:grid@ol7ora12r12][/home/grid]$ srvctl start asm -n
ol7ora12r12
[+ASM1:grid@ol7ora12r11][/home/grid]$ alias
asmcmd
alias asmcmd='rlwrap
asmcmd -p'
[+ASM1:grid@ol7ora12r11][/home/grid]$
asmcmd
ASMCMD
[+DATA1/ORA12R1/CONTROLFILE] > rm -f *
ASMCMD
[+FRA1/ORA12R1/CONTROLFILE] > rm -f *
ASMCMD [+DATA1/ORA12R1/DATAFILE]
> rm -f
ASMCMD [+DATA1/ORA12R1/TEMPFILE] > rm -f *
ASMCMD
[+DATA1/ORA12R1/ONLINELOG] > rm -f *
ASMCMD [+FRA1/ORA12R1/ONLINELOG] >
rm -f *
--> 컨트롤 파일, 데이터 파일, 템프 파일, 리두로그
삭제
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 23:07:23 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
[]<SYS@ORA12R11>$
startup
ORACLE instance started.
Total
System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers 3539992576 bytes
Redo
Buffers
8142848 bytes
ORA-00205: error in identifying control file, check alert log
for more info
--> 컨트롤 파일이 없어서 mount부터 실패하는 상황임
장애 발생!
[]<SYS@ORA12R11>$ shutdown
abort
ORACLE instance shut down.
3. Primary DB의 장애로 인해 Standby DB에서 Failover 작업
접속할
데이터베이스(DB_NAME)를 선택하세요:
1) ORA12S1
2) ORA12R1
3) RESERVED
번호를 입력하세요
(1,2,3): 2
현재 ORACLE_SID:
ORA12R1
ORA12R1 환경 설정 적용 중...
환경 설정 완료 : 현재 ORACLE_SID =
ORA12R1
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12R1:oracle@ol7ora12s1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Sep 20 23:08:43 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12s1]<SYS@ORA12R1>$ alter database recover managed standby database cancel;
Database altered.
Elapsed: 00:00:01.01
[ol7ora12s1]<SYS@ORA12R1>$ alter database activate physical
standby database;
Database altered.
Elapsed: 00:00:03.36
[ol7ora12s1]<SYS@ORA12R1>$
SET LINESIZE 220
SET PAGESIZE
100
SET TAB OFF
COL
DBID
FOR 999999999999
COL
NAME
FOR A12
COL
RESETLOGS_CHANGE# FOR
9999999999
COL
RESETLOGS_TIME
FOR A20
COL
CHECKPOINT_CHANGE# FOR
9999999999
COL
DATABASE_ROLE
FOR A18
COL
CURRENT_SCN
FOR 999999999999
COL
DB_UNIQUE_NAME
FOR A20
COL STANDBY_BECAME_PRIMARY_SCN FOR 9999999999
COL
PRIMARY_DB_UNIQUE_NAME FOR A20
SELECT
DBID
, NAME
,
RESETLOGS_CHANGE#
,
RESETLOGS_TIME
,
CHECKPOINT_CHANGE#
,
DATABASE_ROLE
,
CURRENT_SCN
,
DB_UNIQUE_NAME
,
STANDBY_BECAME_PRIMARY_SCN
,
PRIMARY_DB_UNIQUE_NAME
FROM v$database;
DBID NAME RESETLOGS_CHANGE#
RESETLOGS_TIME CHECKPOINT_CHANGE#
DATABASE_ROLE CURRENT_SCN
DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN
PRIMARY_DB_UNIQUE_NA
------------- ------------ -----------------
-------------------- ------------------ ------------------ -------------
-------------------- --------------------------
--------------------
270370777
ORA12R1
5386572
2025-09-20:23:09:10
5386572
PRIMARY
0
ORA12R1_STB
5386569 ORA12R1
--> failover 되는 순간
resetlogs_time이 갱신됨 (새로운 디비가 되는 것임)
--> 아직 mount 상태라서 CURRENT_SCN이 0임
4. (old) Standby에서 백업받았던 백업본을 (old) Primary로 전송
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ mkdir -p /home/oracle/ORA12R1_BACKUP
[ORA12R1:oracle@ol7ora12s1][/home/oracle/ORA12R1_BACKUP]$ scp *
ol7ora12r11:/home/oracle/ORA12R1_BACKUP
oracle@ol7ora12r11's
password:
'
arch_ORA12R1_20250920_7a4469mr_1_1.bkp
100% 2303KB 182.1MB/s
00:00
arch_ORA12R1_20250920_7b4469mr_1_1.bkp
100% 2238KB 253.6MB/s
00:00
arch_ORA12R1_20250920_7k4469ql_1_1.bkp
100% 2238KB 211.1MB/s
00:00
arch_ORA12R1_20250920_7s4469vt_1_1.bkp
100% 2238KB 159.5MB/s
00:00
c-270370777-20250920-06
100% 19MB 212.7MB/s
00:00
c-270370777-20250920-07
100% 19MB 218.0MB/s
00:00
ORA12R1_L0_20250920_7n4469v2_1_1.bkp
100% 450MB 234.4MB/s
00:01
ORA12R1_L0_20250920_7o4469v2_1_1.bkp
100% 244MB 244.3MB/s
00:01
ORA12R1_L0_20250920_7p4469v2_1_1.bkp
100% 12MB 185.9MB/s
00:00
ORA12R1_L0_20250920_7q4469v2_1_1.bkp
100% 16MB 240.0MB/s
00:00
STB_CUR_CTL_7t4469vu_1_1_20250920
100% 19MB 242.1MB/s 00:00
[ORA12R11:oracle@ol7ora12r11][/home/oracle/ORA12R1_BACKUP]$ ls -l
/home/oracle/ORA12R1_BACKUP
total 806904
-rw-r-----. 1 oracle
oinstall 2358272 Sep 20 23:18
arch_ORA12R1_20250920_7a4469mr_1_1.bkp
-rw-r-----. 1 oracle
oinstall 2291712 Sep 20 23:18
arch_ORA12R1_20250920_7b4469mr_1_1.bkp
-rw-r-----. 1 oracle
oinstall 2291712 Sep 20 23:18
arch_ORA12R1_20250920_7k4469ql_1_1.bkp
-rw-r-----. 1 oracle
oinstall 2291712 Sep 20 23:18
arch_ORA12R1_20250920_7s4469vt_1_1.bkp
-rw-r-----. 1 oracle oinstall
20234240 Sep 20 23:18 c-270370777-20250920-06
-rw-r-----. 1 oracle
oinstall 20234240 Sep 20 23:18 c-270370777-20250920-07
-rw-r-----. 1
oracle oinstall 471597056 Sep 20 23:18
ORA12R1_L0_20250920_7n4469v2_1_1.bkp
-rw-r-----. 1 oracle oinstall 256155648
Sep 20 23:18 ORA12R1_L0_20250920_7o4469v2_1_1.bkp
-rw-r-----. 1 oracle
oinstall 12181504 Sep 20 23:18
ORA12R1_L0_20250920_7p4469v2_1_1.bkp
-rw-r-----. 1 oracle oinstall
16424960 Sep 20 23:18 ORA12R1_L0_20250920_7q4469v2_1_1.bkp
-rw-r-----. 1
oracle oinstall 20201472 Sep 20 23:18
STB_CUR_CTL_7t4469vu_1_1_20250920
5. (old) Primary 서버에서 (old) Standby DB의 백업본으로 복구
[ORA12R11:oracle@ol7ora12r11][/home/oracle/ORA12R1_BACKUP]$ echo
$ORACLE_SID
ORA12R11
[ORA12R11:oracle@ol7ora12r11][/home/oracle/ORA12R1_BACKUP]$ alias
rt
alias rt='rman target /'
[ORA12R11:oracle@ol7ora12r11][/home/oracle/ORA12R1_BACKUP]$
rt
Recovery Manager: Release 12.2.0.1.0 - Production on Sat Sep 20 23:20:09 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
connected
to target database (not started)
Oracle instance started
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers
3539992576 bytes
Redo
Buffers
8142848 bytes
RMAN> restore standby controlfile from '/home/oracle/ORA12R1_BACKUP/STB_CUR_CTL_7t4469vu_1_1_20250920';
Starting
restore at 2025-09-20 23:24:02
allocated channel: ORA_DISK_1
channel
ORA_DISK_1: SID=559 instance=ORA12R11 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore
complete, elapsed time: 00:00:01
output file
name=+FRA1/ORA12R1/CONTROLFILE/current.294.1212362643
output file
name=+DATA1/ORA12R1/CONTROLFILE/current.280.1212362643
Finished
restore at 2025-09-20 23:24:03
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1/CONTROLFILE/
Type Redund
Striped
Time
Sys Block_Size Blocks
Bytes Space Name
CONTROLFILE UNPROT
FINE SEP 20 23:00:00
Y 16384
1229 20135936 33554432
current.280.1212362643
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd ls -sl
+FRA1/ORA12R1/CONTROLFILE/
Type Redund
Striped
Time
Sys Block_Size Blocks
Bytes Space Name
CONTROLFILE UNPROT
FINE SEP 20 23:00:00
Y 16384
1229 20135936 33554432
current.294.1212362643
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup mount
connected
to target database (not started)
Oracle instance started
database
mounted
Total System Global Area 5033164800 bytes
Fixed
Size
8631048 bytes
Variable
Size
1476398328 bytes
Database
Buffers
3539992576 bytes
Redo
Buffers
8142848 bytes
RMAN> list backup;
List of
Backup Sets
===================
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
178
2.25M
DISK 00:00:00
2025-09-20 22:46:51
BP Key:
178 Status: AVAILABLE Compressed: YES Tag:
TAG20250920T224651
Piece Name:
/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7a4469mr_1_1.bkp
List
of Archived Logs in backup set 178
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
2
32 5267023 2025-09-20 22:05:03
5270952 2025-09-20 22:07:32
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
179
2.19M
DISK 00:00:00
2025-09-20 22:46:51
BP Key:
179 Status: AVAILABLE Compressed: YES Tag:
TAG20250920T224651
Piece Name:
/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7b4469mr_1_1.bkp
List
of Archived Logs in backup set 179
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
35 5268293 2025-09-20 22:05:44
5270957 2025-09-20 22:07:32
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
188
2.19M
DISK 00:00:00
2025-09-20 22:48:53
BP Key:
188 Status: AVAILABLE Compressed: YES Tag:
TAG20250920T224853
Piece Name:
/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7k4469ql_1_1.bkp
List
of Archived Logs in backup set 188
Thrd Seq
Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
35 5268293 2025-09-20 22:05:44
5270957 2025-09-20 22:07:32
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
191 Incr 0
11.61M DISK
00:00:01 2025-09-20
22:51:15
BP Key: 191
Status: AVAILABLE Compressed: YES Tag:
TAG20250920T225114
Piece Name:
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7p4469v2_1_1.bkp
List
of Datafiles in backup set 191
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
4 0 Incr
5270952 2025-09-20 22:07:32
5285547 NO
+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
5 0 Incr 5270952 2025-09-20 22:07:32
5285451 NO
+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
192 Incr 0
15.66M DISK
00:00:02 2025-09-20
22:51:16
BP Key: 192
Status: AVAILABLE Compressed: YES Tag:
TAG20250920T225114
Piece Name:
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7q4469v2_1_1.bkp
List
of Datafiles in backup set 192
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
7 0 Incr
5270952 2025-09-20
22:07:32
NO +DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
8 0 Incr 5270952 2025-09-20
22:07:32
NO
+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
193 Incr 0
244.28M DISK
00:00:12 2025-09-20
22:51:26
BP Key: 193
Status: AVAILABLE Compressed: YES Tag:
TAG20250920T225114
Piece Name:
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7o4469v2_1_1.bkp
List
of Datafiles in backup set 193
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
1 0 Incr
5270952 2025-09-20 22:07:32
5285542 NO
+DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
2
0 Incr 5270952 2025-09-20
22:07:32
NO
+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
194 Incr 0
449.74M DISK
00:00:25 2025-09-20
22:51:39
BP Key: 194
Status: AVAILABLE Compressed: YES Tag:
TAG20250920T225114
Piece Name:
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7n4469v2_1_1.bkp
List
of Datafiles in backup set 194
File LV Type Ckp SCN
Ckp Time Abs
Fuz SCN Sparse Name
---- -- ---- ---------- -------------------
----------- ------ ----
3 0 Incr
5270952 2025-09-20 22:07:32
5285546 NO
+DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
BS
Key Type LV Size Device Type Elapsed
Time Completion Time
------- ---- -- ---------- ----------- ------------
-------------------
195 Full
19.28M DISK
00:00:00 2025-09-20
22:51:40
BP Key: 195
Status: AVAILABLE Compressed: NO Tag:
TAG20250920T225140
Piece Name:
/home/oracle/ORA12R1_BACKUP/c-270370777-20250920-06
SPFILE Included:
Modification time: 2025-09-20 22:11:33
SPFILE db_unique_name:
ORA12R1_STB
Standby Control File Included: Ckp SCN:
5270952 Ckp time: 2025-09-20
22:07:32
BS
Key Size Device Type Elapsed Time
Completion Time
------- ---------- ----------- ------------
-------------------
196
2.19M
DISK 00:00:00
2025-09-20 22:51:41
BP Key:
196 Status: AVAILABLE Compressed: YES Tag:
TAG20250920T225141
Piece Name:
/home/oracle/ORA12R1_BACKUP/arch_ORA12R1_20250920_7s4469vt_1_1.bkp
List of Archived Logs in backup set
196
Thrd Seq Low SCN Low
Time Next
SCN Next Time
---- ------- ---------- -------------------
---------- ---------
1
35 5268293 2025-09-20 22:05:44
5270957 2025-09-20 22:07:32
--> (old) Standby 서버에서 백업받았던 위치와 디렉토리 위치가 똑같기 때문에 catalog start
with를 할 필요가 없음
RMAN> restore database;
Starting
restore at 2025-09-20 23:27:40
allocated channel: ORA_DISK_1
channel
ORA_DISK_1: SID=563 instance=ORA12R11 device type=DISK
channel
ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1:
specifying datafile(s) to restore from backup set
channel ORA_DISK_1:
restoring datafile 00004 to
+DATA1/ORA12R1_STB/DATAFILE/undotbs1.271.1212312049
channel ORA_DISK_1:
restoring datafile 00005 to
+DATA1/ORA12R1_STB/DATAFILE/undotbs2.273.1212312049
channel ORA_DISK_1:
reading from backup piece
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7p4469v2_1_1.bkp
channel
ORA_DISK_1: piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7p4469v2_1_1.bkp
tag=TAG20250920T225114
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1:
starting datafile backup set restore
channel ORA_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile
00007 to +DATA1/ORA12R1_STB/DATAFILE/users.275.1212312049
channel ORA_DISK_1:
restoring datafile 00008 to
+DATA1/ORA12R1_STB/DATAFILE/tuner_idx1.274.1212312049
channel ORA_DISK_1:
reading from backup piece
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7q4469v2_1_1.bkp
channel
ORA_DISK_1: piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7q4469v2_1_1.bkp
tag=TAG20250920T225114
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1:
starting datafile backup set restore
channel ORA_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile
00001 to +DATA1/ORA12R1_STB/DATAFILE/system.277.1212312053
channel
ORA_DISK_1: restoring datafile 00002 to
+DATA1/ORA12R1_STB/DATAFILE/tuner_data1.276.1212312053
channel ORA_DISK_1:
reading from backup piece
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7o4469v2_1_1.bkp
channel
ORA_DISK_1: piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7o4469v2_1_1.bkp
tag=TAG20250920T225114
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1:
starting datafile backup set restore
channel ORA_DISK_1: specifying
datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile
00003 to +DATA1/ORA12R1_STB/DATAFILE/sysaux.278.1212312077
channel
ORA_DISK_1: reading from backup piece
/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7n4469v2_1_1.bkp
channel
ORA_DISK_1: piece
handle=/home/oracle/ORA12R1_BACKUP/ORA12R1_L0_20250920_7n4469v2_1_1.bkp
tag=TAG20250920T225114
channel ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at
2025-09-20 23:28:46
[+ASM1:grid@ol7ora12r11][/home/grid]$ asmcmd ls -sl
+DATA1/ORA12R1/DATAFILE
Type
Redund Striped
Time
Sys Block_Size Blocks
Bytes Space Name
DATAFILE
UNPROT COARSE SEP 20 23:00:00
Y 8192 333313
2730500096 2738880512 SYSAUX.259.1212362891
DATAFILE
UNPROT COARSE SEP 20 23:00:00
Y 8192
106241 870326272 876609536
SYSTEM.258.1212362867
DATAFILE UNPROT COARSE SEP 20
23:00:00 Y
8192 196609 1610620928 1619001344
TUNER_DATA1.257.1212362867
DATAFILE UNPROT COARSE SEP
20 23:00:00 Y
8192 131073 1073750016 1082130432
TUNER_IDX1.282.1212362863
DATAFILE UNPROT COARSE SEP
20 23:00:00 Y
8192 122241 1001398272 1006632960
UNDOTBS1.265.1212362861
DATAFILE UNPROT COARSE SEP 20
23:00:00 Y
8192 12801 104865792 109051904
UNDOTBS2.283.1212362861
DATAFILE UNPROT COARSE SEP 20
23:00:00 Y
8192 1921 15736832
16777216 USERS.281.1212362863
RMAN> recover database;
Starting
recover at 2025-09-20 23:33:37
using channel ORA_DISK_1
starting media recovery
archived
log for thread 1 with sequence 35 is already on disk as file
+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_1_seq_35.323.1212358053
archived
log for thread 1 with sequence 36 is already on disk as file
+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_1_seq_36.324.1212361027
archived
log for thread 1 with sequence 37 is already on disk as file
+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_1_seq_37.325.1212361029
archived
log for thread 2 with sequence 33 is already on disk as file
+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_2_seq_33.326.1212361029
archived
log file name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_2_seq_33.326.1212361029
thread=2 sequence=33
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_1_seq_35.323.1212358053 thread=1
sequence=35
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_1_seq_36.324.1212361027 thread=1
sequence=36
archived log file
name=+FRA1/ORA12R1/ARCHIVELOG/2025_09_20/thread_1_seq_37.325.1212361029 thread=1
sequence=37
media recovery complete, elapsed time: 00:00:01
Finished
recover at 2025-09-20 23:33:39
6.
(old) Standby 를 open 시키고 (old) Primary와 실시간 동기화 시키기
--(old) Standby 를
open
[ol7ora12s1]<SYS@ORA12R1>$ alter database
open;
Database altered.
Elapsed: 00:00:05.45
--(old) Primary 와 실시간
동기화
[ol7ora12r11]<SYS@ORA12R11>$ alter database recover managed standby
database cancel;
[ol7ora12r11]<SYS@ORA12R11>$ alter database open read
only;
[ol7ora12r11]<SYS@ORA12R11>$ ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
[ol7ora12r11]<SYS@ORA12R11>$
SET LINESIZE 220
SET PAGESIZE
100
SET TAB OFF
COL
DBID
FOR 999999999999
COL
NAME
FOR A12
COL
RESETLOGS_CHANGE# FOR
9999999999
COL
RESETLOGS_TIME
FOR A20
COL
CHECKPOINT_CHANGE# FOR
9999999999
COL
DATABASE_ROLE
FOR A18
COL
CURRENT_SCN
FOR 999999999999
COL
DB_UNIQUE_NAME
FOR A20
COL STANDBY_BECAME_PRIMARY_SCN FOR 9999999999
COL
PRIMARY_DB_UNIQUE_NAME FOR A20
SELECT
DBID
, NAME
,
RESETLOGS_CHANGE#
,
RESETLOGS_TIME
,
CHECKPOINT_CHANGE#
,
DATABASE_ROLE
,
CURRENT_SCN
,
DB_UNIQUE_NAME
,
STANDBY_BECAME_PRIMARY_SCN
,
PRIMARY_DB_UNIQUE_NAME
FROM v$database;
DBID
NAME RESETLOGS_CHANGE#
RESETLOGS_TIME CHECKPOINT_CHANGE#
DATABASE_ROLE CURRENT_SCN
DB_UNIQUE_NAME STANDBY_BECAME_PRIMARY_SCN
PRIMARY_DB_UNIQUE_NA
------------- ------------ -----------------
-------------------- ------------------ ------------------ -------------
-------------------- --------------------------
--------------------
270370777
ORA12R1
5386572
2025-09-20:23:09:10
4314883 PHYSICAL STANDBY 5388191
ORA12R1
0 ORA12R1_STB
col
group# for 999
col thread# for 999
col member for a50
col STATUS for
a10
col TYPE for a10
col MB for 999,999,999
col STATUS for
a15
SELECT A.GROUP#
,
B.THREAD#
, B.SEQUENCE#
, A.MEMBER
, A.STATUS
,
A.TYPE
, B.BYTES/1024/1024 AS
MB
, B.STATUS
FROM V$LOGFILE
A
, V$LOG B
WHERE A.GROUP# =
B.GROUP#
ORDER BY 1
;
GROUP#
THREAD# SEQUENCE#
MEMBER
STATUS
TYPE
MB STATUS
------ ------- ----------
-------------------------------------------------- --------------- ----------
------------ ---------------
1
1 0
+DATA1/ORA12R1/ONLINELOG/group_1.261.1212363267
(NULL)
ONLINE
200 UNUSED
1
1 0
+FRA1/ORA12R1/ONLINELOG/group_1.263.1212363267
(NULL)
ONLINE
200 UNUSED
2
1 0
+FRA1/ORA12R1/ONLINELOG/group_2.292.1212363267
(NULL)
ONLINE
200 UNUSED
2
1 0
+DATA1/ORA12R1/ONLINELOG/group_2.273.1212363269
(NULL)
ONLINE
200 UNUSED
3
1 0
+DATA1/ORA12R1/ONLINELOG/group_3.266.1212363269
(NULL)
ONLINE
200 UNUSED
3
1 0
+FRA1/ORA12R1/ONLINELOG/group_3.300.1212363269
(NULL)
ONLINE
200 UNUSED
4
2 0
+FRA1/ORA12R1/ONLINELOG/group_4.287.1212363269
(NULL)
ONLINE
200 UNUSED
4
2 0
+DATA1/ORA12R1/ONLINELOG/group_4.272.1212363269
(NULL)
ONLINE
200 UNUSED
5
2 0
+FRA1/ORA12R1/ONLINELOG/group_5.303.1212363269
(NULL)
ONLINE
200 UNUSED
5
2 0
+DATA1/ORA12R1/ONLINELOG/group_5.277.1212363269
(NULL)
ONLINE
200 UNUSED
6
2 0
+FRA1/ORA12R1/ONLINELOG/group_6.301.1212363269
(NULL)
ONLINE
200 UNUSED
6
2 0
+DATA1/ORA12R1/ONLINELOG/group_6.278.1212363269
(NULL)
ONLINE
200 UNUSED
COLUMN
DUMMY FORMAT 999
COLUMN GROUP# FORMAT 9999
COLUMN THREAD# FORMAT
9999
COLUMN SEQUENCE# FORMAT 9999999
COLUMN ARCHIVED FORMAT A8
COLUMN
STATUS FORMAT A12
COLUMN MEMBER FORMAT A60 WORD_WRAPPED
SELECT 1 AS
DUMMY,
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.STATUS AS
LOG_STATUS,
b.TYPE,
b.MEMBER
FROM
V$STANDBY_LOG a, v$logfile b
WHERE a.group# = b.group#
ORDER
BY a.GROUP#;
DUMMY
GROUP# THREAD# SEQUENCE# ARCHIVED STATUS
LOG_STATUS
TYPE MEMBER
----- ------ -------
--------- -------- ------------ --------------------- ----------
------------------------------------------------------------
1 7
1 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_7.258.1212363323
1 7
1 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_7.269.1212363325
1 8
1 2
YES ACTIVE
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_8.299.1212363325
1 8
1 2
YES ACTIVE
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_8.260.1212363325
1 9
1 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_9.327.1212363325
1 9
1 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_9.263.1212363325
1 10
1 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_10.259.1212363323
1 10
1 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_10.268.1212363325
1 11
2 0
YES UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1_STB/ONLINELOG/group_11.261.1212316665
1 11
2 0
YES UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1_STB/ONLINELOG/group_11.287.1212316665
1 12
2 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_12.296.1212363319
1 12
2 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_12.274.1212363319
1 13
2 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_13.256.1212363319
1 13
2 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_13.270.1212363319
1 14
2 0
NO UNASSIGNED
(NULL)
STANDBY
+FRA1/ORA12R1/ONLINELOG/group_14.262.1212363319
1 14
2 0
NO UNASSIGNED
(NULL)
STANDBY
+DATA1/ORA12R1/ONLINELOG/group_14.267.1212363319
col name
for a80
select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA1/ORA12R1/TEMPFILE/temp.264.1212363457
+DATA1/ORA12R1/TEMPFILE/tuner_temp.271.1212363457
select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA1/ORA12R1/DATAFILE/system.258.1212362867
+DATA1/ORA12R1/DATAFILE/tuner_data1.257.1212362867
+DATA1/ORA12R1/DATAFILE/sysaux.259.1212362891
+DATA1/ORA12R1/DATAFILE/undotbs1.265.1212362861
+DATA1/ORA12R1/DATAFILE/undotbs2.283.1212362861
+DATA1/ORA12R1/DATAFILE/users.281.1212362863
+DATA1/ORA12R1/DATAFILE/tuner_idx1.282.1212362863
COL
NAME FOR A40
COL
VALUE FOR A20
COL
UNIT FOR A40
COL TIME_COMPUTED FOR
A30
COL DATUM_TIME FOR A30
SELECT
NAME,
VALUE,
UNIT,
TIME_COMPUTED,
DATUM_TIME
FROM
V$DATAGUARD_STATS
ORDER BY NAME;
NAME
VALUE
UNIT
TIME_COMPUTED
DATUM_TIME
---------------------------------------- --------------------
---------------------------------------- ------------------------------
------------------------------
apply finish
time
+00 00:00:00.000 day(2) to second(3)
interval
09/20/2025
23:45:55
(NULL)
apply
lag
+00 00:00:00 day(2) to second(0)
interval
09/20/2025
23:45:55
09/20/2025 23:45:54
estimated startup
time
14
second
09/20/2025
23:45:55
(NULL)
transport
lag
+00 00:00:00 day(2) to second(0)
interval
09/20/2025
23:45:55
09/20/2025 23:45:54
7.
(old) Standby 에서 (old) Primary로 동기화가 제대로 되는지 확인
--(old)
Standby
drop user adg_test cascade;
create user adg_test identified by
oracle;
GRANT RESOURCE, CONNECT TO adg_test;
ALTER USER adg_test ACCOUNT
UNLOCK;
ALTER USER adg_test DEFAULT TABLESPACE users;
ALTER USER adg_test
TEMPORARY TABLESPACE temp;
ALTER USER adg_test quota unlimited on
users;
create
table adg_test.tb_adg_test_02
(
adg_test_02_no
number(15)
)
;
insert into adg_test.tb_adg_test_02 values (1);
commit;
insert into
adg_test.tb_adg_test_02
select
(select nvl(max(adg_test_02_no),
0) from adg_test.tb_adg_test_02)+level from dual connect by level
<= 1000000
;
commit;
--(old)
Primary
[ol7ora12r11]<SYS@ORA12R11>$ select count(*) from
adg_test.TB_ADG_TEST_02;
COUNT(*)
----------
1000001
1 row selected.
Elapsed: 00:00:00.00
8. (old) Standby 에서 (old) Primary 로 Switch Over 작업
--원래의 Primary가 Primary과 되고 원래의 Standby 가 Standby가 되는 것임
--(old) Standby
에서
[ol7ora12s1]<SYS@ORA12R1>$ alter database commit to switchover to
physical standby with session shutdown;
--(old) Primary
에서
[ol7ora12r11]<SYS@ORA12R11>$ alter database recover managed standby
database cancel;
[ol7ora12r11]<SYS@ORA12R11>$ alter database commit to switchover to
primary with session shutdown;
[ol7ora12r11]<SYS@ORA12R11>$ alter database
open;
--(old) Standby
에서
[ol7ora12s1]<SYS@ORA12R1>$
startup
[ol7ora12s1]<SYS@ORA12R1>$ ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
--> 지금부터 (old) Standby 가
Standby가 되고
--> (old)
Primary는 Primary가 됨
9. Primary 에서 Standby 로 동기화가 제대로 되는지 확인
--Primary
drop user adg_test cascade;
create
user adg_test identified by oracle;
GRANT RESOURCE, CONNECT TO
adg_test;
ALTER USER adg_test ACCOUNT UNLOCK;
ALTER USER adg_test DEFAULT
TABLESPACE users;
ALTER USER adg_test TEMPORARY TABLESPACE temp;
ALTER
USER adg_test quota unlimited on users;
create table
adg_test.tb_adg_test_03
(
adg_test_03_no
number(15)
)
;
insert into adg_test.tb_adg_test_03 values (1);
commit;
insert into adg_test.tb_adg_test_03
select (select
nvl(max(adg_test_03_no), 0) from
adg_test.tb_adg_test_03)+level from dual connect by
level <= 1000000
;
commit;
--Standby
SQL> select count(*)
from adg_test.TB_ADG_TEST_03;
COUNT(*)
---------
1000001
1 rows selected.
--Primary의 RAC 2번 노드도
올림
[ORA12R11:oracle@ol7ora12r11][/home/oracle]$ srvctl start instance -db
ora12r1 -i ORA12R12